One of our Remote DBA service clients recently had an issue with size on disk for a particular table; in short this table was some 25 million rows of application audit data with an on disk size of 345GB recorded solely for the purposes of debugging which may or may not occur. Faced with the task of [...]
Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
Percona XtraDB Cluster (PXC) and the technology it uses (Galera) is an exciting alternative to traditional MySQL replication. For those who don’t know, it gives you: Fully Synchronous replication with a write latency increase equivalent to a ping RTT to the furthest node Automatic cluster synchronization, both incremental and full restores The ability to read [...]
Sharing an auto_increment value across multiple MySQL tables (revisited)
October 26, 2010 By 7 Comments
A couple of weeks ago I blogged about Sharing an auto_increment value across multiple MySQL tables. In the comments, a few people wrote in to suggest alternative ways of implementing this. I just got around to benchmarking those alternatives today across two large EC2 machines:
How (not) to find unused indexes
October 16, 2009 By 2 Comments
I’ve seen a few people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed. This method is flawed – here’s the first reason why:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | CREATE TABLE `sales` ( `id` int(11) NOT NULL AUTO_INCREMENT, `customer_id` int(11) DEFAULT NULL, `status` enum('archived','active') DEFAULT NULL, PRIMARY KEY (`id`), KEY `status` (`status`) ) ENGINE=MyISAM AUTO_INCREMENT=65691 DEFAULT CHARSET=latin1; mysql> SELECT count(*), status FROM sales GROUP by status; +----------+---------+ | count(*) | status | +----------+---------+ |   65536 | archived | |     154 | active | +----------+---------+ 2 rows in set (0.17 sec) mysql> EXPLAIN SELECT * FROM sales WHERE status='active'; # query 1 +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key   | key_len | ref  | rows | Extra      | +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ | 1 | SIMPLE     | sales | ref | status       | status | 2      | const | 196 | Using where | +----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ 1 row in set (0.06 sec) mysql> EXPLAIN SELECT * FROM sales WHERE status='archived'; # query 2 +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra      | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE     | sales | ALL | status       | NULL | NULL   | NULL | 65690 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.01 sec) |
The cardinality of status index is woeful, but provided that the application [...]
AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
How would you expect AUTO_INCREMENT to work with MERGE tables ? Assuming INSERT_METHOD=LAST is used I would expect it to work same as in case insertion happens to the last table… which does not seems to be the case. Alternatively I would expect AUTO_INCREMENT to be based off the maximum value across all tables, respecting [...]
Recovering Innodb table Corruption
July 4, 2008 By 21 Comments
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 [...]
Using MMM to ALTER huge tables
March 27, 2008 By 11 Comments
Few months ago, I wrote about a faster way to do certain table modifications online. It works well when all you want is to remove auto_increment or change ENUM values. When it comes to changes that really require table to be rebuilt – adding/dropping columns or indexes, changing data type, converting data to different character [...]
PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
We’re working with web site preparing for massive growth. To make sure it handles large data sets as part of the process we work on generation test database of significant size as testing your application on table with 1000 rows may well give you very dangerous false sense of security. One of the process web [...]
