This past week was the annual MySQL Users’ Conference. It was my fourth time attending the conference, my second as a MySQL-er, and my first as a Sun employee.

The conference was very well attended with many more people than last year. Overall, I’d say it was a great success.

I work on the Backup project for MySQL and I must say it was a very strange experience being in the spotlight of so much controversy concerning the announcement made about making some backup features enterprise only releases.

While there was a lot of blogging expressing the general distaste for the suggestion of making some features of backup enterprise only (as opposed to free), most of these comments seemed to come from a relatively small number of people.

I presented the Backup session and led the Birds of a Feather session on Backup. I must say that the sentiments of the bloggers was not represented in the audience of these sessions. I …

Once again I spent a week in the San Francisco Bay Area. This time around it was for LugRadio Live USA 2008 and the MySQL Conference & Expo 2008.

I flew in on April 12 (from FRA via LAX to SJC) and arrived in the hotel, the Hyatt Regency Santa Clara, in the afternoon.

The next morning I left the hotel around 7:00 in the morning for a genuine experience of what they call public transportation in the bay area. It took me three hours to travel the distance of …

• http://en.oreilly.com/mysql2008/public/schedule/detail/874
• presented by Charles Lee of Hyperic
• Hyperic has the best performance with MySQL out of MySQL, Oracle, and Postgres in their application
• I suddenly remember hyperic was highly recommended above nagios in MySQL Conference Liveblogging: Monitoring Tools (Wednesday 5:15PM)
• performance bottleneck
  • the database
    • CPU
    • memory
  • IO …

• Damien Seguy of Nexen Services presents
• easiest session of all (phew, that's a relief)
• clever SQL recipes
• tweaking SQL queries
• shows an example where SELECT is ORDERED by a column that is actually an enum.
  • an enum is both a string and a number
  • sorted by number
  • displayed as string
  • can be sorted by string if it's cast as string
• compact column
  • compacts storage
  • faster to search
  • if (var)char is turned into enum, some space can be saved, shows example
• random order
  • order by rand(1) - obviously
  • the integer parameter is actually a seed
  …

• Tom Hanlon of MySQL presents
• monitoring tool basics
  • SHOW FULL PROCESSLIST
  • SHOW GLOBAL STATUS
  • SHOW GLOBAL VARIABLES
• basic tools
  • mysqladmin is provided with the server
    • mysqladmin -i 10 extended status: will repeat the same command every 10 seconds. Pipe through grep "and smoke it" (bad pun, hah hah)
    • -r: show only changed values
  • MySQL Administrator
• cacti
  • rrdtool based network graphing tool
  • uses snmp
  • PHP apache and MySQL based solution
  • MySQL plugins, download and install
  • "poller" gathers data and populates the graphs
  • someone offers …

• Tom Hanlon of MySQL presents
• Benchmarking tools
  • mysqlslap (with MySQL 5.1)
  • sql-bench
  • supersmack - Jeremy Zawodny's tool
  • Apache Bench (combined with some sample PHP scripts)
  • MySQL's benchmark() function
  • …

• Paul McCullagh presents
• BLOB
  • invented by Jim Starkey
  • Basic Large OBject
  • Binary Large OBject
  • photos, films, mp4 files, pdfs, etc
• how MySQL handles BLOBs
  • mysql client send buffer -> receive buffer on the server (max_allowed_packet)
  • streaming a BLOB
    • continuous data stream
    • stream BLOB data directly in and out of the database
    • store BLOBs of any size (>4GB) in the database
    • create a scalable back-end that can handle any throughput and storage requirements. Wouldn't need to know in advance how big the database will get
    • provide an open system that can be used by all engines
    • provide extensions for …

• Jay Pipes, Tobias Asplund
• Finding out the number of rows that would have been returned (MyISAM and InnoDB)
  • SQL_CALC_FOUND_ROWS and FOUND_ROWS()
  • COUNT(*)
  • MEMORY table
  • if query cache is on, then it makes no difference
  • if it's off
    • Memory MyISAM is fastest
    • FOUND_ROWS() is slightly slower than count(*)
  • more in the slides that I'll add later
• quite a lot of humor, these guys are fun
• query union vs index_merge union
  • SELECT … WHERE a UNION SELECT … WHERE b
    vs
    SELECT … WHERE a AND b
  • index_merge wins
• composite index vs index merge
  …

• Phil Hilderbrand of thePlatform for Media, Inc presents
• classic partitioning
  • old school - union in the archive tables
  • auto partitioning and partition pruning
  • great for data warehousing
  • query performance improved
  • maintenance is clearly improved
• design issues in applying partitioning to OLTP (On-Line Transaction Processing)
  • often id driven access vs date driven access
  • 1 big clients could be 80% of the whole database, so there's a difficulty selecting partitioning schemes
• partitioning is only supported starting from MySQL 5.1
• understanding the …

• Robert Hodges from Continuent presents
• About Continuent
  • leading provider of open source database availability and scaling solutions
• solutions
  • uni/cluster - multi-master database clustering that replicates data across multiple databases and load balances reads
  • uses "database virtualization"
• scale-out design motivation
  • protection from db and site failures
  • continuous operation during upgrades
• how come not everyone has it already?
• creating identical replicas across different hosts is hard
  • Brewer's conjecture
• trade-offs
  • DDL support
  • inconsistent reads between replicas
  • deadlocks
  • sequences …