MySQL, many writes waiting on disk i/o access

multi-threadMySQL

I'm seeing a disk i/o problem with a MySQL DB under the following scenario:

  1. 24 CPU's total on the computer
  2. 22 threads writing simultaneously to separate tables
  3. each write is done using INSERT INTO … VALUES …, where there can be ~5000 rows being inserted in a single insert statement
  4. each row's input VALUES are all integers, looking something like this, for example: (13953,195,-149)
  5. tables are MyISAM

The problem is that when all 22 threads are each busy writing the large bulk insert operation, MySQL reports that they are all stuck in state 'closing tables', which equates to flushing the data to disk.

My first analysis found that jdb2 process was most likely causing a bottleneck since the MySQL data directory was under the / partition and journaling was, of course, turned on.

I read that for MySQL data directories this isn't necessary, so I repartitioned the disk, moved the MySQL data directory over to the new partition, turned off journaling for this new partition, and restarted the system.

This resulted in an improvement where the 22 threads were able to complete with no i/o wait and no getting stuck on closing tables, but only for a while, when all threads again end up waiting on 'closing tables'.

mysqladmin processlist, for example, has this for output (cut to save space), where the bottleneck is quite obvious:
processlist output

And then when I check to which process is holding up disk i/o, i find it to be:
[kworker/u65:3]

Which is a kernel process worker. google helped me little in identifying what this is.

So, my questions:

  1. What is this kernel worker thread causing a disk i/o bottleneck, even when journaling is turned off?
  2. If I were to switch the table type to InnoDB, and change the disk partition to raw (with journaling also off, of course), would this take kernel threads out of the picture?
  3. And any other, general, recommendations to remove this bottleneck?

some additional info, per rick james' request:

1) show create table output:

'CREATE TABLE `psd1` (
  `psd_fk` int(10) unsigned NOT NULL,
  `perbin_fk` mediumint(8) unsigned NOT NULL,
  `power` smallint(6) NOT NULL,
  KEY `psd_fk` (`psd_fk`),
  KEY `perpow` (`perbin_fk`,`power`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs'

2) tables are not bigger than RAM, but there are thousands of tables, possibly hundreds of Mb's each.
3) RAM => 32Gb.
4) rows are ordered by 1st column: which is a foreign key (just my own, not a formal MySQL foreign key) to a primary key of another table.

thanks…

Best Answer

  • Switch to InnoDB. Heed the advice here .
  • Change a few things in my.cnf because of the Engine change.
  • Don't do anything special with disk.

InnoDB works with 16KB chunks, not 4KB, like MyISAM for data; 16KB vs 1KB for indexes.

If you still have trouble,... What indexes are on your table? What order are the rows in? Are the tables bigger than RAM? How much RAM do you have? Let's see SHOW CREATE TABLE.