Manually deleting the outq?

Mon, 12 Oct 2020 19:03:52 +0200

buckaroo@hub.netzgemeinde.eu

!Hubzilla Support Forum

I've got a growing problem with my outq: hub.disroot.org apparently went down more than 14 days ago (before september, 25th) and currently my queue for that hub contains 62491 elements, priority 20. During this time the space claimed by outq (including logs in ibdata1) has grown and currently the whole database (including ibdata1) ist using 291 GB(!) and continuing to grow. I've tried to delete as much space as possible, yet the disk space is filling up - at this rate the database will claim all the available space soon.

So the only option I see right now is deleting all elements in the outq for hub.disroot.org - is there any tool or existing sql query for that?

show all 17 comments

Mon, 12 Oct 2020 19:09:35 +0200

Mark Nowiasz

buckaroo@hub.netzgemeinde.eu

Would it be safe to do somthing like delete from outq where outq_posturl like '%hub.disroot%';?

Mon, 12 Oct 2020 19:40:33 +0200

@Mark Nowiasz

Would it be safe to do somthing like delete from outq where outq_posturl like '%hub.disroot%';?

Yes.

Mon, 12 Oct 2020 21:15:33 +0200

Mark Nowiasz

buckaroo@hub.netzgemeinde.eu

@Max Kostikov Thanks, this worked, although it required an OPTIMIZE TABLE outq afterwards to reclaim the disk space. To otpimize idbdata1 - since I'm using files per table it's probable filled up with redo/undo log - I'd have to mysqldump all databases, clean mysql and reimport them - a quite costly operation timewise.

Which brings me to the next question: What shall we/I do if hub.disroot.org won't come up again (which is not unlikely, they've been down for nearly three weeks now)? Is there a config options to automatically dismiss all new entries targeting this hub? Otherwise it would be tedious to delete/optimize the table forever.. and there's no point in cleaning up ibdata1 before the disroot issues is resolved.

Mon, 12 Oct 2020 21:47:52 +0200

El Dorado

mike@z.macgirvin.com

There's also the undocumented:

https://hub.netzgemeinde.eu/admin/queue?expert=1

As for DB space in general I've done some work in Zap to move all the profile photos to the filesystem. I've designed it so that this can be pruned and old entries expired without breaking anything. My own DB is sitting at about 1.5G and most of that is items (posts) that are being regularly expired - so overall DB growth has nearly flatlined. Mentioning it because this is working well and is probably stable enough to port to Hubzilla.

Mon, 12 Oct 2020 21:56:43 +0200

Mark Nowiasz

buckaroo@hub.netzgemeinde.eu

Nice, but what exactly do those two icons right to the queue do? One probably deletes the content of the queue (which one?)? I didn't want to toy with an undocumented feature, so I thought I better ask before ?

Mon, 12 Oct 2020 22:16:59 +0200

What mysql features are you using? Since you talk about ibdataX I assume Innodb is used. Once you delete items from a table it will not clean up space. It simply sees it as additional space for future storage. See it as a self growing tables space.

To fix this you will need to dump the full file, delete the database and make sure you have enough time to reindex everything.

When you want to run a query the alway use a select * from outq where outq_posturl like '%hub.disroot%'; to see if that is all you want to delete. Make sure you have a backup too :-)

Mon, 12 Oct 2020 22:20:03 +0200

El Dorado

mike@z.macgirvin.com

That's the reason it's an 'expert' feature. The 'x' marks the site as dead and the trashcan removes the queue items. This is explained if you're on a platform that permits mouse hovertips. The problem I found is that if you mark the site dead, it doesn't show up in the queue manager any more so you can't go back in and empty the queue after you do this. So that's why the feature remains hidden.

Mon, 12 Oct 2020 23:11:25 +0200

@Mike Macgirvin Sometimes Hubzilla looks like a magician's hat, in which he hides a rabbit or a dove :-)

Mon, 12 Oct 2020 23:14:19 +0200

@Mike Macgirvin

Maybe the expert mode could be standard view and show also dead sites and icon to make them live again. Now there is no way to know some site is marked dead and messages are not delivering. It just disappears, nothing in delivery reports.
Maybe the Connection list could show contacts on dead sites or make them "Archived" state

update site set site_dead = 0 where site_dead = 1;

I had 391 dead sites including pretty active mastodon.social sunbeam.city ...

Mon, 12 Oct 2020 23:26:08 +0200

El Dorado

mike@z.macgirvin.com

Sites are flagged with a timestamp if they're unreachable. If they remain unreachable for a month, we mark them "dead" and no longer deliver stuff to them. We keep the site entries and they are allowed to re-incarnate because we've had sites in the past which came back a few months later.

Mon, 12 Oct 2020 23:26:43 +0200

In fact, you can receive posts from people on dead sites (at least on some protocols it is not checked on incoming messages) but silently not sending out, which makes things weird. Just listing dead in delivery reports would help.

Mon, 12 Oct 2020 23:53:13 +0200

El Dorado

mike@z.macgirvin.com

I may have overlooked clearing site_dead when using the ActivityPub protocol and seeing something new from a site that was already marked dead. That's not too difficult to fix. And I agree this should probably be listed in the delivery report and possibly the queue report, although after three days there shouldn't be anything left in the queue for these sites - so that would only be meaningful for a small slice of time.

Tue, 13 Oct 2020 10:19:18 +0200 zuletzt bearbeitet: Tue, 13 Oct 2020 11:01:39 +0200

~~Could you repost Max' postings? For any reason they did not arrive at my side.~~
No! Having a look from/with/through the browser of my desktop computer they are there.

Fri, 16 Oct 2020 19:50:54 +0200

Hmm. Marking hub.disroot.org as dead doesn't seem to work. I did that 5 times now. The queue entries are piling up in outq with prio 20 again after some time - constantly growing. Also, the prio never changes. It stays on 20 for "/zot". Only the "/post" entries have a prio of 580 now.

Sun, 18 Oct 2020 09:22:53 +0200

Mark Nowiasz

buckaroo@hub.netzgemeinde.eu

@Steffen K9 ? Same here. I had to perform a update site set site_dead=1 where site_url like "%ub.disroot%"; to mark the site as dead.

Sun, 18 Oct 2020 12:22:50 +0200

@Mark Nowiasz Thank you.

Tue, 20 Oct 2020 00:12:29 +0200

El Dorado

mike@z.macgirvin.com

This is fixed in zap (etc.). I'm holding off any more non-critical Hubzilla fixes until after 5.0.