Author Topic: Memory Exhausted when opening Advanced Search page (Read 2867 times)

dougall · July 11, 2008, 01:49:13 am

Hi all,

When I was trying to open the Advanced Search page from the main Dashboard I was seeing a PHP fatal error "Allowed memory size of 41943040 bytes exhausted...".

I wasn't particularly surprised by this since we had 31 custom fields and 30,000 contacts and were only allocating 40Mb for CiviCRM to use.

I'd already read most of the posts on this forum relating to memory exhausted errors, obviously most suggest upping the memory limit.

So, I upped the memory limit to 64Mb and tried again without success, getting the same error ( and to confirm the memory limit setting was taking effect overrode the front page dashboard template to display ini_get('memory_limit') ).

Then I reduced the number of custom fields to just those we feel we *really* need = 15, and upped the memory to 128Mb. This time I got a "Maximum execution time of 60 seconds exceeded..." fatal error.

Next I tried upping the execution time to 120s, this time no server error was generated and the web page in the browser hung indefinitely in the loading state, also the mysqld process became suspiciously busy (20%+ CPU) for >10 mins.

We've been trying to get Advanced Searching working for a couple of days without success, and had been noticing other problems with this server during that time. Our server administrator had this to say:

Quote

..... I've had to reboot XXXXXX both yesterday and today because it's
swapped itself into a state where I didn't think it could recover. I've
also removed nearly 3GB worth of temporary tables from /var/lib/mysql
which all have timestamps from shortly before to partway into the lock-ups.

Both occurences are preceded by a number of "GET
/content/index.php?q=civicrm/contact/search/basic&force=1" in quick
succession. These probably aren't the actual problem causers, those are
unlikely to be logged as they never completed, but it may give some
clues for tracking it down.

I'd greatly appreciate any suggestions on where I can start with trying to resolve this...

Perhaps I need to provide more details?

Thanks,
Dougall

xavier · July 11, 2008, 02:18:05 am

You can activate the log for long queries in mysql, it will tell you where it hands on. What's your mysql version ?

As for your url, it's the simple search, not the advanced one. Does the standard search behave normally ?

Are you sure all the indexes are there on your tables ?

Anyway, get the long queries log running and see what's inside, it will probably give hints.

X+

dougall · July 11, 2008, 08:00:43 am

Hi Xavier,

Mysql version is 5.0.45.

I can see in "my.conf" that we already have the log for long queries activated, although I'm waiting for someone else to give me the necessary permissions to read it (doh!).

Regarding the URL, I suspect that I was going to the "Find Contacts" page before clicking "Advanced Search" so the last successful processed URL recorded was for the standard search page. The standard search is working fine.

Regarding the mysql table indexes there appear to be suitable primary indexes, unique indexes and foreign keys where I'd expect them for civicrm_contact and other related tables (including the civicrm_custom_field, civicrm_custom_group and civicrm_value_1_xxxxx tables).

Thanks for your assistance.
...Dougall

Donald Lobo · July 12, 2008, 11:47:32 am

hey dougall:

is this still an issue? can you share your db? (we'll delete it once we are done). if so, i'd like to take a look at figure out what queries are choking the system.

30K contacts / 31 custom fields should not be a big deal (i'm assuming u have this split into 3-7 custom groups)

lobo

dougall · July 14, 2008, 03:13:24 am

hi lobo,

yeah this is still an issue, but i can't share the database for legal reasons.

it's reassuring to hear that this is an acceptable number contacts/custom fields.

i'll have to figure this one out myself, but i'll give you feedback if i discover anything of interest/relevance.

thanks,
dougall

dougall · July 14, 2008, 06:45:58 am

I've now turned on the mysql general log to see what's going on.

All the sql seems reasonable up until the following:

Quote

080714 14:05:07 10 Init DB civicrm
10 Query SELECT *
FROM civicrm_group
10 Init DB civicrm
10 Query SELECT parent_group_id FROM civicrm_group_nesting WHERE child_group_id = 1 LIMIT 1
10 Init DB civicrm
10 Query SELECT child_group_id FROM civicrm_group_nesting WHERE parent_group_id IN (1)
10 Init DB civicrm
10 Query SELECT *
FROM civicrm_group
10 Init DB civicrm
10 Query SELECT parent_group_id FROM civicrm_group_nesting WHERE child_group_id = 1 LIMIT 1
10 Init DB civicrm
10 Query SELECT parent_group_id FROM civicrm_group_nesting WHERE child_group_id = 2 LIMIT 1
10 Init DB civicrm
10 Query SELECT child_group_id FROM civicrm_group_nesting WHERE parent_group_id IN (2)
10 Init DB civicrm
10 Query SELECT *
FROM civicrm_group
10 Init DB civicrm
10 Query SELECT parent_group_id FROM civicrm_group_nesting WHERE child_group_id = 1 LIMIT 1
10 Init DB civicrm
10 Query SELECT parent_group_id FROM civicrm_group_nesting WHERE child_group_id = 2 LIMIT 1
10 Init DB civicrm
10 Query SELECT parent_group_id FROM civicrm_group_nesting WHERE child_group_id = 8 LIMIT 1
10 Init DB civicrm
10 Query SELECT child_group_id FROM civicrm_group_nesting WHERE parent_group_id IN (8 )

Considering just the child_group_id value, the pattern above repeats like this:
1
1 2
1 2 8
1 2 8 9
1 2 8 9 10
.
.
.
1 2 8 9 10 ..... 262
1 2 8 9 10 ..... 262 263

PHP is then hitting 60s max execution time and no more queries of this sort are run, and the Advanced Search page doesn't load in the browser.

All these thousands of queries are logged under the same timestamp. I don't know what the purpose of the civicrm_group_nesting table is, but it seems likely that it's these queries causing us problems. Also I notice that the civicrm_group_nesting table is actually empty on our server.

Looking in CiviCRM code I can see the SQL "SELECT parent_group_id FROM civicrm_group_nesting WHERE child_group_id = x LIMIT 1" appears to be generated from:
Contact/BAO/GroupNesting.php line 304 - static function hasParentGroups()

which seems to be called only by:
GroupNesting.php line 166 - function _getNextParentlessGroup()

which in turn is called from the following locations:
GroupNesting.php line 91 - function rewind()
and
GroupNesting.php line 214 - function _getNextSiblingGroup() - which is called from line 129 function next()

I'm not familiar enough with CiviCRM to appreciate what's actually going on here.

Thanks,
Dougall

Dave Greenberg · July 14, 2008, 10:33:06 am

Dougall - Lobo or Kurund will hopefully jump in here w/ the right "fix" - but just wanted to let you know that the nested group functionality which appears to be causing the problems was supposed to be "disconnected" in 2.0 since it was incomplete. Also, not sure if this matters, but might be good to know which version / revision of CiviCRM you're running (I didn't see it noted is this thread.)

Donald Lobo · July 14, 2008, 11:58:46 am

what version of 2.0?

can you upgrade to 2.0.5 and see if it fixes the issues. we disconnected the group nesting feature for 2.0 so should definitely not be there

lobo

dougall · July 14, 2008, 12:29:04 pm

version 2.0.2.

yes, sorry it was daft of me not to say which

ok we'll upgrade as soon as possible.

since it's subsequently been removed presumably it's safe to comment out all call's to group nesting related functions as a quick fix on our live server.

thanks for your assistance,
dougall

Donald Lobo · July 14, 2008, 12:38:52 pm

I'm surprised its even there in 2.0.2. i thought we had removed and fixed it in a beta release. I'll double check with the latest 2.0 version sometime this week

lobo

xavier · July 15, 2008, 08:36:30 am

2.0.5 is out already ? Missed it

I'm using the 2 branch on svn, is that the right place ? I feel like It hasn't been updated since a while (At revision 15817)

X+

Dave Greenberg · July 15, 2008, 09:04:26 am

Quote from: xavier on July 15, 2008, 08:36:30 am

I'm using the 2 branch on svn, is that the right place ? I feel like It hasn't been updated since a while (At revision 15817)

v2.0 is the correct branch if you need to look at 2.0.x code (2.0.5 now). If you're working on stuff of 2.1 - then use trunk.

CiviCRM Community Forums (archive)

News:

Author Topic: Memory Exhausted when opening Advanced Search page (Read 2867 times)

dougall

Memory Exhausted when opening Advanced Search page

xavier

Re: Memory Exhausted when opening Advanced Search page

dougall

Re: Memory Exhausted when opening Advanced Search page

Donald Lobo

Re: Memory Exhausted when opening Advanced Search page

dougall

Re: Memory Exhausted when opening Advanced Search page

dougall

Re: Memory Exhausted when opening Advanced Search page

Dave Greenberg

Re: Memory Exhausted when opening Advanced Search page

Donald Lobo

Re: Memory Exhausted when opening Advanced Search page

dougall

Re: Memory Exhausted when opening Advanced Search page

Donald Lobo

Re: Memory Exhausted when opening Advanced Search page

xavier

2.0.5 already...

Dave Greenberg

Re: Memory Exhausted when opening Advanced Search page