MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
MySQL Performance Landscape
Peter Zaitsev, MySQL AB MySQL Users Conference 2006 Santa Clara,CA April 24-27 © MySQL AB 2006
1
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Introduction •
Very wide choise of platforms – Hardware – Platforms – Distribution Vendors (Linux)
•
A lot of possible configuration – RAID Levels, Block size – FileSystems – OS Kernel and Libraries settings
•
We will – Quantify performance with different configurations – Guess why such performance may be observed
2
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
About Benchmark •
Using SysBench – MultiUser benchmark developed by MySQL High Performance Group – Initial Goal – Investigating platform specific performance bugs for MySQL Customers – Simple – easy to analyze, but powerful – OpenSource http://sourceforge.net/projects/sysbench
•
Results may vary – Do not blindly expect it to be same for your application – Do you own benchmarks if you need to be sure
3
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Benchmark Configuration •
Two sizes of Workload – 200MB (1.000.000 rows) – CPU boand •
1, 16 and 256 clients
– 20GB (100.000.000 rows) - IO Bound •
•
1, 16 concurrent clients
Multiple Workloads – «Simple» - single row reads – «Read Write» - multiple read/write statements – «Read Only» - multiple statements, only reads
•
Result processing – Run 3 times after warmp – Best results scores •
«Peak Performance»
4
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
MySQL Configuration • • •
MySQL 5.0.18 and 5.0.19 MyISAM and Innodb storage engines Optimally configured for each platform – No sense to run with «defaults» as results irrelevant
5
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
LinuxThreads and NPTL CPU Bound workload PowerEdge 1425SC, 2*3.0Ghz Xeons CentOS 4.2 64bit
• • •
Complex RW
Simple Innodb
L.Innodb
27500
MyISAM
Innodb
L.MyISAM
650
26743 2655126419 26481
MyISAM 618
600
25000
L.MyISAM
601
550
22500
500
20000
450
17500
400
15000
350
12500
L.Innodb
300
1180212232 1165312065
10000
8279
7500
6431
5000
5363
5511
250 200
0
0 256
264 257
260 252
279 245 245 244
100 50
16
305
150
2500 1
323
1
16
256
6
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Benefit of Second CPU Running Kernel designed for Single CPU
•
– Some single CPU optimizations apply – Do not use HypperThreading for that CPU Complex RW
Simple 1.Innodb
Innodb
27500
1.MyISAM 26743
1.Innodb
MyISAM
1.MyISAM
650
26419
MyISAM
618
600
25000
550
22500
500
20000
450
17500
400 350
15000 12500
Innodb
11802 11653 11176 10923
12926
12663 11125
10000
8812
7500
8279 6431
300 250 200 100
2500
50
0
0 16
256
267 264
293
279
260 260
250 245
166
150
5000
1
328 323
1
16
256
7
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Overhead of SELinux SELinux – Security Extensions for Linux
•
– Enabled by default on most recent distributions – Might not be needed on dedicated MySQL server Complex RW
Simple Innodb
NOSL.Innodb
30000
MyISAM
618
600
MyISAM
NSE.MyISAM
593
550
25000
500
22500
450
20000
400
17500
350
15000 12500
NSE.Innodb
650
28375 28295 26743 26419
27500
Innodb
NOSL.MyISAM
300
1180212268 1165312298
10000 6431 6923
7500
82797820
250 200
323 313 264
278
260
275
279 284 245
150
5000
100
2500
50 0
0 1
16
256
1
16
256
263
8
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Chipset Differences ? Compare PowerEdge 2850 to PowerEdge 1425SC
•
– Considered to be system with Higher Performance – Use Same CPUs for both of them •
CPU bound workload – PE 2850 has much better IO Complex RO
Simple Innodb
2850.Innodb
MyISAM
25000
800
22500
700
20000
838
MyISAM
2850.MyISAM
857
600
17500
531 524
500
15000 12500
2850.Innodb
900
2695226419 26743 26677
27500
Innodb
2850.MyISAM
400
1180211932 1165311983
10000 6431 6976
7500
8279 6966
5000
395 404
485 479 382 384
325 328
300 200 100
2500
0
0 1
16
256
1
16
256
9
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Disable HypperThreading HypperThreading allows running multiple threads on the same CPU
•
– Using the parts of hardware which are unused – Shown as Logical CPU while almost no hardware duplication
•
Complex RO
Simple
Innodb
NoHT.Innodb
27500
MyISAM
26743
25000
23096
NoHT.MyISAM
838 729
700 600
17500 12500
MyISAM
800
20000 15000
NoHT.Innodb
900
26419
23031
22500
Innodb
NoHT.MyISAM
581 531 485 478
500 13623 11802
13591
400
11653
10000
8371 82798033
7500
6431
395 411
382
408
325 330
300 200
5000
100
2500
0
0 1
16
256
1
16
256
10
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
«Random Access» Optimization •
Setting in BIOS to optimize memory for random access – Guess different prefetch/caching configuration
Complex RO
Simple Innodb
Rand.Innodb
MyISAM
25000
800
22500
700
20000
MyISAM
Rand.MyISAM
838 842
582
600
17500
531 485 480
500
15000 12500
Rand.Innodb
900
27097 26743 26574 26419
27500
Innodb
Rand.MyISAM
400
1180212189 1165312158
395 390
382 370 325 325
10000 7500
6431
5858
8279 6556
5000
300 200 100
2500
0
0 1
16
256
1
16
256
11
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
32bit Binary • •
x86-64 is good in running 32bit code Is performance of 64bit and 32bit binaries any different – MySQL official binaries. Newest compilers may expose more differences Complex RO
Simple Innodb
32bit.Innodb
27500
MyISAM
838
MyISAM
32bit.MyISAM
871
800
22500
700
20000
600
17500
580 531 485 484
500
15000 12500
32bit.Innodb
900
26743 26419 25489 25489
25000
Innodb
32bit.MyISAM
400
1180211596 1165311215
10000 6431 6967
7500
8279 6967
5000
395
420
382 394
325 340
300 200 100
2500
0
0 1
16
256
1
16
256
12
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Opteron vs Xeon 2*3.0Ghz 2MB Cache Xeon, PE1425SC, CentOS 4.2 AMD Athlon(tm) 64 X2 Dual Core Processor 3800+, 2Ghz
• •
– Typically desktop model Complex RO
Simple Innodb
Amd.Innodb
MyISAM
25000
20763
17500
17267
19180
18598
12500
11802
735 635
620
600
400
11653
Amd.MyISAM
700 531
520
500
15000
MyISAM
838 818
800
22500 20000
Amd.Innodb
900
27448 26743 26419 25589
27500
Innodb
Amd.MyISAM
485
462 395
382 325
10000
8279
7500
6431
300 200
5000
100
2500
0
0 1
16
256
1
16
256
13
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
SunFire T2000 vs Xeon «Small» version
•
– 4 Cores * 4 Threads = 16 Threads
2*Xeon 3.0Ghz 2MB Cache (Single Core)
•
Complex RO
Simple Innodb
T2000.Innodb
27500
MyISAM
695
600
17500
531 15006
15000 11802
8279 6431 3136
395
382 325
7500 2977
485 469
483
500 400
11653
10000
2500
T2000.MyISAM
838
700
20000
5000
MyISAM
800
22500
12500
T2000.Innodb
900
26743 26419 25978 25601
25000
Innodb
T2000.MyISAM
354
300 200
1892
100
77
49
0
0 1
16
256
1
16
256
14
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
AMD Dual Core How much performance does enabling second core give ?
•
– Usually results are less than that because Dual Cores come at lesser frequency
No MyISAM data for single core
•
Complex RO
Simple 1.Innodb
2.Innodb
32500
1.MyISAM
1.Innodb
2.Amd.MyISAM
900
27500
800
22500
928
674
600 16543
500
15524
15000
2.MyISAM
700
22862
20000 17500
1.MyISAM
1000
31334
30000 25000
2.Innodb
13778
12500 10000
553 487
446
427
400
10980
7500
300 200
5000
100
2500
0
0 1
16
256
1
16
256
15
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Fedora Core 5 vs CentOS 4.3 AMD Athlon 64bit Newer is not always faster
• •
Complex RO
Simple FC.Innodb
CentOS.Innodb
FC.MyISAM 27448
27500 22500 20000 17500
818 735
12500
400
635
625 620
600
15000 10287
CentOS.MyISAM
700
500
10000
FC.MyISAM
884
800 2051820763 19180
18598 17267 16775 15742
CentOS.Innodb
900
2558925190 24309
25000
FC.Innodb
CentOS.MyISAM
493
548
520 421
529
462
300
7500
200
5000
100
2500
0
0 1
16
256
1
16
256
16
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Fedora Core 5 vs SuSE 10 Comparison on same AMD Athlon 64 Very surprising to see Fedora Core to be so much slower
• •
– Or is it SuSE 10 optimized for AMD ? Complex RO
Simple FC.Innodb
SuSE.Innodb
32500
FC.MyISAM
31334
22847
23095
24309
25190
15000
24446 20518
20000 17500
884
900
27500 22500
SuSE.Innodb
1000
32359
30000 25000
FC.Innodb
SuSE.MyISAM
15742
16775
7500
928
700
674 623
600
400 10980 10287
SuSE.MyISAM
800
500
12500 10000
FC.MyISAM
553
548
493
486
625
580 529
421
300 200
5000
100
2500
0
0 1
16
256
1
16
256
17
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Linux Kernels 2.4 vs 2.6 •
Benchmarks run on 4*2.0Ghz Xeon 32bit, Innodb – RedHat AS 3.0 Update 2 (2.4.21-5EL) – Kernel from RH AS 4.0 (2.6.9-34EL) – Latest «Vanilla» Kernel 2.6.16.2 Complex RO
Simple 2.4.21-5EL
2.6.9-34EL
2.4.21-5EL
2.6.16.2
32500
1000
30522 30796
30000
961 860 777
800
25000
700
22500
638
600
20000
17639 17280
17500 15000
7500
2.6.16.2
900
28576
27500
12500 10000
2.6.9-34EL
500 400
9223
300 7869 7844
315
282
295
292
200
5000
100
3193
2500
0
0 1
16
256
1
16
256
308
18
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Linux vs Solaris 10 Dual Opteron 244 (1.8Ghz) RedHat AS 4 update 2, Solaris 10 x86-64 Solaris performance improvement effort is going
• • •
Simple Lin.Innodb
Sol.Innodb
25000
Lin.Innodb
900
2356323349
22500 20000 17724
15000
Lin.Innodb
Sol.Innodb
650
869
13761
500
700
500
12500
612 483
481
447
8365
450 400
5000
200
150 100
2500
100
50
0
0
0
16
256
345
250 200
1
353
350
300
7500
399
300
400
10000
592
550
600
15532
Sol.Innodb 614
600
793
800
17500
Complex RW
Complex RO
1
16
256
127
1
16
256
19
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
SuSE 9.3 vs Windows Dual P4 3.4Ghz, 1GB RAM Benchmark Run over 1Gb network
• •
– SysBench can't be run on Windows
Results are very supecious. Something is wrong, likely.
•
Complex RO
Simple SuSE.Innodb
Win.Innodb
SuSE.MyISAM
SuSE.Innodb
Win.MyISAM
550
20000
15377
15000
SuSE.MyISAM
461
450
397
400 350
12500
250
7500
200
7193 6087
150
5000
382
218 226 137
156
100
3183 1899
386 311
300
10000
Win.MyISAM
524
500
17874 1717216990 16218
17500
2500
Win.Innodb
50
1239
0
0 1
16
256
1
16
256
20
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | Š MySQL AB 2006 | www.mysql.com
IO Bound Benchmarks
21
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
IO Bound Benchmarks • •
100.000.000 rows (about 20GB) Only Innodb Tables – Did not have time for MyISAM
•
Results are not always stable – Fragmentation, background activity etc
•
Uniform distribution – Even data access. Most hard for disk, bad for caches
•
Special Distribution – Very Skewed – Much better cache rate – Close to applications with Mixed CPU/DiskIO load
22
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
O_DIRECT PowerEdge 1425SC Dell PowerEdge 1425SC – 4GB RAM
•
– 2SATA 7200 Drives in software RAID1 – ReiserFS
O_DIRECT is expected to benefit on high end systems
•
– This is not the one O_Direct vs Cached IO ODIRECT
No ODIRECT 50
50
50
45 40 35 30
29 26
25 20
20
17
15 10
12
11
12
13
5 0 RWU-1
RWU-16
RWS-16
ROU-1
ROU16
23
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
O_DIRECT PowerEdge 2850 Dual Xeon 3.0 Ghz, 8GB RAM, 6*10.000RPM SCSI
•
– 4 Drives in RAID5 – EXT3
O_DIRECT is better if performance is same
•
– Saves your OS cache for other applications O_Direct vs Cached IO ODIRECT
No ODIRECT
200
192 176
175 150 125 100 75 50 33
25
29
23
19
0 RWU-1
RWU-16
RWS-16
24
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
PE 2850 vs PE 1425SC Using RAID1 on both of them
•
– PE 2850 has hardware RAID – PE 1425SC uses software one
Memory sizes 4GB vs 8GB not only disk
•
Dell Poweredge 2850 and 1425SC PE1425SC
PE2850
200 178
175 150 125 100 75 50
50 25
29
24 12
11
0 RWU-1
RWU-16
RWS-16
25
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Linux kernel IO Schedulers In Linux 2.6 you can select how your disk IO is scheduled
•
– – – –
Cfq (Completely Fair Queueing) - default for CentOS 4.2 Noop (No scheduling) Deadline AS (Anticipatory) IO Schedulers CFQ
Deadline
Noop
AS
55 50
50
51
49
51
45 40 35 30
30 25 20 15
26 18 12
12
12
10 5
11
13
14
12
18
30 26
18
15
13
0 RWU-1
RWU-16
RWS-16
ROU-1
ROU16
26
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Linux FileSystems PowerEdge 2850, 4 Drives in RAID5. O_DIRECT Innodb has low requirements about filesystems
• •
– Single file tablespace, which is preallocated
JFS is very unexpected winner
•
FileSystems EXT3
EXT2
ReiserFS3
XFS
JFS
200
194
193
195
198
183
175 150 125 100 75
72
50 35
25
18
15
16
14
35
36
37
37 26
19
33
28
78
83 73
35 24
0 RWU-1
RWU-16
RWS-16
ROU-1
ROU16
72
27
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Filesystem Journal Options Only tested for EXT3 and ReiserFS Writeback gives good boost on ReiserFS Especially helps for ReadOnly load. Why ?
• • •
– Might be due to last access time. -o noatime ? Filesystem Journaling Options E.ordered
E.Writeback
E.journal
R.ordered
R.writeback
200 183
186
193
198
182
175 150 125 100 75
72
50 35
25
18
17
15
16
35
35
36
38 26
32
27
28
77
71
35
14
0 RWU-1
RWU-16
RWS-16
ROU-1
ROU16
73
80
28
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
RAID Levels RAID0 is best performance but insecure
•
– Can be used for slaves
2 Drive RAID1 may outperform RAID5 for Writes RAID10 is good. The difference with RAID5 is a lot controller dependent
• •
RAID Levels RAID1
RAID5
RAID10
RAID0
300 275 250 225 200 175 150 125 100 75 50 25 0
288
209 178
183
72
24
18
22
RWU-1
30
29
35
41
RWU-16
40
41
41
68 53
26
RWS-16
ROU-1
ROU16
29
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
RAID Block Sizes Innodb page size is 16K
•
– So smaller block sizes are worst
Optimal size a lot depends on RAID controller Large block sizes (256K+) are expected to be best for OLTP
• •
RAID5 Block Sizes 16K
64K
128K
225 204
200
183
175 150
147
125 100 75
72 55
50 25
15
18
20
27
35
41
50
38 24
26
0 RWU-1
RWU-16
RWS-16
ROU-1
ROU16
30
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Raid5 Degraded Degraded Mode – one of hard drives pulled out Why to check in degraded mode ?
• •
– Because you need to be prepared – So your system does not go down because of overload
Results look really strange but drive was really pulled out
•
RAID5 Degraded Normal
Degraded
200 183
178
175 150 125 100 75
72 62
50 35
25
28
21
18
33
26
0 RWU-1
RWU-16
RWS-16
ROU-1
ROU16
31
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
RAID10 Read Ahead RAID Read-Ahead Configuration Adaptive (default)
• •
– Read full stripe if more then one access to the stripe
Read-Ahead Adaptive
None
225
209
200
200 175 150 125 100 75 50 25
41 22
44
41
40
53
52
22
0 RWU-1
RWU-16
RWS-16
ROU-1
ROU16
32
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Overhead of LVM LVM – Linux Volume Manager
•
– Tests are perfomed with LVM2 by default
Really good practice
•
– Simplifies, speeds up MySQL backup etc
Normally very love overhead
•
LVM
LVM
No LVM
225
211
209
200 175 150 125 100 75
25
55
53
50
41 22
41
40
33
20
0 RWU-1
RWU-16
RWS-16
ROU-1
ROU16
33
MySQL Users Conference 2005 , April 24-27 | MySQL Performance Landscape | © MySQL AB 2006 | www.mysql.com
Thats all ! •
Share you benchmarks experience with me at peter@mysql.com – Also send me your questions if you did not have chance to ask them on the conference
•
Special Thanks for helping to gather performance data – – – –
Vadim Tkachanko Aleksey Kishkin Alexey Kopytov CJ Collier
34