Recently we've ran some (security) updates on our servers and rebooted the machines. Our development server did not came online anymore due to a problem with the GPU (integrated in CPU). We've replaced (and upgraded) the hardware on that machine and also converted the originally bare-metal machine to a VM (KVM) so we can upgrade it from CentOS5 -> CentOS6 later.
However the machine itself was NOT reinstalled, all data was secured and copied 1:1 as a new image the (new) VM could used to boot.
The problem we have now is MySQL performance. It seems to be mostly related to really simple CREATE TABLE statements. We cannot find if this issue is related to the MySQL upgrade to 5.5.50 or the move to a VM.
The problem:
mysql-slow-querylog
# Time: 160610 13:55:50
# User@Host: unittest[unittest] @ localhost [127.0.0.1]
# Query_time: 7.954247 Lock_time: 0.000049 Rows_sent: 0 Rows_examined: 0
use unittest_api_575aaabd9e502;
SET timestamp=1465559750;
-- --------------------------------------------------------
--
-- Table structure for table `customer`
--
CREATE TABLE IF NOT EXISTS `customer` (
`customer_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`crm_id` varchar(255) DEFAULT NULL,
PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;
(our unittest suite creates a DB structure and this is a table that is created)
You'll notice it took almost 8 seconds to create this table! (Our test-suite now takes 2 minutes instead of 30 seconds)
I also ran this query with profiling:
DROP TABLE IF EXISTS `customer`;
set profiling =1;
CREATE TABLE IF NOT EXISTS `customer` (
`customer_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`crm_id` varchar(255) DEFAULT NULL,
PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=25;
set profiling =0;
show profile all for query 1;
And this is the output: The results vary but I do see a lot of high (>1 sec) values), to my opinion this should never take more than one second on a server with little load.
I've tried to make some changes to the my.conf however I did not yet managed to increase performance. I've uploaded our my.cnf for reference.
Some details on the server:
- MySQL 5.5.50
- CentOS 5.11
Host:
- i7 6300
- 32GB RAM
- 2x 1TB harddisk
VM:
- 4 cores
- 16GB RAM
I cannot believe this is only from bare-metal => VM. Can anyone point us in the right direction? If more information is needed let me know.
Additional information:
- VM Config:
- CPU Load: low (~5%)
- iostat output during execution: http://pastebin.com/AHkby04X
virsh dumpxml <vmname>
?