FromDual TechFeed (en)

From time to time some maintenance work on the MySQL database has to be done. During the maintenance window we do not want to have application traffic on the database.

Sometimes it is hard to shut down all applications spread over the whole company. Or we want to allow only some specific hosts to access mysql from remote (for example the monitoring system or the backup server).

For this purpose we can use the Linux packet filtering.

To see what packet filtering rules are available we can run the following …

The mpm agent writes a lock file to prevent that several instances are running at the same time and piling up and possibly slow down the system.

This is the message for. There are several reasons why this message can occur:

• Agent are started too frequently or are locked by something
• Agent died and did not clean the lock file
• Agent quit before cleaning the lock file

Theoretically this message should disappear after a while (10 tries?). You can also try to kill process (3366 in this case) and …

A while ago it was pretty inconvenient to start a complete Galera Cluster from scratch. Rolling restart an such things are already working well but bootstrapping was a pain.

With Galera v2.2 new functionality came in. We tried it out and it did not work as documented. :-( Thanks to Teemu’s help we found there was a documentation bug in the Galera documentation.

The settings which were working for us are:

wsrep_cluster_address = "gcomm://192.168.1.2,192.168.1.3?pc.wait_prim=no"

And when all …

Copy a directory structure from one to an other server:

tar zcvf - mysql | ssh mysql@laptop3 "cd /home ; tar zxvf -"

Some MySQL customers do not want to use the root user for mysqldump backups. For this user you have to grant the following minimal MySQL privileges:

mysqldump --single-transaction (InnoDB)

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'secret';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';

mysqldump --lock-all-tables (MyISAM)

GRANT LOCK TABLES ON *.* TO 'backup'@'localhost';

If we missed a privilege please let us know.

In this weeks MySQL workshop we were discussing, beside other things, about the innodb_file_per_table parameter and its advantages of enabling it. In addition there was a discussion if the InnoDB system tablespace file can be shrinked once it has been grown very large or not. We all know the answer: The InnoDB system tablespace file does never shrink again.

But why should it not be possible? Other databases like for example Oracle can shrink or even get rid of tablespace files… After some …

MySQL hosting providers can easily loose the overview over their customers and which user or schema is still in use and which not.

The MySQL database becomes bigger and bigger, uses more and more RAM and disk space and the backup takes longer and longer.

In this situation it would be nice to know which MySQL database user has logged in within the last 6 months for example. MySQL database users who did not login within a defined period can be backuped and removed from the production MySQL database.

The …

When a MySQL Slave is set-up with mysqldump you have 2 possibilities:

• You dump into a file and then load the data into the Slave with the mysql client utility.
• You dump directly into the mysql client utility.

The first possibility has the advantage that you can start the load again if it failed. You can look into the file (and do some changes if needed).
The second possibility has the advantage that you do not need disk space and that it is possibly faster. But when the load fails you have to start from …

Important: Before you start any operation mentioned below do a proper file system backup of your XFS file system you want to resize. If MySQL is running on this mount point do this with a stopped mysqld. Alternatively you can also use mysqldump to do the MySQL backup but test the restore time before continuing to not experience ugly surprises…

All these operations have to be performed as the root user. First we want to see what mount points are available:

shell> df -h
Filesystem            Size …

MySQL temporary tables are created either in memory (as MEMORY tables) or on disk (as MyISAM tables). How many tables went to disk and how many tables went to memory you can find with:

mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 49094    |
| Created_tmp_tables      | 37842181 |
+-------------------------+----------+

Tables created in memory are …

Based on customer feedback we have decided to add a plugin Galera Cluster for MySQL to our MySQL Nagios/Icinga Plugins.

The module checks, if the node is in status Primary and if the expected amount of Galera Cluster nodes is available. If not, a warning or an alarm is returned.

The script is written in Perl and is Nagios Plugin API v3.0 compatible.

You can download it from our download page.

If you have suggestions for improvements, please contact us. Bugs can be reported at our bugs database.

The …

During and after Henriks great talk about Galera Cluster at the FrOSCon 2012 in St. Augustin we found 2 important things related to Galera Cluster for MySQL:

• The InnoDB double write buffer (innodb_doublewrite) should not be disabled anymore for Galera when using v2.0 and higher!!! The reason for this is: When MySQL crashes InnoDB pages might get corrupted during the crash. They would be fixed by the blocks from the double write buffer during auto-recovery. But if the double write buffer is disabled they …

Recently a customer has shown up with some deadlocks occurring frequently. They were of the following type (I have shortened the output a bit):

*** (1) TRANSACTION:

TRANSACTION 22723019234, fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1216, 14 row lock(s)
update location set expires='2012-08-10 04:50:29' where username='12345678901' AND contact='sip:12345678901@192.168.0.191:5060' AND callid='945a20d4-8945dcb5-15511d3e@192.168.0.191'

*** (1) WAITING FOR THIS LOCK TO …

There is a product out in the wild which is called SchoonerSQL
[ 1
] from an American company called Schooner Information Technology
[ 2
]. They claim to have top-performance
[ 3
] and high-availability features with up 99.999% availability
[ 1
].
The technology seems to be based on InnoDB, SSD and some kind of replication with failover mechanism.

Unfortunately this product is neither open source nor is it really transparent what they are doing. Which is quite suspicious… Because this lack of …

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:

SHOW TABLE STATUS LIKE 'test';

But in some rare cases customer really needs these values for some reasons. To not exhaust the resources of the server with this query which can be fired …

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.

We encourage you to send your proposals. …

Table of Contents

• Building the Galera Load Balancer
• Starting the Galera Load Balancer
• Querying the Galera Load Balancer
• Draining nodes with Galera Load Balancer
• Removing nodes from and adding nodes to Galera Load Balancer

When you install a Galera Cluster for MySQL for High Availability (HA) it is not enough to install the Database Cluster to achieve this goal. You also have to make the application aware of this HA functionality. This is typically done with some kind of load balancing mechanism between …

On April 2^nd 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:

New functionality

• A new server module gathers MySQL database specific server informations. This is especially interesting for the Monitoring …

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.

The first idea was to increase table_open_cache to 2048 and …