Hi,
I compare performance of 5.0 and 5.5 at FreeBSD 7.4.
I have two identical servers:
2x X5687 3.6GHz
98GB
8x HDD 146GB
RAID at srv01:
Id Size Level Stripe State Cache Name mfid0 ( 544G) RAID-1 64K OPTIMAL Writes
RAID at srv02:
Id Size Level Stripe State Cache Name mfid0 ( 952G) RAID-5 128K OPTIMAL Writes
I run sysbench as follows:
MyISAM warmup:
–test=oltp --mysql-db=test --oltp-table-name=sbtest_myisam --max-time=120 --max-requests=0 --num-threads=8 --mysql-socket=/tmp/mysql.sock --oltp-read-only=on run
MyISAM r/o test:
–test=oltp --mysql-db=test --oltp-table-name=sbtest_myisam --max-time=60 --max-requests=0 --num-threads=$i --mysql-socket=/tmp/mysql.sock --oltp-read-only=on run
InnoDB warmup:
–test=oltp --mysql-db=test --oltp-table-name=sbtest_innodb --max-time=120 --max-requests=0 --num-threads=8 --mysql-socket=/tmp/mysql.sock --oltp-read-only=on run
InnoDB r/o test:
–test=oltp --mysql-db=test --oltp-table-name=sbtest_innodb --max-time=60 --max-requests=0 --num-threads=$i --mysql-socket=/tmp/mysql.sock --oltp-read-only=on run
InnoDB r/w test:
–test=oltp --mysql-db=test --oltp-table-name=sbtest_innodb --max-time=60 --max-requests=0 --num-threads=$i --mysql-socket=/tmp/mysql.sock run
What I get is 5.5 defeat:
num-threads 1 2 3 4 5 6 7 8 10 12 14 16 18 20 22 24 26 28 30 32 36 40 44 48 52 56 60 64srv01: MySQL 5.0.67MyISAM-RO, tps 426.71 725.57 973.66 1156.96 1272.73 1226.91 1139.41 1037.77 887.3 753.07 627.38 523.83 462.76 400.93 348.95 302.41 289.03 259.91 251.23 234.1 216.87 198.67 194.87 185.09 182.66 176.76 173.1 170.83InnoDB-RO, tps 340.64 677.72 1003.94 1322.52 1579.73 1805.14 2011.87 2216.72 2452.15 2644.93 2807.23 2902.67 2812.12 2738.45 2691.59 2668.98 2654.41 2649.93 2641.42 2619.1 2601.82 2567.7 2456.73 2429.16 2328.02 2285.03 2210.29 2181.58InnoDB-RW, tps 282.55 538.73 757.27 977.01 1169.77 1330.55 1466.86 1575.6 1735.46 1786.18 1761.36 1757.96 1741.73 1722.25 1658.2 1610.09 1583.49 1582.79 1566.43 1547.11 1478.27 1246 1177.73 786.99 753.66 516.18 627.72 683.74srv02: MySQL 5.5.15MyISAM-RO, tps 415.05 716.39 953.74 1164.5 1252.94 1178.99 1054.93 938.62 895.02 813.39 698.7 592.76 537.41 509.51 491.63 473.91 461.51 448.63 438.83 429.39 414.73 398.43 391.88 418.6 473.02 532.58 585.42 633.26InnoDB-RO, tps 329.41 644.44 942.18 1270.56 1536.27 1817.91 2091.28 2360.85 2253.96 2235.38 2227.67 2230.93 2249.48 2257.12 2262.33 2267.69 2277.28 2281.22 2283.75 2282.72 2289.74 2291.23 2289.82 2290.47 2289.81 2286.34 2284.71 2281.92InnoDB-RW, tps 263.08 500.49 725.2 958.81 1141.32 1295.54 1440.64 1557.53 1607.25 1611.72 1619.78 1617.16 1568.26 1417.52 1419.97 1415.19 1400.13 1401.08 1315 1309.76 1302.36 1280.06 1271.39 1259.55 1245.41 1240.26 1218.8 1199.69
What is a possible reason: misconfiguration, OS, RAID, something else?
The following my.cnf is used:
back_log = 100binlog_cache_size = 1Mconnect_timeout = 5delayed_insert_limit = 10000delayed_insert_timeout = 100delayed_queue_size = 8000000expire_logs_days = 5ft_min_word_len = 4innodb_additional_mem_pool_size = 20Minnodb_buffer_pool_size = 40Ginnodb_data_file_path = ibdata1:10M:autoextendinnodb_flush_log_at_trx_commit = 1innodb_flush_method = O_DIRECTinnodb_lock_wait_timeout = 300innodb_locks_unsafe_for_binlog = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 1900Minnodb_status_file = 0innodb_thread_concurrency = 0interactive_timeout = 120join_buffer_size = 1Mkey_buffer_size = 6Gmax_allowed_packet = 1Mmax_connect_errors = 10max_connections = 200max_heap_table_size = 64Mmax_user_connections = 200myisam_sort_buffer_size = 4Gskip-external-lockingskip-name-resolvesort_buffer_size = 64Ksync_binlog = 1table_cache = 2048thread_cache_size = 286thread_stack = 196Ktmp_table_size = 64Mtransaction_isolation = REPEATABLE-READwait_timeout = 120