請為 my.cnf 推薦更改

我本身沒有任何性能問題，但如果可能的話，我想提高我的網站的性能。對於如何改進 my.cnf 的任何建議，我將不勝感激。我只有一台伺服器，並非專門用於數據庫。我的站點是執行 XenForo 軟體的論壇。

伺服器規格：

• CPU：Intel(R) Xeon(R) CPU E5-1620 v3 @ 3.50GHz（4 核，8 執行緒）
• 記憶體：64 GB DDR4
• CentOS 6.7
• PHP 版本 5.6.13
• LiteSpeed 網路伺服器 5.0.7
• 瑪麗亞數據庫 10.0.21
• 記憶體記憶體

目前的 my.cnf：

[mysqld]
local-infile=0
tmpdir = /var/mysqltmp
character-set-server=utf8
bind-address=127.0.0.1
skip-federated
skip-archive
back_log = 75
max_connections = 300
key_buffer_size = 512M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 1024M
join_buffer_size = 64K
read_buffer_size = 64K
sort_buffer_size = 128K
table_definition_cache = 40000
table_open_cache = 40000
thread_cache_size = 64
wait_timeout = 120
connect_timeout = 10
tmp_table_size = 512M
max_heap_table_size = 512M
max_allowed_packet=536870912
max_seeks_for_key = 1000
group_concat_max_len = 102400
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 256K
bulk_insert_buffer_size = 8M
query_cache_limit = 0
query_cache_size = 0
query_cache_type = 0
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
innodb_purge_threads=1
innodb_doublewrite = 1
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_instances = 16
innodb_buffer_pool_size = 16G

innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1

innodb_io_capacity = 4600
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_flush_neighbors = 0
open_files_limit=14000

免費-m

            total       used       free     shared    buffers     cached
Mem:         64321      63925        395        270       1235      45936
-/+ buffers/cache:      16753      47567
Swap:         4204        451       3753

根@伺服器

$$ ~ $$# perl mysqltuner.pl

>>  MySQLTuner 1.6.1 - Major Hayden <major@mhtx.net>
>>  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 10.0.21-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -ARCHIVE +Aria +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MyISAM
[--] Data in MyISAM tables: 1G (Tables: 113)
[--] Data in InnoDB tables: 3G (Tables: 1535)
[--] Data in MEMORY tables: 2M (Tables: 33)
[!!] Total fragmented tables: 238

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[--] There is 605 basic passwords in the list.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15d 23h 22m 44s (58M q [42.243 qps], 5M conn, TX: 492B, RX: 50B)
[--] Reads / Writes: 65% / 35%
[--] Binary logging is disabled
[--] Total buffers: 17.1G global + 800.0K per thread (300 max threads)
[OK] Maximum reached memory usage: 17.2G (27.31% of installed RAM)
[OK] Maximum possible memory usage: 17.4G (27.65% of installed RAM)
[OK] Slow queries: 0% (78/58M)
[OK] Highest usage of available connections: 7% (22/300)
[OK] Aborted connections: 0.00%  (158/5240320)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (34K temp sorts / 3M sorts)
[!!] Joins performed without indexes: 167178
[OK] Temporary tables created on disk: 8% (196K on disk / 2M total)
[OK] Thread cache hit rate: 99% (22 created / 5M connections)
[!!] Table cache hit rate: 2% (2K open / 66K opened)
[OK] Open file limit used: 0% (295/80K)
[OK] Table locks acquired immediately: 99% (89M immediate / 89M locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.5% (99M used / 536M cache)
[OK] Key buffer size / total MyISAM indexes: 512.0M/545.4M
[OK] Read Key buffer hit rate: 99.9% (51M cached / 51K reads)
[!!] Write Key buffer hit rate: 47.6% (14M cached / 7M writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 16.0G/3.2G
[OK] InnoDB buffer pool instances: 16
[!!] InnoDB Used buffer: 18.64% (195406 used/ 1048560 total)
[OK] InnoDB Read buffer efficiency: 100.00% (25814174259 hits/ 25814306598 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5363748 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
   Run OPTIMIZE TABLE to defragment tables for better performance
   Adjust your join queries to always utilize indexes
   Increase table_open_cache gradually to avoid file descriptor limits
   Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
   Beware that open_files_limit (80311) variable
   should be greater than table_open_cache ( 40000)
Variables to adjust:
   query_cache_size (>= 8M)
   join_buffer_size (> 64.0K, or always use indexes with joins)
   table_open_cache (> 40000)

根@伺服器

$$ ~ $$# tuning-primer.sh

       -- MYSQL PERFORMANCE TUNING PRIMER --
            - By: Matthew Montgomery -

MySQL Version 10.0.21-MariaDB x86_64

Uptime = 15 days 23 hrs 23 min 37 sec
Avg. qps = 42
Total Questions = 58307091
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/10.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 78 out of 58307112 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/10.0/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 64
Current threads_cached = 21
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 300
Current threads_connected = 1
Historic max_used_connections = 22
The number of used connections is 7% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

No InnoDB Support Enabled!

MEMORY USAGE
Max Memory Ever Allocated : 16.53 G
Configured Max Per-thread Buffers : 234 M
Configured Max Global Buffers : 16.51 G
Configured Max Memory Limit : 16.74 G
Physical Memory : 62.81 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 545 M
Current key_buffer_size = 512 M
Key cache miss rate is 1 : 1003
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

SORT OPERATIONS
Current sort_buffer_size = 128 K
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
/usr/local/bin/tuning-primer.sh: line 402: export: `2097152': not a valid identifier
Current join_buffer_size = 68.00 K
You have had 167181 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 80311 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 40000 tables
Current table_definition_cache = 40000 tables
You have a total of 1767 tables
You have 2015 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 512 M
Current tmp_table_size = 512 M
Of 2228513 temp tables, 8% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 64 K
Current table scan ratio = 114 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 7207
Your table locking seems to be fine

謝謝你的幫助！

在對您的處理了解不多的情況下，除了全面的良好設置之外，跳出來的第一件事就是正在進行的未索引連接的數量。找到它們並修復它們。

我建議使用 pt-query-digest。類似於以下內容：

set global slow_query_log = 1;
set global slow_query_log_file = '/var/log/mysql-slow.log';
set global log_queries_not_using_indexes=1;

在您收集了所有常用查詢之後。（留意你的日誌大小）

pt-query-digest /var/log/mysql-slow.log

這應該會給你更多的洞察力。

這些非常大；你有 40K 表嗎？：

table_definition_cache = 40000
table_open_cache = 40000

禁用在生產環境中通常很好：

[!!] Query cache is disabled

您的慢速日誌輸出將對此有所幫助：

[!!] Joins performed without indexes: 167178

許多調諧器的抱怨暗示你有比你“需要”更多的記憶體。

虛假; 不要這樣做：

Run OPTIMIZE TABLE to defragment tables for better performance

不; 它已經太大了：

Increase table_open_cache gradually to avoid file descriptor limits

否和否：

query_cache_size (>= 8M)
table_open_cache (> 40000)

不，它只會使輸出混亂：

You should enable "log-queries-not-using-indexes"

這些都是危險的高。複雜的 SELECT 可以創建一個或多個 tmp 表，從而導致大量記憶體使用。100M會更安全。

Current max_heap_table_size = 512 M
Current tmp_table_size = 512 M

如需更多分析，請提供

SHOW VARIABLES;
SHOW GLOBAL STATUS;

慢日誌（根據 naschoff）可能會顯示隨著數據集和使用量的增長可能會出現問題的查詢。

引用自：https://dba.stackexchange.com/questions/118394