optimizing MySQL

[asishlla]

Hello,
my server running slow,i asked hoet company and they answer me:
-------------
It appears as though your MySQL is also using nearly 50% of the CPU. I would recommend optimizing your MySQL to deal with the queries more effectively if you haven't already done so.
-------------

how can i optimizing my MySQL to deal with the queries more effectively??
thank you very much

 

[SeLLeRoNe]

http://help.directadmin.com/item.php?id=44

Regards

 

[LawsHosting]

Also, see http://blog.dynom.nl/archives/MySQL-5-optimizing_20071009_10.html

It also depends on ram and CPUs, the greater, the better....... then you can increase the values gradually.

Also, see http://mysqltuner.pl - this will guide you to the correct/ideal settings.

 

[asishlla]

Thank you SeLLeRoNe but your guide for sql4 and i have MySQL 5.1.57
petter this guide is good but i cannot find my.cnf in etc
there is just file call: my.cnf.bk
do you have idea where can i find it?
thank you

 

[asishlla]

[root@server ~]# locate my.cnf
/etc/my.cnf.bk
/usr/local/directadmin/conf/my.cnf
[root@server ~]#

but here : /usr/local/directadmin/conf/my.cnf
the file has just phpmyadmin username and pass inside!

 

[SeLLeRoNe]

cp -f /usr/share/mysql/my-large.cnf /etc/my.cnf
/etc/init.d/mysql restart

and then run that script

Regards

 

[LawsHosting]

Make sure you add local-infile=0 to my.cnf under [mysqld]

 

[asishlla]

this is the content in the file:

# The MySQL server
[mysqld]
port		= 3306
socket		= /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 256M
local-infile=0
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

what you thing the best sittings to change?
thank you

 

[LawsHosting]

Leave it for 24 hours then run the script, mysqltuner.pl, it will suggest some appropriate settings to add or increase, change them, restart mysql then leave it 24 hours again, etc etc.