optimize mysql for low cpu usage and high performance

[Sanju Kr]

An unoptimized mysql usualy takes high cpu and a heavy impact on performace.

To optimize the mysql for low cpu load best performance
edit etc/my.cnf

nano /etc/my.cnf

and put the following code:

[mysqld]
safe-show-database
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_user_connections=25
max_connections=500
interactive_timeout=10
wait_timeout=10
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
thread_concurrency=8
myisam_sort_buffer_size=64M
innodb_file_per_table=1
log-error=/var/log/mysqld-error.log
log-slow-queries=/var/log/mysql-slow.log
long_query_time=1
[mysqld_safe]
nice = -10
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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

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

save it.

Then restart mysql server

service mysql restart

[khan]

you can't configure mysql without tuning it. Before doing any change in my.cnf tune it with mysqltuner.

[amylee]

and that will optimize every MySQL server from the little crappy sites to the hugely popular sites with millions of hits a day/week?
to properly optimize MySQL you first need to know what every value means and what it will do to your server and site then analyze logs/traffic stats blah blah blah then twiddle with settings

so in my opinion this is useless

[Sanju Kr]

this mysql config is for busy server and its a copy of one of our server.

[khan]

what define a busy server? One of mine has 8 gb ram another one got 2gb. So if I use the configuration of 8gb ram in 2gb system will it work!! No, it'll just fu*k the system.
So its better to tune it before making any change in my.cnf.

[shushant]

buddy optimizing mysql will make huge load on server its bad idea to use it
caching will be good alternative

[Sanju Kr]

oop during post i was not assumed that still people uses 2gb ram as 2gb ram and 8gb ram server difference is just $20$

I have posted the code for 8gb or more ram server.

[$XTREME$]

What type of a server are you using and what is ur OS , SOFTWARE (KLOXO , CPANEL) ? I can give you the solution ?

[amylee]

one problem i can see in that config is the value "key_buffer=16M" unless you have 128MB of ram :/, as ideally this setting should be set to around 1/4 <-> 1/2 of your servers ram to be properly effective
btw i'm not criticzing what you have shared as this probably won't be effective on every server like for example on mine but here is my config and mysql runs superb with it

[mysqld]
back_log = 75
max_connections = 500
key_buffer = 4096M
myisam_sort_buffer_size = 128M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 7200
connect_timeout = 25
tmp_table_size = 128M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 1000
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 32M
query_cache_limit = 64M
query_cache_size =128M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM
myisam_pct=100
skip-innodb

[mysqld_safe]
nice = -5
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 20480M

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

[Sanju Kr]

Server is managed by a oursource server management know as platiniumservermanagement, they have added that and i have never ever has any issue with this mysql config.