Unexplained Read IO

Question

I have been wrestling with a problem for the last week or so and I am hoping someone can help point me in the right direction.

We have a mysql database (version 5.7.19) running on an NFS volume - the table we are having trouble with is using myisam storage engine.

When running normally it runs at 1000 Inserts/sec 30 updates/sec 0 selects/sec 0 deletes/sec

On volume during this time we see about 1.5 - 2 K IOPS\sec - with about 3 MBps throughput - which is mostly writes

Then some days it suddenly performs slower -and the application inserting data backs up into a queue and we only see below

450 Inserts/sec 27 updates/sec 0 selects/sec 0 deletes/sec

The mystery - the volume during this time shows IOPS at 2 - 2.5k , but the throughput is up to 11 MBps - and 8 - 9 MBps of that is reads. I confirmed with IOtop that it its mysql threads that are performing the reads.

So - I guess my question is - how can I determine what exactly inside of mysql is causing these reads ? I have reviewed the logs and the activity is nearly exactly the same when we have the problem vs when we do not have the problem, its just that the inserts are slower and for some reason mysql is reading 10x the amount of data, even though there are no selects at all - and no more updates than usual , and process list does not show any activity other than the inserts and a bunch of sleeping connections.

There is a trigger on the insert , but its set 'before insert' and its just doing a character replace in one field , and again its the same type of statement when we do not have a problem .

I looked through the performance tables, and while it does show that the tables being read are the ones that are being inserted into , I have no idea why ( no selects , no activity in processlist to explain it ). Also the data being read from all the tables adds up to less than 1.5 GB - while the reads stay at 9 MBps for nearly the entire day - so it should be closer to 250 GB really.

I am sure I am missing something obvious and hope maybe someone can point me in the right direction :)

thanks!

Answer 1

Why to switch engines

MyISAM does not allow much concurrency. Any writes to a table "lock" the entire table, blocking all other writes and reads to that same table.

Furthermore, sometimes a chain of queries from different connections can even cause more blockage than explained above.

Put another way, a write of any kind to a MyISAM table stalls all other accesses until the write finishes.

I doubt if that TRIGGER is causing trouble (since it is not touching other tables).

Switching to InnoDB is very likely to lead to fewer blockages like what you are seeing. InnoDB even allows different rows of the same table to be written simultaneously (with some caveats). And reads are usually not blocked by writes.

I/O Usage by MyISAM

Back to your Question...

MyISAM uses two caching methods:

MyISAM index blocks (1KB each) are cached in the key_buffer. Its size is configurable via key_buffer_size. The recommended setting is about 20% of available RAM. Usually, this is adequate for avoiding reads of index blocks.

MyISAM data blocks (size controlled by the OS) are cached in the leftover RAM. The OS is in control of these blocks.

(InnoDB caches differently; changes are needed in my.cnf if you switch engines.)

Without knowing how much RAM you have, what your settings are, how big your dataset is, and how big the "working set size" is, I can't be more specific.

Further Analysis

See http://mysql.rjweb.org/doc.php/mysql_analysis#tuning for instructions on providing the Global Status and Variables for investigating further -- it will include some analysis of the effectiveness of the key_buffer. It will make some stabs at what is causing I/O (since the last restart).