October 31, 2014

Can’t Create Thread: Errno 11 (A Tale of Two Ulimits)

Recently some of my fellow Perconians and I have noticed a bit of an uptick in customer cases featuring the following error message:

The canonical solution to this issue, if you do a bit of Googling, is to increase the number of processes / threads available to the MySQL user, typically by adding a […]

Using pt-table-checksum with Percona XtraDB Cluster

As of Percona Toolkit v2.1.5, pt-table-checksum works correctly with Percona XtraDB Cluster, but it doesn’t work quite like a traditional replication setup because cluster nodes are not like traditional replicas.  In this post I demonstrate how to use pt-table-checksum with Percona XtraDB Cluster. First, you’ll need Percona Toolkit v2.1.5 or newer and Percona XtraDB Cluster 5.5.27-23.6 […]

Visualization tools for pt-query-digest tables

When you process MySQL slow query logs using pt-query-digest you can store samples of each query into query_review table and historical values for review trend analysis into query_review_history table. But it could be difficult to easily browse those tables without a good GUI tool. For the visual browsing of tables created by pt-query-digest you may […]

Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels

As an instructor with Percona I’m sometimes asked about the differences between the READ COMMITTED and REPEATABLE READ transaction isolation levels.  There are a few differences between READ-COMMITTED and REPEATABLE-READ, and they are all related to locking.

Why ALTER TABLE shows as two transactions in SHOW ENGINE INNODB STATUS

When executing an ALTER TABLE, InnoDB (and XtraDB) will create two InnoDB transactions: One transaction is created when the table being ALTERed is locked by the server. This will show up as something like “TABLE LOCK table schema.table_name trx id XXXX lock mode S” in SHOW ENGINE INNODB STATUS. Another is created when adding or […]

ALTER TABLE: Creating Index by Sort and Buffer Pool Size

Today I was looking at the ALTER TABLE performance with fast index creation and without it with different buffer pool sizes. Results are pretty interesting. I used modified Sysbench table for these tests because original table as initially created only has index on column K which initially contains only zeros, which means index is very […]

DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL

Suppose you have turned on innodb_file_per_table (which means that each table has its own tablespace), and you have to drop tables in a background every hour or every day. If its once every day then you can probably schedule the table dropping process to run during off-peak hours. But I have seen cases where the […]

On Character Sets and Disappearing Tables

The MySQL manual tells us that regardless of whether or not we use “SET FOREIGN_KEY_CHECKS=0″ before making schema changes, InnoDB will not allow a column referenced by a foreign key constraint to be modified in such a way that the foreign key will reference a column with a mismatched data type. For instance, if we […]

Testing Samsung SSD SATA 256GB 830 – not all SSD created equal

I personally like PCIe based Flash, but from a pricing point our customers are looking for cheaper alternatives. SATA SSD is an options. There is many products based on MLC technology, and Intel 320 I would say is the most popular. I do not particularly like its write performance – I wrote about it before, […]

STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication

DELETE IGNORE suppresses errors and downgrades them as warnings, if you are not aware how IGNORE behaves on tables with FOREIGN KEYs, you could be in for a surprise. Let’s take a table with data as example, column c1 on table t2 references column c1 on table t1 – both columns have identical set of rows for […]