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

Myisam / aria multithread contention

Details

    Description

      Hi Guys,
      found out some rather unexpected behaviour when trying to optimize some queries. Basically i have ID->LABEL mapping tables and another large statistical innodb table with several billion rows.

      When trying to optimize it, I just changed one large query to get data for eg. single month, to 30 queries, running it 10 at a time. Then the data is merged on the client. Works around 10x faster.

      Interesting thing is that it only works when all tables are innodb or toku. For myisam and aria when I run 10 threads simultaneously, only the load is ~x8 the normal... the execution time is as slow as if these 10 queries were run in serial not in parallel. For aria results are around 20% better than myisam. If I use innodb it's close to 10 times (1000%) faster.

      Unfortunately can't do testing using recent server version now, as the main table is close to 6b rows and over half TB in size. Could build some test case if anyone is interested in looking into this. Question is if myisam/aria are still developed or is it abandonware and we should just use innodb for everything?

      foreach ($dates as $date) {
      			$data = dbpool::fill_array("SELECT _subnet, user_id, sum(clicks) as clicks, sum(spending) as spending
      				FROM stats_creative
      				INNER JOIN {$this->tmp_domains} ON domain_id = {$this->tmp_domains}.id
      				INNER JOIN {$this->tmp_creative_to_uid} ON creative_id = {$this->tmp_creative_to_uid}.id
      				WHERE date IN ($$)
      				GROUP BY _subnet, user_id", $date);
      		}
      

      Attachments

        1. create_tables.sql
          1 kB
        2. img_run_parallel_aria.png
          img_run_parallel_aria.png
          127 kB
        3. img_run_parallel_myisam.png
          img_run_parallel_myisam.png
          136 kB
        4. img_run_parallel.png
          img_run_parallel.png
          104 kB
        5. img_run_single_query.png
          img_run_single_query.png
          122 kB
        6. run_parallel_aria.sh
          8 kB
        7. run_parallel_myisam.sh
          8 kB
        8. run_parallel.sh
          8 kB
        9. run_serial.sh
          7 kB
        10. run_single_query.sh
          0.3 kB

        Activity

          To start from the question, myisam/aria are supported and maintained, so not abandonware, bugs are being fixed. Not much of the new development is done there, though, but those queries of yours shouldn't be serialized, so it looks like a bug that needs to be fixed.

          How your tables are defined, can you paste the result of SHOW CREATE TABLE for them?

          serg Sergei Golubchik added a comment - To start from the question, myisam/aria are supported and maintained, so not abandonware, bugs are being fixed. Not much of the new development is done there, though, but those queries of yours shouldn't be serialized, so it looks like a bug that needs to be fixed. How your tables are defined, can you paste the result of SHOW CREATE TABLE for them?

          Hi Sergei, thanks for looking into this. Great that other engines are still being developed.

          Have prepared a test case, so you can just run create_tables SQL and then you can use shell scripts to run the stats in serial and in parallel. That was on different server so characteristics are little different but still similar.

          Innodb is much faster (~x5), and is taking advantage of multiple cores (47->11s runtime) when converting just the creative table which has only 50k rows to myisam or aria it gets x2.5 slower (close to 2 minutes) while still eating around 14 CPU cores.

          myisam and aria are faster than innodb when run on single core only (~44s).

          Parallel tests are using 15 threads at once.

          pslawek83 Slawomir Pryczek added a comment - Hi Sergei, thanks for looking into this. Great that other engines are still being developed. Have prepared a test case, so you can just run create_tables SQL and then you can use shell scripts to run the stats in serial and in parallel. That was on different server so characteristics are little different but still similar. Innodb is much faster (~x5), and is taking advantage of multiple cores (47->11s runtime) when converting just the creative table which has only 50k rows to myisam or aria it gets x2.5 slower (close to 2 minutes) while still eating around 14 CPU cores. myisam and aria are faster than innodb when run on single core only (~44s). Parallel tests are using 15 threads at once.

          Specs for the numbers:
          AMD Ryzen 9 5950X 16-Core Processor
          Distrib 10.6.3-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

          Not a huge issue for me and this test was not optimized, so just x5 increase with multiple queries but probably fixing it will improve server multicore perf in more cases...

          pslawek83 Slawomir Pryczek added a comment - Specs for the numbers: AMD Ryzen 9 5950X 16-Core Processor Distrib 10.6.3-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 Not a huge issue for me and this test was not optimized, so just x5 increase with multiple queries but probably fixing it will improve server multicore perf in more cases...

          People

            lstartseva Lena Startseva
            pslawek83 Slawomir Pryczek
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.