Very interesting, then I'll probe some more... Our database system is on a Dual 3GHz Hyperthreaded Compaq G3 (P4 Xeon) system with 1GB of RAM with Ultra320 drives configured in a SCSI RAID 0+1 (4 36G drives, 2 stripe sets, mirrored) I have set my shared memory space at 512M, I would like to go to more, but PostgreSQL doesn't seem to do anything with the extra RAM. We are running v8.0.3. The OS is Gentoo from scratch, with the database on a JFS partition.
We offer POP and IMAP, so we are holding onto more Email for customers. We have 4 servers that run POP/IMAP/SMTP to connect to the DB, each has about 20 connections for a total of ~240 total to the DB. My load is usually around 0.5 - 1.5 most of the time. When I had slony engaged and things we going well, it would be about 2.7-3.7. In checking the number of messages added to the DB, I'd say it seems to be around 25k a day. On average I seem to be deleting about 45k with the daily purge. I use pg_autovacuum for my other databases (very small, low access) and nightly use dbmail-util's optimize to do the vacuum. That appears to take about 4 hours to complete, which seems to be too long to me. I may have to start looking at tweaking the settings for vacuum. I do know that I originally set vacuum priority down some so that it would have little to no impact on the database for users. Admittedly it seems like some of the selects that dbmail does take quite a long time to return. One of the worst is the dbmail-util check for incorrect is_header flag. That seems to take about 3 hours. I don't think it's ever found a problem looking in my log, so should I not bother to do this? Some areas that concern me is that we would like to have a more "near real time backup" hence why we were looking at Slony. At this point I'm thinking of just sticking with one DB server and using PITR in 8.x and saving the WAL files to a IDE RAID. These would be "full" WAL files, not currently in use. I'll take any thoughts on this if you have them. The other thing that I'm toying with is that I see several things on the Postgres site about Hyperthreading being bad. So I'm planning on turning that off to see if helps. Do any of you do anything special for your database setup? Do you put the WAL files on a separate spindle (as suggested)? What are you setting your shared_buffers at (mine is 40000)? Do you modify any of the items like FSM and such in the config? I know that out of the box PostgreSQL is set kind limiting for small databases. But I tuned some of the parameters to help with the size of the database, with help from someone that knew PostgreSQL much better than I. Honestly, the DB seems to run fine when it's just the DB, and no attempt at replication or such. I'm just trying to make it as reliable as I can. I also don't know if I need to start taking harder looks at the disk setup, etc. I just feel that I shouldn't be at that point yet, so any other comments would be great. I'd be happy to share my postgresql.conf file, though not many changes from stock other than shared_buffers and FSM stuff. -- David A. Niblett | email: [EMAIL PROTECTED] Network Administrator | Phone: (352) 334-3400 Gainesville Regional Utilities | Web: http://www.gru.net/ -----Original Message----- From: Dave Logan [mailto:[EMAIL PROTECTED] Sent: Thursday, November 17, 2005 11:55 AM To: DBMail mailinglist Cc: [EMAIL PROTECTED] Subject: Re: [Dbmail] Size, vacuum, performance, etc > So if there is anyone on the list that is using dbmail 2.0 with > PostgreSQL (8.x) with 5k+ users, and a data set of around 20G would > you contact me off list if you like. > We have around that for dataset/userbase, but are using dbmail 1.2. I'll answer your questions anyway. First off, we're running postgresql 7.4.7 on a debian system, adaptec hardware scsi raid5, 2.6 GHz P4, 2 G ram. We only offer POP. > - How/if you backup or replicate your database We dump all tables but messageblks nightly, we dump all tables on friday morn (01:10), and it is in average done about 02:40, compressed size of 4.6GB. We only use pg_dump. > - How you deal with vacuum's (mine seem to take forever) We analyze hourly, vacuum users,pbsp,messages every six hours, vacuum entire db daily. We also reindex weekly. > - How/if you deal with a vacuum FULL. Since we got our maintenance routine down, we have never had to nor felt the need to vacuum full. There was a PG issue with earlier versions that necessitated this, but that was ages ago... > - What kind of schedule you use for dbmail-util We don't use dbmail-util directly for delete or purge. We "update messages set status=003::smallint where status=002::smallint" and "delete from messages where status=003::smallint" once daily (about one and a half hours apart, and about an hour and a half before the daily vacuum). > - The number of connections you have from dbmail and what kind > of load that produces. We have four servers attaching for smtp injection, and two servers attaching for POP retrieval. We remain <1 for load average during most production time. This, of course, goes up some during the vacuums and such, but those are relatively short lived (for example, we vacuum verbose, and the one that runs every six hours runs in 737 seconds). A quick look at the logs shows that for yesterday's log cycle, we injected message ids 49600239 - 49656408, and grepping for "logging out" to catch POP connections, there were ~56000. The postmaster will allow 512 connections, dbmail.conf has a MAXCHILDREN=100 on each of the (two) POP servers. postfix is set to default for all delivery concurrency limits (20). > - What kind of "bad things" happen to DBMail when your database > load gets rather high. The worst thing I've seen is that customer cry like little school girls. I've never seen, experienced, nor heard of lost data, even when the system load "idled" at about 24, and pop connections took upwards of 120 seconds to establish (for those patient enough to wait). If I missed anything, let me know. In fact, at looking over all this data, I may even start doing a full dump nightly (I wasn't even aware it was going that quickly :) Thanks! -- Dave Logan http://www.digitalcoven.com/ "No! Try not! Do. Or do not. There is no try." -- Yoda _______________________________________________ Dbmail mailing list Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
