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

Myisam / aria multithread contention

    XMLWordPrintable

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

          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.