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.
One reply on “Upgrading to MySQL 5.1: I Love Built-in Table Partitioning!”
You also need to create a meta table for the software partition:
CREATE TABLE meta_table (
id INT NOT NULL primary key AUTO_INCREMENT ,
tablename varchar(255),
iterator int(10),
last_user_id int(10)
);