You are here

Shinguz's blog

Bootstrapping Galera Cluster the new way

Taxonomy upgrade extras: 

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.

Privileges of MySQL backup user for mysqldump

Taxonomy upgrade extras: 

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:

Shrinking InnoDB system tablespace file ibdata1 PoC

Taxonomy upgrade extras: 

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.

Last login of MySQL database users

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.

MySQL backup to file, gzip and load in one step

Taxonomy upgrade extras: 

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

Resize XFS file system for MySQL

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:

MySQL tmpdir on RAM-disk

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:

Galera Cluster Nagios Plugin

Taxonomy upgrade extras: 

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.

Galera Cluster discussions at FrOSCon 2012

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:

Deadlocks, indexing and Primary Key's

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

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.

Pages

Subscribe to RSS - Shinguz's blog