我已经多次优化了我的 Mysql 服务器，但现在我想听听您的建议。
mysqltuner：
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.52-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 26G (Tables: 36)
[--] Data in InnoDB tables: 221M (Tables: 35)
[!!] Total fragmented tables: 41
-------- Security Recommendations -------------------------------------------
[!!] User '@localhost' has no password set.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 78d 11h 57m 53s (1B q [151.485 qps], 1M conn, TX: 336B, RX: 218B)
[--] Reads / Writes: 60% / 40%
[--] Total buffers: 2.2G global + 8.2M per thread (300 max threads)
[OK] Maximum possible memory usage: 4.6G (39% of installed RAM)
[OK] Slow queries: 0% (55K/1B)
[OK] Highest usage of available connections: 69% (209/300)
[OK] Key buffer size / total MyISAM indexes: 2.0G/6.1G
[OK] Key buffer hit rate: 98.2% (2B cached / 47M reads)
[OK] Query cache efficiency: 28.9% (79M cached / 276M selects)
[!!] Query cache prunes per day: 102031
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3M sorts)
[OK] Temporary tables created on disk: 25% (1M on disk / 3M total)
[OK] Thread cache hit rate: 98% (16K created / 1M connections)
[!!] Table cache hit rate: 7% (330 open / 4K opened)
[OK] Open file limit used: 14% (149/1K)
[OK] Table locks acquired immediately: 99% (489M immediate / 490M locks)
[!!] InnoDB data size / buffer pool: 221.3M/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 20M)
table_cache (> 400)
innodb_buffer_pool_size (>= 221M)
调优入门：
SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 1.000000 sec.
You have 56239 out of 1027500028 that take longer than 1.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is enabled
Binlog sync is not enabled, you could loose binlog records during a server crash
WORKER THREADS
Current thread_cache_size = 10
Current threads_cached = 7
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 = 26
Historic max_used_connections = 209
The number of used connections is 69% of the configured maximum.
Your max_connections variable seems to be fine.
INNODB STATUS
Current InnoDB index space = 28 M
Current InnoDB data space = 221 M
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
MEMORY USAGE
Max Memory Ever Allocated : 3.78 G
Configured Max Per-thread Buffers : 2.42 G
Configured Max Global Buffers : 2.09 G
Configured Max Memory Limit : 4.52 G
Physical Memory : 11.73 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 6.09 G
Current key_buffer_size = 1.95 G
Key cache miss rate is 1 : 54
Key buffer free ratio = 0 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.
QUERY CACHE
Query cache is enabled
Current query_cache_size = 20 M
Current query_cache_used = 6 M
Current query_cache_limit = 4 M
Current Query cache Memory fill ratio = 33.45 %
Current query_cache_min_res_unit = 4 K
Query Cache is 30 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 2 M
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 2.00 M
You have had 12284 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 = 1024 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 = 400 tables
Current table_definition_cache = 256 tables
You have a total of 94 tables
You have 330 open tables.
The table_cache value seems to be fine
TEMP TABLES
Current max_heap_table_size = 128 M
Current tmp_table_size = 128 M
Of 2988791 temp tables, 25% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.
TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 5 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 5129
Your table locking seems to be fine
以及内存信息：
[air@prod01 ~]$ free -m
total used free shared buffers cached
Mem: 12011 11926 85 0 406 6395
-/+ buffers/cache: 5123 6887
Swap: 1027 0 1027
查询信息：
mysql> show status like "Qc_%";
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 3447 |
| Qcache_free_memory | 13968768 |
| Qcache_hits | 79867788 |
| Qcache_inserts | 163036705 |
| Qcache_lowmem_prunes | 8012726 |
| Qcache_not_cached | 33127499 |
| Qcache_queries_in_cache | 3758 |
| Qcache_total_blocks | 11259 |
+-------------------------+-----------+
我已将 table_open_cache 值从 330 增加到 400。
我应该做些什么来获得更好的表现吗？
谢谢
答案1
您应该做的第一步是安装一些性能图形软件并开始使用它来绘制服务器的重要统计数据。
以下两者之一可以完成这项工作：更好的 Cacti 模板或者Appaloosa Zabbix 模板
一旦完成，您就可以看到您的正常工作量是什么样的，然后开始改变值并查看它们产生的影响。
如果您不衡量自己的表现，那么您所做的任何改变都只不过是猜测而已。
答案2
我已经对 MyISAM 进行了大部分优化，（我的大多数数据库的写入率都小于 3%）但是，我确实有一些常规的 MySQL 优化供您考虑。根据您的情况，按优先顺序排列。
- 碎片表总数：41
考虑到您只有 71 个表，这个数字非常高。由于写入与读取的比例如此之高，因此在您的案例中，这解释了原因。我会优化您的所有数据库。这对 MyISAM 表最有效，但它们构成了您的大部分数据。不要中途停止此命令！
mysqlcheck --all-databases --optimize
- 每日查询缓存修剪次数：102031
这太过分了，几乎可以解释为什么您的查询缓存效率可以更高。按照 mysqltuner 的建议，将 20M 提高到 64M 左右。（如果这太激进，您可以降低它，但最终可能会过于保守）在 my.cnf 中的 mysqld 部分下查找此行，然后将 20M 更改为 64M
query_cache_size = 64M
- innodb_buffer_pool_size（>= 221M）
您需要一个比表数据大 10% 的缓冲池。因此，这个值更接近于 245，但我实际上会将其设置为 250（使其均匀，留出一些空间）将您的行从 221M 更改为 250M。
innodb_buffer_pool_size = 250M
这就是我可以放心推荐的，无需更多信息，我猜你也需要提高 table_cache。但我不确定什么是最好的，你可能想在一段时间内检查你打开的表。
另外，请确保写入繁重的表使用 InnoDB，读取繁重的表我会使用 MyISAM。有些应用程序只使用 InnoDB 的功能，应该考虑这一点。有些人会因为不同的原因而一直争论使用哪种引擎。这正是我要做的。