Blog

Long running transactions can be problematic for OLTP workloads, particularly where we would expect most to be completed in less than a second. In some cases a transaction staying open just a few seconds can cause behaviour that is entirely unexpected, with the developers at a loss as to why a transaction remained open. There are a number of ways to find long running transactions, luckily versions of MySQL from 5.6 onwards provide some very insightful instrumentation.

On a recent consulting engagement, the PSCE team were charged with what can be considered a fairly common task of synchronising tables between master and slave in MySQL Replication. On this occasion the schema contained both foreign key constraints and triggers, this post describes how we avoided the potential problems related to such an operation.

Recently we faced an issue when Nagios reported significant amount of "Locked" queries. To investigate and debug the issue we needed to get more insight about the state of MySQL and the OS at the time the locks occurred. This is how we got the much needed information.

Importing a text file containing a list of SQL commands into MySQL is a straightforward task. All you need to do is simply feed the file contents through pipe into MySQL command line client. For example: mysql app_production < dump.sql. The reasons for doing such imports can be very different - restoring MySQL backups created with mysqldump, manually replaying binary log events or performing database migrations during software roll-outs. While the task is simple, the import may not end successfully and when this happens, how to tell what the problem was?

MySQL lets database administrators define access rights on many levels – from the ability to run global commands down to access to individual columns. Some rights can be applied to many different objects, such as for example SELECT or UPDATE, which can be granted globally or restricted only to certain databases or tables, while others are only meant for one specific purpose. An example of the latter could be FILE privilege, which permits user to interact with the file system from inside a database instance. It only makes sense as the global right and not anywhere else.