beer planet

• Suicide
• having no backups
• depending on slaves for backup
• keeping backups on same SAN
• having a single DBA - Frank didn't like this one at all
• not keeping binlogs
• Restoring from backup
• how much time?
• uncompressed backup ready to mount?
• separate network for recovery?
• In Fotolog, 1TB of data was severely hit.
• first problem: backup was highly compressed (tar.gz)
• uncompressing took hours
• so keep uncompressed backups (at least last N days)
• it should be mountable, rather than transferable
• Frank going over recovery modes at http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
• Row by row recovery
• row by row recovery (get the range of ids)
• custom scripts
• may not be able to use primary key
• foreign key based retrieval faster
• lose 4 seconds for each crashed record (in Fotolog, for some reason some values were crashing mysqld)
• Lessons
• SANs make sense (in some environments)
• try to replicate the whole SAN (in Fotolog, a SAN actually failed because of a bug in its maintenance program)
• everything will fail at some point
• backup everything (cron jobs, my.cnf, custom scripts)
• have backup in a form ready to restore
• don't count replication a backup
• be worried about 'routine' operations
• Peter Zaitsev of Percona takes the stage to talk about his homegrown tools for InnoDB recovery
• innodb-tools - will recover even if mysqld doesn't start, for example if half of RAID0 fails or somebody deleted some data. innodb-tools will recover using InnoDB tablespaces.
• We're out of time

Updated: April 17th, 2008

I had a chance to visit the Kickfire booth after the keynotes and before the first presentation. They gave me a kicking t-shirt, followed by a presentation on the newly announced Kickfire appliance (now in beta, shipping in Fall 2008). Here are some notes I jotted down:

• von Neumann bottleneck
• SQL chip (SQC), packs the power of 10s of conventional CPUs
• Query parallelization on the chip
• On-chip memory - 64GB. No registers - no von Neumann bottleneck
• Beats the performance of a given 3 server, 32 CPU, 130TB box (1TB of actual data - space is used for distributing IO)
• SQC uses column-store, compression, intelligent indexing
• SQL Chip, PCI connection, plugs into a Linux server
  • SQL execution
  • Memory management
  • Loader acceleration
• KDB (Kickfire storage engine), plugs into MySQL
  • Optimizer
  • Transactional engine
  • Column store & cache
• Kickfire appliance size is 2U or 3U
• Highest performing MySQL related database offering
• Starts at $20k (10x performance of similar priced offerings)
• Point and go, point the appliance at the existing db and it sucks the data in
• Up to 3TB database
• Percona ran a test of some Dell box with MySQL vs Kickfire Appliance and Kickfire is 1000x faster

So my questions are:

• does it support foreign keys? The presenter answered yes.
• how does it handle replication? The presenter said it should be addressed in the future. Still unclear on this one.

Update 1: In the latest TPC-H results, Kickfire placed at #1, outperforming all competition by a long margin. The cost per QphH (Query-per-Hour) is only 70 cents! The nearest competition is $3+.

Update 2: Kickfire got an incredible amount of attention at this conference, I think it's everything they'd hoped for and a lot more. When some independent respectable benchmarkers, like Peter, actually get their hands on a sample appliance and post some real life tests, we will truly be able to judge on the performance, but if PR was an indicator of anything, Kickfire will have an insanely successful future.

• Baron Schwartz presents
• only works for SELECTs
• nobody dares admit if they've never seen EXPLAIN
• MySQL actually executes the query
• at each JOIN, instead of executing the query, it fills the EXPLAIN result set
• everything is a JOIN (even SELECT 1)
• Columns in EXPLAIN
• id: which SELECT the row belongs to
• select_type
• simple
• subquery
• derived
• union
• union result
• table: the table accessed or its alias
• type:
• join
• range
• …
• possible_keys: which indexes looked useful to the optimizer
• key: which index(es) the optimizer chose
• key_len: the number of bytes of the index MySQL will use
• ref: which columns/constants from preceding tables are used for lookups in the index named in the key column
• rows: estimated number of rows to read
• extra
• using index: covering index
• using where: server post-filters rows from storage engine
• using temporary: an implicit temp table (for sorting or grouping rows, DISTINCT). No indication of whether the temp table is in memory or on disk
• using filesort: external sort to order result. No indication of which algorithm MySQL will use
• shows an insane EXPLAIN output with 8 EXPLAIN rows
• maatkit includes a tool called mk-visual-explain, which can construct a formatted tree
• Baron shows a demo and answers questions
• EXPLAIN EXTENDED followed by SHOW WARNINGS will give more output about how a query is executed

• Robin Schumacher
• gives overview of MySQL products
• MySQL Enterprise
• MySQL 5.1 announced
  • table/index partitioning -> great for data warehouses, range, cache, key, list, composite, subpartitioning. Partition pruning. Response time greatly improved with proper partitioning.
  • row-based/hybrid replication -> safer and smarter
  • disk-based cluster -> supports bigger DBs
  • built-in job scheduler -> simplified task management
  • problem SQL identification -> easier troubleshooting. Dynamic query tracing is now available, no need to trace things in slow query logs.
  • faster full-text search -> 500% increase in some cases
  • 5.1.24RC available for the conference
• MySQL 6.0
  • Falcon engine - transactional engine
  • new backup (version 1.0) -> cross engine, non-blocking, to replace mysqldump
• Falcon
  • planned default transactional storage engine. Q4 GA (general availability).
  • not InnoDB replacement
  • most InnoDB apps are OK on Falcon
  • crash recovery
  • ACID transactions
  • more features
  • best on multi-CPU, large RAM servers
  • planned to beat InnoDB
• shows latest internal Falcon vs InnoDB benchmarks, all benchmarks have Falcon winning now (dual and quad quadcore CPUs), compared to before
• new backup in 6.0
  • all general engines supported (except for Cluster)
  • SQL-command driven
  • online, non-blocking DML (insert,update,delete) for transactional engines. MyISAM is still blocking (at least for now)
  • point-in-time recovery
  • better recovery times in benchmarks
  • restore is blocking
  • plugins for the backup tool
    • first one is a non-blocking MyISAM plugin
    • compression plugin
    • encryption plugin
• new optimizer enhancements in 6.0
  • example shows 99.75% improvement, seems like a very edgy edge case
• High Availability
  • MySQL 5.1 with disk-based cluster and replication for cluster
• Data Warehousing
  • MySQL 5.1 with data partitioning
    • data management becomes easy if one needs to delete many rows and they sit on one (smartly created) partition. Then a quick DROP DDL statement takes care of the job in a split second.
  • better subquery optimizations (6.0)
  • New Nitro engine available in 5.1 for real-time data warehousing
  • InfoBright engine for TB-sized data warehousing
  • Kickfire
• memcached
  • MySQL Enterprise is going to start offering support
• MySQL Workbench
  • use it
  • reverse engineer a schema
  • find differences
  • sync
  • free and paid version (nicer functionality in paid only?)
• 2008 plans are shown
  • MySQL 6.0, Falcon GA in Q4
  • Maria in Q4
• MySQL 6.x
  • foreign keys in all storage engines
  • better prepared statements
  • better server-side cursors -> faster, less memory
  • replication improvements -> checksums
  • optimizer enhancements
  • more
• MySQL 7.0?
  • Alpha, Beta begin mid-2009
  • GA expected 2009
  • codename "Citadel"
  • security oriented
  • per-column data encryption
  • external authentication methods

  • online alter table -> Online DDL changes (holy crap, bring it NOW!!!)

• Infobright storage engine
  • no indexes needed (wow, definitely need to research this)
• Kickfire
• Rob Young takes over
• talks about Enterprise plans, customer reported pains, a lot have to do with replication
• MySQL Load Balancer (Q3-Q4 2008)
• for high traffic, read intensive apps and websites
• application load balancing extension (not replacement)
• MySQL Enterprise Monitor
• needle in a haystack diagnosis
• MySQL Query Analyzer
• will be able to talk to the Enterprise Monitor
• MySQL Connection Manager (2009)
• connection pooler
• connection concentrator
• optimizes throughput of web applications
• multiplexing transactions onto a single connection
• Lunch time

• Speaker: Mikael Ronstrom, PhD, the creator of the Cluster engine
• Explains the cluster structure
• Aspects of performance
  • Response times
  • Throughput
  • Low variation of response times
• Improving performance
  • use low level API (NDB API), expensive, hard
  • use new features in MySQL Cluster Carrier Grade Edition 6.3 (currently 6.3.13), more on this later
  • proper partitioning of tables, minimize communication
  • use of hardware
• NDB API is a C++ record access API
  • supports sending parallel record operations within the same transaction or in different transactions
  • asynchronous and synchronous
  • NDB kernel is programmed entirely asynchronously
• Looking at performance
  • Fire synchronous insert transactions - 10x TCP/IP time cost
  • Five inserts in one synchronous transaction - 2x TCP/IP time cost
  • Five asynchronous insert transactions - 2x TCP/IP time cost
• Case study
  • develop prototype using MySQL C API - performance X, response time Y
  • develop same functionality using synchronous NDB API - performance 3X, response time ~0.5Y
  • develop same functionality using asynchronous NDB API - performance 6X, response time ~0.25Y
• Conclusion on when to use NDB API
  • performance is critical, need speed, response time, etc
  • queries are not very complex
• Conclusion on when not to use NDB API
  • when design time is critical
  • when complex queries are executed, the MySQL optimizer may handle them better
• New features of MySQL Cluster Carrier Grade Edition 6.3.13
• polling based communication
• CPU used heavily even at lower throughput
• avoids interrupt and wake-up delays for new messages
• some good results in benchmarks
• decreases performance when CPU is the limiting factor
• 10% performance improvement on 2, 4, and 8 data node clusters
• 20% improvement if using Dolphin Express
• epoll replacing select system calls (Linux)
• improved performance 20% on a 32-node cluster
• send buffer gathering
• real-time scheduler for threads
• lock threads to CPU
• distribution awareness
• 100-200% improvement when application is distribution aware
• avoid read before Update/Delete with PK
• UPDATE t SET a=const1 WHERE pk=x;
• no need to do a read before UPDATE, all data is already known
• ~10% improvement
• old 'truths' revisited
• previous recommendation was to run 1 data node per computer
• this was due to bugs, which are now fixed
• partitioning tricks
• if there is a table that has a lot of index scans (not primary key) on it, partitioning this table to only be in one node group can be a good idea
• partition syntax for this: PARTITION BY KEY (id) (PARTITION p0 NODEGROUP 0);
• new performance features in MySQL Cluster 5.0
• lock memory in main memory - ensure no swapping occurs in NDB kernel
• batching IN (…) with primary keys
• 100x SELECT FROM t WHERE pk=x;
• SELECT * FROM t WHERE pk IN (x1, …, x100)
• IN-statement is around 10x faster
• use of multi-INSERT
• similar 10x speedup
• new features in MySQL Cluster CGE version 6.4 (beta, only available in bitkeeper for now)
• multi-threaded data nodes - currently no benefit using DBT2 but 40% increase in throughput for some NDB API benchmarks
• DBT2 improvements to follow later
• use of hardware, CPU choice
• Pentium D @ 2.8Ghz -> Core 2 Duo @ 2.8Ghz => 75% improvement
• doubling L2 cache doubles thread scalability
• choice of Dolphin Express interconnect increases throughput 10-400%
• scalability of DBT2 threads
• 1-2-4 threads - linear
• 4-8 threads - 40-70%
• 8-16 threads - 10-30%
• decreasing scalability over 16 threads
• current recommendation by Mikael himself: use twice as many SQL nodes as data nodes
• future software performance improvements
• batched key access - 0-400% performance improvement
• improvement scan protocol - ~15% improvement
• incremental backups
• optimized backup code
• parallel I/O on index scans using disk data
• Niagara-II benchmark from 2002
• simple read, simple update, both transactional
• 72-CPU Sunfire 15k, 256GB RAM
• CPUs: ultra sparc-III @ 900Mhz
• 32-node NDB Cluster, 1 data node locked to 1 CPU
• db size 88GB, 900 mil records
• simple reads 1.5mil reads per second
• simple update 340,000 per second
• Everyone is overwhelmed, so no questions are asked