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

Too much memory is used by default by performance_schema

    Details

      Description

      It seems MariaDB 10.4.x (and maybe 10.3.x and 10.2.x) uses more memory for performance_schema (in average, per connection) with default setting comparing to MariaDB 10.1.x or MySQL 5.6, We can estimate is as 250K or so comparing to 176K or so in MySQL 5.6 or MariaDB 10.1.

      Consider the following test of 10.4.7:

      openxs@ao756:~/dbs/maria10.4$ bin/mysqld_safe --no-defaults --port=3308 --max_connections=500 --performance_schema=ON --socket=/tmp/mariadb.sock &
      ...
      openxs@ao756:~/dbs/maria10.4$ bin/mysql -uroot --socket=/tmp/mariadb.sock -e"select @@max_connections, @@open_files_limit, @@performance_schema, version()"
      +-------------------+--------------------+----------------------+----------------+
      | @@max_connections | @@open_files_limit | @@performance_schema | version() |
      +-------------------+--------------------+----------------------+----------------+
      | 500 | 4533 | 1 | 10.4.7-MariaDB |
      +-------------------+--------------------+----------------------+----------------+
      openxs@ao756:~/dbs/maria10.4$ bin/mysql -uroot --socket=/tmp/mariadb.sock -e"show engine performance_schema status" | grep performance_schema.memory;
      performance_schema performance_schema.memory 525023664
       
      openxs@ao756:~/dbs/maria10.4$ bin/mysql -uroot --socket=/tmp/mariadb.sock -e"shutdown"
      openxs@ao756:~/dbs/maria10.4$ bin/mysqld_safe --no-defaults --port=3308 --max_connections=600 --performance_schema=ON --socket=/tmp/mariadb.sock &
      [2] 3665
      [1] Done bin/mysqld_safe --no-defaults --port=3308 --max_connections=500 --performance_schema=ON --socket=/tmp/mariadb.sock
      openxs@ao756:~/dbs/maria10.4$ 190729 14:28:24 mysqld_safe Logging to '/home/openxs/dbs/maria10.4/data/ao756.err'.
      190729 14:28:24 mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/maria10.4/data
       
      openxs@ao756:~/dbs/maria10.4$ bin/mysql -uroot --socket=/tmp/mariadb.sock -e"select @@max_connections, @@open_files_limit, @@performance_schema, version()"
      +-------------------+--------------------+----------------------+----------------+
      | @@max_connections | @@open_files_limit | @@performance_schema | version() |
      +-------------------+--------------------+----------------------+----------------+
      | 600 | 4633 | 1 | 10.4.7-MariaDB |
      +-------------------+--------------------+----------------------+----------------+
      openxs@ao756:~/dbs/maria10.4$ bin/mysql -uroot --socket=/tmp/mariadb.sock -e"show engine performance_schema status" | grep performance_schema.memory;
      performance_schema performance_schema.memory 550662064
       
      The difference in memory usage by performance_schema is:
       
      MariaDB [(none)]> select 550662064 - 525023664;
      +-----------------------+
      | 550662064 - 525023664 |
      +-----------------------+
      | 25638400 |
      +-----------------------+
      1 row in set (0,000 sec)
       
      MariaDB [(none)]> select (550662064 - 525023664)/(100*1024);
      +------------------------------------+
      | (550662064 - 525023664)/(100*1024) |
      +------------------------------------+
      | 250.3750 |
      +------------------------------------+
      1 row in set (0,000 sec)
      

      So, with everything else default at a "scale" of max_connections = 500 ... 600 we see increase of 250K of memory per connection.

      If we check 10.1 the same way:

      openxs@ao756:~/dbs/maria10.1$ bin/mysql -uroot --socket=/tmp/mariadb.sock -e"select @@max_connections, @@open_files_limit, @@performance_schema, version()"
      +-------------------+--------------------+----------------------+-----------------+
      | @@max_connections | @@open_files_limit | @@performance_schema | version() |
      +-------------------+--------------------+----------------------+-----------------+
      | 500 | 4533 | 1 | 10.1.41-MariaDB |
      +-------------------+--------------------+----------------------+-----------------+
      openxs@ao756:~/dbs/maria10.1$ bin/mysql -uroot --socket=/tmp/mariadb.sock -e"show engine performance_schema status" | grep performance_schema.memory;
      performance_schema performance_schema.memory 482125360
       
      openxs@ao756:~/dbs/maria10.1$ bin/mysql -uroot --socket=/tmp/mariadb.sock -e"select @@max_connections, @@open_files_limit, @@performance_schema, version()"
      +-------------------+--------------------+----------------------+-----------------+
      | @@max_connections | @@open_files_limit | @@performance_schema | version() |
      +-------------------+--------------------+----------------------+-----------------+
      | 600 | 4633 | 1 | 10.1.41-MariaDB |
      +-------------------+--------------------+----------------------+-----------------+
      openxs@ao756:~/dbs/maria10.1$ bin/mysql -uroot --socket=/tmp/mariadb.sock -e"show engine performance_schema status" | grep performance_schema.memory;
      performance_schema performance_schema.memory 500224560
      openxs@ao756:~/dbs/maria10.1$ bin/mysql -uroot --socket=/tmp/mariadb.sock -e"select (500224560 - 482125360)/(100*1024)"
      +------------------------------------+
      | (500224560 - 482125360)/(100*1024) |
      +------------------------------------+
      | 176.7500 |
      +------------------------------------+
      

      we see about 177K is used per connection at the same "scale". So, where this increase comes from in MariaDB 10.4.x and can it be reduced?

      Upstream MySQL introduced dynamic memory allocation in 5.7 (see https://bugs.mysql.com/bug.php?id=68514), but until we port that it would be still useful to find out why more memory is used in recent versions (while the features are still mostly those of MySQL 5.6) and is there any way to reduce memory footprint. For "large" instances (with max_connections, max_open_files and/or table_open_cache/table_definition_cache set to 10-15K or more) the impact of performance_schema's default sizing is too high and may cause unexpected OOM killing of MariaDB server.

        Attachments

          Activity

            People

            • Assignee:
              svoj Sergey Vojtovich
              Reporter:
              valerii Valerii Kravchuk
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: