1

MySQL seems to be writing rows OK (75,000 rows/min) until when the MYD file reaches 1.8 GB, and then the insert rate drops immensly (10,000 rows/min), and 1 hour later it's even slower (6k rows /min).

The engine is MyISAM, there are no indexes, no PRIMARY key. The machine has 16GB RAM and 8 Cores.

The MySQL configuration is:

key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

At the time of the 'faster' imports, the CPU was > 100% load, and the RAM was 50% in use. Now the CPU is hardly reaching 3% load, and the RAM 1%.

The disk seems to be operating OK, hdparm says:

/dev/vda1:
 Timing cached reads:   14780 MB in  2.00 seconds = 7399.35 MB/sec
 Timing buffered disk reads: 1148 MB in  3.00 seconds = 382.36 MB/sec

And a file writing test says:

dd if=/dev/zero of=/tmp/output bs=8k count=100k; rm -f /tmp/output

102400+0 records in
102400+0 records out
838860800 bytes (839 MB) copied, 2.55773 s, 328 MB/s

I don't think it's the OS, but I'm not sure how to probe that.

In terms of MySQL idle queries, there are none, SHOW PROCESSLIST returns:

+-------+------+-----------------+--------+---------+------+-------+------------------+
| Id    | User | Host            | db     | Command | Time | State | Info             |
+-------+------+-----------------+--------+---------+------+-------+------------------+
| 18548 | .... | .........:57742 | dw_raw | Query   |    0 | init  | show processlist |
+-------+------+-----------------+--------+---------+------+-------+------------------+

Complexity of the queries: SIMPLE; the insert queries are pretty basic, EXPLAIN shows nothing unusual.

Other ways to 'attack' the problem:

  • Emptied the table and resumed the writing to see if the table size could be the problem: the writing is still slow, no change in writing speed.
  • Checked if the app that writes the insert is the bottleneck: seems to be writing fine to a file with good writing speed.

I am out of ideas at this stage, any feedback on what I could be doing next?

Here's a graph of how the CPU/Disk usage decreased:

enter image description here

Many thanks, Vlad

  • Virtualised or real box? – Philᵀᴹ Mar 22 '16 at 20:36
  • how about buffer cache? – Alex Mar 22 '16 at 22:21
  • @Phil Virtualised – user75338 Mar 23 '16 at 13:57
  • @Alex researching this now – user75338 Mar 23 '16 at 13:57
  • If it's virtualised, is the storage sparsely provisioned? If so, having to extend the .vmdk files may explain the performance drop-off ? – Philᵀᴹ Mar 23 '16 at 13:58

Your Answer

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