September 1, 2014

Edge-case behavior of INSERT…ODKU

A few weeks back, I was working on a customer issue wherein they were observing database performance that dropped through the floor (to the point of an outage) roughly every 4 weeks or so. Nothing special about the environment, the hardware, or the queries; really, the majority of the database was a single table with […]

Reasons for run-away main Innodb Tablespace

So you’re running MySQL With innodb_file_per_table option but your ibdata1 file which holds main (or system) tablespace have grown dramatically from its starting 10MB size. What could be the reason of this growth and what you can do about it ? There are few things which are always stored in main tablespace – these are […]

MySQL Error Message Nonsenses

What MySQL honestly was never good at is giving good helpful error messages. Start with basics for example – The error message in case of syntax error gives you information about tokens near by but little details:

It would be much better if MySQL would give error give exact position of error (with complex […]

MySQL Server Variables – SQL layer or Storage Engine specific.

MySQL Server has tons of variables which may be adjusted to change behavior or for performance purposes. They are documented in the manual as well as on new page Jay has created. Still I see constant confusion out where which of variables apply to storage engines only and which are used on SQL layer and […]

Galera replication – how to recover a PXC cluster

Galera replication for MySQL brings not only the new, great features to our ecosystem, but also introduces completely new maintenance techniques. Are you concerned about adding such new complexity to your MySQL environment? Perhaps that concern is unnecessarily. I am going to present here some simple tips that hopefully will let fresh Galera users prevent […]

The ARCHIVE Storage Engine – does it do what you expect?

Sometimes there is a need for keeping large amounts of old, rarely used data without investing too much on expensive storage. Very often such data doesn’t need to be updated anymore, or the intent is to leave it untouched. I sometimes wonder what I should really suggest to our Support customers. For this purpose, the […]

Percona XtraBackup – A workaround to the failed assertion bug

I recently conducted a test backup of my “master-slave” setup in my VirtualBox as I was migrating from Percona Server 5.6.12 to version 5.6.13-rel61.0 with Percona XtraBackup v2.2.0 rev. 4885. However, doing the backup on my slave, I encountered this problem:

This is related to a bug posted by my colleague George https://bugs.launchpad.net/percona-xtrabackup/+bug/1177201. While I was […]

Benchmarking Percona Server TokuDB vs InnoDB

After compiling Percona Server with TokuDB, of course I wanted to compare InnoDB performance vs TokuDB. I have a particular workload I’m interested in testing – it is an insert-intensive workload (which is TokuDB’s strong suit) with some roll-up aggregation, which should produce updates in-place (I will use INSERT .. ON DUPLICATE KEY UPDATE statements […]

Knowing what pt-online-schema-change will do

pt-online-schema-change is simple to use, but internally it is complex.  Baron’s webinar about pt-online-schema-change hinted at several of the tool’s complexities.  Consequently, users often want to know before making changes what pt-online-schema-change will do when it runs.  The tool has two options to help answer this question: –dry-run and –print. When ran with –dry-run and –print, pt-online-schema-change changes nothing […]

Statement based replication with Stored Functions, Triggers and Events

Statement based replication writes the queries that modify data in the Binary Log to replicate them on the slave or to use it as a PITR recovery. Here we will see what is the behavior of the MySQL when it needs to log “not usual” queries like Events, Functions, Stored Procedures, Local Variables, etc. We’ll […]