Optimizing MySQL, part 2

Posted on

Today I wrote new tests. Today tests are targeting 3 large tables, containing from 100’000 to 1’700’000 records. We do huge number of random selects both using indexes and non-indexed ‘like’ selects. We test joins using keys and non-keys.. Well, for sure – if you do joins – you must use keys.

Test results now contain example queries and more details about measured time. What I have found are several suggestions on the following MySQL settings:

  • key_buffer_size – large value won’t help string ‘like’ selections;
  • sort_buffer_size – large value won’t help much either;
  • read_rnd_buffer_size and read_buffer_size – this does not help, when joining large tables.

Affected MySQL settings

key_buffer_size=64M
innodb_sort_buffer_size=1M
myisam_sort_buffer_size=1M
sort_buffer_size=1M
read_rnd_buffer_size=2M 
join_buffer_size=1M
read_buffer_size=2M

Best results for testing on MyISAM tables

#####################################
start global: 17:10:59
start myisam tests: 17:10:59
start get 1000 random pages myisam: 17:10:59
end get 1000 random pages myisam: 17:11:03
total get 1000 random pages myisam: 4s
start get 1000 random pages myisam: 17:11:03
end get 1000 random pages myisam: 17:11:06
total get 1000 random pages myisam: 3s
start get 1000 random pages myisam: 17:11:06
end get 1000 random pages myisam: 17:11:10
total get 1000 random pages myisam: 4s
start get 1000 random pages myisam: 17:11:10
end get 1000 random pages myisam: 17:11:14
total get 1000 random pages myisam: 4s
start get 1000 random pages myisam: 17:11:14
end get 1000 random pages myisam: 17:11:17
total get 1000 random pages myisam: 3s
Selected 5000 random records...
start myisam: SELECT using INDEX: 17:11:17
Running 5000 queries.
SQL QUERY: select * from myisam_jstats_pages where page_id=149207
end myisam: SELECT using INDEX: 17:11:18
total myisam: SELECT using INDEX: 1s
Queries per second: 5000
start myisam: SELECT using INDEX and INDEXED JOIN: 17:11:18
Running 5000 queries.
SQL QUERY: select myisam_jstats_pages.*, myisam_jstats_impressions_key.* from myisam_jstats_pages, myisam_jstats_impressions_key where myisam_jstats_pages.page_id=myisam_jstats_impressions_key.page_id and myisam_jstats_pages.page_id=149207
end myisam: SELECT using INDEX and INDEXED JOIN: 17:11:20
total myisam: SELECT using INDEX and INDEXED JOIN: 2s
Queries per second: 2500
start myisam: SELECT using INDEX and 3 INDEXED JOIN and STRING ORDER: 17:11:20
Running only 100 queries.
SQL QUERY: select myisam_jstats_pages.*, myisam_jstats_impressions_key.*, myisam_jstats_visits.*, myisam_jstats_referrer.* from myisam_jstats_pages, myisam_jstats_impressions_key, myisam_jstats_visits, myisam_jstats_referrer where myisam_jstats_pages.page_id=myisam_jstats_impressions_key.page_id and myisam_jstats_impressions_key.visit_id=myisam_jstats_visits.visit_id and myisam_jstats_referrer.visit_id=myisam_jstats_visits.visit_id and myisam_jstats_pages.page_id=73550 order by myisam_jstats_referrer.referrer
end myisam: SELECT using INDEX and 3 INDEXED JOIN and STRING ORDER: 17:11:32
total myisam: SELECT using INDEX and 3 INDEXED JOIN and STRING ORDER: 12s
Queries per second: 8.33
start myisam: SELECT using INDEX and NON-KEY JOIN, NO ORDER: 17:11:32
Running just 100 queries... NON-KEY joins are slow!
SQL QUERY: select myisam_jstats_pages.*, myisam_jstats_impressions.* from myisam_jstats_pages, myisam_jstats_impressions where myisam_jstats_pages.page_id=myisam_jstats_impressions.page_id and myisam_jstats_pages.page_id=367152
end myisam: SELECT using INDEX and NON-KEY JOIN, NO ORDER: 17:12:10
total myisam: SELECT using INDEX and NON-KEY JOIN, NO ORDER: 38s
Queries per second: 2.63
Now going to do NON-KEY SELECT and JOINs
Just see the power of integer-keys and indexes!

Just 100 selects with like 'string' on a 1.7mln big table...
MySQL will go through all records now.
start myisam: SELECT with LIKE, NO ORDER: 17:12:10
Running 100 queries only...
SQL QUERY: select * from myisam_jstats_pages where page like '/categories/manufacturer/peter-eggink/dirDesc/results,221-240?error=404'
end myisam: SELECT with LIKE, NO ORDER: 17:14:12
total myisam: SELECT with LIKE, NO ORDER: 122s
Queries per second: 0.82
To make things worse we JOIN another table through non-indexed field..

start myisam: SELECT with LIKE and 1 NON-KEY JOIN, NO ORDER: 17:14:12
Running 25 queries only...
SQL QUERY: select myisam_jstats_pages.*, myisam_jstats_impressions.* from myisam_jstats_pages, myisam_jstats_impressions where myisam_jstats_pages.page_id=myisam_jstats_impressions.page_id and myisam_jstats_pages.page like '/component/virtuemart/thread,-reels-itrs/dirDesc/results,21-120?Itemid=0&filter_product='
end myisam: SELECT with LIKE and 1 NON-KEY JOIN, NO ORDER: 17:19:51
total myisam: SELECT with LIKE and 1 NON-KEY JOIN, NO ORDER: 339s
Queries per second: 0.07
Worst case - SELECT with LIKE, NON-KEY JOIN and ORDER BY STRING

start myisam: SELECT with LIKE and 1 NON-KEY JOIN, NO ORDER: 17:19:51
Running 25 queries only...
SQL QUERY: select myisam_jstats_pages.*, myisam_jstats_impressions.* from myisam_jstats_pages, myisam_jstats_impressions where myisam_jstats_pages.page_id=myisam_jstats_impressions.page_id and myisam_jstats_pages.page like '/categories/manufacturer/tora-magic/canes-candles/dirDesc/results,11-110?filter_product=' order by myisam_jstats_pages.page_title
end myisam: SELECT with LIKE and 1 NON-KEY JOIN, NO ORDER: 17:25:32
total myisam: SELECT with LIKE and 1 NON-KEY JOIN, NO ORDER: 341s
Queries per second: 0.07
end myisam tests: 17:25:32
total myisam tests: 873s
end global: 17:25:32
total global: 873s
#####################################

Best MySQL configuration file

[mysqld]
skip-name-resolve
myisam_use_mmap=1
max_allowed_packet=268435456

#table_open_cache=1024
#table_definition_cache=1024

innodb_buffer_pool_size=134217728
default-storage-engine=MyISAM

innodb_file_per_table=1

# THESE MADE DIFFERENCES
innodb_flush_log_at_trx_commit=2 
innodb_thread_concurrency=2 

# THESE MADE DIFFERENCE!
innodb_buffer_pool_size=128M 

# PART 2
innodb_sort_buffer_size=1M # 4m-slower

myisam_sort_buffer_size=1M
sort_buffer_size=1M

read_rnd_buffer_size=2M 
join_buffer_size=1M
read_buffer_size=2M 
# END PART 2

# KEEP ORIGINAL 8192 !
#table_open_cache=20000
#table_definition_cache=20000

# innodb_log_file_size=128M # original value 48M; value=16M - 21s # value=128M - 16s - can keep original
# thread_concurrency  - NOT NEEDED!
# innodb_log_buffer_size=16M # default 8M # value=2M - 15s # value=16M - 17s - can keep original
# thread_stack=2048K # value=256K - 31s # value=1M - 16s # value=2M - 16s - can keep original


# TO TEST WITH MYISAM
query_cache_limit=1M
#query_cache_min_res_unit=256

query_cache_type=1        
query_cache_size=256M     

key_buffer_size=64M 

#thread_cache=8 
#thread_cache_size=8 

tmp_table_size=128M
max_heap_table_size=128M

open_files_limit=20000
0
MySQL , ,

Leave a Reply

Your email address will not be published. Required fields are marked *