Matomo On-Premise Scaling

Support & Bugs
#1

Hi Matomo Team,

I am Abhishek Sharma, Backend Developer at DotPe. We provide an innovative solution that lets brands and retail buyers engage and sell products through an online platform. It replaces physical showrooms and trade shows, eliminating the need for a physical location and physical samples to show new collections.

We are trying to integrate Matomo On-Premise on our servers. We successfully did so, a few days back for 50,000 active domains on our system. But yesterday we realise that it is creating some problems as the data grows. After spending some time we figured out that this query

SELECT option_name, option_value FROM `matomo_option` WHERE option_name LIKE '%report\\_to\\_invalidate\\_%\\_%';

causes some problems in the system as it takes a lot more time to get executed than it is expected.

We liked your project and wanted to integrate it into our system but the hurdles don’t allow us to do so. I request you to please help me out with this, if possible. I shall be thankful to you. Expecting a positive reply from your side.

Thanks and Regards
Abhishek Sharma

#2

Did you have a look at:

?

#3

Yes, we did as mentioned in the URL but nothing works. The “LIKE” is too slow that even in a table with 200K entries, it takes too long to execute. Please help if there are some ways to minimise its effect so as to scale for 50K domains and 100K in the future. Also, we need to look at if it is possible for it to scale that much? @heurteph-ei

#4

This seems a bit similar to

and

.
But in their cases it was caused by a major Matomo upgrade, while you installed a recent Matomo version freshly, right?
I guess the large amout of sites causes this, but nevertheless this query should not be what causes the major slowdown.

#5

Yes we are using the configuration below

Matomo version: 4.8.0
MySQL version: 8.0.18-google
PHP version: 7.2.24-0ubuntu0.18.04.11

As we are using 4CPU each of 16GB RAM and we followed all the steps as mentioned in “How to configure Matomo for Speed?” Still unable to scale that much. This tool is very good and covers all our use cases. The only problem we came across is this as of now. If you could do anything, it would really be great. @Lukas

Thanks

#6

@Abhishek_Sharma1 when you say “slow”, what do you mean by that in terms of milliseconds or seconds?

Do you have a dedicated database server? What does that server look like? And do you know if CPU peaks or so?

The query you mentioned would be executed like every 5 minutes if I see this right? By setting a config/config.ini.php setting you could increase the the caching time of our tracker cache like this:

[Tracker]
tracker_cache_file_ttl = 900

This way it would be only executed every 15min (unless you have multiple servers then each server would execute this every 15 min). It’s not a solution but maybe helps a bit anyway.

#7

@thomas_matomo Thanks for your reply. We consider slow queries in terms of seconds. yes, we have a dedicated database server for it. And yeah keeps on spiking due to that query. We already made these changes in hopes to figure out some solution to it but unfortunately, nothing works as of now.

#8

Thanks @Abhishek_Sharma1

I’ve created a small tweak in https://github.com/matomo-org/matomo/pull/18990 but don’t think it will make it much faster. Created this issue https://github.com/matomo-org/matomo/issues/18991 for the general query.

How many seconds does it take? Do you know if the issue is data transfer? or actually finding the matching rows?

What’s the impact of this query being slow so far? I imagine it would be executed very rarely and since the table is fairly small with say 200K rows I wouldn’t have thought it would have a huge impact.