我怎样才能提高我的服务器内存使用率：安装 mysqltuner 后，服务器内存 = 1GB，我得到以下结果：
[root@server ~]# perl mysqltuner.pl
>> MySQLTuner 1.5.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.44
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 18M (Tables: 185)
[--] Data in InnoDB tables: 19M (Tables: 271)
[--] Data in MEMORY tables: 0B (Tables: 5)
[!!] Total fragmented tables: 22
-------- Security Recommendations -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[!!] User 'admin_jmu@%' hasn't specific host restriction.
[!!] User 'admin_mcstd@%' hasn't specific host restriction.
[!!] User 'admin_melody@%' hasn't specific host restriction.
[!!] There is not basic password file list !
-------- Performance Metrics -------------------------------------------------
[--] Up for: 9h 21m 32s (322K q [9.576 qps], 68K conn, TX: 663M, RX: 16M)
[--] Reads / Writes: 87% / 13%
[--] Binary logging is disabled
[--] Total buffers: 208.0M global + 4.5M per thread (400 max threads)
[OK] Maximum reached memory usage: 244.0M (24.49% of installed RAM)
[!!] Maximum possible memory usage: 2.0G (201.52% of installed RAM)
[OK] Slow queries: 0% (1/322K)
[OK] Highest usage of available connections: 2% (8/400)
[OK] Aborted connections: 0.00% (3/68352)
[!!] Key buffer used: 19.1% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/959.0K
[OK] Read Key buffer hit rate: 99.8% (166K cached / 273 reads)
[OK] Write Key buffer hit rate: 99.2% (237 cached / 2 writes)
[OK] Query cache efficiency: 91.1% (96K cached / 105K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Temporary tables created on disk: 60% (765 on disk / 1K total)
[OK] Thread cache hit rate: 99% (8 created / 68K connections)
[OK] Table cache hit rate: 98% (351 open / 358 opened)
[OK] Open file limit used: 3% (320/8K)
[OK] Table locks acquired immediately: 99% (10K immediate / 10K locks)
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/19.2M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 20.19% (1654 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 99.96% (4013636 hits/ 4015282 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 356 writes)
-------- 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
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
my.cnf 文件包含以下内容：
[mysqld]
max_connections = 400
key_buffer = 32M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
local-infile=0
我该如何更改 my.cnf？谢谢
答案1
您可以在这里看到您的主要问题：
[--] Total buffers: 208.0M global + 4.5M per thread (400 max threads)
根据您的配置，每个线程最多可占用 4.5 MB RAM，并且最多有 400 个线程：
max_connections = 400
将其max_connections降至 100 或以下以降低最大内存使用量。
答案2
你想实现什么？
对于 SQL 来说，您应该为其提供尽可能多的 RAM，它将使用这些 RAM 来缓存索引，对于 InnoDB 来说，还会使用数据页，这意味着整体性能会更好，重复查询的磁盘读取次数也会更少。
我对此不是 100% 确定，但我认为 MyISAM 引擎仅将索引缓存到内存中，在这种情况下，如果您想获得快速运行的 MySQL 数据库，InnoDB 通常是更好的选择。