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 (Moderator: Dave Greenberg) »
  • MySQL maxing
Pages: [1]

Author Topic: MySQL maxing  (Read 3894 times)

Matt2000

  • I post frequently
  • ***
  • Posts: 288
  • Karma: 27
    • http://www.ninjitsuweb.com
MySQL maxing
April 08, 2008, 10:11:18 am
MySQL is maxing my CPU at 99%+.

By 'show processlist;' it appears the problem query is

SELECT count(DISTINCT contact_a.id) FROM civicrm_contact contact_a LEFT JOIN...

I'm running CiviCRM 1.9.

Any ideas?

Drupal/CiviCRM micro-blogging http://twitter.com/matt2000

Ninjitsu Web Development http://www.NinjitsuWeb.com/

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: MySQL maxing
April 08, 2008, 02:22:17 pm

how big is the db etc, how many locations / emails / address / contacts etc. how long does it get max out the cpu for

the 2.0 schema has been significanly changed to reduce the number of left joins. there has also been some optimizations for search when displayed in a page (i.e. a limited number of contacts being displayed)

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

Matt2000

  • I post frequently
  • ***
  • Posts: 288
  • Karma: 27
    • http://www.ninjitsuweb.com
Re: MySQL maxing
April 08, 2008, 05:13:19 pm
9541 rows in civicrm_contacts table. Can't check on much else, as I'm also getting constant "Could not find valid Key" errors.

Once that query runs, it gets stuck, and cpu stays max'd until I kill the query.

I can't upgrade to 2.0 because of the problems with the Dojo HTML editor in CiviMail.
Drupal/CiviCRM micro-blogging http://twitter.com/matt2000

Ninjitsu Web Development http://www.NinjitsuWeb.com/

Matt2000

  • I post frequently
  • ***
  • Posts: 288
  • Karma: 27
    • http://www.ninjitsuweb.com
Re: MySQL maxing
April 12, 2008, 07:50:46 pm
FYI, this problem, which was occurring nearly everyday, has not occurred again since downgrading to CiviCRM 1.9.11960 a few days ago.
Drupal/CiviCRM micro-blogging http://twitter.com/matt2000

Ninjitsu Web Development http://www.NinjitsuWeb.com/

Matt2000

  • I post frequently
  • ***
  • Posts: 288
  • Karma: 27
    • http://www.ninjitsuweb.com
Re: MySQL maxing
April 15, 2008, 10:43:15 am
Never mind. It happened again at least once yesterday, and I just killed 4 queries for today. :-(
Drupal/CiviCRM micro-blogging http://twitter.com/matt2000

Ninjitsu Web Development http://www.NinjitsuWeb.com/

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: MySQL maxing
April 16, 2008, 10:07:33 pm

You might want to investigate how much work is involved with replacing the dojo editor with your favorite WYSIWYG editor and upgrading to 2.0. This is on our issue list for 2.1 and i suspect it will not be too hard. If you do it, please share the code :)

That might be a easier path forward

i hope (and suspect) that the below will not be an issue with 2.0. if so we'll definitely want to take a look at the db and optimize a few more queries

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

Matt2000

  • I post frequently
  • ***
  • Posts: 288
  • Karma: 27
    • http://www.ninjitsuweb.com
Re: MySQL maxing
April 16, 2008, 10:21:58 pm
I started work on the editor issue, but I got no response to my request for help with the incomplete patch I posted at:

http://issues.civicrm.org/jira/browse/CRM-2564

Basically, I have been able to replace the HTML textarea with whatever Drupal designates as the editor, but I have not been able to get it's contents to submit properly with the form.
Drupal/CiviCRM micro-blogging http://twitter.com/matt2000

Ninjitsu Web Development http://www.NinjitsuWeb.com/

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: MySQL maxing
April 16, 2008, 10:33:27 pm

Sorry, we dont pay as close attention to comments on closed issues. Opening a new topic on forum is probably a better idea (IMO)

Basically u cannot use form api within civicrm. QF registers all the form elements created and ignores the other $_POST elements. I definitely would avoid hacking around that condition

I would just add your favorite html editor in the attributes parameter for now

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

Matt2000

  • I post frequently
  • ***
  • Posts: 288
  • Karma: 27
    • http://www.ninjitsuweb.com
Re: MySQL maxing
April 18, 2008, 10:47:54 am
The max out is now happening everytime a user logs in. I'm crippled...

Any suggestions, besides 'upgrade to 2.0' ? Because I don't think everyone using 1.9 had this problem.
Drupal/CiviCRM micro-blogging http://twitter.com/matt2000

Ninjitsu Web Development http://www.NinjitsuWeb.com/

Matt2000

  • I post frequently
  • ***
  • Posts: 288
  • Karma: 27
    • http://www.ninjitsuweb.com
Re: MySQL maxing
April 19, 2008, 02:51:08 pm
On further investigation, it seems this is only occurring for accounts which are in a large number of groups (the main account I'm troubleshooting is in 55 groups, most mostly via organic groups sync.)

So it seems there is a practical limit to the number of groups a contact can safely belong to in 1.9. :-(

Can anyone verify whether this might still be an issue in 2.0 ?

-Matt
Drupal/CiviCRM micro-blogging http://twitter.com/matt2000

Ninjitsu Web Development http://www.NinjitsuWeb.com/

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: MySQL maxing
April 19, 2008, 04:53:42 pm

Can you point us more to the specific functions that trigger the queries. Are you using queries? Is this code specific to the civicrm_og_sync module?

ping me on IRC and we can chat more and i can get more details

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

Matt2000

  • I post frequently
  • ***
  • Posts: 288
  • Karma: 27
    • http://www.ninjitsuweb.com
Re: MySQL maxing
April 20, 2008, 03:19:29 am
Confirming that this problem seems to be resolved after upgrade to 2.0.
Drupal/CiviCRM micro-blogging http://twitter.com/matt2000

Ninjitsu Web Development http://www.NinjitsuWeb.com/
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM (Moderator: Dave Greenberg) »
  • MySQL maxing

This forum was archived on 2017-11-26.