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

Information schema query producing many disk temp table

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.3.16
    • N/A
    • Information Schema
    • None

    Description

      On slow SATA disk our monitoring API try to compute CRC32 of DDL using information schema

      Such trivial queries piling in processlist up to 40s , and so guessing a table lock is hold for the same duration .

      Profiling show the time is spend in removing temp tables

      | Removing tmp table     | 7.341188 |
       
       
      | Created_tmp_disk_tables | 132   |
       
       
      MariaDB [(none)]> SELECT CRC32(b.column_name) + CRC32(b.column_type)+CRC32(COALESCE(b.is_nullable,''))+CRC32(COALESCE(b.CHARACTER_SET_NAME,''))+ CRC32(COALESCE(b.COLLATION_NAME,''))+ CRC32(COALESCE(b.COLUMN_DEFAULT,''))+CRC32(COALESCE(c.CONSTRAINT_NAME,''))+CRC32(COALESCE(c.ORDINAL_POSITION,'')) as CRC  FROM information_schema.COLUMNS b left join information_schema.KEY_COLUMN_USAGE c ON b.table_schema=c.table_schema  AND  b.table_name=c.table_name where b.table_schema='wpmixrgen' AND  b.table_name='wp_comments';
      +-------------+
      | CRC         |
      +-------------+
      | 10559682178 |
      | 13586448768 |
      | 15640549970 |
      | 17764828815 |
      | 18237274403 |
      | 18860079141 |
      | 11228629995 |
      | 11446859360 |
      | 14178433118 |
      | 15610854266 |
      | 19381875266 |
      | 17733767415 |
      | 18959763247 |
      | 17370551431 |
      | 16025333695 |
      +-------------+
      15 rows in set (0.019 sec)
       
      MariaDB [(none)]> show status like '%tmp%';
      +-------------------------+-------+
      | Variable_name           | Value |
      +-------------------------+-------+
      | Created_tmp_disk_tables | 144   |
      

      Found a workaround by using show create table instead but i d like to understand why information_schema produce multiple on disk temp tables , any pointer would be appreciate , thanks i advance .

      Found that changing join level from 6 to 2 change from BNLH to (flat, BNL ) and reduce the number of temp table per 2 . Still so few rows should not produce disk temp table but memory instead no ?

      Attachments

        Issue Links

          Activity

            serg Sergei Golubchik added a comment - - edited

            on-disk temporary table is created to store the values of `column_type` column. because MEMORY tables cannot have BLOB columns, as you know.

            serg Sergei Golubchik added a comment - - edited on-disk temporary table is created to store the values of `column_type` column. because MEMORY tables cannot have BLOB columns, as you know.

            This is not a bug, everything works as intended.

            We want to optimize that in the future and not create temp tables in this case. But still, it's not a bug.

            serg Sergei Golubchik added a comment - This is not a bug, everything works as intended. We want to optimize that in the future and not create temp tables in this case. But still, it's not a bug.

            Thanks i did not know every request to information_schema produce extra memory table as middle man resultset i was just thinking the data can flow from memory to thread result buffer directly, if not the case of course that's not a bug

            stephane@skysql.com VAROQUI Stephane added a comment - Thanks i did not know every request to information_schema produce extra memory table as middle man resultset i was just thinking the data can flow from memory to thread result buffer directly, if not the case of course that's not a bug
            stephane@skysql.com VAROQUI Stephane added a comment - - edited

            Also Monty told me once, aria was now used for temporary tables to avoid touching the disks, was just curious to notice it's not case for IS as well as this make monitoring from IS almost a no go !

            stephane@skysql.com VAROQUI Stephane added a comment - - edited Also Monty told me once, aria was now used for temporary tables to avoid touching the disks, was just curious to notice it's not case for IS as well as this make monitoring from IS almost a no go !

            Yes, every request to information_schema produces a temporary table. If all requested columns are supported by the MEMORY engine then the table is created using MEMORY engine. But if there are blob columns, the engine will be Aria.

            Aria temporary tables don't necessarily go to disk, as far as I understand. They can stay in the page cache and then these pages can be discarded when a table is dropped without ever being written to disk. But if the page cache will need to free some pages, it might write temp table pages to disk.

            serg Sergei Golubchik added a comment - Yes, every request to information_schema produces a temporary table. If all requested columns are supported by the MEMORY engine then the table is created using MEMORY engine. But if there are blob columns, the engine will be Aria. Aria temporary tables don't necessarily go to disk, as far as I understand. They can stay in the page cache and then these pages can be discarded when a table is dropped without ever being written to disk. But if the page cache will need to free some pages, it might write temp table pages to disk.

            People

              sanja Oleksandr Byelkin
              stephane@skysql.com VAROQUI Stephane
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.