September 21, 2014

MySQL Server Upgrade

Today I’ve upgraded MySQL Server on the host running MySQL Performance Blog. MySQL 4.1.12 was running here for well over a year before that.

Why Have not I upgraded before ? Well because it just worked fine. Yes I know there were some security fixes but I have dedicated server with remote MySQL access closed by firewall and only trusted people having local access so I was not worried too much about it.

Why did I upgrade now ? Because new forum application was exposing one of the bugs in MySQL 4.1.12 so there was a need for upgrade.

Which Version did I upgrade to ? I Upgraded to MySQL 4.1.21. Why Did not I go with MySQL 5.0 ? There are three reasons. First there are number of changes in 5.0 some of which are incompatible with MySQL 4.1, for example regarding join syntax. I can’t be 100% sure none of my applications will be affected and I see no reasons to spend time testing it carefully or downgrading if problem finally discovered a week later in some rare circumstances. Not to mention there is the chance new application I might be installing does not yet works with MySQL 5.0.

Second – I know my applications designed to work with MySQL 4.1, they avoid queries which MySQL 4.1 does not optimize well and so MySQL 5.0 will likely reduce performance. It is probably just 5-10% for most queries but this site runs on old Celeron box which does not have too much CPU cycles to waste.

Third – My sense of MySQL 5.0 stability is still not at MySQL 4.1 level. This is not exactly problem in this case – this site uses rather simple workload so MySQL 5.0 probably would do as well as MySQL 4.1 which even in its ancient 4.1.12 version never crashed on this site. However new applications which bring new workloads have higher chance of problems on MySQL 5.0 than on 4.1

This is upgrade strategy I follow for this server which has mix of various third party PHP/MySQL applications. In different situations upgrade strategies may be different. The main point I’m making is – you do not have to rush and use latest MySQL version neither it always makes sense to upgrade to each minor release if there are no fixes which affect you.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Apachez says:

    Peter, you have probably already noticed this but in case you havent…

    I have noticed that this site takes an additional seconds before it replies on my http requests. Specially for the first visit.

    Perhaps some e-accelerator (or whatever is being used to boost the php on this site) is broken (it kind of feels the same way :P) ?

  2. peter says:

    Apachez,

    It is just pretty old server which does a lot of things besides this web site.
    There is eaccelerator installed so it should be OK. I guess I just have pretty complicated first page, which can be over 150KB in size, especially with code formating module which seems to be rather slow.

    Yes something needs to be done with it sometime :)

  3. Apachez says:

    Hmm I see now that I was a bit unclear…

    I meant that 1-2 weeks ago this site had much faster response specially for the first page…

  4. peter says:

    I’ll tell you sad story.

    This host also runs nagios which monitors bunch of servers in different data centers with many services on each. Just as you was browsing the site there was large packet loss to one of the data center which made nagios services randomly going down and recovering. This generated email storm which was processed by spamasassin which loads server quite seriously. I suspect this was the reason.

    I should probably setup nagios forwarders on each of data centers and learn spamasassin to skip local system mail but this time it was as it was :)

    I hope it is better now ?

  5. Apachez says:

    I still takes 1 or 2 seconds before the servers starts to send data but I think it was even worse earlier… or maybe its just a psychological effect :P

  6. peter says:

    Yes. I guess this is because I added few large articles with many SQL queries and highlighting module seems to be slow. Server buffers full page before sending and there is gzip compression enabled for clients which can do it.

    I should find different module and I should have this page cached rather than generated each time. It just requires some time.

  7. Apachez says:
  8. peter says:

    Could be file cache or APC. Any would work much better.

    The problem is actually finding a proper way to invalidate the cache – when I want it to expire ? I want articles to appear at once so it should be invalidator plus I have calendar so date change also should invalidate it.

    It also can be mixed with e-tag to avoid page transfer if it was not changed.

    I will also check first if there are any wordpress plugings which do what I want to do.

    Also may be hiding much of the post under [Read More] will be helpful to save traffic.

Speak Your Mind

*