Need best mysql configuration of My.cnf

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rahulephp
Forum Commoner
Posts: 28
Joined: Mon Oct 05, 2009 11:05 am

Need best mysql configuration of My.cnf

Post by 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Need best mysql configuration of My.cnf

Post by VladSun »

Why dont you try some of the MySQL preconfigured option files first.

From dev.mysql.com comments:
You can copy sample config files from /usr/local/mysql/support-files. There should be five sample files, for small, medium, large, huge, and heavy configurations.

Small: System has <64MB memory, and MySQL is not used often.
Medium: System has at least 64MB memory
Large: System has at least 512MB memory and the server will run mainly MySQL.
Huge: System has at least 1GB memory and the server will run mainly MySQL.
Heavy: System has at least 4GB memory and the server will run mainly MySQL.
Also, make sure you have proper indexes defined for all of your tables. Look for slow queries and optimize them. You may even create a second, denormalized DB from your DB (http://en.wikipedia.org/wiki/Data_warehouse).
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply