使用过多CPU的MySQL TokuDB引擎

Question

我已经将数据库的表从InnoDB转换为TokuDB，并且我注意到使用TokuDB时，读取占用了太多的CPU。为什么会这样呢？

更具体地说，包含TokuDB表的服务器是包含InnoDB的服务器的从属服务器。从服务器只使用普通的percona服务器，而不是PXC。但是从机似乎使用了太多的CPU，我不知道为什么？

下面是我的my.cnf配置：

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
thp-setting=never
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
flush_caches
numa_interleave
core-file-size = unlimited
open_files_limit = 1024

[mysqld]
back_log = 65535
bind-address = 0.0.0.0
binlog_format = ROW
character_set_server = utf8
collation_server = utf8_general_ci
core_file
basedir = /usr
datadir = /var/lib/mysql
#default_storage_engine = InnoDB
enforce-gtid-consistency = 1
expand_fast_index_creation = 1
expire_logs_days = 7
gtid_mode = ON
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_instances = 1
innodb_buffer_pool_populate = 1
innodb_buffer_pool_size = 512M
innodb_data_file_path = ibdata1:64M;ibdata2:64M:autoextend
innodb_file_format = Barracuda
innodb_file_per_table
innodb_force_recovery   = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1600
innodb_large_prefix
innodb_locks_unsafe_for_binlog = 1
innodb_log_file_size = 64M
innodb_print_all_deadlocks = 1
innodb_read_io_threads = 64
innodb_stats_on_metadata = FALSE
innodb_support_xa = FALSE
innodb_write_io_threads = 64
lc-messages-dir = /usr/share/mysql
log-bin = mysqld-bin
log-queries-not-using-indexes
log-slave-updates
long_query_time = 1
master_info_repository = TABLE
max_allowed_packet = 64M
max_connect_errors = 4294967295
max_connections = 2500
max_user_connections = 2550
min_examined_row_limit = 1000
open_files_limit = 1024
port = 3306
relay_log_info_repository = TABLE
relay-log-recovery = TRUE
relay-log-recovery = 1
skip-external-locking
skip-name-resolve
slave_parallel_workers = 8
slow_query_log = 1
slow_query_log_timestamp_always = 1
socket = /var/run/mysqld/mysqld.sock
table_open_cache = 4096
thread_cache = 1024
tmpdir = /srv/tmp
transaction_isolation = REPEATABLE-READ
updatable_views_with_limit = 0
user = mysql
wait_timeout = 60

server-id = 2
# TokuDB fine tuning
default_storage_engine = TokuDB
tokudb_analyze_time = 5
#tokudb_cache_size = 6G
tokudb_directio = 1
tokudb_commit_sync = 0
tokudb_fsync_log_period = 1000
tokudb_load_save_space =1
tokudb_alter_print_error=0
tokudb_block_size = 4MB
tokudb_bulk_fetch = 1
tokudb_disable_slow_alter = 1
tokudb_last_lock_timeout = empty
tokudb_row_format = tokudb_quicklz
#tokudb_data_dir = /var/lib/tokudb


[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M
!includedir /etc/mysql/conf.d/

当tokudb_cache_size最初设置为总内存的80%时，我们的监控系统xymon会报告以下复制消息。

2016-02-25 16:42:04 9604 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=db-kdb-slave-6-relay-bin' to avoid this problem.
2016-02-25 16:42:05 9604 [Warning] Recovery from master pos 552554502 and file mysqld-bin.001163. Previous relay log pos and relay log file had been set to 552554714, ./db-kdb-slave-6-relay-bin.002933 respectively.
2016-02-25 16:42:05 9604 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

-有关运行InnoDB和部分PXC的主服务器的更多信息

## Results from top

top - 10:05:12 up 14 days,  7:56,  2 users,  load average: 2.16, 2.31, 2.39
Tasks: 413 total,   1 running, 412 sleeping,   0 stopped,   0 zombie
%Cpu(s):  8.9 us,  0.6 sy,  0.0 ni, 89.9 id,  0.3 wa,  0.0 hi,  0.2 si,  0.0 st
KiB Mem:  65704012 total, 63553216 used,  2150796 free,   169832 buffers
KiB Swap:   975868 total,   809892 used,   165976 free. 16304268 cached Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 2485 mysql     20   0 60.146g 0.045t 2.612g S 314.9 73.3  27762:43 mysqld


## disk info
george@db-erp-3:~$ df -h
Filesystem       Size  Used Avail Use% Mounted on
udev              32G  8.0K   32G   1% /dev
tmpfs            6.3G  1.2M  6.3G   1% /run
/dev/sda2        274G  2.1G  258G   1% /
none             4.0K     0  4.0K   0% /sys/fs/cgroup
none             5.0M     0  5.0M   0% /run/lock
none              32G     0   32G   0% /run/shm
none             100M     0  100M   0% /run/user
/dev/nvme0n1p1   1.1T  542G  503G  52% /srv
na1:/vol/yphome  4.5T  3.7T  875G  82% /net/account

## Memory info
george@db-erp-3:~$ free -g
             total       used       free     shared    buffers     cached
Mem:            62         60          2          0          0         15
-/+ buffers/cache:         44         17
Swap:            0          0          0
george@db-erp-3:~$


## Database info
+--------------------+----------------------+
| Data Base Name     | Data Base Size in MB |
+--------------------+----------------------+
| information_schema |           0.00976563 |
| dberp              |      347143.32031250 |
| mysql              |           2.11562061 |
| performance_schema |           0.00000000 |
+--------------------+----------------------+
4 rows in set (0.13 sec)

+--------------------+----------------------+------------------+
| Data Base Name     | Data Base Size in MB | Free Space in MB |
+--------------------+----------------------+------------------+
| information_schema |           0.00976563 |       0.00000000 |
| dberp              |      347143.32031250 |    6270.00000000 |
| mysql              |           2.11562061 |       4.00199127 |
| performance_schema |           0.00000000 |       0.00000000 |
+--------------------+----------------------+------------------+
4 rows in set (0.03 sec)

Answer 1

读操作的CPU会更高，因为TokuDB数据需要解压缩才能使用。此外，如果这个从进程正在处理来自主进程的任何活动，那么它还会对插入/更新/删除活动进行压缩。

有几个想法。1.减少tokudb_block_size的值。虽然4MB对于压缩来说是很棒的，但它意味着您的点查询需要解压缩比必须解压缩多得多的数据。尝试使用256KB，看看CPU和性能是如何变化的。您可能必须重新构建您的从属程序才能轻松完成此任务(我现在离在TokuDB工作已经有一年多了)。2.看看你的tokudb_cache_size。它默认为RAM的50%，但如果此服务器上没有其他内容，您应该将其设置为75%到80%之间的某个位置。这将意味着更少的读取和解压缩，因为更多的数据将在您的缓存中。