I’m with heavy use in MySQL MyIsam and want to make some improvements. I’m kindly asking your advice and suggestions.
Current situation:
Since las 11 days, 6 million pages served (taking into account also crawlers) with 2 billion mysql queries at a rate of 350 queries per page and 2.3 k queries per second. I’ve run the slow queries log and I’ve take a tour creating few missing indexes. Some things cannot be improved easily by design. So in the meanwhile I’ll to attack concurrently the cnf file.
Red values are:
Handler_read_rnd 904 M
Handler_read_rnd_next 30 G
Qcache_lowmem_prunes 132 M
Created_tmp_disk_tables 13 M
Key_reads 46 M
Key_writes 27 M
Select_full_join 45 k
Select_range_check 46
Sort_merge_passes 21 k
Opened_tables 2,597 k
Table_locks_waited 1,069 k
I’ve some extra memory to use so I’m think to changes these settings:
join_buffer_size = 131072 → 64M
key_bufer_size = 32M → 256M
query_cache_size = 64M → 512M
read_buffer_size = 2M → 64M (deleting “read_buffer” line)
sort_buffer_size = 2M → 256M
table_cache = 256 → table_open_cache = 1024
query_cache_type = 1 (today not present)
myisam_sort_buffer_size = 64M → 256M
I’ll appreciate your suggestions.
Thanks and Regards
Gustavo