Webinar: Introduction to MySQL SYS Schema follow up 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.