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

DB Hang - an internal temporary table and aria_pagecahce_buffer_size

    XMLWordPrintable

Details

    Description

      Our development team had run a query that uses the complex view.
      When the query was executed by a single thread, it was no problem. (it took about 90 seconds.)
      When the query was executed by multi threads, it had cause DB hang.
      It did never-ending and the other threads could not even run a simple query like "desc table_name".
      In the processlist, thread status was "Copyint to tmp table"

      We performed test by changing parameter value in my.ncf.
      We found that the value of aria_pagecahce_buffer_size is problem.
      In my.cnf , the value of "aria_pagecahce_buffer_size" was 1M.
      We changed the value from 1M to 128M and the query was executed without DB hang.

      I think the DB hang occurs when the following conditions combined.

      • A small size of "aria_pagecahce_buffer_size" .
      • At the same time to run a query that uses a large internal temporary table from multiple threads.

      What is this cause to have problem?

      I attach a script to create a test environment.
      Thanks.

      Attachments

        1. my.cnf
          7 kB
          Yi Yun Jung
        2. test_schema.sql
          5.44 MB
          Yi Yun Jung
        3. issue_query.sql
          6 kB
          Yi Yun Jung

        Activity

          People

            monty Michael Widenius
            Yunjung Yi Yun Jung (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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