OSCON Day 5: Session – MySQL Scaling Pains
This session is given by Jeremy Zawodny. He works for Yahoo as their MySQL guy with their Platform Timing Group. He
also actively contributes to testing MySQL and he personally knows Monty Widenius (Co-Founder and chief developer
of MySQL).
MySQL at Yahoo
200 – 400 servers world wide
they use FreeBSD and Linux
they have mission critical systems
use “live” and “batch” processing
use replication and load-balancing to scale. they use a hardware load balance for their database systems
Problems
need to scale
query optimization
security
more from the slides which can be found here
Need to scale
increase demand (queries/sec)
lots of front-end clients
growing data size
application complexity and features
more developers/DBAs in the mix
Back to basics
benchmark and analyze – how expensive are your queries
optimize – design to scale up
Tune – adjust setting of your config file
Query optimization
slow query log
set “long-query-time” threshold
Too many cooks
need documentation
who owns the db
have naming conventions and procedures
comment the config file and use CVS
have areas of responsibilities
– who does backups
– who runs upgrades
Security
don’t share root access
login with your user account first
ssh from the outside to your server
don’t give applications “ALL PRIVILEGES on
.
“, this is the big GRANT
Size Limits
MyISAM tables have the mysterious 4GB size limit, but this really is a config change
set “MAX_ROWS” attribute in “CREATE TABLE” or use “ALTER TABLE”
Lock Contention
MyISAM tables use table locks
– many readers and 1 writer
watch your read/write rates
– many reads, few write : good
– many writes, few reads : good
– many of both : bad
SHOW STATUS
– Com_select, Com_insert
– Com_update, Com_delete
– Table_locks
watch the lock counters
– 100:1 ratio = good (read/write ratio)
optimize queries
double check indexes
Alter table is slow
very disk i/o intensive
MySQL rewrites all indexes every time
– make several index changes at once
– make sure your key_buffer is big
– make sure your myisam_key_buffer is big
– put the temp tables on different disks
alter table off line if possible
Disk I/O issues
disks are often the bottleneck
if CPU sin’t nearly saturated, check disk i/o stats: vmstat, iostat, sar, etc…
disks w/low seek times are good, so buy them
SCSI disks are better for multi-threaded random i/o
Raid 0 or manual i/o balancing helps – this is fast (for slave machines)
Raid 10 – setup on the master
a controller with RAM cache is a plus
for IDE disks, check the DMA settings
benchmark the server and disks combos
Load Balancing
time your servers
replication
monitoring
– track critical performance numbers
– watch trends over time
– develop reasonable health checks
– use hardware/software load balancers (lVS for example)
Handle many connections
understand MySQL’s memory usage
global variables
“wait_timeout” is your friend, keep it low
connection pooling may be helpful
some thread libraries use limits – 1024
Data partitioning
max cache utilization
FULLTEXT Searching
need online presentation
Like this:
Like Loading...
Comments Off on OSCON Day 5: Session – MySQL Scaling Pains
Charles Engelke's Blog 