If I have run into a mySQL bug, I look to see if that bug is fixed by searching the mySQL bug database.
If I've notice a performance bottleneck, I look to see if the performance bottleneck has been fixed by searching the same database.
I will NOT upgrade to the latest and greatest version of mySQL (5.4) I stay within my branch (5.0).
These are my three general motivations that drive my upgrade decisions. Anytime I upgrade I also make a list of things that might affect my environment for the stuff I use.
- Here are my steps:
- Check the change log
- Ignore all the NDB changes... I don't use it and that's the majority of fixes. This is also, why I do not use it.
- List the changes that will affect the production environment
- Deploy the version that I picked on a few servers running my original config
- Do data corruption tests (make sure my checksum scripts return the same data)
- Verify that the problem I'm trying to fix is fixed
- Deploy to more boxes
- Let the new server bake for a period of no less than a week
- Deploy everyplace
So now, I'm upgrading from 5.0.56 to 5.0.86. What I'm trying to fix is mysql memory overhead at high levels of ram.
For instance, I have a slew of 48GB boxes. I set the bufferpool to 40GB; the OS uses 1 GB of memory (roughly) leaving an overhead of 7GB for the system cache and various spikes of sort buffers. Over a period, I see that mySQL will consume and hold onto 47GB of memory for an unknown reason even with some tight my.cnf settings. (I'm certain they are tight since I know what each buffer does). Therefore, testing some later versions of mySQL we found that these later versions do not grow past the settings defined yet performs the same.
Next, since I decided that upgrading is a good solution, now it’s time to list all the changes that fixes things.
- 5.0.58 - INNODB performance fix
- 5.0.60 - various problems that I should be affected by but havn't noticed so it’s fair to assume that said problems were introduced after my build.
- 5.0.62 - nothing major noticed the sp releases that's why I wait.
- 5.0.64 - nothing major
- 5.0.66 - security fixes and fixes to fix the bugs introduced from this build.
- 5.0.67 - two INNODB performance fixes and crash bug fixes.
- 5.0.68 - changes show status and fixes an innodb crash bug.
- 5.0.70 - fix another INNODB crash bug and security fixes
- 5.0.72 - more general bug fixes
- 5.0.74 - more stuff I don't care about
- 5.0.75 - stuff given to Enterprise users now in community
- 5.8.76 - more bug fixes that I do not need
- 5.0.78 - more bug fixes I do not care about (run MS Access on windows not mySQL)
- 5.0.80 - problem with error messages for concurrency limits that caused an assert failure
- 5.0.82 - Fixes to fix fixes for this build.
- 5.0.83 more minor fixes that I don't seem to have a problem from
- 5.0.84- more bug fixes for INNODB and latches
- 5.0.85 - looks like windows fixes
- 5.0.86 - fixes that I'm not having problems with
Therefore, overall, upgrading should give me a boost in performance. My own internal testing sees some tighter memory usage, even though this is not fixed explicitly, the product has matured overall so I can account for the reduction in memory to that.
3 comments:
Once I learned to ignore the suggestion to upgrade, I became much happier with MySQL support. That have a lot of expertise to share. For example, they might have suggested that http://bugs.mysql.com/bug.php?id=27732 is the cause of your memory leak. It is fixed in MySQL 5.0.60. Nobody is promoting upgrades to MySQL 5.4. It isn't GA.
it isn't fair to say that upgrade is always default answer for support engineers. at least back when I was doing support engineering, suggestion to upgrade would be given with certain bugs and behavior changes in mind. I think you owe an apology.
increased memory use has been blogged about by both me and Mark, thats the cost of having additional control structures for every buffer pool block. increase buffer pool page and overhead will decrease, there're also few other possible changes, like smaller lock structures.
if you need performance and you are running old 5.0 version, you seriously need to change your attitudes, when people tell you to upgrade.
cheers, Domas, proud 4.0 user :)
ooh so touchy Domas. Support Engineers in every case from Intel ICC compiler to Oracle products always say upgrade to the latest version from my personal experience.
Do all support engineers do this? Of course not. Some do the due diligence others do not.
So, for making this post sound like ALL support engineers do this I apologize. But I must state, that there has not been a case in recent years that I needed any support engineers for. Which I account to investigating and understanding the problem 1st-which is the goal of this article.
Post a Comment