Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Lots of piwik_option inserts while archiving when there are thousands of sites #9753

Closed
quba opened this issue Feb 11, 2016 · 7 comments
Closed
Labels
c: Performance For when we could improve the performance / speed of Matomo.

Comments

@quba
Copy link
Contributor

quba commented Feb 11, 2016

Background:

  • a Piwik instance with 47k websites
  • MySQL master-master replication
  • one archiving thread

Issue:
The size of binlog files used to replicate data is so big, that the slave DB is not able to process them. Currently, after 2 minutes we have 400 huge queries to piwik_option to update this value. Situation will be even worse when running a few archiving threads which is a must have when you have thousands of sites.

Idea:
Maybe we could use Redis to store this option and maybe even all Piwik settings?

@kaz231
Copy link

kaz231 commented Feb 11, 2016

It's a matter of \Piwik\CronArchive\SharedSiteIds::getNextSiteId().

@tsteur
Copy link
Member

tsteur commented Feb 11, 2016

If it's caused by \Piwik\CronArchive\SharedSiteIds::getNextSiteId() then we probably have to use Redis for it as we kinda have to always perform this update.

If there are like 10.000 sites, shouldn't there be like max 10.000 queries to update the option table entry? To put this in relation we might execute much more inserts for archive record entries (eg like 100 per site which would be 1mio). Is it actually inserting or updating? Probably doesn't make a difference :)

@quba
Copy link
Contributor Author

quba commented Feb 11, 2016

Standard insert or:
INSERT INTOpiwik_option(option_name option_value autoload) VALUES ('SharedSiteIdsToArchive' '' '') ON DUPLICATE KEY UPDATE option_value = '' ...

@quba
Copy link
Contributor Author

quba commented Feb 11, 2016

The workaround could be to force-idsites one by one, but this makes it really complicated if you want to start a few threads..

@tsteur
Copy link
Member

tsteur commented Feb 13, 2016

Let's have a chat next week about it? I can schedule a meeting

cc @kaz231 @quba @mattab

@kaz231
Copy link

kaz231 commented Feb 13, 2016

👍

@tsteur
Copy link
Member

tsteur commented Feb 16, 2016

Summary of what we discussed:

We already have various caches like Piwik\Cache\Eager, Piwik\Cache\Lazy and Piwik\Cache\Transient see https://github.com/piwik/piwik/blob/2.16.1-b1/core/Cache.php#L17-L57 that can be configured to use different kind of backends in global.ini.php see https://github.com/piwik/piwik/blob/2.16.1-b1/config/global.ini.php#L74-L97.

We are aware that several servers could request the same idSite at the same time because we do not have a proper lock in place but this is ok for now. We need to ideally fix the problem that multiple archives at the same time for same idSite could be created and slow down each other.

@mattab mattab added this to the 2.16.x (LTS) milestone Mar 31, 2016
@mattab mattab added c: Performance For when we could improve the performance / speed of Matomo. and removed PP labels Jul 14, 2016
@mattab mattab modified the milestones: 2.16.x (LTS), Mid term Aug 25, 2016
@mattab mattab closed this as not planned Won't fix, can't repro, duplicate, stale Dec 14, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
c: Performance For when we could improve the performance / speed of Matomo.
Projects
None yet
Development

No branches or pull requests

4 participants