Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-4809

Open table cache not used during replication when tables are using TokuDB engine

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Incomplete
    • Affects Version/s: 5.7.22-22
    • Fix Version/s: None
    • Component/s: TokuDB
    • Labels:
    • Environment:

      Ubuntu Server 14.04

      Description

      Scenario: master - slave replication
      Master: Percona MySQL 5.6.28-76.1
      Slave: Percona MySQL 5.7.22-22

      Complete server stores ~50000 tables across multiple databases, with over 98% of the tables being stored in one database. The storage engine for the tables from this database was changed from InnoDB to TokuDB. Prior to the change, for the given work volume (1 insert per minute in every table), the replication was able to keep up and when stopped, it was able to catch up at a rate of 4x real time. After the change, the replication fails behind slowly (replicates at a speed of 95-98% of real time)

      Executing "show full processlist"  shows the replication worker in "Opening tables" state.
      Executing "show global status like 'opened_tables'" shows that a large number of tables are opened continuously on slave server, at a rate of over 100x master. Slave has no load other than replication.
      Executing "show open tables from databaseX" shows already all existing tables as being open and cached.

      Database settings:
      Master (Percona MySQL 5.6.28-76.1):
      binlog-format = mixed
      sync_binlog = 1
      expire_logs_days = 30
      max_binlog_size = 500M
      binlog-ignore-db = information_schema
      binlog-ignore-db = performance_schema
      binlog-ignore-db = mysql

      Slave:

      skip-name-resolve
      max_connections        = 10000
      query-cache-type    = 0
      table_open_cache    = 200000
      table_open_cache_instances    = 20
      table_definition_cache    = 200000
      thread_stack        = 512K
      thread_cache_size       = 1000
      max_allowed_packet    = 128M
      character-set-server    = utf8
      collation-server    = utf8_unicode_ci
      open_files_limit    = 500000
      tmp-table-size        = 512M
      sql_mode        = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
      metadata_locks_hash_instances    = 256

      relay-log        = mysql-relay-bin
      log-slave-updates    = 1
      replicate-ignore-db    = information_schema
      replicate-ignore-db    = performance_schema
      replicate-ignore-db    = mysql
      slave-parallel-workers    = 24
      skip-slave-start

      innodb_file_per_table    = true
      innodb_buffer_pool_size    = 32768M
      innodb_buffer_pool_instances    = 8
      innodb_flush_log_at_trx_commit    = 1
      innodb_read_io_threads    = 24
      innodb_write_io_threads    = 24
      innodb_io_capacity    = 2000
      innodb_log_file_size    = 256M
      innodb_numa_interleave    = 1
      innodb_online_alter_log_max_size    = 1024M
      innodb-sort-buffer-size    = 64M

      tokudb_row_format    = TOKUDB_ZLIB
      tokudb_cache_size    = 98384M
      tokudb_lock_timeout    = 30000
      tokudb_checkpointing_period    = 3600
      tokudb_fs_reserve_percent    = 1

      max_heap_table_size     = 512M

        Attachments

          Activity

            People

            Assignee:
            george.lorch George Lorch
            Reporter:
            sergiuhlihor Sergiu Hlihor
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Smart Checklist