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
80 cores
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.
The table:
Table: hearts
Create Table: CREATE TABLE hearts (
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 (user_id,entry_id),
KEY index_hearts_on_created_timestamp (created_timestamp),
KEY index_on_user_id_and_created_timestamp (user_id,created_timestamp),
KEY index_hearts_on_entry_id_and_via_user_id (entry_id,via_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED