{"id":21,"date":"2007-04-18T22:36:46","date_gmt":"2007-04-19T06:36:46","guid":{"rendered":"http:\/\/www.codebelay.com\/blog\/?p=21"},"modified":"2007-04-18T22:36:46","modified_gmt":"2007-04-19T06:36:46","slug":"upgrading-to-mysql-51-i-love-built-in-table-partitioning","status":"publish","type":"post","link":"https:\/\/www.codebelay.com\/blog\/2007\/04\/18\/upgrading-to-mysql-51-i-love-built-in-table-partitioning\/","title":{"rendered":"Upgrading to MySQL 5.1: I Love Built-in Table Partitioning!"},"content":{"rendered":"<p>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&#8217;re probably doing on the scripting side <a href=\"http:\/\/conferences.oreillynet.com\/cs\/webex2007\/view\/e_sess\/13253\">because of what Yahoo told you<\/a>. I think improving the &#8220;front-end&#8221; is great and all, but if you look at the recent benchmarks below run on MySQL 5.1, you&#8217;ll see that Yahoo cannot escape the reality of backend speed.<\/p>\n<p>At this week&#8217;s <a href=\"http:\/\/www.web2expo.com\/\">Web 2.0 Expo<\/a>, Yahoo is telling folks to make things work faster on the front end. It&#8217;s a great sound-byte, but stupid advice.<\/p>\n<p>If you upgrade to MySQL 5.1 and <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/partitioning.html\">use its built-in table partitioning<\/a>, I&#8217;m betting that you&#8217;ll see a speed boost in selects of up 300%.<\/p>\n<p>Are you ready to Benchmark? I&#8217;m assuming that you&#8217;ve done a PHP \/ MySQL install before.<\/p>\n<p>Part I: Installation of MySQL 5.1<\/p>\n<ul>\n<li><a href=\"http:\/\/dev.mysql.com\/downloads\/mysql\/5.1.html\">Download<\/a> the beta version of MySQL 5.1\n<li>Back up your mysql database.\n<li><b>DO NOT RUN mysql_upgrade.<\/b> As of this blog posting, it does not work. You wil lock yourself out of your database and go through a painful rebuild process.\n<li>Install MySQL 5.1 fresh as a new install. If you&#8217;re me and want to use partitioning this means: .\/configure &#8211;prefix=\/usr\/local\/mysql &#8211;with-ssl &#8211;with-partition ; make ; make install\n<li>Restore the backed up mysql database.\n<li>Get your database up and running again.\n<\/ul>\n<p>Part II: Get benchmarking scripts up and running<\/p>\n<ul>\n<li>I used <a href=\"http:\/\/www.php.net\/\">PHP 5<\/a>, and PHP Pear&#8217;s <a href=\"http:\/\/pear.php.net\/package\/Benchmark\">Benchmark package<\/a>.\n<li>Download the <a href=\"\/Partition_benchmarks.tar.gz\">Partition Benchmarks scripts<\/a>.\n<li>3 types of tables are benchmarked: partitioned by software (i.e. Yahoo&#8217;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&#8217;re expecting, and 5 is the number of table partitions.\n<li>After the tables are built you can now test a select using the testDao.php script by typing: php testDao.php\n<\/ul>\n<p><a href=\"\/test2.txt\">The results I got<\/a> on a 1 Ghz PowerPC G4 running Mac OS X 10.4.9 are interesting.<\/p>\n<p>First I built a 5 partitioned tables spanning 10000 rows:<br \/>\nshell> php build_tables.php 10000 5<\/p>\n<p>Then I ran the tests on the 3 partition types: software, mysql, and non-partitioned:<br \/>\nshell> php testDao.php<\/p>\n<pre>\r\nElapsed time between Start and Test_Code_Partition: 0.059307\r\nElapsed time between Test_Code_Partition and DB_Partition: 0.005882\r\nElapsed time between DB_Partition and No_Partition: 0.003694\r\n------------------------------------------------------------------\r\nmarker                time index            ex time         perct   \r\n------------------------------------------------------------------\r\nStart                 1176964205.32067900   -                0.00%\r\n------------------------------------------------------------------\r\nTest_Code_Partition   1176964205.37998600   0.059307        85.83%\r\n------------------------------------------------------------------\r\nDB_Partition          1176964205.38586800   0.005882         8.51%\r\n------------------------------------------------------------------\r\nNo_Partition          1176964205.38956200   0.003694         5.35%\r\n------------------------------------------------------------------\r\nStop                  1176964205.38978100   0.000219         0.32%\r\n------------------------------------------------------------------\r\ntotal                 -                     0.069102       100.00%\r\n------------------------------------------------------------------\r\n<\/pre>\n<p>The non-partitioned set up ran the fastest &#8212; but that&#8217;s no surprise b\/c the number of rows aren&#8217;t large enough to take advantage of partitioning.<\/p>\n<p>Built-in Partitioning came in 2nd.<\/p>\n<p>PHP partitioning, or software partitioning came a distant 3rd.<\/p>\n<p>So what are you waiting for????<\/p>\n<p>If you need that urgent speed boost in your database-driven web application, get MySQL 5.1 now.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8217;re probably doing on the scripting side because of what Yahoo told you. I think improving the &#8220;front-end&#8221; is great and all, but if you look at the recent benchmarks below run [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,5],"tags":[],"class_list":["post-21","post","type-post","status-publish","format-standard","hentry","category-how-to","category-techbiz"],"_links":{"self":[{"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/posts\/21","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/comments?post=21"}],"version-history":[{"count":0,"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/posts\/21\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/media?parent=21"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/categories?post=21"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.codebelay.com\/blog\/wp-json\/wp\/v2\/tags?post=21"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}