mysql 5.6单实例下有两万多个库导致启动和备份都很慢,有加速的方法吗?


Server version: 5.6.29-76.2-log Percona Server
数据库统计有24662个库,总数据文件43G
主机4C8G,buffer pool分了4G
这个数据库实例当前重启一下需要将近一个小时,xtrabakcup备份两个小时都没有输出

```
[mysql]
replicate-wild-ignore-table = mysql.%
replicate-wild-ignore-table = information_schema.%
replicate-same-server-id = 0
relay_log_recovery = 1
slave_parallel_workers = 4
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 2048
thread-cache-size = 50
open-files-limit = 655350
table-definition-cache = 4096
table-open-cache = 10240

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 4G

# LOGGING #
log-error = /data/mysql/log/mysql-error.log
log-queries-not-using-indexes = 0
slow-query-log = 1
slow-query-log-file = /data/mysql/log/mysql-slow.log
long_query_time=3
#innodb_force_recovery = 1
#set character
character-set-server=utf8mb4
character-set-client-handshake = FALSE
init_connect= 'SET NAMES utf8mb4'
```

启动时错误日志打印:
```
2018-05-18 01:51:46 0 [Note] /usr/sbin/mysqld (mysqld 5.6.29-76.2-log) starting as process 19018 ...
2018-05-18 01:51:46 19018 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2018-05-18 01:51:46 19018 [Note] Plugin 'FEDERATED' is disabled.
2018-05-18 01:51:46 19018 [Warning] The option innodb (skip-innodb) is deprecated and will be removed in a future release
2018-05-18 01:51:46 19018 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-05-18 01:51:46 19018 [Note] InnoDB: The InnoDB memory heap is disabled
2018-05-18 01:51:46 19018 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-05-18 01:51:46 19018 [Note] InnoDB: Memory barrier is not used
2018-05-18 01:51:46 19018 [Note] InnoDB: Compressed tables use zlib 1.2.8
2018-05-18 01:51:46 19018 [Note] InnoDB: Using Linux native AIO
2018-05-18 01:51:46 19018 [Note] InnoDB: Using CPU crc32 instructions
2018-05-18 01:51:46 19018 [Note] InnoDB: Initializing buffer pool, size = 4.0G
2018-05-18 01:51:47 19018 [Note] InnoDB: Completed initialization of buffer pool
2018-05-18 01:51:47 19018 [Note] InnoDB: Highest supported file format is Barracuda.
2018-05-18 01:51:47 19018 [Note] InnoDB: Log scan progressed past the checkpoint lsn 92551859067
2018-05-18 01:51:47 19018 [Note] InnoDB: Database was not shutdown normally!
2018-05-18 01:51:47 19018 [Note] InnoDB: Starting crash recovery.
2018-05-18 01:51:47 19018 [Note] InnoDB: Reading tablespace information from the .ibd files...
2018-05-18 02:39:16 19018 [Note] InnoDB: Restoring possible half-written data pages
2018-05-18 02:39:16 19018 [Note] InnoDB: from the doublewrite buffer...
2018-05-18 02:39:19 19018 [Note] InnoDB: 128 rollback segment(s) are active.
2018-05-18 02:39:19 19018 [Note] InnoDB: Waiting for purge to start
2018-05-18 02:39:19 19018 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.29-76.2 started; log sequence number 92551859077
2018-05-18 02:39:19 19018 [Note] Recovering after a crash using /data/mysql/log/mysql-bin
2018-05-18 02:39:19 19018 [Note] Starting crash recovery...
2018-05-18 02:39:19 19018 [Note] Crash recovery finished.
2018-05-18 02:39:19 19018 [Note] RSA private key file not found: /data/mysql/data//private_key.pem. Some authentication plugins will not work.
2018-05-18 02:39:19 19018 [Note] RSA public key file not found: /data/mysql/data//public_key.pem. Some authentication plugins will not work.
2018-05-18 02:39:19 19018 [Note] Server hostname (bind-address): '*'; port: 3306
2018-05-18 02:39:19 19018 [Note] IPv6 is available.
2018-05-18 02:39:19 19018 [Note] - '::' resolves to '::';
2018-05-18 02:39:19 19018 [Note] Server socket created on IP: '::'.
2018-05-18 02:39:19 19018 [Warning] 'proxies_priv' entry '@ root@i-x7uz69dp' ignored in --skip-name-resolve mode.
2018-05-18 02:39:20 19018 [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=mysqld-relay-bin' to avoid this problem.
2018-05-18 02:39:20 19018 [Warning] Recovery from master pos 573792050 and file mysql-bin.000032. Previous relay log pos and relay log file had been set to 283, ./mysqld-relay-bin.000088 respectively.
2018-05-18 02:39:20 19018 [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.
2018-05-18 02:39:20 19018 [Note] Slave I/O thread: connected to master 'repl@172.20.200.136:3306',replication started in log 'mysql-bin.000032' at position 573792050
2018-05-18 02:39:20 19018 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2018-05-18 02:39:20 19018 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000032' at position 573792050, relay log './mysqld-relay-bin.000089' position: 4
2018-05-18 02:39:21 19018 [Note] Event Scheduler: Loaded 0 events
2018-05-18 02:39:21 19018 [Note] /usr/sbin/mysqld: ready for connections.
```

启动时strace 打印进程信息:
```
close(11) = 0
open("./APP_KEFU_133014/SL_menu.isl", O_RDWR) = -1 ENOENT (No such file or directory)
open("./APP_KEFU_133014/SL_menu.ibd", O_RDONLY) = 11
fcntl(11, F_SETFL, O_RDONLY|O_DIRECT) = 0
getcwd("/data/mysql/data", 4096) = 17
lstat("/data/mysql/data/APP_KEFU_133014", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
close(11) = 0
open("./APP_KEFU_133014/SL_record_details.isl", O_RDWR) = -1 ENOENT (No such file or directory)
open("./APP_KEFU_133014/SL_record_details.ibd", O_RDONLY) = 11
fcntl(11, F_SETFL, O_RDONLY|O_DIRECT) = 0
getcwd("/data/mysql/data", 4096) = 17
lstat("/data/mysql/data/APP_KEFU_133014", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
close(11) = 0
open("./APP_KEFU_133014/SL_record_details_offline.isl", O_RDWR) = -1 ENOENT (No such file or directory)
open("./APP_KEFU_133014/SL_record_details_offline.ibd", O_RDONLY) = 11
fcntl(11, F_SETFL, O_RDONLY|O_DIRECT) = 0
getcwd("/data/mysql/data", 4096) = 17
lstat("/data/mysql/data/APP_KEFU_133014", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
close(11) = 0
open("./APP_KEFU_133014/SL_service_person.isl", O_RDWR) = -1 ENOENT (No such file or directory)
open("./APP_KEFU_133014/SL_service_person.ibd", O_RDONLY) = 11
fcntl(11, F_SETFL, O_RDONLY|O_DIRECT) = 0
getcwd("/data/mysql/data", 4096) = 17
lstat("/data/mysql/data/APP_KEFU_133014", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
close(11) = 0
open("./APP_KEFU_133014/SL_service_record.isl", O_RDWR) = -1 ENOENT (No such file or directory)
open("./APP_KEFU_133014/SL_service_record.ibd", O_RDONLY) = 11
fcntl(11, F_SETFL, O_RDONLY|O_DIRECT) = 0
getcwd("/data/mysql/data", 4096) = 17
lstat("/data/mysql/data/APP_KEFU_133014", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
close(11) = 0
open("./APP_KEFU_133014/SL_user.isl", O_RDWR) = -1 ENOENT (No such file or directory)
open("./APP_KEFU_133014/SL_user.ibd", O_RDONLY) = 11
close(11) = 0
open("./APP_KEFU_138578/SL_group.isl", O_RDWR) = -1 ENOENT (No such file or directory)
open("./APP_KEFU_138578/SL_group.ibd", O_RDONLY) = 11
fcntl(11, F_SETFL, O_RDONLY|O_DIRECT) = 0
getcwd("/data/mysql/data", 4096) = 17
lstat("/data/mysql/data/APP_KEFU_138578", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
close(11) = 0
open("./APP_KEFU_138578/SL_group_ref_service_person.isl", O_RDWR) = -1 ENOENT (No such file or directory)
open("./APP_KEFU_138578/SL_group_ref_service_person.ibd", O_RDONLY) = 11
fcntl(11, F_SETFL, O_RDONLY|O_DIRECT) = 0
getcwd("/data/mysql/data", 4096) = 17
```

xtrabackup 输出
```
180518 15:43:12 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.6.29-76.2-log
xtrabackup version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysql/data
xtrabackup: open files limit requested 655535, set to 655535
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 268435456
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
```
```
| 8286 | root        | localhost            | NULL | Query       |  7551 | checking permissions                                                        | SELECT
  CONCAT(table_schema, '/', table_name), engine
FROM information_schema.tables
WHERE engine N |         0 |             0 |
| 8473 | root        | localhost            | NULL | Query       |  6427 | checking permissions                                                        | SELECT
  CONCAT(table_schema, '/', table_name), engine
FROM information_schema.tables
WHERE engine N |         0 |             0 |
```
 
```
mysql> show engine innodb status \G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2018-05-18 17:35:35 7f2a1987d700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 33 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1080 srv_active, 0 srv_shutdown, 52666 srv_idle
srv_master_thread log flush and writes: 53742
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2303
OS WAIT ARRAY INFO: signal count 2287
Mutex spin waits 2567, rounds 42616, OS waits 1340
RW-shared spins 962, rounds 28860, OS waits 962
RW-excl spins 1, rounds 32, OS waits 1
Spin rounds per wait: 16.60 mutex, 30.00 RW-shared, 32.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 514677466
Purge done for trx's n:o < 514677462 undo n:o < 0 state: running but idle
History list length 2347
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 9647, OS thread handle 0x7f2a1987d700, query id 26997 localhost root init
show engine innodb status
---TRANSACTION 0, not started
MySQL thread id 8473, OS thread handle 0x7f29b37e6700, query id 23644 localhost root checking permissions
SELECT
  CONCAT(table_schema, '/', table_name), engine
FROM information_schema.tables
WHERE engine NOT IN (
  'MyISAM', 'InnoDB', 'CSV', 'MRG_MYISAM'
)
AND table_schema NOT IN (
  'performance_schema', 'information_schema',  'mysql'
)
---TRANSACTION 0, not started
MySQL thread id 8286, OS thread handle 0x7f29b37a5700, query id 23065 localhost root checking permissions
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size        32767
Buffer pool size, bytes 536854528
Free buffers            32244
Database pages          520
Old database pages      211
Modified db pages       0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 517, created 3, written 582
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 520, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
0 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
0 out of 1000 descriptors used
Main thread process no. 19018, id 139811717371648, state: sleeping
Number of rows inserted 1466, updated 4, deleted 163, read 13105
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)
 
```
已邀请:

yejr

赞同来自: zhagyilig

主要问题在于:数据库统计有24662个库,这个太夸张了, 作死的节奏。
解决方案:
1、尝试设置 table_open_cache_instances = 64,甚至更大,看看会不会好些。
2、把这些统计库进行合并,一方面减少 database 数量,另一方面,也要减少 table 的数量。
祝好运,哈哈

要回复问题请先登录注册