Bottleneck when running a report

[paengski13]

Hi Everyone,

 

I have this problem when trying to run a query which will generate a report.

The database hang depending on how many seconds the query will be processed.

 

Let say the query will take 5 seconds, during this time, when I try to access a page, it will just keeps on loading until the report I generated on the admin side is done. I am not sure what configuration I need to set. I changed engine type from MyIsam to InnoDb, although it is much faster now, but still my database still hang until the query has finished the execution.

 

Any advise? Thanks in advance!

 

[kicken]

Based on your description of the problem, I think what your seeing is not a MySQL problem, but rather a result of PHP locking it's session files.  Basically when you call session_start() and php loads the data from the file on disk, it places a lock on that file until the script ends (or you call session_write_close).  During that time, no other php script can access that file and they will just sit there and wait until the file becomes available.

 

You can test this by using two different browsers.  Run the report in Google chrome and then try browsing in Firefox.  Firefox should still work even if chrome's page is still working on loading the report because it is using a different session file.

[paengski13]

Hi Kicken,

 

I tried to run it using different browser but the problem is still the same, I tried to run the report query "Show processlist" command in mysql and found out that all other request are waiting for the report query to be finished. It means that the table are lock for any other request until the first request is completed.

 

I change all affected tables to InnoDB and now it is working simultaneously, I'm still testing if this will be the fix to my problem.