2

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
  • 1
    MyISAM index size is 718 - is that in MB? if yes then the key buffer is almost 3x bigger than needed (2GB), MyISAM uses it only for indexes and uses OS filesystem cache for the data so you need to left some memory "free". I can't see the innodb_buffer_pool_size value, hope it is not default 128MB. Your query cache is too big at 400MB, so when it has to be invalidated it takes too much time. Read about it and either make it ~32MB or disable it entirely. 1GB tmp_table_size means that when this overflows, it has to save 1GB to disk "instantly" - that will stall such query a lot. – jkavalik Apr 24 '16 at 6:23
  • Seeing that index size is less than data size, are indexes defined for all important queries? If the MyISAM table is mostly read-only, the indexes won't slow writes much but can speed up the selects a lot. – jkavalik Apr 24 '16 at 6:25
  • @jkavalik The size is in MB. I added the value of innodb_buffer_pool_size, it is 12G. – rho Apr 24 '16 at 15:18
  • @jkavalik there is 2 queries run on the table and the indexes are defined for these 2 queries and they return data quickly when there isn't a lot of load. – rho Apr 24 '16 at 15:22
  • 12GB + 2GB is at least 14GB in "permanent" buffers. When you add all those other buffers you get over the 16GB available quickly. Maybe the server is swapping when there are more connections? Check at mysqlcalculator.com for some basic numbers of memory usage to see if it might be the case. Or run the load test and check your memory and IO stats. – jkavalik Apr 24 '16 at 15:28
6

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

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 !!!

  • #2 (preloading indexes) means that you are taking RAM away from things that might need it more. – Rick James Apr 25 '16 at 21:07
  • 1
    #3 (VARCHAR->CHAR). NO! That makes the data more bulky, which, if it is not entirely cached on disk, makes for more I/O, which makes things slower. Only if you have very heavy delete/update traffic to the table should you even think about FIXED. – Rick James Apr 25 '16 at 21:10
  • 1
    @RickJames FIXED increase read performance by 20-25% as published. I have client in the past that enjoyed it when I implemented for them (Of course, they had gobs of money and diskspace, so it's not for everyone or every workload) – RolandoMySQLDBA Apr 25 '16 at 21:17
  • 1
    I admit there can be valid cases. – Rick James Apr 25 '16 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.