CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site

This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Post-installation Setup and Configuration (Moderator: Dave Greenberg) »
  • Memory Exhausted when opening Advanced Search page
Pages: [1]

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

dougall

  • Guest
Memory Exhausted when opening Advanced Search page
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

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4453
  • Karma: 161
    • Tech To The People
  • CiviCRM version: yes probably
  • CMS version: drupal
Re: Memory Exhausted when opening Advanced Search page
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+
-Hackathon and data journalism about the European parliament 24-26 jan. Watch out the result

dougall

  • Guest
Re: Memory Exhausted when opening Advanced Search page
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

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Memory Exhausted when opening Advanced Search page
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
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

dougall

  • Guest
Re: Memory Exhausted when opening Advanced Search page
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

  • Guest
Re: Memory Exhausted when opening Advanced Search page
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
« Last Edit: July 14, 2008, 08:18:36 am by dougall »

Dave Greenberg

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 5760
  • Karma: 226
    • My CiviCRM Blog
Re: Memory Exhausted when opening Advanced Search page
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.)
Protect your investment in CiviCRM by  becoming a Member!

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Memory Exhausted when opening Advanced Search page
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
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

dougall

  • Guest
Re: Memory Exhausted when opening Advanced Search page
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

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Memory Exhausted when opening Advanced Search page
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
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

xavier

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4453
  • Karma: 161
    • Tech To The People
  • CiviCRM version: yes probably
  • CMS version: drupal
2.0.5 already...
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+
-Hackathon and data journalism about the European parliament 24-26 jan. Watch out the result

Dave Greenberg

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 5760
  • Karma: 226
    • My CiviCRM Blog
Re: Memory Exhausted when opening Advanced Search page
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.
Protect your investment in CiviCRM by  becoming a Member!

Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Post-installation Setup and Configuration (Moderator: Dave Greenberg) »
  • Memory Exhausted when opening Advanced Search page

This forum was archived on 2017-11-26.