November 24, 2014

Performance problem with Innodb and DROP TABLE

I’ve been working with an application which does a lot of CREATE and DROP table for Innodb tables and we’ve discovered DROP TABLE can take a lot of time and when it happens a lot of other threads stall in “Opening Tables” State. Also contrary to my initial suspect benchmarking create/drop table was CPU bound […]

How much space does empty Innodb table take ?

How much space would empty MyISAM table take ? Probably 8K for .frm file, 1KB for .MYI file and 0 for MYD file. .MYI file can be larger if you have many indexes. How much space will Innodb take:

Recovering Innodb table Corruption

Assume you’re running MySQL with Innodb tables and you’ve got crappy hardware, driver bug, kernel bug, unlucky power failure or some rare MySQL bug and some pages in Innodb tablespace got corrupted. In such cases Innodb will typically print something like this: InnoDB: Database page corruption on disk or a failed InnoDB: file read of […]

Efficient Boolean value storage for Innodb Tables

Sometimes you have the task of storing multiple of boolean values (yes/now or something similar) in the table and if you get many columns and many rows you may want to store them as efficient way as possible. For MyISAM tables you could use BIT(1) fields which get combined together for efficient storage:

The story of one MySQL Upgrade

I recently worked on upgrading MySQL from one of very early MySQL 5.0 versions to Percona Server 5.1. This was a classical upgrade scenario which can cause surprises. Master and few slaves need to be upgraded. It is a shared database used by tons of applications written by many people over more than 5 years […]

Why audit logging with triggers in MySQL is bad for replication

Recently I was tasked with investigating slippage between master and slave in a standard replication setup. The client was using Maatkit’s mk-table-checksum to check his slave data was indeed a fair copy of that of the master.

Percona Toolkit 2.2.12 is now available

Percona is pleased to announce the availability of Percona Toolkit 2.2.12.  Released on November 14, Percona Toolkit is a collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source. This release is […]

Percona Toolkit for MySQL with MySQL-SSL Connections

I recently had a client ask me how to use Percona Toolkit tools with an SSL connection to MySQL (MySQL-SSL). SSL connections aren’t widely used in MySQL due to most installations being within an internal network. Still, there are cases where you could be accessing MySQL over public internet or even over a public “private” […]

How to avoid hash collisions when using MySQL’s CRC32 function

Percona Toolkit’s  pt-table-checksum performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master – and the tool pt-table-sync synchronizes data efficiently between MySQL tables. The tools by default use the CRC32. Other good choices include MD5 and SHA1. If you have installed the FNV_64 user-defined function, pt-table-sync will […]

MySQL compression: Compressed and Uncompressed data size

MySQL has information_schema.tables that contain information such as “data_length” or “avg_row_length.” Documentation on this table however is quite poor, making an assumption that those fields are self explanatory – they are not when it comes to tables that employ compression. And this is where inconsistency is born. Lets take a look at the same table […]