Skip site navigation (1) Skip section navigation (2)

Re: [dspam-users] Postgres vs. MySQL

From: "Casey Allen Shobe" <cshobe(at)osss(dot)net>
To: "Clifton Royston" <cliftonr(at)tikitechnologies(dot)com>
Cc: "Evilio del Rio" <edelrio(at)cmima(dot)csic(dot)es>,dspam-users(at)networkdweebs(dot)com, pgsql-performance(at)postgresql(dot)org
Subject: Re: [dspam-users] Postgres vs. MySQL
Date: 2004-11-27 02:11:13
Message-ID: 35118.206.63.123.15.1101521473.squirrel@206.63.123.15 (view raw or flat)
Thread:
Lists: pgsql-performance
I posted about this a couple days ago on dspam-dev...

I am using DSpam with PostgreSQL, and like you discovered the horrible
performance.  The reason is because the default PostgreSQL query planner
settings determine that a sequence scan will be more efficient than an
index scan, which is wrong.  To correct this behavior, adjust the query
planner settings for the appropriate table/column with this command:

alter table "dspam_token_data" alter "token" set statistics 200; analyze;

Let me know if it help you.  It worked wonders for me.

-- 
Casey Allen Shobe
cshobe(at)osss(dot)net

On Fri, November 26, 2004 12:35 pm, Clifton Royston said:
> On Wed, Nov 24, 2004 at 02:14:18PM +0100, Evilio del Rio wrote:
>> I have installed the dspam filter
>> (http://www.nuclearelephant.com/projects/dspam) on our mail server
>> (RedHat 7.3 Linux with sendmail 8.13 and procmail). I have ~300 users
>> with a quite low traffic of 4000 messages/day. So it's a quite common
>> platform/environment, nothing spectacular.
>>
>> First time(s) I tried the Postgres interface that was already installed
>> for other applications.  Whenever I begin to train and/or filter
>> messages throug dspam the performance is incredibly bad. First messages
>> are ok but soon the filter time begins to increase to about 30 seconds
>> or more!
>>
>> ...so I looked for some optimization both for the linux  kernel and the
>> postgres server.  Nothing has work for me. I always have the same
>> behavior. For isolation purposes I started using another server just to
>> hold the dspam database and nothing else. No matter what I do: postgres
>> gets slower and slower with each new message fed or filtered.
>
> I know *somewhere* I recently read something indicating a critical
> configuration change for DSPAM + Postgres, but don't think I've seen it
> mentioned on this list.  Possibly it is in the UPGRADING instructions
> for 3.2.1, or in a README file there.  At any rate, it mentioned that
> it was essential to make some change to the table layout used by previous
> versions of DSPAM, and then Postgres would run many times faster.
>
> Unfortunately I no longer have 3.2.1 installed on my system, so I can't
> tell you if it was in there or somewhere else.
>
>   -- Clifton
>
> --
>           Clifton Royston  --  cliftonr(at)tikitechnologies(dot)com
>          Tiki Technologies Lead Programmer/Software Architect
> Did you ever fly a kite in bed?  Did you ever walk with ten cats on your
> head?
>   Did you ever milk this kind of cow?  Well we can do it.  We know how.
> If you never did, you should.  These things are fun, and fun is good.
>                                                                  -- Dr.
> Seuss
>


In response to

Responses

pgsql-performance by date

Next:From: Lionel BoutonDate: 2004-11-27 10:14:30
Subject: Re: [dspam-users] Postgres vs. MySQL
Previous:From: Rod TaylorDate: 2004-11-26 21:48:40
Subject: Re: time to stop tuning?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group