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 »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Run-away Queries in 1.9?
Pages: [1]

Author Topic: Run-away Queries in 1.9?  (Read 1186 times)

torenware

  • I post frequently
  • ***
  • Posts: 153
  • Karma: 4
Run-away Queries in 1.9?
January 17, 2008, 01:17:12 pm
Performance on a client's new site has been very slow, with MySQL taking over essentially 100% of CPU (actually, it's a dual-proc system, and top gives us readings like 197% of CPU.  Wow).  I've turned the slow query log on, and I'm getting lines like this:

# User@Host: dalive_dau[dalive_dau] @ localhost []
# Query_time: 738  Lock_time: 0  Rows_sent: 0  Rows_examined: 165107444

That's 165 million rows.  And that appears to be 12 minutes of execution time. :-(

The database is not terribly large for CiviCRM;   I've set up systems with more that 150K records that perform reasonably, and this one is not that large.

Any help we can get in debugging this, the client would appreciate.



torenware

  • I post frequently
  • ***
  • Posts: 153
  • Karma: 4
Re: Run-away Queries in 1.9?
January 17, 2008, 01:51:14 pm
I've run mysqladmin proc,  and here's a part of one line.  Fields are "command',  'time', 'state', and 'info':

Query   | 718  | Copying to tmp table | SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name

These are building up on the system and appear to be killing the server.  It seems to be mostly these and similar queries, and "Copying to tmp table" seems to be a slow operation here.

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: Run-away Queries in 1.9?
January 17, 2008, 05:07:09 pm

rob:

1. how many contacts and how many locations? lots of custom values? What are your search parameters?

2. the below is an artifact of an excessive number of LEFT JOIN's in Civi 1.x. We've fixed this issue to some extent in 2.0 (by undoing some normalization)

3. I would disable alphabetic pager (u get rid of one large query) and will get the execution time bit. Civi 2.0 has another optimization which includes an additional IN clause with the contact ids which should make the below query much faster. If you'll are willing to share the db, it would be a great test case for both our search optimizations and upgrade scripts :)

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

torenware

  • I post frequently
  • ***
  • Posts: 153
  • Karma: 4
Re: Run-away Queries in 1.9?
January 17, 2008, 05:57:40 pm
Quote from: Donald Lobo on January 17, 2008, 05:07:09 pm

rob:

1. how many contacts and how many locations? lots of custom values? What are your search parameters?

A fair number of custom fields (20+ in total).  3 locations in most records.  Exact number of records is, well, part of my NDA :-)
Quote
2. the below is an artifact of an excessive number of LEFT JOIN's in Civi 1.x. We've fixed this issue to some extent in 2.0 (by undoing some normalization)
3. I would disable alphabetic pager (u get rid of one large query) and will get the execution time bit. Civi 2.0 has another optimization which includes an additional IN clause with the contact ids which should make the below query much faster. If you'll are willing to share the db, it would be a great test case for both our search optimizations and upgrade scripts :)
We'd be happy to let you see the DB, although  it would be limited to you and whoever helped you;  lots of data of lots of people, so you know what that means.

I've also been running the slow query log, and would be happy to give you a copy of it, so you can see which queries are causing trouble.  One of my personal favorites -- I get my entertainment where'er I can -- is one where the user tried to find when a contact was entered by searching on the mod time of the log.  This query never completes;  it can run 20 minutes or longer, and runs until the mysql process is taken out back and put out of its misery :-(
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Run-away Queries in 1.9?

This forum was archived on 2017-11-26.