I attempted to update to the 4.7.0 verion, ran update.php and wound up with runaway queries on the mysql server. Here is some log data

| 203864 | tuckfard_drpl1 | localhost | tuckfard_drpl1 | Query | 587 | Sending data | SELECT COUNT(*) FROM quotes q INNER JOIN node n ON n.vid = q.vid WHERE n.type = 'quotes' AND n.statu |
| 203917 | tuckfard_drpl1 | localhost | tuckfard_drpl1 | Query | 569 | Sending data | SELECT COUNT(*) FROM quotes q INNER JOIN node n ON n.vid = q.vid WHERE n.type = 'quotes' AND n.statu |
| 203946 | tuckfard_drpl1 | localhost | tuckfard_drpl1 | Query | 546 | Sending data | SELECT COUNT(*) FROM quotes q INNER JOIN node n ON n.vid = q.vid WHERE n.type = 'quotes' AND n.statu |
| 204007 | tuckfard_drpl1 | localhost | tuckfard_drpl1 | Query | 513 | Sending data | SELECT COUNT(*) FROM quotes q INNER JOIN node n ON n.vid = q.vid WHERE n.type = 'quotes' AND n.statu |
| 204021 | tuckfard_drpl1 | localhost | tuckfard_drpl1 | Query | 507 | Sending data | SELECT COUNT(*) FROM quotes q INNER JOIN node n ON n.vid = q.vid WHERE n.type = 'quotes' AND n.statu |
| 204068 | tuckfard_drpl1 | localhost | tuckfard_drpl1 | Query | 491 | Sending data | SELECT COUNT(*) FROM quotes q INNER JOIN node n ON n.vid = q.vid WHERE n.type = 'quotes' AND n.statu |
| 204184 | tuckfard_drpl1 | localhost | tuckfard_drpl1 | Query | 433 | Sending data | SELECT COUNT(*) FROM quotes q INNER JOIN node n ON n.vid = q.vid WHERE n.type = 'quotes' AND n.statu |
| 204566 | tuckfard_drpl1 | localhost | tuckfard_drpl1 | Query | 178 | Sending data | SELECT COUNT(*) FROM quotes q INNER JOIN node n ON n.vid = q.vid WHERE n.type = 'quotes' AND n.statu |

Drove cpu use through the roof, why would so many of these queries be run, and what is this one used for?

Comments

red_eye-at-patentlystupid.com’s picture

red_eye-at-patentlystupid.com commented

On further review, is it really nessecary to do a Join to simply do a count?

jhriggs’s picture

jhriggs commented

The only place this query occurs is when displaying a random quotes block. Do you have lots of quotes blocks defined or a really high traffic on your site? Are they all coming from a single visitor or are there lots of visitors hitting the site at once and requesting the quotes block(s)? No one else has reported any such problems, and I have never seen it. This particular section of code changed very little in this latest update.

red_eye-at-patentlystupid.com’s picture

red_eye-at-patentlystupid.com commented

This was with one quote block and likely 1 user. I've tried to strip the quotes module, the quotes table, the quotes type from the node table and the version # from the variable table hoping to reinstall the quotes and no dice. It still doesnt work right I was able to add one quote and it doesnt display in the block , all I get is n/a....

Taran’s picture

Taran commented

Confirmed at KnowProSE.com - but I do have high traffic, and multiple visitors.

I'm also getting the n/a issue.

And NONE of the quotes are showing up on the quote page, just the RSS feed (which IS working)

Rolling back.

jhriggs’s picture

jhriggs commented

None of this makes any sense, and I cannot recreate the problem. Please try the following one at a time, and see if anything changes:

  1. Go to update.php and see if any updates are available for the quotes module. If so, perform them.
  2. Empty your database cache.
  3. Provide complete details of your setup including quotes version (from admin/quotes), any access modules you have enabled, quote block(s) settings, database type, etc., etc. Anything you can provide will be helpful, but as it stands now, I have no idea what you are seeing.
red_eye-at-patentlystupid.com’s picture

red_eye-at-patentlystupid.com commented

If youd like I would not be opposed to giving you the access you need in order to look at whats happening(admin on my drupal site and access to my cpanel for that server). Just drop me an email at rahlquist over at gmail.com and let me know.

red_eye-at-patentlystupid.com’s picture

red_eye-at-patentlystupid.com commented

For some reason mine was defaulting to a particular user for the block. Once I changed the block setting it works! Now I just need to reimport all 47,000 quotes that I stripped out while troubleshooting.

jhriggs’s picture

jhriggs commented
Assigned: Unassigned » jhriggs

I just committed version 4.7r2 which may address several of the query issues. I have optimized several of the queries, changed the PRIMARY KEY on the quotes table, and added a couple of indexes to the quotes table. In my tests, I saw query times and page-creation times drop by 75% or more with these changes. After copying the new version of the module into your modules directory, be sure to run the /update.php script to have the database changes applied.

Note that if you are downloading the .tar.gz file from the web site, it may take several hours for the new version to appear. The .tar.gz files are only re-created every several hours. When you download the file, be sure the top entry in the CHANGELOG.txt file is 2006-05-22.

Taran’s picture

Taran commented

Wow... well, just revisited this after Drupal 4.7.1 and reverted to the initial quotes module of 4.7.0.

BIG DIFFERENCE.

Take a look at what was changed in the node table for 4.7.1 - nid and vid keyed together, etc.

Will try out your code to now.

By the way - thank you. :-)

jhriggs’s picture

jhriggs commented
Status: Active » Closed (fixed)

I assume this is OK now.