Message-ID: <42EC612A.1080908@arbash-meinel.com>
Date: Sun, 31 Jul 2005 00:27:06 -0500
From: John Arbash Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050404)
MIME-Version: 1.0
To: Matthew Schumacher <matt.s@aptalaska.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problems testing with Spamassassin 3.1.0
References: <42E80C28.2040608@aptalaska.net>
	<1122532023.25574.1.camel@localhost.localdomain>
	<42E9749F.6000709@aptalaska.net>
	<1122616211.5691.340.camel@lamb.mcmillan.net.nz>
	<42EA6966.8090402@aptalaska.net>
	<1122668371.5691.352.camel@lamb.mcmillan.net.nz>
	<19405.1122670917@sss.pgh.pa.us> <42EAA410.9010700@aptalaska.net>
In-Reply-To: <42EAA410.9010700@aptalaska.net>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigFEA353E5A7E31AF4F37B27E4"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigFEA353E5A7E31AF4F37B27E4
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Matthew Schumacher wrote:

>All it's doing is trying the update before the insert to get around the
>problem of not knowing which is needed.  With only 2-3 of the queries
>implemented I'm already back to running about the same speed as the
>original SA proc that is going to ship with SA 3.1.0.
>
>All of the queries are using indexes so at this point I'm pretty
>convinced that the biggest problem is the sheer number of queries
>required to run this proc 200 times for each email (once for each token).
>
>I don't see anything that could be done to make this much faster on the
>postgres end, it's looking like the solution is going to involve cutting
>down the number of queries some how.
>
>One thing that is still very puzzling to me is why this runs so much
>slower when I put the data.sql in a transaction.  Obviously transactions
>are acting different when you call a proc a zillion times vs an insert
>query.
>  
>
Well, I played with adding a COMMIT;BEGIN; statement to your exact test
every 1000 lines. And this is what I got:

Unmodified:
real    17m53.587s
user    0m6.204s
sys     0m3.556s

With BEGIN/COMMIT:
real    1m53.466s
user    0m5.203s
sys     0m3.211s

So I see the potential for improvement almost 10 fold by switching to
transactions. I played with the perl script (and re-implemented it in
python), and for the same data as the perl script, using COPY instead of
INSERT INTO means 5s instead of 33s.

I also played around with adding VACUUM ANALYZE every 10 COMMITS, which
brings the speed to:

real    1m41.258s
user    0m5.394s
sys     0m3.212s

And doing VACUUM ANALYZE every 5 COMMITS makes it:
real    1m46.403s
user    0m5.597s
sys     0m3.244s

I'm assuming the slowdown is because of the extra time spent vacuuming.
Overall performance might still be improving, since you wouldn't
actually be inserting all 100k rows at once.


Just to complete the reference, the perl version runs as:
10:44:02 -- START
10:44:35 -- AFTER TEMP LOAD : loaded 120596 records
10:44:39 -- AFTER bayes_token INSERT : inserted 49359 new records into
bayes_token
10:44:41 -- AFTER bayes_vars UPDATE : updated 1 records
10:46:42 -- AFTER bayes_token UPDATE : updated 47537 records
DONE

My python version runs as:
00:22:54 -- START
00:23:00 -- AFTER TEMP LOAD : loaded 120596 records
00:23:03 -- AFTER bayes_token INSERT : inserted 49359 new records into
bayes_token
00:23:06 -- AFTER bayes_vars UPDATE : updated 1 records
00:25:04 -- AFTER bayes_token UPDATE : updated 47537 records
DONE

The python is effectively just a port of the perl code (with many lines
virtually unchanged), and really the only performance difference is that
the initial data load is much faster with a COPY.

This is all run on Ubuntu, with postgres 7.4.7, and a completely
unchanged postgresql.conf. (But the machine is a dual P4 2.4GHz, with
3GB of RAM).

John
=:->

>Anyway, if anyone else has any ideas I'm all ears, but at this point
>it's looking like raw query speed is needed for this app and while I
>don't care for mysql as a database, it does have the speed going for it.
>
>schu
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>  
>


--------------enigFEA353E5A7E31AF4F37B27E4
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFC7GEsJdeBCYSNAAMRAun5AKCqn5Y8W4IzbkEpE+sDSjnd1txoIgCg16zP
SwD0k//d6yrTIoy4RThIh5I=
=hEBe
-----END PGP SIGNATURE-----

--------------enigFEA353E5A7E31AF4F37B27E4--