Performance problem with counts

[jneto81]

Hello, i'm just testing the bolt backend with ~ 1 000 000 records in bolt_entries with a clean default installation on my local machine with php 7.

In the admin area, bolt/overview/entries, for the listing of the entries, the query generated for the count is strange, it takes more than 10 seconds and it consumes a lot of memory +- ( 500 MB for the total request ).

SELECT count(*) FROM bolt_entries _entries LEFT JOIN bolt_relations pages ON _entries.id = pages.from_id AND pages.from_contenttype='entries' AND pages.to_contenttype='pages' LEFT JOIN bolt_relations incomingrelation ON _entries.id = incomingrelation.to_id AND incomingrelation.to_contenttype='entries' LEFT JOIN bolt_taxonomy categories ON _entries.id = categories.content_id AND categories.contenttype='entries' AND categories.taxonomytype='categories' LEFT JOIN bolt_taxonomy tags ON _entries.id = tags.content_id AND tags.contenttype='entries' AND tags.taxonomytype='tags' GROUP BY _entries.id, _entries.id, _entries.id, _entries.id

[bobdenotter]

Are you just trying things out, or do you have a real use case for this many entries? What would be “acceptable” performance with 1M entries?

The largest real-world bolt sites we (our company) maintains have about 150000 records over a number of content types, and it performs just fine on a beefy server. 😇

[jneto81]

Yes, i'm just testing. Can you please check the result of the query ( hundreds of rows, instead of just one )

My use case is, now i work with 3 different CMS to store the content of hundreds of sites. One of the CMS is Wordpress. They are used just for backend to editors to create/edit articles. The custom frontends access the content by custom APIs, that read the content from database / Solr . We have different content types with wordpress, but with a old custom cms, we have ~ 2 M articles ( old content ). With wordpress, we have a backend with 200 000 articles/posts and starting to have slow queries ( after some wordpress core tuning ).
This is the reason why I'm looking for a solution that can scale for millions of records.

[rossriley]

@jneto81 Try this if you are able to add a small patch to your Bolt vendor files.

The file you are looking for will be in vendor/bolt/bolt/src/Storage/ContentRequest/Listing.php

Assuming you are on the latest version around line 122 there is this line:

            $queryCopy->resetQueryPart('orderBy');

Change it to:

            $queryCopy->resetQueryParts(['orderBy', 'join', 'groupBy']);

Let me know if that has any effect on the query performance. If it does I'll get a pull request in for the next minor version.

[jneto81]

Can't remove "join". It breaks the filters.

I tested with $queryCopy->resetQueryParts(['orderBy', 'groupBy']);

and replaced $totalResults = (int) count($queryCopy->execute()->fetchAll());

with $totalResults = (int) $queryCopy->execute()->fetchColumn();

I'm no sure if this does't break anything because i'm just making simple tests, and if the "groupBy" is necessary to remove duplicates.

I reduce the query time and memory usage. Before listing of Entries takes ~8000 ms / 50 MB with 100 000 entries. Now ~ 7000 ms / 6 MB. Still have other performance problems with other queries.

The main problem in the original solution is with the query that return for 100 000 records, a result with 100 000 rows and the count is used to get the total.

I'm new with bolt and now just testing to see if it solve my requirements ( one is backend performance for editors ).

[rossriley]

OK, leave it with me then, I'll get a test setup with a million rows and do some debugging.

[rossriley]

This one ended up being more complicated than I thought and as far as I can see we'll need some restructuring to be able to deal reasonably efficiently with very large numbers of records.

For reference we start to hit usability problems when dealing with 100,000 rows although that figure can be tweaked up a bit by providing more memory and query cache size for MySQL.

These figures will vary but when one of your tables has 100,000 rows you get this problem...

1. In the left hand column the most recent 4 entries query takes 4-6 seconds
2. For the pagination count query this takes about 1-3 seconds
3. For the last modified entries panel this query takes 4-6 seconds

The reason for this is that the ORDER BY datechanged DESC is very slow and even adding indexes for the datechanged column doesn't really help much. You can also run into this problem too in userland if they change the sort in contenttypes.yml to -datepublish then the two queries (fetch and row count) will be super slow too

So the only way I can think we can work around this is.

1. Use id wherever possible in the backend, as soon as we order by id DESC instead of datechanged DESC we go back to sub 100ms queries again.
2. Where we really need to pick the most recently modified we probably need to have a faster indexing mechanism, maybe we could store something in a meta field

I'll try and do some more testing on this and see if there's a way to attack it better.

[SvanteRichter]

I didn't use mysql (it was on sqlite), but I ran a bolt install with 500 000~ish records at my last job, with multiple relations between them and that was pretty snappy. Stuff like a content populated select field was still really slow though (as is expected since it loads them all sync), but IIRC the normal overview/edit pages stayed under 200ms.

I think I did some small modifications (it was during 3.0-dev), I'll see if I can dig it out.

[jneto81]

Hello, is it possible to remove the "most recent 4" with a extension / config ?

If i'm in the pages (list, edit ) for each content type that exist, a query is created with limit 4, query for entries, showcase, ... ( each content type ). Do we need this queries ?

[rossriley]

@jneto81 I imagine it would be possible just provide a blank template in place of the partial that is used to show them, although I'd prefer it if we can give some configuration options to allow people to tweak things for performance reasons.

@SahAssar You're correct about SQLite it handles those orders on datechanged/datepublish I've just done exactly the same as on MySQL and it was a much snappier sub-2second load time so really this is most likely to be a specific MySQL performance problem that we need to optimise around.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. Maybe this issue has been fixed in a recent release, or perhaps it is not affecting a lot of people? It will be closed if no further activity occurs, because we like to keep the issue queue concise and actual. If you think this issue is still relevant, please let us know. Especially if you’d like to help resolve the issue, either by helping us pinpointing the cause of a bug, or in implementing a fix or new feature.

[rossriley]

Keeping this open as a WIP

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. Maybe this issue has been fixed in a recent release, or perhaps it is not affecting a lot of people? It will be closed if no further activity occurs, because we like to keep the issue queue concise and actual. If you think this issue is still relevant, please let us know. Especially if you’d like to help resolve the issue, either by helping us pinpointing the cause of a bug, or in implementing a fix or new feature.

[bobdenotter]

Hush, Stalebot.