vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)
-   -   Queries, Queries, Queries .. (https://vborg.vbsupport.ru/showthread.php?t=45903)

Sebastian 11-19-2002 04:09 PM

Queries, Queries, Queries ..
 
Quote:

Page generated in 0.36285806 seconds (83.35% PHP - 16.65% MySQL) with 18 queries.
[Debug Mode OFF] [PHP v4.2.3] [GZIP enabled - level 1]
I am curious what the average STOCK with 1 - 2 hacks board get.. I just upgraded to 229, I had a problem before because all I had was the welcome panel hack and I was getting 27 queries.. now I am getting 18 which is better, but I have still seen board with 14 - 15 queries WITH hacks! What gives? how are they getting 15 queries with several hacks and I have none and I am getting 18, there is obviously a little problem there :P

Also, before upgrading I was getting 97% PHP and 3% mysql .. now the sql percent doubled..

Can someone explain why is this?

Xenon 11-19-2002 04:36 PM

haven't looked at the vb229 code yet, perhaps something has been optimized....

Also: there are many methods out to reduce queries, try to search pm-queries total threads/posts and so on.

also if you don't want some things, you can reduce queries to, also with large hacked boards ;)

Sebastian 11-19-2002 08:40 PM

Thanks Xenon, I enabled debug mode to see queries.. vbproarcade is adding 3 queries to forum home when there isn't even anything vbproarcade related on forum home..
I have to asks Mr. vbproarcade :paranoid:

Quote:

Query: SELECT * FROM arcadegames ORDER BY title ASC
Time before: 0.32712602615356
Time after: 0.3318430185318

--------------------------------------------------------------------------------

Query: SELECT template FROM template WHERE title='vbproarcade_gamelinkbit' AND (templatesetid=-1 OR templatesetid=4) ORDER BY templatesetid DESC LIMIT 1
Time before: 0.33376896381378
Time after: 0.33636605739594

--------------------------------------------------------------------------------

Query: SELECT template FROM template WHERE title='vbproarcade_gamelinks' AND (templatesetid=-1 OR templatesetid=4) ORDER BY templatesetid DESC LIMIT 1
Time before: 0.34161496162415
Time after: 0.3439689874649

Xenon 11-19-2002 08:49 PM

you can reduce the query ammount by adding vbproarcade_gamelinkbit and vbproarcade_gamelinks to the $templatesused variable ;)

Sebastian 11-19-2002 08:57 PM

sorry am a newbie, can you explain how? :confused:

Xenon 11-19-2002 09:05 PM

in your index.php you'll find something like that:
Code:

$templatesused='forumhome_birthdaybit,error_nopermission,forumhome_pmloggedin,forumhome_welcometext,forumhome_logoutcode,forumhome_newposts,forumhome_todayposts,forumhome_logincode,forumhome_loggedinuser,forumhome_loggedinusers,forumhome_lastpostby,forumhome_moderator,forumhome_forumbit_level1_nopost,forumhome_forumbit_level1_post,forumhome_forumbit_level2_nopost,forumhome_forumbit_level2_post,forumhome,forumhome_unregmessage';
just add the two names there

Erwin 11-19-2002 09:34 PM

Make sure all templates being eval() are cached in $templatesused in all your php files. :) That helps.

Sebastian 11-20-2002 03:19 AM

Thanks guys, I am down from 21 queries to 18, I am trying to go lower, lol .. how does this look? just trying to make sure nothing is looping :P

is it bad if I keep debug=1 in config.php so I could keep an eye on this page when I feel the need? or is there another easy method?

Code:

Query: SELECT template FROM template WHERE title='options'
Time before: 0.15240097045898
Time after:  0.15558695793152

--------------------------------------------------------------------------------

Query: SELECT sessionhash,userid,host,useragent,styleid FROM session WHERE lastactivity>1037764842 AND sessionhash='c9f334daa0620212783908c9197b180f' AND (host='68.63.161.176' OR (althash='' AND althash<>'')) AND useragent='Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;'
Time before: 0.19247901439667
Time after:  0.19490694999695

--------------------------------------------------------------------------------

Query: SELECT user.*,userfield.* FROM user LEFT JOIN userfield ON userfield.userid=user.userid WHERE user.userid='1'
Time before: 0.19714498519897
Time after:  0.20041298866272

--------------------------------------------------------------------------------

Query: SELECT templatesetid,replacementsetid,userselect FROM style WHERE styleid='1'
Time before: 0.20493304729462
Time after:  0.20647895336151

--------------------------------------------------------------------------------

Query: SELECT template,title
                          FROM template
                          WHERE (title IN ('forumhome_birthdaybit','error_nopermission','forumhome_pmloggedin','forumhome_welcometext','forumhome_logoutcode','forumhome_newposts','forumhome_todayposts','forumhome_logincode','forumhome_loggedinuser','forumhome_loggedinusers','forumhome_lastpostby','forumhome_moderator','forumhome_forumbit_level1_nopost','forumhome_forumbit_level1_post','forumhome_forumbit_level2_nopost','forumhome_forumbit_level2_post','forumhome','forumhome_unregmessage','vbproarcade_gamelinkbit','vbproarcade_gamelinks','gobutton','timezone','username_loggedout','username_loggedin','phpinclude','headinclude','header','footer','forumjumpbit','forumjump','nav_linkoff','nav_linkon','navbar','nav_joiner','pagenav','pagenav_curpage','pagenav_firstlink','pagenav_lastlink','pagenav_nextlink','pagenav_pagelink','pagenav_prevlink','home_microstats')
                            AND (templatesetid=-1 OR templatesetid=4))
                          OR (title = 'maxloggedin')
                          OR (title = 'birthdays')
                          ORDER BY templatesetid
Time before: 0.20853698253632
Time after:  0.23826396465302

--------------------------------------------------------------------------------

Query: SELECT * FROM usergroup WHERE usergroupid=6
Time before: 0.27794396877289
Time after:  0.28122901916504

--------------------------------------------------------------------------------

Query: SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=1
Time before: 0.28548502922058
Time after:  0.28720498085022

--------------------------------------------------------------------------------

Query: SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=1 AND dateline>1037749782 AND folderid=0
Time before: 0.28879702091217
Time after:  0.29008603096008

--------------------------------------------------------------------------------

Query: SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=1 AND messageread=0 AND folderid=0
Time before: 0.29159295558929
Time after:  0.29285097122192

--------------------------------------------------------------------------------

Query: SELECT COUNT(*) AS users,MAX(userid) AS max FROM user
Time before: 0.30035698413849
Time after:  0.30172097682953

--------------------------------------------------------------------------------

Query: SELECT COUNT(*) AS posts FROM post
Time before: 0.303297996521
Time after:  0.30426096916199

--------------------------------------------------------------------------------

Query: SELECT COUNT(*) AS threads FROM thread
Time before: 0.30569303035736
Time after:  0.30717396736145

--------------------------------------------------------------------------------

Query: SELECT userid,username FROM user WHERE userid=24
Time before: 0.30870699882507
Time after:  0.31004095077515

--------------------------------------------------------------------------------

Query: SELECT * FROM forum WHERE displayorder<>0 AND active=1 ORDER BY parentid,displayorder
Time before: 0.31353199481964
Time after:  0.31699800491333

--------------------------------------------------------------------------------

Query: SELECT forumid,canview,canpostnew FROM forumpermission WHERE usergroupid='6'
Time before: 0.32239699363708
Time after:  0.32396697998047

--------------------------------------------------------------------------------

Query: SELECT user.userid,user.username,moderator.forumid
                                  FROM moderator
                                  LEFT JOIN user
                                    ON (moderator.userid=user.userid)
                                  ORDER BY user.username
Time before: 0.3255490064621
Time after:  0.32686197757721

--------------------------------------------------------------------------------

Query: SELECT COUNT(*) AS sessions FROM session WHERE userid=0 AND lastactivity>1037764843
Time before: 0.32852005958557
Time after:  0.3303279876709

--------------------------------------------------------------------------------

Query: SELECT DISTINCT session.userid,username,invisible,usergroupid
                              FROM session
                              LEFT JOIN user ON (user.userid=session.userid)
                              WHERE session.userid>0 AND session.lastactivity>1037764843
                              ORDER BY invisible ASC, username ASC
Time before: 0.3318909406662
Time after:  0.33439099788666

--------------------------------------------------------------------------------

Query: SELECT findword,replaceword FROM replacement WHERE replacementsetid IN(-1,'4') ORDER BY replacementsetid DESC,replacementid DESC
Time before: 0.45747303962708
Time after:  0.46432197093964

--------------------------------------------------------------------------------


Page generated in 0.4018360376358 seconds with 19 queries,
spending 0.070354580879211 doing MySQL queries and 0.33148145675659 doing PHP things.
Query: UPDATE session SET lastactivity=1037765742,location='/forums/index.php?s=&showqueries=1' WHERE sessionhash='c9f334daa0620212783908c9197b180f' Time before: 0.5302209854126 Time after: 0.53218805789948
--------------------------------------------------------------------------------
Query: UPDATE user SET lastactivity=1037765742,inforum='0' WHERE userid='1' Time before: 0.53371500968933 Time after: 0.53505504131317
--------------------------------------------------------------------------------


joor 11-20-2002 04:01 AM

Super noob here, what is the debug=1 in config.php? I would like to try that.

:banana:

Sebastian 11-20-2002 04:34 AM

debug allows you to troubleshoot problems or when your trying to optimize things like me :)

in admin/config.php add $debug=1; on a new line, then go to your forums and on the page you want to debug add: &showqueries=1 or &explain=1 to the url .. for example, if you want to see the queries being performed in forum home you're url should look like: /forums/index.php?s=&showqueries=1

don't forget to remove it from the config once your done.

joor 11-20-2002 06:29 AM

Oh cool! I added the debug info but didn't know about the querystring to add. Just moved from a shared to a dedicated today, want to see my difference?

Shared Server:
Page generated in 1.614294052124 seconds with 21 queries,
spending 0.69835984706879 doing MySQL queries and 0.91593420505524 doing PHP things.

Dedicated Server:
Page generated in 0.10820305347443 seconds with 21 queries,
spending 0.029100894927979 doing MySQL queries and 0.079102158546448 doing PHP things

Woot! Thanks, I didn't want to install the vbMicrostats to get that info. So now I have your question, is it safe to leave that debug in config?

Erwin 11-20-2002 07:29 AM

No. :) It is a security hole that hackers can take advantage of. This is why from vB 2.2.4 upwards it was disabled as standard.

Xenon 11-20-2002 01:35 PM

also with debug mode you can change the gloabal template set and other inner vb-things, which can be very risky, too if you don't know what you do ;)

Dean C 11-20-2002 07:39 PM

Geez 18 queries...

I have 48 on my forumhome..

- miSt

Sebastian 11-20-2002 08:34 PM

yeah, and I want it lower lol ;)

N9ne 11-20-2002 09:09 PM

48 on forumhome? Mist, you're killing your server :eek:

Sebastian 11-21-2002 12:06 AM

Forum home usually performs more queries than any other page .. 48 is a tad high, I've seen some boards over 250! it makes me very nervous :paranoid:

they obviously do not know what the heck they are doing and don't know how to manage their forums for that matter.

Dean C 11-21-2002 03:30 PM

Well i do know what im doing..

But my server can cope very well...

Im going to check my file tonight for any misplaced template calls that aren't in the $templatesused variable :)

- miSt

Xenon 11-21-2002 04:15 PM

if you have questions, just ask ;)

there are some guys out here who likes to optimize queries ;)

N9ne 11-21-2002 05:10 PM

I'm one of them, but TECK is the god of query optimization!

Anyway...I definitely think anything above 35 on forumhome is not good...I, on my "version 2" of my forum, will have 23 queries logged out, and 25 logged in :)

The only problems I have are with showthread, as there is much to be put there.

Dean C 11-21-2002 06:02 PM

Mind if i send my index.php your way Xenon?

I had a quick go tonite and only cut down one query strangly...

- miSt

Sebastian 11-21-2002 08:57 PM

POST IT! We all want to see/help :)

NTLDR 11-21-2002 09:01 PM

Quote:

Originally posted by Sebastian
POST IT! We all want to see/help :)
You are not allowed to post complete vB here ;)

Sebastian 11-21-2002 09:43 PM

Quote:

Originally posted by NTLDR
You are not allowed to post complete vB here ;)
Thanks for reminding me, I forgot :rolleyes:

Xenon 11-22-2002 11:29 AM

mist, yes you can send it to me, then i'll look at it and try to find some things ;)

but don't expect too much too fast. i've got a lot of work ;)


All times are GMT. The time now is 03:51 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01199 seconds
  • Memory Usage 1,803KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (2)bbcode_code_printable
  • (4)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (25)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 

Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.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/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.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
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete