• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Fine tuning my.cnf

DaveL

Well-known member
#1
Just moved my first Xenforo site over to my new VPS and it seems pretty sluggish despite the VPS having twice as much as my last one.

Checked the my.cnf file and this is what I got back

root@server1 [~]# cat /etc/my.cnf
[mysqld]
innodb_file_per_table=1
default-storage-engine=MyISAM
I cant believe at how empty it is. Is there a default my.cnf I can use?
 

DaveL

Well-known member
#2
Ive seen this in another thread:

[mysqld]

# GENERAL #
safe-show-database
skip-name-resolve
skip-host-cache
max_connections = 600
wait_timeout = 3600
connect_timeout = 800
max_allowed_packet = 64M
max_connect_errors = 10
bulk_insert_buffer_size = 8M
query_prealloc_size = 16384
query_alloc_block_size = 16384
thread_concurrency = 4
tmpdir = /usr/tmp
long_query_time = 5
ft_min_word_len = 2
log-slow-queries=/var/lib/mysql/slow.log


# MYISAM STUFF #
myisam_sort_buffer_size = 32M


# CACHES AND LIMITS #
tmp_table_size = 128M
max_heap_table_size = 32M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
table_open_cache = 1800
thread_cache_size = 384
key_buffer_size = 1024M
query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1


# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
innodb_buffer_pool_size = 2G

Is it a case of just inputting the above details via SSH?
 

CTXMedia

Formerly CyclingTribe
#3
Copying and pasting a my.cnf isn't really the way to go - you need to tailor it to your available server resources.

Post your OS, RAM, PHP and MySQL versions and you'll hopefully get some good "base level" suggestions you can use. (y)

Cheers,
Shaun :D
 

DaveL

Well-known member
#5
Copying and pasting a my.cnf isn't really the way to go - you need to tailor it to your available server resources.

Post your OS, RAM, PHP and MySQL versions and you'll hopefully get some good "base level" suggestions you can use. (y)

Cheers,
Shaun :D
Hi Shaun,

These are my details.

Cent OS 6.4 x86
2GB Ram
Apache 2.2.24
PHP - 5.4.15
MySQL - 5.5.30

I did see that xcache was available to add on via Easy Apache, however I wasnt too sure how to configure that so I havent installed it yet.

Hope the above helps. Really appreciate your help.

Dave
 

Tracy Perry

Well-known member
#7
Hi,
Just wondered if anyone would be able to help me with a my.cnf based on the above server details please :)
This worked for me (could probably be fine tuned). NOTE: I run it on a non-standard port (where the x's are).
Code:
[client]
port        = xxxxxxxxxx
socket        = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket        = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
port        = xxxxxxxxx
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
skip-external-locking
bind-address        = 127.0.0.1
thread_stack        = 192K
thread_cache_size      = 8
key_buffer_size        = 32M
myisam-recover        = BACKUP
max_connections        = 100
table_cache            = 128000
thread_concurrency    = 10
query_cache_limit    = 54M
query_cache_size        = 64M
join_buffer_size    = 3M
open_files_limit    = 8192
expire_logs_days    = 10
max_binlog_size        = 100M
tmp_table_size                = 48M
max_heap_table_size            = 32M
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size          = 128M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 750M
log_error                      = /var/log/mysql/mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                = 1
slow_query_log_file            = /var/log/mysql/mysql-slow.log
[mysqldump]
quick
quote-names
max_allowed_packet    = 16M
[mysql]
#no-auto-rehash    # faster start of mysql but no tab completition
[isamchk]
key_buffer        = 16M
!includedir /etc/mysql/conf.d/
 
Last edited:

Tracy Perry

Well-known member
#9
1306 is a standard port:
Code:
# grep 1306 /etc/services
re-conn-proto  1306/tcp                # RE-Conn-Proto
re-conn-proto  1306/udp                # RE-Conn-Proto
Is common to use high numbers when you assign custom ports (i.e. 33306).
I'm aware of that... I changed the port to something I don't use (don't like to put that info out on the web - even tho' there are ways to find it no need to advertise it). Should have probably put xxxxxx instead. ;)

EDIT: Original post edited to reflect that. :D