We have a large table which we are attempting to migrate to its own hardware.
The table only has 4 columns but has >3.5 billion rows.
The table performs fine running on MySQL 5.6.12 and in a shared environment with several other databases.
The problem we are seeing is that when there are plenty of free buffers the queries performs fine, zero slow queries at all.
However once the buffer is full or close to full( 1k free buffers) all of the queries become slow and many take several minutes for a simple select on a primary key.
We are running on the same hardware for both MySQL 5.6.12 and Percona 5.6.14
256GB RAM with a inodb_buffer_pool of 200GB
and 1.2 TB FusionIO drives.
Another confusing part is that when percona does have it buffer pool become full there is little to no disk IO.
Create Table: CREATE TABLE
user_id int(11) unsigned NOT NULL,
entry_id int(11) unsigned NOT NULL,
via_user_id int(11) unsigned DEFAULT NULL,
created_timestamp int(11) unsigned NOT NULL,
PRIMARY KEY (
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED