MariaDB 10.2性能调整-锁定等待超时问题

Question

我有4个实例数据库集群(其中2个是mariadb 10.2服务器，2个是garbd服务器)我们在数据库中使用更新繁重的查询，处理一个巨大的表，其中一些表有超过2000万条记录。我们也有很多使用数据库的java应用程序，这些线程主要是在表中进行“更新”过程。在peak_time中，我们使用了大约200线程。

我们的问题是我们的应用程序中有“死锁”。我相信我可以通过在数据库配置中进行一些性能调优来解决这个问题。我的最后一个配置如下；

DB1: x.x

DB2: Y.Y

Garbd-1: W.W

Garbd-2: Z.Z

DB-1 : my.cnf

[client-server]

socket=/app/mysql/mysql.sock

port=3306

[mysqld]

user=mysql

port=3306

bind-address=0.0.0.0

socket=/app/mysql/mysql.sock

skip-name-resolve

lower_case_table_names=1

server-id=1

event-scheduler=ON

datadir=/app/mysql

pid-file=/app/mysql/db1.pid

log-error=/app/mysql/db1.err

log_bin_trust_function_creators=1

query_cache_type=0 

query_cache_size=0

#query_cache_limit = 4M

default_table_type = InnoDB

table_open_cache = 4096

open_files_limit = 8192

max_connections = 800

wait_timeout=100

interactive_timeout=100

#net_read_timeout=3600

#net_write_timeout=3600

max_heap_table_size=64M

tmp_table_size=64M

thread_cache_size=256

sort_buffer_size = 2M

join_buffer_size = 256K

read_buffer_size=128K

read_rnd_buffer_size = 256M

transaction-isolation=READ-COMMITTED

log_warnings

slow_query_log

long_query_time=5

# Physical RAM is 32G (8-core)

innodb_buffer_pool_size =24G

innodb_buffer_pool_instances=6

key_buffer_size=512M

nnodb_write_io_threads=8

innodb_read_io_threads=8

innodb_thread_concurrency = 16

#innodb_data_file_path = ibdata1:1G:autoextend

#innodb_autoextend_increment=128M

#innodb_file_per_table

innodb_flush_log_at_trx_commit=2

#sync_binlog=1

innodb_log_buffer_size = 16M

innodb_log_file_size = 2G

innodb_max_dirty_pages_pct = 80

innodb_flush_method=O_DIRECT

innodb_lock_wait_timeout = 120

binlog_format=ROW

innodb_autoinc_lock_mode=2

[mysqldump]

quick

max_allowed_packet = 512M

[mysql]
no-auto-rehash

[mysqld_safe]

datadir=/app/mysql

pid-file=/app/mysql/db1.pid

log-error=/app/mysql/db1.err

[mariadb-10.2]

wsrep_on=ON

wsrep_provider=/usr/lib64/galera/libgalera_smm.so

wsrep_provider_options="gcache.size=10G;gmcast.listen_addr=tcp://0.0.0.0:4567;socket.checksum=2"

wsrep_cluster_address="gcomm://10.92.221.215,10.92.221.216,10.92.223.215,10.92.223.216"

wsrep_cluster_name='galera_cluster'

wsrep_node_address='10.92.221.215'

wsrep_node_name='ivrocsdbp00'

wsrep_sst_method=rsync

wsrep_sst_auth=sst_user:dbpass

这些也是在第二个服务器中配置的，使用server-id=2和db2.pid，db2.err更改。

这些改变能解决我的问题吗？

我想知道这些参数是否配置良好？

我们在服务器中有32 GB的RAM (8核)。

谢谢你的帮助。

Answer 1

我猜nnodb_write_io_threads是个拼写错误，因为mysqld不会以它开头。

我会从innodb_thread_concurrency = 0开始，解除对线程并发限制的限制。

你的更新是完全索引的吗，最好是在哪里使用PK？

有没有一个很好的理由让你把innodb_file_per_table注释掉？

也许有必要验证一下您的table_open_cache是否太小(查看进程列表，看看是否经常看到处于“打开|关闭表”状态的查询。

这些服务器是虚拟机吗?它们是否在繁忙/超额预订的主机上？我在时钟不稳定和不可预测的情况下看到了非常有趣的虚假死锁，例如在严重超额预订的虚拟机上。有一些有趣的线程竞速发生在这些条件下，似乎完全无法在裸机上重现。在云环境中增加实例大小通常可以解决问题，因为更大的实例往往位于超额预订较少的主机上。

Answer 2

感谢您的回复。

正如您所提到的，我已经更正了我的拼写错误并将我的配置文件更改为innodb_thread_concurrency =0。

我尝试配置性能非常低的消耗参数，并且我已经了解到innodb_file_per_table参数可能会影响内存使用率，因此我决定将其注释掉。实际上，我不清楚这些参数是用来做什么的。

让我告诉你我在重启两个数据库服务器后的一些观察结果。

首先，我停止了所有连接到db-1的应用程序(实际上我们通过DB1建立了所有的db连接)，然后重启了两个DB-server。首先，让我澄清一下，当我启动应用程序时，没有任何流量。

在我启动了我们使用的所有appcaliton后，我意识到我在INFORMATION_SCHEMA中看到了大约120个进程，这些进程是由我们的用户在PROCESSLIST中创建的。此外，我还知道其中一些连接除了使用connnection_pool之外什么也做不了。

我设置了100秒的wait_timeout，在100秒后，PROCESSLIST降到了"27“，threads_cache变成了92。

我还知道这27个连接被一些应用程序使用，这些应用程序以特定的时间间隔运行一些检查查询。(10秒、4秒、2秒)，所以当它们运行查询时，它们的connection_time返回0并再次计数，因为它们从未关闭。

我相信在peak_time中，我们将需要thread_cache_size，所以将这个参数配置为256对我们的系统是有好处的。

​

​

我知道现在很难理解，但你能检查一下我的innodb引擎状态并给我一些建议吗，因为我不太擅长数据库管理系统的概念。

=====================================
2020-05-08 01:10:28 0x7fd8cc55c700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 7 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 4897 srv_active, 0 srv_shutdown, 371 srv_idle
srv_master_thread log flush and writes: 5266
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 34869
OS WAIT ARRAY INFO: signal count 121009
RW-shared spins 0, rounds 214984, OS waits 15858
RW-excl spins 0, rounds 1540716, OS waits 11401
RW-sx spins 52309, rounds 782230, OS waits 3893
Spin rounds per wait: 214984.00 RW-shared, 1540716.00 RW-excl, 14.95 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 211830460
Purge done for trx's n:o < 211830450 undo n:o < 0 state: running but idle
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422083415170480, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415166264, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415162048, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415157832, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415153616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415149400, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415145184, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415140968, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415136752, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415132536, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415128320, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415124104, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415119888, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415115672, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415111456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415107240, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415103024, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415098808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415094592, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415090376, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415086160, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415081944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415077728, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415073512, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415069296, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415065080, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415060864, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415056648, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422083415052432, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
363895 OS file reads, 105906 OS file writes, 18722 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 6.14 writes/s, 2.43 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 13430, seg size 13432, 2410 merges
merged operations:
 insert 72630, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 6374293, node heap has 108 buffer(s)
Hash table size 6374293, node heap has 64 buffer(s)
Hash table size 6374293, node heap has 64 buffer(s)
Hash table size 6374293, node heap has 18 buffer(s)
Hash table size 6374293, node heap has 10 buffer(s)
Hash table size 6374293, node heap has 72 buffer(s)
Hash table size 6374293, node heap has 4 buffer(s)
Hash table size 6374293, node heap has 634 buffer(s)
3473.22 hash searches/s, 553.49 non-hash searches/s
---
LOG
---
Log sequence number 129572121784
Log flushed up to   129572121784
Pages flushed up to 129572121784
Last checkpoint at  129572120321
0 pending log flushes, 0 pending chkp writes
3127 log i/o's done, 0.71 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 26398949376
Dictionary memory allocated 278784
Buffer pool size   1572672
Free buffers       1200805
Database pages     370893
Old database pages 137029
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 80.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2349, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 363786, created 7107, written 94745
0.00 reads/s, 0.00 creates/s, 4.43 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 370893, unzip_LRU len: 0
I/O sum[0]:cur[24], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   262112
Free buffers       198770
Database pages     63178
Old database pages 23341
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 80.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 372, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 62294, created 884, written 22682
0.00 reads/s, 0.00 creates/s, 2.14 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 63178, unzip_LRU len: 0
I/O sum[0]:cur[4], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   262112
Free buffers       198593
Database pages     63353
Old database pages 23406
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 80.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 365, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 62005, created 1348, written 17745
0.00 reads/s, 0.00 creates/s, 0.86 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 63353, unzip_LRU len: 0
I/O sum[0]:cur[4], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   262112
Free buffers       202987
Database pages     58961
Old database pages 21784
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 80.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 388, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 58042, created 919, written 15919
0.00 reads/s, 0.00 creates/s, 1.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 58961, unzip_LRU len: 0
I/O sum[0]:cur[4], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   262112
Free buffers       199895
Database pages     62058
Old database pages 22928
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 80.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 439, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 60791, created 1267, written 12163
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 62058, unzip_LRU len: 0
I/O sum[0]:cur[4], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   262112
Free buffers       201239
Database pages     60712
Old database pages 22431
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 80.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 382, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 59190, created 1522, written 12539
0.00 reads/s, 0.00 creates/s, 0.43 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 60712, unzip_LRU len: 0
I/O sum[0]:cur[4], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   262112
Free buffers       199321
Database pages     62631
Old database pages 23139
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 80.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 403, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 61464, created 1167, written 13697
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 62631, unzip_LRU len: 0
I/O sum[0]:cur[4], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=31986, Main thread ID=140569828304640, state: sleeping
Number of rows inserted 276741, updated 1143, deleted 276686, read 179703655
0.00 inserts/s, 0.29 updates/s, 0.00 deletes/s, 109971.15 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

我也会在1-2天后分享线程状态和INNODB引擎状态。我希望我们不会遇到任何锁定等待超时的问题。

祝您今天愉快。

Answer 3

服务器已经运行了超过48小时，让我在DB-1中分享调优入门的结果。

        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 10.2.27-MariaDB-log x86_64

Uptime = 2 days 3 hrs 54 min 46 sec
Avg. qps = 26
Total Questions = 5022294
Threads Connected = 17

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/10.2/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 5.000000 sec.
You have 10 out of 5022308 that take longer than 5.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/10.2/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 256
Current threads_cached = 103
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 800
Current threads_connected = 17
Historic max_used_connections = 120
The number of used connections is 15% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 4.29 G
Current InnoDB data space = 4.72 G
Current InnoDB buffer pool free = 69 %
Current innodb_buffer_pool_size = 24.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 54.82 G
Configured Max Per-thread Buffers : 202.07 G
Configured Max Global Buffers : 24.51 G
Configured Max Memory Limit : 226.59 G
Physical Memory : 31.26 G

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 638 K
Current key_buffer_size = 512 M
Key cache miss rate is 1 : 144181
Key buffer free ratio = 81 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 260.00 K
You have had 373030 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 9031 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 4096 tables
Current table_definition_cache = 400 tables
You have a total of 186 tables
You have 428 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 64 M
Current tmp_table_size = 64 M
Of 490764 temp tables, 0% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 34 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 383
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=ALWAYS'.

我应该减少my.cnf中的一些参数吗？有什么建议吗？

谢谢