Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Won't Fix
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
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?
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.
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
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.