我有一个网站，其设置与其他 20 个网站类似，只是这个网站不像其他网站那样处理数据库流量。它有 24 个处理器、196 GB 内存。然而，在大负载下，我发现使用htop和free比5.6gig 更常用top。当机器几乎空闲时，它使用 2.6gigs——其他机器都没有使用这么少的内存。我的一台 8 处理器机器在几乎空闲时通常为 15 GB。
我使用相同的软件来自动调整mariadb配置文件，所有盒子都使用nginx, 和php74.
我有许多慢查询和包含以下内容的整个消息日志文件
[Warning] Aborted connection 34 to db: 'mysite_com_EnfJ52WV' user: 'theuserco3v94' host: 'localhost' (Got an error reading communication packets)
我已在另一个虚拟机管理程序上重建了该站点，但问题仍然存在。我非常感谢任何想法、建议或发生这种情况的原因？
- 首先是错误日志：
Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Warning] You need to use --log-bin to make --binlog-format work. Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] InnoDB: Compressed tables use zlib 1.2.11 Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] InnoDB: Number of pools: 1 Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] InnoDB: Using Linux native AIO Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] InnoDB: Initializing buffer pool, total size = 60800630784, chunk size = 134217728 Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] InnoDB: Completed initialization of buffer pool Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] InnoDB: 128 rollback segments are active. Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] InnoDB: Creating shared tablespace for temporary tables Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] InnoDB: 10.6.16 started; log sequence number 3516889734036; transaction id 64889121 Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] Plugin 'FEEDBACK' is disabled. Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] Server socket created on IP: '::1'. Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] Server socket created on IP: '127.0.0.1'. Jan 6 19:11:57 thesite mariadbd[3624]: 2024-01-06 19:11:57 0 [Note] /usr/sbin/mariadbd: ready for connections. Jan 6 19:11:57 thesite mariadbd[3624]: Version: '10.6.16-MariaDB-1:10.6.16+maria~ubu2004-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution Jan 6 19:11:58 thesite mariadbd[3624]: 2024-01-06 19:11:58 0 [Note] InnoDB: Buffer pool(s) load completed at 240106 19:11:58 Jan 6 19:16:51 thesite mariadbd[3624]: 2024-01-06 19:16:51 34 [Warning] Aborted connection 34 to db: 'thesite_com_EnfJ52WV' user: 'thesiteco3v94' host: 'localhost' (Got an error reading communication packets) - 配置文件：
max_connections = 100 connect_timeout = 5 wait_timeout = 60 max_allowed_packet = 64M thread_cache_size = 128 sort_buffer_size = 4M bulk_insert_buffer_size = 16M tmp_table_size = 256M max_heap_table_size = 256M # # * MyISAM # # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched. On error, make copy and try a repair. myisam_recover_options = BACKUP key_buffer_size = 16M open-files-limit = 500000 table_open_cache = 16000 myisam_sort_buffer_size = 128M concurrent_insert = 2 read_buffer_size = 2M read_rnd_buffer_size = 1M # # * Query Cache Configuration # # Cache only tiny result sets, so we can fit more in the query cache. query_cache_limit = 128K query_cache_size = 0 # for more write intensive setups, set to DEMAND or OFF query_cache_type = 0 innodb_log_file_size = 7246M innodb_buffer_pool_size = 57969M innodb_log_buffer_size = 14492M innodb_file_per_table = 1 innodb_open_files = 500000 innodb_io_capacity = 500000 innodb_flush_method = O_DIRECT - 慢速查询：（只有两个，但很多人喜欢）
# Time: 240106 19:36:18 # User@Host: thesiteco3v94[thesiteco3v94] @ localhost [] # Thread_id: 803 Schema: thesite_com_EnfJ52WV QC_hit: No # Query_time: 12.114378 Lock_time: 0.000152 Rows_sent: 5 Rows_examined: 13885 # Rows_affected: 0 Bytes_sent: 198962 # Tmp_tables: 1 Tmp_disk_tables: 1 Tmp_table_sizes: 34226176 # Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: Yes # Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: No SET timestamp=1704569778; SELECT DISTINCT wp_posts.*, SUM( COALESCE( pvc.count, 0 ) ) AS post_views FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_post_views pvc ON pvc.id = wp_posts.ID AND pvc.type = 4 WHERE 1=1 AND wp_posts.ID NOT IN (1402,1691,4351,28684,28692,30874,36540,36579,36581,36583,36589,37011,37014,37015,37017,38591,38599,38601,38604,38646,38648,38649,38651,38652,38655,38657,38800,38944,38945,38946,38947,39181,39642,39650,39661,39664,39678,39713,39714,39715,39716,39717,39718,39719,39720,39801,39827,39846,39848,39851,40358,40364,40366,40367,40369,40372,40390,40391,40436,40927,41096,41106,41108,41110,41111,41116,41117,41118,41222,41268,41699,41703,41710,41722,42392,42398,42399,42401,42402,42404,42405,43096,44489,44503,46503,46523,46568) AND ( wp_term_relationships.term_taxonomy_id IN (1,3,4,42,92,99,120,161,164,167,201,213,227,231,268,283,297,316,321,341,345,370,494,572,746,1031,6252,7224,7583,7719,8723,9424,9960,11147,11251,11252,11253,12122,12187,12533,12636,12637,12692,12693,12694,12695,12868,12933,12934,12935,13416,13802,13803,13856,13987,13988,13989,14057,14134,14135,14136,14137,14223,14224,14307,14318,14319,14405,14493,14494,14495,14496,14497,14773,14778,14779,15150,15151,15152,15153,15167,15517,15636,15640,15641,15942,16030,16287,16310,17175,17201,17212,17269,18589,18590,18591,18592,19048,20165,20317,20320,20942) ) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID, wp_term_relationships.term_taxonomy_id HAVING post_views > 0 ORDER BY post_views DESC, wp_posts.ID DESC LIMIT 0, 5; # Time: 240106 19:36:19 # User@Host: thesiteco3v94[thesiteco3v94] @ localhost [] # Thread_id: 805 Schema: thesite_com_EnfJ52WV QC_hit: No # Query_time: 11.439592 Lock_time: 0.000092 Rows_sent: 5 Rows_examined: 13885 # Rows_affected: 0 Bytes_sent: 198962 # Tmp_tables: 1 Tmp_disk_tables: 1 Tmp_table_sizes: 34226176 # Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: Yes # Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: No SET timestamp=1704569779; SELECT DISTINCT wp_posts.*, SUM( COALESCE( pvc.count, 0 ) ) AS post_views FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_post_views pvc ON pvc.id = wp_posts.ID AND pvc.type = 4 WHERE 1=1 AND wp_posts.ID NOT IN (1402,1691,4351,28684,28692,30874,36540,36579,36581,36583,36589,37011,37014,37015,37017,38591,38599,38601,38604,38646,38648,38649,38651,38652,38655,38657,38800,38944,38945,38946,38947,39181,39642,39650,39661,39664,39678,39713,39714,39715,39716,39717,39718,39719,39720,39801,39827,39846,39848,39851,40358,40364,40366,40367,40369,40372,40390,40391,40436,40927,41096,41106,41108,41110,41111,41116,41117,41118,41222,41268,41699,41703,41710,41722,42392,42398,42399,42401,42402,42404,42405,43096,44489,44503,46503,46523,46568) AND ( wp_term_relationships.term_taxonomy_id IN (1,3,4,42,92,99,120,161,164,167,201,213,227,231,268,283,297,316,321,341,345,370,494,572,746,1031,6252,7224,7583,7719,8723,9424,9960,11147,11251,11252,11253,12122,12187,12533,12636,12637,12692,12693,12694,12695,12868,12933,12934,12935,13416,13802,13803,13856,13987,13988,13989,14057,14134,14135,14136,14137,14223,14224,14307,14318,14319,14405,14493,14494,14495,14496,14497,14773,14778,14779,15150,15151,15152,15153,15167,15517,15636,15640,15641,15942,16030,16287,16310,17175,17201,17212,17269,18589,18590,18591,18592,19048,20165,20317,20320,20942) ) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID, wp_term_relationships.term_taxonomy_id HAVING post_views > 0 ORDER BY post_views DESC, wp_posts.ID DESC LIMIT 0, 5;