MySQL diary #3: the tale of lagging databases, transactions and primary keys
Back in 2016, we were dealing with an issue with lagging slave on one of our database clusters. Interestingly, this problem was only affecting a single cluster and happening at a specific time every day. Here’s how we solved it.
Read a previous episode:
Transactions
Our first trace was a binary log containing massive transactions on querycache tables on various wikis. This table acts as a cache for MediaWiki special pages such as most linked articles. It’s refreshed on a daily basis (only when there was edit on a wiki within the last 24h).
Transactions are great if you know what you’re doing, but long running transactions are usually a problem. Transactions should be avoided when many rows are affected. And when you do not really need an option to rollback your changes.
Our journey towards the fix
We started fixing this issue by removing database transactions from code that was triggered by updateSpecialPages.php maintenance script. Sampled log of queries issued by MediaWiki (with the detailed context) was really useful here.
The MySQL 5.6 bug report that our DBA found was a great help for us and it actually gave us the root cause of lagging c1 cluster slaves. Due to the way InnoDB internally stores data having at least one UNIQUE key (PRIMARY counts as well) on a table is crucial. Without it, slave runs queries on such tables in way less effective way.
One of the worst transaction that was killing cluster replication was coming from one wiki — namely “ruvlab” that was performing delete and insert of ~75k rows in a single transaction (a separate ticket to fix it was reported). And the MySQL ticket says that replicating 10k of rows in tables affected by this bug can take up to 30 minutes. Yay!
So we decided to make an alter on this table.
The table schema before the alter:
CREATE TABLE `querycache` (`qc_type` varbinary(32) NOT NULL,
`qc_value` int(10) unsigned NOT NULL DEFAULT '0',
`qc_namespace` int(11) NOT NULL DEFAULT '0',
`qc_title` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
KEY `qc_type` (`qc_type`,`qc_value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1+--------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| qc_type | varbinary(32) | NO | MUL | NULL | |
| qc_value | int(10) unsigned | NO | | 0 | |
| qc_namespace | int(11) | NO | | 0 | |
| qc_title | varchar(255) | NO | | | |
+--------------+------------------+------+-----+---------+-------+
The alter itself:
DELETE FROM querycache WHERE qc_type = ‘’;
ALTER TABLE querycache DROP INDEX qc_type;ALTER TABLE querycache ADD UNIQUE INDEX `qc_type_value_ns_title` (qc_type, qc_value, qc_namespace, qc_title);
(The DELETE was necessary to clean up the table and remove duplicated entries that would prevent the unique index from being created)
The table schema after the alter:
CREATE TABLE `querycache` (`qc_type` varbinary(32) NOT NULL,
`qc_value` int(10) unsigned NOT NULL DEFAULT '0',
`qc_namespace` int(11) NOT NULL DEFAULT '0',
`qc_title` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
UNIQUE KEY `qc_type_value_ns_title` (`qc_type`,`qc_value`,`qc_namespace`,`qc_title`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1+--------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| qc_type | varbinary(32) | NO | PRI | NULL | |
| qc_value | int(10) unsigned | NO | PRI | 0 | |
| qc_namespace | int(11) | NO | PRI | 0 | |
| qc_title | varchar(255) | NO | PRI | | |
+--------------+------------------+------+-----+---------+-------+
As you can see MySQL treats this unique index as a primary key. We’re saved :)
Primary keys
Table data in InnoDB are stored in the index structure of the primary key (explicit or implicit). Hence a properly defined keys can save the storage required by your table.
Here’ s a comparison for the lyricwiki database (table statistics and schema):
mysql@slave.db-a.service.consul[lyricwiki]>SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'querycache' AND table_schema = 'lyricwiki'\G*************************** 1. row ***************************
TABLE_SCHEMA: lyricwiki
TABLE_NAME: querycache
DATA_LENGTH: 24248320
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 16842752
1 row in set (0.00 sec)ALTER here...mysql@slave.db-a.service.consul[lyricwiki]>SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'querycache' AND table_schema = 'lyricwiki'\G
*************************** 1. row ***************************
TABLE_SCHEMA: lyricwiki
TABLE_NAME: querycache
DATA_LENGTH: 15286272
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
Before the alter query (when we had a unique key that holds a copy of row data) “querycache” table data and an index was three times bigger than after the alter. Note the empty index_length entry. Links to a fix.
Learnings and final thoughts
- Let’s start with the obvious one — upgraded software brings bug fixes, but also introduces new ones :)
- Detailed logging with a proper context provides great help when debugging issues.
- Transactions should be avoided when a large number of rows and replication are involved.
- In general, transactions affecting a large number of rows locally can work but can cause locking problems.
- You should use a transaction only if you need an option to rollback your changes.
- You do not want to wrap single query inside a transaction — it’s useless overhead and locking potential
- Not every part of MediaWiki is extremely broken. If you’re reusing/extending core functionality take a deeper look at how it’s implemented.
- Every InnoDB table should have a UNIQUE / PRIMARY index in order for replicated transactions to perform properly.
- Properly constructed primary key (or a unique one acting as a primary key) can save storage space as InnoDB keeps row data in the index structure.
This post was first published on our internal blog. Written with help from Piotr Drozdowski.
