2

I'm pasting section of MySQL Tuner result regarding MyISAM Metrics.

[OK] Currently running supported MySQL version 5.5.57-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture

-------- Performance Metrics -----------------------------------------------------
[--] Up for: 25d 1h 40m 20s (6M q [3.149 qps], 170K conn, TX: 15G, RX: 1G)
[--] Reads / Writes: 90% / 10%

-------- MyISAM Metrics ----------------------------------------------------------
[!!] Key buffer used: 20.1% (80M used / 402M cache)
[OK] Key buffer size / total MyISAM indexes: 384.0M/31.6M
[OK] Read Key buffer hit rate: 99.9% (7M cached / 4K reads)
[!!] Write Key buffer hit rate: 1.2% (2M cached / 25K writes)

I'm looking for way to improve or optimize the Write Key buffer hit rate. Or why is it so bad?

I don't know which server variables may play role in this result, so please let me know what variables should I paste here or if you need any specific details.

Thanks for help!

UPDATE 2017-08-11 13:56 CET

I did some more tests based on recommendations from @RolandoMySQLDBA and it really seems I should not worry about this metric. After I lowered the key_buffer_size, the Write buffer key hit rate improved directly. However, I couldn't measure any improvement in speed of reads. I assume it's because there was not enough memory for cache, so almost all writes went through. I still don't really understand what is the good or bad thing about Write buffer key hit rate, but it seems to be related to optimal size of key buffer based on the data and index in database.

  • What is the Uptime? 25K writes per minute would be a problem; 25K writes per month would not. – Rick James Aug 13 '17 at 19:08
  • Thanks, I added metrics about uptime and read/write ratio. I was checking another machine, with same config and uptime and it had 84K writes. Still no problem? – lp1051 Aug 15 '17 at 6:28
  • That was 25K writes in 25d -- a trivial 1K/day; less than 1/minute. But that is for MyISAM. Are you using InnoDB for your tables? – Rick James Aug 15 '17 at 21:00
  • Yes, it's just one MyISAM table for generating search texts for full-text search, the rest is InnoDB – lp1051 Aug 16 '17 at 8:29
1

You are still using MyISAM ??? OUCH !!!

All kidding aside, you need two status variables:

  • Key_write_requests : The number of requests to write a key block to the MyISAM key cache
  • Key_writes : The number of physical writes of a key block from the MyISAM key cache to disk

You want the ratio Key_writes/Key_write_requests to be as close to 1 (100%) as possible. I wrote a nice set of queries you can run to see such ratios. You can find it in my Jun 17, 2013 answer to the post MyISAM key buffer

Perhaps just running FLUSH TABLES; will clear away whatever index changes are lingering. If your system does not do heavy writes, this is probably all you need.

I recommend converting everything you have to InnoDB so your database can survive a crash. After all, you do not want any changes to an index to linger.

Take a quick look at the InnoDB Plumbing (picture from Percona CTO Vadim Tkachenko)

InnoDB Plumbing

See the InnoDB Buffer Pool on the Left ? It has an Insert Section responsible for index changes. InnoDB would automatically flush index changes for you. No such mechanism exists for MyISAM. So, please convert to InnoDB ASAP.

UPDATE 2017-08-07 15:25 EDT

If you must go on using MyISAM, you should look into using Dedicated MyISAM Key Caches. You can create a key buffer for specific set of MyISAM tables. In your case, you would create a dedicated key cache for the one table with the FULLTEXT indexes. You would have to judge how big or how small to make the cache.

Suppose your table with the FULLTEXT indexes is mydb.myfull.

You could then run this to find the proper size rounded up to the nearest MB

SELECT index_length,((FLOOR(index_length / 1048576) + 1) * 1048576) index_length_rounded
FROM information_schema.tables WHERE table_schema='mydb' and table_name='myfull';

Whatever number comes back for index_length_rounded is the size you use.

Please look up my posts about how to create Dedicated MyISAM Key Caches.

I would not worry too much about this since the INSERTs and UPDATEs seems so rare.

  • 1
    The reason for MyISAM is full-text search in old 5.5 version. Thanks for the pointers, but is there no way to improve the writes? I tried to FLUSH TABLES; but nothing changed. I'm testing also on sandbox machine that has no traffic, no writes, only reads. What else should I look at? – lp1051 Aug 2 '17 at 7:26
  • no writes = no heavy or concurrent writes – lp1051 Aug 2 '17 at 7:34
2

OK, the hit rate is low. But let's put things in perspective... 25K writes in 25 days is so insignificant (< 1/minute) as to be the least of your worries. 5K/minute would be a concern.

If you are having performance problems, locate the slowest query and study it -- its indexes, its formulation, etc.

The "key buffer" applies to MyISAM tables, of which you have only 1. The InnoDB statistics are not listed.

For further discussion, see http://mysql.rjweb.org/doc.php/mysql_analysis

  • Thank you! Perhaps I should mention it, but I have no performance problems. The reason for my question is solely to understand what does Write Key buffer hit rate mean, why is it reported by MySQLTuner as problematic configuration and how to improve it. I couldn't find any direct explanation or examples, and so may others... – lp1051 Aug 17 '17 at 11:11
  • 1
    Hitting the disk is a performance issue. The hit rate indicates what percent of the time it needs to hit the disk, which is a different metric. Hence, I was trying to redirect you to a more meaningful metric. There are a about 500 metrics; Tuner lists only a few. Many metrics are presented without good clues to answer "so what?"; you hit one such metric. – Rick James Aug 17 '17 at 14:42

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.