Can you help me to optimize my.cnf file ?

[Janyour]

Hello,

i have a dedicated MYSQL server with 256mb dedicated memory,
here is the content of my sql configuration file my.cnf :

[mysqld]

tmp_table_size=5M

query_cache_size=10M

thread_cache_size=5

max_connections=75

open_files_limit=900


skip-locking

key_buffer_size = 10M

max_allowed_packet = 1M

table_cache = 300

sort_buffer_size = 1M

#join_buffer_size = 1M //COMMENTED BY OVH TUNER SCRIPT

read_buffer_size = 512K

read_rnd_buffer_size = 256K

net_buffer_length = 2K

thread_stack = 128K

max_binlog_cache_size = 1M

max_join_size = 12M

max_seeks_for_key = 2M

max_write_lock_count = 512K

myisam_max_sort_file_size = 1M

########################

##Configuration Innodb##

##Uncomment the next line to disable Innodb

#skip-innodb

innodb_buffer_pool_size		 = 16M

innodb_additional_mem_pool_size = 2M

innodb_log_file_size		    = 10M

innodb_log_buffer_size		  = 4M

innodb_flush_log_at_trx_commit=1


######### ADDED BY OVH TUNER SCRIPT #######

#skip-innodb

open_files_limit = 2K

skip-innodb

join_buffer_size = 4M

tmp_table_size = 320M

max_heap_table_size = 2G

table_open_cache = 9600

max_connections = 129

wait_timeout = 60

interactive_timeout = 60

thank you for helping me to optimize it :)
regards

[Weppa333]

this has to be tuned according to two things
- your number of CPUs and your RAM
- the volume of your DB ( especially the size of the indexes)

And... It's a job :smile:

The file here is well balanced for a small single CPU server.

tmp_table& heap table size is ridiculously high : if heavily loaded, it would mean running queries out of your swap...
tmp_table bigger than avail RAM undermines performance.

[Weppa333]

I would increase
read_rnd_buffer_size = 256K, eg to 512K.

it is one of the most useful things in mysql after the query cache, imho.

[Janyour]

thank you, i have 256mb dedicated mysql memory,
my db sql file is about 367,8 Mio ! (about 250 users per hour)

[raindog308]

You might run mysqltuner.pl (that is alsothe script's URL)

[Janyour]

thank you raindog i will try that tool, its possible on a shared host ?

[Weppa333]

Looking at your file, it's probably created my mysqltuner by OVH.
Honnestly, for such a small install, what you've got over there in the #1 message is enough.

Most of the MySQL "tuning" takes part in multi CPU land, where it's kinda hard to differenciate between an actual CPU and a virtual one, and tune accordingly... Other than that, most mysql install are so overtuned to the point they give slower results than with a default my.cnf. my 2 cents :)