2

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: slow_query 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:

  • Can you show the output of virsh dumpxml <vmname>? – shodanshok Jun 10 '16 at 12:53
  • @shodanshok Here you go: pastebin.com/0tfYiaE2 – Matthijs Jun 10 '16 at 13:02
  • did you checked iowait and cpu load during testing? – Nikita Kipriyanov Jun 10 '16 at 13:39
  • 1
    "2 x 1TB harddisk" - slow, terrible IOPS, unfit to run database virtualization (or any database) with any performance requirements. – TomTom Jun 10 '16 at 13:41
  • @TomTom Yes harddisks are slow compare to SSD's, however this is a development server. We've never had any issues (like this) before. The database is really light used. I'm asking here on the difference between pre HW failure and after HW replacement and VM conversion. Yes the performance won't be as 'good' as when it was bare-metal but it should not be so bad that a simple CREATE TABLE statements takes 8 seconds! – Matthijs Jun 10 '16 at 14:05
2

You did not specified the vdisk cache type, so libvirt is assuming the most safest cache scheme: directsync, which imply that all writes are immediately synched to the physical disk.

Such restrictive cache type is generally overkill for modern, cache-aware application, which uses write barriers by themselves to ensure important writes are synched to disk.

Do the following:

  • shutdown your machine
  • open its configuration via virt-manager
  • select virtio disk 1 and, in the right pane, click advanced, then performance options
  • set cache type to writeback
  • finally, restart your virtual machine.

The VM should be much faster now. However, as you are using a quite old OS (CentOS 5.x), please be sure to enable write barriers inside the guest OS. To do that, you should mount your guest's filesystem with the barrier=1 mount option (eg: passing it via /etc/fstab).

For some other information about caching and barriers, give a look here

  • Thank you for your answer. I cannot do this right now as there are people using the server but I will let you know if it worked! – Matthijs Jun 10 '16 at 13:16
  • Don't forget about enabling barriers inside the guest OS via the barrier=1 mount option - it is very important to prevent data loss in case of system crash. – shodanshok Jun 10 '16 at 13:26
  • enabling barriers does not prevent against data loss, it ensures that filesystem maintains integrity in a crash - not the same thing at all. And trying to ensure that a single node is as reliable as possible is rarely a good solution to HA. – symcbean Jun 10 '16 at 16:13
  • Journaled applications (as MySQL) use barriers to prevent partial writes and unexpected data loss: they guarantee that if a write is acknowledged to the user, data really are on stable storage (directly in the main filesystem or in the journal log). Fully journaled filesystem (eg: ext4 with data=journaled mount option) are even safer, but they pay a significant price in performance. – shodanshok Jun 10 '16 at 16:25
  • I've make the change and indeed it is a (huge) improvement. For example our unittest suite that took 2+ minutes will now run in ~50 seconds. However I'm not there yet. In my local vagrant box it runs in 30 seconds (also from HDD). When I look at the mysql processlist it seems (again) that CREATE TABLE statements are slow. If you want I can create some code that does xx CREATE TABLES on our test server, a (busy) production server (also VM & HDD) and my local vagrant box? Would that be useful? – Matthijs Jun 10 '16 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.