| Bug #105204 | ha_innopart traverse all partitions instead of only used partitions causing loss | ||
|---|---|---|---|
| Submitted: | 13 Oct 2021 11:22 | Modified: | 13 Oct 2021 11:54 |
| Reporter: | Hope Lee (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 8.0.26 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution | ||
[13 Oct 2021 11:23]
Hope Lee
[Optimize] Traverse the used partitions instead of all partitions (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-Optimize-Traverse-the-used-partitions-instead-of-all.patch (application/octet-stream, text), 3.39 KiB.
[13 Oct 2021 11:54]
MySQL Verification Team
Hello Lee, Thank you for the report and contribution. regards, Umesh
[1 Nov 2021 8:41]
Hope Lee
Update the patch (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-Optimize-Traverse-the-used-partitions-instead-of-all.patch (application/octet-stream, text), 3.59 KiB.
Description: In innobase handler for partitioned table codes, there exist several places where the server traverses all partitions like ha_innopart::store_lock(), ha_innopart::external_lock() and ha_innopart::clear_blob_heaps(). The partitions not used in the query are also traversed, which is unnecessary and loss to performance. How to repeat: Set breakpoints at the above functions. CREATE TABLE tr ( id INT, name VARCHAR(50), purchased DATE ) PARTITION BY RANGE (YEAR(purchased)) ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN (2005), PARTITION p4 VALUES LESS THAN (2010), PARTITION p5 VALUES LESS THAN (2015) ); SELECT * FROM tr PARTITION(p0); Execute this query, you can see the server traverses all partitions instead of only used partition p0. Suggested fix: If we optimize the codes and let the server only traverse the partitions used in this query, like the following example (the complete changes are included in the patch). We can gain performance improvement. --- a/storage/innobase/handler/ha_innopart.cc +++ b/storage/innobase/handler/ha_innopart.cc @@ -4033,7 +4034,8 @@ int ha_innopart::external_lock(THD *thd, int lock_type) { m_prebuilt->table = m_part_share->get_table_part(0); error = ha_innobase::external_lock(thd, lock_type); - for (uint i = 0; i < m_tot_parts; i++) { + for (uint i = m_part_info->get_first_used_partition(); i < m_tot_parts; + i = m_part_info->get_next_used_partition(i)) { dict_table_t *table = m_part_share->get_table_part(i); switch (table->quiesce) { We make sysbench lua script support partitioned table to evaluate performance improvement by oltp_point_select.lua. The partitioned table contains 1000 partitions and 100 records for each partition. We use 10 client threads to simply prove the performance effect. Before optimization: Throughput: events/s (eps): 120421.8384 time elapsed: 180.0025s total number of events: 21676232 After optimization: Throughput: events/s (eps): 124797.5937 time elapsed: 180.0025s total number of events: 22463880 The performance improvement rate is about 3.5%.