News

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.

A rolling upgrade of your synchronous Galera Replication Cluster from version 1.0 to 1.1 is quite easy when you stay at the same MySQL version (5.5).

To not lose the availability of your database service during the upgrade you should have at least 3 Galera nodes in …

On December 2^nd 2011 FromDual released the new version v0.7.2 of its Performance Monitor for MySQL. The new version can be downloaded from here.

The Performance Monitor for MySQL is an agent which is hooked into the agent of the well known Monitoring solution called Zabbix. Zabbix is an integrated Enterprise Monitoring solution which can produce performance graphs and alerting.

The changes in the new release are:

New functionality

• A new module for Galera was added (mainly Galera 0.8).
• A new module for the …

Introduction

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

• Master-Master Replication with MySQL
• MySQL Cluster
• The non-open source product called Schooner

Very often they can be easily replaced by Galera’s synchronous Multi-Master Replication for MySQL.

All those products have some advantages and disadvantages. Very often MySQL Master-Master Replication is used in the field because of its simplicity to set-up. But …

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…

Then we checked the table_open_cache …

Introduction

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

• The wsrep patches for MySQL (codership-mysql) and
• the Galera Replication Plugin (galera).

If you do not want to download the prepared binaries you can build it on you own.
First you have to download the native MySQL sources, then patch it with the Galera wsrep patches and compile it. In a second step you have to build the Galera Plugin.

This is especially useful because in the standard Galera binary tar balls the garbd …

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

Recently a customer pointed me to a programming language called Vala. Vala is a C-style programming language generating C code which afterwards can be compiled and linked with the normal gcc.
This I found pretty useful to not mess around with pointers and all this stuff in C and to be capable anyway to write C programs for some projects I had in mind in my head since long.
Vala is mostly used around the Gnome …

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.

If you like those exercises we could also provide the exercises of our Advanced MySQL DBA Workshop. Please let us know if you are interested in…

Have fun.

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:

• Logging in
• Put some articles into the basket
• Buy the articles
• Log out

I found it pretty hard to find good and detailed examples how to do this with JMeter and I wasted a lot of time searching and figuring out how it …

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.

Start with a backup first

From time to time I get into the situation where I have to compress some database files. This happens usually when I have to do some recovery work on customers systems. Our rule number 1 before starting with a recovery is: Do a file …

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.

What was in my mind from the past

A while ago when MySQL released its Stored Language features in v5.0 I have seen a book
[1
] about this topic. In this book was a performance comparison between different implementations of computational tasks, one was done in MySQL Stored Language. The result was, that MySQL Stored Language feature sucks also …

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.

New Table in the INFORMATION_SCHEMA

Some of my …

Some people did not agree with my statements or had some questions…

A search using col_name IS NULL employs indexes if col_name is indexed. [1]

Some experiments on a table with a few (= 4) NULL values:

EXPLAIN SELECT * FROM t2 WHERE dt IS NULL;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       | …

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.

What we can see often out in the field

Recently we had a discussion with a customer if it makes more sense to store a default value or NULL in InnoDB tables when we do not know …

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:

Operating System

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 …

Dear MySQL and MariaDB User,

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

Sie können diesen Newsletter auch auf deutsch lesen oder sich den deutschprachigen Newsletter. abonnieren.

Topics

• Basic- and Silver Support for MySQL and MariaDB
• FromDual Performance Monitor for MySQL with more InnoDB Graphs
• Summer vacation without a MySQL DBA?
• Upcomming Trainings and Workshops
• Technical Information
• German Speaking MySQL User Group founded
• News about MySQL

Basic- and Silver-Support for …

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:

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 11328    |
| Qcache_free_memory      | 89442000 |
| Qcache_hits …

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.

Please let us know, what you want to have monitored in addition and send us your feedback and …

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

master2> SHOW SLAVE STATUS<br>G
                ...
    Master_Log_File: master1-bin.000014
Read_Master_Log_Pos: 97975045
   Slave_IO_Running: No
  Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 97975045
      Last_IO_Errno: 1236
      Last_IO_Error: Got fatal error 1236 from master when reading data from
                     binary log: 'Client requested master to start replication …