2500 queries for a single pageview? Is that normal?

[pleini]

Hi there,

we just created a new website with processwire and since we set it live our database crashed because

of too many connections.

I checked Processwire and I have seen there are two databases:

1) wire()->database

and

2) wire()->db

The first database seems to be used for all queries the core processwire does.

When I debugged with getQueryLog() I have seen for a page

2498 queries and for another very simple one 629 queries.

We just use about 10-20 fields for a template.

So is around 2.500 queries per page view normal?

Most of them start with SELECT SQL_NO_CACHE

Furthermore our database crashes because of too many connections.

Is it only one or two connections per page view?

There were just 30 website visitors on the page and even a caching CDN on generating 5 minutes later over 400 connections.

Thank you for your help,

Chris

[BitPoet]

While 2500 queries per page load is quite a bit, it isn't completely unheard of for a complex multi-language site (though it cries for a bit of caching). Two connection per view is normal.

The SQL_NO_CACHE keyword is only added if $config->dbCache is disabled, which is enabled by default, and this definitely puts a heavy load on the server (data for every query has to fetched anew). So I'd enable dbCache in site/config.php again and test if the problem is still present, then also make sure that $config->debug is disabled for the live site.

[DaveP]

Just for information, wire()->database is the core PDO database handler while wire()->db is the (now deprecated(?)) MySQLi version, kept available for some modules that haven't been updated PDO yet.

[pleini]

We have $config->dbCache enabled but we have on some pages > 2500 queries anyways. Is this still normal or is there possibly a bug?

[Wanze]

What are you displaying on this site where you get this count?

We just use about 10-20 fields for a template.

Any field that is not configured as "autoload" will require one select query per page where you access the field. So if you have 20 fields on a template and you're displaying each field for 100 pages in a loop, this would produce about 100*20 = 2000 queries.

[pleini]

Ok. Any suggestions on how optimized MySQL settings to make the database processwire optimized?

[LostKobrakai]

You certainly need to keep mysql connections and mysql queries apart. The first one is causing your db to crash, whereas the latter ones are using these connections. If 30 visitors are causing 400 (I'd expect concurrent) connections I'd check if mysql connections are properly closed.

[pleini]

You are right, they were not closed, this was the reason. Now we have a faster server and they are closed again.

Are there any default mysql settings that are suggested for processwire?