由买买提看人间百态

boards

本页内容为未名空间相应帖子的节选和存档,一周内的贴子最多显示50字,超过一周显示500字 访问原贴
Database版 - 大虾帮忙看看MySQL的设置
相关主题
mysql rebuild index very slowhelp: questions about MySql
MySQL的InnoDB表不支持full text index 怎么办?大批量文本的存储与查询,用什么数据库比较合适?
MYSQL 的LOG SIZE怎么在不停 变大问个基础的理论问题----数据库的基本差异
MySQL row selection questionOracle to buy SUN? (转载)
求救,升级mysql后table not existMeet some of the MySQL geeks at SANTA CLARA
MySQL concat 使用一问MySQL DBA 的前途
why this full-text search doenst work properly?mysql innodb 文件转换
MySQL Server is Open Source, even Backup extensions(ZZ)急问一个数据库的问题
相关话题的讨论汇总
话题: ok话题: innodb话题: buffer话题: metrics话题: myisam
进入Database版参与讨论
1 (共1页)
c**t
发帖数: 2744
1
在inMotion租了个eLite Dedicated Server: 3.7G Turbo 4C/8T; 16GB RAM and
2X250 GB SSD. 想跑个小的MySQL,支持800人同时在线,主要是读。总感觉m
ysql的设置不对。哪位大虾帮忙指点一下,怎么优化设置?
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
2
问题挂了好几天,没人回答?
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. 想跑个小的MySQL,支持800人同时在线,主要是读。总感觉m
: ysql的设置不对。哪位大虾帮忙指点一下,怎么优化设置?
: 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)
1 (共1页)
进入Database版参与讨论
相关主题
急问一个数据库的问题求救,升级mysql后table not exist
job openning-Test and Quality Engineer,MySQL Database Admin (转载)MySQL concat 使用一问
【签名】支持yunmeng办mysql讲座的why this full-text search doenst work properly?
MySQL table either insert or drop/truncate table running foreverMySQL Server is Open Source, even Backup extensions(ZZ)
mysql rebuild index very slowhelp: questions about MySql
MySQL的InnoDB表不支持full text index 怎么办?大批量文本的存储与查询,用什么数据库比较合适?
MYSQL 的LOG SIZE怎么在不停 变大问个基础的理论问题----数据库的基本差异
MySQL row selection questionOracle to buy SUN? (转载)
相关话题的讨论汇总
话题: ok话题: innodb话题: buffer话题: metrics话题: myisam