[RESOLVED] Speed Issues - can someone point me in the right direction?

jonki

Hi,

I have a couple of websites on the same dedicated server which has 2Gb of RAM. Last night I did a TV campaign and I am currently having major problems with swapping.

The people where my server is located inform me that it is probably due to bad programming. My data base isnt that big and I dont have that many clients.

Can someone please let me know; if I have an Intel 3Ghz 2MB cache with 4GB RAM, for a couple of websites with aprox 4000 visits per day, querying about 6000 entries in each database for search purposes mainly, with 4 small fotos (30-50kb) each per entry and my CPU starts swapping and MySQL starts timing out

is this a normal expactation for this computer? Is there something clearly wrong with the programming? Should I think about getting a bigger/faster machine?

(The guys at my hosting company mention "closing the queries or else they carry on running and slow the system down" - I have NEVER heard of this, nor can I find it on any website....any one enlighten me?)

I would be so grateful for a reply - I had a perfectly running website and now am spending a fortune in advertising and people see error messages only.

halojoy

jonki wrote:
The guys at my hosting company mention "closing the queries
or else they carry on running and slow the system down"
- I have NEVER heard of this, nor can I find it on any website
....any one enlighten me?

I would be so grateful for a reply - I had a perfectly running website and now am spending a fortune in advertising and people see error messages only.

There is a mysql function to 'clean up' of results from query: [man]mysql_free_result[/man]
There are also a so called [man]mysql_unbuffered_query[/man]
Otherwise complete results will be stored in a buffer, even if they are not used.

How this would help for your website, I can not tell.
Personally I think, your issues can very well
be something with HOST SERVER settings, HOST php.ini settings

and maybe so causing a mismatch with your script

MySQL databasse connections, and several other databases, can be either
> persistent, stay open

non-persistent, really close
Maybe you should check to see that connections are Not Persistent.

Also [man]mysql_connect[/man] can be set to open a New connection, handle, each time

or be set to re-use the previous open connection..

Best starting point, most info would here, at this function:
http://docs.php.net/mysql_connect

References in PHP Manual, for these functions:
http://docs.php.net/mysql_free_result

http://docs.php.net/mysql_pconnect
http://docs.php.net/mysql_connect
http://docs.php.net/mysql_close

http://docs.php.net/mysql_query
http://docs.php.net/mysql_unbuffered_query

regars 🙂
halojoy

jonki

I have spend the last day going through your recomendations and following links.

I have drastically cleaned up my code and think that I have resolved all the problems with out having to resort to free_result (which I have heard mixed opinions, and due to my small database, dont think it is necesary for my code).

You pointed me in the right direction and I think I have solved the problem. Many thanks for your help.

Here are a couple of things that I found whilst following your lead, hope this helps someone else with the same questions;

http://phpbuilder.com/board/showthread.php?t=10335796&highlight=mysql_free_result%28%29

http://dev.mysql.com/doc/refman/4.1/en/optimization.html

But the most that helped me was
http://www.linuxformat.co.uk/wiki/index.php/PHP-Optimising_MySQL_queries

Again - many thanks