c**t
发帖数: 2744 | 1 在inMotion租了个eLite Dedicated Server: 3.7G Turbo 4C/8T; 16GB RAM and
2X250 GB SSD. 想跑个小的ＭｙＳＱＬ，支持８００人同时在线，主要是读。总感觉ｍ
ｙｓｑｌ的设置不对。哪位大虾帮忙指点一下，怎么优化设置？
07:27 PM/etc>cat my.cnf
[mysqld]
max_connections = 150
max_user_connections = 100
query_cache_size = 64M
skip-external-locking
key_buffer_size = 64M
max_allowed_packet=268435456
table_open_cache = 256
sort_buffer_size = 1M
net_buffer_length = 16K
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 32M
innodb_file_per_table
tmp_table_size = 64M
max_heap_table_size = 64M
thread_cache_size = 8
query_cache_type = on
# This setting allows the use of asynchronous I/O in InnoDB.
# The following files track usage of this resource:
# - /proc/sys/fs/aio-max-nr
# - /proc/sys/fs/aio-nr
# Default limit is 65536, of which a single instance of mysql uses 2661 out
of the box
innodb_use_native_aio = 1
default_storage_engine = MyISAM
default-storage-engine=MyISAM
open_files_limit=10000 |
c**t
发帖数: 2744 | |
t*****s
发帖数: 124 | 3 设置优化都是跟你的应用相关的
旁人很难给你很具体的建议
你最好把slow query log打开
然后针对任何超过2秒以上的query进行分析,优化设置以及index
另外，你的key buffer,sort buffer和read buffer的size都太小
这些buffer size跟query的performance关系很大
建议你尽量设置大一点，比如256M
如果内存够用，还可以考虑设置得更大一些，尤其是key buffer
【在 c**t 的大作中提到】
: 问题挂了好几天，没人回答？
|
d*****y
发帖数: 205 | 4 别太懒了，Google一下MySql optimization有很多文章可以参考。
【在 c**t 的大作中提到】
: 在inMotion租了个eLite Dedicated Server: 3.7G Turbo 4C/8T; 16GB RAM and
: 2X250 GB SSD. 想跑个小的ＭｙＳＱＬ，支持８００人同时在线，主要是读。总感觉ｍ
: ｙｓｑｌ的设置不对。哪位大虾帮忙指点一下，怎么优化设置？
: 07:27 PM/etc>cat my.cnf
: [mysqld]
: max_connections = 150
: max_user_connections = 100
: query_cache_size = 64M
: skip-external-locking
: key_buffer_size = 64M
|
c**t
发帖数: 2744 | 5 [OK] Currently running supported MySQL version 5.6.30
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------
------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM
+MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 170K (Tables: 17)
[--] Data in InnoDB tables: 28M (Tables: 107)
[!!] Total fragmented tables: 5
-------- 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: 10d 1h 51m 5s (214K q [0.246 qps], 18K conn, TX: 388M, RX: 33M)
[--] Reads / Writes: 93% / 7%
[--] Binary logging is disabled
[--] Physical Memory : 15.5G
[--] Max MySQL memory : 935.2M
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/
FAQ
[--] Other process memory: 283.4M
[--] Total buffers: 336.0M global + 3.5M per thread (150 max threads)
[--] P_S Max memory usage: 74M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 427.7M (2.69% of installed RAM)
[OK] Maximum possible memory usage: 935.2M (5.88% of installed RAM)
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/
FAQ
[OK] Overall possible memory usage with other process is compatible with
memory available
[OK] Slow queries: 0% (2/214K)
[OK] Highest usage of available connections: 3% (5/150)
[!!] Aborted connections: 8.27% (1541/18639)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (24 temp sorts / 28K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 2% (1K on disk / 43K total)
[OK] Table cache hit rate: 58% (227 open / 388 opened)
[OK] Open file limit used: 0% (90/10K)
[OK] Table locks acquired immediately: 100% (145K immediate / 145K locks)
-------- ThreadPool Metrics ------------------------------------------------
------------------------
[--] ThreadPool stat is disabled.
-------- Performance schema ------------------------------------------------
------------------------
[--] Performance schema is enabled.
[--] Memory used by P_S: 74.2M
[--] Sys schema isn't installed.
-------- MyISAM Metrics ----------------------------------------------------
------------------------
[!!] Key buffer used: 18.4% (12M used / 67M cache)
[OK] Key buffer size / total MyISAM indexes: 64.0M/186.0K
[OK] Read Key buffer hit rate: 99.9% (76K cached / 76 reads)
[!!] Write Key buffer hit rate: 27.2% (1K cached / 778 writes)
-------- AriaDB Metrics ----------------------------------------------------
------------------------
[--] AriaDB is disabled.
-------- InnoDB Metrics ----------------------------------------------------
------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/28.9M
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[!!] InnoDB Used buffer: 29.70% (2433 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 100.00% (3060923610 hits/ 3060923899
total)
[OK] InnoDB Write log efficiency: 98.03% (64578 hits/ 65879 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1301 writes)
-------- TokuDB Metrics ----------------------------------------------------
------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ----------------------------------------------------
------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------
------------------------
[--] Galera Synchronous replication: NO
[--] 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 [email protected]/* */
Reduce or eliminate unclosed connections and network issues
Variables to adjust:
query_cache_type (=0)
innodb_buffer_pool_instances (=1) |
