How many sql queries should a cached page invoke?

Developing with Concrete
1

While investigating the issue of a 404 call creating 167 sql queries and thus making concrete cms sites particularly susceptible to botnet attacks [ see Make Page Not Found Page cacheable · Issue #10165 · concretecms/concretecms · GitHub ], it became apparent that our websites are behaving very oddly with regards to the number of sql queries invoked, which is always a lot, and usually more with full page caching enabled than without …go figure!

I was under the impression that if a page is cached, it should only require a handful of queries to identify the target cache-file. But something is very wrong with our sites. All have bespoke themes but switching a site to the Atomic theme and creating a simple new test page makes no noticeable difference to the trends reported below.

Please could someone load up phpdebugbar ( PHP Debug Bar for Concrete CMS :: Concrete CMS Marketplace ) and let me know if the gist of my results are similar to those experienced by others, or if we are misconfiguring our website instances in some way i.e. have we uncovered a global problem, or just made a silly mistake somewhere?

Note that once phpdebugbar is enabled, the new Task Permission of ‘Show Debug Bar’ needs to be set to ‘Guest’ in order to see the tool when logged out.

These are the number of SQL queries invoked after refreshing each page (to allow any secondary server caching mechanisms to kick-in) and being logged out of the admin tools i.e. as a guest user:

WEBSITE 1 (simple demo site hardly ever used)

Home page with a few different blocks:
with page caching disabled: 908
with FPC on if blocks allow: 944
with FPC on in all cases: 944

Very simple page with one block of text:
with page caching disabled: 809
with FPC on if blocks allow: 815
with FPC on in all cases: 815

WEBSITE 2 (dev instance of busy commercial site)

Home page with quite a bit of content:
with page caching disabled: 991
with FPC on if blocks allow: 223
with FPC on in all cases: 1038

Terms & Conditions page with simple text:
with page caching disabled: 819
with FPC on if blocks allow: 176
with FPC on in all cases: 821

Relative variables:
“Concrete\Core\Cache\Level\OverridesCache” => true
“Concrete\Core\Cache\Level\RequestCache” => true
“Doctrine\Common\Cache\ArrayCache” => true
“orm/cache” => true
“orm/cachedAnnotationReader” => true
“Concrete\Core\Cache\Level\ExpensiveCache” => true

EDIT - environment:

Concrete Version
Core Version - 9.4.5
Version Installed - 9.4.5
Database Version - 20250827152432

System User
apache

Database Information
Version: 8.0.43-34.1
SQL Mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Character Set: utf8mb4
Collation: utf8mb4_unicode_ci

Concrete Cache Settings
Block Cache - On
Overrides Cache - On
Full Page Caching - On - In all cases.
Full Page Cache Lifetime - Only when manually removed or the cache is cleared.

Database Entities Settings
Doctrine Development Mode - Off

Server Software
Apache

Server API
fpm-fcgi

PHP Version
8.3.26

PHP Extensions
bcmath, calendar, cgi-fcgi, Core, ctype, curl, date, dom, exif, FFI, fileinfo, filter, ftp, gd, gettext, hash, iconv, igbinary, imagick, imap, intl, json, ldap, libxml, mbstring, mysqli, mysqlnd, openssl, pcre, PDO, pdo_mysql, Phar, posix, random, readline, redis, Reflection, session, shmop, SimpleXML, soap, sockets, sodium, SPL, standard, sysvmsg, sysvsem, sysvshm, tokenizer, xml, xmlreader, xmlrpc, xmlwriter, xsl, Zend OPcache, zip, zlib

2

Not a full explanation and maybe not applicable in your case. Hard coded blocks - for example, some custom themes use hard coded navigation blocks or page lists - do not cache, whereas blocks added through edit can be cached. (Somewhat counter intuitive)

3

Thanks for the input John, but the theme and nearly all the bells-and-whistles ‘feature’ blocks are my own. Not perfect, I’m sure, but hand-coded with experience. All the navigation blocks use the default Auto-Nav block with templates …no hard-coded links.

If someone can confirm that a standard 9.4.5 site with FPC enabled invokes query numbers in single figures (which is what I would expect), I can set about drilling down deeper to find out what I’m doing wrong (obviously I’ve spent some time on that already), but I don’t want to waste time doing this if the problem is with the codebase, not my use of it.

4

I think debugbar will disable full page caching in order to work on “guest” mode.

At least that was a case 2 years ago when I tried to figure out real number of queries when page was refreshed.
I ended up logging queries to file (on localhost) and checking it that way.

5

Ah, that might explain things then! …thanks - I’ll dig a bit deeper into this :slight_smile:

However, I’d still like to know how on earth the initial rendering of a simple page can invoke between 800-900 queries. This seems a bit extreme to me. For example, what phpdebugbar showed me was that queries were being run to check the content for block-areas that were not part of the page template in use. Checking the template first, then looking for content in only the block-areas in use would seem to be an instant improvement, especially on a site with lots of different areas defined throughout the site.

6

Have you looked at the orphan blocks for the page in question and cleaned them out?

7

Have you looked at the orphan blocks for the page in question and cleaned them out?

Thanks for the suggestion John, but I’ve been using concrete5 since the very early days and learnt a long time ago not to leave orphaned block data in the database. Just to make sure though, I installed ‘Toolbar Block Outline’ in one of the sites I tested, and checked all the pages I tested, only to find no reported orphan blocks. Don’t let me stop you coming up with other possible causes though :+1:

8

To be honest, 800-900 queries seems “normal” for Concrete.
It was always like that.
You start adding global areas, blocks, stacks, styles, whatever, and counter keeps increasing.
Not like you cannot increase sql query counter by yourself (if you are not carefull), but Concrete makes a lot queries by itself.

It doesnt matter for low-traffic sites or when you can enable full page caching.
But it gets a little annoying when you have to actually disable full page caching for some specific pages/part of website.
I haven’t looked at it lately, but I doubt something has drastically changed since.

9

Well, for the first time since I’ve been using concrete (since 5.4 ??) my host complained about too many queries. I posted about it here a while ago. I ended up putting a HUGE number of bot blocking rules in my .htaccess file (at their request) which cut down on the bot traffic. AI is scanning whatever it can as fast as it can so a site that requires thousands of queries for a simple page is going to hit hard.

10

FYI, 94 queries on the default Atomik home page on my local, when I created a performance improvement pull request this January.

Additionally, if the full page cache is working as expected, the number of queries should be 0.

So I think some blocks will prevent your full page caching and calls too many queries.