[MDEV-19002] Partition performance optimization Created: 2019-03-21  Updated: 2023-12-07

Status: Stalled
Project: MariaDB Server
Component/s: Partitioning, Server
Fix Version/s: None

Type: Task Priority: Major
Reporter: willhan Assignee: Alexey Botchkov
Resolution: Unresolved Votes: 0
Labels: None

Attachments: Text File Test_procedures_delete.txt     Text File Test_procedures_insert.txt     Text File Test_procedures_select.txt     Text File Test_procedures_update.txt     Zip Archive gmon_del_with_1024_partitions.zip     Zip Archive gmon_del_without_partition.zip     Zip Archive gmon_ins_with_1024_partitions.zip     Zip Archive gmon_ins_without_partition.zip     Zip Archive gmon_sel_with_1024_partitions.zip     Zip Archive gmon_sel_without_partition.zip     Zip Archive gmon_spider_del_with_1024_partitions_after.zip     Zip Archive gmon_spider_del_with_1024_partitions_before.zip     Zip Archive gmon_spider_del_without_partition.zip     Zip Archive gmon_spider_ins_with_1024_partitions_after.zip     Zip Archive gmon_spider_ins_with_1024_partitions_before.zip     Zip Archive gmon_spider_ins_without_partition.zip     Zip Archive gmon_spider_sel_with_1024_partitions_after.zip     Zip Archive gmon_spider_sel_with_1024_partitions_before.zip     Zip Archive gmon_spider_sel_without_partition.zip     Zip Archive gmon_spider_upd_with_1024_partitions_after.zip     Zip Archive gmon_spider_upd_with_1024_partitions_before.zip     Zip Archive gmon_spider_upd_without_partition.zip     Zip Archive gmon_upd_with_1024_partitions.zip     Zip Archive gmon_upd_without_partition.zip     Text File test_results_of_spider.txt    
Issue Links:
Problem/Incident
causes MDEV-26158 SIGSEGV in spider_free_mem from ha_sp... Closed
causes MDEV-26539 SIGSEGV in spider_check_and_set_trx_i... Closed
causes MDEV-26582 SIGSEGV in spider_db_bulk_insert and ... Closed
causes MDEV-27171 Spider: SIGSEGV in spider_set_direct_... Closed
causes MDEV-27239 Spider: Assertion `thd->transaction->... Closed
causes MDEV-27240 SIGSEGV in ha_spider::store_lock on L... Closed
causes MDEV-30191 SIGSEGV & heap-use-after-free in spid... Closed
causes MDEV-31996 Segfault when setting spider_delete_a... Closed
Relates
relates to MDEV-20250 do not open partitions excluded by au... Stalled

 Description   

Add the partition options of the following function for batch call instead of each partition

  • store_lock
  • external_lock
  • start_stmt
  • extra
  • cond_push
  • info_push
  • top_table

original description:
Partition performance optimization: improve the select/insert/delete/update/replace performance of the partition table under multiple partitions by executing prune_partition before the lock table

 Comments   
Comment by Kentoku Shiba (Inactive) [ 2019-05-20 ]

When increasing number of partitions, performance is dropping down even if all partitions are quite fast.
So, I added some options to partition handler for calling at once instead of calling each partitions.
Please review the following commit especially changing of handler and partition handler.
ae106f1

Comment by Kentoku Shiba (Inactive) [ 2019-10-20 ]

gmon_*_without_partition.zip is call stack of MyISAM table without partitioning.
gmon_*_with_1024_partitions.zip is call stack of MyISAM table with partitioning.
Above call stacks are for knowing this performance dropping down with partitioning is not only caused by Spider.

gmon_spider_*_without_partition.zip is call stack of Spider table without partitioning.
gmon_spider_*_with_1024_partitions_before.zip is call stack of Spider table with partitioning before using new options.
gmon_spider_*_with_1024_partitions_after.zip is call stack of Spider table with partitioning after using new options.
Test_procedures_*.txt is test sequences.
test_results_of_spider.txt is the results of performance testing.

Generated at Thu Feb 08 08:48:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.