Fine tuning my.cnf

[DaveL]

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]

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]

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.

Cheers,
Shaun

 

[Floren]

@Clickfinity +1. IMO, tuning MySQL is a long process.
You cannot do it with a perl script or copy/paste from Google.

 

[DaveL]

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.

Cheers,
Shaun

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

 

[DaveL]

Hi,
Just wondered if anyone would be able to help me with a my.cnf based on the above server details please

 

[TPerry]

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).

[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/

 

[Floren]

NOTE: I run it on a non-standard port.

1306 is a standard port:

# 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).

 

[TPerry]

1306 is a standard port:

# 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.

 

[ARealRiley]

https://github.com/major/MySQLTuner-perl

 

[TPerry]

https://github.com/major/MySQLTuner-perl

That is good for a general guide.... but it still won't let you fine tune it.