The Codebelay Blog

I decided to benchmark the code that builds out the partitioned and non-partitioned tables.
The results are very perplexing.

Code-partitions are fast on the inserts.

MySQL Partitions are fast on the reads.

This seems to suggest a complicated MySQL master-slave set up where you just have code partitions on the Master and MySQL partitions on the slave. It’s probably not worth the effort setting up, so I’ll just go with the MySQL partitions and work on finding ways to make the MySQL code work faster.

What do you folks think?

If you upgrade to MySQL 5.1 (which is still in beta), you will love the performance boost you get over the partitioning that you’re probably doing on the scripting side because of what Yahoo told you. I think improving the “front-end” is great and all, but if you look at the recent benchmarks below run on MySQL 5.1, you’ll see that Yahoo cannot escape the reality of backend speed.

At this week’s Web 2.0 Expo, Yahoo is telling folks to make things work faster on the front end. It’s a great sound-byte, but stupid advice.

If you upgrade to MySQL 5.1 and use its built-in table partitioning, I’m betting that you’ll see a speed boost in selects of up 300%.

Are you ready to Benchmark? I’m assuming that you’ve done a PHP / MySQL install before.

Part I: Installation of MySQL 5.1

• Download the beta version of MySQL 5.1
• Back up your mysql database.
• DO NOT RUN mysql_upgrade. As of this blog posting, it does not work. You wil lock yourself out of your database and go through a painful rebuild process.
• Install MySQL 5.1 fresh as a new install. If you’re me and want to use partitioning this means: ./configure –prefix=/usr/local/mysql –with-ssl –with-partition ; make ; make install
• Restore the backed up mysql database.
• Get your database up and running again.

Part II: Get benchmarking scripts up and running

• I used PHP 5, and PHP Pear’s Benchmark package.
• Download the Partition Benchmarks scripts.
• 3 types of tables are benchmarked: partitioned by software (i.e. Yahoo’s way and how a lot of folks did it before MySQL 5.1), partitioned by MySQL, and not partitioned at all. The script is called build_tables.php . It works on the command line like this: php build_tables.php 10000 5, where 10000 is the number of total rows you’re expecting, and 5 is the number of table partitions.
• After the tables are built you can now test a select using the testDao.php script by typing: php testDao.php

The results I got on a 1 Ghz PowerPC G4 running Mac OS X 10.4.9 are interesting.

First I built a 5 partitioned tables spanning 10000 rows:
shell> php build_tables.php 10000 5

Then I ran the tests on the 3 partition types: software, mysql, and non-partitioned:
shell> php testDao.php

Elapsed time between Start and Test_Code_Partition: 0.059307
Elapsed time between Test_Code_Partition and DB_Partition: 0.005882
Elapsed time between DB_Partition and No_Partition: 0.003694
------------------------------------------------------------------
marker                time index            ex time         perct
------------------------------------------------------------------
Start                 1176964205.32067900   -                0.00%
------------------------------------------------------------------
Test_Code_Partition   1176964205.37998600   0.059307        85.83%
------------------------------------------------------------------
DB_Partition          1176964205.38586800   0.005882         8.51%
------------------------------------------------------------------
No_Partition          1176964205.38956200   0.003694         5.35%
------------------------------------------------------------------
Stop                  1176964205.38978100   0.000219         0.32%
------------------------------------------------------------------
total                 -                     0.069102       100.00%
------------------------------------------------------------------

The non-partitioned set up ran the fastest — but that’s no surprise b/c the number of rows aren’t large enough to take advantage of partitioning.

Built-in Partitioning came in 2nd.

PHP partitioning, or software partitioning came a distant 3rd.

So what are you waiting for????

If you need that urgent speed boost in your database-driven web application, get MySQL 5.1 now.

Do a search for your favorite tag in youtube. If it’s Britney Spears, then youtube will tell you that they’ve got 19,400 plus videos on Britney. I got to the 1000th Britney Spear video by skipping all 999 of them on this search result page.

What happens when you try to go to the 1001st video?

You get the error below:

Guess what? Youtube’s not alone. Flickr does it too. Can anyone guess their limit?