how much queries do you have running whan a...
 

Index page is loading? - 10 q'
forum page is loading? - 15 q'
thread page is loading?- 34-35 q' (something is wrong and it's killing the server..)

I have to find out why the hell i have 35 q' when a thread is loading, its killing my server, somthing like 400 users are entering a page in a sec, do the math :X

so i wanted to see what is the situation on other boards ?

thanks lot for cooperating :]

What mods do you have installed? Modifications tends to add queries, especially if you have iSHOP installed.

I'm guessing that you have a lot going on in your postbit.

It's not how many queries you do that matters, it's how long they take.

Run the board in debug mode, and have a look at the explain. It should show you which queries are causing the headache.

Eikinskjaldi i'm running debug mode, where should i find the explain?
also, how can i see the debug mode as a guest ? i know that it isn't really possible, but i have queries that are running just when guests are visiting the board,
thanks lot.

Index: 4
Forum: 7
Thread: 9

We are highly modified however and making use of memcache, etc.

index: 15
forumdisplay: 16 / in eventboard: 95 (omg i have to do something)
thread: 16 / 33 in eventboard

phu heavy

can you explain how? how are you highly modified and don't have lot of queries at all?

here is the the debug info - in a thread..
vBulletin 3.6.7 Debug Information
Page Generation 1.05369 seconds Memory Usage 3,076KB Queries Executed 30 (?)
More Information
Template Usage:
(1)SHOWTHREAD
(1)editor_clientscript
(1)editor_jsoptions_font
(1)editor_jsoptions_size
(15)editor_smilie
(1)editor_smiliebox
(5)editor_smiliebox_row
(1)footer
(1)forumjump
(1)forumrules
(1)gobutton
(1)header
(1)headinclude
(2)im_aim
(5)im_icq
(1)im_msn
(1)im_yahoo
(1)navbar
(4)navbar_link
(1)newpost_disablesmiliesoption
(108)option
(1)pagenav
(1)pagenav_curpage
(1)pagenav_pagelink
(15)postbit_ip
(15)postbit_legacy
(15)postbit_onlinestatus
(15)postbit_wrapper
(1)quickreply_addon
(1)showthread_quickreply
(1)showthread_quickreply_manage
(1)sid_hide_head
(1)smilies_quickreply
(1)spacer_close
(1)spacer_open
(1)threadadmin_imod_menu_post
(1)welcome_headers
Included Files:
vbseo.php
includes/functions_vbseo.php
includes/config_vbseo.php
includes/config.php
showthread.php
global.php
includes/init.php
includes/class_core.php
includes/functions.php
includes/class_hook.php
plugins/vbplaza_fetch_foruminfo.php
plugins/vbplaza_cache_templates.php
ewt/core/functions_spidercheck.php
plugins/vbplaza_global_start.php
includes/functions_bigthree.php
includes/class_postbit.php
includes/class_bbcode.php
includes/functions_reputation.php
plugins/vbplaza_showthread_start.php
plugins/vbplaza_postbit_start.php
plugins/vbplaza_postbit_imicons.php
plugins/vbplaza_postbit_complete.php
includes/functions_editor.php
plugins/vbplaza_forumrules.php

Hooks Called:
init_startup
fetch_userinfo_query
fetch_musername
fetch_userinfo
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
bbcode_parse_start
postbit_imicons
bbcode_parse_complete
postbit_display_complete
pagenav_page
pagenav_complete
editor_wysiwyg_compatible
editor_toolbar_switch
editor_toolbar_start
editor_toolbar_end
forumrules
navbits
navbits_complete
showthread_complete

can some one help me please?
i have somthing like 7-8K uniques a day, 60K~ page views.. (45K reg' users, 1,100,000~ posts etc')

Some where in debug it will tell you how much of that second is php and how much mysql. What is the total mysql processing time?

My guess is your problem is php related. Have you got a memory acelerator installed?

i can't really find where, about the mysql \ php processing time , I got the latest version of eaccelerator (i think 0.9.5)

p.s there is another board on the server, with out any mods, it has a much better generation time, most of the pages have somthing likt 0.05-0.4 sec' but much less modified ( 3.5.8 ) ..

how can i find the queries that makes us an headache ?

in the debug mode there is a Messages:
and there i can choose from a scroll bar lot of things like "today info.. not in cache"
returning default icon / some thing icon id etc'

and if I'll pick one of those nothing will happened :\

what to do?

just realized that that message is the part that gives me the queries , but how do i know which query makes the problem?

We're highly modified to REMOVE queries :) We actually make extensive use of memcache to cache things like templates, which reduces the amount of queries per page actually hitting our database server. There are queries, but they hit memcache, and don't count.

Index - 3 queries
forumdisplay - 9 queries (with "currently active users" enabled)
showthread - 7 queries

I've read both of yours posts on VB com , in Kron's (muts) thread "Some tuning tips from the MySQL experts" and i have to say that I'm really impressed , i don't have lot of SQL + PHP knowledge but my partner will try to do the things that you had said there , I've installed memcache (https://vborg.vbsupport.ru/showthread.php?t=150431) and i would really like to know how can i do that - looking forward to your replies, thanks lot.

p.s - this is my board http://www.atraf-f.net (running in Hebrew ) I just wanted you to have a look at the index look, in your opinion can i optimize the code that makes the index to look like that? I'll upload it if you think you can give me an hand with optimizing it .

thanks :)

It would be so great to know more about your tuning!

It's killing me, I have 2 boards on my server one has a DB which weights 320 MB
the other one is 800~ MB, and im using memcache (and there isn't any improvement in the page generation time)
- first board - On index Executed 9 queries (0.1.. s')
inside a forum Executed 13 queries (0.2)
inside a thread Executed 9 queries(0.2)
DB 300 MB ( 1 modification installed )

- second board (3.6.7)
on index - Queries Executed 9 (0.66 s')
in a forum - Executed 14 (1.6 s' DAMT!!)
in a thread Queries Executed 24 (0.9 s') (24 que' What The Hell?!)
DB 800 MB (highly modified) - running memcache and it hadn't helped at all..
how can i decrease the page generation time?

i run debug mode, and i don't really know how to read this tool output..

I have to do somthing..

Thanks lot..

You could ask on vbulletin.com -> Server Configuration Forum for more help!

It has nothing to do with the server, this is pure programming ..
I have optimized my server, most of the show users online are off , I really don't know what to do, maybe it has something to do with the MySql collection, because we had converted our DB to UTF8 and it had grown form 500 MB to 800..
but I don't think that it's relevant because the other board DB was coverted to UTF8 too and it is running pretty fast..

I'll be happy to get some tips \ help..

Unfortunately I can't really help any more than I have in my many posts over at vBulletin.com (search for posts by username KrON).

I have outlined some of the steps we've taken, but I can't really provide actual directions or provide support for modifications made.

A lot of the steps we've taken are complicated or proprietary and not really something to be undertaken by anyone but people with a solid background in PHP and vBulletin.

I have a partner which have s a solid background in PHP and vBulletin,
I'm asking you for tips , how to know which queries are getting us the reason that we have high Page Generation time, i saw the tool you gave on VB.com but i can't compile MYSQL from source cous it can make problems with the DB cous my board is in Hebrew..

what can i do? i just want to get rid from 5-10 unuseful queries, that are delaying our page load time..

Well that tool I gave you is very beneficial. You don't have to apply the microslow patch to use the query analyzer I don't think. Turn on your slowquery log. Log your queries for a while, then work backwards to see what queries are taking the most time, and figure out what functionality they relate to.

Index: 9
Forum: 16
Thread: 10

Happy with it (we have about 10-15 mods installed), but I will get worried about the 16 query forum display when our board gets more visitors.

DEBUG mode enabled, logged as Guest:
Index
* Page Generation 0.01844 seconds
* Memory Usage 553KB
* Queries Executed 4

Forumdisplay
* Page Generation 0.03062 seconds
* Memory Usage 635KB
* Queries Executed 9

Showthread
* Page Generation 0.03081 seconds
* Memory Usage 778KB
* Queries Executed 6

DEBUG mode enabled, logged as Admin:
Index
* Page Generation 0.02090 seconds
* Memory Usage 602KB
* Queries Executed 5

Forumdisplay
* Page Generation 0.03693 seconds
* Memory Usage 714KB
* Queries Executed 10

Showthread
* Page Generation 0.05298 seconds
* Memory Usage 921KB
* Queries Executed 7

I might work on the forumdisplay to lower it even more... but I really don't like to edit the vBulletin code. The above results were obtained while inserting code into hooks.
I think the best part is the memory usage. I lowered it's value from 3500KB to 600-900KB.

I use lighttpd as server software. All applications are set on bleeding edge.
Yes, I hate Apache... it eats memory like an elephant. :)

EDIT: I use both XCache and memcached, on my server.
Since we are here, I wonder if it's better to simply remove the queries or cache them with memcached. Somehow, I think 4 queries on the frontpage is better then 9, even with mighty memcached. Another issue is how to check if memcached is actually caching the queries. If I use -vv, it will give me all kind of huge numbers into the log files. However, memcached lacks on documentation so it's actually hard to know how and when the queries are processed/cached.

Let me know what you think about this matter. Thanks for your advice.

Actually, they count very much. Even if memcached caches your datastore, it will still use bandwidth. Yes you save the processor, but your month bandwidth will increase. On the other hand, if you use XCache... it will chew the disk I/O. The real question is: What is more beneficial, performance wise?

Thanks for the input. :)

That is very impressive TECK

In my opinion, bandwidth will always be cheaper than CPU time or disk. I don't see how moving a query from hitting a MySQL database to memcache increases my bandwidth any, unless you're referring to the traffic between the memcache or database server and your webservers, but I'd hope that any site that is doing enough traffic to justify a memcache setup would most likely have an unmetered private network between their servers.

In my opinion, bandwidth will always be cheaper than CPU time or disk.
Very good point, mute. :)

I'd hope that any site that is doing enough traffic to justify a memcache setup would most likely have an unmetered private network between their servers.
Another excellent reflection.

So what do we do. Do we put emphasis on the memcached or XCache? In other words, more queries and less PHP files to be cached?
Personally, I never liked executing queries. Instead, I cache the query data with XCache... avoiding possible table locks. I have this theory: the less queries you run the easier it is for the MySQL server.

Recently, I had an interesting talk with one of the vBulletin developers and he told me that they are not 100% sure what is the best solution: cache more queries or files.
For example, in vBulletin 4, you will be able to cache the templates as files, on the vBulletin datastore, or directly with memcached.
That is quite ingenious, since you have the liberty to use whatever method suits best your forum.

Please guys, comment more on this issue. Your opinion is important to me.

Floren