우리 조직에는 최근까지 훌륭하게 작동해 왔지만 눈에 띄는 변경 사항이 없는 상당히 발전된(많은 움직이는 부분에서) 웹 응용 프로그램이 있습니다.
Apache 웹 서버, mySQL 서버(데이터 처리), 그리고 대중에 대한 반복적인 요청을 처리하는 또 다른 mySQL 서버가 있습니다. 기본 웹 서버로의 트래픽 유입은 보조 SQL 서버에만 거의 영향을 미칩니다. 반복 요청 서버는 데이터 처리 서버의 슬레이브이지만 쓰기 쿼리는 반복 요청 서버(읽기 전용)로 전송되지 않습니다.
제가 직면하고 있는 문제는 데이터 처리 mySQL 서버가 로드가 심한 경우에도 일반적으로 15~20%로 작동할 때 CPU 사용량이 무작위로 100%까지 급증하는 것 같다는 것입니다. 100% CPU 사용량은 약 8초 동안 지속되며 때로는 저절로 사라질 때까지 2~3분마다 급증합니다.
느린 쿼리 로그에는 100%일 때 처리를 시도한 쿼리 외에는 아무것도 없습니다. 로그로 이어지는 쿼리는 무작위 급증 이전에 일반적으로 문제 없이 실행되었던 쿼리와 동일합니다.
HTOP에 표시되는 유일한 활동은 mySQL이며, 이 서버에도 예약된 크론이 없으며 이 시간 동안 다른 쿼리 활동이 증가하지 않습니다. 열린 연결 스레드 계정은 3~5개 정도 안정적으로 유지되며 PROCESS LIST에도 이전, 도중 또는 이후에 3~5개의 쿼리만 있습니다.
SELECT * FROM audio
WHERE associated_incident IS null
AND archive IS NULL
AND temp_skip IS null
AND length >= 3
AND (locked <> 1 or locked IS NULL)
AND timestamp > (NOW() - INTERVAL 4 HOUR)
ORDER BY `audio`.`id` DESC
LIMIT 1;
UPDATE audio SET locked=1, lockexpr = '$ulockexpr', lockuser = '$ulockuser' WHERE id = $audio_id;
위의 쿼리는 직원이 사용하는 소프트웨어에서 반복적으로 실행되지만 특정 시간에 초당 1-2회를 넘지 않지만 문제의 범위를 좁힐 수 있는 가장 가까운 쿼리입니다. 이 쿼리는 급증 기간 동안에도 Slow_query_log에 표시됩니다. 하지만 다시 한 번 말씀드리자면, 이 쿼리는 하루 종일 광고에 사용되지만 항상 이러한 문제가 발생하는 것은 아닙니다.
이 쿼리를 사용하여 서버를 스트레스 테스트하고 5초 내에 100회 실행해도 반환 시간에 대해 .002~.005초의 평균 결과를 얻을 수 있습니다.
/var/log/mysql에는 8.2GB의 mysql-bin 파일이 있습니다.
my.cnf에 대해서만 가능한 수정 사항
key_buffer_size = 16M
myisam-recover-options = BACKUP
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M
binlog_do_db = incident_log
다른 모든 것은 지정되지 않았거나 주석 처리되었습니다.
MySQL 튜너
>> MySQLTuner 2.1.1
* Jean-Marie Renouard <[email protected]>
* Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[OK] Currently running supported MySQL version 8.0.32-0ubuntu0.20.04.2
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql/error.log exists
[--] Log file: /var/log/mysql/error.log (0B)
[--] Log file /var/log/mysql/error.log is empty. Assuming log-rotation. Use --server-log={file} for explicit file
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 1.5G (Tables: 107)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Views Metrics -----------------------------------------------------------------------------
-------- Triggers Metrics --------------------------------------------------------------------------
-------- Routines Metrics --------------------------------------------------------------------------
-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8.0+
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 12h 41m 34s (3M q [73.289 qps], 1M conn, TX: 5G, RX: 445M)
[--] Reads / Writes: 81% / 19%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 1.9G
[--] Max MySQL memory : 10.1G
[--] Other process memory: 0B
[--] Total buffers: 176.0M global + 65.9M per thread (151 max threads)
[--] Performance_schema Max memory usage: 239M
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 10.2G (528.62% of installed RAM)
[!!] Maximum possible memory usage: 10.1G (525.28% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (3/3M)
[!!] Highest connection usage: 100% (152/151)
[OK] Aborted connections: 0.09% (1203/1391547)
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance
[--] Query cache has been removed since MySQL 8.0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10K sorts)
[!!] Joins performed without indexes: 5915
[OK] Temporary tables created on disk: 0% (0 on disk / 8K total)
[OK] Thread cache hit rate: 94% (75K created / 1M connections)
[OK] Table cache hit rate: 99% (1M hits / 1M requests)
[OK] table_definition_cache (2000) is greater than number of tables (434)
[OK] Open file limit used: 0% (3/10K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] Binlog cache memory access: 100.00% (346520 Memory / 346520 Total)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 239.2M
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled since MySQL 8.0.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M / 1.5G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 48.0M * 2 / 128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk: 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.87% (403976971 hits / 404504329 total)
[!!] InnoDB Write Log efficiency: 59.62% (1713194 hits / 2873563 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1160369 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
MySQL was started within the last 24 hours: recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Reduce or eliminate persistent connections to reduce connection usage
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
Buffer Key MyISAM set to 0, no MyISAM table detected
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
skip-name-resolve=1
join_buffer_size (> 256.0K, or always use indexes with JOINs)
key_buffer_size=0
innodb_buffer_pool_size (>= 1.5G) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
모든 조언을 위해 열려있습니다.
편집:
mysql> SHOW TABLE STATUS WHERE name LIKE "audio";
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
| audio | InnoDB | 10 | Dynamic | 969276 | 164 | 159039488 | 0 | 90898432 | 7340032 | 1644653 | 2023-04-13 14:41:27 | 2023-04-17 14:56:21 | NULL | utf8mb4_0900_ai_ci | NULL | | |
PHP
$sql = "SELECT * FROM audio
WHERE associated_incident IS null
AND archive IS NULL
AND temp_skip IS null
AND length >= 3
AND (locked <> 1 or locked IS NULL)
ORDER BY `audio`.`id` DESC
LIMIT 1;
";
$result = mysqli_query($conn, $sql) or die(mysqli_error());
$audio = mysqli_fetch_assoc($result);
if ($audio) {
$audio_id = $audio['id'];
$audio_timestamp = $audio['timestamp'];
$audio_hash = $audio['hash'];
$audio_length = $audio['length'];
$ulockexpr = date("Y-m-d H:i:s", strtotime('+ 5min'));
$ulockuser = $_SESSION['login_user'];
$sql_lock = "UPDATE audio SET locked=1, lockexpr = '$ulockexpr', lockuser = '$ulockuser' WHERE id = $audio_id";
답변1
다음으로 변경하세요 innodb_buffer_pool_size = 500M. 이전 기본값인 128M은 매우 낮습니다.
SELECT작업할 행을 찾기 위해 이 작업을 수행한 다음 해당 작업을 수행하면 UPDATE트랜잭션을 사용하십시오.
START TRANSACTION;
SELECT ... FROM audio ... FOR UPDATE;
... process ...
UPDATE audio SET ... WHERE id = ...;
COMMIT;
이렇게 하면 START - FOR UPDATE - COMMIT두 개의 스레드가 작업하기 위해 동일한 항목을 잡는 것을 방지할 수 있습니다.
ORDER BY timestamp DESC대신 사용하는 것을 고려해 보세요 ORDER BY id DESC.
문제가 되는 진술을 보려면 이 구성이 필요할 수 있습니다.
log_slow_admin_statements = ON
테이블에는 어떤 인덱스가 있습니까? 제공 부탁드립니다 SHOW CREATE TABLE.
[예, 낮음] max_connections을 으로 낮추십시오 50. 그리고동시 하위 항목이 40개 이상 생성되지 않도록 Apache 설정을 낮추세요. 나는 그 Max_used_connections히트를 본다 max_connections. 이런 일이 발생하면 MySQL은 문제가 해결될 때까지 혼란에 빠질 것입니다. 한도를 낮추면 더 빨리 해제가 시작됩니다.