In MySQL 8.0.2, users will see the additional columns in the existing Group Replication Performance Schema tables which will provide extended information about Group Replication. Now user can view role and MySQL version of each member of the group, which earlier required a complex set of query.…
10 Older Entries »
We have mentioned ClickHouse in some recent posts (ClickHouse: New Open Source Columnar Database, Column Store Database Benchmarks: MariaDB ColumnStore vs. Clickhouse vs. Apache Spark), where it showed excellent results. ClickHouse by itself seems to be event-oriented RDBMS, as its name suggests (clicks). Its primary purpose, using Yandex Metrica (the system similar to Google Analytics), also points to an event-based nature. We also can see there is …[Read more]
This post was originally published on the MySQL Support Team Blog at https://blogs.oracle.com/mysqlsupport/entry/innodb_locks_analysis_why_is on 14 April 2017.
Consider the scenario that you execute a query. You expect it to be fast – typically subsecond – but now it take not return until after 50 seconds (innodb_lock_wait_timeout seconds) and then it returns with an error:
mysql> UPDATE world.City SET Population = Population + 999 WHERE ID = 130; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
You continue to investigate the issue using the sys.innodb_lock_waits view or the underlying Information Schema tables (INNODB_TRX, INNODB_LOCKS and INNODB_LOCK_WAITS).
Note: The above Information Schema tables with lock and lock waits information have been moved to the Performance Schema in 8.0 as the data_locks and data_lock_waits tables. The sys schema view however …[Read more]
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.…
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.…
10 Older Entries »