X
  • MySQL
    December 17, 2014

Memory summary tables in Performance Schema in MySQL 5.7

Guest Author
One of great innovation in MySQL 5.7 is memory summary tables в Performance Schema and corresponding views in sys schema

And as troubleshooting freak I have huge reason to greet this feature.

Before version 5.7 we had very limited abilities to diagnose memory issues in MySQL. We could use operating system tools, such as vmstat, top, free, but they only showed what MySQL server uses memory, but do not show how. In version 5.7 things changed.

Lets examine what can we study about memory usage by MySQL Server.

At first, this is total amount of memory, used by all internal MySQL structures:

mysql> select * from sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 458.44 MiB      |
+-----------------+



There is also similar statistics by hosts, users and threads, including those which are created for user connections:


mysql> select host,
current_count_used ccu, current_allocated, current_avg_alloc, current_max_alloc, total_allocated from sys.memory_by_host_by_current_bytes WHERE host IS NOT NULL;
+------------+------+-------------------+-------------------+-------------------+-----------------+
| host       | ccu  | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+------------+------+-------------------+-------------------+-------------------+-----------------+
| background | 2773 | 10.84 MiB         | 4.00 KiB          | 8.00 MiB          | 30.69 MiB       |
| localhost  | 1509 | 809.30 KiB        | 549 bytes         | 176.38 KiB        | 83.59 MiB       |
+------------+------+-------------------+-------------------+-------------------+-----------------+

mysql> select
host, current_count_used ccu, current_allocated, current_avg_alloc, current_max_alloc, total_allocated from sys.memory_by_user_by_current_bytes;
+------+------+-------------------+-------------------+-------------------+-----------------+
| user |  ccu | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+------+------+-------------------+-------------------+-------------------+-----------------+
| root | 1401 | 1.09 MiB          | 815 bytes         | 334.97 KiB        | 42.73 MiB       |
| mark |  201 | 496.08 KiB        | 2.47 KiB          | 334.97 KiB        | 5.50 MiB        |
+------+------+-------------------+-------------------+-------------------+-----------------+

mysql> select thread_id tid, user, 
current_count_used ccu, current_allocated ca, current_avg_alloc caa, current_max_alloc cma, total_allocated from sys.memory_by_thread_by_current_bytes;
+-----+-------------------------+--------+-------------+-----------+-----------+-----------------+
| tid | user                    |    ccu |          ca |       caa |       cma | total_allocated |
+-----+-------------------------+--------+-------------+-----------+-----------+-----------------+
|   1 | sql/main                | 660327 | 2.53 GiB    | 4.01 KiB  | 2.10 GiB  | 2.69 GiB        |
| 150 | root@127.0.0.1          |    620 | 4.06 MiB    | 6.71 KiB  | 2.00 MiB  | 32.17 MiB       |
| 146 | sql/slave_sql           |     38 | 1.31 MiB    | 35.37 KiB | 1.00 MiB  | 1.44 MiB        |
| 145 | sql/slave_io            |    102 | 1.08 MiB    | 10.84 KiB | 1.00 MiB  | 2.79 MiB        |
...
|  92 | innodb/io_write_thread  |      0 | 0 bytes     | 0 bytes   | 0 bytes   | 384 bytes       |
| 124 | innodb/io_write_thread  |      0 | 0 bytes     | 0 bytes   | 0 bytes   | 384 bytes       |
|  28 | innodb/io_read_thread   |      0 | 0 bytes     | 0 bytes   | 0 bytes   | 2.25 KiB        |
|  60 | innodb/io_read_thread   |      0 | 0 bytes     | 0 bytes   | 0 bytes   | 384 bytes       |
| 139 | innodb/srv_purge_thread |    -24 | -328 bytes  | 14 bytes  | 272 bytes | 754.21 KiB      |
|  69 | innodb/io_write_thread  |    -14 | -1008 bytes | 72 bytes  | 0 bytes   | 34.28 KiB       |
|  68 | innodb/io_write_thread  |    -20 | -1440 bytes | 72 bytes  | 0 bytes   | 298.05 KiB      |
|  74 | innodb/io_write_thread  |    -23 | -1656 bytes | 72 bytes  | 0 bytes   | 103.55 KiB      |
|   4 | innodb/io_log_thread    |    -40 | -2880 bytes | 72 bytes  | 0 bytes   | 132.38 KiB      |
|  72 | innodb/io_write_thread  |   -106 | -7632 bytes | 72 bytes  | 0 bytes   | 1.10 MiB        |
+-----+-------------------------+--------+-------------+-----------+-----------+-----------------+
145 rows in set (2.65 sec)


This way you can find out, for example, which thread used most memory and kill it, if necessary.

But it is not always good idea to kill a thread even if it is certain that it leaks memory. For example, in case, described in bug #69848, it is not good idea to kill Slave IO and SQL threads. Although one of them, certainly, leaks memory.

You can see on this output how slave IO thread used more and more memory until I stopped my test. But after two cups of tea memory usage was still high and stayed at 1.04 GiB RAM even on idle server:

mysql> select thread_id tid, user, current_count_used ccu, current_allocated ca, current_avg_alloc caa, current_max_alloc cma, total_allocated from sys.memory_by_thread_by_current_bytes where thread_id in (145, 146);
+-----+---------------+--------+------------+-----------+------------+-----------------+
| tid | user          |    ccu |         ca |       caa |        cma | total_allocated |
+-----+---------------+--------+------------+-----------+------------+-----------------+
| 145 | sql/slave_io  | 170357 | 501.41 MiB | 3.01 KiB  | 496.90 MiB | 8.06 GiB        |
| 146 | sql/slave_sql |  10133 | 1.54 MiB   | 159 bytes | 1.00 MiB   | 10.38 GiB       |
+-----+---------------+--------+------------+-----------+------------+-----------------+
2 rows in set (2.76 sec)

...

mysql> select thread_id tid, user, current_count_used ccu, current_allocated ca, current_avg_alloc caa, current_max_alloc cma, total_allocated from sys.memory_by_thread_by_current_bytes where thread_id in (145, 146);
+-----+---------------+--------+------------+-----------+------------+-----------------+
| tid | user          |    ccu |         ca |       caa |        cma | total_allocated |
+-----+---------------+--------+------------+-----------+------------+-----------------+

| 145 | sql/slave_io  | 229012 | 641.95 MiB | 2.87 KiB  | 636.07 MiB | 10.32 GiB       |
| 146 | sql/slave_sql |  14033 | 1.61 MiB   | 120 bytes | 1.00 MiB   | 10.79 GiB       |
+-----+---------------+--------+------------+-----------+------------+-----------------+
2 rows in set (3.04 sec)

...

mysql> select thread_id tid, user, current_count_used ccu, current_allocated ca, current_avg_alloc caa, current_max_alloc cma, total_allocated from sys.memory_by_thread_by_current_bytes where thread_id in (145, 146);
+-----+---------------+--------+------------+-----------+------------+-----------------+
| tid | user          |    ccu |         ca |       caa |        cma | total_allocated |
+-----+---------------+--------+------------+-----------+------------+-----------------+

| 145 | sql/slave_io  | 412396 | 1.04 GiB   | 2.64 KiB  | 1.03 GiB   | 17.10 GiB       |
| 146 | sql/slave_sql |  26083 | 1.79 MiB   | 72 bytes  | 1.00 MiB   | 12.03 GiB       |
+-----+---------------+--------+------------+-----------+------------+-----------------+
2 rows in set (2.79 sec)


In this context we'd rather find out how it is used in order to decide if we can find workaround for it. In version 5.7 we have a tool which gives us this information! This is table performance_schema.memory_summary_by_thread_by_event_name and its column CURRENT_NUMBER_OF_BYTES_USED, which contains amount of currently used memory.

mysql> select THREAD_ID tid,EVENT_NAME,COUNT_ALLOC ca,SUM_NUMBER_OF_BYTES_ALLOC snba,SUM_NUMBER_OF_BYTES_FREE as bf,CURRENT_NUMBER_OF_BYTES_USED as cbu,HIGH_NUMBER_OF_BYTES_USED as hbu from performance_schema.memory_summary_by_thread_by_event_name where SUM_NUMBER_OF_BYTES_ALLOC > 0 and THREAD_ID in (145) and CURRENT_NUMBER_OF_BYTES_USED>0 order by CURRENT_NUMBER_OF_BYTES_USED DESC;
+-----+-------------------------------+----------+------------+-------------+------------+------------+
| tid | EVENT_NAME                    |       ca |       snba | bf          | cbu        | hbu        |
+-----+-------------------------------+----------+------------+-------------+------------+------------+
| 145 | memory/sql/thd::main_mem_root |     1039 | 1104280592 |           0 | 1104280592 | 1104280592 |
| 145 | memory/innodb/std             | 19378997 | 2848628832 |  2838767096 |    9861736 |    9862208 |
| 145 | memory/sql/NET::buff          |        3 |     557077 |       16391 |     540686 |     557077 |
| 145 | memory/innodb/trx0undo        |    11424 |    3929856 |     3720360 |     209496 |     209496 |
| 145 | memory/mysys/array_buffer     |       11 |      25952 |        8320 |      17632 |      25504 |
| 145 | memory/vio/read_buffer        |        1 |      16384 |           0 |      16384 |      16384 |
| 145 | memory/sql/THD::transaction...|        1 |       4112 |           0 |       4112 |       4112 |
| 145 | memory/client/MYSQL           |        4 |       1366 |           0 |       1366 |       1366 |
| 145 | memory/client/mysql_options   |        9 |      545 |           0 |        545 |        545 |
| 145 | memory/vio/vio                |        1 |        496 |           0 |        496 |        496 |
| 145 | memory/sql/Log_event          |       11 |       1510 |        1122 |        388 |        772 |
| 145 | memory/innodb/mem0mem         |  2405744 | 1220964072 | 11220963816 |        256 |      21928 |
| 145 | memory/sql/String::value      |        4 |        232 |          16 |        216 |        216 |
| 145 | memory/sql/THD::Session_tra...|        6 |        209 |           0 |        209 |        209 |
| 145 | memory/sql/Gtid_set::Interv...|        1 |        200 |           0 |        200 |        200 |
| 145 | memory/sql/Mutex_cond_array...|        1 |        168 |           0 |        168 |        168 |
| 145 | memory/mysys/lf_slist         |        5 |        160 |           0 |        160 |        160 |
| 145 | memory/sql/THD::debug_sync_...|        1 |        120 |           0 |        120 |        120 |
| 145 | memory/sql/Owned_gtids::sid...|        1 |         96 |           0 |         96 |         96 |
| 145 | memory/mysys/lf_node          |        2 |         80 |           0 |         80 |         80 |
| 145 | memory/innodb/ha_innodb       |        1 |         72 |           0 |         72 |         72 |
| 145 | memory/sql/Sid_map::Node      |        1 |         20 |           0 |         20 |         20 |
| 145 | memory/sql/plugin_ref         |        2 |         16 |           0 |         16 |         16 |
| 145 | memory/sql/THD::Session_sys...|        1 |          8 |           0 |          8 |          8 |
+-----+-------------------------------+----------+------------+-------------+------------+------------+
24 rows in set (0.17 sec)


I watched this output in quite a time when slave was active, so I can be certain what memory/sql/thd::main_mem_root is the reason:

| 145 | memory/sql/thd::main_mem_root |      707 |    512337872 |         0 |   512337872 |  512337872 |
...
| 145 | memory/sql/thd::main_mem_root |      802 |    658752752 |         0 |   658752752 |  658752752 |
...
| 145 | memory/sql/thd::main_mem_root |     1039 |   1104280592 |         0 |  1104280592 | 1104280592 |


Unfortunately, in this case, this knowledge is hardly useful for users (but not for MySQL developers!), because this member of class THD (./sql/sql_class.h):

is used for two purposes:
    - for conventional queries, to allocate structures stored in main_lex
    during parsing, and allocate runtime data (execution plan, etc.)
    during execution.
    - for prepared queries, only to allocate runtime data. The parsed
    tree itself is reused between executions and thus is stored elsewhere.


Although we can be sure what reason of memory leak is not SQL thread, so can save our time and don't do unnecessary optimizations. In other situations we can be more lucky and observe information which could help to find workaround.

Join the discussion

Comments ( 1 )
  • guest Saturday, February 14, 2015

    Wow this is amazing. We need 5.7 to be production ready!


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.