Slides (PDF, 402K) These are the slides for my talk "Monitoring MySQL" at the MySQL Users Conference and Expo 2007 from yesterday.

I tried to reproduce my talk as a written text, but what I attach below is somewhat more and at the same time less than what I was actually saying yesterday.


Continue reading "Monitoring MySQL"

Yesterday Phillip Reisner was giving an introduction to DRBD. I won't get into it here, but essentially it's a way to keep blockdevices on two different hosts in sync to get a kind of shared disk. (Go read about it on their site!)

I've used it for a while to get high-availability from two NFS servers and I always recommend it in my scalability talk. It's awesome. (Heartbeat which is the "fail-over" software in the typical configuration isn't so great, more on that another time).

Anyway, being at the MySQL conference the context of course is "how can this be used for MySQL". Usually I prefer a simpler master-master replication setup for redundancy. Later in the afternoon Mats Kindahl (one of the replication …

Got a MySQL project that needs a partitioned table. It's the ideal candidate really. Historical data that naturally would be partitioned by date. I need to keep an active window of data and delete old data quickly without doing DML.

Except partitioning is in 5.1 (more on that later) and I'm on 5.0.

One way I would overcome this limitation in Oracle Standard Edition would be to have multiple tables that hold a month worth of data and put a view on top of them. My users might notice a slight performance degradation, but it would be worth it to drop the data quickly.

So, I tried the same experiment in MySQL. I created three tables of this format:

CREATE TABLE `xyz_2007_01` (
`id` bigint(10) NOT NULL default '0',
`report_dt` date default NULL,
`acct_id` varchar(8) default NULL,
`some_text` varchar(222) default NULL,
PRIMARY KEY  (`id`),
KEY `xyz_rdt_acct_2007_01` …

Listening to Paul Tuckfield talk about YouTube's use of MySQL at the 2007 MySQL User Conference.

YouTube's web stuff is Python and Memcache. Database is MySQL with some serious replication. 100M views in a day happened in July 2006 but it is actually looks like it has more than doubled since then according to a graph that Paul showed.

Started with the replication setup with a single master for writes and many slaves for reads. Moved to a system where specific pages are pulled from specific replicas.

One of the important lessons they learned. When upgrading to 5.0 from 4.1 they had some of the servers perform much better than that others. Turned out that if they dump ed and then reimported the data the server performed much better. When moving to 5.0 if the tables are rebuilt they use a more compact data storage and gets better performance.

Paul spends some …

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 …