How to I clean up my database?

Thu, 13 Apr 2023 21:42:00 +0200

adminsforum@hubzilla.org

Hans van Zijst wrote the following post Thu, 13 Apr 2023 21:42:00 +0200

How to I clean up my database?

Hi,

I've run my own Hubzilla instance since 2019, but I'm having some problems lately. I think most of it is caused by an overloaded machine, so cleaning stuff up is my first port of call.

My database has grown to 25GB, and I'd like to make it a lot smaller. I'm the only user on my hub, so it shouldn't need to be this big.

I notice I have items in it that date back to 2017, 2 years before I started this hub. I thought I had configured it to expire old stuff, but it turns out I hadn't. So I changed it to 2 years, everything older than 2 year can be removed. Running the Cron.php doesn't help, so I looked at its source and noticed that it selects the items to remove by expiry date.

I think I can simply set an expiry date on items that were created over 2 years ago and then run Cron.php, but before I destroy my instance, I'd like to hear your opinion first.

If that's indeed the way to go, do I set an expiry date on records in "item", or are there other things that need attention too?

Mon, 17 Apr 2023 10:22:49 +0200

ivan zlax

zlax@ussr.win

Here the 'discussion': #^https://ussr.win/link/6141447

!{support@zotadel.net} !{hubzilla-development@zotlabs.org}

UPDATE: this is an instruction for extreme cases only, for example when sys_expire_days equals 0 or a large number of days (with many channels and connections) and public stream enabled. The size of the item table in InnoDB sometimes grows to critical sizes.

Hubzilla has an undocumented problem that hub administrators often face. This is an overflow of the database table `item` to the point when the free space required to reduce the database is running out.
Databases of the InnoDB type stores removed records until the "OPTIMIZE TABLE" operation is performed (InnoDB does not support optimisation, but recreates the table instead). In doing so, the table is rebuilt, which requires sufficient free space.
Sometimes administrators have a problem: the tables grow to such an extent that there is the lack of free space to do optimization. This problem is accompanied by the error "The table 'item' is full. Operation failed".
Check how many entries you have in the 'item' table, maybe hundreds of thousands of entries, maybe more than a million. The procedure for deleting outdated records is time-consuming, so it is recommended that you do everything in stages, for example in portions of 50,000 records.
Above all, make a database backup. Next, the necessary parameters must be set:
util/config system.sys_expire_days 50
This parameter will determine the mark for deleting records older than 50 days (from the public stream rather than the hub channels). Then:
util/config system.expire_limit 50000
This will determine the number of entries marked during the `Expire` operation. Next, start the operation of marking for deletion:
php Zotlabs/Daemon/Master.php Expire
This operation can take up to 10 minutes. If you have 500,000 records, it must be repeated 10 times, if you have 1,500,000 records, it must be repeated 30 times.
You can make a bash script that repeats this operation and shows how many iterations have been processed during execution:

num=0
while [ "$num" -lt 10 ]
do
  num=`expr $num + 1`
  echo iteration: $num
  php Zotlabs/Daemon/Master.php Expire
done

If there are no entries left for the deletion, the operation will be performed relatively quickly.
If it is not possible to free up enough free space on the host for operation "OPTIMIZE TABLE" (the free space must be approximately as much as the `item` table files size), it is possible to stop the database, copy the database files to another host with sufficient resources - the following optimisation operations can be performed there and then copy the files back.
Next, delete entries from the database itself:

mysql -u user -pPASS -D hubzilladb -e "DELETE FROM item WHERE item_deleted = 1 LIMIT 50000;"
mysql -u user -pPASS -D hubzilladb -e "DELETE FROM item WHERE item_pending_remove = 1 LIMIT 50000;"

These operations must also be repeated several times to delete all previously marked entries.
And after that, execute the table optimization command:
mysql -u user -pPASS -D hubzilladb -e "OPTIMIZE TABLE item;"
It is also recommended that other large tables be optimized afterwards to reduce the size of the database (`photo`, `xchan`, `iconfig`, `term`, `hubloc`, `dreport`)

The execution of these operations is allowed only in the exceptional case of database overflow.

Instruction for this problem from Maximilian von Königsberg:

Max Kostikov wrote the following post Thu, 03 Dec 2020 22:00:54 +0100

If someone was so careless that allowed Hubzilla server to run out of disk space and does not have ability add it, I would recommend doing the following.

Stop the web server and / or PHP application server.
Make a MySQL dump on remote host. This can be done without local disk space usage by SSH. E.g. from the same server you may run something like mysqldump -u dbuser -pdbpassword hubzilla_database_name | gzip -c | ssh user@remote.host 'cat > backup.gz'
Drop and recreate hubzilla database.
Inspect local disk usage with any tool you like e.g. du and remove unnecessary data such as archived log files, temporary files, downloaded packages and so on.
Check your MySQL configuration. If you are not using replication, then it is a good idea to disable binary logs to save disk space.
Restore MySQL database from the remote host. Again, if you are on the same host you may run mysql -u dbuser -pdbpassword hubzilla_database_name < ssh user@remote.host 'gunzip -c backup.gz'
In addition you can delete contents of outq and workerq tables.
Tune imported content expiration settings system.default_expire_days and system.active_expire_days (and possibly system.expire_limit) if need.
Check current disk space usage and decide if this is enough to get you going for at least 10 days.
Start your web server and / or PHP application server.

Steps listed above do not require any special skills beyond basic system administration knowledge.

In addition before server restart to speed up content expiration you may

Execute php Zotlabs/Daemon/Expire.php
Execute mysql -u dbuser -pdbpassword hubzilla_database_name -e "UPDATE item SET changed = NOW() - INTERVAL 10 DAY WHERE item_deleted = 1 AND item_pending_remove = 0"
Execute php Zotlabs/Daemon/Expire.php one more time

#hubzilla #innodb #overflow #database #expire