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

Improving memory utilization for information schema

    XMLWordPrintable

Details

    Description

      This task is about improving memory utilization and performance for
      Information schema

      Some work has recently been done in bb-10.2-ext to free memory early for
      tables and views used be performance schema. The next step is to create
      more efficient temporary tables that doesn't store information that we don't
      need.

      MariaDB [test]> select MEMORY_USED,MAX_MEMORY_USED from information_schema.processlist where db="test";
      +-------------+-----------------+
      | MEMORY_USED | MAX_MEMORY_USED |
      +-------------+-----------------+
      |       86120 |          245768 |
      +-------------+-----------------+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select table_name from information_schema.tables where table_schema="mysql";
      ....
      MariaDB [test]> select MEMORY_USED,MAX_MEMORY_USED from information_schema.processlist where db="test";
      +-------------+-----------------+
      | MEMORY_USED | MAX_MEMORY_USED |
      +-------------+-----------------+
      |       86120 |          696880 |
      +-------------+-----------------+
      

      Here we used 600K memory for a simple query

      MariaDB [test]> select count(*) from information_schema.tables where table_schema="mysql";
      MariaDB [test]> select table_name from information_schema.tables;
      ...
      MariaDB [test]> select MEMORY_USED,MAX_MEMORY_USED from information_schema.processlist where db="test";
      +-------------+-----------------+
      | MEMORY_USED | MAX_MEMORY_USED |
      +-------------+-----------------+
      |       86120 |         5293216 |
      +-------------+-----------------+
      

      Here we used 5M memory for a simple query over 341 tables.

      The reason for the excessive memory used comes from that the temporary table
      created has a very wide record:

      While running:

      select table_name from information_schema.tables;
      

      in gdb:

      (gdb) break handler::ha_write_tmp_row
      (gdb) p table->s->reclength
      $2 = 14829
      

      Two possible ways to fix this:

      1) Extend heap tables to store VARCHAR and BLOB efficiently
      2) In sql_show, change all fields that are not used to be CHAR(1)

      1) is a major tasks and we can't get that done in time for 10.3
      2) will help even if we do 1) as we have less to store.

      This task is to do 2)

      This should not be that hard as information_schema already knows which
      fields are accessed in the query. This is already used to decide if we
      can solve the information_schema access without opening the table.

      This should be done against the bb-10.2-ext tree, which has the new
      MAX_MEMORY_USED column in information_schema.processlist.

      Attachments

        1. improve_memory.diff
          4 kB
          musazhang
        2. improve_memory-final.diff
          8 kB
          Michael Widenius

        Activity

          People

            monty Michael Widenius
            monty Michael Widenius
            Votes:
            2 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.