Metadata locks – Live demo

by nivas on April 16, 2015

 

Why metadata locks ?

The server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted started transaction in another session.

Session 1 :

start transaction ;

use market ;

select * from store ;

session 2:

Drop table store ;

show processlist :

show processlist ;
+—-+——+———–+——–+———+——+———————————+———————-+———–+—————+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+—-+——+———–+——–+———+——+———————————+———————-+———–+—————+
| 40 | root | localhost | market | Query | 110 | Waiting for table metadata lock | drop table store | 0 | 0 |
| 41 | root | localhost | market | Query | 0 | init | show processlist | 0 | 0 |
| 42 | root | localhost | market | Query | 31 | Waiting for table metadata lock | select id from store | 0 | 0 |
+—-+——+———–+——–+———+——+———————————+———————-+———–+—————+

 

{ Comments on this entry are closed }

 

 

1) To find slow queries

pt-query-digest /var/lib/mysql/cloud-server-01-slow.log > slowqueroutput.log

2) Between a particular time range

pt-query-digest –since ’2015-04-10 00:00:00′ –until ’2015-04-10 23:59:59′ /var/lib/mysql/cloud-server-01-slow.log > slowqueryrange.log

3) For a particular database

pt-query-digest –filter ‘$event->{db} && $event->{db} =~ /employee/’ /var/lib/mysql/cloud-server-01-slow.log > slowqueroutputemp.log

pt-query-digest –filter ‘$event->{db} && $event->{db} =~ /market/’ /var/lib/mysql/cloud-server-01-slow.log > slowqueroutputmkt.log

4) For a particular user

pt-query-digest –filter ‘$event->{user} && $event->{user} =~ /root/’ /var/lib/mysql/cloud-server-01-slow.log > slowqueryoutputuser.log

{ Comments on this entry are closed }

How to use mysql_config_editor – video

April 4, 2015

Below video will depict : * How to set a login path * How to use login path in mysql client * How to use login path in mysqldump * How to remove login path     You can download the slides from below URL config_editor

Read the full article →

Musings on MySQL enhancements

April 1, 2015

1) Update_time not accurate in information_schema.tables Information_schema.tables has a field called update_time and in 5.6 you will notice that it is always NULL. Future versions of MySQL will fix this problem 2) Multiple instance manager Currently creating , managing and dropping instances in MySQL requires many DBA hours. mysqld_multi is there to help you but […]

Read the full article →

MySQL Vs Db2 – Part 1

March 9, 2015

MySQL is open-source RDBMS and is gaining popularity in the recent years.. I am starting a comparison series for mysql and db2..This series will help MySQL DBAs to catchup Db2 quickly and vice versa.. Please post your feedback 1. Knowing the software version : mysql> select version() ;   +———–+ | version() | +———–+ | […]

Read the full article →

mysqlfailover – Installation and Operations tutorial

March 6, 2015

Configuration   1) Download the utilities. Python connector is a prerequisite and this needs to be installed along with mysql utilities rpm wget http://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.0.2-1.el6.noarch.rpm wget http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.5.3-1.el6.noarch.rpm   2) Install the RPMs. “Mysqlfailover –help” will confirm the install of the utilities rpm –ivh mysql-connector-python-2.0.2-1.el6.noarch.rpm rpm -ivh mysql-utilities-1.5.3-1.el6.noarch.rpm   3) Set the GTID MODE to ON. GTID […]

Read the full article →

mysqldump – 25 tips for DBAs

December 1, 2014

1) Is mysqldump text backup or binary backup It is a text backup . If you open the backup file you will see all the statements that can be used to recreate databases and objects . It also has the insert statements to populate the tables with data 2) What is the syntax for mysqldump […]

Read the full article →

MySQL and swapping

November 24, 2014

Did you ever encounter swap space issue with MySQL ? This problem is really annoying and here are some possible solutions : 1) Track memory usage.. Try to identify the bottleneck using query below. It is not trivial job to zero in on the problem heap.. There are several temp tables being created at run […]

Read the full article →

Slow query log – Confusing information in manuals

October 16, 2014

Below is the excerpt from MySQL manuals on slow query log : “By default, administrative statements are not logged, nor are queries that do not use indexes for lookups. This behavior can be changed using log_slow_admin_statements and log_queries_not_using_indexes, as described later. “ This manual entry is kind of misleading . It means that queries not […]

Read the full article →

10 common replication problems

September 23, 2014

Below are 10 common replication problems : 1)Session binlog : Setting sql_log_bin = 0 will disable binary logging for the session. So any DML/DDL query executed within that session on the master will not be replicated to slave Example: Turn off binary logging on master : mysql> set sql_log_bin = 0 ; Query OK, 0 […]

Read the full article →