이 사이트가 다른 사이트처럼 데이터베이스 트래픽을 처리하지 않는다는 점을 제외하면 20개의 다른 사이트와 유사하게 설정된 사이트가 있습니다. 프로세서 24개, 메모리 196GB를 갖추고 있습니다. 그러나 부하가 심한 경우에는 1 htop및 free5.6gig를 사용하는 것이 5.6gig보다 더 일반적입니다 top. 머신이 거의 유휴 상태일 때 2.6GB를 사용합니다. 다른 어떤 머신도 그렇게 적은 메모리를 사용하지 않습니다. 내 8개 프로세서 컴퓨터 중 하나는 거의 유휴 상태일 때 일반적으로 15GB를 갖습니다.
나는 동일한 소프트웨어를 사용하여 모든 상자에서 사용하는 프로필을 자동으로 조정 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 - 느린 쿼리: (2개만 있지만 많은 사람들이 좋아함)
# 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;