MariaDB - growing ibtmp1 #18864

dvo-rak · 2022-03-01T20:55:09Z

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 &quot;No more room in record file&quot;)

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!

The text was updated successfully, but these errors were encountered:

sgiehl · 2022-03-02T09:59:54Z

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 · 2022-03-02T10:34:29Z

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 · 2022-03-02T10:38:47Z

@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 · 2022-03-02T19:21:32Z

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 · 2022-03-03T08:31:31Z

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 · 2022-03-03T08:54:23Z

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

gertvdijk · 2022-08-26T08:03:43Z

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 · 2022-09-12T14:40:50Z

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)

dvo-rak added the Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. label Mar 1, 2022

dvo-rak closed this as completed Mar 3, 2022

justinvelluppillai added the answered For when a question was asked and we referred to forum or answered it. label Apr 11, 2022

SteVio89 mentioned this issue Aug 10, 2022

Integrity constraint violation: 1062 Duplicate entry '...' for key 'PRIMARY for segment temporary table #18937

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

MariaDB - growing ibtmp1 #18864

MariaDB - growing ibtmp1 #18864

dvo-rak commented Mar 1, 2022 •

edited

sgiehl commented Mar 2, 2022

dvo-rak commented Mar 2, 2022

sgiehl commented Mar 2, 2022

tsteur commented Mar 2, 2022

dvo-rak commented Mar 3, 2022

sgiehl commented Mar 3, 2022

gertvdijk commented Aug 26, 2022

gertvdijk commented Sep 12, 2022 •

edited

MariaDB - growing ibtmp1 #18864

MariaDB - growing ibtmp1 #18864

Comments

dvo-rak commented Mar 1, 2022 • edited

Expected Behavior

Current Behavior

Possible Solution

Your Environment

sgiehl commented Mar 2, 2022

dvo-rak commented Mar 2, 2022

sgiehl commented Mar 2, 2022

tsteur commented Mar 2, 2022

dvo-rak commented Mar 3, 2022

sgiehl commented Mar 3, 2022

gertvdijk commented Aug 26, 2022

gertvdijk commented Sep 12, 2022 • edited

dvo-rak commented Mar 1, 2022 •

edited

gertvdijk commented Sep 12, 2022 •

edited