Should you be worried about STATEMENT based replication?

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 faced constant issues, getting the error 'Binary logging not possible. Message: Statement-based format required for this
statement, but not allowed by this combination of engines'. What the heck does this mean?

It's a misleading error message. Partitioning is not a true storage engine, but is a virtual one. The first time an underlying table is opened, the partition engine caches the table flags of the real storage engine, in this case InnoDB. Lets say for example, an app performs a SELECT on a partitioned InnoDB table. Let's also assume that the transaction isolation level is READ UNCOMMITTED. The SELECT will execute without any issues. But, try to insert a record from a different session, and it will fail every time. I filed this BUG#39084 over a month and a half ago. Repeatable test cases were also given.

Since we are committed to releasing our reporting database and can not wait for MySQL to come around and realize mistakes they made, we came up with our own patch that addresses this immediate concern, and for this reporting db.

So, should you be worried? I think I would be.

Wishlist for partitioning

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 partition pruning. How many times can you use datetme when timestamp is available. Also when would functions other than TO_DAYS and YEAR be supported?

MySQL 5.1 Partitioning - Part 4 (Results)

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...

Table Type	Elapsed Time
Normal	7 minutes, 41 seconds
Partitioned	5 minutes, 51 seconds

By partitioning, we gain about 25% improvement in performance. Not bad, but not great. I would have expected more than 25%, given that I loaded approximately 110 million records for the test, and there are approximately 650,000 records per day for the range for the test.

Here are the details of testing:

• Machine is an HP DL385 with 16GB of memory, 1 73GB 15k RPM SCSI drive for boot, root and /var partitions. 3 146GB 15k RPM SCSI drives in RAID-0 stripe (Hardware RAID).
  
• Using CentOS 4.3 as operating system
  
• MySQL version 5.1.12-beta (Compiled from SRPM)
  
• InnoDB Parameters
  
  
  
  • innodb_buffer_pool_size = 11G
    
  • innodb_file_per_table
    
  • innodb_open_files = 1000
    
  • innodb_log_file_size = 2000M
    
  • innodb_flush_method = O_DIRECT
    
  • innodb_flush_log_at_trx_commit = 1
    
  • innodb_support_xa = 0
    
  
  
• Prior to each test run, the database server was restarted, and mysql was run with the database name (It takes approximately 40 seconds for mysql to run the first time as it caches the table information)
  
• The test query was run 3 times (each with a db server restart) and the average of the 3 runs taken.
  
• SQL (Non-partitioned): select entity_id,buyer_entity_id,buyer_line_item_id,sum(roi_cost) from network_daily_local_nonpart force index (ix_nsdl_ymdh_entity_seller) where ymdh in ('2006-11-01 00:00:00', '2006-11-02 00:00:00', '2006-11-03 00:00:00', '2006-11-04 00:00:00', '2006-11-05 00:00:00', '2006-11-06 00:00:00', '2006-11-07 00:00:00', '2006-11-08 00:00:00', '2006-11-09 00:00:00', '2006-11-10 00:00:00', '2006-11-11 00:00:00', '2006-11-12 00:00:00')and entity_id = 2 group by entity_id,buyer_entity_id,buyer_line_item_id order by entity_id,buyer_entity_id,buyer_line_item_id
  
• SQL (Partitioned): select entity_id,buyer_entity_id,buyer_line_item_id,sum(roi_cost) from network_daily_local_part force index (ix_nsdl_ymdh_entity_seller) where ymdh in ('2006-11-01 00:00:00', '2006-11-02 00:00:00', '2006-11-03 00:00:00', '2006-11-04 00:00:00', '2006-11-05 00:00:00', '2006-11-06 00:00:00', '2006-11-07 00:00:00', '2006-11-08 00:00:00', '2006-11-09 00:00:00', '2006-11-10 00:00:00', '2006-11-11 00:00:00', '2006-11-12 00:00:00')and entity_id = 2 group by entity_id,buyer_entity_id,buyer_line_item_id order by entity_id,buyer_entity_id,buyer_line_item_id
  

Now, one thing at RightMedia, is we have LOTS of data. In fact there is some skew on the entity_id column and the majority of the records have the value listed in the SQL above. That still should not explain huge increases in performance, should it?

Okay, one last experiment (It's 3:46AM EST!!) I am going to subpartition the partitioned table into 7 subpartitions, hash by the entity_id column.

Result: 14.32 seconds! Holy friggin' turbo mode Batman! It's way too late to be trying to quantify performance gain, so I'm quantifying it by WTF units. I'd really like to get an explanation of what is really going on behind the scenes that yields this type of performance gain, and why I don't see at least 1 or 2 WTF units of gain on the original partitioned table, given that the amount of data is much less for the date range compared to the whole table.

MySQL 5.1 Partitioning - Part 3

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 NULL default '0',
  `is_adjustment` tinyint(4) NOT NULL default '0',
  `adv_learn_type` char(1) NOT NULL default '',
  `pub_learn_type` char(1) NOT NULL default '',
  `frequency` smallint(6) NOT NULL default '0',
  `ymdh` datetime NOT NULL default '0000-00-00 00:00:00',
  `imps` bigint(20) NOT NULL default '0',
  `clicks` int(11) NOT NULL default '0',
  `convs` int(11) NOT NULL default '0',
  `id` int(10) unsigned NOT NULL default '0',
  `checkpoint` int(11) default NULL,
  PRIMARY KEY  (`id`,`ymdh`),
  KEY `ix_nsl_ymdh_buyerli` (`ymdh`,`buyer_line_item_id`),
  KEY `ix_nsdl_ymdh_entity_buyer` (`ymdh`,`entity_id`,`buyer_entity_id`),
  KEY `ix_nsdl_ymdh_entity_seller` (`ymdh`,`entity_id`,`seller_entity_id`)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(`ymdh`))
(
  PARTITION p2005 VALUES LESS THAN (TO_DAYS('2006-01-01')),
  PARTITION p200601 VALUES LESS THAN (TO_DAYS('2006-02-01')),
  PARTITION P200602 VALUES LESS THAN (TO_DAYS('2006-03-01')),

  ...

  PARTITION P200609 VALUES LESS THAN (TO_DAYS('2006-10-01')),
  PARTITION P20061001 VALUES LESS THAN (TO_DAYS('2006-10-02')),
  PARTITION P20061002 VALUES LESS THAN (TO_DAYS('2006-10-03')),
  PARTITION P20061003 VALUES LESS THAN (TO_DAYS('2006-10-04')),

  ...

  PARTITION P20061130 VALUES LESS THAN (TO_DAYS('2006-12-01'))
);

There are a lot of partitions that I have defined. I need to keep a rolling 60 days of daily partitions active. The plan is to use the ALTER TABLE REORGANIZE PARTITION statement to merge the older partitions together once per day, and to add a new partition once per day.

After the data was reloaded, it was time to test the performance, and the ability to add partitions and reorganize them (this was broken in version 5.1.11-beta).

Performance, surprisingly wasn't what I expected. Queries that ran on the partitions were about the same performance wise as those on the unpartitioned table. Hmm, I have to double check my results. I'll post all of the performance data.

The real good news is that the partition maintenance commands all worked with InnoDB! Dropping & Reorganizing partitions worked perfectly. I'll have to redo my testing and to see what happened to the performance.

The joys of MySQL 5.1 (Part 2- The Current Problem)

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` int(11) NOT NULL default '0',
  `country_group_id` int(11) NOT NULL default '0',
  `is_adjustment` tinyint(4) NOT NULL default '0',
  `adv_learn_type` char(1) NOT NULL default '',
  `pub_learn_type` char(1) NOT NULL default '',
  `frequency` smallint(6) NOT NULL default '0',
  `ymdh` datetime NOT NULL default '0000-00-00 00:00:00',
  `imps` bigint(20) NOT NULL default '0',
  `clicks` int(11) NOT NULL default '0',
  `convs` int(11) NOT NULL default '0',
  `id` int(10) unsigned NOT NULL default '0',
  `checkpoint` int(11) default NULL,
  PRIMARY KEY  (`id`,`ymdh`),
  KEY `ix_nsl_ymdh_buyerli` (`ymdh`,`buyer_line_item_id`),
  KEY `ix_nsdl_ymdh_entity_buyer` (`ymdh`,`entity_id`,`buyer_entity_id`),
  KEY `ix_nsdl_ymdh_entity_seller` (`ymdh`,`entity_id`,`seller_entity_id`)
) ENGINE=InnoDB

Each of the 6 machines holds a portiton of the data for this table (and others) Also, there are 2 other slaves per machine for queries. This is our biggest table, which each machine holding about 170 million records.

This set up was fine for a while, but started to degrade in performance over the past 3 months. Every tweak to InnoDB was done, but still we need more performance. Here is an example of one of these killer queries against this table:

select entity_id, ymdh, buyer_entity_id, buyer_line_item_id, sum(imps),sum(clicks)
from network_daily
where  ymdh IN ('2006-11-01 00:00:00', '2006-11-02 00:00:00', '2006-11-03 00:00:00',
'2006-11-04 00:00:00', '2006-11-05 00:00:00', '2006-11-06 00:00:00',
'2006-11-07 00:00:00', '2006-11-08 00:00:00', '2006-11-09 00:00:00',
'2006-11-10 00:00:00', '2006-11-11 00:00:00', '2006-11-12 00:00:00')
and entity_id = 2
group by entity_id, ymdh, buyer_entity_id, buyer_line_item_id
order by entity_id, ymdh, buyer_entity_id, buyer_line_item_id

I ran this query against an idle machine, and the query took 2 minutes and 31 seconds to run. Yuck! If I were a customer waiting for this report (a month-to-date report by day) I would be hitting the reload button quite a few times!

How can we look at optimizing this query? It's hitting the right indexes. The problem is that each day, we load over 700,000 records into this particular table, so that equates to 8.4 million records for this date range. The entity_id column does help with the index scan, so the average number of records per day to look at goes down to 100,000, but that's still 1.2 million records. While it should not take too long to scan through 1.2 million records the rest of the time could be spent building the temporary table for the group by operation. How have we been solving this? Buying more machines and splitting the database! While this has been working, it has come tgo a point that the growth rate of the database will soon outweigh the amount of splitting that can be done. So it's now time for some creative alternatives.

2 that come to mind: Materialized Views and MySQL 5.1 with Partitioning. I'll talk about Materialized Views in another blog (This is one feature that I wish MySQL would implement! This is fairly crucial to large scale reporting). Partitioning in the upcoming MySQL 5.1 release looks very promising. By splitting the underlying storage of the table into smaller tables, the size of the index becomes smaller, and it takes less time and more importantly disk I/O to scan the index to retrieve the data. The previous release, 5.1.11-beta was pretty horrible, especially with InnoDB. About the only thing I could use this for was some minor testing. Any SQL that was used to modify partitions resulted in a server crash and a corrupted database. I just finished compiling the 5.1.12-beta version and installed the RPMS on one of my test systems (exactly the same configuration as that of a production machine). In the next part of this blog, I'll reveal my results.

Thye joys of MySQL 5.1

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!