PS_history is a tool which collects historical snapshots of the
PERFORMANCE_SCHEMA (P_S). This allows you to trend P_S values
over time, for example, it is possible to look at the 95 th
percentile response time for a query over time.
PS_history is stored procedure and event based, and thus it
resides entirely inside of the database with no external
dependencies. It uses a clever technique to capture all of the
P_S data in one consistent snapshot. This ensures that all of the
sys_history views (bundled now with PS_history) have a consistent
set of data.
By default, as long as the event_schedule is enabled, PS_history
will collect data every 30 seconds. If a snapshot takes 30
seconds, there will be a 30 second delay before the next snapshot
starts. This value can be changed by calling the
`ps_history`.`set_collection_interval`(N) where N is the number
of seconds between samples.
The `sys_history` schema is …
PS_history is a tool which collects historical snapshots of the
PERFORMANCE_SCHEMA (P_S). This allows you to trend P_S values
over time, for example, it is possible to look at the 95 th
percentile response time for a query over time.
PS_history is stored procedure and event based, and thus it
resides entirely inside of the database with no external
dependencies. It uses a clever technique to capture all of the
P_S data in one consistent snapshot. This ensures that all of the
sys_history views (bundled now with PS_history) have a consistent
set of data.
By default, as long as the event_schedule is enabled, PS_history
will collect data every 30 seconds. If a snapshot takes 30
seconds, there will be a 30 second delay before the next snapshot
starts. This value can be changed by calling the
`ps_history`.`set_collection_interval`(N) where N is the number
of seconds between samples.
The `sys_history` schema is …
Last week during the Oracle Users Group Leaders Summit in Bucharest I had the pleasure the meet the leaders for the MySQL Users Group from Azerbaijan, Finland, Madrid and the Netherlands.
During some discussions, it appeared that some users are not aware of the Performance_Schema integration in MySQL Workbench. Indeed with WB you can enable PFS, add sys table if not present by default (<5.7).
You can use a default configuration or customize it and enable all the instruments and consumers you need.
When enabled, you can generate some useful reports
In case you are also not yet aware of it, the following dashboard is also available in MySQL Workbench
The latest version of MySQL Workbench has been released yesterday, June 13th : 6.3.7 GA
A while back, I wrote a blog post explaining how
PERFORMANCE_SCHEMA
improvements in MySQL Server 5.7
provides new visibility into the SSL/TLS status of each
running client configuration. An excellent
recent post from Frederic Descamps at Percona covers
similar territory. Both of us use
PERFORMANCE_SCHEMA
tables directly – a powerful
interface, but one that requires a query joining multiple tables.
Thanks to the excellent work of Mark Leith, and a contribution from Daniël van Eeden, access to this
same information is made far easier via the SYS
schema.
I overlooked the SYS
…
When trying out new software there are many other questions you may ask and one of those is going to be the one above. The answer requires you to have built your software to capture and record low level database metrics and often the focus of application developers is slightly different: they focus on how … Continue reading Is MySQL X faster than MySQL Y? – Ask queryprofiler
The post Is MySQL X faster than MySQL Y? – Ask queryprofiler first appeared on Simon J Mudd's Blog.
In many types of database workloads, using a multi-threaded slave from 5.6+ helps improve replication performance. I’ve had a number of users enable this feature, but have not seen anyone ask how each thread is performing. Here’s a quick way with Performance_Schema to measure the amount of multi-threaded slave activity on each thread (after you have already configured MTS on your slave of course ).
First, we need to enable the
statements
instruments:
slave1> UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements_%'; Query OK, 2 rows affected (0.00 sec) Rows matched: 3 Changed: 2 Warnings: 0
Next, let’s find the
THREAD_ID
for our slave workers:
slave1> SELECT THREAD_ID, …[Read more]
MySQL Connector/Java supports connection attributes since version 5.1.25. This projects useful metadata about the client environment into the database, where MySQL administrators can query PERFORMANCE_SCHEMA tables to remotely survey application deployment environments. One useful piece of information exposed is the version and vendor of the JVM in use by the client. This very short blog demonstrates how to get this information from PERFORMANCE_SCHEMA.
The metadata including the Java runtime environment version and vendor can be found in PERFORMANCE_SCHEMA.SESSION_CONNECT_ATTRS table. Here’s the full contents of that table for a single connection from Connector/Java:
mysql> SELECT * -> FROM PERFORMANCE_SCHEMA.SESSION_CONNECT_ATTRS -> WHERE processlist_id = 31\G *************************** 1. row *************************** PROCESSLIST_ID: 31 ATTR_NAME: _runtime_version ATTR_VALUE: …[Read more]
Previous episodes:
MySQL replication in action - Part 1: GTID &
CoMySQL replication in action - Part 2 - Fan-in
topology
In the previous article, we saw the basics of
establishing replication from multiple origins to the same
destination. By extending that concept, we can deploy more
complex topologies, such as the point-to-point (P2P) all-masters
topology, a robust and …
MySQL 5.7 comes with many changes. Some of them are better
explained than others.
I wanted to see how many changes I could get by comparing SHOW
VARIABLES in MySQL 5.6 and 5.7.
The most notable ones are:
- binlog_format: the default is now ROW. This variable affects the format of the binary log, whether you use it as a backup complement or for replication, the change means bigger binary logs and possibly side effects.
- …
I thought it was worth a moment to reiterate on the new Performance Schema related defaults that MySQL 5.7.7 brings to the table, for various reasons.
For one, most of you might have noticed that profiling was marked as deprecated in MySQL 5.6.7. So it is expected that you invest into learning more about Performance Schema (and Mark’s sys schema!).
Second, there are lots of virtual environments and appliances out there running Community Edition MySQL where Performance Schema can be a useful tool for analyzing performance. Thus, expect to see more articles about using PERFORMANCE_SCHEMA and SYS_SCHEMA from us!
Third, we have more and more junior readers who might benefit from light reads such as this.
The new defaults that I wanted to highlight are mentioned in the …
[Read more]