tag:blogger.com,1999:blog-16959946.post3808783844661595298..comments2023-12-09T16:44:47.897+01:00Comments on The Data Charmer: Two quick performance tips with MySQL 5.1 partitionsGiuseppe Maxiahttp://www.blogger.com/profile/15801583338057324813noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-16959946.post-58192887709555094602011-02-22T17:09:54.311+01:002011-02-22T17:09:54.311+01:00Does this mean inserts on 5.1 would be slower if I...Does this mean inserts on 5.1 would be slower if I use myisam? Can it be rectified by using innodb? Or does it need a version upgrade to 5.5?Unknownhttps://www.blogger.com/profile/15842372305183974938noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-29519793833292706662010-05-07T18:26:43.559+02:002010-05-07T18:26:43.559+02:00Hi Giuseppe.
There is no table lock in partition...Hi Giuseppe. <br /><br />There is no table lock in partitioning, not even in 5.1!<br /><br />bug#37252 is that open and lock is not prunable.<br /><br />All locking is handled in the underlying storage engines, the general partitioning handler does only forward the locking calls.<br /><br />So partitioning should lead to less mutex contention also in 5.1 (but will probably also hit the dictionary mutex as shown in Dimitri's benchmark).<br /><br />Since the benchmark only uses 3 partitions, the overhead of bug#37252 is ignorable, but the mutex contention seems to drop and increases the performance.<br /><br />Sorry Giuseppe if I have not been clear enough about the problem with bug#37252.Unknownhttps://www.blogger.com/profile/17754461110335991577noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-57987418062629718102010-05-07T18:03:35.869+02:002010-05-07T18:03:35.869+02:00Dimitri,
The improvement on locking happens only o...Dimitri,<br />The improvement on locking happens only on 5.5 innodb, or am I missing something?<br />In MySQL 5.1, the whole table is locked, regardless of the engine. <br />http://bugs.mysql.com/bug.php?id=37252<br /><br />GiuseppeGiuseppe Maxiahttps://www.blogger.com/profile/15801583338057324813noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-57099517422149329062010-05-07T17:43:30.132+02:002010-05-07T17:43:30.132+02:00Giuseppe, it'll be fine to update your blog re...Giuseppe, it'll be fine to update your blog regarding the statement about "table lock on insert" - many people will not follow comments and stay with idea that partitions are dangerous for OLTP..<br /><br />While the table lock is true for MyISAM, it's not true for InnoDB, and recently I've published the benchmark results showing performance improvements when partitions are used: http://dimitrik.free.fr/blog/archives/2010/05/mysql-performance-improving-stability.html<br /><br />Rgds,<br />-DimitriUnknownhttps://www.blogger.com/profile/02492844539822196005noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-193640060956834332010-05-07T11:28:23.808+02:002010-05-07T11:28:23.808+02:00Matthew,
Indeed. MySQL 5.5 solves the issue nicely...Matthew,<br />Indeed. MySQL 5.5 solves the issue nicely. I forgot to mention it. Since I wrote two articles on this matter, perhaps I felt that everyone knows.<br />Thanks for the reminder<br />GiuseppeGiuseppe Maxiahttps://www.blogger.com/profile/15801583338057324813noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-55547810145928001142010-05-07T10:25:38.978+02:002010-05-07T10:25:38.978+02:00'Whenever you insert a record, the partitionin...'Whenever you insert a record, the partitioning engine locks the entire table' is easy to misunderstand:<br /><br />What happens is that it does forward the lock requests to ALL partitions, it does not do any additional locking besides the locking the underlying storage engines does! So if you use partitioned InnoDB it will still use row-locks, but since open and locking is done before pruning, it will call open for all partitions and then lock for all partitions, and that is bug#37252.<br /><br />What this means is that you can gain on concurrent inserts, due to all partitions are independent, but each statement has an overhead which becomes higher the more partitions you have.<br /><br />One related thing that decreased the overhead was to limit ha_partition::records_in_range (http://bugs.mysql.com/bug.php?id=48846)Unknownhttps://www.blogger.com/profile/17754461110335991577noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-46043622296764160542010-05-06T20:45:14.416+02:002010-05-06T20:45:14.416+02:00Thankfully this is fixed in 5.5 with the PARTITION...Thankfully this is fixed in 5.5 with the PARTITION BY RANGE COLUMNS() feature. It will no longer require TO_DAYS() and will place those invalid dates in line with valid dates.<br /><br />mysql> CREATE TABLE partition_date_test (dt DATE PRIMARY KEY)<br />-> PARTITION BY RANGE COLUMNS(dt) (<br />-> PARTITION day_20100226 VALUES LESS THAN ('2010-02-27'),<br />-> PARTITION day_20100227 VALUES LESS THAN ('2010-02-28'),<br />-> PARTITION day_20100228 VALUES LESS THAN ('2010-03-01'),<br />-> PARTITION day_20100301 VALUES LESS THAN ('2010-03-02'),<br />-> PARTITION day_20100302 VALUES LESS THAN ('2010-03-03'),<br />-> PARTITION day_20100303 VALUES LESS THAN ('2010-03-04'),<br />-> PARTITION max VALUES LESS THAN MAXVALUE);<br />Query OK, 0 rows affected (0.06 sec)<br /><br />mysql> SET SQL_MODE = 'allow_invalid_dates';<br />Query OK, 0 rows affected (0.00 sec)<br /><br />mysql> INSERT INTO partition_date_test VALUES ('2010-02-26'),('2010-02-27'),('2010-02-28'),('2010-02-29'),('2010-03-00'),('2010-03-02'),('2010-03-03'),('2010-03-04');<br />Query OK, 8 rows affected (0.00 sec)<br />Records: 8 Duplicates: 0 Warnings: 0<br /><br />mysql> SET SQL_MODE = '';<br />Query OK, 0 rows affected (0.00 sec)<br /><br />mysql> EXPLAIN PARTITIONS SELECT dt FROM partition_date_test WHERE dt BETWEEN '2010-02-28' AND '2010-03-02';<br />+----+-------------+---------------------+----------------------------------------+-------+---------------+---------+---------+------+------+--------------------------+<br />| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |<br />+----+-------------+---------------------+----------------------------------------+-------+---------------+---------+---------+------+------+--------------------------+<br />| 1 | SIMPLE | partition_date_test | day_20100228,day_20100301,day_20100302 | index | PRIMARY | PRIMARY | 3 | NULL | 4 | Using where; Using index |<br />+----+-------------+---------------------+----------------------------------------+-------+---------------+---------+---------+------+------+--------------------------+<br />1 row in set (0.01 sec)Matthew Montgomeryhttps://www.blogger.com/profile/04421029174237420523noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-6126240440807965692010-05-06T19:02:04.518+02:002010-05-06T19:02:04.518+02:00Thanks for this post. The #2 issue is one I have j...Thanks for this post. The #2 issue is one I have just tackled a couple days ago. Great to have this clarified!Shlomi Noachhttps://www.blogger.com/profile/11874165719204714241noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-61430980148463108662010-05-06T12:26:21.327+02:002010-05-06T12:26:21.327+02:00Great post!
Thanks for explaining, and thanks fo...Great post! <br /><br />Thanks for explaining, and thanks for the workarounds.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.com