invidious [Bug] users database table requires too much disk space

Describe the bug The users database table grows slowly (and sometimes very fast) for unknown reasons.

Screenshots image-disk-usage

Additional context

The cause is unknown but it happened with at least 2 different instances. A user from matrix reported it happened to a private single user instance. In my case the users table shrinked from 34GB to < 200MB after dump, drop and re-import. We know that the disk space was not used by indexes.

Apr 27 '21 17:04 Perflyst

Happened again

Jun 29 '21 17:06 Perflyst

@Perflyst have you setup a cron job that VACUUMs tables daily, or something like that?

Jun 29 '21 21:06 SamantazFox

Happened again

You need to do VACUUM FULL users; like explained on Matrix.

Jun 29 '21 21:06 unixfox

We could schedule a periodic VACUUM with https://github.com/citusdata/pg_cron.

Jul 16 '21 17:07 unixfox

Or just use crystal job system, that's simpler.

Sep 29 '21 20:09 unixfox

PostgreSQL supports automatic vacuum already https://www.postgresql.org/docs/10/routine-vacuuming.html#AUTOVACUUM It should be enabled by default

Sep 30 '21 16:09 Perflyst

PostgreSQL supports automatic vacuum already postgresql.org/docs/10/routine-vacuuming.html#AUTOVACUUM It should be enabled by default

AUTOVACUUM != VACUUM FULL a vacuum full literally freeze/lock the table while doing its thing, that's not a good thing to have that enabled by default.

Sep 30 '21 17:09 unixfox

Of course, I thought you want to run a normal VACUUM with pg_cron. The autovacuum doesn't even support full vacuum

Sep 30 '21 17:09 Perflyst

You need to do VACUUM FULL users; like explained on Matrix.

I'm not using Matrix, can you tell me what command I would need to run?
My guess is

psql --username kemal --dbname aaaa --password bbbb -c "VACUUM FULL users;" > /dev/null

Is this correct?

I would love to use pg_cron, but after spending 20min trying to make it work, I'm giving up and will use a cronjob on the server.

Nov 04 '21 23:11 schklom

I'm not using Matrix, can you tell me what command I would need to run? My guess is
psql --username kemal --dbname aaaa --password bbbb -c "VACUUM FULL users;" > /dev/null
Is this correct?

Yes!

Nov 04 '21 23:11 SamantazFox

Thanks :D

Should I run the VACUUM command instead of https://docs.invidious.io/Database-Information-and-Maintenance.md:

psql --username kemal --dbname aaaa --password bbbb -c "DELETE FROM nonces * WHERE expire < current_timestamp"
psql --username kemal --dbname aaaa --password bbbb -c "TRUNCATE TABLE videos"

or should I schedule the 3 commands?

Nov 05 '21 00:11 schklom

Thanks :D

Should I run the VACUUM command instead of https://docs.invidious.io/Database-Information-and-Maintenance.md:
psql --username kemal --dbname aaaa --password bbbb -c "DELETE FROM nonces * WHERE expire < current_timestamp"
psql --username kemal --dbname aaaa --password bbbb -c "TRUNCATE TABLE videos"
or should I schedule the 3 commands?

VACUUM FULL shouldn't be run often. This locks the database and create a fresh copy. This can take a while (multiple minutes). It should only be done when no database cleaning job have been executed for a while and that your accept a few minutes of downtime. See the documentation of VACUUM.

You should however run a periodic VACUUM video; VACUUM users; (every week or so, depending on your instance's size), in addition to the two commands recommended in the docs.

Nov 05 '21 01:11 SamantazFox

I'm running into this as well on my instance (invidious.sethforprivacy.com), but it's filling the disk in ~1h ATM for some reason.

In 1h20m, my users table went from 0 to 127GB after I ran a VACUUM FULL users; for the first time. There is obviously some major bug or DoS vector in the latest release.

Platform: Docker amd64 Version: quay.io/invidious/invidious:latest (dca3da7b696e)

Jan 05 '22 19:01 sethforprivacy

I'm running into this as well on my instance (invidious.sethforprivacy.com), but it's filling the disk in ~1h ATM for some reason.

In 1h20m, my users table went from 0 to 127GB after I ran a VACUUM FULL users; for the first time. There is obviously some major bug or DoS vector in the latest release.

Platform: Docker amd64 Version: quay.io/invidious/invidious:latest (dca3da7b696e)

Do you mind trying this old docker image if it solves your issue: quay.io/invidious/invidious:fdc380e7f7977cdae4b9d55cc06ea5e53fdab762?

Jan 05 '22 19:01 unixfox

Do you mind trying this old docker image if it solves your issue: quay.io/invidious/invidious:fdc380e7f7977cdae4b9d55cc06ea5e53fdab762?

Testing now, users had grown to 20GB in the last hour already in the meantime! Vacuumed it again and running on the specified tag above now.

Will report back.

Jan 05 '22 21:01 sethforprivacy

Just confirming I am seeing this as well. Got up to 177GB overnight for the total database size and the full vacuum dropped it to 1.2GB but its climbing rapidly at about 1GB a minute.

Looking into this its specifically the users table and the column notifications. The array there is gaining multiple entries a second. This is only the case with a user that has subscriptions which is as expected. While its going through this process after the vacuum the subscriptions page is not tracking I have seen the latest notifications nor updating with recent videos. I presume its just going to keep growing until its caught back up again with all channels.

Is there a process to prune old notifications that are no longer necessary? 28GB corresponds to about 164271 entries. That should mean its just 11 characters per notification * 164271 = 1,806,981 runes and they appear to be normal characters so 8 bits a piece but the overhead looks substantial in space usage, nearly 16600x what you would expect.

Jan 06 '22 10:01 BrightCandle

My users table only grew 19MB overnight using quay.io/invidious/invidious:fdc380e7f7977cdae4b9d55cc06ea5e53fdab762, @unixfox.

Seems there is some breaking change in the latest Docker image that causes this bug as I thought! Will stay on that image until this is resolved.

Jan 06 '22 12:01 sethforprivacy

Could all of you try the latest Docker image in order to confirm that it solves the issue?

Jan 06 '22 13:01 unixfox

Could all of you try the latest Docker image in order to confirm that it solves the issue?

Testing a65da2f60860 now.

Jan 06 '22 13:01 sethforprivacy

quay.io/invidious/invidious:latest is to be tested

Jan 06 '22 14:01 unixfox

quay.io/invidious/invidious:latest is to be tested

a65da2f60860 is quay.io/invidious/invidious:latest FWIW.

Jan 06 '22 14:01 sethforprivacy

Testing latest version.

The subscription page appears to be working, watch notifications is resetting back to zero and its pulled some updates from today although it missed the last 11 hours or so.

The notifications column in users has multiple times limited its total growth and shrunk down after growing to a few thousand entries in the array. But alas its slowly growing again now need to wait and see if it continues.

Database size is still massive (133GB) and the space hasn't been reclaimed.

Jan 06 '22 14:01 BrightCandle

Testing latest version.

The subscription page appears to be working, watch notifications is resetting back to zero and its pulled some updates from today although it missed the last 11 hours or so.

The notifications column in users has multiple times limited its total growth and shrunk down after growing to a few thousand entries in the array. But alas its slowly growing again now need to wait and see if it continues.

Database size is still massive (133GB) and the space hasn't been reclaimed.

You need to do a VACUUM FULL; in order to reclaim the disk space.

Jan 06 '22 14:01 unixfox

Could all of you try the latest Docker image in order to confirm that it solves the issue?

Testing a65da2f60860 now.

The latest image, a65da2f60860, has the same issue for me -- users grew to 35GB in ~2h.

Jan 06 '22 15:01 sethforprivacy

I'm now running this command every 10 minutes in order to force a soft vacuum that doesn't lock the tables: VACUUM FREEZE users;. It won't reclaim the disk space used by postgres, but it will make more free space for the new data. In other words, it stops the insane growing size of the table.

Jan 06 '22 19:01 unixfox

I have had to roll back to this container quay.io/invidious/invidious:823121637146c4b5b13ab4f4326cb73ff16828d6

I do not have my table growing anymore.

Note: I did not try every container in between the current version and the one I posted.

Jan 06 '22 22:01 omar-elnaggar

About the main growing users table size issue, it's mainly caused by the notifications being updated very frequently when you have a lot of users. Watch history could also be an issue, but I think a bit less of an issue.

That's why I disabled notifications on my instance (https://yewtu.be): https://github.com/unixfox/invidious-custom/tree/master/patches

In my opinion, watch history and notifications should be two things that can be disabled on demand.

Feb 03 '22 21:02 unixfox

So it happening again, i saw continous insert update when checking # iotop, stacking up 50GB

quay.io/invidious/invidious:latest-arm64

docker image inspect a64880c690e1                            
[
    {
        "Id": "sha256:a64880c690e16cd6ed125c32b3fee169e95250b3832040624ace5f626712026d",
        "RepoTags": [],
        "RepoDigests": [
            "quay.io/invidious/invidious@sha256:7570a702157474c1e07d44686ec71a3bf119cd8b79ab747f3accf6d068b40939"
        ],
        "Parent": "",
        "Comment": "buildkit.dockerfile.v0",
        "Created": "2022-02-13T01:06:49.087190214Z",
        "Container": "",
        "ContainerConfig": {
            "Hostname": "",
            "Domainname": "",
            "User": "",
            "AttachStdin": false,
            "AttachStdout": false,
            "AttachStderr": false,
            "Tty": false,
            "OpenStdin": false,
            "StdinOnce": false,
            "Env": null,
            "Cmd": null,
            "Image": "",
            "Volumes": null,
            "WorkingDir": "",
            "Entrypoint": null,
            "OnBuild": null,
            "Labels": null
        },
        "DockerVersion": "",
        "Author": "",
        "Config": {
            "Hostname": "",
            "Domainname": "",
            "User": "invidious",
            "AttachStdin": false,
            "AttachStdout": false,
            "AttachStderr": false,
            "ExposedPorts": {
                "3000/tcp": {}
            },
            "Tty": false,
            "OpenStdin": false,
            "StdinOnce": false,
            "Env": [
                "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin"
            ],
            "Cmd": [
                "/invidious/invidious"
            ],
            "ArgsEscaped": true,
            "Image": "",
            "Volumes": null,
            "WorkingDir": "/invidious",
            "Entrypoint": null,
            "OnBuild": null,
            "Labels": {
                "quay.expires-after": "12w"
            }
        },
        "Architecture": "arm64",
        "Os": "linux",
        "Size": 85798308,
        "VirtualSize": 85798308,
        "GraphDriver": {
            "Data": {
                "LowerDir": "/var/lib/docker/overlay2/af56a6b9bea8148ddac166e02c1e3307356332b7dc3177c5da4f0da82c6c9c0d/diff:/var/lib/docker/overlay2/c84a9c749ef18336d9e537fbe5c53a992bd79dc2986d5eab322c455d6f5c9edb/diff:/var/lib/docker/overlay2/0008f0212cef99818527d4571ed7eb9e305e9cc7fea8b4a18e85b62ec50a507a/diff:/var/lib/docker/overlay2/fe9cc8fb8b6e415bbef21e06ed3897c5b7772c342641e62c2ce72844f4b4ee57/diff:/var/lib/docker/overlay2/2800cb1432dd5c7225e09f54d68da7e9e56bfec34727a17df609071f701ae9cb/diff:/var/lib/docker/overlay2/11ffda75c256263a6604051be353570b2e6ca4ba8c11d325a6c5a15c1163c8c7/diff:/var/lib/docker/overlay2/9524e3a05dcaee444a90850516ef941bdc244a3ededb7e3809c016c168e6c244/diff:/var/lib/docker/overlay2/ea245c796342c2d6cb906122600553575ede61d206e6b6e7988cf88e0ca47a46/diff:/var/lib/docker/overlay2/fe4d18de2cec60f94e579f857f5ecba4d36f2fa42e17bea96c99cd4c1942a146/diff:/var/lib/docker/overlay2/a17fbafb1a387a65955e09809d945c5f7d72cbb19bebe9541a40137c9af79a5b/diff",
                "MergedDir": "/var/lib/docker/overlay2/d9189f9a20f4a3043bc71b7baa86a47dbbea935b6d2fab7cdf0406ab6622148d/merged",
                "UpperDir": "/var/lib/docker/overlay2/d9189f9a20f4a3043bc71b7baa86a47dbbea935b6d2fab7cdf0406ab6622148d/diff",
                "WorkDir": "/var/lib/docker/overlay2/d9189f9a20f4a3043bc71b7baa86a47dbbea935b6d2fab7cdf0406ab6622148d/work"
            },
            "Name": "overlay2"
        },
        "RootFS": {
            "Type": "layers",
            "Layers": [
                "sha256:07d3c46c9599a88fa81c385391300ab877b98ef71b18cce942455025657edeb5",
                "sha256:5ccbbdeee92e9202698851e1bc615b6e58f01aa882a23d861486a61213d6f433",
                "sha256:7d68944cb47b3880fc48a15f2538353cd90223fd9598d31c1c8460ae1651b266",
                "sha256:db4344727231cecb07dbcba525f7da233e9d62ba3ce45ae0d046c4b403e047da",
                "sha256:69370c9a0cce3fbbc9324f0e4d495292014939956420760a8a5609eb04264c71",
                "sha256:c2b1180fc0f89145cf21f376f06c8595769dd5bf46430773828cb810c27b4ada",
                "sha256:5e88a53af16006cbf1aea0165a528ddc713f38e2a06e50b7618cdbc8a6c9f1df",
                "sha256:f2735a8a927608a31e02c2b2d9ff317ca09bf18928eb6dcc0ace21ff27d315a0",
                "sha256:74603a10566bc87bedfd68977c5fdfc0dce99a159f9202fe11ec9f0c24a2672e",
                "sha256:138c9b3b33660c4c010a3b09d4076436e4401b334ff0de718cab56efc6c46bbb",
                "sha256:9b0030accee609a337b12d070893ad600d64e2e1d139efb398062eed264e0e28"
            ]
        },
        "Metadata": {
            "LastTagTime": "0001-01-01T00:00:00Z"
        }
    }
]

Feb 15 '22 04:02 martadinata666

FWIW this has started happening again very badly again for us -- despite 'vacuum users' every minute and 'vacuum full users' daily. Minutely vacuum worked fine for a while (half a year?), but in the past couple of weeks we've had our database size grows by 20-30GB in the space of a couple of minutes and miserably failing with ENOSPC at that point.

We've just now applied yewtu.be's patch to disable notifications as a stop-gap but this really strikes me as weird; the table itself is tiny what is it we're doing that makes postgres use so much space? Could the updates be batched in memory so we rewrite the full table when we're done (or every x steps or something)? assuming it's src/invidious/channels/channels.cr it looks like we're looping over each feeds for each users and updating notifs per feed, for now batching per user might already make a big difference.. (I haven't actually confirmed if it's the channel update or somewhere else, but I think that's the current assumption?)

Dec 03 '22 02:12 martinetd

New PR created for globally disable the user notifications in order to combat against the possible ever growing disk space increase: https://github.com/iv-org/invidious/pull/3473

Please test the PR and give your feedback in the comments of the PR. You need to add enable_user_notifications: false in the config.yml file.

Mentioning @martinetd @martadinata666 @FireMasterK

Dec 07 '22 12:12 unixfox

invidious invidious copied to clipboard

[Bug] users database table requires too much disk space

invidious
invidious copied to clipboard