@dvoraan8 opened this Issue on March 1st 2022

I am managing Matomo instance with about 500k daily visitors with 3M daily actions. Recently I've upgraded from Ubuntu Xenial with MariaDB 10.1, PHP 7.2, matomo 4.5x. Since then I have noticed issues related to database.

Since the beginning we operate with Matomo (3+ years), the size of ibtmp wasn't capped. After the update ibtmp grew over all available space (90GB+), so I have capped the limit to 5GB:
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G

Really, after that the max size of ibtmp file is capped to 5GB, but once it hit the 5GB threshold, archivation process started to fail:

Can't create table `matomo`.`matomo_logtmpsegment6f0bbf5774c1682697d37bba2507083d` (errno: 135 "No more room in record file") 

We run archivation every hour with the following command:
/usr/bin/php /srv/matomo/console core:archive --url=https://matomo.xxx/ >> /var/log/matomo-archive.log 2>> /var/log/matomo-archive-error.log

I have increased the maxsize of autoextend to 50GB and we restart the MySQL to wipe ibtmp +- once a week, which is far from ideal.

Expected Behavior

Matomo archivation working

Current Behavior

When ibtmp1 size limit is achieved, archivation process starts to fail

Possible Solution

restart of MySQL service...which is not ideal

Your Environment

  • Matomo Version: 4.7.1
  • PHP Version: 8.1.3
  • Server Operating System: Ubuntu Focal
  • MySQL version: MariaDB 10.6.7
  • Config:
    [General]
    enable_sql_optimize_queries = 0
    login_allowlist_apply_to_reporting_api_requests = 0
    login_allow_logme = 1
    salt = "xxx"
    trusted_hosts[] = "xxx"
    trusted_hosts[] = "xxx"

Thank you!

@sgiehl commented on March 2nd 2022 Member

Hi @dvoraan8. Thanks for creating the issue. We are actually creating temporary tables while archiving in various cases.
See https://github.com/matomo-org/matomo/blob/fe734297e24cea49d1ca3a35b4879e17f92b2921/core/DataAccess/LogAggregator.php#L273-L298
But those tables should be dropped if they aren't needed anymore.

It seems MariaDB changed the handling of temporary tables in 10.2. See https://mariadb.com/kb/en/innodb-temporary-tablespaces/

I wonder if that is an issue with MariaDB not cleaning up the space correctly. Or are you able to see a lot temporary tables starting with logtmpsegment ?

@dvoraan8 commented on March 2nd 2022

Hello @sgiehl,

unfortunately it seems like there is no way to observe the contents of The Temporary Tablespace in current version of mariadb...

MariaDB [(none)]> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_TEMP%';
Empty set (0.000 sec)

MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
ERROR 1109 (42S02): Unknown table 'INNODB_TEMP_TABLE_INFO' in information_schema

MariaDB [(none)]> SELECT  * FROM INFORMATION_SCHEMA.FILES;
Empty set (0.001 sec)

https://jira.mariadb.org/browse/MDEV-12459

@sgiehl commented on March 2nd 2022 Member

@dvoraan8 you could actually try to disable the segment cache with setting enable_segments_cache = 0 in config. That should prevent creating any temporary tables while archiving. But I guess it will slow down the archiving a lot.
Maybe @tsteur has another idea on that.

@tsteur commented on March 2nd 2022 Member

I'm not so much into this topic but did a quick search. https://stackoverflow.com/questions/41216630/how-to-clean-or-resize-the-ibtmp1-file-in-mysql suggests you may need to restart the DB to reclaim the storage and similarly https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html says the same?

To reclaim disk space occupied by a temporary tablespace data file, restart the MySQL server. .... When the data file reaches the maximum size, queries fail with an error indicating that the table is full.

I don't know if it makes sense what I'm writing but maybe it's expected to restart the DB from time to time there? Generally our temp tables should be typically fairly small, also with about 500K daily visitors. And temporary tables are automatically dropped by us, and MySQL automatically drops/deletes any temporary table as soon as the MySQL connection is closed (which happens after we generated a report for a given day period). With that I'm meaning I'm not sure we can do much differently since the temp tables are generally small and dropped as soon as they are no longer used. I wonder if this is maybe expected behaviour that the file increases in size and then needs a restart? (doesn't sound right but maybe it is the case)

@dvoraan8 commented on March 3rd 2022

Thank you @sgiehl , setting "enable_segments_cache = 0" did the job. Ibtmp stopped growing and archivation is little slower, but still very well in acceptable range.

@tsteur from time to time is acceptable, but ibtmp file growing 5GB per day and restarting database every week even when you have ibtmp size limit comparable to size of whole matomo db is plain stupid.


I'd consider this issue as solved.

@sgiehl commented on March 3rd 2022 Member

It's still a bit weird that MariaDB doesn't seem to clean up the space after a table is dropped.

This Issue was closed on March 3rd 2022
Powered by GitHub Issue Mirror