关于MySQLTuner和MySQL配置的帮助

Question

我正在Cloudways服务器上运行我的服务器；这是一个LearnDash WordPress网站。我试图调整服务器配置，因为他们的支持不想对此做任何事情。

我有一个16 vCPU和32 GB内存

我通常使用3GB内存和13%的CPU，但突然之间，使用率就变得非常高，服务器由于MySQL和查询而中断( Cloudways支持)

我想知道你是否能帮我理解MySQLTuner，或者给我一些指点，说明我能用它做些什么？

这是MySQLTuner输出。

如果您也能指出php.ini配置的正确方向，我们将不胜感激。

[--] Skipped version check for MySQLTuner script                                                                                                                                                                                                                                          
[!!] Failed to execute: SHOW REPLICA STATUS\G                                                                                                                                                                                            
[!!] FAIL Execute SQL / return code: 256                                                                                                                                                                                                                                                  
[!!] Failed to execute: SHOW SLAVE STATUS                                                                                                                                                                                                                                                 
[!!] FAIL Execute SQL / return code: 256                                                                                                                                                                                                                                                  

[OK] Currently running supported MySQL version 10.6.3-MariaDB-1:10.6.3+maria~buster-log                                                                                                                                                                                                   
[OK] Operating on 64-bit architecture                                                                                                                                                                                                                                                     

-------- Log file Recommendations ------------------------------------------------------------------                                                                                                                                                                                      
[!!] Log file  doesn't exist                                                                                                                                                                                                                                                              

-------- Storage Engine Statistics -----------------------------------------------------------------                                                                                                                                                                                      
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE                                                                                                                                                                                                 
[--] Data in InnoDB tables: 10.6G (Tables: 105)                                                                                                                                                                                                                                           
[!!] Total fragmented tables: 1                                                                                                                                                                                                                                                           
-------- Analysis Performance Metrics --------------------------------------------------------------                                                                                                                                                                                      
[--] innodb_stats_on_metadata: OFF                                                                                                                                                                                                                                                        
[OK] No stat updates during querying INFORMATION_SCHEMA.                                                                                                                                                                                                                                  
-------- Performance Metrics -----------------------------------------------------------------------                                                                                                                                                                                      
[--] Up for: 1d 19h 29m 16s (39M q [253.332 qps], 279K conn, TX: 839G, RX: 6G)                                                                                                                                                                                                            
[--] Reads / Writes: 97% / 3%                                                                                                                                                                                                                                                             
[--] Binary logging is disabled                                                                                                                                                                                                                                                           
[--] Physical Memory     : 30.4G                                                                                                                                                                                                                                                          
[--] Max MySQL memory    : 153.2G                                                                                                                                                                                                                                                         
[--] Other process memory: 0B                                                                                                                                                                                                                                                             
[--] Total buffers: 2.8G global + 1.0G per thread (150 max threads)                                                                                                                                                                                                                       
[--] Performance_schema Max memory usage: 0B                                                                                                                                                                                                                                              
[--] Galera GCache Max memory usage: 0B                                                                                                                                                                                                                                                   
[!!] Maximum reached memory usage: 41.9G (137.73% of installed RAM)                                                                                                                                                                                                                       
[!!] Maximum possible memory usage: 153.2G (503.69% of installed RAM)                                                                                                                                                                                                                     
[!!] Overall possible memory usage with other process exceeded memory                                                                                                                                                                                                                     
[OK] Slow queries: 0% (349/39M)                                                                                                                                                                                                                                                           
[OK] Highest usage of available connections: 26% (39/150)                                                                                                                                                                                                                                 
[OK] Aborted connections: 0.34% (936/279187)                                                                                                                                                                                                                                              
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance                                                                                                                                                                    
[!!] Query cache may be disabled by default due to mutex contention.                                                                                                                                                                                                                      
[!!] Query cache efficiency: 10.6% (4M cached / 41M selects)                                                                                                                                                                                                                              
[!!] Query cache prunes per day: 35147                                                                                                                                                                                                                                                    
[OK] Sorts requiring temporary tables: 10% (174K temp sorts / 1M sorts)                                                                                                                                                                                                                   
[!!] Joins performed without indexes: 18728                                                                                                                                                                                                                                               
[!!] Temporary tables created on disk: 67% (513K on disk / 756K total)                                                                                                                                                                                                                    
[--] Thread cache not used with thread pool enabled                                                                                                                                                                                                                                       
[OK] Table cache hit rate: 99% (7M hits / 7M requests)                                                                                                                                                                                                                                    
[OK] table_definition_cache (400) is greater than number of tables (184)                                                                                                                                                                                                                  
[OK] Open file limit used: 0% (60/32K)                                                                                                                                                                                                                                                    
[OK] Table locks acquired immediately: 100% (75 immediate / 75 locks)                                                                                                                                                                                                                     

-------- Performance schema ------------------------------------------------------------------------                                                                                                                                                                                      
[!!] Performance_schema should be activated.                                                                                                                                                                                                                                              
[--] Sys schema is not installed.                                                                                                                                                                                                                                                         

-------- ThreadPool Metrics ------------------------------------------------------------------------                                                                                                                                                                                      
[--] ThreadPool stat is enabled.                                                                                                                                                                                                                                                          
[--] Thread Pool Size: 128 thread(s).                                                                                                                                                                                                                                                     
[--] Using default value is good enough for your version (10.6.3-MariaDB-1:10.6.3+maria~buster-log)                                                                                                                                                                                       
                                                                                                                                                                                                                                             

-------- InnoDB Metrics ----------------------------------------------------------------------------                                                                                                                                                                                      
[--] InnoDB is enabled.                                                                                                                                                                                                                                                                   
[OK] InnoDB File per table is activated                                                                                                                                                                                                                                                   
[!!] InnoDB buffer pool / data size: 2.5G / 10.6G                                                                                                                                                                                                                                         
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (5%): 128.0M * 1 / 2.5G should be equal to 25%                                                                                                                                                                                  
[--] Number of InnoDB Buffer Pool Chunk: 20 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.88% (10547518318 hits / 10559895724 total)                                                                                                                                                                                                         
[!!] InnoDB Write Log efficiency: 158.73% (440849 hits / 277733 total)                                                                                                                                                                                                                    
[OK] InnoDB log waits: 0.00% (0 waits / 718582 writes)                                                                                                                                                                                                                                    

-------- Recommendations ---------------------------------------------------------------------------                                                                                                                                                                                      
General recommendations:                                                                                                                                                                                                                                                                  
    Run ALTER TABLE ... FORCE or OPTIMIZE TABLE to defragment tables for better performance                                                                                                                                                                                               
      ALTER TABLE `xxmjcawbqz`.`wp_woocommerce_sessions` FORCE; -- can free 92 MiB                                                                                                                                                                                                        
    Total freed space after defragmentation: 92 MiB                                                                                                                                                                                                                                       
    7 CVE(s) found for your MySQL release. Consider upgrading your version !                                                                                                                                                                                                              
    Reduce your overall MySQL memory footprint for system stability                                                                                                                                                                                                                       
    Dedicate this server to your database for highest performance.                                                                                                                                                                                                                        
    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).                                                                                                                                                                                                                       
    When making adjustments, make tmp_table_size/max_heap_table_size equal                                                                                                                                                                                                                
    Reduce your SELECT DISTINCT queries which have no LIMIT clause                                                                                                                                                                                                                        
    Performance schema should be activated for better diagnostics                                                                                                                                                                                                                         
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
    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 ***                                                                                                                                                                                                                                
    skip-name-resolve=1                                                                                                                                                                                                                                                                   
    query_cache_size (=0)                                                                                                                                                                                                                                                                 
    query_cache_type (=0)                                                                                                                                                                                                                                                                 
    query_cache_limit (> 2M, or use smaller result sets)                                                                                                                                                                                                                                  
    query_cache_size (> 64M)                                                                                                                                                                                                                                                              
    join_buffer_size (> 256.0K, or always use indexes with JOINs)                                                                                                                                                                                                                         
    tmp_table_size (> 16M)                                                                                                                                                                                                                                                                
    max_heap_table_size (> 16M)                                                                                                                                                                                                                                                           
    performance_schema=ON                                                                                                                                                                                                                                                                 
    innodb_buffer_pool_size (>= 10.6G) if possible.                                                                                                                                                                                                                                       
    innodb_log_file_size should be (=640M) if possible, so InnoDB total log file size equals 25% of buffer pool size.        

这些是我的缓冲区值：

key_buffer_size |64.000 MB |
query_cache_size |64.000 MB |
innodb_buffer_pool_size |2560.000 MB |                                                                                                                                                                                                                         
innodb_additional_mem_pool_size |0.000 MB |
innodb_log_buffer_size |16.000 MB |                                                                                                                                                                                                                         
sort_buffer_size |2.000 MB |
read_buffer_size |0.125 MB |
read_rnd_buffer_size |0.250 MB |
join_buffer_size |0.250 MB |
thread_stack |0.285 MB
binlog_cache_size |0.031 MB |
tmp_table_size |16.000 MB |

每个连接内存18.941 MB

不幸的是，我无法访问my.cnf文件，但如果需要，我可以更改一些值。

Answer 1

但是突然之间，CPU的使用率非常高，服务器也随之下降。

听起来像是“雷鸣般的羊群”问题。减少max_connections，这样连接的突然涌出不会淹没服务器。

另外，使用SlowLog找出哪些查询是“最差的”，并讨论如何改进它们。

同时，MySQLTuner也有一些优点和一些值得怀疑的地方。

如果您不交换，那么它并不是真正超过RAM。

“查询缓存”已从未来版本中删除--好处太少，缺点太多。

除非你有很多其他内存用户，否则把innodb_buffer_pool_size提高到大约70%的可用内存。