Wednesday Dec 17, 2014

Memory summary tables in Performance Schema in MySQL 5.7

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.

Wednesday Jan 30, 2013

Troubleshooting Performance Diagrams


Last year, when I was speaking about MySQL performance at Devconf in Moscow, I expected my audience will be very experienced as this always happen at all PHPClub conferences. So I had to choose: either make full-day seminar and explain people every basic of performance, or rely on their knowledge and make some one and half hours seminar. I prefer short speeches, so I considered latter.



But even with such a mature audience you don't always know if they knew some or another basic thing. Like somebody can be good analyzing EXPLAIN output and other is in reading InnoDB Monitor printout. Also, native language of the audience is not English and it would be always good to have short reference to simple things, described in their native language. In this case Russian. This is why I created these slides first time.



I was in doubt if I need to translate them to English, because information there repeats the official MySQL user reference manual in many aspects. Although diagrams still can be useful for English-speaking audience. So I did not translate those slides in first turn.



Time passed and in few days I am speaking about MySQL Troubleshooting at Fosdem. This is 30-minustes talk this time! And I just created slides and content for 8-hours Oracle University training! Can I zip this 8-hours training into 30-minutes talk? Of course, not. But I also want to give users as much in these 30 minutes as possible. So idea with add-ons came back.



You can download them in PDF format either from my own website or from slideshare.

About

Working blog of Sveta Smirnova - MySQL Senior Principal Support Engineer working in Bugs Analysis Support Group

Search

Categories
Archives
« March 2015
SunMonTueWedThuFriSat
1
2
3
4
5
6
8
9
10
11
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    
       
Today