I'm now running general database maintenance daily (with expire) for each group.

This has been something that would have been manually done previously, and not often. This should clean up group database files, defragment them, reduce disk usage and speed up requests.

Group article databases (sqlite) get fragmented and grow in size when articles are deleted (expired, nocem or whatever). Space is not recovered until maintenance is run, so we now do that daily. This also rebuilds the indexes which should make searching in the db (happens constantly) faster.

--
Retro Guy

On Wed, 4 Oct 2023 14:18:33 +0000
retro.guy@rocksolidbbs.com (Retro Guy) wrote:

> I'm now running general database maintenance daily (with expire) for
> each group.
>
> This has been something that would have been manually done
> previously, and not often. This should clean up group database files,
> defragment them, reduce disk usage and speed up requests.
>
> Group article databases (sqlite) get fragmented and grow in size when
> articles are deleted (expired, nocem or whatever). Space is not
> recovered until maintenance is run, so we now do that daily. This
> also rebuilds the indexes which should make searching in the db
> (happens constantly) faster.

How much faster is rslight when using sqlite database rather than
tradspool? Is it more than 2x in your experience? A ballpark figure is
good enough.

I'm thinking maybe my public peer should use database and my hidden
mirror peers should use tradspool, since nobody will be accessing the
hidden ones via web or nntp. That way my gopher/sftp/ssh access of
article files remains intact.

Also it is much faster for me to queue up 'grep -r' statements than it
is to type in sqlite queries, as I like to grep through the spool data.

--
__|__|__|__ 3883@sugar.bug
__|__|__|__ https://sybershock.com
__|__|__|__ news://alt.sources.crypto
| | | #CipherTag #WaffleTag #Cryptologer

On Wed, 4 Oct 2023 12:13:01 -0500
SugarBug <3883@sugar.bug> wrote:

> On Wed, 4 Oct 2023 14:18:33 +0000
> retro.guy@rocksolidbbs.com (Retro Guy) wrote:
>
> > I'm now running general database maintenance daily (with expire) for
> > each group.
> >
> > This has been something that would have been manually done
> > previously, and not often. This should clean up group database files,
> > defragment them, reduce disk usage and speed up requests.
> >
> > Group article databases (sqlite) get fragmented and grow in size when
> > articles are deleted (expired, nocem or whatever). Space is not
> > recovered until maintenance is run, so we now do that daily. This
> > also rebuilds the indexes which should make searching in the db
> > (happens constantly) faster.
>
> How much faster is rslight when using sqlite database rather than
> tradspool? Is it more than 2x in your experience? A ballpark figure is
> good enough.

I'm not sure that it is faster. It would depend on your filesystem, etc.
Just like with inn, all these files (articles) take up a lot of inodes, so
a database resolves that issue (or create your filesystem with a lot of
inodes).

Overview access is faster with sqlite, as in tradspool all the overview
data is in one big flat file. When you want to search for something in there,
you need to read through the file until you find it. sqlite is faster for
this specific need.

Also, sqlite systems will have the added features of searching article bodies,
and also providing snippets of each post in overboard, search etc.

> I'm thinking maybe my public peer should use database and my hidden
> mirror peers should use tradspool, since nobody will be accessing the
> hidden ones via web or nntp. That way my gopher/sftp/ssh access of
> article files remains intact.

That might make sense. Then you provide all features on your public peer,
but maintain the ability to work directly with the spool on your internal
servers.

> Also it is much faster for me to queue up 'grep -r' statements than it
> is to type in sqlite queries, as I like to grep through the spool data.

grep is your friend :)

--
Retro Guy