Testing mysql

[theMAD2]

Hi

I've been having a problem with (fasthosts) mysql server lately; every now and then my database program pauses for a couple of minutes before returning a page, and when it does, it returns the page as a download (not rendered in the browser window). I guess the page is incomplete or something. I think it's the connection from php to mysql, as standard php pages load fine, it's just pages which require database interaction. This is the same across browsers/computers/internet connections.

Does anybody know of any good database stress tests, or anyway I can prove this problem to fasthosts?

Thanks

 

[theMAD2]

Anyone?

I tried making a script with a loop which connects to the database like 20 times, but I want it to run a click every 5 seconds or so. Is there anyway I can do this so the results come up in realtime? It seems the whole loop will run and then the page is returned, which is quite a wait and you can't see if it's failing as well. Maybe this would be better with some sort of AJAX solution?

 

[JIMA]

Hi,

Could it be an issue of the number of database connections being established and then not dropped when finished with, resulting in an ever increasing number of connections until it proves too much? Also, could the connections be creating locks on tables etc, maybe eventually resulting in deadlocks etc. This could explain the pause as the deadlock is only finally "resolved" when a locking connection times out. I imagine deadlocks etc. would be reported in a log file.

Is there any way of using a connection pool whereby a fixed number of connections are established (say by Apache etc.) and then used as required? These connections are also configured with timeout etc. I've used a connection pool in some Java/Struts work, definitely a good idea IMHO. It also helps to decouple your app from the database being used which is also useful.

Sorry that the above is a bit vague. I've not used mysql/php but the above are similar sounding problems I've encountered in the past, thought the reply might still be useful.

Jim

 

[Beansprout]

Sounds like CWSOOMSOSC to me

(Crappy Web Server Or Overloaded MySQL Server Or Server Connection )

 

[theMAD2]

I've been looking through the script I am running and there is no mysql_close; at the end of the index.php, could this be causing the problem? I thought as soon as the script has finished it will close the mysql connection anyway, but I've always added mysql_close; as good practice.

I've also been looking at the mysql server using the mysql server administration tool, are there any points I should be looking at here in the server health variables? There seem to be a constant aprox 30 threads connected at any one time, busy server I guess

 

[Beansprout]

Unless you're using mysql_pconnect(), there is no need to use mysql_close()

30 threads is fine if they're mostly idle - most likely sites with persistent connections