Mysql – thesql variables are inconsistent

Back on January 21, 2009, Peter Zaitsev stated the following on mysqlperformanceblog.com

As the call for action – I would surely like someone to see if EXT3 can be fixed in this regard as it is by far the most common file system for Linux. It is also worth to investigate if something can be done on MySQL side – may be opening binlog with O_DSYNC flag if sync_binlog=1 instead of using fsync will help ? Or may be binlog pre-allocation would be good solution.

As of yet, I know of no one having touched this issue. O_DSYNC as a default is not an appealing prospect but does accommodate faster writes that are not really verified. That why there is so much hype around O_DIRECT.

I can tell you do not have the InnoDB Plugin installed. With the InnoDB Plugin, several variables should exist.

You should upgrade InnoDB in one of two ways:

• Upgrade to MySQL 5.5 (All InnoDB Plugin enhancements are part of MySQL 5.5)
• Upgrade the InnoDB Plugin

Once you have done so, you can enhance InnoDB to

• access more CPUs and Cores
• increase read and write I/O threads
• scale the I/O capacity (this is especially needed for different storage media)

Here are my past posts on the settings you can change for this:

• Sep 12, 2011 : Possible to make MySQL use more than one core?
• Sep 20, 2011 : Multi cores and MySQL Performance
• Apr 26, 2012 : Is the CPU performance relevant for a database server?
• Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian

There are several reasons why this can happen:

• Make sure you are not using the query cache on your old server and not on the new one. Execute always benchmarks with SELECT SQL_NO_CACHE. Execute the same queries several times.

• If it is a new server -the buffer pool, filesystem cache and other buffer are probably not yet settled for best performance, requiring MySQL to perform more operations for longer time. Remember that even if SSDs are fast, memory will always be faster. If the table is so small, it probably was fully on memory before. It will take some time to be fully on memory on the new server. That happens every time you restart MySQL, but it is minimized if you activate the buffer pool preload feature in 5.6 (after is has been filled, of course). You do not provide your buffer pool size (assuming you are running InnoDB) for comparing values, as that is critical and is probably going to be different between servers.

• MySQL 5.6 activates by default the performance_schema (it was off by default on 5.5). While this is a very nice feature, it may consume by default quite some amount of memory and many people -me included- have observer a 5-10% overhead in single-query execution performance. Independently of if you want to activate this feature or not, you should do the tests on equal conditions, which usually means setting performance_schema = off on the configuration file for 5.6.

• Aside from performance_schema, there are other changes on configuration by default (innodb_log_file_size, innodb_old_blocks_time, innodb_checksum_algorithm, join_buffer_size, query_cache_type, innodb buffer pool partitions, ...). While most of the changes provide better or at least the same performance, in very specific cases a regression has been found when relying on default values. A similar thing can be said about the optimizer. Make sure that you have the same configuration on both servers (beyond the same my.cnf) -there are tools for that- and that you are executing the same query plan.

• As you have also changed your hardware and OS, the same could be applied to Ubuntu configuration. A change on something like, for example, the disk scheduler can have a large impact on the performance, specially on high-end hardware.

In general, you want to be as specific as possible when asking why is MySQL slower. Profile your queries, profile your system/IO and find a symptom in order to find the cause.

Edit: I do not want to suggest this yet -as it is too soon-, but it is true that some people have reported small degradations in query performance for very simple queries on some of the newest versions. Normally, the difference is very small, and it is normal as applications become more and more complex (performance gets focused on other bottlenecks and more features under high concurrency). Some of those regressions are fixed if they are detected, but I must say that -as you mention you are using MyISAM-, the level of "support" and "care" that that engine gets in comparison like others such as InnoDB in the latest versions is not precisely great. For example, many SSD-specific improvements apply only to InnoDB.

Edit2: I do not see a significant statistical difference, aside from the fact that you are logging to the slow log on the old server and not on the new one. There is a 0.000033 seconds difference, which certainly could be due to the change between versions. The largest change is in the starting phase, with 0.000017 of difference. Some new features have been added which could contribute to that. The question is: are you going to execute isolated queries in concurrency 1 or are you going to execute with high concurrency? If on the first case, do you really care about 33 microseconds? If you only care about single threaded performance, and not high throughput, you are probably better with MySQL 4. If on the second case, make a more significant benchmark with higher concurrency, and probably you will get better performance overall in the newest versions.