Rocksolid Light

News from da outaworlds

mail  files  register  groups  login

Message-ID:  

BOFH excuse #363: Out of cards on drive D:

rocksolid / Rocksolid Nodes Announce / Re: rslight database maintenance automated

SubjectAuthor
* rslight database maintenance automatedRetro Guy
`* Re: rslight database maintenance automatedSugarBug
 `- Re: rslight database maintenance automatedRetro Guy

1
Subject: rslight database maintenance automated
From: Retro Guy
Newsgroups: rocksolid.nodes.announce
Organization: RetroBBS
Date: Wed, 4 Oct 2023 14:18 UTC
Path: eternal-september.org!news.eternal-september.org!news.quux.org!newsfeed.endofthelinebbs.com!i2pn.org!i2pn2.org!.POSTED!not-for-mail
From: retro.guy@rocksolidbbs.com (Retro Guy)
Newsgroups: rocksolid.nodes.announce
Subject: rslight database maintenance automated
Date: Wed, 4 Oct 2023 14:18:33 +0000
Organization: RetroBBS
Message-ID: <05aa1ce92e8bac6001b76802f826528a@rocksolidbbs.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Injection-Info: i2pn2.org;
logging-data="272559"; mail-complaints-to="usenet@i2pn2.org";
posting-account="qk6pvs/sIyKYNRNFdjVS+ghlZZkCUq7cWs+7p7kaLpU";
User-Agent: Rocksolid Light 0.9.1
X-Face: .&YR-G(w(DZ$$,}%k=]*5*!p'=(anr"IT`wZG'2VWdfl\r)l[42u7JH`n(JUQ*e5*A|XCDf
?&\X&uwkl38"CYX3O8m}C8E4p'%N$2#kSTVzx{Ly|DjLT\Vk7NE}NQ(VC$Yq]i:7|z[.9iv^g>*8_B
H0=hZt'[%)4kG|
X-Rslight-Site: $2y$10$J6ZfRiE9olQZCRuGl.1pfuo1Kx7VY7yneBB8f6bUHf1JMHWDL8JBO
X-Rslight-Posting-User: 7f2224730128256930309c9186f6203084896743
X-Spam-Checker-Version: SpamAssassin 4.0.0 (2022-12-13) on novalink.us
View all headers

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

Subject: Re: rslight database maintenance automated
From: SugarBug
Newsgroups: rocksolid.nodes.announce
Organization: sybershock.com
Date: Wed, 4 Oct 2023 17:13 UTC
References: 1
Path: eternal-september.org!news.eternal-september.org!i2pn.org!rocksolid2!.POSTED!not-for-mail
From: 3883@sugar.bug (SugarBug)
Newsgroups: rocksolid.nodes.announce
Subject: Re: rslight database maintenance automated
Date: Wed, 4 Oct 2023 12:13:01 -0500
Organization: sybershock.com
Message-ID: <d85f0a21e8221e0893a2e263af820dda$1@sybershock.com>
References: <05aa1ce92e8bac6001b76802f826528a@rocksolidbbs.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: novabbs.org;
logging-data="1286520"; mail-complaints-to="usenet@novabbs.org";
posting-account="TzG3Hl99Aa0Fgb506WreKRgRTO2mG9+aGjVDifyfNqo";
View all headers

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

Subject: Re: rslight database maintenance automated
From: Retro Guy
Newsgroups: rocksolid.nodes.announce
Organization: novaBBS
Date: Wed, 4 Oct 2023 21:07 UTC
References: 1 2
Path: eternal-september.org!news.eternal-september.org!i2pn.org!i2pn2.org!.POSTED!not-for-mail
From: retroguy@novabbs.com (Retro Guy)
Newsgroups: rocksolid.nodes.announce
Subject: Re: rslight database maintenance automated
Date: Wed, 4 Oct 2023 14:07:08 -0700
Organization: novaBBS
Message-ID: <20231004140708.44952c5ea7df8ec5af29fa6e@novabbs.com>
References: <05aa1ce92e8bac6001b76802f826528a@rocksolidbbs.com>
<d85f0a21e8221e0893a2e263af820dda$1@sybershock.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: i2pn2.org;
logging-data="328476"; mail-complaints-to="usenet@i2pn2.org";
posting-account="t+lO0yBNO1zGxasPvGSZV1BRu71QKx+JE37DnW+83jQ";
X-Face: .&YR-G(w(DZ$$,}%k=]*5*!p'=(anr"IT`wZG'2VWdfl\r)l[42u7JH`n(JUQ*e5*A|XCDf ?&\X&uwkl38"CYX3O8m}C8E4p'%N$2#kSTVzx{Ly|DjLT\Vk7NE}NQ(VC$Yq]i:7|z[.9iv^g>*8_B
H0=hZt'[%)4kG|
X-Newsreader: Sylpheed 3.8.0beta1 (GTK+ 2.24.33; x86_64-pc-linux-gnu)
View all headers

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

1

rocksolid light 0.9.8
clearnet tor