Performance Schema is a mechanism to collect and report run time statistics for running MySQL server. These statistics are stored-in and fetched-from internal memory buffers. In MySQL 5.6 GA, memory for these buffers is allocated during MySQL server startup with either user specified configuration values or with default values that autosize. Once the server has started, the size of the buffers is fixed and performance_schema does not do any additional memory allocation or freeing during execution.
Using fixed memory allocation has a few limitations. To outline two specific situations that could occur whether a user specifies configuration values for performance_schema or uses the default values:
- [Concern 1] significant amount of allocated buffer is left unused if less instances of instrument are encountered
- [Concern 2] amount allocated is not sufficient and performance_schema starts loosing further statistics if more instances of instrument are encountered.
This then asks the question – is there a way, Performance Schema itself decides what should be the optimal size of different buffers and change it according to the server load? Is it at all possible?
Well, yes !!! In MySQL 5.7 GA Performance Schema has been provided with this intelligence.
Changes in MySQL 5.7
In MySQL 5.7 memory allocation for Performance Schema buffers doesn’t happen at server start-up but is instead based on the actual runtime requirement.
For the server variables (which control buffer size), you can now specify:
| Value | Description |
| 0 | to tell P_S not to collect stats thus no allocation for this buffer. |
| N | to tell P_S to collect stats for maximum N instance only. Memory allocation happens as and when need arises. And this allocation continues until space for max (N here) instances is allocated. |
| -1 | to tell P_S take your own decision for maximum limit. As above, memory is allocated as and when need arises. This allocation continues until space for max (decided by P_S here) instances is allocated. |
Lets try to understand this behavior with an example:
[Concern 1] Significant amount of allocated buffer is left unused if less instances of instrument are encountered
Start MySQL server with intention to collect stats for maximum 2000 prepared statements:
|
1 |
mysqld --performance_schema_max_prepared_statements_instances=2000 |
Start a client and lets see the memory allocated for the buffer to store prepared statements statistics:
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql > show engine performance_schema status; +--------------------+--------------------------------------+----------+ | Type | Name | Status | +--------------------+--------------------------------------+----------+ ... | performance_schema | prepared_statements_instances.size | 1664 | | performance_schema | prepared_statements_instances.count | 0 | | performance_schema | prepared_statements_instances.memory | 0 | ... +--------------------+--------------------------------------+----------+ 229 rows in set (0.00 sec) |
So till now NO memory is allocated for this buffer as there is no prepared statement on server yet.
Now lets prepare a statement:
|
1
2
3
|
mysql > prepare stmt1 from 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; Query OK, 0 rows affected (0.00 sec) Statement prepared |
We can see the size of buffer allocated:
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql > show engine performance_schema status; +--------------------+--------------------------------------+----------+ | Type | Name | Status | +--------------------+--------------------------------------+----------+ ... | performance_schema | prepared_statements_instances.size | 1664 | | performance_schema | prepared_statements_instances.count | 1024 | | performance_schema | prepared_statements_instances.memory | 1703936 | ... +--------------------+--------------------------------------+----------+ 229 rows in set (0.01 sec) |
As you can see, the numbers have changed, and memory was allocated as it was required.
Question: There is only 1 prepared statement on server and memory required for 1 prepared statement stat is 1664. Then why did P_S allocate 1703936?
Answer: P_S does not allocate memory for each any every instance but it allocates memory in chunk, (1703936 for 1024 prepared statements). Then this allocated chunk is used for further instances, until it could not accommodate any more. Then a new chunk (of 1703936 here) is allocated again provided it doesn’t exceed the maximum (N) specified by user (or decided by P_S in case -1 is given).
If we compare memory allocation of P_S buffer, before & after this feature, here is what it looks like :
|
1 |
mysql > show engine performance_schema status; |
[Concern 2] Amount allocated is not sufficient and it start loosing further statistics if more instances of instrument are encountered.
To resolve this, where one can’t anticipate the huge volume of incoming instruments, now (s)he can set value of variable to ‘-1‘. In this case, it would keep on allocating memory with the runtime requirements.
So for example, if one (wrongly) anticipates 2000 prepared statements to occur but at runtime 3000 prepared statements seen, then
BEFORE without auto-scaling:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql > show variables like 'performance_schema_max_prepared_statements_instances'; +------------------------------------------------------+-------+ | Variable_name | Value | +------------------------------------------------------+-------+ | performance_schema_max_prepared_statements_instances | 2000 | +------------------------------------------------------+-------+ 1 row in set (0.00 sec) mysql> show status like "%prepared_statements_lost%"; +-----------------------------------------------+-------+ | Variable_name | Value | +-----------------------------------------------+-------+ ... | Performance_schema_prepared_statements_lost | 1000 | ... +-----------------------------------------------+-------+ |
As it can be seen, statistics for 1000 instances are lost.
Now with auto-scaling:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql > show variables like 'performance_schema_max_prepared_statements_instances'; +------------------------------------------------------+-------+ | Variable_name | Value | +------------------------------------------------------+-------+ | performance_schema_max_prepared_statements_instances | -1 | +------------------------------------------------------+-------+ 1 row in set (0.00 sec) mysql> show status like "%prepared_statements_lost%"; +-----------------------------------------------+-------+ | Variable_name | Value | +-----------------------------------------------+-------+ ... | Performance_schema_prepared_statements_lost | 0 | ... +-----------------------------------------------+-------+ |
Statistics for all instances are collected and nothing lost.
One more important thing to mention here is that memory allocated by P_S for buffers at runtime, is NEVER deallocated.
For server variables which controls P_S buffers’ size and which could be auto-scaled (i.e. could be set with value ‘-1’), default value is already set to “-1”. Here is the list of these server variables:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
mysql> show variables where variable_name like "performance_schema%" and variable_value=-1; +------------------------------------------------------+-------+ | Variable_name | Value | +------------------------------------------------------+-------+ | performance_schema_accounts_size | -1 | | performance_schema_hosts_size | -1 | | performance_schema_max_cond_instances | -1 | | performance_schema_max_file_instances | -1 | | performance_schema_max_index_stat | -1 | | performance_schema_max_metadata_locks | -1 | | performance_schema_max_mutex_instances | -1 | | performance_schema_max_prepared_statements_instances | -1 | | performance_schema_max_program_instances | -1 | | performance_schema_max_rwlock_instances | -1 | | performance_schema_max_socket_instances | -1 | | performance_schema_max_table_handles | -1 | | performance_schema_max_table_instances | -1 | | performance_schema_max_table_lock_stat | -1 | | performance_schema_max_thread_instances | -1 | | performance_schema_setup_actors_size | -1 | | performance_schema_setup_objects_size | -1 | | performance_schema_users_size | -1 | +------------------------------------------------------+-------+ 18 rows in set, 1 warning (0.01 sec) |
Auto-sized vs. Auto-scaled performance schema variables.
Above (auto-scaled) variable should not be confused with auto-sized variables. Even for auto-sized variables, values could be set as ‘-1’ during server startup. But there is a difference between them.
-
auto-sized:
Based on estimated server load/configuration, value of this variable would be determined by performance schema during startup.
-
auto-scaled:
Based on actual runtime requirement, memory is allocated for buffers controlled by these variables.