MariaDB - growing ibtmp1

[dvo-rak]

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]

Hi @dvoraan8. Thanks for creating the issue. We are actually creating temporary tables while archiving in various cases.
See

matomo/core/DataAccess/LogAggregator.php

Lines 273 to 298 in fe73429

	private function createTemporaryTable($unprefixedSegmentTableName, $segmentSelectSql, $segmentSelectBind)
	{
	$table = Common::prefixTable($unprefixedSegmentTableName);
	if ($this->doesSegmentTableExist($table)) {
	return; // no need to create the table, it was already created... better to have a select vs unneeded create table
	}
	$engine = '';
	if (defined('PIWIK_TEST_MODE') && PIWIK_TEST_MODE) {
	$engine = 'ENGINE=MEMORY';
	}
	$createTableSql = 'CREATE TEMPORARY TABLE ' . $table . ' (idvisit BIGINT(10) UNSIGNED NOT NULL, PRIMARY KEY (`idvisit`)) ' . $engine;
	// we do not insert the data right away using create temporary table ... select ...
	// to avoid metadata lock see eg https://www.percona.com/blog/2018/01/10/why-avoid-create-table-as-select-statement/
	$readerDb = Db::getReader();
	try {
	$readerDb->query($createTableSql);
	} catch (\Exception $e) {
	if ($readerDb->isErrNo($e, \Piwik\Updater\Migration\Db::ERROR_CODE_TABLE_EXISTS)) {
	return;
	} else {
	throw $e;
	}
	}

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 ?

[dvo-rak]

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]

@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]

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)

[dvo-rak]

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]

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

[gertvdijk]

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 #14776 :-(

So... perhaps reopen this bug report to investigate to confirm it's this MariaDB bug, and if so, address this in Matomo documentation.

[gertvdijk]

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!

Update 2: MariaDB 10.6.10, 10.7.6, 10.8.5 and 10.9.3 with the fix have been released September 19. 🥳

Fix InnoDB Temporary Tablespace (ibtmp1) is continuously growing (MDEV-28240)