!
Hubzilla Support Forum !
Zotlabs|Hubzilla Development 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 50This 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 50000This will determine the number of entries marked during the `Expire` operation. Next, start the operation of marking for deletion:
php Zotlabs/Daemon/Master.php ExpireThis 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 the developer:
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
