Prevent running out of disk space with MySQL


#1

Could whoever maintains the git + wiki (install guides), please add a note for MySQL installations?

InnoDB for MySQL/MariaDB’s temp file is set up by default to expand forever and the only way to clear/reset it is to restart MySQL. It will keep growing until there is no more disk space left, or you restart the MySQL service.

The good news is this is an easy fix. The simple fix for this is to add a new line to the MySQL config file that limits how big the temp file can grow.

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G

The setting above is set for 10GB max file but can be set to anything.

Please add a note to the install guide and git docs for any MySQL users who run into this issue.

Thanks


#2
  1. i don’t really see how this is related to tt-rss
  2. even for mysql this sounds like something too stupid to be true

#3

Sounds like it’s the most recent addition to a system with other things on there that are using lots of temp tables.

Lots.

To the point where 10G of them at the same time seems reasonable.


#4

I had the same/similar problem of MySQL eating RAM like a hungry zombie craves brains… Solved it by moving to postgres. Since then, no more MySQL problems whatsoever.


#5

TT-RSS uses InnoDB alot, and everytime it does the InnoDB temp file expands in size.

On an TT-RSS installation that has around 30 feeds with lots of activity InnoDB temp file grew to 40GB in size, preventing it from working (updaing feeds, acting weird) untill mysql was restarted.

This is directly related to how TT-RSS functions and uses MySQL/MariaDB

By putting a hard cap on the file size, it will prevent TT-RSS from making MySQL consume all the free space.

https://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tablespace.html


#6

Looks like I need to upgrade my MySQL version as well then - I don’t seem to have this feature…


#7

Not sure what version you are running but I have mysql-server-5.7 running.

Location of the default InnoDB temp file is /var/lib/mysql/ibtmp1


#8

I have been running TT-RSS with more than 30 feeds for years on MySQL. I have never come across this issue, ever.

Regardless, this is not a TT-RSS issue.

e: From the docs:

the temporary tablespace data file is autoextending and increases in size as necessary to accommodate on-disk temporary tables. For example, if an operation creates a temporary table that is 20MB in size, the temporary tablespace data file, which is 12MB in size by default when created, extends in size to accommodate it. When temporary tables are dropped, freed space can be reused for new temporary tables, but the data file remains at the extended size.

[emphasis mine]

I guess this is why most users don’t have an issue–the space is reused.


#9

Well, quite - as I said upthread…

This smells of more than just TTRSS running on that server.


#10

tt-rss is not somehow unique in its ability to insert data into a database so this isn’t in any way related to tt-rss (or any other database client) per se.

if this issue really does exist as described (i’m not convinced still) this should be reported to mysql and maybe distro maintainers because inability to deal with inserts without restarting seems like a critical problem.

in any case, OP made the thread here, so if other people run into this issue with tt-rss specifically they should be able to find a solution. i’m not going to update any documentation because of this, however.

e: does tt-rss even use temp tables? not explicitly that’s for sure.


#11

I just figured I would post this to try and get it added.

I wiped one of my servers and installed tt-rss (love it btw), some plugins and a mysql search.

In about a week with me viewing it everyday, I started getting out of memory errors, to the point it kind of broke tt-rss (nothing would work and was getting gui errors)

Did some research and found that the innodb file expanded to my whole drive, only way to fix it was to restart the service, and set a cap to prevent it in the future.

This was the exact error it caused
https://tt-rss.org/oldforum/viewtopic.php?t=2608

NOTE Error only, not related to thread title, or resolution (well it was never resolved for him)


#12

That post relates to RAM, whereas you’ve been talking about disk space this entire time.


#13

The thing is its not caused by ram, its the same error if you run out of disk space


#14

Sure, but when you say, “This was the exact error it caused,” and link to a thread that’s discussing out of RAM issues… It’s misleading and could confuse others. Your issue is distinct from the issue in that thread.


#15

But it is the exact error I was getting.

I understand your point and I’ll edit it to reflect error only, but do you think it would help if I stole their image as well? Havent gotten this error in awhile so I cant make a new one.

Also, him adjusting his memory never fully resolved the issue.


#16

I tend to think that the PHP memory error is unrelated if only because, based on your posts so far, there isn’t an issue with PHP. The issue is with MySQL running out of disk space, that should cause MySQL to fail and PHP (I should think) would report a failure with the database.

Now PHP could have a memory or disk error because TT-RSS does write to disk, etc. but without seeing the actual error message for your particular issue it would be difficult to diagnose.

Keep an eye on your system and post with an error from your system if one ends up occurring.


#17

As I stated before.

That is the exact error message I was receiving.

The photo in the other thread, 100% verbatim


#18

The error message might be the same but the location at which the error occurs will likely be different, and that’s the information that’s going to help resolve it.

Like I said, keep an eye on your system and post with an error from your system if one ends up occurring.


#19

I’m not a MySQL guy, so take this with a grain of salt. If this issue is related to temporary tables/files/etc in MySQL consuming all available disk space, MySQL could be treating that as “virtual memory” (like an OS treats swap). If the database is unable to store more information in that “swap” area, and if the database treats this swap area as virtual “memory”, then it may report an out of memory error back to PHP.

If that’s what’s going on, it could explain why this out of disk issue manifests as an out of memory error in the tt-rss logs. What do the MySQL logs say?

But as I said, I’m not a MySQL guy (viva PostgreSQL).


#20

As I also already stated, I resolved it by capping how large the temp file can grow.

This post is literally just asking if they could include that in the setup instructions.

The InnoDB temp files only grows, it does not shrink. It will eventually grow to use all available disk space unless MySQL is restarted.

The config option I was proposing to have included in the install guide is to limit how much the temp file can grow instead of letting it consume the whole disk.