Hardware: Raspberry Pi at home YunoHost version: latest I have access to my server : Through SSH Are you in a special context or did you perform some particular tweaking on your YunoHost instance ? : not really
Description of my issue
I can’t backup Nextcloud, as it gives me this error:
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `oc_filecache` at row: 2881244
You mean using Yunohost automatic backup ?
I might have a couple of possibilities to check in order to help you make that upgrade, if you wish
Do you have a topic on this, so I can avoid the off-topic here ?
I mean, when you for example change the timeout from 120 seconds to 300 seconds, and compare the logs from the two attempts, does the second run about 180 seconds longer?
Another guess: you changed the timeout of net_r/w_timeout, could it be that that timeout is not used by mysqldump? The flash layer of the SD card is heavily hammered and maybe has to clear blocks before writing data, MySQL is patient enough to wait, but mysqldump thinks MySQL has gone away?
8 GB of free space, it failed (and even sooner than before, line 2264100).
Not a disk space issue I think.
I did not measure it, at all, it’s a long automatic backup (using archivist), I won’t monitor it, it’s too painful ^^
But it stops around the same number of rows, which makes me think that’s not a timeout issue (I tripled the value, no visible change).
I have no idea…
One quick suggestion, just to be sure: if you have enough space for the temporarybackup (before it makes the final .tar file I mean), but not for it + the final tar archive, and are using a recent version of Yunohost where all backups are not compressed (not .tar.gz files), one solution could be to compress existing backups to save space, at least temporary.
In addition, you could check if some system log files are using a lot of storage (like >100MB), do apt-get clean to free apt cache. That might save you a few gigs.
I might have a broader issue: several times a day, my server start lagging a lot, and mysql takes a very high load, for no apparent reason. Restarting it solves the issue. Waiting a long time too (such as 1h).
Turns out I have another issue that is causing this: my oc_filecache table has… 12495189 rows. 38GB.
I don’t have that many files.
In fact around 10000000 “cached files” rows are from… /home/myaccount/ folder… Which is almost empty, apart from a symbolic link to /media, where I mount an external HDD (via USB, not smb or such). Only something like 10 000 rows are dedicaded directly to /media (in another group of oc_filecache table).
This explains also why mysql has been regularly overloading my server… turns out there is an issue with file caching ?
Sorry for keeping silent, yes, I also had the filecaching problem.
I removed all external storage from the Nextcloud configuration, but the problem persists because my server is not capable enough to remove records from the table. No help from me, at this point
I may have some notes with a simple cursor for removing cache rows, let me check…
I “solved” my issue by very slowly (it took days) deleting most of these lines, from the category belonging to some “external storage” (actually /home, with a few symbolic link pointing to an external storage mounted that was counted a few hundred times…), then doing a “truncate” to empty the database table. The filecache rebuilding went well.
I wished there were a more lightweight option for Nextcloud to present files through the web interface. As far as I understand, any file to be shown in Nextcloud, has to come from the cache. It would be nice if it could just link to the file on disk as long as no changes/versioning is made.
Sorry not to reply any sooner. I did look for my cleanup scripts, but have not found them.
@wbk@Lapineige same issue as yours with a 2.7Gb oc_filecache !
I have 6 SMB/CIFS external_storage from NAS…
I understand why my NC has become so slow !
I was migrating it from LXC to Docker… and the SQL backup show me the issue.
I will report if I find something than can help !
Back then I have spend hours (days, if not weeks) trying to delete just the records matching the external storage. That ran fast enough, but there were millions of records to be removed, and they got added again in a blink of an eye.
Now I can hardly believe that I just freed 330 GB of tablespace in less than 20 seconds