Re: [Sqlgrey-users] Performance with Postfix and SQLGrey

[Lionel B. <lio...@bo...>]

Dave Strickler wrote the following on 12.02.2007 18:21 :

Please don't reply to old posts when creating new subjects, it hides
your post in threaded views and make following discussions difficult.

> I am seeing, under the load of about 1,000 Postfix connections per
> minute, SQLGrey slow down. Note the times here in the log below. The
> email is seen by Postfix at 10:44, but not finished and ready to send
> for about 30 seconds. I have about 350 threads open on this server,
> and they are typically all in play. The LA is fairly low (3 to 4).
>  
> Is this SQLGrey that's slowing things down? It has to spawn a lot of
> sessions (see the stats above). Or is it our Postgres DB that's
> slowing this down?
>  
> Feb 12 12:10:44 ib17 postfix/smtpd[14833]: 5E9FE1D3A481:
> client={removed}[{removed}.99]
> Feb 12 12:10:44 ib17 postfix/cleanup[15475]: 5E9FE1D3A481:
> message-id=<7FA493658BF13544BCDD9CF41F9BFC01BB5068@{removed}.com
> <mailto:7FA493658BF13544BCDD9CF41F9BFC01BB5068@%7Bremoved%7D.com>>
> Feb 12 12:10:45 ib17 postfix/qmgr[14681]: 5E9FE1D3A481: from=jsmith
> <mailto:from=jsmith@%7Bremoved%7D.com>@{removed}
> <mailto:from=lsegesvary@%7Bremoved%7D>.com, size=34936, nrcpt=2 (queue
> active)
> Feb 12 12:11:13 ib17 postfix/smtp[16934]: 5E9FE1D3A481:
> to=<kwilson@{removed}.com <mailto:kwilson@%7Bremoved%7D.com>>,
> relay={removed}[{removed}.27], delay=34, status=sent (250 Ok: queued
> as 27A6A5FB6723)

To know which of PostgreSQL or SQLgrey is responsible, you should
monitor at least one. SQLgrey can't really measure the time spent from
receiving a request to the actual response because of the design of
Net::Server::Multiplex, so your best bet is to monitor PostgreSQL.

You can log the queries which take more than 100ms to be completed and
you'll have more informations, like:
- is it always the same type of query (should you try to optimize your
PostgreSQL configuration to optimize this query)?
- does it occur only during short periods (look for other processes
hammering the disk and competing with PostgreSQL for data access or
other databases being used)?

More general monitoring advices: use top and vmstat if you know when
your system becomes unresponsive to get a better view of what's going on.

Oh, and if you don't do it yet: use pg-autovacuum. PostgreSQL needs to
run VACUUM/ANALYZE on busy tables to maintain good performance.

Lionel.