Go Back   vb.org Archive > vBulletin 4 Discussion > vB4 Programming Discussions
  #1  
Old 05-05-2012, 12:59 PM
mdreamer's Avatar
mdreamer mdreamer is offline
 
Join Date: Jul 2008
Posts: 67
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default Very Slow query - How can I find the source of it?

Hi,

I recently checked my DB and run some statistics\analytical reports and I came up with this:

### 8 Queries
### Total time: 143.073695, Average time: 17.884211875
### Taking 14.020451 , 16.494445 , 17.016344 , 18.409444 , 18.767823 , 18.886155 , 19.287588 , 20.191445 seconds to complete
### Rows analyzed 794111, 794111, 794135, 794135, 794139, 794143, 794143 and 794143
SET timestamp=XXX;
(
SELECT
a.attachmentid, a.contenttypeid, a.displayorder
FROM attachment AS a
INNER JOIN filedata AS fd ON (a.filedataid = fd.filedataid)
LEFT JOIN attachmenttype AS at ON (at.extension = fd.extension)
INNER JOIN album AS album ON (album.albumid = a.contentid)
LEFT JOIN profileblockprivacy AS pbp ON (pbp.userid = a.userid AND pbp.blockid = 'XXX')
LEFT JOIN user AS user ON (a.userid = user.userid)
WHERE
a.contenttypeid = XXX AND
(
(
a.contentid = XXX
AND
a.userid = XXX
)
OR
(
(
a.state <> 'XXX'
OR
a.userid = XXX
) AND album.state = 'XXX' AND album.state <> 'XXX' AND (a.userid = XXX OR pbp.requirement IS NULL OR pbp.requirement = XXX)
)
)
AND a.attachmentid IN (XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, XXX, X......XX)

How can I find out what causes this?

Thanks
Reply With Quote
  #2  
Old 05-05-2012, 03:03 PM
kh99 kh99 is offline
 
Join Date: Aug 2009
Location: Maine
Posts: 13,185
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Well, I'm pretty sure it's part of vbulletin. If you look in packages/vbattach/attach.php there is a function fetch_sql_ids_specific() which builds part of it, but I don't know where in the vb code that gets called or even in how many places.
Reply With Quote
  #3  
Old 05-05-2012, 03:35 PM
mdreamer's Avatar
mdreamer mdreamer is offline
 
Join Date: Jul 2008
Posts: 67
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

How can I know what calls this function? (assuming it is it...)
Reply With Quote
  #4  
Old 05-05-2012, 03:42 PM
Lynne's Avatar
Lynne Lynne is offline
 
Join Date: Sep 2004
Location: California/Idaho
Posts: 41,180
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Get the function name and then search in your files for where the function is called.
Reply With Quote
  #5  
Old 05-05-2012, 03:58 PM
mdreamer's Avatar
mdreamer mdreamer is offline
 
Join Date: Jul 2008
Posts: 67
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

You mean "build" the function call (with the arguments) that calls fetch_sql_ids_specific() ?

Thanks guys
Reply With Quote
  #6  
Old 05-05-2012, 04:09 PM
kh99 kh99 is offline
 
Join Date: Aug 2009
Location: Maine
Posts: 13,185
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

I'm not sure what you mean by that. In any case I was being a bit lazy because of course it's possible to find where that function is called, but in this case a search finds 6 calls to that function, and they are in classes which means you next have to find where those classes are created and which method calls (or whatever they're called in php) result in fetch_sql_ids_specific() being called.

Another way would be to temporarily edit that file and put in code that checks for the parameters you're looking for and calls debug_backtrace() to get the call stack. Then you'd have to output it in some way (I usually create a file and write out the output, but I know there can be permission problems trying to do that depending on who your server is set up).
Reply With Quote
  #7  
Old 05-10-2012, 11:40 PM
mdreamer's Avatar
mdreamer mdreamer is offline
 
Join Date: Jul 2008
Posts: 67
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

I'll look for the fetch_sql_ids_specific() calling in the files. I'll report my results.
Reply With Quote
  #8  
Old 05-11-2012, 12:27 AM
blind-eddie's Avatar
blind-eddie blind-eddie is offline
 
Join Date: Apr 2006
Location: Michigan
Posts: 2,310
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Via FTP, look in your tmp file (before public_html) you will then see the mysql_slow_queries folder. It will contain many logs with every slow querie you have ever had, all dated.
Reply With Quote
  #9  
Old 05-14-2012, 08:39 PM
mdreamer's Avatar
mdreamer mdreamer is offline
 
Join Date: Jul 2008
Posts: 67
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

thanks
I'll check it out
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 01:53 PM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.03902 seconds
  • Memory Usage 2,229KB
  • Queries Executed 11 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (9)post_thanks_box
  • (9)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (9)post_thanks_postbit_info
  • (9)postbit
  • (9)postbit_onlinestatus
  • (9)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete