MySQL (Percona)：多个同时长时间运行的连接

Question

我在云中运行一个分布式应用程序，其中客户端保持与MySQL (Percona)服务器的多个长期运行连接。

在更新到最新的Percona版本之后，同时连接的客户端的最大数量已经显著下降。在过去，在之前的Percona版本中，它被成功地用于8K连接，现在它很难达到3K以上。

所谓“挣扎”，我的意思是，当连接瓶颈已经到达时，连接到MySQL甚至在命令行中也会超时。当我设法连接时，show processlist不会显示任何挂起的查询或锁。因此，已经建立起来的联系运作得很好。用于监视项目的PHP webapp应用程序的Apache webapp服务器也无限期挂起。

这是我使用的my.cnf，由各种指南拼凑而成。

[mysqld]
open_files_limit               = 16384
table_open_cache               = 16384
character_set_server           = utf8mb4
max_connections                = 16384
expire_logs_days               = 10
max_binlog_size                = 100M
innodb_open_files              = 16384
innodb_file_per_table          = 1
innodb_read_io_threads         = 4
innodb_write_io_threads        = 4
innodb_thread_concurrency      = 0
innodb_log_file_size           = 128M
innodb_open_files              = 4000
innodb_flush_method            = O_DIRECT
innodb_buffer_pool_instances   = 1
thread_pool_size               = 16
local_infile                   = 1
skip-name-resolve
thread_cache_size              = 16384
thread_handling                = pool-of-threads
innodb_buffer_pool_size        = 512M
innodb_buffer_pool_instances   = 1
innodb_log_buffer_size         = 64M

我正在运行的Percona版本：

mysqld --version
Ver 8.0.15-6 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release '6', Revision '63abd08')

OS：Ubuntu 18.04.2 LTS

硬件：

• CPU：Intel(R) Xeon(R) Platinum 8175M CPU @ 2.50GHz
• 拉姆：32G

我正在运行的查询都是基于唯一的索引(没有联接)，表都是InnoDB。在更新到Percona 8之前，相同的配置运行良好。

升级后必须删除的my.cnf设置不再受支持，它们是：

• innodb_locks_unsafe_for_binlog = 0
• query_cache_size = 0
• query_cache_type = 0

我试过的事情：

• 我运行mysqltuner.pl，但没有从它得到任何相关的建议。
• ulimit -n和ulimit -s显示的值明显高于我所使用的值(特别是1048576和16384 )。

还有其他建议吗？

Answer 1

在Connections的5小时内，Uptime只有92次，一次不到4次(Max_used_connections)。所以我不明白你关于3K连接的问题。

观测：

• 版本: 8.0.15-6
• 32 GB内存
• 正常运行时间= 04:54:20；一些全局状态值可能还没有意义。
• 你确定这是一场全球演出吗？
• 您不在Windows上运行。
• 运行64位版本
• 您似乎正在完全(或大部分)运行InnoDB。

更重要的问题：

buffer_pool上极高的磁盘活动。因为您有32 to，并且假设它主要用于MySQL，所以将innodb_buffer_pool_size更改为22G。

为什么这么多的检查，设置，删除，显示列语句？

你似乎有一些很大的疑问。打开慢吞吞的木头，抓住最坏的。例如，删除可能缺少一个有用的索引？

表缓存不是很有效，将table_open_cache增加到2000。

Max_connections = 15574相当高。尤其是最常用的只有4，把它降到100。

详细信息和其他观察：

( Innodb_buffer_pool_reads ) = 28,825,842 / 17660 = 1632 /sec -- InnoDB buffer_pool I/O读取速率--检查innodb_buffer_pool_size

( Innodb_buffer_pool_pages_flushed ) = 61,160,824 / 17660 = 3463 /sec --写(冲) --检查innodb_buffer_pool_size

( innodb_buffer_pool_size / _ram ) = 512M / 32768M = 1.6% --用于InnoDB buffer_pool的内存的百分比

( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 979 / (8530 + 979) = 10.3% -- table_open_cache的有效性。-增加table_open_cache并检查table_open_cache_instances。

( innodb_lru_scan_depth ) = 1,024 -- "InnoDB: page_cleaner: 1000的预定循环.“可以通过降低lru_scan_depth来固定

( Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests ) = 28,825,842 / 997216830 = 2.9% --读取必须访问磁盘的请求--如果有足够的内存，则增加innodb_buffer_pool_size。

( Innodb_pages_read / Innodb_buffer_pool_read_requests ) = 50,177,127 / 997216830 = 5.0% --读取必须访问磁盘的请求--如果有足够的内存，则增加innodb_buffer_pool_size。

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((28825842 + 61160824) ) / 17660 = 5095 /sec -- InnoDB I/O --增加innodb_buffer_pool_size？

( innodb_log_buffer_size / innodb_log_file_size ) = 64M / 48M = 133.3% --缓冲区在内存中，文件在磁盘上.-- buffer_size应该更小，file_size应该更大。

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 248,832 / (17660 / 3600) / 2 / 48M = 0.0005 -比率-(见记录)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 17,660 / 60 * 48M / 248832 = 59,535 -从5.6.8开始的InnoDB日志轮转之间的分钟时间，这可以动态更改；确保也要更改my.cnf。-- (轮流60分钟的建议有点武断)。调整innodb_log_file_size。(AWS中无法更改)

( Innodb_rows_deleted / Innodb_rows_inserted ) = 201 / 0 = INF --搅动--“不要排队，只管去做。”(如果MySQL被用作队列的话)。

( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 50177127 + 224 ) / 17660 / 200 = 1420.6% --如果> 100%，需要更多的io_capacity。--如果驱动器能处理的话，增加innodb_io_capacity。

( innodb_io_capacity ) = 200 --每秒可以在磁盘上执行I/O操作。100用于慢速驱动器；200用于旋转驱动器；1000-2000用于SSD；乘以RAID因子。

( expand_fast_index_creation ) = expand_fast_index_creation = OFF --使用ON可以大大加快修改和优化速度。-也许最好是上.

( innodb_thread_concurrency ) = 0 -0=让InnoDB决定concurrency_tickets的最佳选择。-设置为0或64这可能会减少CPU。

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF --是否记录所有死锁。--如果你被死锁所困扰，把这个打开警告:如果您有很多死锁，这可能会写入很多磁盘。

( max_connections ) = 15,574 --最大连接数(线程)。影响各种拨款。--如果max_connections太高，各种内存设置都很高，那么可能会耗尽内存。

( (Com_show_create_table + Com_show_fields) / Questions ) = (0 + 436) / 1804 = 24.2% --淘气框架--花费了大量的精力重新发现模式。-向第三方供应商投诉。

( local_infile ) = local_infile = ON - local_infile = ON是一个潜在的安全问题

( Select_scan / Com_select ) = 217 / 199 = 109.0% -%的选择执行全表扫描。(可能被存储的例程愚弄了)-添加索引/优化查询

( slow_query_log ) = slow_query_log = OFF --是否记录慢速查询。

( long_query_time ) = 10 --定义“慢速”查询的截止值(秒)。-建议2

( back_log ) = 15,574 -- (基于max_connections的自动大小为5.6.6；基于max_connections) --提高到最小(150，max_connections)在执行大量连接时可能会有所帮助。

( thread_cache_size ) = 16,384 --需要保留多少额外的进程(在使用线程池时不相关)(自5.6.8；基于max_connections) --大于100可能导致OOM。

( thread_cache_size / Max_used_connections ) = 16,384 / 4 = 409600.0% -线程缓存大于可能的连接数没有好处。浪费空间是不利的。

异常小：

Bytes_received = 6.2 /sec
Bytes_sent = 145 /sec
Com_insert = 0
Com_select = 41 /HR
Handler_read_key = 0.45 /sec
Handler_write = 0.33 /sec
Innodb_background_log_sync = 0
Innodb_data_writes = 77 /HR
Innodb_data_written = 222 /sec
Innodb_pages0_read = 0
Innodb_rows_inserted = 0
Innodb_secondary_index_triggered_cluster_reads = 0.32 /sec
Max_used_connections = 4
Open_files = 2
Select_range = 0
Table_locks_immediate = 2.7 /HR
Table_open_cache_hits = 0.48 /sec
innodb_default_encryption_key_id = 0

异常大：

( Innodb_pages_read + Innodb_pages_written ) / Uptime = 2,841
Com_check = 22 /HR
Com_create_db = 0.2 /HR
Handler_read_next / Handler_read_key = 51,277
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 33,884
Innodb_buffer_pool_pages_made_not_young = 12596 /sec
Innodb_buffer_pool_read_ahead = 1209 /sec
Innodb_data_pending_reads = 0.82 /HR
Innodb_data_read = 41774567 /sec
Innodb_data_reads = 2841 /sec
Innodb_pages_read = 2841 /sec
Threadpool_idle_threads = 9
Threadpool_threads = 11
gtid_executed_compression_period = 0.057 /sec
host_cache_size = 1,381
innodb_max_dirty_pages_pct_lwm = 10
innodb_undo_tablespaces = 2
max_error_count = 1,024
max_length_for_sort_data = 4,096
slave_pending_jobs_size_max = 128MB

异常字符串：

bind_address = 0.0.0.0
default_authentication_plugin = caching_sha2_password
event_scheduler = ON
have_ssl = YES
have_symlink = DISABLED
innodb_fast_shutdown = 1
innodb_undo_log_truncate = ON
optimizer_trace = enabled=off,one_line=off
slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN
thread_handling = pool-of-threads
transaction_write_set_extraction = XXHASH64

Answer 2

每秒速率= RPS - my.cnf 米舍尔德部分需要考虑的建议

max_connections=4000  # from 15574 to get above your 3,000 stall
thread_cache_size=100  # from 16384 to be capped at 100, per 5.7 to avoid OOM
innodb_buffer_pool_size=22G  # from 512M to reduce innodb_buffer_pool_reads RPS of 1,632
innodb_buffer_pool_instances=8  # from 1 to reduce mutex contention
innodb_lru_scan_depth=100  # from 1024 to 90% of CPU cycles used for function
innodb_log_file_size=1G  # from 50M, should always be greater than innodb_log_buffer_size
table_open_cache=4000  # from 400 to reduce opened_tables RPHr of 200

瑞克·詹姆斯已经提到了其中的一些建议。

免责声明:我是在我的个人资料，网络配置文件中提到的网站的内容作者，免费的实用程序脚本可供下载，联系信息是可用的。