Sample my.cnf for VPs

[bdwebservices]

Hello,
Can any help me MySQL optimization. Is there any sample my.cnf for 12GB RAM?
Thanks in advance,

[JPC-Sabrina]

Did you get any help on this yet? A couple of our regular forum members are usually in later in the afternoon and they may be able to help you out with your question. They are the technical wizards on the forums. If you are still in need of help then you can always open a support ticket and our technical support team can assist you.

[gohighvoltage]

Do you run vbulletin?

[bdwebservices]

no, I will run lot of wordpress and joomla sites

[gohighvoltage]

Can you post what you have now?

[bdwebservices]

Here is current my.cnf with 6GB Ram/12GB

[mysqld]
max_connections = 100
key_buffer = 32M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 7000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 6M
query_cache_size = 12M
query_cache_type = 1
tmp_table_size = 16M
local-infile=0
log-slow-queries
safe-show-database

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

[gohighvoltage]

Here is current my.cnf with 6GB Ram/12GB

[mysqld]
max_connections = 100
key_buffer = 32M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 7000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 6M
query_cache_size = 12M
query_cache_type = 1
tmp_table_size = 16M
local-infile=0
log-slow-queries
safe-show-database

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

I am sure others will have some feedback, but I would start by changing these (some you will need to do by trial and error)
There is no "one right" answer for Mysql its very dependant on what's running on it, how big the tables, how well optimized they are etc..

Suggestion:

[mysqld]
max_connections = 1000
key_buffer = 256M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 286
interactive_timeout = 60
wait_timeout = 120
connect_timeout = 10
max_allowed_packet = 64M
max_connect_errors = 100000
query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1
tmp_table_size = 64M
local-infile=0
log-slow-queries
safe-show-database

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 256M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout



------
Note:
If you get mysql server gone away error messages, then keep increasing wait_timeout value in my.cnf by 60 second increments, then restart mysql after my.cnf changes for it to take effect. Test for a few days and see if you get less or eliminate that error message. If it still occurs, then keep repeating the 60 second increment until the message goes away.

[gohighvoltage]

Make sure you look at my post again above, I changed it.

[bdwebservices]

I am sure others will have some feedback, but I would start by changing these (some you will need to do by trial and error)
There is no "one right" answer for Mysql its very dependant on what's running on it, how big the tables, how well optimized they are etc..

Suggestion:

[mysqld]
max_connections = 1000
key_buffer = 256M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 286
interactive_timeout = 60
wait_timeout = 120
connect_timeout = 10
max_allowed_packet = 64M
max_connect_errors = 100000
query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1
tmp_table_size = 64M
local-infile=0
log-slow-queries
safe-show-database

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 256M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout



------
Note:
If you get mysql server gone away error messages, then keep increasing wait_timeout value in my.cnf by 60 second increments, then restart mysql after my.cnf changes for it to take effect. Test for a few days and see if you get less or eliminate that error message. If it still occurs, then keep repeating the 60 second increment until the message goes away.

Hello,
Thank you!!
Now new my.cnf is:

[mysqld]
max_connections = 1000
key_buffer = 32M
key_buffer_size= 256M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 286
interactive_timeout = 60
wait_timeout = 120
connect_timeout = 10
max_allowed_packet = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 32M
max_connect_errors = 100000
query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1
thread_concurrency=4
tmp_table_size = 16M
local-infile=0

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 256M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

-bash-3.2# ./mysqltuner.pl
Variables to adjust:
innodb_buffer_pool_size (>= 143M)

is it ok now?
Best regards,
Aziz

[gohighvoltage]

Looks good! Give that a shot and let me know how it works!

[thecoalman]

You have a VPS with 12GB of RAM? Are you sure that is correct?

I'm no expert on MySQL but have researched it quite a bit. You'll find a lot of sample configurations around the internet for various setups based on the resources available to you but ultimately there is no "best" or one size fits all optimization.