Mysql
問答動態網站的 MySQL Tuner 設置
我在已經連續工作 1 週的伺服器上執行了 mysqltuner 腳本。
我分享我得到的值和現有的 mysql 配置文件。你可以幫幫我嗎？當我在網際網路上查看時，我看不到非常詳細的解決方案。
MySQL 調諧器輸出
[--] Skipped version check for MySQLTuner script [OK] Logged in using credentials from Debian maintenance account. [OK] Currently running supported MySQL version 5.7.32-0ubuntu0.18.04.1 [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [!!] log_error is set to stderr MT can't read stderr -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA [--] Data in MyISAM tables: 173.9K (Tables: 5) [--] Data in InnoDB tables: 1.0G (Tables: 110) [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 [!!] User 'root@localhost' has no password set. [!!] There is no basic password file list! -------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 3d 6h 34m 46s (40M q [143.364 qps], 346K conn, TX: 133G, RX: 8G) [--] Reads / Writes: 94% / 6% [--] Binary logging is disabled [--] Physical Memory : 3.9G [--] Max MySQL memory : 51.7G [--] Other process memory: 0B [--] Total buffers: 1.4G global + 257.8M per thread (200 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [!!] Maximum reached memory usage: 4.7G (121.05% of installed RAM) [!!] Maximum possible memory usage: 51.7G (1342.95% of installed RAM) [!!] Overall possible memory usage with other process exceeded memory [OK] Slow queries: 0% (0/40M) [OK] Highest usage of available connections: 6% (13/200) [OK] Aborted connections: 0.00% (0/346968) [--] Skipped name resolution test due to skip_networking=ON in system variables. [!!] Query cache may be disabled by default due to mutex contention. [OK] Query cache efficiency: 72.5% (27M cached / 38M selects) [!!] Query cache prunes per day: 105070 [OK] Sorts requiring temporary tables: 0% (720 temp sorts / 421K sorts) [OK] No joins without indexes [OK] Temporary tables created on disk: 18% (35K on disk / 188K total) [OK] Thread cache hit rate: 99% (13 created / 346K connections) [OK] Table cache hit rate: 95% (1K open / 1K opened) [OK] table_definition_cache(2048) is upper than number of tables(394) [OK] Open file limit used: 0% (48/5K) [OK] Table locks acquired immediately: 100% (789 immediate / 789 locks) -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. [--] Memory used by P_S: 0B [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.2% (48M used / 268M cache) [OK] Key buffer size / total MyISAM indexes: 256.0M/102.0K [OK] Read Key buffer hit rate: 99.8% (8K cached / 16 reads) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 4 [OK] InnoDB File per table is activated [!!] InnoDB buffer pool / data size: 1.0G/1.0G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (9.375 %): 48.0M * 2/1.0G should be equal to 25% [OK] InnoDB buffer pool instances: 1 [--] Number of InnoDB Buffer Pool Chunk : 8 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.97% (2850068636 hits/ 2850872999 total) [!!] InnoDB Write Log efficiency: 23.15% (238067 hits/ 1028533 total) [OK] InnoDB log waits: 0.00% (0 waits / 790466 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is disabled. -------- 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: Set up a Secure Password for root@localhost user: SET PASSWORD FOR 'root'@'SpecificDNSorIp' = PASSWORD('secure_password'); Reduce your overall MySQL memory footprint for system stability Dedicate this server to your database for highest performance. Increasing the query_cache size over 128M may reduce performance Performance schema should be activated for better diagnostics 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 *** query_cache_size (=0) query_cache_type (=0) query_cache_size (> 128M) [see warning above] performance_schema = ON enable PFS innodb_buffer_pool_size (>= 1.0G) if possible. innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.MySQL 配置文件
# # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. # Here is entries for some specific programs # The following values assume you have at least 32M ram [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /var/tmpfs tmp_table_size=2K lc-messages-dir = /usr/share/mysql skip-external-locking bind-address = 127.0.0.1 performance_schema=off skip-networking tmpdir=/tmp max_connections=200 max_user_connections=200 key_buffer_size=256M myisam_sort_buffer_size=128M join_buffer_size=1M read_buffer_size=128K sort_buffer_size=128K table_open_cache=3072 thread_cache_size=1024 table_definition_cache=2048 open_files_limit=52000 wait_timeout=120 connect_timeout=120 max_heap_table_size=128M max_allowed_packet=268435456 query_cache_limit=4M query_cache_size=128M query_cache_type=1 interactive_timeout=120 max_connect_errors=15 local-infile=0 #innodb_additional_mem_pool_size=64M innodb_flush_method=O_DIRECT default-storage-engine=InnoDB innodb_buffer_pool_size=1000M innodb_buffer_pool_instances=2 innodb_log_buffer_size=16M innodb_file_per_table=1 innodb_file_format = barracuda innodb_thread_concurrency=4 innodb_flush_log_at_trx_commit = 1 innodb_write_io_threads=2 innodb_read_io_threads=2 #general_log_file = /var/log/mysql/mysql.log #general_log = 1 [isamchk] key_buffer=512M sort_buffer_size=2M read_buffer=2M write_buffer=2M [myisamchk] tmpdir=/tmp key_buffer=512M sort_buffer=2M read_buffer=2M write_buffer=2M如果你能幫忙，我會很高興。我應該更改哪些設置？
問題是什麼？如果是“慢”查詢，那麼我們需要查看它們。一些可調參數比需要的要高，所以這裡有一些建議的更改：
key_buffer_size = 50M max_connections = 100 table_open_cache = 1000 table_definition_cache = 400 temp_table_size = 40M max_heap_table_size = 40M innodb_buffer_pool_instances = 1 query_cache_size = 40M thread_cache_size = 10 innodb_buffer_pool_size = 1200M如需更深入的分析：http: //mysql.rjweb.org/doc.php/mysql_analysis
XtrmuS 請應用 2020 年 11 月 1 日的 Rick James 建議進行改進。
為了在幾天內提高下一次性能，需要考慮的其他建議，
join_buffer_size=128K # from 1M for this per connection RAM footprint reduction thread_cache_size=100 # from RJ suggestion of 10 - to reduce threads_created count query_cache_type=0 # for OFF from ON to conserve CPU cycles query_cache_size=0 # from 128M to conserve CPU cycles and eliminate Query cache prunes per day query_cache_limit=0 # from 4M - with QC OFF you will conserve this RAM innodb_thread_concurrency=0 # from 4 to allow auto size by MySQL innodb_change_buffer_max_size=40 # from 25 percent to accomodate high volume INSERTS請查看配置文件、網路配置文件以獲取聯繫資訊和免費下載的實用程序腳本以幫助進行性能調整。