One good feature in MySQL 5.1 is the support of partitions which is built into the MySQL server. Lot of applications that I noticed use their own partition schemes by splitting the large table data to multiple tables on a desired key and wrapper table or application logic that identifies what table to use.

Here comes MySQL to rescue all those applications to push the partition logic to MySQL server itself without needing a application specific logic; which maintains partitions underneath the covers (each partition is a sub-table) on a single large table as shown below:

The partition key is specific to table and/or application. If the partition key is account_id or site_id or anything that takes both active writes and reads; …

Earlier this month, an announcement about STATEMENT based binary logging would be the default starting with MySQL version 5.1.29. I've always preached that backwards compatibility was key to new releases. In this case, lessons were not learned until close to final GA date.

I would like to point out that for 90% of customer cases, STATEMENT based replication will work fine as advertised. But I'd like to point out some use cases where STATEMENT based replication will be at best spotty (at least it is in 5.1.28).

If you primarily use InnoDB as your storage engine you will want to pay close attention to your transaction isolation level. There is a minimum requirement that READ COMMITED level be used, otherwise statement based replication can not be used.

Partitioning + InnoDB + STATEMENT-based binlog also has its problems. We …

I love the way partitioning works in MySQL. I remember in the past how many projects I implemented using application logic to parallelize I/O. Partitioning makes this seamless now. But it's not without its share of problems and workarounds. So I compiled my own wishlist that hopefully might make it into a future version of MySQL.

1. Partition level table locking. Partitions should be treated like tables and locked individually rather than the who table and all of its partitions.
2. Ability to add partitions from existing tables. This is very ueful, especially when trying to perform bulk maintainance operations.
3. Ability to convert a partition to a table.
4. Be able to mix and match storage engines for partitions and subpartitions. How cool would it be to have an archive partition for older data reside using ARCHIVE tables while the remaining partitions are InnoDB or MyISAM.
5. More usuable datatypes for …

In my previous post I started out by setting up MySQL 5.1.12 on a box in order to test the performance of the new partitioning option. During testing, I noted that I did not see any noticeable performance improvements from using partitioning. So I spent some time Saturday and Sunday (I guess I don't have anything else better to do!) to build the testing environment and perform the tests. So I was wrong, but only slightly. Partitioning does show better performance than standard tables, but not by as much as you would think. But, wait, there is light at the end of the tunnel (as well as a WTF). The numbers...

By partitioning, …

Finally, it's time to start putting MySQL 5.1.12-beta through the wringer. First order of business, convert the existing table schema to one that supports partitioning...

I made some minor changes to the configuration for partitioning, namely innodb_file_per_table and innodb_open_files. I set innodb_open_files to 1000 based on the tables and partitions I plan on supporting.

This is what the new table schema looks like with partitioning:

CREATE TABLE `network_daily` (
  `entity_id` int(11) NOT NULL default '0',
  `buyer_entity_id` int(11) NOT NULL default '0',
  `buyer_line_item_id` int(11) NOT NULL default '0',
  `seller_entity_id` int(11) NOT NULL default '0',
  `seller_line_item_id` int(11) NOT NULL default '0',
  `size_id` int(11) NOT NULL default '0',
  `pop_type_id` int(11) NOT NULL default '0',
  `country_group_id` int(11) NOT …

I've been very busy testing and testing MySQL 5.1.12-beta and on the surface, it passes my tests. But first, let me set the stage as far as what we are trying to accomplish at RightMedia...

All of our adserving data is aggregated into our reporting database, which runs MySQL version 5.0.22. The database is so large, that we split it across 6 machines. Each of these machines has a similar configuration: 2 Dual core Opteron CPUs, 16GB of memory and 3 146GB 15k RPM SCSI drives in a RAID-0 set.

Here is what one of our typical tables looks like:

CREATE TABLE `network_daily` (
  `entity_id` int(11) NOT NULL default '0',
  `buyer_entity_id` int(11) NOT NULL default '0',
  `buyer_line_item_id` int(11) NOT NULL default '0',
  `seller_entity_id` int(11) NOT NULL default '0',
  `seller_line_item_id` int(11) NOT NULL default '0',
  `size_id` int(11) NOT NULL default '0',
  `pop_type_id` …

It's been a while since I blogged. I've been busier than you can imagine.
Anyways, I finally got the opportunity to start playing with MySQL 5.1 beta and installed 5.1.12-beta on one of our beta database srevers to startt testing functionality. I'm really excited abiut this version, particularly Partitioning with InnoDB. 5.1.11-beta didn;t work at all. I'm about to put it through its tests and really start testing the performance of this new version. Stay tuned!