mysql scaling

MySql Scaling

Presenters:Chris SchneiderAnd Kevin Knapp

Intro

Consolidation methodologies Local vs Shared

Local storage, NFS, iSCSI, Fiber File systems

EXT3, XFS, VXFS and ZFSSun hardware

Innodb PerformanceSharding

Local vs. SAN

ConsiderationsPartitioningType of disk and setupBackups

LVM, mysqldump, parallel, rdiff, innodb hot backup

The key is to BenchmarkSysbenchSupersmackHome grown

Test on different file systems

EXT3Traditional and widely used

XFSHas been great to work with in my

experienceShow best performance in in my

benchmarksZFS

Snapshots with no penalties

The Tortus and the Hare

WaterfallsSeven FallsNiagra

The case study:Shared hosting environment

Hundreds of thousands of users Users with almost full autonomyMySql 4.0, 4.1 and 5.0

Comparison Breakdown

Dell 2950 Sun T2000 Sun T5220 Sun T5240 SB6000/6300 SB6000/6320RU 2 RU 2 RU 2 RU 2 RU 10 RU 10 RU

CPU Xeon 5148 T1 T2 T2 Plus T1 T2Speed 2.33 GHz 1.20 GHz 1.20 GHz 1.20 GHz 1.20 GHz 1.20 GHzCache 4 MB 3 MB 4 MB 4 MB 3 MB 4 MBCPU Count 2 1 1 2 10 10# Cores/CPU 2 8 8 8 8 8Total Cores/Server 4 8 8 16 8 8HW Threads per Core 1 4 8 8 40 80HW Threads/Server 4 32 64 128 320 640

MySql Instances Focus

Dell 2950 Sun T2000 Sun T5220 Sun T5240 SB6000/6300 SB6000/6320

CPU Xeon 5148 T1 T2 T2 Plus T1 T2HW Threads/Server 4 32 64 128 320 640

Instances of mySQL 4 8 16 32 80 160

Total Instances 752 Instances 752 Instances 752 Instances 752 Instances 752 Instances 752 InstancesTotal Systems 188 Systems 94 Systems 47 Systems 24 Systems 9 Systems 5 SystemsTotal Rack Units 376 RU 188 RU 94 RU 48 RU 90 RU 50 RUTotal Racks 10 Rack 5 Rack 3 Rack 2 Rack 3 Rack 2 Rack

Systems Focus

Dell 2950 Sun T2000 Sun T5220 Sun T5240 SB6000/6300 SB6000/6320

CPU Xeon 5148 T1 T2 T2 Plus T1 T2HW Threads/Server 4 32 64 128 320 640

Instances of mySQL 4 8 16 32 80 160

Total Instances 752 Instances 752 Instances 752 Instances 752 Instances 752 Instances 752 InstancesTotal Systems 188 Systems 94 Systems 47 Systems 24 Systems 9 Systems 5 SystemsTotal Rack Units 376 RU 188 RU 94 RU 48 RU 90 RU 50 RUTotal Racks 10 Rack 5 Rack 3 Rack 2 Rack 3 Rack 2 Rack

Number of Systems

Number of Systems Required

0

50

100

150

200

Dell 2950 Sun T2000 Sun T5220 Sun T5240 SB6000/6300 SB6000/6320

Datacenter Space

Total Rack Units Required

0

100

200

300

400

Dell 2950 Sun T2000 Sun T5220 Sun T5240 SB6000/6300 SB6000/6320

Performance

Memory Allocations Optimized SQL Normalize till it hurts denormalize till it works Innodb vs MyISAM

Transactions and concurrent inserts

Timing and location of script and backup execution

Recoverability Point in time

Q & A

Questions