The new release of MySQL is packed with exciting features that help detecting and analyzing replication lag. In this post, you will be able to learn all about the new replication timestamps, the new useful information that is now reported by performance schema tables, and how delayed replication was improved.…
10 Older Entries »
In this blog post, we’ll look at Performance Schema benchmarks for OLTP Read/Write workloads.
I am in love with Performance Schema and talk a lot about it. Performance Schema is a revolutionary MySQL troubleshooting instrument, but earlier versions had performance issues. Many of these issues are fixed now, and the default options work quickly and …[Read more]
Why do we sometimes want to keep duplicate indexes?
I’ve done dutiful DBA work in the past to identify and remove what are commonly called duplicate indexes. That is, those indexes that look like (a) and (a,b). The thought is that a query will utilize an index as easily on (a) as on (a,b), and removing (a) will save storage cost and write performance. I’ve had the experience, though, of removing (a) and seeing performance tank.
(As an aside, these are really redundant indexes. A duplicate index would be (a,b) and (a,b) by two different names – this can commonly be done by object relational mapping (ORM) or other automated schema creation tools. I’ll call (a) and (a,b) redundant indexes below.)
This test is on Percona Server 5.7.14 with the sys schema installed and performance schema enabled.
Given two tables with the same number of rows and …[Read more]
In a previous article, Ovais demonstrated how a DDL can render a table blocked from new queries. In another article, Valerii introduced performance_schema.metadata_locks, which is available in MySQL 5.7 and exposes metadata lock details. Given this information, here’s a quick way to troubleshoot metadata locks by creating a stored procedure that can:
- Find out which thread(s) have the metadata lock
- Determine which thread has been waiting for it the longest
- Find other threads waiting for the metadata lock
Setting up …[Read more]
During an upgrade to Percona XtraDB Cluster 5.6, I faced an issue that I wanted to share. In this environment, we set up three Percona XtraDB Cluster nodes (mostly configured as default), copied from a production server. We configured one of the members of the cluster as the slave of the production server.
During the testing process, we found that a full table scan query was taking four times less in the nodes where replication was not configured. After reviewing mostly everything related to the query, we decided to use perf.
perf record -a -g -F99 -p $(pidof mysqld) -- sleep 60
And the query in another terminal a couple of times. …[Read more]
In MySQL 8.0.0, the Performance Schema can now instrument server errors. There are 5 new summary tables introduced:
mysql> show tables like "%by_error%"; +-------------------------------------------+ | Tables_in_performance_schema (%by_error%) | +-------------------------------------------+ | events_errors_summary_by_account_by_error | | events_errors_summary_by_host_by_error | | events_errors_summary_by_thread_by_error | | events_errors_summary_by_user_by_error | | events_errors_summary_global_by_error | +-------------------------------------------+ 5 rows in set (0.02 sec)
As names suggest, statistics of errors in these tables are aggregated by error.…
In MySQL 8.0 we are improving the Performance Schema to support indexes. This increases the flexibility and ease of use of using performance_schema, as a number of monitoring queries will see a dramatic speed up in performance.
Since it’s initial launch in MySQL 5.5, the Performance Schema has grown to 93 tables, with some of these exposing a lot of data.…
Starting with MySQL 5.7, performance_schema has been enhanced to instrument metadata locks, and can be turned on by adding the following line to your my.cnf file:
(At runtime, it can also be enabled by modifying the setup_instruments table in performance_schema.)
From here, you can now query performance_schema.metadata_locks to reveal all currently open metadata locks on your server:
mysqlselect * from performance_schema.metadata_locks\G *************************** 1.
Performance Schema is a mechanism to collect and report run time statistics for running MySQL server. These statistics are stored-in and fetched-from internal memory buffers. In MySQL 5.6 GA, memory for these buffers is allocated during MySQL server startup with either user specified configuration values or with default values that autosize.…
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
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
for our slave workers:
slave1> SELECT …[Read more]
10 Older Entries »