This week has been the MySQL Conference & Expo in Santa Clara, which has been all consuming for over 1,400 registered attendees.  It's the biggest conference we've ever done and it's nice to see participants from more than 30 countries including Antarctica, Argentina, Belgium, Brazil, Bulgaria, China, Croatia, Canada (Yay!), Egypt, Denmark, Finland, France, Germany, Haiti, India, Italy, Ireland, Israel, Japan, Korea, Mexico, Nigeria, Norway, Philippines, Russia, Sierre Leone, Singapore, Spain, South Africa, Sweden, Switzerland, Taiwan, Thailand, UK and the US. 

The tutorials on Monday included topics such as Scaling, MySQL Cluster, Replication, MySQL 5.1, MySQL Monitoring & Advisory Service, Performance Tuning, Writing your own storage engine and other topics.  Special thanks to all the tutorial …

innodb_buffer_pool_size - most important tunable, set to 70% of system memory, this is much more efficient then system cache.

innodb_additional_mem_pool - This is used for the dictionary and automatically grows so don't set to high. If you really don't use transactions, just use the default.


Putting Log Files on a different disk really doesn't make sense for INNODB. I too concur, none of my tests show any real improvement.

innodb_flush_logs_at_trx_commit: use 2 and 0 if using innodb like myISAM,
0 - flush the log per second to disk
1 - flush the log at commit expensive
2 - flush the log every second to the file system cache, it good if mysql crashes not good if the OS crashes.

innodb_flush_method *IMPORTANT* use O_DIRECT but can make writes a bit slower, and have a battery backed cache.
- O_OSYNC == O_DSYNC so don't worry - DSYNC would be cool because …

I am sitting in the session "InnoDB Performance Optimization" by Heikki Tuuri and Peter Zaitsev. This is going to be the last session of a very enjoyable and informative MySQL Conference and Expo 2007.

General Application Design is Paramount
Of course, you should design your schema, indexes and queries right. Storage engines aspects are often fine tuning. Storage engine selection may affect your schema layout and indexes.

Each storage engine has unique design and operating properties. App written for 1 storage engine may not perform best with other storage engines. Special optimizations exist for each storage engine.

There are always transactions with InnoDB, even if you don't use them explicitly. There is a cost with transactions. If running in autocommit mode, each transaction commit overhead for each statement.

You should wrap multiple updates in the same transaction for …

This morning I'll be giving my session on hacking MySQL to create INFORMATION_SCHEMA tables. The presentation starts by talking about the what and how of INFORMATION_SCHEMA and then goes through an example of creating your own I_S to show how much space your data files are taking on the disk.

I have a copy of Pro MySQL and Expert MySQL to give away during the session.

The slides are here.

This is a talk by Peter Z. from www.mysqlperformanceblog.com and Heikki from INNOBASE i.e. Oracle.

Here is a list of things commonly known. The talk is well organized and concise.

Keep Primary keys small in innodb, like less then 16 bytes. Don't update primary keys since it will require all the indexes to change it's pointer.


Auto increments may limit scalability since generating it requires a full table lock per increment. There is a patch from INNODB but it will not make it into the tree anytime soon.


Multi Versioning:

Complements row level locking to get even better concurrency. READ COMMITTED is a good transaction isolation level, if you have very long transactions that are unpurged.

Try to avoid SERIALIZABLE - it's just not good.


Foreign Keys Performance:

May cause locks it certainly increases row …

Chip Turner and Mark Callaghan are presenting the session "MySQL: The Real Grid Database"

Data is sharded vertically and they have a lots of replicas. Resharding is a bigger pain than sharding. Make really smart software and manage with least human resources as possible. They are going to talk about problems that matter to them.

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.
Which allows them to support many servers with a few DBAs.


I asked Chip earlier and he told me that they don't use any memcache at all. Their spindles are flying more crazily than their network bandwidth.

What is manageability?
-Make it easy to do the tasks that must be done
Reduce the number of tasks that must be done …

The expo floor is now closed.  The Phorum booth had decent traffic over the two days.  It was more than it appeared the HP megabooth was getting.  We heard one thing over and over.  Admins of forums really want to integrate them with their existing user systems.  We have some ability in this area, but we all now think we need to make it a no brainer feature of Phorum.  We did confirm while we were there that the Phorum embedding module will work with Joomla.  It uses the Mambo connector and that works with the current release of Joomla.  They told us that 1.5 would be out soon and it may not work with 1.5.  We will have to look at that.  They were right next to us in the expo hall.  Others that were in the DotOrg pavillion included  …

Paul Tuckfield of YouTube is giving a keynote. He has been sysadmin for 25 years, Oracle DBA for 15 years and MySQL DBA for 8 months.

For them, MySQL is one (important) piece of scalability. Top reasons for YouTube Scalability includes drinking :).

Top reasons for YouTUbe database scalability include Python, Memcache and MySQL replication. WHat you can do with Python, you can't do with other scripting languages. The fastest query on the database is that is never sent to the database.

They were doing click tracking on separate MyISAM whereas the read/write was being done on InnoDB. Everything was being written to one master and then being replicated to slaves. There were far fewer writes than reads. Every time replication slows down, you are screwed. Keeping replication going fast was hard.

They had a lot of replicas. One of the things that …

In my third day at the MySQL Conference and Expo 2007, I again attended keynotes and sessions, one of which I participated in. This evening I had dinner with a fellow community member and arrived late to the Quiz Show, even though I was supposed to be on one of the teams! I blame it on the restaurant, because they took too long to figure out what I meant when I said "können wir einen Hubschrauber essen heute abend?"

Today I attended by a decent margin the best sessions I've been at all week. If you don't think they're saving the best for last, come to my tutorial and demo on monitoring MySQL and InnoDB with innotop tomorrow and see!

Just two quick notes: I am recording the sessions I attend on my iRiver when possible, and …

solidDB has been available as a MySQL storage engine for about four months now (check here: http://dev.soliddb.com). It is one of few transactional engines available. You can see it running at the conference. You can try yourself. That is not, however, the end of the story.

People who have heard about Solid know that Solid has other goodies, too, in addition to just plain old database (PODB :-). Solid has been selling for more than 10 years to demanding markets of network equipment providers and other embedded systems. In the process, a few unique incarnations were born. One is a fully transactional in-memory database that may be transparently blended with the disk-based database. Another is a load-balancing two-node hot-standby configuration with sub-second failovers.

These goodies are not offered yet to the MySQL community. They will come ... in time.