Mysql – High load and iowait on MySQL database server leads to slow MySQL INSERTS

hard driveMySQLperformance

I have a MySQL database server runnuing on Ubuntu 12.10 with 64GB RAM, 2×3 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:

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)
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!

Best Answer

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

Related Solutions

MySQL DB causing high IO running Zabbix server

innodb_buffer_pool_size > 1G, because your dataset size is 1,8GB.

To decrease number of reads you need to increase innodb_buffer_pool_size. To decrease number of writes you need to edit your zabbix templates (disabling some unnecessary items like free inodes, increase intervals between checks).

You have Reads / Writes ratio at 57% / 43%, so enabling Query Cache will not help (it may make things worse, because writes to tables invalidates cache).

Think about increasing tmp_table_size and max_heap_table_size to avoid creating tmp tables on disk (13% of tmp tables). Temporary tables are in MB? it is count? If it is counter, its too high.

Decrease number of connection to 50 (your highest number was 33).

innodb_support_xa = false
innodb_buffer_pool_size = 256M # It depends how many memory is available to MySQL, more is better.
innodb_flush_log_at_trx_commit = 0 # disable writing to logs on every commit and disable fsync on each write
innodb_max_dirty_pages_pct = 90 # avoid flushing dirty pages to disk
innodb_flush_method = O_DIRECT # direct access to disk without OS cache
thread_cache_size = 4
query_cache_size = 0
table_cache = 80? # a little more than number_of_tables_in_zabbix_database

Usefull link about InnoDB optimization.

Mysql server high trafic makes websites really slow or unable to load

Some nice pictures, but there's a lot of info missing from your post.

What's the max connections? How big is the database? What engine? What's the key buffer size? How much free memory does the DBMS have?

full table scans, could this maybe be the problem?

Could be - not enough information to say. If the query needs to read more than approx a tenth of the data, the a full table scan will be faster than an index lookup.

queries, one for a menu that outputs around 1000 rows

You have menus on your web pages with 1000 entries? Then most of your problems are not in the database. Your application is fetching FAR TOO MUCH DATA from the database. If it's then sending this information across internet then performance will be appalling. If it's not sending out all the data it's fetching then why aren't you doing the filtering on the DBMS.

Every table has a primary index and thats it

Maybe you should think about aligning your schema with your queries (i.e. add relevant indexes) however think the biggest problem you've got is that you're fetching data from the DBMS which just isn't getting used.

OTOH do you mean that when you analyse the queries using the same tool as in your screenshot it tells you it's processing thousands of rows? In that case fixing the indexing will have a huge impact.

Related Topic