The best config my.cnf for VPS

I have found that many people face a lot of issues relating to MYSQL since their server is not configured properly to handle all the database load, they need to check their my.cnf file.

I’ve created mine as follows;

[mysqld]
bind-address=127.0.0.1
datadir=/mysql5/data
socket=/var/lib/mysql/mysql.sock
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=3000
max_user_connections=600
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=256M # 64M for 1GB, 128M for 2GB,
256 for 4GB
join_buffer_size=4M # 1M for 1GB, 2M for 2GB, 4M
for 4GB
max_allowed_packet=32M
table_cache=1024
sort_buffer_size=4M # 1M for 1GB, 2M for 2GB, 4M
for 4GB
read_buffer_size=4M # 1M for 1GB, 2M for 2GB, 4M
for 4GB
read_rnd_buffer_size=3072K # 768K for 1GB, 1536K for 2GB, 3072K
for 4GB
max_connect_errors=10
thread_concurrency=4
myisam_sort_buffer_size=128M # 32M for 1GB, 64M for 2GB, 128 for 4GB
skip-locking
server-id=1

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
bind-address=127.0.0.1
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192
SAFE_MYSQLD_OPTIONS=”–defaults-file=/etc/my.cnf
–log-slow-queries=/var/log/slow-queries.log”

#[mysqldump]
#quick
#max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=256M # 64M for 1GB, 128M for 2GB,
256M for 4GB
sort_buffer=256M # 64M for 1GB, 128M for 2GB,
256M for 4GB
read_buffer=64M # 16M for 1GB, 32M for 2GB,
64M for 4GB
write_buffer=64M # 16M for 1GB, 32M for 2GB,
64M for 4GB

[myisamchk]
key_buffer=256M # 64M for 1GB, 128M for 2GB,
256M for 4GB
sort_buffer=256M # 64M for 1GB, 128M for 2GB,
256M for 4GB
read_buffer=64M # 16M for 1GB, 32M for 2GB,
64M for 4GB
write_buffer=64M # 16M for 1GB, 32M for 2GB,
64M for 4GB

[mysqlhotcopy]
interactive-timeout

Leave a Reply

Your email address will not be published.