3

I have some confusion about my mysql status. mysql> show status like '%key%';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Com_assign_to_keycache | 0     |
| Com_preload_keys       | 0     |
| Com_show_keys          | 0     |
| Handler_read_key       | 2     |
| Key_blocks_not_flushed | 0     |
| Key_blocks_unused      | 13396 |
| Key_blocks_used        | 0     |
| Key_read_requests      | 0     |
| Key_reads              | 0     |
| Key_write_requests     | 0     |
| Key_writes             | 0     |

but,there have large (more then 1 billion each day) insert,update and query on the server,but why the status's value is 0.The server has run nearly 3 days.(Uptime:2 days 18 hours 54 min 19 sec).I did not flush the server's status. Some db config engine = innodb,key_buffer = 16M, innodb_buffer_pool_size = 2147483648. Thanks for any information.

luofei
  • 31
  • 4
  • Am I to understand you've had over 10 BILLION operations on your DB in 3 days (not including where clauses?) – DanRedux May 15 '12 at 07:13
  • I am so sorry.I want to say is 1 billion every day no more than 10 billion. It include all operations.the status is this:Threads: 17 Questions: 429324377 Slow queries: 453 Opens: 17281 Flush tables: 1 Open tables: 64 Queries per second avg: 1387.984.I had saw the slow queries,They almost caused by the insert operation which the table be inserted has to many index.I removed many indexes then it seems running,and I add this indexes to my slave server. – luofei May 16 '12 at 01:58

2 Answers2

4

Perhaps you're using InnoDB tables ?

Those Key_XXX server status values are for MyISAM tables.

nos
  • 223,662
  • 58
  • 417
  • 506
1

The values you're looking at are for MyISAM tables. They represent the MyISAM Key Cache:

http://dev.mysql.com/doc/refman/5.0/en/myisam-key-cache.html

This cache holds recently used keys with the expectation that keys used recently are likely to be reused again soon -- therefore they could be valuable to cache.

Since you're using innodb, the key cache isn't being used.

For tuning purposes you should minimize the amount of memory you have dedicated to the key cache. Any memory taken away from innodb processing is probably wasted -

Kevin Bedell
  • 13,254
  • 10
  • 78
  • 114
  • Thanks again.I was a little confused about the status of innodb and myisam engine.The really reason is one table has too many useless indexes which caused the server has many slow queries. – luofei May 16 '12 at 03:18
  • Great. I'm glad I could help. If you felt I answered the question well, I'd appreciate you accepting my answer. Thanks and good luck! – Kevin Bedell May 16 '12 at 14:23