MySQL Forums
Forum List  »  Performance

key butter hit rate 99.9% ???
Posted by: Shashidhar Y
Date: August 08, 2011 01:23AM

Hi

I have been trying to fine tune my mysql server. Using mysqlTuner tool, I got below report.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 26d 0h 2m 5s (28M q [12.498 qps], 1K conn, TX: 2666B, RX: 13B)
[--] Reads / Writes: 1% / 99%
[--] Total buffers: 4.2G global + 36.4M per thread (100 max threads)
[OK] Maximum possible memory usage: 7.7G (24% of installed RAM)
[OK] Slow queries: 0% (3K/28M)
[OK] Highest usage of available connections: 24% (24/100)
[OK] Key buffer size / total MyISAM indexes: 4.0G/9.9G
[OK] Key buffer hit rate: 99.9% (47B cached / 43M reads)
[OK] Query cache efficiency: 39.9% (255K cached / 641K selects)
[OK] Query cache prunes per day: 91
[!!] Sorts requiring temporary tables: 418% (7K temp sorts / 1K sorts)
[OK] Temporary tables created on disk: 5% (23K on disk / 452K total)
[!!] Thread cache hit rate: 9% (1K created / 1K connections)
[!!] Table cache hit rate: 2% (385 open / 14K opened)
[OK] Open file limit used: 0% (532/65K)
[OK] Table locks acquired immediately: 99% (27M immediate / 27M locks)
[!!] InnoDB data size / buffer pool: 49.1M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
sort_buffer_size (> 16M)
read_rnd_buffer_size (> 10M)
thread_cache_size (> 50)
table_cache (> 600)
innodb_buffer_pool_size (>= 49M)

My worry is on key buffer size. I see I have 4.0G set but my total MyISAM indexes (I only use MyISAM tables in my database) coming to 9.9G.
Doesn't this mean to indication to increase key_buffer_size. I am bit confused on this, as why mysqlTuner didn't report this as something to worry (It said, OK!)

Can any one of you PLEASE help me to have some thoughts.

Options: ReplyQuote


Subject
Views
Written By
Posted
key butter hit rate 99.9% ???
12903
August 08, 2011 01:23AM
4127
August 09, 2011 12:03AM
4995
August 10, 2011 08:13PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.