More than 100 queries for a thread ?!

30 posts in this topic

Posted

I'm still not running "live" with my IPB install, which is an import of a 4M posts VB install.

Everything works well, but the performance is awful compared to VB3.7 (my IPB runs with CDN, sphinx, xcache and all I can think of is enabled to get better performance)

Looking at the debug output, it can take up to 100 queries to generate the first page of a topic (for the first time), some pages take 14 seconds to render. (Running on a 8CPU 12G MySQL server - of course the server is running other sites with substantial load at the same time.)

I understand that when I hit "reload" the query count goes from 101+ to 13, sometimes less. And beeing the one and only user of my test install does not help having stuff in cache either.

But I'm seriously considering never putting this install live with insane query counts like that. I don't have any fancy mods involved either.

Is that considered normal performance by IPBers ? So, is there a way to have less queries for simple tasks like... viewing a thread ? Am I doing something wrong ? An "insane queries" checkbox to untick ? :)

Share this post


Link to post
Share on other sites

Posted

Do you have mods installed? That might be the source of the queries.

Share this post


Link to post
Share on other sites

Posted

To me, that query count seems insane but correct.

We also have a large board (well over 5m posts) converted from phpBB, and the first page of a topic, first load is sometimes 93 queries and then 12-13 afterwards. However, it takes about 0.8s to render in the first instance and about 0.19s after that. I also made sure to check on a busy forum and a large thread to see if it made a difference (the thread has over 1000 replies).

I suspect that I've just never noticed it before as 0.8s isn't a huge amount of time but 93 queries (or 100 in your case) is pretty insane... I wonder why it needs so many. I suspect a lot of it will be caching posts, but who knows? It might be worth configuring your MySQL Slow Query log to run at a lower amount of seconds (maybe 15) and see if there's a single query that's really slowing you up, perhaps an index could be added.

Share this post


Link to post
Share on other sites

Posted

I'm sure that once it is running with real people on it, caching will make a difference, but the fact remains (and you confirm it), in my 3.3 install with no mods whatsoever, it takes 101 or 103 queries to generate the first page of a thread for the first time.
With SQL slowdowns probably caused by my other websites on the same database (but well, no slowdowns with VB3.7 : he does max 15 queries for the same output)

Again I realize that IPB3.3 has way more features than my vintage 3.7 full of tweaks; but 100 queries just seems... wrong :-)

The front page of IPC2.3 with a dozen blocks is "only" 50 queries !
What the HECK is IPB3.3 doing with over a hundred queries just to display a thread ???
If it's "caching" stuff, can I pre-cache it to avoid major slowdowns to my users ?

I'm happy to see that, at least, it's not something wrong with my install :smile:

Share this post


Link to post
Share on other sites

Posted

0.8 seconds is significant for boards that need to add modifications and thereby further increase load time. I have seen stock installs with an execution time of 0.95 seconds. Page load time has a large effect on your SE traffic influx and on your pageviews.
Around 100 queries is scary. How is this possible?
By way of comparison: A stock install of vb has an execution time of 0.1 second and 15 queries for a 1000+ replies thread. I have added 110 modifications to my big board and this increased it to 60 queries and 0.5 seconds execution time. I hope to be able to migrate my sites and its addons to IPB, but the number of queries and execution time seems to be a major issue.

Share this post


Link to post
Share on other sites

Posted

[quote name='Weppa333' timestamp='1333202789' post='2248375']
I'm sure that once it is running with real people on it, caching will make a difference, but the fact remains (and you confirm it), in my 3.3 install with no mods whatsoever, it takes 101 or 103 queries to generate the first page of a thread for the first time.
With SQL slowdowns probably caused by my other websites on the same database (but well, no slowdowns with VB3.7 : he does max 15 queries for the same output)

Again I realize that IPB3.3 has way more features than my vintage 3.7 full of tweaks; but 100 queries just seems... wrong :-)

The front page of IPC2.3 with a dozen blocks in "only" 50 queries !
What the HECK is IPB3.3 doing with over a hundred queries just to display a thread ???

I'm happy to see that, at least, it's not something wrong with my install :smile:

I kind of have to go jeezuz crimoly here....
I spit custom code in IP.Content... and I've never, ever seen that high a query count, and this is literally with me knowingly adding some for requested functionality in places... consider that each feed block is literally another complex query atop it.... jeeez that's inane for a topic view.

Share this post


Link to post
Share on other sites

Posted

Well it's just plain facts, I couldn't believe my eyes either. I modified the footer of my website to ALWAYS display query count, which is why I noticed some really scary numbers. At first I thought the query counter was wrong :) But switching to full debug confirms...

Most of the queries are inserts (which is even worse than a Select) about caching stuff.
May be nice if all my users suddenly wanted to read the same thread, but kinda useless with 200,000+ topics.

Again I'm new to IPB :laugh: , I run web communities on VB since more than 10 years tough, I really don't have to be convinced of the advantages of IPB over VB4, but I'm a bit afraid to switch the "live" install to IPB when I see such query counts...

Share this post


Link to post
Share on other sites

Posted

Here are the queries that seem to be stuck in a loop, this block of queries is done for each post basically which leads to more than 100 queries with the default of 20 posts per page. If you had more than 100 posts per page you'd probably end up with a thousand queries per page. :smile:

It's about rep and caching of rep, and caching of post data.


http://community.invisionpower.com/gallery/image/7456-/

Share this post


Link to post
Share on other sites

Posted

It's creating cached copies of the posts for easier retrieval later. It isn't an issue for most boards because those are created as their posted, but for converted boards it would definitely be an issue. This is probably something you should send to IPS in a ticket to have them add this to the convertors.

AndyF and Marcher Technologies like this

Share this post


Link to post
Share on other sites

Posted

Two thirds of the queries are from the rep system tough. I'll dig a bit deeper, trying different settings.

Share this post


Link to post
Share on other sites

Posted

If you dont mind me asking (and a touch OT), what program is that you are using to view these selects and inserts?

Share this post


Link to post
Share on other sites

Posted

IP.Board :) Just search "debug" in your ACP and turn it to level 2.

Share this post


Link to post
Share on other sites

Posted

I thought it was debug, but was not sure - thanks

Share this post


Link to post
Share on other sites

Posted

What you are seeing is post caching and reputation caching. This only happens once for each topic.

You are only seeing 100 queries because it is caching an entire topic because you've converted. In real use each post is cached as it is added so you shouldnt see anywhere near that number of queries when native topics are added and replied to.

AndyF likes this

Share this post


Link to post
Share on other sites

Posted

Just to confirm (and make it clear), are you saying that these loads are because this is the first time anyone has visited this thread since conversion, and will only happen once, not once for each member?

Alfa1 likes this

Share this post


Link to post
Share on other sites

Posted

[quote name='Chel' timestamp='1333213224' post='2248474']
Just to confirm (and make it clear), are you saying that these loads are because this is the first time anyone has visited this thread since conversion, and will only happen once, not once for each member?


That's exactly what he's saying. :)

Share this post


Link to post
Share on other sites

Posted

I thought so, but you wouldn't believe the number of times a conversation misses the vital input :D

Share this post


Link to post
Share on other sites

Posted

[quote name='Matt' timestamp='1333211989' post='2248467']
What you are seeing is post caching and reputation caching. This only happens once for each topic.

You are only seeing 100 queries because it is caching an entire topic because you've converted. In real use each post is cached as it is added so you shouldnt see anywhere near that number of queries when native topics are added and replied to.

So the number queries would be around 30 after that?
But what about page execution time? Is 0.8 - 0.95 execution time to be expected on a thread with 1000+ replies?

Share this post


Link to post
Share on other sites

Posted

Page execution time can vary horrendously depending on hardware and also how the software is configured. It's not possible to give an "expected" loading time for a thread. Also, it's 12-13 queries after that from what I've seen.

Share this post


Link to post
Share on other sites

Posted

Well I have an ultrafast private cloud. My hardware is new & optimal. So there is no bottleneck there.

Share this post


Link to post
Share on other sites

Posted

[quote name='Alfa1' timestamp='1333216839' post='2248499']
So the number queries would be around 30 after that?
But what about page execution time? Is 0.8 - 0.95 execution time to be expected on a thread with 1000+ replies?


On my live board running IPB 3.2.3 with the Similar Topics hook, a thread with 1036 replies took .1455s to execute with 15 queries. A thread with 14,082 replies took .1236s to execute with 14 queries. Those numbers seem reversed based on the size of the threads, and obviously can and will vary, but they are pretty average for me.

Share this post


Link to post
Share on other sites

Posted

For me, a thread with 32,880 (that's not a typo) replies takes on average 0.154s to load (after caching). That's after 10 refreshes taking the average.

But as I said, you really can't compared load times. You could have the best hardware in the world, and a badly configured web or database server can heavily increase load times.

Share this post


Link to post
Share on other sites

Posted

My hardware and configuration is optimal and can easily handle 10k concurrent users without hiccups.
Those load times give me a pretty positive outlook on IPB. This is helpful.

Share this post


Link to post
Share on other sites

Posted

It's worth noting that when a topic has been archived (new to 3.3) the queries drop to about 6 or 7 as it doesnt need to check followers and likes.

Share this post


Link to post
Share on other sites

Posted

unless I do not understand the ACP settings, any thread that nobody has seen for more than 7 days will ALSO generate 100 queries (because post cache lasts 7 days - I don't know how long rep cache lasts).
Even if rep cache last forever, a thread no seen in the last 7 days will generate between 20 and 40 queries more than normal ( for 20 posts per page ) If rep cache lasts 7 days too, any thread not seen in 7 days will generate 40 queries more too.

I guess that an alternative way of working would be to avoid caching everything at once in a thread nobody has seen before (eg cache max 5 posts at once). It would make an intelligent use of the thread cache (only threads accessed by 4 people in the last 7 days would be completely cached - a thread only googlebot has seen in the last 7 days wouldn't be cached).
Could be nice to not cache for bots too.
Just suggestions.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now

  • Who's Browsing   0 members

    No registered users viewing this page.