Optimizing MySQL, part 1

MySQL is a most popular database management system. It can be configured to match your needs using huge amount of options and settings. But which options are optimal? Wrong values or settings might affect the speed of your queries – both positively and negatively.

By this article I start my personal investigation and setting analysis. My goal is find be options, that would suit modern shared hosting needs. I am going to write my own test sets, trying to simulate different scenarios.

Test case

creating many tables, running bulk inserts and random selects

I start with very simple and basic tests.

• create one MyISAM and one InnoDB table and insert 1’000’000 records;
• create 200 MyISAM and 200 InnoDB tables, insert 1’000 records in each;
• do 500 random selects using primary key on 100 random tables.

Results without configuration (everything default)

#####################################
start global: 09:03:40
start simple myisam insert: 09:03:41
end   simple myisam insert: 09:04:01
total simple myisam insert: 20s
start simple innodb insert: 09:04:01
end   simple innodb insert: 09:04:23
total simple innodb insert: 22s
start random select innodb: 09:04:23
end   random select innodb: 09:04:48
total random select innodb: 25s
start random select myisam: 09:04:48
end   random select myisam: 09:05:15
total random select myisam: 27s
start create tables: 09:05:28
end   create tables: 09:06:02
total create tables: 34s
start fill innodb tables: 09:06:02
end   fill innodb tables: 09:17:47
total fill innodb tables: 705s
start innodb random queries: 09:17:47
end   innodb random queries: 09:17:47
total innodb random queries: 0s
start create myisam tables: 09:17:47
end   create myisam tables: 09:17:52
total create myisam tables: 5s
start fill myisam tables: 09:17:52
end   fill myisam tables: 09:18:30
total fill myisam tables: 38s
start random myisam queries: 09:18:30
end   random myisam queries: 09:18:42
total random myisam queries: 12s
end   global: 09:18:42
total global: 902s
Execution time: 902 seconds
#####################################

Results with my tuned settings

#####################################
start global: 09:23:20
start simple myisam insert: 09:23:20
end   simple myisam insert: 09:23:37
total simple myisam insert: 17s
start simple innodb insert: 09:23:37
end   simple innodb insert: 09:23:52
total simple innodb insert: 15s
start random select innodb: 09:23:52
end   random select innodb: 09:24:14
total random select innodb: 22s
start random select myisam: 09:24:14
end   random select myisam: 09:24:36
total random select myisam: 22s
start create tables: 09:24:38
end   create tables: 09:24:59
total create tables: 21s
start fill innodb tables: 09:24:59
end   fill innodb tables: 09:25:43
total fill innodb tables: 44s
start innodb random queries: 09:25:43
end   innodb random queries: 09:25:43
total innodb random queries: 0s
start create myisam tables: 09:25:43
end   create myisam tables: 09:25:47
total create myisam tables: 4s
start fill myisam tables: 09:25:47
end   fill myisam tables: 09:26:20
total fill myisam tables: 33s
start random myisam queries: 09:26:20
end   random myisam queries: 09:26:31
total random myisam queries: 11s
end   global: 09:26:31
total global: 191s
Execution time: 191 seconds
#####################################

Example of my settings (best so far)

[mysqld]
skip-name-resolve
max_allowed_packet=268435456
myisam_use_mmap=1
myisam_sort_buffer_size=128M
join_buffer_size=4M # NO DIFF
tmp_table_size=128M
max_heap_table_size=128M
key_buffer_size=256M # OK, JUST LEAVE
read_buffer_size=4M
read_rnd_buffer_size=2M
default-storage-engine=MyISAM
innodb_file_per_table=1
innodb_buffer_pool_size=134217728

# THESE MADE DIFFERENCES
innodb_flush_log_at_trx_commit=2 # makes faster! 25sec
innodb_thread_concurrency=2 #original 0; slower
innodb_buffer_pool_size=128M # default - 128M  can keep original
innodb_sort_buffer_size=512K # default=1M !!!! value 64M -  MINUTES !!! # value=512K -  17s; # value=2M - 21s

query_cache_type=1
query_cache_size=256M
query_cache_limit=1M
#query_cache_min_res_unit=256 # small values does not speed up, just keep original

# KEEP ORIGINAL 8192 !
# makes creating tables very slow
#table_open_cache=20000
#table_definition_cache=20000
#table_open_cache=1024
#table_definition_cache=1024

# innodb_log_file_size=128M # NO DIFFERENCE 
# thread_concurrency  - NOT NEEDED!
# innodb_log_buffer_size=16M # NO DIFFERENCE
# thread_stack=2048K # NO DIFFERENCE
# thread_cache=8 # NO DIFFERENCE
# thread_cache_size=8 # NO DIFFFERENCE

open_files_limit=20000