english

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

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.

Introduction

Galera is a synchronous Multi-Master Replication for MySQL. It is therefore in competition with several other MySQL architectures:

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...

Introduction

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

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

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.

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:

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.

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.

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:

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.

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.

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:

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.

Dear MySQL and MariaDB User,

With this Newsletter you receive the News about FromDual for Summer 2011.

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:

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

• 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.

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.

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.

In the following article I have summarized some steps and hints to set-up a MySQL active/passive failover 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 failover Cluster is the following:

Be careful when using SAN (Storage Area Networks) or similar shared storage solutions (and any other virtualization, consolidation or cloud solutions).

This week it happened again: A customer called us because he had some troubles with his on-line shop (note the date!). Everybody in his company complained that the databases are answering slowly.

From the Cooperation of these two companies arises the biggest independent service provider for MySQL and MariaDB in Europe.

Hamburg, Uster -- February 28, 2011 - The Hamburg based PrimeBase Technologies and the near Zürich located FromDual are forming a Cooperation for MySQL products and services, starting March 1st, 2011.

This Cooperation enables both companies to offer a complete set of services for all MySQL and MariaDB customers.

The customers of both parties now have he possibility to demand a 24x7 support service from their provider.