Jump to content


Photo
* * * * * 2 votes

More than 100 queries for a thread ?!


  • Please log in to reply
29 replies to this topic

#1 Weppa333

Weppa333

    IPB Full Member

  • Members
  • PipPipPip
  • 168 posts

Posted 31 March 2012 - 06:04 AM

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 ? :)

#2 Bob van Leeuwen

Bob van Leeuwen

    Advanced Member

  • Members
  • PipPipPipPip
  • 475 posts

Posted 31 March 2012 - 07:29 AM

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

#3 PeterUK

PeterUK

    IPB Full Member

  • Members
  • PipPipPip
  • 188 posts

Posted 31 March 2012 - 08:28 AM

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.

#4 Weppa333

Weppa333

    IPB Full Member

  • Members
  • PipPipPip
  • 168 posts

Posted 31 March 2012 - 09:06 AM

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:

#5 Alfa1

Alfa1

    Advanced Member

  • Members
  • PipPipPipPip
  • 450 posts

Posted 31 March 2012 - 09:09 AM

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.

#6 Marcher Technologies

Marcher Technologies

    $life=FALSE;$code=TRUE;$time--;

  • +Clients
  • 11,747 posts

Posted 31 March 2012 - 09:13 AM

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.

#7 Weppa333

Weppa333

    IPB Full Member

  • Members
  • PipPipPip
  • 168 posts

Posted 31 March 2012 - 09:26 AM

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...

#8 Weppa333

Weppa333

    IPB Full Member

  • Members
  • PipPipPip
  • 168 posts

Posted 31 March 2012 - 09:46 AM

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.


Image5665651


#9 Michael

Michael

    Meet Jay

  • +Clients
  • 19,587 posts

Posted 31 March 2012 - 10:15 AM

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

Contact Me: Email · Facebook · Twitter · Google+


#10 Weppa333

Weppa333

    IPB Full Member

  • Members
  • PipPipPip
  • 168 posts

Posted 31 March 2012 - 10:30 AM

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

#11 Aussie Cable

Aussie Cable

    I am done with crazy people

  • +Clients
  • 855 posts

Posted 31 March 2012 - 10:40 AM

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

#12 Weppa333

Weppa333

    IPB Full Member

  • Members
  • PipPipPip
  • 168 posts

Posted 31 March 2012 - 10:42 AM

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

#13 Aussie Cable

Aussie Cable

    I am done with crazy people

  • +Clients
  • 855 posts

Posted 31 March 2012 - 10:45 AM

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

#14 Matt

Matt

    Chief Software Architect

  • IPS Management
  • 26,142 posts

Posted 31 March 2012 - 11:39 AM

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

Matt Mecham
Invision Power Services, Inc.
"I love deadlines. I especially like the whooshing sound they make as they go flying by."
-- Douglas Adams (1952 - 2001)


#15 Chel

Chel

    Advanced Member

  • Members
  • PipPipPipPip
  • 237 posts

Posted 31 March 2012 - 12:00 PM

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

#16 PeterUK

PeterUK

    IPB Full Member

  • Members
  • PipPipPip
  • 188 posts

Posted 31 March 2012 - 12:13 PM

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. :)

#17 Chel

Chel

    Advanced Member

  • Members
  • PipPipPipPip
  • 237 posts

Posted 31 March 2012 - 12:14 PM

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

#18 Alfa1

Alfa1

    Advanced Member

  • Members
  • PipPipPipPip
  • 450 posts

Posted 31 March 2012 - 01:00 PM

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?

#19 PeterUK

PeterUK

    IPB Full Member

  • Members
  • PipPipPip
  • 188 posts

Posted 31 March 2012 - 01:28 PM

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.

#20 Alfa1

Alfa1

    Advanced Member

  • Members
  • PipPipPipPip
  • 450 posts

Posted 31 March 2012 - 02:37 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users