What is the deal with "queries" thing? I need some study material for understanding servers.

Paul Buonopane

Aspirant
Joined
Jan 17, 2015
Messages
28
This is just wrong. Of course its a metric you should be worried about.

It might not be the only metric too look at, but to say its not important is just nonsense.
If your page is running 1000 queries then something is wrong - regardless of how "optimised" they are.
I didn't really mean to dismiss the relevance of extremes; obviously 1000 queries is nuts. However, I think it's silly to complain when an add-on performs, say, two or three additional queries per page. If it's displaying data, it's probably going to perform queries--and even if it could technically avoid those queries, it might be worse off.

My statement isn't wrong: in general, you shouldn't be worrying about the number of queries; you should be worrying about the time your queries take to complete, both in aggregate and individually. If you have 1000 queries, that time is going to be unacceptable anyway, so there's no need to fret over the number of queries unless you already know something is wrong. Complaining about an additional two queries per page doesn't make sense when they're probably taking something like 1ms combined.
 

zappaDPJ

Administrator
Joined
Aug 26, 2010
Messages
6,967
..and he never did understand how wrong that was.

When vB5 was still in early alpha, some pages were running over a thousand queries. They had to add caching just to get that down to hundreds.
Wow! Over a thousand, that explains why it was so horribly slow in the early days. I do recall someone on the staff saying it was best suited to small forums. Full marks to him for turning a disaster into a selling point :)
 

MagicalAzareal

Magical Developer
Joined
Apr 25, 2019
Messages
510
Wow! Over a thousand, that explains why it was so horribly slow in the early days. I do recall someone on the staff saying it was best suited to small forums. Full marks to him for turning a disaster into a selling point :)
100 - 1000 queries is too much even for small forums. Some hosts will suspend you.
 

Snog

Enthusiast
Joined
Sep 9, 2012
Messages
225
..and he never did understand how wrong that was.

When vB5 was still in early alpha, some pages were running over a thousand queries. They had to add caching just to get that down to hundreds.
This is what ultimately lead to my leaving vB. When he responded the way he did when I asked the same question in the alpha test forums, the hair stood up on the back of my neck and chills went down my spine.

At the time I thought that maybe I was just stuck in my "old ways" and checked with people that might be more up to date. They confirmed my suspicions and pretty much said if anyone ever says the number of queries don't matter, run as fast and as far away from that system as you can.

While it is true that 2 or 3 well formed queries can perform better than 1 poorly formed query, the fastest query is the one that never takes place. The trick is to perform 1 well formed query, not 2 or 3 whenever possible. ;)
 

Snog

Enthusiast
Joined
Sep 9, 2012
Messages
225
My statement isn't wrong: in general, you shouldn't be worrying about the number of queries; you should be worrying about the time your queries take to complete, both in aggregate and individually. If you have 1000 queries, that time is going to be unacceptable anyway, so there's no need to fret over the number of queries unless you already know something is wrong. Complaining about an additional two queries per page doesn't make sense when they're probably taking something like 1ms combined.
I think the point of watching query count boils down to what can happen on large sites where there can be a thousand members accessing the database at once.

A single add-on that adds one or two database queries isn't normally a problem and is part of the nature of the beast. But, when a site runs a large number of add-ons and each one adds one or two queries, that's where the problems begin to crop up.
 

Paul Buonopane

Aspirant
Joined
Jan 17, 2015
Messages
28
I think the point of watching query count boils down to what can happen on large sites where there can be a thousand members accessing the database at once.

A single add-on that adds one or two database queries isn't normally a problem and is part of the nature of the beast. But, when a site runs a large number of add-ons and each one adds one or two queries, that's where the problems begin to crop up.
As long as the queries complete quickly and are reads, it usually isn't an issue. Problems do arise when you have a lot of writes or mixed read/write transactions, though. We're doing a bit over 500 queries/sec/instance at the moment; each one is an r4.large RDS instance, which has 16 GiB RAM if I recall correctly. It's normal for it to go into the thousands at peak times. (It's an average of about 5 queries per request, but we cache heavily, so an uncached request is probably closer to 50-100 queries.)
 

Paul M

Limeade Addict
Joined
Jun 26, 2006
Messages
3,854
You need to remember that most forums are not running on dedicated hardware, they are on underpowered, over used, shared servers. Some hosts (still) limit your query count.
 

Paul Buonopane

Aspirant
Joined
Jan 17, 2015
Messages
28
You need to remember that most forums are not running on dedicated hardware, they are on underpowered, over used, shared servers.
For the most part, our production servers are shared. They're roughly the equivalent of VPSes.

Some hosts (still) limit your query count.
As far as I'm aware, most forums don't use shared hosting in the traditional sense; they use VPSes instead. Query count shouldn't be arbitrarily limited on those.

Platformless shared hosting--the kind where you don't have your own VM and are given a generic web server, as opposed to an integrated platform like WordPress--is something I would discourage even for the smallest websites. Those are the services likely to have query rate limits.
 

MagicalAzareal

Magical Developer
Joined
Apr 25, 2019
Messages
510
The best query count is when you use none at all. 12k req/s on toilet paper VPS vs 1k req/s.
This is comparing a meagre five to zero. All reads. Do note you might be tapped out on bandwidth, depending on the payload. Results may vary depending on the size of the result set.

A dynamic and particularly slow read query will usually drop it down to about... 300 req/s, although these are large pages.

Writes are even lighter than reads as I can just insert aggregated data rather than each and every event every now and then. This has the side-effect of preserving some degree of anonmity which people like these days.

Also, do note that these are artificial benchmarks, therefore may not be indicative of real world results. I do like not wasting money, and generally speaking, this is so low hanging fruit.
 
Last edited:

Paul M

Limeade Addict
Joined
Jun 26, 2006
Messages
3,854
As far as I'm aware, most forums don't use shared hosting in the traditional sense; they use VPSes instead.
Nope, the majority use shared hosting.
The majority of forum admins have no technical skills and would not even know where to start with VPSes.
 

zappaDPJ

Administrator
Joined
Aug 26, 2010
Messages
6,967
Nope, the majority use shared hosting.
The majority of forum admins have no technical skills and would not even know where to start with VPSes.
I can't easily supply any metrics to support that but I do believe it to be true. The vast majority of forum installations don't get enough traffic to even warrant a VPS.
 

MagicalAzareal

Magical Developer
Joined
Apr 25, 2019
Messages
510
I can't easily supply any metrics to support that but I do believe it to be true. The vast majority of forum installations don't get enough traffic to even warrant a VPS.
A VPS is usually best, to be honest.
I can understand why people would use shared on the other-hand.
 

zappaDPJ

Administrator
Joined
Aug 26, 2010
Messages
6,967
A VPS is usually best, to be honest.
I can understand why people would use shared on the other-hand.
I agree, for flexibility and control a VPS is going to be the preferred option but in my experience the majority of forum owners don't have the necessary technical skills to run one.
 
Top