Archive for April 27th, 2007

MySQL Conference – YouTube

Friday, April 27th, 2007

MySQL Conference 2007 Day 4 rolled quickly into the second keynote Scaling MySQL at YouTube by Paul Tuckfield.

The introduction by Paul Tuckfield was; “What do I know about anything, I was just the DBA at PayPal, now I’m just the DBA at youTube. There are only 3 DBA’s at YouTube.”

This talk had a number of great performance points, with various caching situations. Very interesting.

Scaling MySQL at YouTube

Top Reasons for YouTube Scalability

The technology stack:

  • Python
  • Memcache
  • MySQL Replication

Caching outside the database is huge.

It a display of numbers of hits per day it was said “I can neither confirm or deny the interpretation will work here (using an Alexa graph)”. This is not the first time I’ve heard this standard “Google” response. They must get lessons by lawyers in what you can say.

Standardizing on DB boxes (but they crash almost daily)

  • 4x2ghz opteron core
  • 16G RAM
  • 12x10k scsi
  • LSI hardware raid 10
  • Replication played a big part in fixing
  • Get a reliable H/W supplier

Replication Lessons

  • You don’t worry about it when a replicas fail.
  • One thing that sucks, Innodb doesn’t recover very fast. It does that durability think, but it takes hours to finish recovering (was it going to finish)
  • How many backups can you restore. When you switch you a replica, are you sure it’s right?
  • Did you test recovery, did you test your backups.
  • replication was key to trying different H/W permutations to identify incompatible H/W (combinations of controllers/disks)
  • we got good at re-parenting/promoting replicas, really fast
  • we built up ways to clone databases as fast as possible
  • Excellent way to test tuning changes or fixes (powerful place to test things)
  • Keep “intentional lag”/Stemcell replicas – Stop SQL thread, keeps a server a few hours or a day behind. Say if you drop a table you have a online backup.
  • When upgrading, always mysqldump then reload, rather then upgrade database.
  • Don’t care about CPU’s. I want as much memory as possible, I want as many spindles as possible.
  • For YouTube 2-3 second lag is acceptable.

If you db fits in ram, great otherwise

  • Cache is king
  • Writes should be cached by raid controller (buffered really) not the OS
  • Only the db should cache reads (not raid, not Linux buffer cache)

Only DB should cache reads

  • Hit in db cache means lower caches went unused.
  • Miss in db cache can only miss in other caches since they’re smaller.
  • Caching reads is worse then useless. It’s serialized writes.
  • Avoiding serialization in reads reaps compounds benefits under high concurrency

An important lesson learned. Do no cache reads in F/S and Raid Controller.

Caching Lessons
Overcoming Mystery Serialization

  • Use O_DIRECT
  • vm.swappiness=1-5
  • if you’re >80% buys — your not doing I/O concurrently look at other figures e.g. 80% busy 8 I/O’s, next configuration 80%, only 4 I/O’s
  • Mirror in H/W strip in S/W

Scale Out

  • Writes are parallel to master, but serialized to replicas. We need true horizontal partitioning.
  • We want true independent masters
  • EMD – Even More Databases — Extreme Makeover Database
  • Slave transactions must serialize to preserve commit order (this is why replication is always way slower)
  • The oracle caching algorithm (that’s a small o) — predicting the future
  • Replication lags: one IO bound thread. You do know the future, commands are coming up serially.
  • Write a script to do reads, before updates coming up (because they are cache hits).
  • The diamond. For golive, play shards binlogs back to original master for fallback.

MySQL Conference – Get Behind Dorsal Source

Friday, April 27th, 2007


In a community session yesterday at MySQL Conference 2007, I first heard about Dorsal Source. A collaboration between Solid DB and Proven Scaling that allows for community people to upload patches to MySQL, get it compiled across multiple platforms, and have a downloadable distribution available on H/W individual contributors will never have access to.

That’s a great idea. I also hope we get the opportunity to get compiling of patches into multiple versions, as well to get builds of a lot of patches together. Personally, I’m running 3 versions just to diagnose one problem. 5.0.36 with a custom binary change, 5.0.37 so I have SHOW PROFILE, and 5.0.33 so I have microslow patch.

With new patches becoming available from the community, I hope I can see builds that combine all known patches that Dorsal Source may have.

I think this is going to be a great project.

MySQL Conference – PHP on Hormones

Friday, April 27th, 2007

MySQL Conference 2007 Day 4 started early again at 8:20 am with PHP on Hormones by the father of PHP Ramus Lerdorf.

A very funny man, one of the best insightful talks of the conference (rather scary actually). Here are some opening comments.

  • In his own words as Keynote speaker. “I’m here because I’m old”.
  • Php 1 from 1994 started after seeing Mozilla in 1993. Because it was just me using it, I could change the language any time.
  • In 2005 the code looks like this (in comparison on 1995) — I’m not sure if this is worth 10 years of development
  • I wrote PHP to avoid programming
  • It’s changed to be more OO because people expect that. Universities teach this.
  • Hey, I was fixing bugs in my sleep. Iwould wake up, and in my mail box there would be bug fixes to bugs I didn’t even know I had.

Why do people contribute?

  • Self-interest
  • self expression
  • hormones
  • Improve the world

The slide included a great Chemical equation of “The Neuropeptide oxytocin” — Nature’s trust hormone

People need to attract other people, it makes you feel good, it comes out when you interact with people.

It’s not what people think about you, but rather what they think about themselves.

  • PHP was my baby, giving up control, just because I started it, doesn’t mean I have a bigger say in it.
  • Systems that harness network effects and get better the more people use them in a way that caters to their own self-interest. — Web 2.0
  • Once you build a framework your done, the users build the site, they drive the content.
  • The same people that work on open source projects, are the same people that use websites.
    • Self-interest
    • self expression
    • hormones
    • Improve the world

1. Performance
It your sites falls apart your done.

  • Benchmark
    • http_load
    • Callgrind inside valgrind
    • XDebug

valgrind –tool=callgrind

  • Excellent tool to see where time is spent in the code. You have to run a profiler.
  • Example of using Drupal. It turns out 50% of time was spent in the them, it had 47 SQL queries, 46 Selects.
  • Went from 4 per second to 80 per second, without any code changes. Some performance options, and some caching.
  • Guaranteed you can double the speed of your website by using a profiler.

2. Security
Critical problem areas.

  • 404 pages
  • Search page
  • PHP_SELF
  • $_GET, $_POST, $_COOKIE
  • $_SERVER
  • Lots of stupidity in IE (e.g. Always send a charset)

The web is broken you can all go home now.

People are venerable because people run older versions of browsers, and their data is not secure, and you can’t secure their data.

What can happen??
9 out of 10 of you have cross-site scripting hole on your site

Remote Greasemonkey
Profile Hacks
JS Trojans

Added a PHP logo to the MySQL User Website, it’s really the PHP website
IBM webpage, on article about security.

Tool to find holes, banks, insurance companies, CIA, even Yahoo where I work.

You know if they have been to bankofamerica.com, you can tell if they are a customer, you can tell if they are logged, you can then see their cookie credentials.

You don’t know if any sites have these problems.

JS trojan, iframe that captures
reconfigures your wireless router, moves it outside your DMZ, then uses traditional techniques to attack your machine (that you thought was secure inside a firewall)

You should never ever click on a link. It sort of defeats the purpose of the web.

Never use the same browser instance to do personal stuff and browsing.

So what are we doing about this?
There isn’t much we (PHP) can do to secure sites developed.
Built a filter extension in 5.2, back in 5.1.

http://php.net/filter *** YOU MUST IMPLEMENT THIS
filter.default=special_chars

3. APIs are Cool!

Two lines to grap the Atom feed from flickr of photos just uploaded.
That’s all I have to add to my code.

The really make you want to use the servers. It’s so easy.

API drives passion, drive people to use your site.
You can add a lot of cool things to your sites.

What to do

  • Avoid Participation Gimmicks
  • Get their Oxytocin flowing
  • Solve One Problem
  • Clean and Intuitive UI
  • API’s
  • Make it work

A full copy of the slides can be found at http://talks.php.net/show/mysql07key

MySQL Conference – Google

Friday, April 27th, 2007

MySQL: The Real Grid Database

Introduction

  • Can’t work on performance problems until we solve the availability
  • We want MySQL to fix our problems first.

The problem

  • Deploy a DBMS for a workload with
    • too many queries
    • to many transactions
    • to much data

A well known solution

  • deploy a grid database
  • -use many replicas to scale read performance
    -shard your data over many master to scale write performance
    -sharding is easy, resharding is hard

  • availability and manageability trump performance
  • - make it easy to run many severs
    - unbretable aggregate perfomance

  • we describe problems that matter to us.
  • The grid database approach

    • Deploy a large number of small servers
    • use highly redundant commodity components
    • added capacity has a low incremental cost
    • not much capacity lost when a server fails
    • support many servers with a few DBAs

    Managability
    Make it easy to do the tasks that must be done. Reduce, Reduce.
    Make all tasks scriptable
    Why does it mater, support hundreds of servers, spend time solving more interesting problems. You generally have lots of problems to solve.

    Underutilize your severs
    Require less maintenance
    Requre less tuning
    tolerate load spikes better
    tolerate bad query plans better

    In a Perfect World
    Short running queries
    uses kill mistake and runaway queries
    accounts new use to many connections
    query plans are good
    new apps increase database workload by a small amount
    only appropiate date is stored in the database

    Reality

    • Long running transactions, create replication delays everywhere
    • servers with round robin DNS aliases make queries hard to find
    • applications create more connections where the database is slow
    • some storage engines use sampling to get query plan statistics
    • new applications create new database performance problems
    • applications use the database as long as rows are never deleted
    • many long running queries on replicas

    Solutions

    • Improve your ability to respond because prevention is impossible
    • Need tools to make monitoring easier
    • determine what is happening across servers
    • detemine what happened in the past

    Mantra

    • Monitor everything you can, and archive as long as possible. (vmstat 5 secs, iostat, mysql error logs)
    • You will need these to reconstruct failures
    • save as much as possible
    • script as much as possible

    Monitoring Matters

    • Display what is happening
    • -which table, account or statemsns caused most of the load
      -many fast queries can be as much a problem as one slow query

    • Record what happending
    • –archivce show status counters somweere
      - query data from the archive
      – visualise data from the archivce

    • record queries that have been run
    • – archive show processlist output (do every 30 seconds)
      – support queries on this archive

    • All of this much scale to an environment with many servers

    Monitoring Tools

    • Display counters and rate change for counters
    • aggregate values over many servers
    • visualize and rang results
    • display results over time

    Google mpgrep tools

    New Commands
    We changed mysql, three new commands
    SHOW USER _STATISTICS
    SHOW TABLE STATISTICS
    SHOW INDEX STATISTICS

    Per Account Activity
    USER_STATISTICS
    seconds executing commands
    number of rows fetched and changed
    total connections
    number of select/updates/other/commits/rollback/binlog bytes written.

    TABLE STATISTICS
    number of rows fetched/changed

    INDEX STATISTICS
    display number of rows fetched per index
    helps find indexes that are never used

    available in code.google.com in 4.0, porting to 5.0

    MySQL High Availability

    • Great options
      • Cluster
      • Replication
      • Middelware — e.g. continum
      • DRBD
    • We need some features right now
    • we are committed to innodb and mysql replication
    • *a lot of appplicaton code works on this
      *our tools and processed support this

    • We favor commodity hardware

    There are all great features but we are much more limited in what we can use.
    Management want to know we don’t loose transactions, not loose some transactions.

    Desired HA Functionality

    • Zero transaction loss on failures of a master
    • minimal downtime on failures of a master
    • reasonable cost in performance and dollars
    • fast and automatic failover to local or remove server
    • no changes to our programming model
      • does it support MVCC
      • does it support long running transactions (5 mins – populate temp table then use to update another table, changing rows), 5 mins on master, causes 5 mins on slave, causes code to failover from slaves to master

    • replication and reporting are concurrent on a slave

    MVCC must have update concurrent with query.

    Failures happen everywhere
    OS – kernal oom or panic (older 2.4 32 bit systems)
    mysqld – caused also by code we added
    disk, misdirected write, corrupt write (love innodb checksums)
    file system – inconsisted after unplanned hardware reboot (use ext2)
    server – bad RAM
    lan, switch – lose
    Rack – reboot
    Data center – power loss, overheading, lightning, fire
    People – things get killed or rebooted by mistake ( a typo can take out the wrong server, when names differ by a character or a digit)

    ext2 and 4.0 are great, there are the same generation.
    Trying not to use RAID, not battery backed raid etc, we try work around with software solutions. We do use RAID 0, but we also try software solution.
    When we have the right HA solution, we won’t need RAID.

    Mark. “Yes, Google programmers have bugs. Not me personally, it was my predecessor.”

    HA Features we want in MySQL
    Synchronous replication as an option
    a product that watches a master and initiates a failover
    archives of the master binlogs stored elsewhere
    state stored in the filesytstem t obe consistent after a crash
    . innodb and mysql dictionaries can get out of sync
    .replicatoin state on a slave can get out of sync

    We could not wait
    Features we added to MySQL 4.0.26
    We can do things a lot faster
    . we have more developers lying around
    . Our needs as specific, not a general product solution

    Transactional replications for slaves
    semi-synchronous replication
    mirrored binlogs
    fast and automated failover

    Transactional Replication
    Replication state on a slave is stored in files
    slave sql thread commits to storage engines and then updates a file
    a crash between the two can make replication state inconsistent
    transactional replication
    MySQL can solve this in the future by storing replication state in tables

    Semi-synchronous replication
    Block return from commit on a master until at least one slave has acknowledged receipt of
    slave io thread acknowledges receipt after buffering the changes
    modified mysql replication protocol to support acknowledgments
    conifuration options
    where to the master uses it
    where a slave used it
    how long the maser waits for an acknowledgement

    can run a server with some semi-sync replication slaves and some regulare replication salves
    this can be worked with any storage engines that supports commit, but we only use innodb

    * This is how we guarantee to management for Zero Transaction Loss.

    Latency single stream 1ms, multi-stream 10ms. This is acceptable for us.

    The MySQL Replication Protocol

    • The current replication protocol is efficient
    • a slaves makes one request

    Replication Acknowledgment

      Slaves register as semi-sync or async at connect time
      prepend flag bytes to all replication events sent to semi-sync clients
      the master sends the flag bytes to request acknowledged for replication events that represent the end of the transaction
      the slave use the existing connection for acknowledgments

    Mirrored Binlogs
    mysql does not provide a way to maintain a copy of a master’s binlog on a replica. By copy we me a file of same name and equivalent byte for byte.
    Hierarachial replication works much better where a slave can disconnect from one replication proxy and reconnect to another with adjusting binlog offsets.
    Hot backups taken before a failover and difficult to use after a failover

    Mirrored Binlog Implementions
    Slave IO threads write their own relay log and a copy of the bin log
    all events but the rotate log event are written

    After failover, start a new binlog on new master

    Fast Failover

    Slaves use a hostname, rather then an IP
    You can’t enable the binlog dynamically (in 4.0)
    Added new SQL STATEMENTS that does
    disconnect users with SUPER privilege
    disable new connections
    enable the bin log
    enable connections from all users

    Automatic failover
    Something must decided that a master has failed
    Something must choose the new master

    Q: What keeps up from moving to 5.0?
    A: Queries don’t parse (Joins)

    Data sets, 8GB servers, 50-100GB’s

    Quote – 26 April 2007

    Friday, April 27th, 2007
    “The web is broken you can all go home now.”

    Ramus Lerdorf — Father of PHP — MySQL Conference 2007