Mysql
MySQL 佔用 400% CPU
DigitalOcean 上的 LEMP 堆棧（48GB 記憶體，960GB SSD，12 個 vCPU），上面有 WordPress。添加了一個新主題，mysql 飛速發展。MySQL 本身佔用了 400% 的 CPU 使用率！
查看慢日誌以找出這
SQL_CALC_FOUND_ROWS是慢查詢。當有人在部落格上搜尋一個詞時，它觸發了 3 次。所以我按照這個部落格替換
SQL_CALC_FOUND_ROWS了COUNT(*)我的functions.php。但現在我看到有類似的條目
SELECT COUNT(*) FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE '%zenbot%') OR (wp_posts.post_excerpt LIKE '%zenbot%') OR (wp_posts.post_content LIKE '%zenbot%')) AND ((wp_posts.post_title LIKE '%bitmex|Bityard.com%') OR (wp_posts.post_excerpt LIKE '%bitmex|Bityard.com%') OR (wp_posts.post_content LIKE '%bitmex|Bityard.com%') ) AND ((wp_posts.post_title LIKE '%258U%') OR (wp_posts.post_excerpt LIKE '%258U%') OR (wp_posts.post_content LIKE '%258U%') ) AND ((wp_posts.post_title LIKE '%Bonus%') OR (wp_posts.post_excerpt LIKE '%Bonus%') OR (wp_posts.post_content LIKE '%Bonus%')) ) AND (wp_posts.post_password = '') AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish');這是什麼禪機？我的 WordPress 是否被機器人攻擊？
還有更多這樣的
SELECT COUNT(*) FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE '%f(x)%') OR (wp_posts.post_excerpt LIKE '%f(x)%') OR (wp_posts.post_content LIKE '%f(x)%')) AND ((wp_posts.post_title LIKE '%binance|Bityard.com%') OR (wp_posts.post_excerpt LIKE '%binance|Bityard.com%') OR (wp_posts.post_content LIKE '%binance|Bityard.com%') ) AND ((wp_posts.post_title LIKE '%258U%') OR (wp_posts.post_excerpt LIKE '%258U%') OR (wp_posts.post_content LIKE '%258U%') ) AND ((wp_posts.post_title LIKE '%Bonus%') OR (wp_posts.post_excerpt LIKE '%Bonus%') OR (wp_posts.post_content LIKE '%Bonus%')) ) AND (wp_posts.post_password = '') AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish');我已經調整了我的
my.cnf但似乎沒有任何工作。任何人都可以請指導嗎？我的my.cnf設置不正確嗎？[mysqld] performance_schema = ON 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 query_cache_limit = 128K query_cache_size = 0 query_cache_type = 0 slow_query_log = 1 slow_query_log_file = /var/log/mysql/mariadb-slow.log long_query_time = 3 default_storage_engine = InnoDB # you can't just change log file size, requires special procedure innodb_log_file_size = 1811M innodb_buffer_pool_size = 10G innodb_log_buffer_size = 3622M innodb_file_per_table = 1 innodb_open_files = 500000 innodb_io_capacity = 500000 innodb_flush_method = O_DIRECT innodb_read_io_threads=16 innodb_write_io_threads=16 innodb_buffer_pool_instances = 11這是
mysqltuner結果[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in InnoDB tables: 2.1G (Tables: 58) [OK] Total fragmented tables: 0 -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA. -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users -------- Performance Metrics ----------------------------------------------------------------------- [--] Reads / Writes: 99% / 1% [--] Binary logging is disabled [--] Physical Memory : 47.2G [--] Max MySQL memory : 22.1G [--] Other process memory: 0B [--] Total buffers: 15.0G global + 71.5M per thread (100 max threads) [--] P_S Max memory usage: 95M [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 20.9G (44.37% of installed RAM) [OK] Maximum possible memory usage: 22.1G (46.89% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (584/15M) [OK] Highest usage of available connections: 83% (83/100) [OK] Aborted connections: 0.00% (0/50946) [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6M sorts) [OK] No joins without indexes [!!] Temporary tables created on disk: 95% (204K on disk / 214K total) [OK] Thread cache hit rate: 99% (83 created / 50K connections) [OK] Table cache hit rate: 98% (389 open / 395 opened) [OK] table_definition_cache(400) is upper than number of tables(217) [OK] Open file limit used: 0% (56/500K) [OK] Table locks acquired immediately: 100% (44 immediate / 44 locks) -------- Performance schema ------------------------------------------------------------------------ [--] Memory used by P_S: 95.1M [--] Sys schema isn't installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 12 thread(s). [--] Using default value is good enough for your version (10.3.23-MariaDB-1:10.3.23+maria~bionic-log) -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.2% (24M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/124.0K -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 11.0G/2.1G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (32.1555397727273 %): 1.8G * 2/11.0G should be equal to 25% [OK] InnoDB buffer pool instances: 11 [--] Number of InnoDB Buffer Pool Chunk : 88 for 11 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.99% (1464055771 hits/ 1464192013 total) [!!] InnoDB Write Log efficiency: 59.44% (27661 hits/ 46534 total) [OK] InnoDB log waits: 0.00% (0 waits / 18873 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/1B [OK] Aria pagecache hit rate: 98.8% (60M cached / 745K reads) -------- 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: OFF [--] Semi synchronous replication Slave: OFF [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: MySQL was started within the last 24 hours - recommendations may be inaccurate Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU Variables to adjust: innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
許多事情會使您的查詢變慢。
- 前導萬用字元
LIKE（例如LIKE '%Bonus%'）。它必須仔細掃描每一行LIKE。- 如果這是一個正則表達式“或”，它不是：
LIKE '%binance|Bityard.com%'OR是不可優化的。檢查 3 列的相同內容意味著工作量增加了 3 倍。- 一個
FULLTEXT索引可能比所有這些都好LIKEs。- 這個文章索引可能會有所幫助：
INDEX(post_status, post_password, post_type)不做
COUNT(*)或SQL_CALC_FOUND_ROWS預設做；讓使用者單獨要求昂貴的操作。您無法通過調整來擺脫 CPU 問題。
你的 innodb_log_buffer_size 永遠不能大於你的 innodb_log_file_size。您可能會顛倒這些值。