分享一个低配VPS下运行的mysql配置文件
在各种内存CPU核心只有1/2核,内存只有512M/1G的vps下,内存、CPU、硬盘都不是太充裕。因此主要思路是,禁止吃内存大户innodb引擎,默认使用MyISAM。禁止吃硬盘大户log-bin,再调节一些参数到合适的数值。
[mysql] # CLIENT # port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld] # GENERAL # user = mysql default-storage-engine = MyISAM default-tmp-storage-engine = MYISAM socket = /var/run/mysqld/mysqld.sock pid-file = /var/run/mysqld/mysqld.pid bind-address = 127.0.0.1 lc-messages-dir = /usr/share/mysql/ skip-external-locking # MyISAM # key-buffer-size = 16M myisam-recover = FORCE,BACKUP # SAFETY # max-allowed-packet = 16M max-connect-errors = 256 skip-name-resolve sysdate-is-now = 1 # DATA STORAGE # datadir = /var/lib/mysql/ # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 0 query-cache-size = 0 max-connections = 64 thread-stack = 192K thread-cache-size = 4 thread-concurrency = 2 open-files-limit = 1028 table-definition-cache = 256 table-open-cache = 64 # INNODB # loose-skip-innodb loose-innodb-trx = 0 loose-innodb-locks = 0 loose-innodb-lock-waits = 0 loose-innodb-cmp = 0 loose-innodb-cmp-per-index = 0 loose-innodb-cmp-per-index-reset = 0 loose-innodb-cmp-reset = 0 loose-innodb-cmpmem = 0 loose-innodb-cmpmem-reset = 0 loose-innodb-buffer-page = 0 loose-innodb-buffer-page-lru = 0 loose-innodb-buffer-pool-stats = 0 loose-innodb-metrics = 0 loose-innodb-ft-default-stopword = 0 loose-innodb-ft-inserted = 0 loose-innodb-ft-deleted = 0 loose-innodb-ft-being-deleted = 0 loose-innodb-ft-config = 0 loose-innodb-ft-index-cache = 0 loose-innodb-ft-index-table = 0 loose-innodb-sys-tables = 0 loose-innodb-sys-tablestats = 0 loose-innodb-sys-indexes = 0 loose-innodb-sys-columns = 0 loose-innodb-sys-fields = 0 loose-innodb-sys-foreign = 0 loose-innodb-sys-foreign-cols = 0 # LOGGING # log-error = /var/log/mysql/error.log log-queries-not-using-indexes = 0 long-query-time = 1 slow-query-log = 1 slow-query-log-file = /var/log/mysql/slow.log
重启后mysql的内存占用大概为70M+,其中和内存关系较大的是key-buffer-size、tmp-table-size和thread-cache-size属性,作用分别为缓存MyISAM的索引、执行Group By之类语言时使用的临时表内存空间、查询线程。
配置的使用环境为percona server 5.6,其他版本的mysql没有测试。