Diamond Notes

Disclaimer: I have talked to Kickfire extensively before the conference. I have blogged several times and written and article for MySQL Magazine about this.  I have not received any payment from Kickfire.

C2 (now Kickfire) approached MySQL about a year and a half ago with the idea of building a chip that would run SQL in hardware. Since this time they have developed what amounts to an “appliance” that is now the number one ranked server on the TPC-H benchmarks in price/performance and also number one in performance in non-clustered environment.

Kickfire is looking to reduce two bottlenecks of a data warehousing system - the I/O bottleneck and the CPU bottleneck.

Raj didn’t talk about the CPU bottleneck much. However, some of their techniques bypass caches to improve performance.  Also, the SQL Chip is going to offload much of the load from the CPU because it does in the SQL Chip what was being done in the internal CPU.

I/O bottleneck resolution approaches:

• Column store format and compression of data (including on the fly reading of compressed data)
• intelligent indexing
• smart pre-fetching

In addition they developed the aforementioned SQL chip. The benefits of this:

• execution in hardware of SQL commands
• parallel query execution

Raj then went through some of the benchmarks. I released these numbers in the magazine article. It is available at http://mysqlzine.net (spring 2008 issue).

I think that there was a lot of wondering (as in “is this real?”) before the conference.  I think a number of questions have been answered.  It will be interesting to see what comes out of all this!

Why Falcon?

Because the world is changing.  He emphasized the fact that hardware is changing rapidly (something I have harped about quite a bit).  When Falcon development started two socket boards where pretty rare.  Now, quad-cores, many threads.  Relative to CPUs and memory, disks are getting slower and slower and slower

Where Applications are going

• batch - dead
• timesharing - dead
• departmental computing dead
• client server - fading fast
• application server for most of us
• web services for the really big guy

The Challenge

exhaust CPU and memory and avoid the disk

Falcon tradeoffs

• use memory (page cache) to avoid disk reads
• use memory (record cache) to avoid the page cache manipulation
• use CPU to find the fastest path to a record
• use CPU to minimize record size
• synchronize most data structures with user mode read/write locks
• synchronize high contention data structures with interlocked instructions

The Falcon Architecture

• incomplete in-memory database with disk backfill
• multi-version concurrency control in memory
• updates in memory until commit
• group commits in a single serial log write
• post-commit multi-threaded pipe line to move updates to disk

Incomplete in-memory database

• selected records cached in memory
• separate cache for disk pages
• record cache is 15% the cost of a page cache hit
• record cache is more memory efficient than page cache

Record Encoding - Cache Efficiency

Records encoded by value, not declaration — what this means is that the string “abc” occupies the same space in varch(3) or varchar(4096) or char(3).  The number 7 is the same if its a small int, medium int, int, decimal or numeric.

MVCC

• update operations create new record versions
• readers don’t block writers (hmmmm)
• everyone sees a consistent view of the data

Updates are in memory until commit

• updates are held in memory pending commit
• index changes arre held in memory peding commit
• verb rollback is dirt cheap
• transaction rollback is dirt cheap

At commit time

• pending record updates are flushed to serial log
• pending index updates are flushed to serial log
• commit record written to serial log
• serial log flushed to the oxide and the transaction is committed

What happens when we run out of memory?

• large transaction flushes uncommitted data early to the serial log (called “chilled”)
• these records can be fetched from the serial log (called “thawed”)
• scavenger garbage collects unloved records periodically
• when things get really bad, entire record chains are flushed to backlog

Falcon is definitely oriented towards OLTP, large volume, fairly small statements.

Falcon Weaknesses

• Transactions are ACID but not serializable
• Latency advantage disappears at saturation
• Very large transactions degrade performace
• optimized for Web, not batch operations

Falcon Strengths

• Runs like a memory database when data fits in cache
• scales like a disk-based database when data doesn’t fit in cache
• lowest possible latency for Web applications
• absorbs huge spiky loads

When should you use what?

• if you don’t need ACID, MyISAM is probably fastest
• for Uniprocessors and small memory systems, Innodb is a good choice
• For large transaction batch, InnoDB maybe be best match
• for multicores and large number of threads, Falcon is probably best
• For the web Falcon is hard to beat

Sounds to me like  Falcon is really coming along.  My question would be if the single-threaded nature of the MySQL server itself will hold Falcon back down the road.

The MySQL Sandbox is a way to set up a new test server or servers in just a few seconds.

Features

• not an official MySQL product
• GPL
• installs from a tarball in a few seconds
• installs side servers, not the main one (doesn’t disturb running servers!!!)
• creates a separate environment — data directory, port and socket

Sandbox commands

• start
• stop
• clear ( creates a fresh installation)
• use
• start_all
• stop_all
• clear_all
• multi_cmd

m for master and s for slave

quick install

./express_install.pl /path/mysql_os_5.1.tar.gz

This would install a single server

For replication

./set_replication.pl /path/mysql_os.tar.gz

for multiple servers

./set_many 5.0.51

and multiple servers of different versions

./set _custom_many.pl 5.0.51 5.1.23 6.0.5

(can specify full path for configurations

–how_man_nodes option creates theh specified number of nodes (–how_many_nodes=4 creates four hosts)

–help prints the help screen

Fine Tuning

./express_install.pl /path/mysql_OS.tar.gz [option] [option] …

./express_install.pl /path/mysql-OS.tar.gz –my_file={small|medium|large|hug}

or you can specify a path to a custom my.cnf

Commands

start simply start the server, creates the pid and socket files

stop stops the server

clear stops the server and removes all files in the data directory and clears the test database

use start the mysql client

• uses the credentials in the my_sandbox.cnf fiel
• deafault username and password msandbox/msandbox and root password of msandbox

my {sqldump|sqlbinlog|sqladmin|}

For Replication

start_all

starts the master and then the slave

stop _all

stops the slave and then the master

clear_all

clear all the slaves and then the master

For Multi-node Sandbox

start_all

stop_all

clear_all

multi_cmd

executes command on all nodes

This is a really cool looking tool.  I have heard of it before, but never taken the time to download it and evaluate it.  It would be really nice to use this on our test machine.  If you do much testing at all I would recommend you download it and take it for a spin!!

Resources

http://sf.net/prjects/mysql-sandbox

So multiple people have blogged about this announcement already.  The Innodb company has announced a new plug-in that can be a drop-in replacement for the current Innodb code in MySQL 5.1.  Evidently it is really easy also.  It has some interesting new features including non-blocking index creation if I am understanding things correctly.  That  to me seems to be the big new feature to me.

However, the reason why I am pointing this announcement out when others already have is that Peter Zaitsev did a talk today about the scalablitiy issues of Innodb dbs this afternoon.  I blogged during the talk here.  He pointed out (with benchmarks) multiple times that the 5.1 server (with Innodb tables) was actually not working as well as 5.0 currently does. If I understand correctly he didn’t have a chance to test the new plug-in (not for certain though — I was unclear about that).  They possibly could have fixed these regression issues in the new version but I would love to see tests of it.  Just a thought before we all get to excited :)  I like the idea personally that the Innodb plug-in isn’t so tightly coupled with the MySQL server. Good to see them releasing something.  Hope they continue!  Scaling improvements above 8 cores would be nice.

Scalability: the ability for a server to grow up (and technically down…but they don’t consider this)

Performs: response time is decent (ie not 30 seconds), easy to measure in production. Make sure there is enough system capacity (system loaded with degrading in response time and failing). With system operations you need to be able to have reasonable times for activities (24 hr backup of 2 terabytes is not acceptable).

Application Growth Parameters

• load - growing from 100 q/sec to 1,000 q/sec
• database size growing from 10 GB to 100 GB
• data distribution - going from 10 friends on average to 250 average
• launching new features - adding full text search can add a heavy load

As applicatons grow all the parameters tend to grow at once. This includeds more complex queries on larger databases. To maintain performance you can optimize the application: schema, queries, caching, sharding and replication.

What is a platform?

• hardware
• OS
• MySQL

Testing done through Micro Benchmarks (simple operations designed to test some particular aspect of behavior). Peter didn’t really explain this enough for me…other than a general idea not sure about it.

Issue with Innodb

• large tables are hard to deal
• backup - physical backup is a must
• can’t move separate tables between servers
• no repair functionality (corruption means dump and restore)
• ALTER TABLE is very slow (MM replication helps)
• table maint with OPTIMIZE TABLE

Online Index Creation in InnoDB

plugin just announced today by Innodb

Innodb can now build indexes without rebuilding whole table (index build done by sort which is much faster).  It is ten times faster loading data and adding indexes (separately).

Dealing with Many Tables in Innodb

many people avoid the problem of large table by creating many small tables.

using innodb_file_per_table=1 will resolve this problem.

“Warmup” is still a problem.  In 5.0 only one table can be opened at a time.  stats update on open makes it quite slow.

Large Buffer Pool Size

In general, the more memory you can get for buffer pool the better it is

Warmup will take a long time.  With a 32GB buffer pool, it can take an hour to fill at 600 pages/sec fill rate.  SHOW STATUS and SHOW INNODB STATUS gets expensive because of global locking

In addition, shutdown will take longer with large buffer pool.

You can set innodb_max_dirty_pages_pct=0 in advance and it will help with the flushing of the buffer pool.

Peter discussed benchmarking 5.0 and 5.1.  It appears that 5.1 has some regression issues!  They tested up to 64 threads in a test.  In all cases 5.1 simply did not perform as well.  Quad-core chips did not perform as well as dual core.

Looks to me like 5.1 has bit to go in respect to Innodb.  However, I don’t believe they had the latest patches.  He kept mentioning Mark Callhan??

Slides available @ http://www.pythian.com/blogs/917/best-practices-for-database-administrators-slides-and-links

Best Practices

Make it easy for yourself

• automate
• document (everything)
• use your brain for cpu not for storage (request tracking system & google!!!!)

Monitoring Basics

• graph (cacti, nagios)
• alerts (nagios)
• check your checks

Tradeoffs always exists

Backups & Restore

Backup of course but make sure you Restore!!

In case of failure…

• restore from backup
• master/slave
• master/master
• cluster

Test HA (High Availability) also!!

what scenarios do they cover?

what scenarios do they not cover?

Ounces of Prevention

• configuration vs. reality
• possibly memory usage
• disk space/tablespace size (limiting tablespace for innodb?)

Pounds of Cure

• error logs
• slow query logs
• query review (like code review)

mysqldumpslow (what server versions available?)

mysqlsla http://hackmysql.com

mk-query-profiler

show status before and after

mysqlreport http://hackmysql.com

procedure analyse()

what does each column, table do?

what’s in a name? good information!! can you read the data columns/tables and decipher what is going on?

question “best practices”

Schema Profile

• start normal
• denormalize if necessary
• stored procedures for developers (maybe they are afraid of normalization)

Replication

Be careful of TRIGGERs and any DML for SP or UDF

Sync often with mk-table-sync

Handle duplicates carefully
Maintenance

partition

archive

purge

static data

Manage User Expectations

aggregate data once every hour or fifteen minutes

split off processing:

• customer/non-customer
• internal/external

Foreign Keys

application still has to handle problem!!

MySQL 5.1 Features

• table/index partitioning   — not parallel, key has to be of a numeric value and no foreign keys
• row-based/hybrid replication — hybrid replication is the default and is changeable on the fly
• disk-based cluster tables
• built-in job scheduler (events) — replacement for cron etc.
• problem SQL identification — dynamic tracing via slow query log and can be logged
• faster full-text search
• archive engine improvements

RC 5.1.24 (Enterprise only I believe) available at conference

GA schedulted for the end of Q2 2008

MySQL 6.0 Features

• Falcon — user defined tablespaces
• maria
• online backups
• optimizer enhancements

scheduled for the end of 2008 (but I am making NO bets on it!! )

It looks like MySQL is beginning to de-couple cluster from the base server project.  They have a roadmap of cluster products through the rest of this year and into next year.  I think this is a great plan since the cluster product/engine is so different from everything else.

memcached will be offered support under MySQL Enterprise
MySQL 6.X — “citadel” mid to end 2009 is the plan

• foreign keys on all engines
• better prepared statements
• better server-side cursors
• replication improvements (checksums)
• data auditing
• external authentication
• security groups/roles
• transparent data encryption
• online alter table

Rob Young is talking about the enterprise products.

A few minutes ago I uploaded the new issue of the MySQL Magazine.  I am very excited about this issue as the quality continues to increase!  Not only do we have a great group of articles, Sheeri Cabral (our new Community Advocate Award winner) did a fabulous job creating an entire new layout and look for the magazine.  It looks absolutely outstanding.  I have told her several times how much I appreciate it, but let me once again take the time to say I appreciate the tireless work she did on the layout.

Here is a list of the table of contents:

A Tour of MySQL Certification

Coding Corner

On Efficiently Geo-Referencing IPs with MaxMind GeoIP and MySQL GIS

Introducing Kickfire

Automatic Query Optimization with QOT

As always, the magazine is free for download and is available from http://www.mysqlzine.net and http://www.paragon-cs.com/mag. Enjoy!!

Dr. Bell summarized new features found in the implementation of backup and restore in version 6.0.

SQL Driven — run from the command line

Database level backup

• tables
• views
• stored procedures
• stored functions
• triggers
• events

Enterprise level consistitency — from point in time

non-blocking DML operations on Innodb and Falcon

streaming backup data (only to local server file now..should extend to tape or other servers)

tablespace support for falcon

no data engine backup eg: blackhole, merge (didn’t quite understand why this is useful but he gave some reasons)

synchronization with binary log for point-in-time recovery

defaults to mysql datadir although you can specify a directory in the backup filename

example:

BACKUP DATABASE database_name TO ‘database.bak’;

RESTORE command is destructive

backup log information looks useful but Dr. Bell indicates it will be changing probably.

Future plans:

• coverage for all MySQL storage engines
• use plug-in architecture
• backup to other media/servers
• standalone mysqlbackuptool
• full server, database and enhanced point-in-time recovery

Still a work in progress but looks very promising!! Honestly, the backup parts of 6.0 might be the most important features of the upcoming 6.0 servers. Falcon looks nice and sounds nice, but backups rule the day when the $%#@ hits the fan. I personally am excited to finally see development on this front.

Resources:

http://forge.mysql.com/wiki/OnlineBackup

Johnathan made mention that the ZFS Filesystem “might” be moving from the CDDL license to a GPL license. Yeah!!