Charles Engelke's Blog

July 13, 2003

OSCON Day 5: Session – MySQL Scaling Pains

Filed under: Ozette Brown — Charles Engelke @ 6:19 pm

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

    Advertisements
  • Blog at WordPress.com.

    %d bloggers like this: