@mddvul22 opened this Issue on April 7th 2021

We have, what I believe is a very large Matomo database. Up until this week, we have never scheduled any regular deletion of old raw data. Some example table sizes in our database, as of two days ago:
piwik_log_link_visit_action.ibd : 1009G
piwik_log_visit.ibd: 161G
piwik_log_action.ibd: 19G

As you can see, those three tables, combined are over 1TB.

So, yesterday, I configured the section: "Regularly delete old raw data", and told it to delete logs older than 730 days.
image

Well, according to Matomo, it deleted data earlier this morning:
image

But, this morning I logged in to our server and checked and our database has not gotten smaller, at all. Today, it shows:

piwik_log_link_visit_action.ibd : 1010G
piwik_lpiwik_log_action.ibd: 162G
piwik_log_action.ibd: 19G

Why aren't these tables getting smaller? According to this page: https://matomo.org/faq/troubleshooting/faq_42/ it appears that deleting raw data should "free significant database space". Am I doing something wrong?

Expected Behavior

I would expect that those tables would have gotten much smaller.

Current Behavior

Two of the tables actually increased in size. I think the increase is because of new data having been imported overnight. So, most likely, the purge of old data did nothing.

Context

Our database is becoming unmanageable and is causing problems for us, with backups and with Matomo upgrades. We need to reduce its size.

Your Environment

  • Matomo Version: 3.14.1
  • PHP Version: 7.2.24
  • Server Operating System: CentOS 8.3
  • Additionally installed plugins:
  • Browser: Firefox
  • Operating System: Linux
@diosmosis commented on April 7th 2021 Member

Hi @mddvul22, it should have worked to delete the logs, it's possible the task that deletes the logs is failing somehow... would you be able to check the following?

  • Find the visit_last_action_time and idvisit of the first visit in your log_visit table. If it's newer than 730 days, then it's possible the data gets deleted but server space isn't freed for some reason.
  • If you're not seeing any data being deleted, you could try running the delete logs command (note: this would take a lot of time to finish if it runs successfully), for example:
./console core:delete-logs-data --dates=2001-01-01,2019-04-08 --optimize-tables

(Note: 2019-04-08 is 730 days before today, or thereabouts. You could do a shorter range just to test. Also note that optimizing the tables also takes a long time but might also be the reason you're not seeing a change in table size.)

@mddvul22 commented on April 8th 2021

Thanks @diosmosis When looking at the log_visit table, it appears that the data did get deleted. The oldest data in that table is dated April 8th, 2019. What should I be seeing when I look at the log_link_visit_action table? The server_time column of the first row in that table is showing a timestamp of "2014-04-1 13:35:07".

@diosmosis commented on April 8th 2021 Member

@mddvul22

For the log_visit table, would you be able to OPTIMIZE it to see if it decreases the size? This should be done as part of the purging process, but if the mysql user is not allowed to or it's disabled by matomo config, it won't happen.

Regarding the log_link_visit_action table, rows should be deleted from them as well, however, we don't delete via server_time, but through idvisit. We collect the list of idvisits from log_visit to delete, then delete entries in log_link_visit_action whose idvisit matches one. Can you check if the first row in that table has an idvisit that is still in log_visit?

log_action should also have rows deleted. This is done by deleting every row that is no longer referenced in other tables, and is a rather slow process.

@mddvul22 commented on April 10th 2021

Thanks @diosmosis I think I'm making progress. I ran the following command (giving it a shorter time period, as you mentioned):

./console core:delete-logs-data --dates=2001-01-01,2015-04-08 --optimize-tables

After a few hours, I got the error that PHP had run out of memory. I was already giving it 2GB. But I raised it to 3GB, and started over. This worked and the first step of deleting visits completed. So, I think the core problem the other day was that it ran out of memory.

Then, it started optimizing table log_visit and completed, successfully. Then, it started optimizing table log_link_visit_action. Here is where I ran into a problem that I've not seen before:

Optimizing table log_link_visit_action... WARNING [2021-04-10 10:50:32] 1976738 /var/www/webanalytics/matomo/libs/Zend/Db/Statement/Pdo.php(228): Warning - PDOStatement::exec
ute(): MySQL server has gone away - Matomo 3.14.1 - Please report this message in the Matomo forums: https://forum.matomo.org (please do a search first as it might have been r
eported already)
WARNING [2021-04-10 10:50:32] 1976738 /var/www/webanalytics/matomo/libs/Zend/Db/Statement/Pdo.php(228): Warning - PDOStatement::execute(): Error reading result set's header -
Matomo 3.14.1 - Please report this message in the Matomo forums: https://forum.matomo.org (please do a search first as it might have been reported already)
ERROR [2021-04-10 10:50:32] 1976738 Uncaught exception: /var/www/webanalytics/matomo/libs/Zend/Db/Statement/Pdo.php(234): SQLSTATE[HY000]: General error: 2006 MySQL server ha
s gone away
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
#0 /var/www/webanalytics/matomo/libs/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /var/www/webanalytics/matomo/libs/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /var/www/webanalytics/matomo/libs/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('OPTIMIZE TABLE ...', Array)
#3 /var/www/webanalytics/matomo/core/Db/Adapter/Pdo/Mysql.php(309): Zend_Db_Adapter_Pdo_Abstract->query('OPTIMIZE TABLE ...', Array)
#4 /var/www/webanalytics/matomo/core/Db.php(276): Piwik\Db\Adapter\Pdo\Mysql->query('OPTIMIZE TABLE ...', Array)
#5 /var/www/webanalytics/matomo/core/Db.php(460): Piwik\Db::query('OPTIMIZE TABLE ...')
#6 /var/www/webanalytics/matomo/plugins/CoreAdminHome/Commands/DeleteLogsData.php(190): Piwik\Db::optimizeTables(Array)
#7 /var/www/webanalytics/matomo/plugins/CoreAdminHome/Commands/DeleteLogsData.php(101): Piwik\Plugins\CoreAdminHome\Commands\DeleteLogsData->optimizeTables(Object(Symfony\Comp
onent\Console\Output\ConsoleOutput))
#8 /var/www/webanalytics/matomo/vendor/symfony/console/Symfony/Component/Console/Command/Command.php(257): Piwik\Plugins\CoreAdminHome\Commands\DeleteLogsData->execute(Object(
Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#9 /var/www/webanalytics/matomo/vendor/symfony/console/Symfony/Component/Console/Application.php(874): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\
Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#10 /var/www/webanalytics/matomo/vendor/symfony/console/Symfony/Component/Console/Application.php(195): Symfony\Component\Console\Application->doRunCommand(Object(Piwik\Plugin
s\CoreAdminHome\Commands\DeleteLogsData), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#11 [internal function]: Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)
)
#12 /var/www/webanalytics/matomo/core/Console.php(140): call_user_func(Array, Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\Consol
eOutput))
#13 /var/www/webanalytics/matomo/core/Access.php(644): Piwik\Console->Piwik{closure}()
#14 /var/www/webanalytics/matomo/core/Console.php(141): Piwik\Access::doAsSuperUser(Object(Closure))
#15 /var/www/webanalytics/matomo/core/Console.php(93): Piwik\Console->doRunImpl(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\Cons
oleOutput))
#16 /var/www/webanalytics/matomo/vendor/symfony/console/Symfony/Component/Console/Application.php(126): Piwik\Console->doRun(Object(Symfony\Component\Console\Input\ArgvInput),
Object(Symfony\Component\Console\Output\ConsoleOutput))
#17 /var/www/webanalytics/matomo/console(32): Symfony\Component\Console\Application->run()
#18 {main},
caused by: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
#0 /var/www/webanalytics/matomo/libs/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/webanalytics/matomo/libs/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#2 /var/www/webanalytics/matomo/libs/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /var/www/webanalytics/matomo/libs/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('OPTIMIZE TABLE ...', Array)
#4 /var/www/webanalytics/matomo/core/Db/Adapter/Pdo/Mysql.php(309): Zend_Db_Adapter_Pdo_Abstract->query('OPTIMIZE TABLE ...', Array)
#5 /var/www/webanalytics/matomo/core/Db.php(276): Piwik\Db\Adapter\Pdo\Mysql->query('OPTIMIZE TABLE ...', Array)
#6 /var/www/webanalytics/matomo/core/Db.php(460): Piwik\Db::query('OPTIMIZE TABLE ...')
#7 /var/www/webanalytics/matomo/plugins/CoreAdminHome/Commands/DeleteLogsData.php(190): Piwik\Db::optimizeTables(Array)
#8 /var/www/webanalytics/matomo/plugins/CoreAdminHome/Commands/DeleteLogsData.php(101): Piwik\Plugins\CoreAdminHome\Commands\DeleteLogsData->optimizeTables(Object(Symfony\Component\Console\Output\ConsoleOutput))
#9 /var/www/webanalytics/matomo/vendor/symfony/console/Symfony/Component/Console/Command/Command.php(257): Piwik\Plugins\CoreAdminHome\Commands\DeleteLogsData->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#10 /var/www/webanalytics/matomo/vendor/symfony/console/Symfony/Component/Console/Application.php(874): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#11 /var/www/webanalytics/matomo/vendor/symfony/console/Symfony/Component/Console/Application.php(195): Symfony\Component\Console\Application->doRunCommand(Object(Piwik\Plugins\CoreAdminHome\Commands\DeleteLogsData), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#12 [internal function]: Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#13 /var/www/webanalytics/matomo/core/Console.php(140): call_user_func(Array, Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#14 /var/www/webanalytics/matomo/core/Access.php(644): Piwik\Console->Piwik{closure}()
#15 /var/www/webanalytics/matomo/core/Console.php(141): Piwik\Access::doAsSuperUser(Object(Closure))
#16 /var/www/webanalytics/matomo/core/Console.php(93): Piwik\Console->doRunImpl(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#17 /var/www/webanalytics/matomo/vendor/symfony/console/Symfony/Component/Console/Application.php(126): Piwik\Console->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#18 /var/www/webanalytics/matomo/console(32): Symfony\Component\Console\Application->run()
#19 {main}

[Zend_Db_Statement_Exception]
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

[PDOException]
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

core:delete-logs-data [--dates="..."] [--idsite[="..."]] [--limit="..."] [--optimize-tables]

I'm not really sure what that means. The mysql server is still running. It did reduce the size of the piwik_log_link_visit_action table from 1010GB to 844GB.

Do you have any ideas about the mysql server has gone away error?

Thanks!

@diosmosis commented on April 10th 2021 Member

@mddvul22 it means the connection was automatically closed because it hadn't been used in a certain amount of time. We have a faq about how to fix these errors: https://matomo.org/faq/troubleshooting/faq_183/. Can you see whether any of these work for you?

Powered by GitHub Issue Mirror