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.
