mysql db needs optimizing i think...advice appreciated.

When visiting any sites on my server during peak times, they either take ages to load or simply don't load at all. I done a TOP and mysqld sometimes is using like 80% and higher cpu usage. and sometimes the server load is at 15 and higher. Every other day I'm having to optimize tables etc before there is any slight improvement.

The server is an Intel pentium 4 3.06GHz, with 512mb ram. this is what is in my.cnf

[mysqld]
set-variable = max_connections=1000
safe-show-database

Code (markup):

What should i do to that to make the situation better?

 

First thing to check - are you using mysql_pconnect() in PHP or some other form of persistent connection? If yes, turn it off. In MySQL the overhead of establishing a connection is very minimal - a couple of milliseconds. But the risk of leaking connections is quite high.

 

thanks for the tip

 

could you copy paste your whole my.cnf here? It's very hard to say what is the problem without seeing the config. Also, is your cpu usage high?

 

hello
reduce wait_timeout connect_timeout in you my.cnf

 

Can you get more RAM into your server? Databases like to cache frequently needed data in memory so they don't have to keep pulling it off the disc.

 

Hey,

Before spending any money on more RAM or changing any of your settings in the configuration, make sure the queries are properly optimized.

To do this start by checking the slow queries log, if it is not enabled then to enable the slow query log, start mysqld with the --log-slow-queries[=file_name] option.

Leave it running for a few hours and then check the file to see which queries are causing your server problems.

 

I dont come from a PHP/MYSQL Background so I cant give you specifics but in the general database world these are pretty good tips

• Keep your database connection open for the shortest time possible, if PHP has an equlivant of ASP's GetRows() use it where appropriate
• Only return data that you need, if you only need 3 columns only return 3 columns
• Select * is normally a bad move, even if your selecting all the columns
• Avoid using wildcard string searches if you dont need them
• Check to see your indexing the right things, basically anything you search on frequently should have an index as well as columns used in join conditions
• While optimizing slowest running queries is good its a good idea to optimize the queries run most, you may get a smaller benefit on each run but it may add up to a bigger saving

Jen