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.

Sign up for GitHub

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

Jump to bottom

Mysql process list is clogged with very inefficient updates on the archive blob tables #7195

Closed
moee opened this issue Feb 13, 2015 · 10 comments
Closed

Mysql process list is clogged with very inefficient updates on the archive blob tables #7195

moee opened this issue Feb 13, 2015 · 10 comments
Labels
Bug For errors / faults / flaws / inconsistencies etc. Major Indicates the severity or impact or benefit of an issue is much higher than normal but not critical.
Milestone
2.11.1

Comments

@moee
Copy link

moee commented Feb 13, 2015

I maintain a piwik installation that consists of approximately 1,300 sites and a database size of 70+ GB.

Since the update to 2.10.0 I see very inefficient update queries clogging the process list. Consequently, performance is severely degraded and opening statistics for a specific website most of the time fail with the error message "SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction".

Offending queries look as follows, where date1 and date2 always are today's date and idsite is either id.

UPDATE piwik_archive_blob_2015_01  SET value = 4 WHERE ( (date1 <= '2015-02-13' AND '2015-02-13' <= date2 AND name LIKE 'done%') )  AND idsite IN ('7991')

The shown query, for instance, has been running for 250 seconds and counting. I assume that the query comes from method "updateArchiveAsInvalidated" in core/DataAccess/Model.php.

In the config.ini.php, enable_browser_archiving_triggering is set to false and time_before_archive_considered_outdated is set to 86400. The archiver job itself is triggered every hour. However, since the database being blocked with the mentioned updates, it fails to finish within one hour.

Please see also this post in the piwik support forum: Call to updateArchiveAsInvalidated performs very inefficient update statements / Massive performance problems since update to 2.10.0.
@mattab mattab added the Bug For errors / faults / flaws / inconsistencies etc. label Feb 16, 2015
@mattab mattab added this to the Piwik 2.11.0 milestone Feb 16, 2015
@mattab
Copy link
Member

mattab commented Feb 16, 2015

Hi @moee - thanks for the report. I don't know if it will change anything but just to be sure, could you try with 2.11.0-RC version? we've made some improvements in #7031

@mattab mattab modified the milestones: Piwik 2.11.0, Piwik 2.12.0 Feb 18, 2015
@mgazdzik
Copy link
Contributor

Hi guys, I'm just wondering if it even makes sense to run update queries on blob tables ?
Since where clause contains
( (date1 <= '2015-02-13' AND '2015-02-13' <= date2 AND name LIKE 'done%') )
this update query will actually never find a single record to update (AFAIK done flags are only located in numeric tables which are way smaller than blobs).
I believe that this line https://github.com/piwik/piwik/blob/master/core/DataAccess/ArchiveInvalidator.php#L176
should return only numeric tables, and this could make invalidating archives run way faster.

@quba
Copy link
Contributor

quba commented Feb 18, 2015

+1

@mattab
Copy link
Member

mattab commented Feb 18, 2015

Hi guys, I'm just wondering if it even makes sense to run update
queries on blob tables ?

Good point, thanks!! we'll make the change

@mattab
Copy link
Member

mattab commented Feb 18, 2015

@mgazdzik @moee Are the queries locking the database ONLY the queries on the archive_blob_* table? or do you sometimes see the archive_numeric_* tables as well?

@moee
Copy link
Author

moee commented Feb 18, 2015

@mattab thanks for the follow up. It always are the archive_blob_* tables but never the archive_numeric_* tables that are affected.
ATM I'm updating to 2.11.0. I will shortly post a follow up whether the update fixes the slow query problem or not.

@quba
Copy link
Contributor

quba commented Feb 18, 2015

It's not fixed in 2.11. Probably we will have 2.11.1 to address this issue.

@mattab mattab added the Major Indicates the severity or impact or benefit of an issue is much higher than normal but not critical. label Feb 18, 2015
@mattab mattab closed this as completed in 1324cfc Feb 18, 2015
@mattab
Copy link
Member

mattab commented Feb 18, 2015

Hi @moee Piwik 2.11.1-b1 has been released with the bug fix- can you please test and tell us if you still have any issue?

@mattab mattab modified the milestones: Piwik 2.12.0, Piwik 2.11.1 Feb 19, 2015
@freelock
Copy link

Hi,

We were experiencing this issue too -- making Piwik practically unusable for us. Can confirm that 2.11.1 fixes it... Much, much better!

Thanks!

@moee
Copy link
Author

moee commented Feb 25, 2015

Hey @mattab - Sorry for the late response. I updated to 2.11.1 and I also can confirm that this resolves the issue. Thanks for the fix!

@quba quba mentioned this issue Jun 29, 2015
Closed
@peterbo peterbo mentioned this issue Feb 17, 2020
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Bug For errors / faults / flaws / inconsistencies etc. Major Indicates the severity or impact or benefit of an issue is much higher than normal but not critical.
Projects
None yet
Milestone
2.11.1
Development

No branches or pull requests
5 participants
@freelock @moee @mattab @quba @mgazdzik