1

MyISAM queries results too slow,What changes should i make,

if
Ram 16GB DB 500GB

mysql> SHOW STATUS LIKE 'key%'

+------------------------+-------------+
| Variable_name          | Value       |
+------------------------+-------------+
| Key_blocks_not_flushed | 0           | 
| Key_blocks_unused      | 275029      | 
| Key_blocks_used        | 3316428     | 
| Key_read_requests      | 11459264178 | 
| Key_reads              | 3385967     | 
| Key_write_requests     | 91281692    | 
| Key_writes             | 27930218    | 
+------------------------+-------------+
Ryan
  • 26,884
  • 9
  • 56
  • 83
Syed Daniyal Asif
  • 726
  • 1
  • 5
  • 19

1 Answers1

2

After subtracting off enough room for the actual sizes of the MEMORY tables and any applications that are running on the same machine, give 10% of the remaining RAM to key_buffer_size to 10% and innodb_buffer_pool_size to 35% of RAM. That should be a good first cut at the main memory users.

The Key_reads / Key_read_requests says that key_buffer_size is big enough. Key_blocks_used * 1024 / key_buffer_size will give you an upper bound on what you need currently.

If you have made any other changes in my.cnf, let's see them.

http://mysql.rjweb.org/doc.php/memory

Rick James
  • 135,179
  • 13
  • 127
  • 222