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.

Thursday Dec 11, 2014

Performance Schema memory tables and rightless users

When I talk about troubleshooting I like to repeat: "Don't grant database access to everybody!" This can sound a bit weird having one can give very limited read-only access.

But only if ignore the fact what even minimal privileges in MySQL allows to change session variables, including those which control server resources. My favorite example is "Kill MySQL server with join_buffer_size". But before version 5.7 I could only recommend this, but not demonstrate. Now, with help of memory summary tables in Performance Schema, I can show how unprivileged user can let your server to use great amount of swap.

At first lets create a user account with minimal privileges and login.

$../client/mysql -ulj -h127.0.0.1 -P13001
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.6-m16-debug-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user(), current_user();
+--------------+----------------+
| user()       | current_user() |
+--------------+----------------+
| lj@127.0.0.1 | lj@%           |
+--------------+----------------+
1 row in set (0.00 sec)

mysql> show grants;
+--------------------------------+
| Grants for lj@%                |
+--------------------------------+
| GRANT USAGE ON *.* TO 'lj'@'%' |
+--------------------------------+
1 row in set (0.00 sec)


As you see user 'lj'@'%' has single privilege: USAGE. It even does not have read access! Although it still can create temporary tables.

mysql> create temporary table t1(f1 int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t2 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t3 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t4 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t5 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t6 select * from t1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t7 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t8 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t9 select * from t1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table t0 select * from t1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0


Nothing wrong with it: current connection uses only 235.34 KiB RAM.

mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in(152)\G
*************************** 1. row ***************************
         thread_id: 152
              user: lj@127.0.0.1
current_count_used: 303
 current_allocated: 235.34 KiB
 current_avg_alloc: 795 bytes
 current_max_alloc: 45.70 KiB
   total_allocated: 2.33 MiB
1 row in set (2.78 sec)


(Note here thread_id is not processlist_id, you should query P_S.threads table to find necessary thread_id)

And even if 'lj'@'%' will run quite complicated JOIN memory usage won't increase much if default options used.

mysql> select sleep(1) from t1,t2,t3,t4,t5,t6,t7,t8,t9,t0;
...


mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in(152)\G
*************************** 1. row ***************************
         thread_id: 152
              user: lj@127.0.0.1
current_count_used: 322
 current_allocated: 18.23 MiB
 current_avg_alloc: 57.97 KiB
 current_max_alloc: 18.00 MiB
   total_allocated: 20.38 MiB

1 row in set (2.62 sec)

However privilege USAGE allows 'lj'@'%' not only create temporary tables, but change session variables as it wants. For example, increase value of join_buffer_size up to maximum.

mysql> set join_buffer_size=4294967295;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select sleep(1) from t1,t2,t3,t4,t5,t6,t7,t8,t9,t0;
...


And with this value we will see completely different picture.

mysql> select * from sys.memory_by_thread_by_current_bytes where thread_id in(152)\G
*************************** 1. row ***************************
         thread_id: 152
              user: lj@127.0.0.1
current_count_used: 325
 current_allocated: 36.00 GiB
 current_avg_alloc: 113.43 MiB
 current_max_alloc: 36.00 GiB
   total_allocated: 37.95 GiB

1 row in set (2.70 sec)

36G is huge even in year 2014.

I am happy we finally have instrument which allows to both demonstrate and catch such issues.

Saturday Mar 01, 2014

Translated slides from my seminar about using Performance Schema for MySQL troubleshooting at Devconf 2013

Few weeks ago I asked my friends who speak both English and Russian if it is worth translating slides about Performance Schema which I prepared for a seminar at Devconf 2013. They said it is. Today I finished translation and uploaded slides to SlideShare.

Strictly speaking simple translation of slides is not enough, because they were created for the seminar where I was going to explain what they mean. I think I need to repeat same seminar, this time in English language. But if you have rough imagination about what Performance Schema is and need hints for practical use you will find such suggestions in the slides. You will also find ready-to-use queries which you can use to troubleshoot most frequent performance issues.

Enjoy!
About

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

Search

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