Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19002

Partition performance optimization

Details

    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

      Attachments

        1. gmon_del_with_1024_partitions.zip
          485 kB
        2. gmon_del_without_partition.zip
          471 kB
        3. gmon_ins_with_1024_partitions.zip
          485 kB
        4. gmon_ins_without_partition.zip
          460 kB
        5. gmon_sel_with_1024_partitions.zip
          502 kB
        6. gmon_sel_without_partition.zip
          474 kB
        7. gmon_spider_del_with_1024_partitions_after.zip
          433 kB
        8. gmon_spider_del_with_1024_partitions_before.zip
          436 kB
        9. gmon_spider_del_without_partition.zip
          393 kB
        10. gmon_spider_ins_with_1024_partitions_after.zip
          407 kB
        11. gmon_spider_ins_with_1024_partitions_before.zip
          409 kB
        12. gmon_spider_ins_without_partition.zip
          375 kB
        13. gmon_spider_sel_with_1024_partitions_after.zip
          446 kB
        14. gmon_spider_sel_with_1024_partitions_before.zip
          452 kB
        15. gmon_spider_sel_without_partition.zip
          407 kB
        16. gmon_spider_upd_with_1024_partitions_after.zip
          448 kB
        17. gmon_spider_upd_with_1024_partitions_before.zip
          452 kB
        18. gmon_spider_upd_without_partition.zip
          399 kB
        19. gmon_upd_with_1024_partitions.zip
          490 kB
        20. gmon_upd_without_partition.zip
          470 kB
        21. Test_procedures_delete.txt
          1 kB
        22. Test_procedures_insert.txt
          1 kB
        23. Test_procedures_select.txt
          1 kB
        24. Test_procedures_update.txt
          1 kB
        25. test_results_of_spider.txt
          7 kB

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26158 SIGSEGV in spider_free_mem from ha_spider::open on INSERT

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26539 SIGSEGV in spider_check_and_set_trx_isolation and I_P_List_iterator from THD::drop_temporary_table (10.5.3 opt only) on ALTER

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26582 SIGSEGV in spider_db_bulk_insert and spider_db_connect and spider_db_before_query, and hang in "End of update loop" / "Reset for next command" query states

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27171 Spider: SIGSEGV in spider_set_direct_limit_offset on INSERT

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27239 Spider: Assertion `thd->transaction->stmt.ha_list == __null || trans == &thd->transaction->stmt' failed in ha_commit_trans on BEGIN WORK after FTWRL

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27240 SIGSEGV in ha_spider::store_lock on LOCK TABLE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30191 SIGSEGV & heap-use-after-free in spider_db_print_item_type, SIGABRT in __cxa_pure_virtual/spider_db_print_item_type, Got error 128 "Out of memory in engine", 56/112 memory not freed, and Assertion `fixed()' failed in Item_sp_variable::val_str on SP call

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31996 Segfault when setting spider_delete_all_rows to 0 and delete all rows of a spider table, ASAN heap-use-after-free in spider_db_delete_all_rows

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-20250 Implement early partition pruning before statement start

          • Critical - Crashes, loss of data, severe memory leak.
          • In Progress

          Activity

            Ascending order - Click to sort in descending order
            willhan willhan created issue -
            Kentoku Kentoku Shiba (Inactive) made changes -
            Field Original Value New Value
            Assignee Kentoku [ kentoku ]
            Kentoku Kentoku Shiba (Inactive) made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            Kentoku Kentoku Shiba (Inactive) made changes -
            Attachment gmon_with_1024_partition.zip [ 48018 ]
            Attachment gmon_without_partition.zip [ 48019 ]
            Attachment Test_procedures.txt [ 48020 ]
            Kentoku Kentoku Shiba (Inactive) made changes -
            Attachment gmonnpdel.txt [ 48026 ]
            Attachment gmonnpins.txt [ 48027 ]
            Attachment gmonnpupd.txt [ 48028 ]
            Attachment gmonptdel.txt [ 48029 ]
            Attachment gmonptins.txt [ 48030 ]
            Attachment gmoptpupd.txt [ 48031 ]
            Kentoku Kentoku Shiba (Inactive) made changes -
            Attachment gmoptpupd.txt [ 48031 ]
            Kentoku Kentoku Shiba (Inactive) made changes -
            Attachment gmon_without_partition.zip [ 48019 ]
            Kentoku Kentoku Shiba (Inactive) made changes -
            Attachment gmonnpdel.txt [ 48026 ]
            Kentoku Kentoku Shiba (Inactive) made changes -
            Attachment gmonnpupd.txt [ 48028 ]
            Kentoku Kentoku Shiba (Inactive) made changes -
            Attachment gmonptdel.txt [ 48029 ]
            Kentoku Kentoku Shiba (Inactive) made changes -
            Attachment gmonptins.txt [ 48030 ]
            Kentoku Kentoku Shiba (Inactive) made changes -
            Attachment gmonnpins.txt [ 48027 ]
            Kentoku Kentoku Shiba (Inactive) made changes -
            Attachment gmon_with_1024_partition.zip [ 48018 ]
            Kentoku Kentoku Shiba (Inactive) made changes -
            Attachment Test_procedures.txt [ 48020 ]
            Kentoku Kentoku Shiba (Inactive) made changes -
            Attachment gmon_del_with_1024_partitions.zip [ 48032 ]
            Attachment gmon_del_without_partition.zip [ 48033 ]
            Attachment gmon_ins_with_1024_partitions.zip [ 48034 ]
            Attachment gmon_ins_without_partition.zip [ 48035 ]
            Attachment gmon_sel_with_1024_partitions.zip [ 48036 ]
            Attachment gmon_sel_without_partition.zip [ 48037 ]
            Attachment gmon_upd_with_1024_partitions.zip [ 48038 ]
            Attachment gmon_upd_without_partition.zip [ 48039 ]
            Attachment Test_procedures_delete.txt [ 48040 ]
            Attachment Test_procedures_insert.txt [ 48041 ]
            Attachment Test_procedures_select.txt [ 48042 ]
            Attachment Test_procedures_update.txt [ 48043 ]
            Kentoku Kentoku Shiba (Inactive) made changes -
            Attachment test_results_of_spider.txt [ 48093 ]
            Kentoku Kentoku Shiba (Inactive) added a comment -

            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

            Kentoku Kentoku Shiba (Inactive) added a comment - 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
            Kentoku Kentoku Shiba (Inactive) made changes -
            Assignee Kentoku [ kentoku ] Michael Widenius [ monty ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            Kentoku Kentoku Shiba (Inactive) added a comment -

            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.

            Kentoku Kentoku Shiba (Inactive) added a comment - 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.
            Kentoku Kentoku Shiba (Inactive) made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            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 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
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s N/A [ 14700 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s N/A [ 14700 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Component/s Partitioning [ 10802 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.7 [ 24805 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            serg Sergei Golubchik made changes -
            Assignee Michael Widenius [ monty ] Alexey Botchkov [ holyfoot ]
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) made changes -
            Roel Roel Van de Paar made changes -
            Assignee Alexey Botchkov [ holyfoot ] Kentoku Shiba [ kentoku ]
            Roel Roel Van de Paar made changes -
            Fix Version/s N/A [ 14700 ]
            Fix Version/s 10.7 [ 24805 ]
            Resolution Fixed [ 1 ]
            Status In Review [ 10002 ] Closed [ 6 ]
            Roel Roel Van de Paar made changes -
            Resolution Fixed [ 1 ]
            Status Closed [ 6 ] Stalled [ 10000 ]
            Roel Roel Van de Paar made changes -
            Fix Version/s 10.7 [ 24805 ]
            Fix Version/s N/A [ 14700 ]
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) made changes -
            Assignee Kentoku Shiba [ kentoku ] Nayuta Yanagisawa [ JIRAUSER47117 ]
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) made changes -
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.8 [ 26121 ]
            Fix Version/s 10.7 [ 24805 ]
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) made changes -
            Fix Version/s 10.8 [ 26121 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 93418 ] MariaDB v4 [ 131719 ]
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) made changes -
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) made changes -
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) made changes -
            Assignee Nayuta Yanagisawa [ JIRAUSER47117 ] Alexey Botchkov [ holyfoot ]
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) made changes -
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) made changes -
            Assignee Alexey Botchkov [ holyfoot ] Nayuta Yanagisawa [ JIRAUSER47117 ]
            AirFocus AirFocus made changes -
            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             		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
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) made changes -
            ycp Yuchen Pei made changes -
            Assignee Nayuta Yanagisawa [ JIRAUSER47117 ] Alexey Botchkov [ holyfoot ]
            ycp Yuchen Pei made changes -

            People

              holyfoot Alexey Botchkov
              willhan willhan
              Votes:
              0 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.