You are here

Shinguz's blog

FromDual Performance Monitor for MySQL (MPM) v0.9 released

On April 2nd 2012 FromDual released the new version v0.9 of its Performance Monitor for MySQL (mpm). The new version can be downloaded from here.

The Performance Monitor for MySQL (mpm) is an agent which is hooked into Zabbix. Zabbix is an integrated Enterprise Monitoring solution which can produce performance graphs and alerting.

The changes in the new release are:

Troubles with MySQL 5.5 on FreeBSD 9

FreeBSD 9 seems to have some troubles with MySQL 5.5.20. A customer has moved from MySQL 5.0 on Linux to MySQL 5.5 on FreeBSD 9. He experienced a lot of periodic slow downs on the new, much stronger, system which he has not seen on the old Linux box.

This slow downs were also shown in high CPU system time but we could not see any I/O going on.

When we looked into MySQL we have seen many threads in Opening tables state in the MySQL processlist.

Does InnoDB data compression help with short disk space?

Taxonomy upgrade extras: 

Because we are a bit short off disk space on one of our servers I had the idea to try out the MySQL feature Data Compression for InnoDB. This feature is useful if you have tables with VARCHAR, BLOB or TEXT attributes.

To not make it not too simple our table is partitioned as well. Our table looks like this:

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

Introduction

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

Introduction

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:

INNODB_CMP
INNODB_CMP_RESET
INNODB_CMPMEM
INNODB_CMPMEM_RESET
INNODB_TRX
INNODB_LOCK_WAITS
INNODB_LOCKS

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: 

Abstract:

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.

Pages

Subscribe to RSS - Shinguz's blog