Made good on my MySQL patch promise to make things better for everyone!!

For those that didn’t read my last post on MySQL (Its a couple of blogs down), I wrote about how we had significant problems with MySQL in general. Specifically we hated the inability to track usage by users thus allowing certain users to bring a server to it knees without being able to block and assign “blame” to the individual account that caused the problems.

While those issues are still present, we now have a patch to track that usage closely and are now creating automated tools to react in realtime to MySQL overloads and inefficient scripts that are running. So not only we will be able to block disproportionate usage by users accessing MySQL, but when our tools are done we will be able to present users with exact usage and where the problems occur on their side so that they will be able to fix/update their scripts to address these MySQL issues. In the past we knew many of the problems that happened on our servers, but we unable to react in realtime. This history helps us address repeat offenders but didn’t really do anything for the stability of our system in real time. This will soon change.

In keeping with my original promise (two blogs entries down), I have decided to make this patch public for all users of the MySQL server. If you are a hosting company and have problems with MySQL usage (I KNOW YOU DO, 100% OF YOU :) ) and understand the importance of individual accountability in MySQL I HIGHLY suggest you make use of this patch. If you review the patch and don’t think its worth while, then you really ought not to be doing shared hosting – I know thats harsh to say, but its true in my opinion!

Ok, enough blabbing – Below is a link to the patch – We have tested it to work with MySQL 5.0.45. If you are running a different version of the MySQL server I will at no cost create a version of the patch to work down to 5.0.37. If you are running a version of the server before that the patch may apply cleanly, but I can’t/won’t guarantee that it will work correctly. I will update the patch to ensure that it will continue to work in 5.0.x going forward from now on.

http://www.mattheaton.com/mysql-userstats.diff

Here is the link to describe how to access and use the additional statistics that will now be available to you in the MySQL server. Please review and test carefully, as I am unwilling to provide support directly for the patch. The patch is there for you to use, but I don’t have the time or inclination to do support for something like this.

http://mattheaton.com/mysql-usage.html

I do have high hopes for what we can accomplish with the tools that we write based on the information gathered with this patch. Although customers of Bluehost/Hostmonster won’t necessarily see or feel any difference on their end, you should notice a much more stable server experience going forward. It will most likely take us 30-45 days to roll out these changes.

For all the complaining I do about MySQL, I am actually extremely grateful to the MySQL team for creating a wonderful database that is completely free to use and provides a stellar databse for millions of people to use every day.

Thanks,
Matt Heaton / President Bluehost.com

14 Responses to “Made good on my MySQL patch promise to make things better for everyone!!”

  1. It would be helpful to have a way to know when this is implemented on “my” Bluehost box. Also to have a little FAQ added to the knowledge base telling how we can use the capabilities to self evaluate our MySQL usage. Waytogo Bluehost. The main reason you are so popular is because you keep improving. It helps us, and your bottom line. Keep it up.

  2. Asia says:

    Hi, this is not related, however, I must comment. I’m a customer I host several of my own sites w/ you & several of my customers’. I know I’m not a big customer, but pls consider this comment seriously.

    I see you use WordPress & I know a ton of your Customers do also, so why does it take sometimes weeks before update versions are available via BH. For all that you do to keep Bluehost competitive, I would think this is a small attention to detail that would make customers like me happy but importantly keep us competitive. I have asked support & they give me an array of flimsy but polite excuses. I like Bluehost but I’m considering moving because of this. I hope you can change this, I look forward to seeing this improvement.
    Thanks

  3. rusty_angel says:

    It fails to compile on openSuse 10.3 and fc2. I patched vanilla 5.0.45 src.rpm. gcc complains about ‘thd’ in sql/sql_parse.cc

    sql_parse.cc: In member function `virtual void
    st_select_lex::set_lock_for_tables(thr_lock_type)’:
    sql_parse.cc:7015: error: `end_time_error’ undeclared (first use this function)
    sql_parse.cc:7015: error: (Each undeclared identifier is reported only once for
    each function it appears in.)
    sql_parse.cc:7015: error: `end_time’ undeclared (first use this function)
    sql_parse.cc:7016: error: `end_usecs’ undeclared (first use this function)
    sql_parse.cc:7020: error: `start_usecs’ undeclared (first use this function)
    sql_parse.cc:7020: error: `start_time_error’ undeclared (first use this
    function)
    sql_parse.cc:7021: error: `thd’ undeclared (first use this function)

  4. rusty_angel says:

    I managed to update patch for 5.0.51a and make in compile (looks like your diff did something wrong and simply put some code into another function. Will build rpm, test it and release if it’s working.

  5. Dave says:

    Looks great Matt! I don’t think it’s been rolled out to my box yet. Here are some issues you might consider addressing in the FAQ: Will normal users have the ability to run this command, or will it only be allowed as root or another administrator account, to be accessed by BH admins? Is there any performance overhead from tracking these stats? How long will it take for the stats to reset? For example, if you imported large database, or had a daily cron that exported the db, would that make your daily stats go off the chart? Is account suspension automated, like CPU quota, or do you get an email warning, or what?

    Thanks!

  6. hola:

    es un placer conocerte aunque sea por este medio. me gustaria comentar lo siguiente:

    se que te sorprendera que esto no este en ingles. pero bueno, esto hara que te des cuenta que!! no cuentas con personal bilingue que nos ayude a los que no hablamos ingles medianamente comprensible.

    asi que decidi saludarte y comentar al respecto, hay mucha gente en mexico y en otros paises de habla hispana, que probablemente les interese estar en tu web hosting.
    pero los problemas tecnicos o preguntas podrian ser un problema mayor al no poder comunicarnos apropiadamente.

    me despido de ti y de tus lectores del blog.

    I born to fishing.

  7. I am an extremely unhappy customer. I am running an online newspaper and MySQL was out for 7 hours on Apr. 3. I know because I log SQL query errors and I got hundreds of “MySQL server has gone away” errors, followed by hundreds of “Incorrect key file for table . Try to repair it.” I ended up repairing each table manually.

    No warning; no support; no apology (apparently no awareness). I really don’t appreciate having to play CSI to figure out why my site went down. When I find out it wasn’t my developers and it wasn’t my customers…I’m angry. My site is on box162; I’m not sure where MySQL is. I see, digging around, that my MySQL has been up 6 hours–it’s 9:47 p.m. 4/5 now, so that means it went down around 3:30 Saturday. I’ll just tell my subscribers that they can’t access the site sometimes for no reason. They’ll understand.

  8. Well, I’m feeling mollified. Before leaving my previous comment (15 min. ago), I filed a support ticket. It’s Saturday night, and Bluehost support (Gordon) just emailed to say they looked at my account and performed an unspecified fix. I didn’t expect a response before Monday. So, thanks. I don’t mind a brief outage in the interest of progress, but things got disastrous all of a sudden with this latest change.

  9. rusty_angel says:

    Here is a patch for mysql-5.0.51a, the latest at the moment. It works at my company’s servers for 4 days already.
    It fails to pass the tests (make test-bt) when you try no build rpm from src.rpm, but works just fine. (And vanilla mysql fails all the tests, too)

  10. Great work in giving this back to the community.

    We just need a build farm/repository of key patches that can be applied together and pre-built for more community members to get access,uptake,testing and feature improvement.

  11. Roman says:

    mysqld crashes with this patch on query
    create table bbb SELECT * FROM aaa;
    when table aaa does not exists

    # mysql -A
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 2817 to server version: 5.0.51b-log

    Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

    mysql> create database foobar;
    Query OK, 1 row affected (0.00 sec)

    mysql> use foobar
    Database changed
    mysql> create table bbb SELECT * FROM aaa;
    ERROR 2013 (HY000): Lost connection to MySQL server during query

  12. Roman says:

    mysqld crashes with this patch on query
    create table bbb SELECT * FROM aaa;
    when table aaa does not exists

    # mysql -A
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 2817 to server version: 5.0.51b-log

    Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

    mysql> create database foobar;
    Query OK, 1 row affected (0.00 sec)

    mysql> use foobar
    Database changed
    mysql> create table bbb SELECT * FROM aaa;
    ERROR 2013 (HY000): Lost connection to MySQL server during query

  13. Roman says:

    New version MySQL is available – 5.0.67
    Will this patch be adopted for it?

Leave a Reply