Server Overload?

Submitted by clare on Wed, 2007-08-15 12:26 in Price Tapestry

I have faced a few problems the last few days with my server becoming overloaded.

I am told by someone who knows more about this stuff than me, that it is due to a query running on mySQL which he says maybe i could modify to optimise the load.

This is the query

that support says is overloading server?

It is using 76% of the CPU , this isnt normal is it?

Or do you think I might need to upgrade my server package to be able to handle the queries on the databases, which due to some product feeds being so large, are really quite huge.

I currently have 1GB RAM on the server but could get 4GB

Should this be necessary, I wanted your opinion on this because support at my host obviously just recommends upgrading to a more expensive package all the time and I wondered if you know from your experience if that should be necessary.

Hi Clare, Before considering

Submitted by support on Wed, 2007-08-15 12:30

Hi Clare,

Before considering a server upgrade, check through the discussion on MySQL configuration for very large Price Tapestry sites...

http://www.pricetapestry.com/node/732

In particular, the values for key_buffer_size and sort_buffer_size. With 1GB available, I would try increasing these significantly, key_buffer_size to about 300M, and a sort_buffer_size of maybe 128M. To make these changes, you will need to have access to my.cnf, and then be able to restart your server after making the changes. If not, your host should be able to change the configuration for you...

Hope this helps,
Cheers,
David.

Thanks I will do

Submitted by clare on Wed, 2007-08-15 16:25

Thanks I will do that.

Actually I just noticed that the queries that are causing the large server overload are being made by the automatic sitemap generator script I installed recently, it appears to be querying the sql for each page which I suppose it has to and isnt any different from any other query that may arise.

Thanks for the password wizard you put in the other thread btw, it has been on my todo list for ages to set password on all the admin pages, will be easier with that.