Announcement

Announcement Module
Collapse
No announcement yet.

percona 5.6.14 queries stack up when free buffer count is low

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • percona 5.6.14 queries stack up when free buffer count is low

    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

  • #2
    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?

    Comment

    Working...
    X