MattW
Well-known member
Just wondering if someone would be able to take a look at my current MySQL configuration, and possibly suggest any improvements? I've been managing my own servers for the last 5 years, and have never really managed to get a properly tweaked my.cnf.
Anyway, config:
current my.cnf (this was auto adjusted by cpanel when I upgraded to 11.32 and MySQL 5.5)
mysqltuner output
I'm running on a VPS with 3GB dedicated RAM, 2GB swap, Centos 5.7 32bit, WHM/CPANEL 11.32, NGINXadmin, apache 2.2.22 and memcached.
MySQL on my VPS has previously not use INNODB, as all the phpBB tables were MyISAM.
Thanks,
Matt
Anyway, config:
Code:
php -v
PHP 5.3.10 (cli) (built: Feb 25 2012 09:05:55)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies
with eAccelerator v0.9.6.1, Copyright (c) 2004-2010 eAccelerator, by eAccelerator
with the ionCube PHP Loader v4.0.12, Copyright (c) 2002-2011, by ionCube Ltd.
with Suhosin v0.9.33, Copyright (c) 2007-2012, by SektionEins GmbH
Code:
mysql -v
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 42594
Server version: 5.5.21-cll MySQL Community Server (GPL)
current my.cnf (this was auto adjusted by cpanel when I upgraded to 11.32 and MySQL 5.5)
Code:
[myisamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
write_buffer=2M
[mysqld]
log-bin=mysql-bin
myisam_sort_buffer_size=64M
read_rnd_buffer_size=8M
expire_logs_days=7
query_cache_size=128M
thread_cache_size=8
max_allowed_packet=1M
skip-federated
table_definition_cache=2048
local-infile=0
table_cache=2048
max_connections=60
read_buffer_size=2M
slow_query_log=1
binlog-do-db=british_mods
binlog-do-db=z22se_forum
binlog-do-db=z22se_newshop
binlog-do-db=pure1_shop
server-id=1
slow_query_log_file="/var/log/slow_queries.log"
thread_concurrency=16
sort_buffer_size=2M
port=3306
join_buffer_size=3M
key_buffer=384M
query_cache_limit=4M
socket="/var/lib/mysql/mysql.sock"
skip-external-locking
query-cache-type=1
long_query_time=5
default-storage-engine=MyISAM
innodb_file_per_table=1
[client]
port=3306
socket="/var/lib/mysql/mysql.sock"
[mysqldump]
max_allowed_packet=16M
quick
[mysql]
no-auto-rehash
[isamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
write_buffer=2M
[mysqlhotcopy]
interactive-timeout
mysqltuner output
Code:
>> MySQLTuner 1.0.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.21-cll
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 393M (Tables: 674)
[--] Data in InnoDB tables: 224M (Tables: 278)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 3M (Tables: 6)
[!!] Total fragmented tables: 103
-------- Performance Metrics -------------------------------------------------
[--] Up for: 23h 40m 25s (947K q [11.116 qps], 42K conn, TX: 2B, RX: 258M)
[--] Reads / Writes: 47% / 53%
[--] Total buffers: 672.0M global + 15.2M per thread (60 max threads)
[OK] Maximum possible memory usage: 1.5G (51% of installed RAM)
[OK] Slow queries: 0% (7/947K)
[OK] Highest usage of available connections: 15% (9/60)
[OK] Key buffer size / total MyISAM indexes: 384.0M/670.4M
[OK] Key buffer hit rate: 99.5% (18M cached / 91K reads)
[OK] Query cache efficiency: 60.0% (331K cached / 553K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (6 temp sorts / 29K sorts)
[!!] Joins performed without indexes: 1666
[OK] Temporary tables created on disk: 11% (7K on disk / 63K total)
[OK] Thread cache hit rate: 99% (9 created / 42K connections)
[!!] Table cache hit rate: 0% (1K open / 261K opened)
[OK] Open file limit used: 39% (1K/4K)
[OK] Table locks acquired immediately: 99% (692K immediate / 692K locks)
[!!] InnoDB data size / buffer pool: 224.5M/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
join_buffer_size (> 3.0M, or always use indexes with joins)
table_cache (> 2048)
innodb_buffer_pool_size (>= 224M)
I'm running on a VPS with 3GB dedicated RAM, 2GB swap, Centos 5.7 32bit, WHM/CPANEL 11.32, NGINXadmin, apache 2.2.22 and memcached.
MySQL on my VPS has previously not use INNODB, as all the phpBB tables were MyISAM.
Thanks,
Matt