Mailing List Archive

http://bugzilla.spamassassin.org/show_bug.cgi?id=3202





------- Additional Comments From parkerm@pobox.com 2004-03-21 23:06 -------
Subject: Re: Replace DB_File with SQLite?

On Sun, Mar 21, 2004 at 11:00:04PM -0800, bugzilla-daemon@bugzilla.spamassassin.org wrote:
> > I don't know if it has better I/O characteristics than DB_File, but it
> > would let us do SQL all around, and use DBI/DBD, which is a goal we've
> > had for a while.
>
> It's not a bad goal if it results in simpler and easier to maintain
> code. However, we should compare its performance against QDBM and
> perhaps TDB, though, not DB_File. Michael's performance experiments
> with QDBM seemed pretty promising.

Every single performance test I've done with SQLite I've stopped long
before it finished because I didn't want to wait that long. My last
test I stopped at about 5% done and it had already taken around 3
times longer than it was taking to do a complete run with DB_File.

Michael





------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.

http://bugzilla.spamassassin.org/show_bug.cgi?id=3202





------- Additional Comments From lan@ac-sw.com 2004-03-21 23:38 -------
Subject: Re: Replace DB_File with SQLite?

> Every single performance test I've done with SQLite I've stopped long
> before it finished because I didn't want to wait that long. My last
> test I stopped at about 5% done and it had already taken around 3
> times longer than it was taking to do a complete run with DB_File.

Did you optimize query for sqlite ? Create indexes and so on ?
Sometimes logic of operation with data needed to be changed to use advantages
of database.





------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.

http://bugzilla.spamassassin.org/show_bug.cgi?id=3202





------- Additional Comments From parkerm@pobox.com 2004-03-21 23:45 -------
Subject: Re: Replace DB_File with SQLite?

On Sun, Mar 21, 2004 at 11:38:09PM -0800, bugzilla-daemon@bugzilla.spamassassin.org wrote:
>
> > Every single performance test I've done with SQLite I've stopped long
> > before it finished because I didn't want to wait that long. My last
> > test I stopped at about 5% done and it had already taken around 3
> > times longer than it was taking to do a complete run with DB_File.
>
> Did you optimize query for sqlite ? Create indexes and so on ?
> Sometimes logic of operation with data needed to be changed to use advantages
> of database.
>

Bayes queries are fairly optimized and work very well under other
database setups. However, what I had not done is set is
PRAGMA default_synchronous = OFF and upping the default_cache_size.
I've done this now and it seems to be behaving much better. It does
mean that we lose a little peace of mind as far as making sure data
gets all the way to the disk. The SQLite docs say you can get a 50
times speed up turning it off.

Interesting however, is that halfway through my mass-check test it's
now slowed to a crawl.

Michael





------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.

http://bugzilla.spamassassin.org/show_bug.cgi?id=3202





------- Additional Comments From felicity@kluge.net 2004-03-22 07:55 -------
Subject: Re: Replace DB_File with SQLite?

On Sun, Mar 21, 2004 at 11:45:24PM -0800, bugzilla-daemon@bugzilla.spamassassin.org wrote:
> I've done this now and it seems to be behaving much better. It does
> mean that we lose a little peace of mind as far as making sure data
> gets all the way to the disk. The SQLite docs say you can get a 50
> times speed up turning it off.

Yeah, but we don't get that now with DB_File, so it's not a loss on
that end.





------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.

http://bugzilla.spamassassin.org/show_bug.cgi?id=3202





------- Additional Comments From jm@jmason.org 2004-03-22 10:21 -------
Subject: Re: Replace DB_File with SQLite?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


>On Sun, Mar 21, 2004 at 11:45:24PM -0800, bugzilla-daemon@bugzilla.spamassassin.org wrote:
>> I've done this now and it seems to be behaving much better. It does
>> mean that we lose a little peace of mind as far as making sure data
>> gets all the way to the disk. The SQLite docs say you can get a 50
>> times speed up turning it off.
>
>Yeah, but we don't get that now with DB_File, so it's not a loss on
>that end.

I think we *do*, actually -- strace DB_File some time, there's definitely
some fsync()ing going on. as far as I can tell, DB_File does attempt
to avoid corruption as much as possible.

- --j.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)
Comment: Exmh CVS

iD8DBQFAXy6AQTcbUG5Y7woRAgnoAJ9aM+0fgBpZejhTkUWsdL09iDfWWwCdEfbO
sb2PBNeu5Bd5S8moMU4TGPc=
=iiAI
-----END PGP SIGNATURE-----





------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.

http://bugzilla.spamassassin.org/show_bug.cgi?id=3202





------- Additional Comments From parkerm@pobox.com 2004-03-22 11:23 -------
Subject: Re: Replace DB_File with SQLite?

On Sun, Mar 21, 2004 at 11:45:23PM -0800, bugzilla-daemon@bugzilla.spamassassin.org wrote:
>
> Interesting however, is that halfway through my mass-check test it's
> now slowed to a crawl.
>

Because that's when we finally auto-learned 200 of each.

My mass-check test shows that SQLite is a little more than 5 times
slower than DB_File. It's possible that additional tuning and perhaps
modifying how we access SQLite could help speed things up.

Michael





------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.

http://bugzilla.spamassassin.org/show_bug.cgi?id=3202

felicity@kluge.net changed:

What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |WORKSFORME



------- Additional Comments From felicity@kluge.net 2004-03-29 21:35 -------
unless someone has an issue, I'm closing the ticket as wfm.

it looks like without tuning, sqlite is a bunch slower than db_file, and I don't think that's going to be a
viable option for most of our users. the people who would do sqlite tuning can use sql just as easily.



------- You are receiving this mail because: -------
You are the assignee for the bug, or are watching the assignee.