Not the answer you need?
Register and ask your own question!

[MySQL] Huge performance drop after upgrade from 5.1 to 5.5

HotkeyHotkey EntrantCurrent User Role Participant
Hi There!

I've got a problem after a server upgrade from MySQL 5.1 to 5.5 with the performance of adding indizes or creating tables.
I've got multiple production servers with different specs running a Webapplication with a MySQL database.
The servers are "normal" PC's (Quad Core, 16GB Ram, SSD(some still HDD)) on Debian Linux.

After upgrading one of the servers to 5.5 i recognized a huge performance drop which i was able to narrow down to CREATE and ALTER ... ADD INDEX statements.

Example (table contained 1 row at that time):

ALTER TABLE `rb` ADD INDEX `linkIdx` (`link_id`),ADD INDEX `rbIdx` (`rb_id`) ;</pre>

This statement took 1ms with 5.1 and now takes 300ms.

I then created the following queries for testing purpose, and ran the query on different servers 10 times. (as the aberration of the values was very small, i guess the 10 times will be enough for now).
Test queries:

SET profiling = 1;DROP TABLE test;CREATE TEMPORARY TABLE `test` ( `rb_id` MEDIUMINT(8) UNSIGNED DEFAULT NULL, `link_id` MEDIUMINT(8) UNSIGNED NOT NULL) ENGINE=INNODB DEFAULT CHARSET=latin1;INSERT INTO test (rb_id,link_id) VALUES (1234,5678);ALTER TABLE test ADD INDEX `linkIdx` (`link_id`),ADD INDEX `rbIdx` (`rb_id`);SET profiling = 0;SHOW PROFILES;</pre>


Here are the results of two servers. Server 1 is a testing machine with 5.5.18 and Server 2 an online system.

Query_ID Duration Query


31 0.07105000 CREATE TEMPORARY TABLE `test` ( `rb_id` MEDIUMINT(8) UNSIGNED DEFAULT NULL, `link_id` MEDIUMINT(8) UNSIGNED NOT NULL ) ENGINE=INNODB DEFAULT CHARSET=latin1 32 0.00025700 INSERT INTO test (rb_id,link_id) VALUES (1234,5678) 33 0.01928875 ALTER TABLE test ADD INDEX `linkIdx` (`link_id`),ADD INDEX `rbIdx` (`rb_id`) </pre>

Server 2 (online, just migrated from 5.1 to 5.5):

Query_ID Duration Query


312 0.18535400 CREATE TEMPORARY TABLE `test` ( `rb_id` MEDIUMINT(8) UNSIGNED DEFAULT NULL, `link_id` MEDIUMINT(8) UNSIGNED NOT NULL ) ENGINE=INNODB DEFAULT CHARSET=latin1 313 0.00030325 INSERT INTO test (rb_id,link_id) VALUES (1234,5678) 314 0.29724500 ALTER TABLE test ADD INDEX `linkIdx` (`link_id`),ADD INDEX `rbIdx` (`rb_id`) </pre>


As you can see the difference is very huge. The results of the online system are not noticably affected by server load (tested also at high usage time as well as at night). Both computers are using HDD's where the online server has a faster CPU and more Memory as the testing server.

To make it even worse here's the result of a third online system which has been setup for two month. It uses a SSD and is even more powerfull then the first online system:


Query_ID Duration Query


2 0.14713750 CREATE TEMPORARY TABLE `test` ( `rb_id` MEDIUMINT(8) UNSIGNED DEFAULT NULL, `link_id` MEDIUMINT(8) UNSIGNED NOT NULL ) ENGINE=INNODB DEFAULT CHARSET=latin1 3 0.00018025 INSERT INTO test (rb_id,link_id) VALUES (1234,5678) 4 0.18428525 ALTER TABLE test ADD INDEX `linkIdx` (`link_id`),ADD INDEX `rbIdx` (`rb_id`) </pre>


Any idea what might causes this behaviour? The MySQL variables only differ in terms of buffer sizes to reflect the different amount of available memory (8,16,24GB) (max_tmp_table, sortbuffer, innodb buffer, max connections and so on). I could provide these values if they are of interest.

Many thanks in advance for reading until here :)
Would be great if someone could help me with this issue!

Comments

  • gmousegmouse Mod Squad Inactive User Role Leader
    What's the point of creating so many temporary innodb tables?

    It may help to disable fast index creation ( http://www.percona.com/doc/percona-server/5.5/management/inn odb_fast_index_creation.html ). Fast index creation is much faster for filled tables, but I don't know its behaviour for empty tables.

    Could you check disk utilization? It may well be possible that fsync's take a long time and cause this slowness. Do you use a raid controller with bbu cache?
  • HotkeyHotkey Entrant Current User Role Participant
    The servers default storage engine is innodb, so the temporary tables are also innodb. I added the Engine manually for the test in order to reflect this scenario. The temporary tables are created for certain calculation procedures.

    About the fast index creation: How can i check if it's enabled or not? If it's a new Feature of MySQL 5.5: whats the default setting after migrating from 5.1?

    About disk utilization: I have to ask the provider for this, good point. The online systems have a raid controler with bbu cache.

    Nevertheless: the hardware didn't changed while migrating from 5.1 to 5.5
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.