RHEL 5.0.77 vs 5.1.42-MariaDB
From Ye Ole Stash
Contents |
Hardware and General Information
- CPU : Quad-Core AMD Opteron(tm) Processor 2378
- CPU Cache : 512 KB
- cpu family : 16
- stepping : 2
- CPUS : 2
- Cores : 8
- Hyperthreading : No
- Memory : 16G DDR2 Reg ECC
- I/O Scheduler: Deadline
- Motherboard : PowerEdge 2970
- Drives Used: 6x Dell 146G 15K SAS (2x hardware raid (OS) 1 4x hardware raid 10 (/var/lib/mysql)
- SCSI Controller : LSI Logic / Symbios Logic MegaRAID SAS 1078 (rev 04)
Benchmark
Sysbench
- Prepare
- /usr/local/sysbench/bin/sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=youruser --mysql-password='yourpass' --mysql-socket=/var/lib/mysql/mysql.sock prepare
- Read Only
- /usr/local/sysbench/bin/sysbench --num-threads=16 --max-requests=250000 --test=oltp --oltp-table-size=1000000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-read-only --mysql-user=youruser --mysql-password='yourpass' run
- Read Write
- /usr/local/sysbench/bin/sysbench --num-threads=16 --max-requests=250000 --test=oltp --oltp-table-size=1000000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-test-mode=complex --mysql-user=youruser --mysql-password='yourpass' run
Maria DB Results
XFS
Read Only
OLTP test statistics:
queries performed:
read: 3500252
write: 0
other: 500036
total: 4000288
transactions: 250018 (4949.49 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 3500252 (69292.89 per sec.)
other operations: 500036 (9898.98 per sec.)
Test execution summary:
total time: 50.5139s
total number of events: 250018
total time taken by event execution: 806.1618
per-request statistics:
min: 1.27ms
avg: 3.22ms
max: 39.49ms
approx. 95 percentile: 8.85ms
Threads fairness:
events (avg/stddev): 15626.1250/200.78
execution time (avg/stddev): 50.3851/0.00Read/Write
OLTP test statistics:
queries performed:
read: 3501512
write: 1250540
other: 500216
total: 5252268
transactions: 250108 (2560.46 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 4752052 (48648.77 per sec.)
other operations: 500216 (5120.92 per sec.)
Test execution summary:
total time: 97.6808s
total number of events: 250108
total time taken by event execution: 1560.0485
per-request statistics:
min: 1.78ms
avg: 6.24ms
max: 273.85ms
approx. 95 percentile: 13.28ms
Threads fairness:
events (avg/stddev): 15631.7500/79.27
execution time (avg/stddev): 97.5030/0.00EXT3
Read Only
OLTP test statistics:
queries performed:
read: 3500266
write: 0
other: 500038
total: 4000304
transactions: 250019 (4905.54 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 3500266 (68677.55 per sec.)
other operations: 500038 (9811.08 per sec.)
Test execution summary:
total time: 50.9667s
total number of events: 250019
total time taken by event execution: 813.4848
per-request statistics:
min: 1.30ms
avg: 3.25ms
max: 36.85ms
approx. 95 percentile: 8.36ms
Threads fairness:
events (avg/stddev): 15626.1875/187.20
execution time (avg/stddev): 50.8428/0.00Read/Write
OLTP test statistics:
queries performed:
read: 3501092
write: 1250387
other: 500155
total: 5251634
transactions: 250077 (2572.31 per sec.)
deadlocks: 1 (0.01 per sec.)
read/write requests: 4751479 (48873.99 per sec.)
other operations: 500155 (5144.62 per sec.)
Test execution summary:
total time: 97.2190s
total number of events: 250077
total time taken by event execution: 1552.7358
per-request statistics:
min: 1.62ms
avg: 6.21ms
max: 212.74ms
approx. 95 percentile: 13.23ms
Threads fairness:
events (avg/stddev): 15629.8125/93.48
execution time (avg/stddev): 97.0460/0.00RHEL Default MySQL Results
XFS
Read Only
OLTP test statistics:
queries performed:
read: 3500042
write: 0
other: 500006
total: 4000048
transactions: 250003 (3210.28 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 3500042 (44943.90 per sec.)
other operations: 500006 (6420.56 per sec.)
Test execution summary:
total time: 77.8758s
total number of events: 250003
total time taken by event execution: 1243.9839
per-request statistics:
min: 1.42ms
avg: 4.98ms
max: 85.66ms
approx. 95 percentile: 15.12ms
Threads fairness:
events (avg/stddev): 15625.1875/141.30
execution time (avg/stddev): 77.7490/0.00Read/Write
OLTP test statistics:
queries performed:
read: 3500658
write: 1250235
other: 500094
total: 5250987
transactions: 250047 (944.78 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 4750893 (17950.86 per sec.)
other operations: 500094 (1889.56 per sec.)
Test execution summary:
total time: 264.6610s
total number of events: 250047
total time taken by event execution: 4231.7281
per-request statistics:
min: 1.96ms
avg: 16.92ms
max: 191.81ms
approx. 95 percentile: 56.32ms
Threads fairness:
events (avg/stddev): 15627.9375/91.02
execution time (avg/stddev): 264.4830/0.0EXT3
Read Only
OLTP test statistics:
queries performed:
read: 3500070
write: 0
other: 500010
total: 4000080
transactions: 250005 (3233.10 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 3500070 (45263.42 per sec.)
other operations: 500010 (6466.20 per sec.)
Test execution summary:
total time: 77.3267s
total number of events: 250005
total time taken by event execution: 1235.2167
per-request statistics:
min: 1.40ms
avg: 4.94ms
max: 67.18ms
approx. 95 percentile: 15.12ms
Threads fairness:
events (avg/stddev): 15625.3125/157.52
execution time (avg/stddev): 77.2010/0.00Read/Write
OLTP test statistics:
queries performed:
read: 3500630
write: 1250225
other: 500090
total: 5250945
transactions: 250045 (1004.18 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 4750855 (19079.48 per sec.)
other operations: 500090 (2008.37 per sec.)
Test execution summary:
total time: 249.0034s
total number of events: 250045
total time taken by event execution: 3981.2570
per-request statistics:
min: 1.90ms
avg: 15.92ms
max: 228.61ms
approx. 95 percentile: 48.84ms
Threads fairness:
events (avg/stddev): 15627.8125/87.39
execution time (avg/stddev): 248.8286/0.01EXT3 w/ modified my.cnf (see below for contents)
Read Only
OLTP test statistics:
queries performed:
read: 3500070
write: 0
other: 500010
total: 4000080
transactions: 250005 (4410.85 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 3500070 (61751.96 per sec.)
other operations: 500010 (8821.71 per sec.)
Test execution summary:
total time: 56.6795s
total number of events: 250005
total time taken by event execution: 904.9808
per-request statistics:
min: 1.36ms
avg: 3.62ms
max: 72.51ms
approx. 95 percentile: 14.52ms
Threads fairness:
events (avg/stddev): 15625.3125/246.41
execution time (avg/stddev): 56.5613/0.00Read/Write
OLTP test statistics:
queries performed:
read: 3500854
write: 1250305
other: 500122
total: 5251281
transactions: 250061 (2750.46 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 4751159 (52258.69 per sec.)
other operations: 500122 (5500.91 per sec.)
Test execution summary:
total time: 90.9162s
total number of events: 250061
total time taken by event execution: 1452.0175
per-request statistics:
min: 1.93ms
avg: 5.81ms
max: 78.49ms
approx. 95 percentile: 17.24ms
Threads fairness:
events (avg/stddev): 15628.8125/135.03
execution time (avg/stddev): 90.7511/0.01Observations with my.cnf
- Base my.cnf
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql old_passwords=1 innodb_data_file_path = ibdata1:3G;ibdata2:1G:autoextend innodb_autoextend_increment=512 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
- These settings allowed for almost identical performance to the MariaDB with the base my.cnf.
max_connections = 1000 max_connect_errors = 10 table_cache = 1024 max_allowed_packet = 16M max_heap_table_size = 64M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 64 thread_concurrency = 18 query_cache_size = 64M query_cache_limit = 2M tmp_table_size = 64M key_buffer_size = 32M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_log_buffer_size = 8M innodb_log_file_size = 512M innodb_log_files_in_group = 2 innodb_buffer_pool_size = 13G innodb_data_file_path = ibdata1:3G;ibdata2:1G:autoextend innodb_autoextend_increment=512
