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 …

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 …