Help With Slow Queries Generated by from pbpBB?

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Scam Warning
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
rick5150
Registered User
Posts: 283
Joined: Wed Feb 02, 2005 6:19 pm

Help With Slow Queries Generated by from pbpBB?

Post by rick5150 » Thu Feb 07, 2013 5:05 pm

I am getting bombarded with slow queries and this has caught the attention of my host. The first suggestion was to delete the database and start fresh! I am not going to lose 5+ years of data and members. I know nothing about SQL and how to index tables to be more efficient, but since May 2007 everything has been just fine. How can I find problem and fix it ...without deleting the database and starting fresh, of course.

User avatar
Lumpy Burgertushie
Registered User
Posts: 66866
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: Help With Slow Queries Generated by from pbpBB?

Post by Lumpy Burgertushie » Thu Feb 07, 2013 5:39 pm

need to see the error messages, need to see what the host is talking about. although considering their advice to delete you database, I would not put much faith in anything they say.

by default, phpbb does not do this.

it is either going to be a MOD you have installed or a problem with the server and not phpbb.

Please fill out the Support Request Template Generator and post it back here to enable us to assist you better.

robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.2 Styles by PlanetStyles.net

If a tree falls in the forest and nobody is there, does it make a sound?

User avatar
Christian 2.0
Former Team Member
Posts: 4507
Joined: Wed Nov 04, 2009 11:16 pm
Location: UK
Name: Christian
Contact:

Re: Help With Slow Queries Generated by from pbpBB?

Post by Christian 2.0 » Thu Feb 07, 2013 6:07 pm

Lumpy Burgertushie wrote:by default, phpbb does not do this.
It does, regularly on large boards. phpBB can be notorious for CPU resource usage when a site gets busy enough.

Rick, as Robert suggested you'll need to get some more information from your host on which processes exactly are causing a load on their server. In the meantime you may wish to refer to: viewtopic.php?t=1114055

rick5150
Registered User
Posts: 283
Joined: Wed Feb 02, 2005 6:19 pm

Re: Help With Slow Queries Generated by from pbpBB?

Post by rick5150 » Thu Feb 07, 2013 6:24 pm

Thank you - I will see what is causing the problems. I will do that soon. I have no mods now. The only thing I added was:

automod-1.0.2
tapatalk
support tool kit
Glass template
Absolution template

I also installed ZB Block which seems to be helping. The killed_log.txt is getting pretty big.

rick5150
Registered User
Posts: 283
Joined: Wed Feb 02, 2005 6:19 pm

Re: Help With Slow Queries Generated by from pbpBB?

Post by rick5150 » Thu Feb 07, 2013 7:41 pm

Here is what I received from the host before all the mods. I did away with the WWH mod since that looked like the big offender, but I cannot do the same with sessions and posts. The Zen Cart one is my store, so that has to stay as well. The header for this is all screwed up, but that is the way I received it as well.

Code: Select all

Database User               CPUTime/hr       BusyTime/hr CPU/Busy    
RowsExamined/hr        RE*CPU/Busy TotalCPU     Cmds/hr  Cmds/hr Cmds/hr 
CtxSw/hr CtxSw/hr StrtCPU  EndCPU Qrys BusyTime CPUTime 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
mysite_php1                 454.5043        1,532.1377   0.2966        
344,662,606         78,926,053   0.0944      14,949    5,967   4,008     NULL     
NULL    NULL    NULL NULL     NULL    NULL 
 
+---------------+------------------------------------+-----------+--------------+------------------------+ 

| TABLE_SCHEMA  | TABLE_NAME                         | ROWS_READ | ROWS_CHANGED 
| ROWS_CHANGED_X_INDEXES | 
+---------------+------------------------------------+-----------+--------------+------------------------+ 

| mysite_php1 | WWH_TABLE                          |  60693880 |           19 
|                     19 | 
| mysite_php1 | phpbb_wwh                          |    144783 |           24 
|                     48 | 
| mysite_php1 | phpbb_sessions                     |     26425 |           61 
|                    244 | 
| mysite_php1 | phpbb_posts                        |     18224 |            0 
|                      0 | 
| mysite_znc2 | znc_banners_history                |     17148 |            0 
|                      0 |

User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: Help With Slow Queries Generated by from pbpBB?

Post by Oyabun1 » Fri Feb 08, 2013 12:13 am

rick5150 wrote:Here is what I received from the host before all the mods.
Before all which MODs? Clearly the WWH MOD is responsible for much of the load in that report so must have been installed. It may also access the sessions table accounting for some of the load there as well.

After removing the WWH MOD is the problem resolved?
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests

rick5150
Registered User
Posts: 283
Joined: Wed Feb 02, 2005 6:19 pm

Re: Help With Slow Queries Generated by from pbpBB?

Post by rick5150 » Fri Feb 08, 2013 1:55 am

Sorry, if I did not write that myself, I would not believe it. :oops: It should have said "...before I removed all the mods."

I started with a fresh forum install attached the original database, and then I cleaned it out with the STK to get rid of the WWH and a few other things.

It is still happening - not as bad though. I will try to get a new report.

User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: Help With Slow Queries Generated by from pbpBB?

Post by Oyabun1 » Fri Feb 08, 2013 3:32 am

You can do some checks yourself by enabling debug mode on the board.

Open /config.php

Find

Code: Select all

// @define('DEBUG', true);
// @define('DEBUG_EXTRA', true);   
Replace with

Code: Select all

@define('DEBUG', true);
@define('DEBUG_EXTRA', true); 
Use a proper text editor to make the edit, such as one of those suggested here, Tools needed to set up and customise phpBB. The encoding to save files in is UTF8 without BOM.

That will show a line in the footer similar to:
Time : 0.080s | 9 Queries | GZIP : Off | Memory Usage: 4.82 MiB | Explain
The Explain part is a link to a SQL report that gives information about each query that was run for that page. You may need to try a few different pages to find the one that uses the problem query.
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests

rick5150
Registered User
Posts: 283
Joined: Wed Feb 02, 2005 6:19 pm

Re: Help With Slow Queries Generated by from pbpBB?

Post by rick5150 » Fri Feb 08, 2013 1:14 pm

Great, I will try that - thanks. How do I access this information that is generated? Where will it display?

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50895
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Help With Slow Queries Generated by from pbpBB?

Post by stevemaury » Fri Feb 08, 2013 3:40 pm

When you click the link it will display on your computer.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

rick5150
Registered User
Posts: 283
Joined: Wed Feb 02, 2005 6:19 pm

Re: Help With Slow Queries Generated by from pbpBB?

Post by rick5150 » Fri Feb 08, 2013 4:03 pm

I kind of figured it would display on the computer, but I read that it would be in the footer and it was not showing. It is now though...

rick5150
Registered User
Posts: 283
Joined: Wed Feb 02, 2005 6:19 pm

Re: Help With Slow Queries Generated by from pbpBB?

Post by rick5150 » Fri Feb 08, 2013 4:14 pm

The slow query log is showing things like this:

The biggest query (rows examined) that I can find is this one so far...

Code: Select all

# Fri Feb  8 06:13:41 2013
# Query_time: 1.534267  Lock_time: 0.000121 Rows_sent: 1  Rows_examined: 2028
use mysite_php1;
SET timestamp=1360329221;
SELECT COUNT(p.post_id) AS prev_posts
  FROM phpbb_posts p
  WHERE p.topic_id = 571
  AND p.post_approved = 1 AND (p.post_time < 1359093133 OR (p.post_time = 1359093133 AND p.post_id <= 73340))
I have no idea what this means, but it just looks wrong to me.

Code: Select all

# Fri Feb  8 06:31:07 2013
# Query_time: 2.334815  Lock_time: 0.002784 Rows_sent: 0  Rows_examined: 26
use mysite_php1;
SET timestamp=1360330267;
DELETE FROM phpbb_search_wordmatch
  WHERE word_id IN ('2', '3', '4', '6', '13', '15', '19', '21', '26', '27', '29', '30', '33', '34', '36', '39', '44', '46', '47', '52', '53', '56', '69', '70', '73', '100', '111', '112', '113', '123', '125', '129', '137', '144', '146', '150', '153', '157', '158', '159', '191', '201', '205', '206', '214', '234', '236', '252', '259', '265', '266', '274', '286', '299', '301', '306', '312', '316', '320', '329', '330', '336', '342', '343', '344', '354', '393', '411', '422', '427', '433', '435', '449', '452', '455', '456', '459', '475', '488', '495', '499', '503', '510', '532', '544', '576', '582', '599', '658', '673', '681', '688', '697', '699', '701', '717', '718', '774', '775', '805', '807', '889', '915', '952', '955', '968', '980', '987', '1001', '1010', '1021', '1024', '1105', '1133', '1141', '1164', '1301', '1382', '1444', '1460', '1477', '1576', '1587', '1592', '1609', '1658', '1686', '1756', '1778', '1827', '1880', '2000', '2778')

# Fri Feb  8 06:39:26 2013
# Query_time: 4.315971  Lock_time: 2.025496 Rows_sent: 3  Rows_examined: 14
use mysite_znc2;
SET timestamp=1360330766;
select distinct p.products_id, p.products_image, pd.products_name, p.master_categories_id,pd.products_description
  from (znc_products p
  left join znc_featured f on p.products_id = f.products_id
  left join znc_products_description pd on p.products_id = pd.products_id)
  where p.products_id = f.products_id
  and p.products_id = pd.products_id
  and p.products_status = 1 and f.status = 1
  and pd.language_id = '1'
  and p.products_id in (3, 16, 17, 18, 19, 21, 22, 23, 24, 25, 26, 27, 28, 29, 31, 32, 33, 34, 36, 37, 38, 39, 40, 48, 58, 61, 62, 93, 105, 107)


User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50895
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Help With Slow Queries Generated by from pbpBB?

Post by stevemaury » Fri Feb 08, 2013 9:29 pm

Those tables in the second message in the second code box are not phpBB tables.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

rick5150
Registered User
Posts: 283
Joined: Wed Feb 02, 2005 6:19 pm

Re: Help With Slow Queries Generated by from pbpBB?

Post by rick5150 » Sat Feb 09, 2013 11:29 am

Yes, they are from the ZC installation - but I am concerned that something is not right about these. These two are from the memberlist page debug...

Code: Select all

SELECT *
FROM phpbb_users
WHERE user_id IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 13, 14, 15, 17, 18, 19, 20, 23, 24, 25, 27, 28, 29, 30, 31, 33, 35, 43, 44, 46, 48, 49, 50, 52, 53, 54, 57, 62, 64, 65, 66, 69, 72, 73, 74, 75, 77, 78, 83, 86)

Code: Select all

SELECT *
FROM phpbb_profile_fields_data
WHERE user_id IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 13, 14, 15, 17, 18, 19, 20, 23, 24, 25, 27, 28, 29, 30, 31, 33, 35, 43, 44, 46, 48, 49, 50, 52, 53, 54, 57, 62, 64, 65, 66, 69, 72, 73, 74, 75, 77, 78, 83, 86)
I now have the ability to run debugging, but since I do not understand the results, it matters very little. Are queries of this type normal behavior for phpbb?

rick5150
Registered User
Posts: 283
Joined: Wed Feb 02, 2005 6:19 pm

Re: Help With Slow Queries Generated by from pbpBB?

Post by rick5150 » Sat Feb 09, 2013 11:39 am

I think I figured out why those are generated. Each member page has a set of higher numbers, so it looks as if they are the result of the member numbers of that page? Probably normal. I have thousands of pages and each is generating a report based on the page.

Is there a page on this site that will help me narrow where I should be looking and how to interpret these results or is that outside the realm of what this site was designed for?

Locked

Return to “[3.0.x] Support Forum”