Thoughts on MySQL 5.1 and later

It’s been a while since I’ve posted anything new. In the meantime I’ve been pretty busy. Working on production systems often means that you are not running bleading edge. That’s fine but sometimes you need to look at doing the upgrade and to do that you have to do quite a few checks to see how well newer versions of the software you use will work.

I’ve been in that situation with MySQL. I have quite a few boxes most of which are 5.0 and have been working fine. MySQL-5.1 has been GA now for some time, and I have started to look at it as support for 5.0 is about to end.  I’ve already found quite a few 5.1 bugs, good enough to crash the server, but again that’s to be expected. Heavy loads and odd usage of the server means that MySQL can never be able to test all things.  Replication problems caused a few issues with the first versions of 5.1 but they all seem to have been ironed out now.  The other main headache I experienced was upgrading one specific mysql 5.0 instance. It proved quite painful. Turns out that this is mainly due to problems if you have stored procedures or triggers and the rather awkward process you need to go to upgrade in these circumstances.  Something to go into more detail at another time, but improving the upgrade procedure is something that MySQL should really look into. My conclusion is that people just don’t use triggers or stored procedures, except at least for very simple stuff. Perhaps those that do like to suffer in silence?

Those of you who do not have triggers or stored procedures of course will wonder what all the fuss is about. Other boxes I’ve upgraded just work without almost any effort at all.  Really, really easy.  So just be aware that your mileage may vary.

One thing is getting the upgrade done and seeing that things seem to work fine. For me many of the boxes are replication slaves so it is necessary to check that replication works too. The final task is to check the box under load. That is the real test of any new version of software. See how it goes under real load and compare the numbers. If you are lucky you get extra features AND better performance. If you can: load the box until it breaks. Do that also with the old version on the same hardware and then you can see how they compare. In spite of all these synthetic benchmarks you can run, realistic testing is so much more convincing.

So that is what I’ve been doing by making a simulated load from web users and applying it to a MySQL server. I was not sure what to expect and some contacts had reported worst performance on 5.1 compared to 5.0. I have been pleasantly surprised. MySQL 5.1 does perform a lot better than 5.0, and while I am not looking to use it, 5.4 is so much better still.  That is, the same hardware can take about 20-30% more load on 5.1 vs 5.0 and about the same again on 5.4 vs 5.1. I would like to use 5.4 but it is not going to be GA so I will have to wait for 5.5 or whichever version MySQL decides to push out after 5.1. Nevertheless, it is nice to know that things are better and there is more improvement to come in the future. It may be worth pointing out that these tests were done on new 16-core machines with quite a lot of memory (48 GB). It’s been known that 5.0 does not scale well past 4-way servers, and 5.1 has some limitations as the number of cores increases. Sun has been trying to fix this with 5.4 and it shows. Good stuff.

Moving to 5.1 looks promising and is worthwhile. If MySQL can get some of the 5.4 performance improvements out soonish then that will be even better. Do your experiences with the newer versions of MySQL co-incide with mine?

Tags: , , ,

13 Responses to “Thoughts on MySQL 5.1 and later”

  1. Arjen Lentz says:

    With 4+ cores, which can really be considered baseline these days, stock Sun/MySQL neither delivers something usable for MySQL 5.0 nor for 5.1.
    This is very unfortunate, but nevertheless it’s a fact.

    For 5.0 you need to use the percona or google patches, packaged by for instance ourdelta.org

    For 5.1, you can finnick with the InnoDB plugin from Oracle and disable the build-in, but several things tend to break when you try that. The solution is to disable the internal stuff during compile. MariaDB 5.1 does that and simply includes XtraDB which is the Oracle InnoDB plugin + the necessary enhancements that were also in the 5.0 percona and google patches.
    (again, packages available from ourdelta.org)

    I have personally been very impressed with the speed at which Monty and Monty Program Ab respond to bugs and other issues, and for instance porting/integrating the microslow and *_statistics patches from 5.0 to 5.1, people who have used them now regard them as essential for any MySQL deployment.

  2. Jeffrey Pugh says:

    Simon, have you checked out the InnoDB Plugin that started shipping with MySQL Server 5.1.38 – it contains the key performance improvements of 5.4, which you can access by turning off built-in InnoDB, and loading the Plugin. The Plugin is not GA yet, but we are converging on that. Let me know what your experience is.

    • sjmudd says:

      I should have mentioned this but I was using 5.1.39 (enterprise rpm), 5.4.3 (community rpm) and a quite old 5.0.68 (enterprise rpm) for comparison. I know that there’s a 1.0.4 Innodb plugin but it does not work on 5.1.39 which is unfortunate, and I still need to test to see if it works with a 1.0.4 Innodb plugin. If I can get time to stress test with this I will but I’m not sure the hardware that’s been used will be available for this shortly.

  3. Harrison says:

    Hi Simon,

    Was your look at 5.1 performance using the InnoDB plugin that is now shipped with it, or with the standard InnoDB? The InnoDB plugin included with it has a lot of the same performance enhancements that were in 5.4, so it should be pretty close in performance in most cases.

    • sjmudd says:

      I was looking at 5.1 with the built-in InnoDB plugin, not an external one and that’s also something I intend to look at later.

  4. I did a blog post at the beginning of the year with a checklist (under the video), I always point people to it, and it includes the stored procedure/trigger issue. Maybe that’s why you haven’t seen a lot on it, because it’s “known”?

    http://www.pythian.com/news/1414/new-in-mysql-51-sheeris-presentation/

    Specifically on this issue, it’s the 2nd bullet point:
    Note:
    EDITED 2/2/2009: Due to http://bugs.mysql.com/bug.php?id=30731, stored procedures, functions, triggers and views should be logically exported (as they are when using mysqldump) before the upgrade, and imported after the upgrade. From the bug: “Invoke mysqldump with a –default-character-set option that names the non-ASCII character set that was used for the definitions when the objects were originally defined.”

    ——————————
    Also, the official MySQL way to upgrade to a different release series (ie, 5.0 to 5.1) is to do a logical export (mysqldump) and import all the data. So in this case, MySQL’s official upgrade process is correct, and you’d avoid the problems you faced.

    Of course, that process isn’t particularly realistic for most folks…hence the checklist I provided :)

    • sjmudd says:

      Hi. Thanks for the pointer. I wasn’t are of your url. Your point is of course valid but I think unacceptable. The 5.1 upgrade documentation is about 10 pages if I remember correctly but it’s a list of odd things in no specific order. Most other DB vendors provide an “upgrade” check script which will tell you what needs to be done to do the upgrade, and perhaps even do it for you. That’s not the case with MySQL. When things go wrong as they did for me the error reporting is very poor, missing or imprecise so it can take a long time to work out what is wrong.

      This is something I’ve complained about on the official support channels, but I’ve yet to see if satisfactorily addressed.

      The other point is that expecting to do a mysqldump followed by a load is just not reasonable for any database larger than a perhaps 10GB. Not as part of a system running in a production environment. While there are ways to get around this it should not be necessary and an in-place upgrade should be possible in most cases, and accepted as a normal upgrade procedure and also supported. That’s not really the case at the moment.

  5. Kostja says:

    Sheeri,
    the reason we required to export and re-import stored procedures, triggers, and functions is we wanted to draw your attention to it. You should have read the manual, which explains that this is the only way to correctly identify (for the server) what was the original environment, such as character set of connection, client and database, at the time when the trigger/function/routine was created.

    Anyway, now we learned that nobody cares to restore their original character set correctly, and are refixing this
    so that the upgrade sets some reasonable defaults for you (see Bug#41659), which will likely go out in the next 5.1 GA.

    This is kind of disappointing that, even though you have experienced this issue, and we met numerous time, I’ve never learned from you that it’s critical and never got a chance to explain why the implementation actually worked the way it did.

    • sjmudd says:

      Kostja, While what you say sounds great it entirely misses the issue that many people using systems 24×7 just can not do a mysqldump followed by a load. That’s impractical. I manage a lot of slaves and our normal procedure for making new boxes or even for starting an upgrade process is to clone an active slave. The upgrade instructions don’t mention replication at all and seem to assume that you are doing an upgrade on a single isolated machine. If you have to drop triggers and stored procedures on a slave you have to be very careful to not start the server with the slave running so that missing stored procedures and triggers mean the contents of the db changes from what is expected. This step by step process while perhaps not needed by everyone is not even mentioned, so easy to overlook.

      Again if you’re doing an inline upgrade the expectation from the DBA is that the server will do as much as it can without intervention. I’ve seen a sybase 12.5 server startup from an image of an older release and it immediately starts to do the minimal upgrade steps. That’s what I’d like. Failing that I’d like to see a step by step procedure which covers almost all cases, and mentions things that currently are not explicitly in the documentation. It may be possible to skip many steps and that’s fine.
      Current documentation gives you a list of bugs which are fixed and may require actions spread in a pretty random order over 10 pages. That just leaves you confused and to “try the upgrade” and see what happens.

      So I’m sure that the intentions of the mysql developers are good but if they really want to see MySQL as a database suitable for the enterprise many of this sort of issue need addressing better than at the moment.

  6. Mark Callaghan says:

    Nice post.

    Unmodified MySQL is good on 4-core servers. It becomes inefficient after that but the InnoDB 1.0.4 plugin in MySQL 5.1.38 makes it much better. The updated InnoDB plugin in MySQL 5.1.41 makes things even better with support for group commit and changes to prevent table scans from wiping out the buffer cache. Compression can also make a big difference — http://www.facebook.com/MySQLatFacebook#/notes/ryan-mack/initial-innodb-compression-test-results/178798260932. The results there got a 4X improvement for queries from a production web workload courtesy of the InnoDB 1.0.4 plugin.

    I have a stake in this as I will be doing more with 5.1 and want others to join me, especially users who write about their experiences.

  7. Kostja says:

    I agree that the problem was not solved correctly. We should not had left the creation context overboard in the first place.
    I just think that we could have learned it and fixed the issue much quicker if there was an established link between engineers and MySQL DBAs. What I find, personally, on planetmysql, and elsewhere, is that community often takes an observer/commenter role, rather than engages in a conversation. My way of tracking problems like that is tracking reported bugs — out of a couple of thousands of open bugs @ bugs.mysql.com, I track something like a quarter. And I love to engage in conversations with users when it comes to real world usage scenarios and try to understand better why a certain bug causes a problem.
    In the example with broken stored procedures and functions, for example, have we had this conversation before, I would have given you a workaround (run UPDATE statement against .proc table and set character set columns to character_set_connection, character_set_database where they are NULL). But if you look at Bug#41569/Bug#30731, you’ll notice no comments from community — so, having to handle half a dozen or more bugs a week, I didn’t pay sufficient attention. I hope in future we can handle it better, that’s all.

    • sjmudd says:

      Perhaps there’s not enough interaction between Sun/MySQL and the community. For small scale usage that probably doesn’t matter but for larger setups that can be important. MySQL does offer enterprise support, and that works quite well so perhaps that needs to be sold to the community more.

Leave a Reply