pt-query-digest is a tool available from Percona for analysis of MySQL queries from slow log, general log and binary log files. It can also analyze queries from mysql processlist and tcpdump mysql data. It is one of the best tools for slow query analysis on mysql server and can help you to improve mysql performance and efficiency. This tool is part of Percona Toolkit, the well-known MySQL management software utility toolkit for MySQL server administration (of course it also works on MariaDB and Percona Server). Formerly, percona toolkit was known as maatkit and pt-query-digest was called mk-query-digest. It has lots of options available to explore. In this post we'll compare some of the different sources pt-query-digest can use.

Slow query logs

MySQL can log …

Stored routines give you the ability to create a statement in one application (MySQL) that can be used in multiple client applications that use different programming languages or work on different platforms.

Take the MySQL for Developers course to learn more about stored routines and other key topics. This will build your expertise as a MySQL Developer.

You can take this 5-day instructor-led course as a:

• Live-Virtual Event: Attend a live-event from your own desk, no travel required. Choose from a selection of events already on the schedule to suit different timezones.
• In-Class Event: Travel to an education center to attend a class. Below is a selection of the events already on the schedule.

Taxonomy upgrade extras:  myenv operation MySQL Operations multi instance consolidation Backup catalog

FromDual has the pleasure to announce the release of the new version 1.1.1 of its popular MySQL, MariaDB and Percona Server multi-instance environment MyEnv.

The majority of improvements happened in the MySQL Backup Manager (mysql_bman) where we added a …

Introduction

This blog is a follow up to my original blog in https://blog.mariadb.org/performance-evaluation-of-mariadb-10-1-and-mysql-5-7-4-labs-tplc/ . First of all I would like to thank for all the comments I received. Based on the comments there was a concern if the differences seen on performance was due to different configuration setup. Furthermore, I did not know that there was a configuration variable to get similar multi-threaded flush mechanism on MySQL as there is on MariaDB. To find out if the different configuration variables or different defaults were the reason for different performance, I ran several rounds of new tests.

Test 1

Changing number of buffer pool instances or value of innodb thread concurrency do not seem to have significant effect on at least LinkBench benchmark …

Disclaimer: I’m not specially an expert of benchmarking, this post is to compare different options. All comments and advices are welcome.

I’m not telling you anything new, the show processlist command is a fantastic command line tool for instant check.
But what about monitor your databases with this command embedded in a tool?

Just have a look at this graph:

With 5K queries per seconds, how much will be hidden with a show processlist executed every seconds? Probably a lot.
So, I wanted to test which alternatives could be efficient to retrieve all the queries during a time lapse.

Test procedure and configuration

I used sysbench 0.5 (with oltp.lua) to make my tests on different configurations and …

Below are some of the common scenarios where information_schema is useful :

Tables table

How to check the database size using information_schema ?

SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024 
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;

How to list the tables created before 4 days ?

select * from information_schema.tables where CREATE_TIME < NOW() - INTERVAL 4 DAY ;

Columns table :

List all the tables that have a column called birth_date

select table_name from information_schema.columns where table_schema='employees' and column_name='birth_date';

List all tables without primary/unique key:

select 
    table_schema,table_name 
from  
    information_schema.columns  
group by 
    table_schema,table_name   
having 
    sum(if(column_key in ('PRI','UNI'), …