MySQL Performance Tuning for MyISAM

Question

I need to performance tune a mysql server and need help.

I have a 16G server dedicated to MySQL with 1 MyISAM table with about 2 million rows that gets a significant amount of traffic. There are about 100 other innodb tables on the same database. The MyISAM table is pretty much read only and used by user for searches.

The server has a max_connection value of 800, under load test of about 300 concurrent users the query time starts to increase significantly. Without load the queries take about 500ms to run.

The goal is to significantly improve the performance of searches on the MyISAM table.

The size of the tables in MB:

data_size   index_size  total           engine
8991        6920        15911           InnoDB
1110        718         1829            MyISAM

The following are my settings:

connect_timeout 10
innodb_additional_mem_pool_size 24117248
innodb_buffer_pool_size 12G
innodb_commit_concurrency   0
innodb_flush_log_at_trx_commit  1
innodb_log_buffer_size  11534336
innodb_log_file_size    449839104
innodb_open_files   300
innodb_thread_concurrency   8
innodb_thread_sleep_delay   10000
join_buffer_size    131072

key_buffer_size 2147483648
key_cache_age_threshold 300
key_cache_block_size    1024
key_cache_division_limit    100


max_allowed_packet  134217728
max_connections 800
max_delayed_threads 20
max_join_size   18446744073709551615
max_user_connections    0


myisam_max_sort_file_size   107374182400
myisam_mmap_size    18446744073709551615
myisam_sort_buffer_size 70254592

open_files_limit    2048

query_cache_limit   104857600
query_cache_min_res_unit    4096
query_cache_size    419430400
query_cache_type    ON

read_buffer_size    262144

socket  MySQL
sort_buffer_size    2097152

table_type  InnoDB
thread_cache_size   100
thread_handling one-thread-per-connection
thread_stack    262144

tmp_table_size  1073741824

Answer 1

You clearly stated you goal in the question

The goal is to significantly improve the performance of searches on the MyISAM table

OK let's tune the MyISAM side of things.

I have four(4) suggestions for you

SUGGESTION #1 : Reduce your RAM

First of all, look back 5 years ago and note what I said in my answer to the post What are the main differences between InnoDB and MyISAM?: The MyISAM storage engine caches only the index pages from the .MYI of a MyISAM table.

You set key_buffer_size to 2147483648, that 2G. Yet, your MyISAM indexes only total 718M. You need to reduce key_buffer_size to 768M just to free up 1.2G of RAM.

SUGGESTION #2 : Load All MyISAM indexes into RAM

Many are not aware of this, but you can load one or more indexes from a MyISAM table into RAM. It's called a dedicated keycache. Over 3.5 years ago, I wrote about this in the answer to the post Can I partially invalidate the MySQL table cache? (See SUGGESTION #1). It is also in the MySQL Documentation : See Cache Index Syntax

SUGGESTION #3 : Expand all VARCHARs into CHARs

If the MyISAM table has many VARCHAR fields, you could make VARCHAR fields behave like a CHAR with one DDL command

ALTER TABLE myisamtable ROW_FORMAT=FIXED;

This will expand the table is such a way that a the data type of a VARCHAR(32) is actually 32 bytes wide all the time, just like a CHAR(32). This can potentially double or triple the size of the MyISAM table but you can gain a 20-25% increase in read speed.

I wrote about this many times

• Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes
• Aug 12, 2011: Which DBMS is good for super-fast reads and a simple data structure?
• Sep 20, 2011 : Best of MyISAM and InnoDB
• May 02, 2012 : Which mysql storage engine to choose?
• May 03, 2012 : Which is faster, InnoDB or MyISAM?

If you do this, you will need to go back and measure how big the MyISAM index has grown. Then, redo SUGGESTION #2 and create the MyISAM dedicated keycache to that new size.

SUGGESTION #4

Although MyISAM does not have a global cache of MyISAM data, each DB Connection has a local cache size by read_buffer_size (for sequential reads) and read_rnd_buffer_size (for indexed reads).

You have read_buffer_size set at 256K (262144). You did not set read_rnd_buffer_size at all, so it is set to the default size of 256K (262144). You may want to experiment with caching data but be very careful because those two MyISAM data caches are for each DB Connection.

GIVE IT A TRY !!!