Query Locked

[namida]

Hi,
I have an application which has queries of about 8 inner joins (takes around 0.3 second of execution) . This application is accessed from around 8 different points ( web and wap front end). Since it got so many hits the processes in mysql starts to lock and a lot of people can't access the page.

I am using PHP with mysql_connect() not persistent. We tried persistent but it gives the same result. I'm trying to research for Connection Pooling (like in java) but i haven't found the right resources.

I tried upgrading to mysql 4.20 and change the queries to sub queries but it seems that the execution is twice slower than with Inner JOin (I wonder why). I've also tried to seperate the burden to PHP but it's not such a good idea since it makes my application not scalable.

Please help me with this issue?

Thanks a bunch

Regards,

Namida

 

[Bastien]

If the data is relatively static for the query, consider caching it once a day (or other time frame). See here for caching of php code

Also consider creating a static text page of the content that can be used to store that info for certain periods of time.

i have found that breaking up the query and storing and manipulating the bits does buy some performance (I reduced a 4 minute query to about 20 seonds this way). also ensure that yo are closing the connections as soon as you are completed with it

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[namida]

Thanks.
the data unfortunately is not static, it is updated by many parties, and even if I cache it it would only work for probably the index page.

we close the connection every end of the page.


Regards,

Namida

 

[Bastien]

At this point, I would then suggest looking into replication and mutilple servers to balance and split the load out. High Performance Mysql is a site / book that gives a lot of pointers in how to set this up and make it run

You can also increase the max-connections value to allow more users to connect, but thats more of a stop gap measure IMO

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...