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.
Matomo archivation working
When ibtmp1 size limit is achieved, archivation process starts to fail
restart of MySQL service...which is not ideal
[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"
Hi @dvoraan8. Thanks for creating the issue. We are actually creating temporary tables while archiving in various cases.
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
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)
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)
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.
It's still a bit weird that MariaDB doesn't seem to clean up the space after a table is dropped.
I'm also running into this issue and it seems like a bug in MariaDB 10.6+ that's at least contributing to this: MDEV-28240.
Updated to latest 10.6.x, .9 did not resolve it for me, so it appears unfixed in there.
Will have to try the suggestion on setting
enable_segments_cache=0, but then I'm worried about performance impact which is already slow on segments. Moreover, the setting is scheduled for removal in Matomo 5.x via https://github.com/matomo-org/matomo/issues/14776 :-(
So... perhaps reopen this bug report to investigate to confirm it's this MariaDB bug, and if so, address this in Matomo documentation.
Update: MariaDB has found the cause (present since every current 10.6.x GA release) and implemented a fix to be released in upcoming versions 10.6.10, 10.7.6, 10.8.5 (via MDEV-28240). I haven't tested it yet, but this seems great news!
Fix InnoDB Temporary Tablespace (ibtmp1) is continuously growing (MDEV-28240)