search

matomo-org / matomo

Empowering People Ethically with the leading open source alternative to Google Analytics that gives you full control over your data. Matomo lets you easily collect data from websites & apps and visualise this data and extract insights. Privacy is built-in. Liberating Web Analytics. Star us on Github? +1. And we love Pull Requests!
https://matomo.org/
GNU General Public License v3.0
19.72k stars 2.63k forks source link

Don't show DB Size on PrivacyManager Admin Page #7981

Open futureweb opened 9 years ago

futureweb commented 9 years ago

Our Piwik got a 30GB DB and everytime I try to load the PrivacyManager Admin Page our MySQL stalls because this Page tries to figure out the Size of the DB ... (MySQL Process 100% CPU Usage) DB size should only be shown if requested by User - as it brings Problems for big installations.

thx Andreas Schnederle-Wagner

mattab commented 9 years ago

Hi @futureweb thanks for the report. When Mysql is 100% can you print out the output for SHOW FULL PROCESSLIST \g; ? I'd like to see which part of the DB estimate is slow for you.

futureweb commented 9 years ago

Hi @mattab,

first it does a "SHOW TABLE STATUS" for about 15 Seconds, then lot's of Queries start to fill the process list and make it a little bit hard to analyze. After 10 Seconds there were about 80 Queries with Status "WAITING FOR TABLE LOCK" ... But since this Query was the only one actually running - I guess this Query is the troublemaker:

| 103017 | piwik | *ip*:42420 | piwik | Execute |    3 | Sending data                 | SELECT idvisit
FROM piwik_log_visit
WHERE '2014-11-26 00:00:00' > visit_last_action_time
AND idvisit <= '15267659'
AND idvisit > '15167659'
ORDER BY idvisit DESC
LIMIT 1 

| 103017 | piwik | *ip*:42420 | piwik | Execute |    8 | Sending data                 | SELECT idvisit
FROM piwik_log_visit
WHERE '2014-11-26 00:00:00' > visit_last_action_time
AND idvisit <= '15167659'
AND idvisit > '15067659'
ORDER BY idvisit DESC
LIMIT 1          

| 103017 | piwik | *ip*:42420 | piwik | Execute |   19 | Sending data                 | SELECT idvisit
FROM piwik_log_visit
WHERE '2014-11-26 00:00:00' > visit_last_action_time
AND idvisit <= '15067659'
AND idvisit > '14967659'
ORDER BY idvisit DESC
LIMIT 1  

| 103017 | piwik | *ip*1:42420 | piwik | Execute |   24 | Sending data                 | SELECT idvisit 
FROM piwik_log_visit
WHERE '2014-11-26 00:00:00' > visit_last_action_time
AND idvisit <= '14967659'
AND idvisit > '14867659'
ORDER BY idvisit DESC
LIMIT 1

piwik_log_visit got about ~3 Million entries and is ~1GB in size. Hope those Information help you find the problem.

mattab commented 9 years ago

Hi @futureweb

could you please confirm that you still have the problem with 2.14.0? Since we made some improvements to this area, if we are lucky, this problem could have improved. Thanks

futureweb commented 9 years ago

Hi @mattab,

unfortunately it still "stalls" my Piwik MySQL Server when going to Privacy Manager Admin Page :-/

Andreas