Bug #60190 | partition prune on insert/delete/update | ||
---|---|---|---|
Submitted: | 21 Feb 2011 15:42 | Modified: | 29 Apr 2013 15:02 |
Reporter: | Roberto Spadim (Basic Quality Contributor) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S2 (Serious) |
Version: | 5.1.32 | OS: | Linux |
Assigned to: | Jon Stephens | CPU Architecture: | Any |
Tags: | partition insert, qc |
[21 Feb 2011 15:42]
Roberto Spadim
[21 Feb 2011 15:54]
Valeriy Kravchuk
Our manual, http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html, clearly says: "For a table that is partitioned by KEY, has a composite primary key, and uses a composite partitioning key, it is possible to perform pruning for queries meeting the following two criteria: - The query must have a WHERE clause of the form pkcol1 = c1 AND pkcol2 = c2 AND ... pkcolN = cN, where pkcol1 ... pkcolN are the partitioning key columns and c1 ... cN are constant values. - All columns of the partitioning key must be referenced in the WHERE clause." Your INSERT does not (and can not) have WHERE clause, so I am not sure we can call this a bug...
[21 Feb 2011 17:25]
Davi Arnaut
Hum, most DDLs take table level exclusive locks.
[21 Feb 2011 17:38]
Roberto Spadim
i will change to feature request, since manual don´t cover INSERT, ALTER, DELETE, UPDATE, TRUNCATE, REPAIR, OPTIMIZE, CHECK
[21 Feb 2011 17:40]
Roberto Spadim
yeah, the alter table take a table lock maybe we could change alter table XXX YYYY partition, to partition lock (not table lock) = it´s a feature request =)
[3 Mar 2011 10:50]
Mattias Jonsson
In 5.5 all partitions are locked, since pruning is done in optimizing phase, which require the tables to be locked (Bug#37252). This is somewhat fixed by WL#5217, which is just pushed to mysql-trunk. And will be fixed in a more general way by WL#4443. Inserts are not currently pruned, since it requires to loop through all records before locking the table, by that may be included in WL#4443. DELETE and UPDATE are pruned (scanning wise) just like SELECT. So the real problem is ALTER TABLE, since it takes an exclusive table lock (which must be taken when altering the .frm file). But ALTER TABLE t ANALYZE/CHECK/OPTIMIZE/REPAIR PARTITION p is actually executed the same way as ANALYZE/.../REPAIR TABLE but per partition. It would need exclusive lock on that partition though, which is not possible on the current meta data locking scheme.
[3 Mar 2011 15:15]
Roberto Spadim
with all works that's it. hum... .frm files, yeah, now i see was it is locked... since it don't use always frm to optimize/repair, we change the lock when running partition tables? not a 'exclusive' lock for .frm, but a 'read lock' (no writers) to .frm files and just run on partitions not at full tables, just one idea... you know better mysql/myisam/partition/innodb codes another question.. why insert don't use partition prone? for example insert into table set field=value_for_partition_1, will insert execute on partitions 2,3,4,5,6... or only on partition 1?
[3 Mar 2011 15:27]
Roberto Spadim
sorry bad text hum... .frm files, yeah, now i see *why* it is locked... since it don't use always *.frm* to optimize/repair, we *could* change the lock when running partition tables? not a 'exclusive' lock for .frm, but a 'read lock' (no writers) to .frm files and just run on partitions not at full tables, just one idea... you know better mysql/myisam/partition/innodb codes
[29 Apr 2013 14:57]
Jon Stephens
This is not a feature request; it's a documentation issue.
[29 Apr 2013 15:02]
Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.
[29 Apr 2013 15:04]
Jon Stephens
Fixed in mysqldoc rev 35030. The issue was that we did not state explicitly what was already supported. To be clear, in MySQL 5.5+, pruning is supported for SELECT, UPDATE, DELETE; it is not supported for INSERT. Thanks.
[29 Jul 2014 10:12]
Mattias Jonsson
Also INSERT are pruned in 5.6+ (see WL#4443).