Slow Database Query Slowing Down Site
Permalink
After going on a year of trying to speedup a slow ecommerce site, talked with BlueHost who identified a query that was killing the site. As noted below, 251,159 rows were examined to produce a result from btCoreScrapbookDisplay. The representative was a database specialist, and did some indexing (not exactly sure how or what)and the site speed has improved 10 fold without cache.
Does anybody know what was going on here, and what I should do to correct beyond what BlueHost was able to do?
While I am hopeful this is fixed, got to say I have celebrated before on what I thought fixed the speed issue only to find a deterioration soon afterwards.
Thanks much for any input!
# Thu Mar 8 06:39:37 2012
# Query_time: 2.566184 Lock_time: 0.001729 Rows_sent: 0 Rows_examined: 251159
use globams9_cnc6;
SELECT cID, cvID, arHandle FROM CollectionVersionBlocks WHERE bID in (select bID from btCoreScrapbookDisplay where bOriginalID = '417')
Does anybody know what was going on here, and what I should do to correct beyond what BlueHost was able to do?
While I am hopeful this is fixed, got to say I have celebrated before on what I thought fixed the speed issue only to find a deterioration soon afterwards.
Thanks much for any input!
# Thu Mar 8 06:39:37 2012
# Query_time: 2.566184 Lock_time: 0.001729 Rows_sent: 0 Rows_examined: 251159
use globams9_cnc6;
SELECT cID, cvID, arHandle FROM CollectionVersionBlocks WHERE bID in (select bID from btCoreScrapbookDisplay where bOriginalID = '417')
Interesting. Any improvement after you run this query?
Andrew, No improvement over what was done by BH this morning, have not generated any logs since. Suspect this is what he did to alleviate the situation as he said he indexed to the table. Thanks Much!
Oh, I missed that part of the original message.
I've updated that particular query in the 5.5.3-devel branch (NOT production ready, not even close) of github with a rewritten query that should better take advantage of that index.
I've updated that particular query in the 5.5.3-devel branch (NOT production ready, not even close) of github with a rewritten query that should better take advantage of that index.
Thanks!
Andrew, Indexing the “scrapbookdisplay” has really done wonders in speeding up the site, “Fantastic”
Without Cache Pages Load in an average of 4 -5 seconds, with a first byte time of 1.5 seconds
With Cache Pages Load in an average of 2 - 2.5 seconds, with a first byte time of .3 seconds
Disadvantage with using Cache is that on first load, extends to 7 - 8 seconds, I do not cache product pages, so they take 7 - 8 seconds most of the time.
Miser is being used under both situations.
After a long struggle with this site, finally find the speed approaching acceptable. Looking forward to any other enhancements regarding the indexing in 5.3. Thanks!
Without Cache Pages Load in an average of 4 -5 seconds, with a first byte time of 1.5 seconds
With Cache Pages Load in an average of 2 - 2.5 seconds, with a first byte time of .3 seconds
Disadvantage with using Cache is that on first load, extends to 7 - 8 seconds, I do not cache product pages, so they take 7 - 8 seconds most of the time.
Miser is being used under both situations.
After a long struggle with this site, finally find the speed approaching acceptable. Looking forward to any other enhancements regarding the indexing in 5.3. Thanks!
Andrew, Believe I may have found the issue that is stalling old version delete tied to the log scrapbook index errors. I do not generate errors per the previous mentioned fix unless I remove old versions. Removing old versions is darn near impossible taking tons of time. After removing about 100 old page versions I ended up with tons (hundreds) of scrapbook index errors.
The previous fix mentioned above fixed the user issue, but not when removing old page versions.
Do you have any Idea on what I can do to fix? I am confident if I fix it, it will fix the old version issue I have been struggling with. Thx!
Error Sample:
# Thu May 31 06:00:02 2012
# Query_time: 1.596043 Lock_time: 0.000058 Rows_sent: 0 Rows_examined: 238447
use globams9_cnc6;
SET timestamp=1338465602;
SELECT cID, cvID, arHandle FROM CollectionVersionBlocks WHERE bID in (select bID from btCoreScrapbookDisplay where bOriginalID = '1238')
# Thu May 31 06:00:04 2012
# Query_time: 1.537720 Lock_time: 0.000055 Rows_sent: 0 Rows_examined: 238446
SET timestamp=1338465604;
SELECT cID, cvID, arHandle FROM CollectionVersionBlocks WHERE bID in (select bID from btCoreScrapbookDisplay where bOriginalID = '1240')
The previous fix mentioned above fixed the user issue, but not when removing old page versions.
Do you have any Idea on what I can do to fix? I am confident if I fix it, it will fix the old version issue I have been struggling with. Thx!
Error Sample:
# Thu May 31 06:00:02 2012
# Query_time: 1.596043 Lock_time: 0.000058 Rows_sent: 0 Rows_examined: 238447
use globams9_cnc6;
SET timestamp=1338465602;
SELECT cID, cvID, arHandle FROM CollectionVersionBlocks WHERE bID in (select bID from btCoreScrapbookDisplay where bOriginalID = '1238')
# Thu May 31 06:00:04 2012
# Query_time: 1.537720 Lock_time: 0.000055 Rows_sent: 0 Rows_examined: 238446
SET timestamp=1338465604;
SELECT cID, cvID, arHandle FROM CollectionVersionBlocks WHERE bID in (select bID from btCoreScrapbookDisplay where bOriginalID = '1240')
