Need best mysql configuration of My.cnf

[rahulephp]

Hi all,

 

First of all I am new with server using.

Below my virtual private server details:

 

• Processor : 2.8 GHz Dual Core
  
• Processor Cores: 2
  
• RAM Memory : 4GB
  
• Apache Version: Apache/2.2.3 (CentOS)
  
• PHP Version 5.1.6
  
• MySQL Version: 5.0.77
  
• HDD: 100GB
  
• Bandwidth: Unlimited
  

 

Due to 3GB of database size, server may not respond some times and working very slow even if good server configuration.

Website is running good on our local computers.

I think, I need to change the mysql configuration settings for online server.

 

Please help me out to set the best configuration of my.cnf for above server specially following settings:

(Table type is MyISAM)

• connect_timeout = 20
  
• key_buffer_size = 64M
  
• max_allowed_packet = 2G
  
• table_cache = 100M
  
• wait_timeout= 360
  
• interactive_timeout = 360
  
• key_buffer = 16M
  
• sort_buffer_size = 512K
  
• net_buffer_length = 8K
  
• read_buffer_size = 256K
  
• read_rnd_buffer_size = 512K
  
• myisam_sort_buffer_size = 8M
  

 

Thanks in advance

[trq]

The specs you have provided do not read at all like any virtual private server.

[rahulephp]

Here is server specs with some modifications.

http://order.1and1.com/xml/order/VirtualServerXL

[Mchl]

key_buffer_size = 64M

Start by increasing that to 512MB-1024M, see what's the effect on your performance (might improve database, but impact negatively on other services)

 

table_cache = 100M

???? do you have 100 million tables in your databse?

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_table_cache

 

key_buffer = 16M

what's that? documentation doesn't mention such variable

 

sort_buffer_size

See here for some tips:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_sort_buffer_size

 

read_buffer_size

Again, this is something you must consider yourself

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_net_buffer_length

 

myisam_sort_buffer_size

This is only used when creating/altering or repairing tables (table indexes specifically). Shoudln't affect normal operation

 

Also consider enabling query cache

http://dev.mysql.com/doc/refman/5.1/en/query-cache-configuration.html