Showing entries 1 to 10 of 66
10 Older Entries »
Displaying posts with tag: performance schema (reset)
Extending replication instrumentation: account for memory used in XCom

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).…

Extending replication instrumentation: an insight on transaction retries

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.…

Can MySQL Parallel Replication Help My Slave?

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

slave_parallel_workers

  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]
Instrumenting Read Only Transactions in InnoDB

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]
MySQL 8: Performance Schema Digests Improvements

Tweet

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.

[Read more]
Replication Monitoring with the Performance Schema

Tweet

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 Setup

The replication setup that will be used for the examples in this blog can be seen in the following figure.

[Read more]
What Does I/O Latencies and Bytes Mean in the Performance and sys Schemas?

Tweet

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

[Read more]
Webinar 6/27: MySQL Troubleshooting Best Practices: Monitoring the Production Database Without Killing Performance

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).

Register Now

 

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 …
[Read more]
Reading the log positions of a snapshot consistently

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.…

Performance Schema for MySQL Troubleshooting Webinar: Q & A

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]
Showing entries 1 to 10 of 66
10 Older Entries »