4

I recently installed MySQL 5.1 on CentOS 6.2 and had a performance gain over the MySQL 4.1 we are running. So I upgrade the MySQL 5.1 to MySQL 5.5 to see if there was even more of a gain but it actually ran half the speed of the MySQL 5.1 setup.

The test I ran was on a table with 2.3M records with blob notes.

5.1: 57.5899 sec

5.5: 96.3821 sec

What is really interesting is if I pull 100,000 records the 5.5 beats the 5.1 but anything after ends up making the 5.5 load like in spikes; speeds up and slows down as loading which is where the extra seconds seem to be coming from.

Any thoughts to why this is? Same my.cnf for both 5.1 and 5.5

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
bind-address = xxx.xxx.xxx.xxx

#This option makes InnoDB to store each created table into its own .ibd file.
innodb_file_per_table=1
max_allowed_packet=900M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
3
  • are you using the same db engine? InnoDB is the default in 5.5.
    – Jure1873
    Feb 21, 2012 at 22:18
  • @Jure1873: I don't see how that change can affect anything. The DB files on disk won't have changed, so on upgrading, the server would have used the same engine to access them. This change won't make the server try to read a MyISAM table as InnoDB, for example. You have to dump and re-load to change storage engines. Feb 22, 2012 at 15:48
  • Yes, if you didn't do a dump and re-load then it wouldn't matter, or it would only for new tables if you don't specify the engine. I suspect that some default variables got changed, so what Warren Young suggested would probably help.
    – Jure1873
    Feb 22, 2012 at 16:24

2 Answers 2

7

There are times when MySQL 5.1 may outperform MySQL 5.5 under certain circumstances.

Percona performed a bake-off among multiple releases of MySQL

  • MySQL 4.1
  • MySQL 5.0
  • MySQL 5.1 (with built-in InnoDB)
  • MySQL 5.1 with InnoDB-plugin
  • MySQL 5.5
  • MySQL 5.6

All tests were performed with MySQL unconfigured (In other words, no my.cnf was made). The results?

  • MySQL 4.1 performs the best with InnoDB single-threaded
  • MySQL 5.1 with InnoDB plug-in scales on multiple cores better than 5.1 built-in InnoDB, 5.5 and 5.6

If you want newer versions of MySQL to perform better, you must tune for it. In fact, I described in the DBA StackExchange the idea of performing a MySQL Bakeoff.

What do I mean tune for it?

In MySQL 5.5, there are new InnoDB options for utilizing more dedicated read threads, write threads, and overall I/O capacity. This can engage more CPUs in multicore servers. Left unconfigured, MySQL 5.5 would operate on the same level playing field, in most cases, as older versions of MySQL. Sometimes, it could perform worse.

Think in terms of the following:

  • Garbage in, garbage out
  • You get what you pay for
  • Two words : Due diligence

Bottom Line : MySQL 5.5 and Percona Server must be configured for the desired performance enhancements.

Keep this in mind

What is a Lamborghini ?

1

The problems you're seeing could be caused by dozens of different root causes. You definitely should not guess; you certainly need to measure and diagnose carefully. If you gather enough information, the true cause will be evident, and the solution will be obvious (provided that you also understand enough about the server's internals to know what you're looking at). If you guess and try to do things such as reconfiguring the server, my experience shows that you can make the problem much worse, or cause other problems, and you'll never really know whether any specific change helped or not.

I would suggest using the pt-stalk tool from Percona Toolkit to capture a set of diagnostic data from the server when one of the spikes of slowness happens, and ditto for when it's running more quickly. There should almost certainly be enough information there to understand what's happening. If you are not comfortable making a diagnosis from it, any competent MySQL support provider should have no problem if you tarball the samples from pt-stalk and send them over.

I don't mean to be too repetitive or insistent, but again please don't use trial-and-error on this one.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .