Mike,
I'm basically doing most all of what you suggest. I'm going
to start over with a new build, go to 8.0.3 on postgres and
probably 2.0.4-svn.
We did find a duplex mis-match between the dbmail server and
the database. So that could be part of the issue.
I don't think the Xeon's that I have are HT. The memory is
set up as (2) 512M chips one in each bank.
--
David A. Niblett | email: [EMAIL PROTECTED]
Network Administrator | Phone: (352) 334-3400
Gainesville Regional Utilities | Web: http://www.gru.net/
-----Original Message-----
From: M. J. [Mike] O'Brien [mailto:[EMAIL PROTECTED]
Sent: Saturday, July 16, 2005 4:44 AM
To: DBMail mailinglist
Subject: [Dbmail] DBMail + PostgreSQL Problem
Hello David:
* I see a couple of issues. Would you consider a default postgresql.conf
with minor tweaks?
Like:
shared_buffers = 600
sort_mem = 1024 (not much higher than 8192 as you seem to have many users)
* Your comments about orphan message hunting on the multiple joins
(dbmail-util) makes me wonder if all your indices are intact.
I am running a big server on 7.3.6 and smaller setups on 8.0.3 The
former
(7.3.6) is on Intel, not Xeons but a pair of 1.4G Tualatins -
3gig mem but mail doesn't get it all cuz machine does other mem-intensive
admin stuff too.
The server handles the "alerts" from hundreds of monitored servers on
Micromuse Netcool and other Entmgt monitors. Some Admins literally kick
the
crap out of it with overabundance of senseless yellow alerts :o( they've
been asked a billion times to drop. Hundreds of accounts get thousands of
little messages and hundreds of big ones every hour. Never been a problem
on
psql. Thing is bullet-proof-stable. Cron runs minor dbmail-util every 40
minutes and full dbmail-util every 6hrs. It scoots through these pretty
quick but I agree, the join on the message orphans is a little rough --
but
at worst a few extra minutes, not days.
Questions:
a) In your para(1) you say Dbmail 'master daemon dies' ... which do you
mean
by that (lmtpd, imapd. pop3d ?) (Is PostgreSQL denying a connection
because
it is out of memory, I wonder?)
b) ...have you traced the connect status between the two servers -- is it
consistant? permissions good?.
Troubleshoooting ideas:
1) After 'hiding' your ambitious conf and using a more tame, quasi-default
conf, list your indices and see if they are all there, none missing. (I
pasted a full set below so you can compare to a working server.)
2) You likely did this: check your psql error log and see what if anything
it says the problem is during the time of the dbmail-util run. Also
syslog?
Also see if you can run 'systat -vmstat' to get a picture of phys and
swap
mem status while things are haywire (if you have any cpu left at all
(. ) Also, if you like, go to trace level 5 for lmtpd and see how it
is
doing with its database connects (are they consistant?) - it might tell
something you didn't know.
3) Another suggestion for database checking: Using the latest DbMail 2.0.4
SVN ~/dbmail_snapshot/sql/postgresql/create_tables.sql, create another
(empty) database on your server. Call it dbmail2 or something; compare the
schema; and run some tests against it by changing dbmail.conf on the
database server. (If you don't have dbmail installed on the database
server, it would be a good idea to do so. That way you can run your tools
on
the DB server instead of across the LAN)
4) Consider a fresh dev rebuild using PGSQL 8+ ... it's quite nice* and a
good excuse for a step by step rebuild of your system, using more
conservative aproaches to configs until all is up and running well. *The
nice stuff in 8+ includes Savepoints, Improved Buffer Management,
Checkpoint, Vacuum, Point-In-Time Recovery which remedy your last point:
"I'd also be very interested in knowing a better way to... etc"
5) Hardware: (>DB Server - Dual Xeon 3.06GHz, 1GB RAM, SCSI RAID Ultra320
drives.<) You might not have enough memory for your aggressive
configuration. Gottabe 'HT Xeons'. (Is PosgreSQL threading across all 4
CPUs? Are any threads going linear on account of a broken network
connection
or other issue -- this could eat memory and push into swap and even cause
broken sequences.)
Memory: Are there two mem banks on the board, one for each CPU? When only
one bank is used, that should be CPU0 for most boards. Check manual. I
wonder about a memory issue with mismatched Dual Rank x4/x8 400mhz memory
or
wrong memory for the board. 'HT Xeon' boards can be picky. Run a mem test
to
make certain the mem sticks are paired. (If you are running a single stick
of 1 gig it seldom goes in the first slot. Check ur manual.) What can
happen
say with a pair of 512s mismatched on a HT Xeon board is that Linux will
manage memory well until it must *reuse* phys memory past the first 512
...
it can then have troubles... with bizarre symptoms.
PSQL Indices
dbmail_acl_pkey
dbmail_aliases_alias_idx
dbmail_aliases_alias_low_idx
dbmail_aliases_pkey
dbmail_auto_notifications_pkey
dbmail_auto_replies_pkey
dbmail_idx_ipnumber
dbmail_idx_since
dbmail_mailboxes_name_idx
dbmail_mailboxes_owner_idx
dbmail_mailboxes_owner_name_idx
dbmail_mailboxes_pkey
dbmail_messageblks_physmessage_idx
dbmail_messageblks_physmessage_is_header_idx
dbmail_messageblks_pkey
dbmail_messages_7
dbmail_messages_8
dbmail_messages_mailbox_idx
dbmail_messages_physmessage_idx
dbmail_messages_pkey
dbmail_messages_seen_flag_idx
dbmail_messages_status_idx dbmail_messages_status_notdeleted_idx
dbmail_messages_unique_id_idx
dbmail_pbsp_pkey
dbmail_physmessage_pkey
dbmail_subscription_pkey
dbmail_users_name_idx
dbmail_users_pkey
Sequences:
dbmail_alias_idnr_seq
dbmail_mailbox_idnr_seq
dbmail_message_idnr_seq
dbmail_messageblk_idnr_seq
dbmail_physmessage_id_seq
dbmail_seq_pbsp_id
dbmail_user_idnr_seq
Hope this helps...
best...
Mike
----- Original Message -----
From: "Niblett, David A" <[EMAIL PROTECTED]>
To: <dbmail@dbmail.org>
Sent: Friday, July 15, 2005 9:07 AM
Subject: [Dbmail] DBMail + PostgreSQL Problem
Hello all,
I'm in serious need of help here. I'm about at my whits
end of dealing with dbmail and getting it to work. I've
had a couple of database crashes now and found things like
if I run dbmail-util my DB process load skyrockets and
the server becomes unusable.
I'm hoping that maybe I'm just incapable of tuning PostgreSQL for
performance. At this point I'd like to know if there is anyone out
there that has experience with dbmail-2.0.4 on PostgreSQL-7.4.7 in a
moderately large table size (9-10GB). We are very interested in paying
for some help in the form of consulting if need be. At this point if
we can't work out the bugs then we are going to scrap the entire thing
and go back to our simple Windows based NTMail system.
Some items that seems to happen are:
1) If I stop/reload the postgres database (normal nice stop which
should allow all transactions to finish) the dbmail master daemon dies
and we seem to get a lot of unconnected messages suddenly in the
database. We see these in the form of no user, no subject messages in
users mailboxes.
2) When dbmail-util runs, the process load just sky rockets on the db
server. It seems to be related to the large join done for finding the
messageblks that are not connected.
3) When we vacuum the database the process load screams sky high (like
160+) on the server. The last time we did this it took 4 days for the
vacuum to finish. We believe we have this fixed by using the
pg_autovacuum daemon.
Our set up is:
DB Server - Dual Xeon 3.06GHz, 1GB RAM, SCSI RAID Ultra320 drives.
DBMail Server - P4 3.06GHz, 1GB RAM, SATA drives.
Running: dbmail-2.0.4, postgresql-7.4.7 on Gentoo with Linux 2.6
kernel
As far as tweaking goes, I've set sort_mem and vacuum_mem on postgres
to each 100M (102400) to help try and stop swaps. I've also increased
the shared memory limit from 32M to 100M.
I'd also be very interested in knowing a better way to limit the
database transaction logs such that should I suffer a crash I'm not
having to dump the database and restore. I never really had this
issue with MSSQL. I expect to lose things like the message that is
being delivered, but not corrupt the dbmail_users table and everything
else.
HELP... TIA
--
David A. Niblett | email: [EMAIL PROTECTED]
Network Administrator | Phone: (352) 334-3400
Gainesville Regional Utilities | Web: http://www.gru.net/
_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
