Mysql
幫助在專用伺服器上設置 MySQL - 提供詳細報告
我需要幫助，因為我不太了解專用伺服器上的 MySQL 設置。
我安裝了一個帶有 W3 Total Cache 的中等流量的 WordPress 網站。
儲存引擎是 InnoDB。
以下是我從 MySQLTuner 獲得的配置和報告，我不太了解，需要一些幫助。
伺服器
CPU: 8 cores / 8 threads, 1 CPU 64bit architecture L3 cache: 8MB Speed: 2400 MHz RAM: 32GB RAM DDR3 Storage: 2x250GB SSD (RAID1) Link: 1Gbps設置
Debian 7 (Linux) Nginx PHP 5.4 using php-fpm (cache: PHP-APC, PHP-memcache, Memcached) MySQL 5.5MySQL 配置
key_buffer 512M max_allowed_packet 16M thread_stack 256K thread_cache_size 286 table_cache 1024 table_open_cache 16384 table_definition_cache 8192 sort_buffer_size 32M read_buffer_size 32M read_rnd_buffer_size 32M join_buffer_suze 32M query_cache_limit 20M query_cache_size 2048M tmp_table_size 1024M max_heap_table_size 1024M innodb_buffer_pool_size 20G innodb_buffer_pool_instances 8 [mysqldump] max_allowed_packet 16M [isamchk] key_buffer 16MMySQLTuner 報告
-------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 1d 21h 26m 35s (8M q [49.822 qps], 471K conn, TX: 399G, RX: 1G) [--] Reads / Writes: 87% / 13% [--] Binary logging is disabled [--] Physical Memory : 31.4G [--] Max MySQL memory : 42.4G [--] Other process memory: 5.7G [--] Total buffers: 23.5G global + 128.2M per thread (151 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [!!] Maximum reached memory usage: 26.8G (85.18% of installed RAM) [!!] Maximum possible memory usage: 42.4G (135.00% of installed RAM) [!!] Overall possible memory usage with other process exceeded memory [OK] Slow queries: 0% (0/8M) [OK] Highest usage of available connections: 17% (26/151) [OK] Aborted connections: 0.12% (576/471646) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [!!] Query cache may be disabled by default due to mutex contention. [OK] Query cache efficiency: 59.6% (3M cached / 5M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (42 temp sorts / 629K sorts) [OK] No joins without indexes [!!] Temporary tables created on disk: 56% (236K on disk / 414K total) [OK] Thread cache hit rate: 99% (26 created / 471K connections) [OK] Table cache hit rate: 84% (1K open / 2K opened) [OK] Open file limit used: 5% (1K/32K) [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks) -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. [--] Memory used by P_S: 0B [--] Sys schema isn't installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 21.5% (115M used / 536M cache) [OK] Key buffer size / total MyISAM indexes: 512.0M/416.0K [OK] Read Key buffer hit rate: 100.0% (148M cached / 16K reads) [!!] Write Key buffer hit rate: 53.2% (546K cached / 290K writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [!!] InnoDB File per table is not activated [OK] InnoDB buffer pool / data size: 20.0G/1.9G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.048828125 %): 5.0M * 2/20.0G should be equal 25% [!!] InnoDB buffer pool instances: 8 [--] InnoDB Buffer Pool Chunk Size not used or defined in your version [OK] InnoDB Read buffer efficiency: 100.00% (12017309000 hits/ 12017381306 total) [!!] InnoDB Write Log efficiency: 59.93% (169492 hits/ 282840 total) [OK] InnoDB log waits: 0.00% (0 waits / 113348 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is disabled. -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- RocksDB Metrics --------------------------------------------------------------------------- [--] RocksDB is disabled. -------- Spider Metrics ---------------------------------------------------------------------------- [--] Spider is disabled. -------- Connect Metrics --------------------------------------------------------------------------- [--] Connect is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] Binlog format: STATEMENT [--] XA support enabled: ON [--] Semi synchronous replication Master: Not Activated [--] Semi synchronous replication Slave: Not Activated [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: Reduce your overall MySQL memory footprint for system stability Dedicate this server to your database for highest performance. Enable the slow query log to troubleshoot bad queries Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Consider installing Sys schema from github Before changing innodb_log_file_size and/or innodb_log_files_in_group read this Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (=0) query_cache_type (=0) innodb_file_per_table=ON innodb_log_file_size should be (=2G) if possible, so InnoDB total log files size equals to 25% of buffer pool size. innodb_buffer_pool_instances(=20)請幫忙。謝謝。
為您的 my.cnf 考慮的建議
$$ mysqld $$部分 以 # 空格鍵開頭，以允許預設值適用於以下內容
sort_buffer_size read_buffer_size read_rnd_buffer_size join_buffer_size您上面的值無用地影響 RAM 佔用空間
thread_cache_size=100 # from 286 for CAP suggested in v 8.0 to avoid OOM key_buffer_size=256M # from 512M because only 115M used key_cache_age_threshold=64800 # from 300 seconds to reduce key_reads key_cache_division_limit=50 # from 100 percent for Hot/Warm caches key_cache_block_size=16384 # from 1024 to minimize overhead tmp_table_size=320M # from 1024M for 1% of RAM max_heap_table_size=320M # should always be same size as tmp_table_size innodb_buffer_pool_size=4G # from 20G with ~2G of data and ndx's 4G allows growth如需更多幫助，請參閱我的個人資料以獲取聯繫資訊。
希望在時間允許的情況下獲得有關您成功的回饋。