Version 8.0.12 added many great new features to MySQL. One of the new features included is the memory instrumentation of the XCom cache, which allows users to view and monitor the memory utilization of the cache by querying Performance Schema (PS).…
10 Older Entries »
MySQL 8.0.13 improves replication lag monitoring by extending the instrumentation for transaction transient errors. These temporary errors, which include lock timeouts caused by client transactions executing concurrently as the slave is replicating, do not stop the applier thread: instead, they cause a transaction to retry.…
Parallel replication has been around for a few years now but is still not that commonly used. I had a customer where the master had a very large write workload. The slave could not keep up so I recommended to use parallel slave threads. But how can I measure if it really helps and is working?
At my customer the
was 0. But how big should I increase it, maybe to 1? Maybe to 10? There is a blog post about how can we see how many threads are actually used, which is a great help.
We changed the following variables on the slave:
slave_parallel_type = LOGICAL_CLOCK; slave_parallel_workers = 40; slave_preserve_commit_order = ON;
40 threads sounds a lot, right? Of course, this is workload specific: if the transactions are independent it might …[Read more]
Probably not well known but quite an important optimization was introduced in MySQL 5.6 – reduced overhead for “read only transactions”. While usually by a “transaction” we mean a query or a group of queries that change data, with transaction engines like InnoDB, every data read or write operation is a transaction.
Now, as a non-locking read operation obviously has less impact on the data, it does not need all the instrumenting overhead a write transaction has. The main thing that can be avoided, as described by documentation, is the transaction ID. So, since MySQL 5.6, a read only transaction does not have a transaction ID. Moreover, such a transaction is not visible in the SHOW ENGINE INNODB STATUS output, though I will not go deeper on what really that means under the hood in this article. The fact is that this optimization …[Read more]
Since MySQL 5.6, the digest feature of the MySQL Performance Schema has provided a convenient and effective way to obtain statistics of queries based on their normalized form. The feature works so well that it has almost completely (from my experience) replaced the connector extensions and proxy for collecting query statistics for the Query Analyzer (Quan) in MySQL Enterprise Monitor (MEM).
MySQL 8 adds further improvements to the digest feature in the Performance Schema including a sample query with statistics for each digest, percentile information, and a histogram summary. This blog will explore these new features.
The traditional way to monitor replication in MySQL is the
SHOW SLAVE STATUS command. However as it will be
shown, it has its limitations and in MySQL 5.7 and 8.0 the MySQL
developers have started to implement the information as
Performance Schema tables. This has several advantages including
better monitoring of the replication delay in MySQL 8.0. This
blog discusses why
SHOW SLAVE STATUS should be
replaced with the Performance Schema tables.
The replication setup that will be used for the examples in this blog can be seen in the following figure.
The Performance Schema and
sys schema are great for
investigating what is going on in MySQL including investigating
performance issues. In my work in MySQL Support, I have a several
times heard questions whether a peak in the InnoDB Data File
I/O – Latency graph in MySQL Enterprise Monitor (MEM) or
some values from the corresponding tables and view in the
Performance Schema and
sys schema are cause for
concern. This blog will discuss what these observations means and
how to use them.
The Tables and Views Involved
Please join Percona’s Principal Support Escalation Specialist Sveta Smirnova as she presents Troubleshooting Best Practices: Monitoring the Production Database Without Killing Performance on Wednesday, June 27th at 11:00 AM PDT (UTC-7) / 2:00 PM EDT (UTC-4).
During the MySQL Troubleshooting webinar series, I covered many monitoring and logging tools such as:
- General, slow, audit, binary, error log files
- Performance Schema
- Information Schema
- System …
MySQL 8.0.11 introduced a new performance schema table named log_status, which provides consistent information about MySQL server instance log positions from replication and transactional storage engines.
Used in conjunction with other MySQL 8.0 feature (the backup lock), this new feature will allow backup tools to take backup with minimal impact in DML throughput, but ensuring consistent snapshot with respect to GTIDs, replication, binary logs and transactional storage engine logs.…
In this blog, I will provide answers to the Q & A for the Performance Schema for MySQL Troubleshooting webinar.
First, I want to thank everybody for attending my March 1, 2018, webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I was unable to answer fully during the webinar.
Q: Is Workbench able to take advantage of the enhancements to Perf schema?
A: MySQL Workbench is a graphical tool for database …[Read more]
10 Older Entries »