You are here

Shinguz's blog

Change MyISAM tables to InnoDB and handle SELECT COUNT(*) situation

Its a known problem that changing the Storage Engine from MyISAM to InnoDB can cause some problems [ 1 ] if you have queries of this type:

SELECT COUNT(*) from table;

Luckily this query happens rarely and if, the query can be easily omitted or worked around by guesstimating the amount of rows in the table. For example with:

MySQL @ FrOSCon 7 in St. Augustin (Germany)

Taxonomy upgrade extras: 

Also this year we will have a special track for MySQL, Galera, Percona und MariaDB at the FrOSCon in St. Augustin in Germany. The conference is scheduled for August 25 and 26 2012.

Together with the PostgreSQL people we are organizing a sub-conference for Open Source RDBMS there. Now we are looking for interesting talks about MySQL and related techniques like Galera, Percona, MariaDB. The only restriction for the talks is: They must be about an Open Source topic.

How to make the MySQL Performance Monitor work on Windows?

A customer recently was asking why our FromDual Performance Monitor for MariaDB and MySQL (fpmmm) is not working on Windows...? The answer is short: It was developed on Linux and never tested on Windows...

But I was wondering how much effort it would take to make it work on Windows as well.

I was quite surprised how fast it was to make the basic functionality working on Windows. It took me less than one hour to install, configure and patch FPMMM.

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.

Migrating from MySQL Master-Master Replication to Galera Multi-Master Replication

Introduction

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

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

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:

Pages

Subscribe to RSS - Shinguz's blog