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

Research - Ideas about parallel query execution

Details

    Description

      Developing parallel query execution in MariaDB would enable the engine to exploit modern multi-core hardware by breaking down large or complex queries into smaller sub-tasks that can be processed concurrently.

      This approach promises to:

      Improve Performance and Scalability: By executing aggregates, joins, and sorts across multiple threads or nodes, query response times can scale linearly with available cores—vital for handling ever-growing data volumes.

      Enhance Resource Utilization: Parallel execution minimizes CPU idle time and better leverages disk and memory bandwidth, ensuring higher throughput on mixed OLTP/analytical workloads.

      Strengthen Competitiveness: As competitors like Oracle and PostgreSQL already provide parallel processing, integrating parallel query capabilities into the core server will be crucial for MariaDB to remain competitive and match the performance of other OLTP databases.

      This ticket is to research how parallel query can be approached, and the acceptance criteria is creation of the necessary stories to being the feature into the server.

      Previous ideas -
      Some ideas about using multiple threads to run a query.

      == Position at N% of table/index ==
      Consider queries

      select sum(a) from tbl group by non_key_col
      

      select sum(a) from tbl where key between C1 and C2 group by non_key_col
      

      If we want to run these with N threads, we need to give 1/Nth of table to each thread. (An alternative is to run one "reader" thread and distribute work to multiple compute threads. The problem with this is that reading from the table won't be parallel. This will put a cap on the performance.)

      In order to do that, we will need storage engine calls that do

      • "position at N% in the table"
      • "position at N% in the index range between [C1 and C2]".

      these calls would also let us build equi-height histograms based on sampling.

      == General execution ==
      There are many works about converting SQL into MapReduce jobs. Are they relevant to this task? The difference seems to be in the Map phase - we assume that source data is equi-distant to all worker threads.

      == Evaluation ==
      It would be nice to assess how much speedup we will get. In order to get an idea, we could break the query apart and run the parts manually. The merge step could also be done manually in some cases (by writing to, and reading from temporary tables).

      Attachments

        Issue Links

          Activity

            When Kentoku was onboarded he was supposed to work on SQL rewrite plugin that would have enable easy SQL syntax integration of such concurrent execution

            stephane@skysql.com VAROQUI Stephane added a comment - When Kentoku was onboarded he was supposed to work on SQL rewrite plugin that would have enable easy SQL syntax integration of such concurrent execution

            The first integration of this feature was possible on queries using a single table, but a following work was made that if other tables of in the query are federated to the same backend as the partition then it possible to used parallel partition scan as well.

            stephane@skysql.com VAROQUI Stephane added a comment - The first integration of this feature was possible on queries using a single table, but a following work was made that if other tables of in the query are federated to the same backend as the partition then it possible to used parallel partition scan as well.
            TheWitness Larry Adams added a comment - - edited

            Stephane,

            I've been going through the documentation, I don't think there is a real good write-up on how to run Spider queries in a true parallel map reduce fashion (or force it to). I'll do more investigation in my home lab. Thus far, I have distributed a years worth of tables (365) to 4 servers that are all separate from the server that will solve as the Spider server. So, I've got a good setup. When I did my first test in a customer environment, as mentioned, it was pure serial one server and one partition at a time (disappointing). But that environment used the spider server as a data server as well, so not sure if that had any impact.

            What I'm really trying to do I will attach as a screen grab.

            My plan will be to:

            1) Setup 20 servers to 4 back-ends
            2) Create a script to UNION 40 tables into a partition table (programmatically)
            3) Run some queries while watching the process list for parallelism.
            4) Tweak settings and repeat 3.

            Larry

            TheWitness Larry Adams added a comment - - edited Stephane, I've been going through the documentation, I don't think there is a real good write-up on how to run Spider queries in a true parallel map reduce fashion (or force it to). I'll do more investigation in my home lab. Thus far, I have distributed a years worth of tables (365) to 4 servers that are all separate from the server that will solve as the Spider server. So, I've got a good setup. When I did my first test in a customer environment, as mentioned, it was pure serial one server and one partition at a time (disappointing). But that environment used the spider server as a data server as well, so not sure if that had any impact. What I'm really trying to do I will attach as a screen grab. My plan will be to: 1) Setup 20 servers to 4 back-ends 2) Create a script to UNION 40 tables into a partition table (programmatically) 3) Run some queries while watching the process list for parallelism. 4) Tweak settings and repeat 3. Larry
            TheWitness Larry Adams added a comment -

            Stephane,

            With all those settings, zero performance difference. Each partition is handled serially. There is this document where Shiba-san was claiming that he had a patch that added parallelization and he provided no instructions in the write-up as to how to leverage it. I'm not sure what ever became of that though.

            https://blog.mariadb.org/wp-content/uploads/2014/05/Spider_in_MariaDB_20140403.pdf

            Larry

            TheWitness Larry Adams added a comment - Stephane, With all those settings, zero performance difference. Each partition is handled serially. There is this document where Shiba-san was claiming that he had a patch that added parallelization and he provided no instructions in the write-up as to how to leverage it. I'm not sure what ever became of that though. https://blog.mariadb.org/wp-content/uploads/2014/05/Spider_in_MariaDB_20140403.pdf Larry
            stephane@skysql.com VAROQUI Stephane added a comment - - edited

            Hi Larry you need to look at the spider test case and how it's done , one trick was to declare different server name and or user name on for each shareded partiition despite it is pointing to the same host port and always partition by range. Alos to not use joins in the first time. IWe have been abe to push down joins as well but with some tricks using regular replications and extar spider table pointing as mirror but may the new developers of spider broke this feature

            stephane@skysql.com VAROQUI Stephane added a comment - - edited Hi Larry you need to look at the spider test case and how it's done , one trick was to declare different server name and or user name on for each shareded partiition despite it is pointing to the same host port and always partition by range. Alos to not use joins in the first time. IWe have been abe to push down joins as well but with some tricks using regular replications and extar spider table pointing as mirror but may the new developers of spider broke this feature

            People

              Unassigned Unassigned
              psergei Sergei Petrunia
              Votes:
              12 Vote for this issue
              Watchers:
              19 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.