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

Discussion in 'Servers and Control Panels' started by palhanow, Jul 1, 2019.

  1. Paul Buonopane

    Paul Buonopane Aspirant

    25
    83
    +26
    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.
     
  2. zappaDPJ

    zappaDPJ Administrator

    6,875
    1,432
    +5,485
    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 :)
     
  3. MagicalAzareal

    MagicalAzareal Magical Developer

    445
    332
    +209
    100 - 1000 queries is too much even for small forums. Some hosts will suspend you.
     
  4. Snog

    Snog Enthusiast

    225
    615
    +241
    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. ;)
     
  5. Snog

    Snog Enthusiast

    225
    615
    +241
    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.
     
  6. zappaDPJ

    zappaDPJ Administrator

    6,875
    1,432
    +5,485
    My post was filled to the brim with sarcasm :)
     
  7. Paul Buonopane

    Paul Buonopane Aspirant

    25
    83
    +26
    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.)
     
  8. Paul M

    Paul M Limeade Addict

    3,823
    1,627
    +2,241
    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.
     
  9. Paul Buonopane

    Paul Buonopane Aspirant

    25
    83
    +26
    For the most part, our production servers are shared. They're roughly the equivalent of VPSes.

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

    MagicalAzareal Magical Developer

    445
    332
    +209
    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: Jul 12, 2019
  11. Paul M

    Paul M Limeade Addict

    3,823
    1,627
    +2,241
    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.
     
    • Agree Agree x 2
    • Informative! Informative! x 1
    • List
  12. zappaDPJ

    zappaDPJ Administrator

    6,875
    1,432
    +5,485
    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.
     
    • Agree Agree x 2
    • Informative! Informative! x 1
    • List
  13. MagicalAzareal

    MagicalAzareal Magical Developer

    445
    332
    +209
    A VPS is usually best, to be honest.
    I can understand why people would use shared on the other-hand.
     
  14. zappaDPJ

    zappaDPJ Administrator

    6,875
    1,432
    +5,485
    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.
     
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.