mysql sys schema 案例两则
#######sample 1
https://www.percona.com/blog/2015/09/16/webinar-introduction-mysql-sys-schema-follow-questions/
Thanks to all who attended my webinar Introduction to MySQL SYS Schema. This blog is for me to address the extra questions I didn’t have time to answer on the stream.
Can i have the performance_schema enabled in 5.6 and then install the sys schema? Or they are one and the same?
You need to have enabled the performance_schema in order to use it through the sys schema. They are different entities. In general, performance_schema collects and stores the data, and sys schema reads and presents the data.
The installation of sys schema on primary database will be replicated to the slaves?
By default, no. If you wish that the Sys Schema replicates to the slaves, you can modify the before_setup.sql (https://github.com/MarkLeith/mysql-sys/blob/master/before_setup.sql#L18) to skip the “SET sql_log_bin = 0;”
Can MySQL save the slow running query in any table?
Yes it does: https://dev.mysql.com/doc/refman/5.6/en/log-destinations.html
How to see the query execution date & time from events_statements_current/history views in performance_schema?
You can check the performance_schema.events_statements_summary_by_digest table, that have the fields FIRST_SEEN and LAST_SEEN which are both a datetime values.
When the Sys Schema views show certain stats for the queries, is there a execution time range for queries under evaluation or is it like all the queries executed until date?
It’s all the queries executed until date, except when using some of the stored procedures that receive as a parameter a run time value, like “diagnostics” or “ps_trace_statement_digest”
I want to write the automated script to rebuild table or index. How to determine which table(s) or index(es) need to be rebuilt because of high fragmentation ratio?
For this you need to use something completely different. To know the fragmentation inside an InnoDB table i’ll recommend you to use XtraBackup with the –stats parameter https://www.percona.com/doc/percona-xtrabackup/2.2/xtrabackup_bin/analyzing_table_statistics.html
Downside to using? Overhead?
The overhead is the one that comes with using Performance Schema. I like the perspective of this presentation (https://www.percona.com/live/mysql-conference-2014/sessions/performanceschema-worth-overhead): Overhead is dynamic. Do not rely on other people’s benchmarks. Benchmark your application and find out what your overhead is.
What is the performance cost with regards to memory and io when using sys schema? Are there any tweaks or server variables with help the sys schema performing better?
Use only the instrumentation needed. This blog post have extensive info about the topic http://marcalff.blogspot.com.co/2011/06/performance-schema-overhead-tuning.html
For replicate how does sys schema record data?
It doesn’t until MySQL 5.7.2 where the Performance Schema provides tables that expose replication information: https://dev.mysql.com/doc/refman/5.7/en/performance-schema-replication-tables.html and talking about the sys schema, currently the only place where you can find info about replication is in the “diagnostics” procedure, but as you can imagine, it only get’s data if the MySQL version is 5.7.2 or higher.
Is sys schema built into any o the Percona releases?
At the moment, no.
Is it possible to use SYS schema in Galera 3 nodes cluster?
Yes, since the only requirement is to have performance_schema, which is also available on PXC / Galera Cluster
Can you create trending off information pulled from the Sys Schema? Full table scans over time, latency over time, that kind of thing?
Yes, you can use procedures like, for example, “diagnostics”
How do I reset the performance data to start collecting from scratch?
By calling the ps_truncate_all_tables procedure. Truncate a performance_schema table equals to “clear collected events”. TRUNCATE TABLE can also be used with summary tables, but the effect is to reset the summary columns to 0 or NULL, not to remove rows.
Can we install SYS schema before 5.6?
You can use the ps_helper on 5.5 https://github.com/MarkLeith/dbahelper
Does sys support performance_schema from 5.0?
Unfortunately, MySQL 5.0 doesn’t have performance_schema. P_S is available since MySQL 5.5.3
If you install the sys schema on one node of a Galera cluster will all the nodes get the Sys schema? Also, is the Sys schema cluster aware or does it only track the local node?
For PXC 5.6 with Galera 3, the answer is: yes, it will be replicated to all the nodes. And the performance schema will always only collect data of the local node.
##sample 2
Sys Schema for MySQL 5.6 and MySQL 5.7
https://www.percona.com/blog/2014/11/20/sys-schema-mysql-5-6-5-7/
Performance Schema (P_S) has been available since MySQL 5.5, more than 4 years ago. It is still difficult to see production servers with P_S enabled, even with MySQL 5.6 where the feature is enabled by default. There have been several complaints like the overhead, that the fix is a work in progress, and the ease of use. 52 tables to query and 31 configuration variables is enough to scare people.
There is a solution for the second problem, the usability. It’s name is “sys schema“. It is a collection of views, functions and procedures to make P_S more user friendly.
Installation
If you are a MySQL Workbench user the installation is pretty easy because sys schema is already included. You just need to install it. Click on “Performance – Performance Reports” and there you will find the “Install Helper” button that will install sys schema.
If you don’t use MySQL Workbench you need to download sys_56.sql or sys_57.sql (depends if you use 5.6 or 5.7) from the github repository. Then, just import the sql file as usual:
|
1
|
mysql -u root -p < ./sys_56.sql
|
Usage
After the import, you will have a new “sys” schema with some very descriptive table names. Let’s see an example. Do you want to know what tables are using most of our InnoDB buffer memory? Easy:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> select * from sys.innodb_buffer_stats_by_table;
+---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+
| object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+
| test | t | 63.61 MiB | 58.06 MiB | 4071 | 4071 | 4071 | 2101222 |
| InnoDB System | SYS_FOREIGN | 32.00 KiB | 0 bytes | 2 | 2 | 2 | 0 |
| InnoDB System | SYS_TABLES | 32.00 KiB | 1.11 KiB | 2 | 2 | 2 | 10 |
| InnoDB System | SYS_COLUMNS | 16.00 KiB | 4.68 KiB | 1 | 1 | 1 | 71 |
| InnoDB System | SYS_DATAFILES | 16.00 KiB | 324 bytes | 1 | 1 | 1 | 6 |
| InnoDB System | SYS_FIELDS | 16.00 KiB | 722 bytes | 1 | 1 | 1 | 17 |
| InnoDB System | SYS_INDEXES | 16.00 KiB | 836 bytes | 1 | 1 | 1 | 12 |
| InnoDB System | SYS_TABLESPACES | 16.00 KiB | 318 bytes | 1 | 1 | 1 | 6 |
| mysql | innodb_index_stats | 16.00 KiB | 274 bytes | 1 | 1 | 1 | 3 |
| mysql | innodb_table_stats | 16.00 KiB | 53 bytes | 1 | 1 | 1 | 1 |
+---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+
|
Pretty easy and useful, right? You can also get what is the database using more memory in the buffer pool querying innodb_buffer_stats_by_schema.
For each table there is another similar ones that it’s name starts with x$. For example, you have user_summary_by_file_io and x$user_summary_by_file_io. The difference is that the first table has a human readable output values while the second one has the real values. Let’s see an example:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> select * from sys.user_summary_by_file_io;
+------------+-------+------------+
| user | ios | io_latency |
+------------+-------+------------+
| root | 19514 | 2.87 s |
| background | 5916 | 1.91 s |
+------------+-------+------------+
2 rows in set (0.00 sec)
mysql> select * from sys.x$user_summary_by_file_io;
+------------+-------+---------------+
| user | ios | io_latency |
+------------+-------+---------------+
| root | 19514 | 2871847094292 |
| background | 5916 | 1905079715132 |
+------------+-------+---------------+
|
For humans, at least for me, it is easier to read seconds rather than picoseconds
There are multiple tables with very descriptive names.
– io_by_thread_by_latency
– schema_unused_indexes
– statements_with_temp_tables
– statements_with_errors_or_warnings
– user_summary_by_statement_type
– waits_by_host_by_latency
…
There are lot more, and they are explained with examples in project’s README file.
Configuration
On the MySQL side nothing special is needed. Just enable performance_schema:
|
1
|
performance_schema="on"
|
sys schema also provides some procedures to enable/disable some features. For example:
– ps_setup_enable_background_threads
– ps_setup_enable_consumers
– ps_setup_enable_instrument
and so on…
We also have the same procedures with “disable”. After you have made the changes you can save them calling ps_setup_save() and reload it later on if you want calling ps_setup_reload_saved(). If you want to reset the configuration to default values just call ps_setup_reset_to_default().
For example, we can check that some consumers are disabled. It is possible to enable them and save the configuration:
|
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
|
mysql> CALL sys.ps_setup_show_disabled_consumers();
+--------------------------------+
| disabled_consumers |
+--------------------------------+
| events_stages_current |
| events_stages_history |
| events_stages_history_long |
| events_statements_history |
| events_statements_history_long |
| events_waits_current |
| events_waits_history |
| events_waits_history_long |
+--------------------------------+
mysql> CALL sys.ps_setup_enable_consumers('events');
+---------------------+
| summary |
+---------------------+
| Enabled 8 consumers |
+---------------------+
mysql> CALL sys.ps_setup_show_disabled_consumers();
Empty set (0.00 sec)
mysql> CALL sys.ps_setup_save(TRUE);
|
Conclusion
Performance Schema is very useful when we want to know what is happening inside the database. It is getting more features with each new GA and will probably be the single point of information in near future. Now thanks to sys schema it is also easy to use.
