Not the answer you need?
Register and ask your own question!

percona 5.6.14 queries stack up when free buffer count is low

dodizzledodizzle EntrantCurrent User Role Beginner
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

Comments

  • przemekprzemek Percona Support Engineer Percona Staff Role
    Can you confirm the same problem does not happen when on MySQL 5.6.14 versus Percona Server 5.6.14?
    Do you use exactly the same configuration and hardware for both MySQL and Percona?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.