Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
None
Description
I have installed Mariadb-10.6.8 on Amazon Web Service (AWS) RDS instance. The RDS instance has a disk capacity of 150GB and 16GB RAM and it hosts 3 databases of total size 13gb. This database serves a website which hardly has much DML operations and predominately read data from this database using stored procedures. These stored procs extensively use temporary tables and the query performance is well under 1 sec. On the website there would be only around 10 to 25 concurrent users most of the time and at peak time there would be 30 to 35 users. Please note the db engine for all tables are InnoDb
The problem
-----------
When I start the RDS instance the disk space available is 137 GB (with 13 gb used by the data held by the databases). Now as the day progresses and the users access the website the disk space starts reducing drastically and reduces by 35gb in 1 day (though there are hardly couple of inserts/updates). If now I restart the RDS instance then the disk space of 137GB is available again and as the day progresses the disk space keeps on reducing again. So the issue is why is the disk space reducing automatically.
Attachments
Issue Links
- duplicates
-
MDEV-28240 InnoDB Temporary Tablespace (ibtmp1) is continuously growing
-
- Closed
-
- links to
Activity
Hi Daniel, Thank you for your reply.
We have been using Mariadb 10.3.8 since last 10+ years and never had this problem and just 3 weeks back we migrated to 10.6.8 and its the same code base and we are facing this issue.
We are extensively using explicitly created temporary tables and as mentioned earlier never faced this issue on 10.3.8 version. Since the database is deployed on AWS RDS instance we do not have directory access (or at least I am not aware how to do it) so i am not sure what is the file size of /var/lib/mysql/ibtmp1. I checked the database sizes and also tried checking file size using the below queries
SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024/1024,2) "size in GB" FROM information_schema.tables GROUP BY 1 ORDER BY 2 DESC; – even though the AWS console shows the disk space used as around 80GB the individual database size returned by this query sums up to 11gb
select * from information_schema.files; – this does not return any record.
Since this issue is on our live website we also are looking to move back to an earlier db version and hence we have set up a 10.3.35 database (development instance) and have simulated website hits to this new database and will be observing whether the disk usage reduces as it does for 10.6.8 and if all good will check against 10.4.<last release> and 10.5.<last release>.
I'm going to close this for now. After 10.6.10 is in RDS, if it still occurs please reopen.
Visibility was obviously a problem, so thanks to thiru visibility of the size of the innodb temp tablespace is coming to information_schema.innodb_sys_tablespaces (details MDEV-29479).
This might be a duplicate of
MDEV-26782. Are the temporary tables explicit (like create temporary table) or implicit as the result of table joins/sort?Can you examine the /var/lib/mysql/ibtmp1 size?