1

I have a MySQL database server runnuing on Ubuntu 12.10 with 64GB RAM, 2x3 TB SATA 6 Gb/s HDD 7200 rpm and software RAID 1 and an Intel Core i7-3930K.
The database server is accessed over a local network with a dedicated 1 GBit ethernet card.
The database is around 50 GB big and is the master in a replication setup.

IMO, there are two problems that lead to slow MySQL querying:

  1. The load is always relatively high (>2) even when the db server is idle (it's not a big problem since the processor has 12 cores but it doesn't seem right)
  2. top shows that around 95% of the system is idle, <1% is used 5% is waiting (see following screenshot)

iotop --only shows that most of the disk IO is produced by jdb2/md2-8 and not mysqld - so mysqld shouldn't be the problem - but why is jdb2/md2-8 using up so many ressources?

cat /proc/mdstat outputs the following:

My questions are:
What causes the slow MySQL performance?
Is it the iowait? What causes the iowait?
Is it the RAID 1 - Is it the used harddisk?

Any help is greatly appreciated!

2

You are running a log-heavy database on just about the slowest disks out there. The reason you're seeing jdb2 on such high iowaits is because it's having a hard time keeping up writing the ext4 journals to disk

Either change disks to something that can keep up (SSD's come to mind, allthough regular SAS disks with a battery cache would work) and/or start tuning stuff. There are plenty of documentation out there when it comes to filesystem tuning in SQL environments.

A good starting point is here: http://dev.mysql.com/doc/refman/5.0/en/disk-issues.html

  • would hdparm -m 16 -d 1 work for SSD as well? – horen Feb 5 '13 at 10:45
0

I experienced this issue after upgrading to MySQL 5.7.

Setting "sync_binlog=0" fixed it for me. MySQL had changed the default from 0 to 1 in 5.7.

  • Not sure that enabling "throw all my transactions away in the event of a crash" is a great thing to be advocating, in the general case. – womble Jul 15 '17 at 23:22

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.