Performance Schema has been with us for a while now. Over the years, it has gone a long way from being a curiosity disabled by default to becoming a sophisticated diagnostic tool you may want to enable permanently in your production database.
MySQL 5.7 introduced some exciting Performance Schema features and the first one I'm going to look at is the instrumentation for server memory usage.
Have I Got Data For You
MySQL 5.7.9 (GA) supports the following memory summary tables (per documentation) that let you look at server memory usage from different angles:
- "memory_summary_by_account_by_event_name" summarizes events for a given account.
- "memory_summary_by_host_by_event_name" summarizes events for a given host.
- "memory_summary_by_thread_by_event_name" summarizes events for a given thread and event name.
- "memory_summary_by_user_by_event_name" summarizes events for a given user.
- "memory_summary_global_by_event_name" summarizes events for a given event name.
As with other summary tables, they all instrument the same family of events but the data is summarized/grouped by different dimensions e.g. accounts or hosts. I will use the "memory_summary_global_by_event_name" table for this article.
Poking around
Before trying to obtain any data from summary table, let's see what events are instrumented:
mysql> select count(*) from performance_schema.setup_instruments where name like 'memory/%'; +----------+ | count(*) | +----------+ | 375 | +----------+ 1 row in set (0.00 sec)
That's impressive! We can diagnose nearly 400 different memory-chewing structures. I encourage you to skip through the complete list but here are the main classes of information you can expect to see there:
- Events specific to storage engines such as "memory/innodb", "memory/myisam", "memory/memory".
- "memory/sql" exposing numerous events related to the SQL layer.
- "memory/client" with several events related to client-server communication.
- "memory/performance_schema" for profiling memory usage within the Performance Schema itself.
mysql> update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory/%'; Query OK, 305 rows affected (0.00 sec) Rows matched: 375 Changed: 305 Warnings: 0
Note that memory instruments can only be enabled or disabled but they cannot be timed. I kept all memory instruments enabled during all tests mentioned in this article.
Example 1: Explicit memory tables
First we're going to look at the obvious thing, which is the memory usage of user-created memory tables. SQL commands/outputs are pasted and explained below.
Let's go through the output line by line:
mysql> truncate table performance_schema.memory_summary_global_by_event_name;
Query OK, 0 rows affected (0.00 sec)
mysql> create table memory1 (c1 char(255)) engine = memory;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into memory1 select '' from performance_schema.setup_instruments i;
Query OK, 1006 rows affected (0.00 sec)
Records: 1006 Duplicates: 0 Warnings:
mysql> select * from performance_schema.memory_summary_global_by_event_name where event_name = 'memory/memory/hp_ptrs'\G
*************************** 1. row ***************************
EVENT_NAME: memory/memory/HP_PTRS
COUNT_ALLOC: 3
COUNT_FREE: 0
SUM_NUMBER_OF_BYTES_ALLOC: 383000
SUM_NUMBER_OF_BYTES_FREE: 0
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 3
HIGH_COUNT_USED: 3
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 383000
HIGH_NUMBER_OF_BYTES_USED: 383000
1 row in set (0.00 sec)
mysql> select data_length from information_schema.tables where table_name = 'memory1';
+-------------+
| data_length |
+-------------+
| 383000 |
+-------------+
1 row in set (0.00 sec)
- Line 1: Truncate the summary table to ensure that the data I'll see later is exclusively related to this individual test.
- Lines 4, 7: Create a dummy table with CHAR(255) column, inserting some rows into it.
- Line 11: Immediately after populating the table, pull performance counters from the summary table. I'm looking for the event named "memory/memory/hp_ptrs" because it's the one representing memory allocation for heap tables. The counter we're interested in is "CURRENT_NUMBER_OF_BYTES_USED", which shows that currently our memory tables are using just over 374 KiB of memory. You may refer to the documentation for more detailed information about the other columns visible in the output.
- Line 26: Check the table size per information_schema. The size reported is the same as the figure observed earlier in performance schema.
Example 2: Internal memory tables
Let's now play with data that may actually be useful in real life. In this example I'm going run two SQL queries that require an internal temporary table to be processed. I will then use performance schema to learn exactly how much memory MySQL had to allocate in order to process each query.
mysql> set global tmp_table_size = 268435456;
Query OK, 0 rows affected (0.00 sec)
mysql> set global max_heap_table_size = 268435456;
Query OK, 0 rows affected (0.00 sec)
mysql> create table table1 (id int primary key auto_increment, s1 varchar(32), s2 varchar(32));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into pstest.table1 values (null, md5(rand()), md5(rand()));
Query OK, 1 row affected (0.00 sec)
mysql> insert into pstest.table1 select null, md5(rand()), md5(rand()) from pstest.table1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> (INSERTs repeated until there's 262144 rows in the table)
mysql> truncate table performance_schema.memory_summary_global_by_event_name;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(id) from pstest.table1 group by s1, s2 limit 1;
+-----------+
| count(id) |
+-----------+
| 1 |
+-----------+
1 row in set (0.43 sec)
mysql> select * from performance_schema.memory_summary_global_by_event_name where event_name = 'memory/memory/hp_ptrs'\G
*************************** 1. row ***************************
EVENT_NAME: memory/memory/HP_PTRS
COUNT_ALLOC: 216
COUNT_FREE: 216
SUM_NUMBER_OF_BYTES_ALLOC: 27658000
SUM_NUMBER_OF_BYTES_FREE: 27658000
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 0
HIGH_COUNT_USED: 216
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
HIGH_NUMBER_OF_BYTES_USED: 27658000
1 row in set (0.00 sec)
mysql> truncate table performance_schema.memory_summary_global_by_event_name;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(id) from pstest.table1 group by s1 limit 1;
+-----------+
| count(id) |
+-----------+
| 1 |
+-----------+
1 row in set (0.29 sec)
mysql> select * from performance_schema.memory_summary_global_by_event_name where event_name = 'memory/memory/hp_ptrs'\G
*************************** 1. row ***************************
EVENT_NAME: memory/memory/HP_PTRS
COUNT_ALLOC: 147
COUNT_FREE: 147
SUM_NUMBER_OF_BYTES_ALLOC: 18817480
SUM_NUMBER_OF_BYTES_FREE: 18817480
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 0
HIGH_COUNT_USED: 147
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
HIGH_NUMBER_OF_BYTES_USED: 18817480
1 row in set (0.00 sec)
Let's go line by line again:
- Lines 1 and 4: Increase the default MySQL memory table limits so that our test fits in memory. The default temporary/heap table size limit is 16MB, this test needs a bit more. I could have used a smaller table here but I'm deliberately not doing it. This gives me a chance to highlight that you *may* need to tweak some configuration in order to get meaningful results.
- Lines 7 through 17: Create a table and populate it with data.
- Lines 19 and 45: Truncate the summary table before each test run.
- Line 22: Run a query with a compound grouping condition that requires a temporary table for processing.
- Line 30: Pull data from the summary table. There is no current memory utilization because the table was only temporary, but we can still see the sum/peak memory usage. According to the figures, MySQL needed to allocate just over 26MB of temporary memory to process the query.
- Line 48: Run the query again, this time with a simplified grouping condition.
- Line 56: Pull diagnostic data again. This time our query needed around 19MB of temporary memory.
Example 3: Heavy partitioning/InnoDB heap
In this scenario I'm using performance schema to measure memory utilization immediately after running a test case taken from my previous blog post (MySQL 5.7.9, 16 threads, table_open_cache=2K).
According to OS tools (/proc/{pid}/stat) MySQL memory usage peaked at ~3800MiB during the test and now I'm curious to see what performance schema has to say about it.
mysql> select sum(high_number_of_bytes_used) from performance_schema.memory_summary_global_by_event_name; +--------------------------------+ | sum(high_number_of_bytes_used) | +--------------------------------+ | 2157662007 | +--------------------------------+ 1 row in set (0.01 sec)
Performance schema says the memory usage peaked at around 2GiB. It's understandable that not all memory areas are instrumented but something doesn't look quite right here. Also, I'd rather expect this figure to be bigger than the actual peak memory utilization due to the fact that different areas might have reached their peak size at different times (and here we're summing everything up). After a little more digging I found something I shouldn't have found according to documentation, namely, negative counters in the global summary table:
mysql> select event_name, low_number_of_bytes_used, high_number_of_bytes_used from performance_schema.memory_summary_global_by_event_name order by high_number_of_bytes_used desc limit 10; +-----------------------------------------------------------------+--------------------------+---------------------------+ | event_name | low_number_of_bytes_used | high_number_of_bytes_used | +-----------------------------------------------------------------+--------------------------+---------------------------+ | memory/innodb/mem0mem | -1399519242 | 1616301196 | | memory/performance_schema/table_handles | 0 | 150994944 | | memory/innodb/os0event | -68413440 | 78607728 | | memory/sql/TABLE | -2452 | 69406196 | | memory/performance_schema/file_instances | 0 | 46137344 | | memory/innodb/dict0dict | -21728256 | 24303616 | | memory/innodb/lock0lock | 0 | 14885584 | | memory/performance_schema/events_statements_history_long | 0 | 14320000 | | memory/innodb/partitioning | 0 | 11097216 | | memory/performance_schema/events_statements_history_long.tokens | 0 | 10240000 | +-----------------------------------------------------------------+--------------------------+---------------------------+ 10 rows in set (0.00 sec)Unless it's too late and I can no longer read documentation correctly, this looks like a potential bug (already verified in #79285). Interestingly, memory utilization looks more "okay" if you count the negative values in:
mysql> select sum(high_number_of_bytes_used) + sum(if(low_number_of_bytes_used>0, 0, low_number_of_bytes_used * -1)) from performance_schema.memory_summary_global_by_event_name; +--------------------------------------------------------------------------------------------------------+ | sum(high_number_of_bytes_used) + sum(if(low_number_of_bytes_used>0, 0, low_number_of_bytes_used * -1)) | +--------------------------------------------------------------------------------------------------------+ | 3653197581 | +--------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)However, this might be a mere coincidence so I'll need to dig into this later.
No comments:
Post a Comment