mysqltuner - Write Key buffer hit rate

Question

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.

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

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.

Answer 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