In this post, we will see how to change the default character set from the default latin1_swedish_c to utf8_general_ci (or any other) and how to propagate charset changes in a master-master replication environment. 1. First determine the current default values, by issuing the MySQL command: mysql> show variables like ‘%character_set_%’; This will return something like […]
mysql
MySQL: how to figure out which session holds which table level or global read locks
Question: How to Retrieve Table Level or Global Read Lock Status from Running MySQL Server? Prior to MySQL 5.7, it is not possible to figure out table level or global read locks held by each sessions. As of MySQL 5.7, a new performance schema table, metadata_locks is added. This performance schema can achieve the goal. […]
Recommended Configuration of the MySQL Performance Schema
In this post, we will see how to configure the Performance Schema and avoid it becomes a performance bottleneck. The default settings are a good starting point. Those have been extensively tested to ensure that in the vast majority of cases, they will not cause a severe overhead. It can be worth considering to enable […]
MySQL: Identify what user and thread are holding on to a meta data lock that is preventing other queries from running
The preferred solution depends on the version of MySQL. If you are using MySQL 5.7 or later with metadata lock instrumentation enabled in the Performance Schema (available in MySQL 5.7 and later), it is possible to get the information directly from the performance_schema.metadata_locks table. Otherwise, there is no direct way to get the information. To […]
MySQL: How to kill a Long Running Query using max_execution_time
In this post, we will learn about what options are available if you need to automatically kill long-running queries. MySQL 5.7 and Later For SELECT Statement Starting from MySQL 5.7.4 there is support for automatic timeouts of read-only SELECT statements (the following discussion assumes 5.7.8 or later as the feature had changes to the option/hint […]
How to install and configure MySQL sys schema
This is general guidance for DBAs who are looking to use the MySQL sys schema. Introduction The MySQL sys schema is a collection of views, stored procedures, and stored functions to help MySQL database administrators get insight into what MySQL is doing and to help use the Performance Schema. However while the sys schema primarily […]
How to Troubleshoot InnoDB Lock Issues
In this post, we will learn about the tools available to investigate issues with InnoDB locks preventing queries to run. The two commands that can be used in all versions of MySQL to investigate locks preventing InnoDB queries to proceed are SHOW [FULL] PROCESSLIST and SHOW ENGINE INNODB STATUS. Additionally in MySQL 5.1 using InnoDB […]
Troubleshooting MySQL Query hung – “Waiting for Table Flush”
Question: The Queries are hung and Process list shows: “Waiting for table flush”. How to troubleshoot the issue? There are two main causes of this problem. The most obvious and easiest to detect is a pending FLUSH TABLES WITH READ LOCK. It is obvious because the pending FLUSH can be seen in the process list. […]
MySQL: How To Find Queries Taking Longer Than N Seconds
Question: How to find queries taking more than a given amount of time to complete? The simplest way to find long-running queries is to look at the process list. There are various options for how to do this depending on your version and whether you have the Sys Schema installed. The examples in this post […]
MySQL Backup stuck at “Starting to lock all the tables”
The Problem When starting a backup, the message “Starting to lock all the tables” appears and then backup stalls, it will not continue even after many hours. MySQL Enterprise Backup connection on the server can be observed with SHOW PROCESSLIST to be in the following state: State: Waiting to get readlock Info: FLUSH TABLES WITH […]