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

Reduce performance_schema memory footprint

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

          Ascending order - Click to sort in descending order
          robertbindar Robert Bindar added a comment - - edited

          Hi valerii. I was able to reproduce your numbers on fresh 10.1 and 10.4, the memory consumption is indeed higher. Sergey and I did some analysis and it seems the sizes of PFS arrays were increased by this revision c9f612db (m_events_waits_history_sizing, m_events_stages_history_sizing, m_events_statements_history_sizing).
          Reverting those sizes in 10.4 seems to reduce the PFS memory consumption considerably.

          You reported 10.1 uses around 177k per connection and 10.4 around 250k. In my local setup 10.4 with reverted PFS sizes seems to use around 186k per connection. This explains more than 80% of the memory consumption difference.

          I attached here a diff of the PFS memory counters for max_connections=500. Left column is fresh 10.1, middle column fresh 10.4 and right column is 10.4 with reverted PFS array sizes.

          robertbindar Robert Bindar added a comment - - edited Hi valerii . I was able to reproduce your numbers on fresh 10.1 and 10.4, the memory consumption is indeed higher. Sergey and I did some analysis and it seems the sizes of PFS arrays were increased by this revision c9f612db (m_events_waits_history_sizing, m_events_stages_history_sizing, m_events_statements_history_sizing). Reverting those sizes in 10.4 seems to reduce the PFS memory consumption considerably. You reported 10.1 uses around 177k per connection and 10.4 around 250k. In my local setup 10.4 with reverted PFS sizes seems to use around 186k per connection. This explains more than 80% of the memory consumption difference. I attached here a diff of the PFS memory counters for max_connections=500. Left column is fresh 10.1, middle column fresh 10.4 and right column is 10.4 with reverted PFS array sizes.
          svoj Sergey Vojtovich added a comment - - edited

          In other words increased PFS memory usage was caused by adding more "stages". Though it is unclear why "waits" and "statements" histories had to be updated.

          valerii, is it acceptable to lower down relevant variables (on the customer's side), or do you feel like we should try spending more time on this and coming up with some other solution?

          svoj Sergey Vojtovich added a comment - - edited In other words increased PFS memory usage was caused by adding more "stages". Though it is unclear why "waits" and "statements" histories had to be updated. valerii , is it acceptable to lower down relevant variables (on the customer's side), or do you feel like we should try spending more time on this and coming up with some other solution?
          valerii Valerii Kravchuk added a comment - - edited

          Surely tuning P_S settings to decrease memory consumption is an option and it would be a good workaround for specific cases. But I'd prefer to see less usage in 10.4 vs 10.1 (or MySQL 5.6) by default (as this is what the majority of customers use when they enable P_S). Maybe defaults have to be changed for this. If there is anything you can do to use less memory with the same settings, that would be great and positive improvement.

          In this specific issue associated customer explicitly complained that MariaDB's memory usage (in all versions, 10.2 to 10.4) with the same explicit settings and high max_connections value is notably higher than for MySQL 5.5 or 5.7.

          Long term I'd prefer to see dynamic allocation improvements similar to what was done in MySQL.

          valerii Valerii Kravchuk added a comment - - edited Surely tuning P_S settings to decrease memory consumption is an option and it would be a good workaround for specific cases. But I'd prefer to see less usage in 10.4 vs 10.1 (or MySQL 5.6) by default (as this is what the majority of customers use when they enable P_S). Maybe defaults have to be changed for this. If there is anything you can do to use less memory with the same settings, that would be great and positive improvement. In this specific issue associated customer explicitly complained that MariaDB's memory usage (in all versions, 10.2 to 10.4) with the same explicit settings and high max_connections value is notably higher than for MySQL 5.5 or 5.7. Long term I'd prefer to see dynamic allocation improvements similar to what was done in MySQL.
          serg Sergei Golubchik added a comment -

          It's fixed in 10.5 that has both smaller autosize defaults and dynamic P_S allocation like in 5.7.

          We won't change defaults in in the old GA version, that is 10.4. Particularly as there is a very simple workaround — just set these table sizes manually from your my.cnf

          serg Sergei Golubchik added a comment - It's fixed in 10.5 that has both smaller autosize defaults and dynamic P_S allocation like in 5.7. We won't change defaults in in the old GA version, that is 10.4. Particularly as there is a very simple workaround — just set these table sizes manually from your my.cnf

          People

            serg Sergei Golubchik
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            8 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.