# curl -L http://mysqltuner.pl/ | perl
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
LC_CTYPE = "UTF-8",
LANG = "en_US.UTF-8"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 211k 100 211k 0 0 29678 0 0:00:07 0:00:07 --:--:-- 120k
>> MySQLTuner 1.7.9 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.58-cll
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/sal.sallyfashion.com.my.err(1M)
[OK] Log file /var/lib/mysql/sal.sallyfashion.com.my.err exists
[OK] Log file /var/lib/mysql/sal.sallyfashion.com.my.err is readable.
[OK] Log file /var/lib/mysql/sal.sallyfashion.com.my.err is not empty
[OK] Log file /var/lib/mysql/sal.sallyfashion.com.my.err is smaller than 32 Mb
[!!] /var/lib/mysql/sal.sallyfashion.com.my.err contains 42 warning(s).
[!!] /var/lib/mysql/sal.sallyfashion.com.my.err contains 4497 error(s).
[--] 75 start(s) detected in /var/lib/mysql/sal.sallyfashion.com.my.err
[--] 1) 180416 14:40:24 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 180416 14:39:09 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 180416 14:38:01 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 180416 14:37:41 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 180416 14:36:32 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 180416 14:36:22 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 180416 14:36:21 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 180416 14:36:19 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 180416 14:36:17 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 180416 14:36:16 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8 shutdown(s) detected in /var/lib/mysql/sal.sallyfashion.com.my.err
[--] 1) 180416 14:40:22 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 180416 14:39:06 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 180416 14:38:03 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 180416 14:37:46 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 180416 14:36:29 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 180416 14:34:26 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 180416 13:15:01 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 180416 13:14:23 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 13G (Tables: 1763)
[--] Data in InnoDB tables: 90M (Tables: 1783)
[--] Data in MEMORY tables: 3M (Tables: 77)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 20h 35m 29s (81M q [1K qps], 403K conn, TX: 302G, RX: 14G)
[--] Reads / Writes: 71% / 29%
[--] Binary logging is disabled
[--] Physical Memory : 15.6G
[--] Max MySQL memory : 3586.2G
[--] Other process memory: 2.1G
[--] Total buffers: 1.6G global + 3.5G per thread (1024 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 2795.0G (17945.76% of installed RAM)
[!!] Maximum possible memory usage: 3586.2G (23025.23% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (307K/81M)
[OK] Highest usage of available connections: 77% (798/1024)
[OK] Aborted connections: 0.03% (101/403957)
[!!] 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: 95.8% (75M cached / 78M selects)
[!!] Query cache prunes per day: 1018432
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 559K sorts)
[!!] Joins performed without indexes: 2211
[!!] Temporary tables created on disk: 40% (142K on disk / 353K total)
[OK] Thread cache hit rate: 99% (2K created / 403K connections)
[!!] Table cache hit rate: 19% (5K open / 25K opened)
[OK] Open file limit used: 44% (4K/11K)
[!!] Table locks acquired immediately: 90%
-------- 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 ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (67M used / 67M cache)
[OK] Key buffer size / total MyISAM indexes: 64.0M/4.2G
[OK] Read Key buffer hit rate: 99.6% (3B cached / 14M reads)
[!!] Write Key buffer hit rate: 3.0% (28M cached / 848K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 16
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 512.0M/90.5M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (1.953125 %): 5.0M * 2/512.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 96.23% (290632 hits/ 302029 total)
[!!] InnoDB Write Log efficiency: 86.35% (253 hits/ 293 total)
[OK] InnoDB log waits: 0.00% (0 waits / 40 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:
Control warning line(s) into /var/lib/mysql/sal.sallyfashion.com.my.err file
Control error line(s) into /var/lib/mysql/sal.sallyfashion.com.my.err file
MySQL started within 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.
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
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (11034) variable
should be greater than table_open_cache (5000)
Optimize queries and/or use InnoDB to reduce lock wait
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Read this before changing innodb_log_file_size and/or innodb_log_files_in_group:
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 (> 512M) [see warning above]
join_buffer_size (> 3.0G, or always use indexes with joins)
table_open_cache (> 5000)
innodb_log_file_size should be (=64M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
答案1
Linux 将以 CPU 核心的百分比形式报告 CPU 使用率。因此，如果您的数据库服务器有 4 个核心，那么您可以看到高达 400% 的 CPU 使用率（4 x 100%）。MySQL 可以很好地扩展以使用所有可用的 CPU/核心，因此它可以扩展。
看起来唯一真正注意到的问题是内存占用，输出提供了一些选项，但关键的选项是向系统添加额外的 RAM。
答案2
你应该感到高兴——它可以扩展到单个执行单元。有些人正在拼命寻找它……）
答案3
针对 my.cnf-ini [mysqld] 部分的建议
Mysqltuner 表示您可以使用约 3TB 的 RAM，而您有 16G
以 # 或 REMOVE 开头，以允许默认设置工作
. 排序缓冲区大小 . 读取缓冲区大小 . 读取rnd缓冲区大小 . 加入缓冲区大小
以上所有内容均为每个连接的 RAM 请求。
thread_cache_size=100 # to CAP of 100 suggested in REFMAN to avoid OOM
key_cache_division_limit=50 # from 100 percent for HOT/WARM use split
key_cache_age_threshold=64800 # from 300 seconds to reduce key_reads of 14M
key_cache_block_size=16384 # from 1024 to free more space when needed
query_cache_min_res_unit=512 # from 4096 to store more QC results, reduce prunes
table_open_cache=10000 # from 5000 to support more of 25K opened_tables
innodb_purge_threads=2 # from 1 to reduce contention for purge activities
tmp_table_size 和 max_heap_table_size 增加 10%，以减少 40% 的磁盘空间。