MySQL queries affecting server performance

Posted by kenorb on March 4, 2009 at 10:42am

5 followers

Issue Summary

Please optimize MySQL queries, it's very slow and not optimized.

| 1977581 | web245 | localhost:53366 | web245 | Query | 2096 | Copying to tmp table | SELECT DISTINCT(n.nid) FROM node n INNER JOIN job_posting p
ON n.nid = p.nid
INNER |
| 1977584 | web245 | localhost:53369 | web245 | Query | 2095 | Copying to tmp table | SELECT DISTINCT(n.nid) FROM node n INNER JOIN job_posting p
ON n.nid = p.nid
INNER |

The database is creating multiple connections using temporary tables slowing the MySQL services and generating load on the server affecting.

Comments

#1

Posted by gmarus on March 4, 2009 at 3:20pm

Category:

bug report

» feature request

Unless I'm mistaken I don't see how this is a module-related issue as job_posting simply uses Drupal's built-in DB abstraction layer to do its queries. For instance, nothing in the module code directly relates to the use of temporary tables and the code follows published Drupal best practices. If you're suggesting that the module should bypass Drupal's DBA layer in order to use hand-written, optimized SQL then by all means go ahead and do so or hire someone qualified to do this work (that's the beauty of an open-source licence, under which you and I both benefit).
IMHO if performance is your main imperative you might want to consider other options including a more powerful server platform or another CMS or framework rather than Drupal. It's strength lies in its flexibility and impressive out-of-the-box features, not speed/performance.

#2

Posted by kenorb on March 4, 2009 at 4:38pm

Project:	Job Posting	» Drupal core
Version:	6.x-2.x-dev	» 6.10
Component:	Code	» other
Category:	feature request	» support request

Which component in Drupal is doing copy some whole node table to the tmp table?
Any clue?

#3

Posted by kenorb on March 4, 2009 at 4:59pm

But still don't know what's wrong and which module cause so slowdown and memory consumption.

#4

Posted by kenorb on March 4, 2009 at 4:47pm

Title:

MySQL queries affecting server performance

» "Copying to tmp table" | "SELECT DISTINCT(n.nid) FROM node" causing slowdown server

#5

Posted by __rajeev on March 4, 2009 at 4:52pm

Title:

"Copying to tmp table" | "SELECT DISTINCT(n.nid) FROM node" causing slowdown server

» MySQL queries affecting server performance

Probably you already have done this:

1) Try disabling few suspect modules one at a time and see if there is any difference in the number of queries. Some modules, if configured to make online updates, will take a lot of time and make a lot of queries.

2) Try to change Cache settings

Please post your Drupal configuration - the list of key modules you have installed - someone may quickly remember issues they faced from modules names.

Thanks

#6

Posted by kenorb on March 4, 2009 at 5:07pm

Problem is that I've got around 200 modules and it's difficult to diagnose it.
Especially if I don't have access to mysql-slow.log. And nothing in logs.
I've enabled Devel to log all my queries with time, but at the moment I've no idea what's wrong.
Hosting provider send me this information to fix that problem imediately, otherwise he will block my whole website.

I'm using boost, fastpath_fscache and javascript_aggregator as cache engines.
Time for caching in boost is set to 10 minutes, to cache everything what's possible, but the problem is still somewhere there.

#7

Posted by __rajeev on March 4, 2009 at 10:22pm

I am not sure about this but I think you have too many modules installed to be running in a shared hosting environment.

To diagnose the problem,

1) Start by disabling all unwanted modules that you may have installed but not really using.
2) Drupal comes with built-in caching - you do not need any external caching modules. If you want to use external caching -then you certainly need only one caching module. To begin with, disable all external modules. fastpath_fscache requires you to change settings.php file - you will have to undo those changes in settings.php file.
3) For now, I would suggest disable Drupal caching module as well. Also go to Admin->Performance and disable all caching there.
4) Disable modules such as xmlsitemap, tagadelic, sitemenu
5) Check this http://2bits.com/articles/bottleneck-replacing-taxonomy-term-count-nodes... - it has a list of modules that can using tmp table copy approach.

You can try to take dump of the devel db query output and post the output here for some more clues.

#8

Posted by ainigma32 on March 4, 2009 at 10:28pm

Status:

active

» postponed (maintainer needs more info)

Taking the query you posted:

SELECT DISTINCT(n.nid), n.title
FROM node n INNER JOIN job_posting p ON p.nid = n.nid

and doing an explain gives:

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra                        |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+------------------------------+
|  1 | SIMPLE      | p     | index  | PRIMARY,nid   | nid     | 4       | NULL          |    1 | Using index; Using temporary |
|  1 | SIMPLE      | n     | eq_ref | PRIMARY       | PRIMARY | 4       | drupal6.p.nid |    1 |                              |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+------------------------------+

By removing the DISTINCT clause you get:

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | p     | index  | PRIMARY,nid   | nid     | 4       | NULL          |    1 | Using index |
|  1 | SIMPLE      | n     | eq_ref | PRIMARY       | PRIMARY | 4       | drupal6.p.nid |    1 |             |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+

which is what you want.

The DISTINCT clause is added by _db_rewrite_sql
So if you want to know which module is causing the DISTINCT clause to be set you can add some debugging to that function.
Maybe something like this:

<?php
//snip

      if (isset($result['distinct']) && $result['distinct']) {
        $distinct = TRUE;
        watchdog('debug', 'Added DISTINCT to query: '. $query); 
      }


//snip
?>

Unfortunately there is no guarantee that that will solve your problem because the problem can also be caused by a bad ORDER BY ... LIMIT clause.

Please post back your findings.

- Arie

BTW I would have to agree with rajeev, you are using an incredible amount of modules. Are you sure you need all of them?

#9

Posted by kenorb on March 27, 2009 at 2:31pm

I'm trying to make some quite big community website and most of the modules I think I need.
Most of them provide small improved functionality or extending existing modules.
It's difficult to disable only modules that I need, because there are too many dependencies between them and it will take weeks to check every day if the problem still there.
I know that's easy to say disable all modules (which will not solve the real problem), but if there is some problem, there is a solution.
I'll need to make some JMeter test plan on my local environment and test website using some example of access log and I'll check which module adding DISTINCT command as Arie proposed. Thanks.
I'll try to use as well bench_chart module which I've wrote (http://drupal.org/project/dtools) to show me from which hooks this slow query is executed.

I've got execution time of each query via devel (what's the next module which slowing down everything, but I can't find out without debugging everything). But at the moment it's new functionality of Devel module and there is no any statistics page where I can easily check (instead of looking into table).

I'll try to diagnose it as in #8

Additional details:
I've got some modules related to RSS (via feedapi with job_posting), I've decreased deletion time to 4 weeks to decrease number of records in the tables.
Some of tables contain few thousands of records (like job_posting table), but I don't know if this problem should appear even in this case and slowing down MySQL.
I know that it should be for this some dedicated server, but at the moment I can't move it, because the website it's not finished (because of recent problems like that one) and it will be moved when it will fully work.

#10

Posted by ainigma32 on March 27, 2009 at 1:44pm

@kenorb: Any luck on those JMeter tests?

- Arie

#11

Posted by kenorb on March 27, 2009 at 2:34pm

Not yet, I'll move this website to dedicated server and I'll make some tests there.
Probably it'll take me couple of weeks, because meantime I've got too many things on my head;/