Author Topic: Timeout on Event overview -> heavy query? (Read 3150 times)

BarisArt · February 20, 2008, 01:53:30 am

When I want to overview my events from the dashboard (using the CiviEvent link), I get a timeout (no response after 30 seconds).

I have about 100 events (8 to come, 88 in the past) and 1200+ contacts.

Is it possible that the used query is too slow?

Code: [Select]

Fatal error: Maximum execution time of 30 seconds exceeded in /public_html/sites/all/modules/civicrm/packages/DB/DataObject.php on line 2337.

I use CiviCRM on Drupal 5.6.

Please help!

Michał Mach · February 20, 2008, 03:41:10 am

Can you send us specific link (after server part) where this timeout happens? Which version of CiviCRM are you using?

Thx,
m

BarisArt · February 20, 2008, 04:08:52 pm

Sure can:

http://www.domain.com/civicrm/event?reset=1

I now changed all events in the past to is_active = 0, this helps a little (so there are now 8 active events).
The page is parsing now within the limit of 30 seconds. It is taking approx 27/28 seconds to generate the overview (still way to slow!).

I am using CiviCRM 2.0beta1 on Drupal 5.6

Michał Mach · February 20, 2008, 11:52:57 pm

From first look, it seems like db usage is far from optimal here. We'll be still doing some db usage optimisations for 2.0, so please stay tuned for announcements about next beta releases, try them out and let us know if they improve your situation. And thanks for heads up on this problem.

Thx,
m

Donald Lobo · February 21, 2008, 12:33:22 pm

1. who is your hosting provider? Any idea what the load on the machine is when you are running the below tests. How long does an "empty search" on "Find Contacts" take?

2. in the 8 active events, how many participants are there?

3. how large is your contact db? how many addresses?

thanx

lobo

BarisArt · February 23, 2008, 12:58:17 am

Hi lobo,

I will try to answer as many questions as possible.

1. I host the website on pcExtreme (www.pcextreme.nl) on a shared server.
This is the server info:

Code: [Select]

Processor Name		Intel(R) Xeon(R) CPU E5310 @ 1.60GHz
Vendor ID		GenuineIntel
Processor Speed (MHz)	1599.999
Vendor ID		GenuineIntel
Processor Speed (MHz)	1599.999
Total Memory		1894608 kB
Free Memory		86724 kB
Total Swap Memory	1048568 kB
Free Swap Memory	1048532 kB
System Uptime		13 Days, 5 Hours and 16 Minutes
Apache 2.0.59 Server	Running
DirectAdmin 		Running
Exim 4.63		Running
MySQL 5.0.24		Running
Named 9.3.2 		Running
ProFTPd 1.3.0 		Running
sshd 			Running
vm-Pop3d 1.1.7f-T6-DA 	Running

I don't know what the load is. An empty search on the Contact takes approx. 3 seconds. The result is 1234 contacts.

2. At the moment it contains 26 participants. This will increase within the next 4 weeks to approx 200 participants.

3. 1234 contacts, and also 1234 addresses. I only use one address per contact.

Thanks for your help!

Donald Lobo · February 23, 2008, 12:10:50 pm

Your server seems quite fast and contact search returns quite quickly. not sure why event loading takes such a long time with so few participants. the demo box has a similar dataset and the event page displays quite fast

There are two main queries to construct that page:

1. the query to get all the active events. this should be a pretty fast query
2. the search query to get the list of participants.

if i had to guess, its the second query that is slow on your system. We'll need your help in helping isolate and fix this issue. Can u enable mysql query logging (check mysql manual for how to do so) and see the queries being issued. Also manually execute the queries and see which one is taking long and potentially why (you can use a mysql explain for this)

thanx

lobo

BarisArt · February 25, 2008, 04:00:40 am

Hi Lobo,

True. If I disable the first listing on CRM/Event/BAO/Event.php, function getEventSummary, the page is still frustrating slow.
So the delay will probably be caused in the creation of the second listing (10 recent participants).

Is it possible that the limit of 10 is only used in the pager, instead of in the query itself? So it first fetches ALL 1200 contact, and then limits its result for being displayed? This could explain why it is so damn slow..

ps: One other thing: the listing is sorted on contact's name ASC, instead of registration_date DESC, which I think is much more helpful.

Donald Lobo · February 25, 2008, 02:27:53 pm

The limit is used in the query (but order by does slow the query down a lot). You'll need to check your mysql query and slow query logs and figure out what query is the bottleneck and why

I took another look at the query log and was able to eliminate a few queries by caching at the php level. However i dont think this will speed things up a lot without really knowing what the bottleneck query was. This will be part of the next beta3

we dont display registration date, so sorting by registration date might be a bit confusing to users. however, i think for the dashboard its a valid suggestion. Wanna file an issue and we'll potentially fix it in 2.1

I've also created the optimize query issue here: http://issues.civicrm.org/jira/browse/CRM-2735

lobo

CiviCRM Community Forums (archive)

News:

Author Topic: Timeout on Event overview -> heavy query? (Read 3150 times)

BarisArt

Timeout on Event overview -> heavy query?

Michał Mach

Re: Timeout on Event overview -> heavy query?

BarisArt

Re: Timeout on Event overview -> heavy query?

Michał Mach

Re: Timeout on Event overview -> heavy query?

Donald Lobo

Re: Timeout on Event overview -> heavy query?

BarisArt

Re: Timeout on Event overview -> heavy query?

Donald Lobo

Re: Timeout on Event overview -> heavy query?

BarisArt

Re: Timeout on Event overview -> heavy query?

Donald Lobo

Re: Timeout on Event overview -> heavy query?