You are here

Shinguz's blog

What can MySQL performance monitoring graphs tell you?

Many of you may monitor their databases for different purposes. Beside alerting it is often good to also make some graphs from MySQL performance counters to see what is actually happening on your database.

I prefer MySQL binary tar balls with Galera...

Taxonomy upgrade extras: 

In my set-ups I have different MySQL versions (MySQL 5.0, 5.1, 5.5 and 5.6, Percona Server 13.1 and 24.0, MariaDB 5.2.10, 5.3.3, Galera 1.0, 1.1 and 2.0) running in parallel at the same time.

Recover lost .frm files for InnoDB tables

Recently I found in a forum the following request for help:

Rolling upgrade of Galera 1.0 to 1.1

Taxonomy upgrade extras: 

A few days ago Codership announced their new version Galera v1.1 - synchronous Replication Cluster for MySQL. Before we look at the new feature of Rolling Online Schema Upgrade (OSU) we have a look at how to upgrade to the new Galera release.

How MySQL behaves with many schemata, tables and partitions


Recently a customer claimed that his queries were slow some times and sometimes they were fast.

First idea: Flipping query execution plan caused by InnoDB could be skipped because it affected mainly MyISAM tables.

Second idea: Caching effects by either the file system cache caching MyISAM data or the MyISAM key buffer caching MyISAM indexes were examined: File system cache was huge and MyISAM key buffer was only used up to 25%.

I was a bit puzzled...

Building Galera Replication from Scratch


MySQL/Galera synchronous Multi-Master Replication consists of 2 parts:

MySQL Vala Program Example

Taxonomy upgrade extras: 

Summary: In this article we have a short look at a simple MySQL example program written in Vala.

Exercises of Advanced MySQL Developer Workshop

Taxonomy upgrade extras: 

Our Advanced MySQL Developer Workshop is over now and IMHO it was quite a success.
During the workshop it is planned to have some exercises. If you are curious and if you want to test or train your MySQL skills, find the exercises here: Advanced MySQL Developer Workshop Exercises.

The solutions are available on request as well.

MySQL JMeter Webshop Benchmark

Taxonomy upgrade extras: 

Abstract: In this article we provide a little JMeter WebShop Example Benchmark for MySQL.

For our Advanced MySQL Developer Workshop we have one exercise Benchmarking MySQL with JMeter. For this exercise we are using the FoodMart-2.0 [1] Schema and simulating a simple WebShop Transaction:

To zip, or not to zip, that is the question

Taxonomy upgrade extras: 

Abstract: In this article we have a look at the compression options of common zipping tools and its impact on the size of the compressed files and the compression time. Further we look at the new parallel zip tools which make use of several cores.

How good is MySQL INSERT TRIGGER performance

Abstract: In this article we discuss how big is the performance impact of MySQL TRIGGERs compared to application side logging (with INSERT) into a MySQL table.

ER-Diagram of the InnoDB Data Dictionary

Taxonomy upgrade extras: 

With the new MySQL 5.6 release there are some more InnoDB Data Dictionary Tables in the INFORMATION_SCHEMA:

New with MySQL 5.5 are:


New with MySQL 5.6 are:

Warming up the InnoDB Buffer Pool during start-up

Taxonomy upgrade extras: 

Abstract: Heating up the InnoDB Buffer Pool during the MySQL instance startup should significantly improve InnoDB Performance in the beginning of the life of the Instance. This is achieved by sequential scans of the needed data instead of random I/O reads which would happen when we just let the system work it out by itself.
How to find the database objects which can be loaded during MySQL start-up and how to load them automatically is described in this article.

Using NULL as default values

Taxonomy upgrade extras: 


It is common practice in MySQL table design that fields are declared as NOT NULL but some non-sense DEFAULT values are specified for unknown field contents. In this article we show why this behavior is non optimal an why you should better declare a field to allow NULL values and use NULL values instead of some dummy values.

MySQL out in the wild

One of our partners recently asked me on what platforms do we usually see MySQL installed out there...

The last 5 years I gave the answer: Typically it is 80% Linux, 10% Windows, 5% Solaris and 5% all others. But this was only the picture of my limited view and I was not sure how objective this was.

This time I really wanted to know it and so I collected the information of about 570 MySQL installations of customers.

The following numbers came out:

MySQL Query Cache does not work with Complex Queries in Transactions

Taxonomy upgrade extras: 

We did recently a review of one of our customers systems and we found that the Query Cache was disabled even thought it had significant more read than write queries.
When we asked the customer why he has not enabled the Query Cache he mentioned a review that was done a few years ago and which stated that the Query Cache hit ratio was non optimal.
This was verified on a testing system which had the Query Cache enabled by accident.

But we all thought that the Query Cache would make sense in this situation so we investigated a bit more.

Regularly flushing the MySQL Query Cache

Taxonomy upgrade extras: 

When we analyze our customers systems we see typically a high fragmentation of the query cache after a while. This leads to a less optimal use of the Query Cache than possible.

With the following Query you can see the values for your Query Cache:

InnoDB Graphs for MySQL Performance Monitor

We have just released v0.7.1 of the FromDual MySQL Performance Monitor. The new release can be downloaded from here.

In all editions some error messages have been cleaned-up, the fall-back data gather method mysql has been removed and the Maria SE template has been renamed to Aria.

In the edition dedicated to our customers most of the InnoDB graphs which are available with MEM v2.3.3 have been implemented now.

Be cautious when using Virtualized System with your Database

A customer rose a support case with a problem on his Master-Master set-up. The 2nd Master claims to have a problem:

German speaking MySQL User Group (DMySQLAG) founded today

Taxonomy upgrade extras: 
Today the German speaking MySQL User Group (DMySQLAG) was officially founded in Berlin. The association advocates:
  • the information of the use, the handling and the experience with MySQL and development in the MySQL Eco-System as well as systems which use MySQL.
  • the exchange of experience between MySQL users about MySQL and other systems.
  • the Advice and Cooperation with Oracle and vendors of other systems.
  • the submission of suggestions of the members to Oracle and vendors of other systems.

When a MySQL table was last touched

In our last customer project we had around 600 Gbyte of data in a MySQL database. Because this database consumed a significant amount of our disk space and backups with the InnoDB backup tool took pretty long we wanted to find out if we could get rid of some of the tables.

This application was growing over the last 10 years and it was not clear if some tables are still in use or not.

But how to find out when a table was touched last? MySQL/InnoDB theoretically could know about but does not report this information.

Dumping BLOB's from the MySQL database

Taxonomy upgrade extras: 

A customer who is handling digital certificates had a problem with one of those. So we had to investigate.

Because the certificate is in binary form it is stored in a BLOB and we had to extract it from the database to do some verifications.

What first came to my mind was to extract the certificate with the SELECT INTO OUTFILE command. But the verification tool complained and told us that the certificate has a wrong format.

Configuration of MySQL for Shared Hosting

If you ask around about shared hosting setups with MySQL everybody is frightened. In fact it looks like shared hosting is one of the most difficult setups you can get.

The number of users is big, the number of tables huge and the load pattern is completely unpredictable and the queries often very, let us say: non-optimal.

Here one of the DBA wisdoms come into play: Controlling developers is like herding cats.

If you talk to the Shared MySQL Hoster they confirm that this setups are very demanding!

MySQL HA (high availability) Cluster cookbook

In the following article I have summarized some steps and hints to set-up a MySQL active/passive fail-over Cluster also sometimes called MySQL HA.

With such a set-up you can achieve a 99.99% (4x9) HA set-up for MySQL (52 minutes downtime per year). The same procedure also works for PostgreSQL, Oracle and other database systems running on Linux (DRBD runs on Linux only).

The concept

The concept of an active/passive fail-over Cluster is the following:


Subscribe to RSS - Shinguz's blog