Re: [Dbmail] mbox to dbmail converter
Jacques, Your make does not appear to be pulling in all of the objects on the command line. Check the definition of UNIONE_OBJECTS, DIRS and LIB in the Makefile. You need to have run build.sh first to generate the proper Makefile. Its probably better to put the lines I suggested in Makefile.concept, then run build.sh. This will make the changes more permanent. If it is configured properly, when run the make, you should see a line like this cc -Wall -O2 -D_BSD_SOURCE -D_SVID_SOURCE mb2db.c -o mb2db list.o \ debug.o mysql/dbmysql.o dbmd5.o md5.o mysql/dbauthmysql.o mime.o config.o \ -L/usr/lib/mysql/ -I/usr/include/mysql/ -lmysqlclient -lcrypto It may vary slightly depending on where you defined the location of your mysql libraries and includes when you ran build.sh. I am using the dbmail-1.0 source. John. Jacques Beaudoin wrote: Thanks John for mb2db.c When i do the make i get compiling errors Any idea where to look Thanks [EMAIL PROTECTED] dbmail]# make mb2db cc -Wall -O2 -D_BSD_SOURCE -D_SVID_SOURCEmb2db.c -o mb2db /tmp/ccb3yJbn.o: In function `main': /tmp/ccb3yJbn.o(.text+0x3e): undefined reference to `configure_debug' /tmp/ccb3yJbn.o(.text+0x56): undefined reference to `ReadConfig' /tmp/ccb3yJbn.o(.text+0x62): undefined reference to `_db_pass' /tmp/ccb3yJbn.o(.text+0x67): undefined reference to `_db_user' /tmp/ccb3yJbn.o(.text+0x6c): undefined reference to `_db_db' /tmp/ccb3yJbn.o(.text+0x71): undefined reference to `_db_host' /tmp/ccb3yJbn.o(.text+0x76): undefined reference to `GetDBParams' /tmp/ccb3yJbn.o(.text+0x7e): undefined reference to `db_connect' /tmp/ccb3yJbn.o: In function `processmbox': /tmp/ccb3yJbn.o(.text+0x137): undefined reference to `auth_user_exists' /tmp/ccb3yJbn.o: In function `create_folder': /tmp/ccb3yJbn.o(.text+0x30e): undefined reference to `db_findmailbox' /tmp/ccb3yJbn.o(.text+0x330): undefined reference to `db_createmailbox' /tmp/ccb3yJbn.o: In function `process_mboxfile': /tmp/ccb3yJbn.o(.text+0x3cd): undefined reference to `trace' /tmp/ccb3yJbn.o(.text+0x522): undefined reference to `db_insert_message_block' /tmp/ccb3yJbn.o(.text+0x62d): undefined reference to `db_insert_message_block' /tmp/ccb3yJbn.o(.text+0x6a9): undefined reference to `db_insert_message' /tmp/ccb3yJbn.o(.text+0x753): undefined reference to `db_insert_message_block' /tmp/ccb3yJbn.o(.text+0x7da): undefined reference to `db_update_message' /tmp/ccb3yJbn.o(.text+0x801): undefined reference to `trace' /tmp/ccb3yJbn.o(.text+0x818): undefined reference to `db_get_mailboxid' /tmp/ccb3yJbn.o(.text+0x864): undefined reference to `db_set_msgflag' /tmp/ccb3yJbn.o(.text+0x895): undefined reference to `query' /tmp/ccb3yJbn.o(.text+0x8a3): undefined reference to `query' /tmp/ccb3yJbn.o(.text+0x8a8): undefined reference to `db_query' /tmp/ccb3yJbn.o(.text+0x8bd): undefined reference to `trace' /tmp/ccb3yJbn.o(.text+0x940): undefined reference to `db_insert_message_block' /tmp/ccb3yJbn.o(.text+0x9c7): undefined reference to `db_update_message' /tmp/ccb3yJbn.o(.text+0x9ee): undefined reference to `trace' /tmp/ccb3yJbn.o(.text+0xa05): undefined reference to `db_get_mailboxid' /tmp/ccb3yJbn.o(.text+0xa51): undefined reference to `db_set_msgflag' /tmp/ccb3yJbn.o(.text+0xa88): undefined reference to `query' /tmp/ccb3yJbn.o(.text+0xa96): undefined reference to `query' /tmp/ccb3yJbn.o(.text+0xa9b): undefined reference to `db_query' /tmp/ccb3yJbn.o(.text+0xab0): undefined reference to `trace' collect2: ld returned 1 exit status ___ Dbmail mailing list Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
[Dbmail] How scalable is it?
Does anybody have any benchmarks or even subjective opinions on how dbmail performs when dealing with large volumes of mail? I am considering transfering all of our user's mail, including all their folders of saved and archived mail to dbmail. This will come to around 40-50GB, which makes for quite a large messageblks table. I don't have enough experience with mysql to know how it will deal with that. Presumably the more mail that is in the system, the slower things will get. I'm not sure this fits with the idea of a 'scalable' system. With the system we are using at present (sendmail/procmail/uw-imap), the volume of saved mail has no effect on performance, so long as individual folders and mailboxes are kept to a reasonable size. I can see that dbmail will require less dicipline from users and will cope better with those who let their inbox grow to hundreds of megabytes, but is it going to get slower and slower over time?
Re: [Dbmail] How scalable is it?
At 10:37 AM 18/12/2002 +1100, John Heller wrote: Does anybody have any benchmarks or even subjective opinions on how dbmail performs when dealing with large volumes of mail? Subjective opinion is that it works well. But there are at least four definitions of large volumes: 1. mail traffic into site 2. client traffic viewing/retrieving mail 3. mail volume preserved on server 4. large IMAP folders We have about 100-200MB/day incoming, up to several clients accessing mail every second, and a low volume of preserved email (up to 5GB, usually around 2GB). We're running PostgreSQL and the server does not even show the load. But, as noted elsewhere, it is important to make sure that PostgreSQL is configured properly for such a large DB especially if you have high levels of transient data. We did have one inactive account with 86000 messages in INBOX, and that took a few minutes to open with IMAP -- but I *think* that's an IMAP problem since, even to retrieve the first twelve messages it insisted on getting summaries for all of them. If you choose to use MySQL, the only warning that may be relevant is that it does not have great locking, so if you have a large volume of active connections you may end up with contention problems (meaning slower retrieval/updates etc). I am considering transfering all of our user's mail, including all their folders of saved and archived mail to dbmail. This will come to around 40-50GB, which makes for quite a large messageblks table. I don't have enough experience with mysql to know how it will deal with that. Neither do I. Assuming you put the effort into configuring postgresSQL properly, it should have no problems. Presumably the more mail that is in the system, the slower things will get. Why? The only issues I have seen relate to IMAP mailbox sizes. I can see that dbmail will require less dicipline from users and will cope better with those who let their inbox grow to hundreds of megabytes, but is it going to get slower and slower over time? Sadly, this is probably also untrue -- but it may not be DBMail's fault. I don't have much experience with IMAP, but what I have seen using Horde/IMP suggests that it is not very good at handling large mailboxes. One of the features that is often overlooked when codsidering DBMail is that you get all the advantages of a *real* database Hope this helps Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [Dbmail] How scalable is it?
At 11:40 AM 18/12/2002 +1100, Philip Warner wrote: One of the features that is often overlooked when codsidering DBMail is that you get all the advantages of a *real* database Sorry...send was hit too soon... One of the features that is often overlooked when considering DBMail is that you get all the advantages of a *real* database (at least in the case of PostgreSQL), so we have: - aliases & users as views constructed based on a much more complex set of rules relating to user preferences & settings. - rewrite rules (updatable views) to handle DBMail setting the last_login date - stored procedures (callable from PHP, Perl etc) for adding & maintaining users (basically write the code once, call from anywhere). - a system of access controls to allow user-management & creation by authorized users - complex usage reports and traffic details - postfix also uses DB, and again the virtuals are a view constructed based on other business rules. etc etc. In some sense, DBMail is the starting point for a much more usable and functional system of mail handling. You should not just focus on if it will be faster (although, making sure it won't be slower is important). Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
[Dbmail] Patch for mysql/dbmysql.c
Hi there DBMailers..
Below is a patch for mysql/dbmysql.c to make the update of the pbsp table
easier. It uses the command REPLACE which will do an INSERT or an UPDATE
depending of the entry is there or not. This means only 1 SQL query instead
of 2 (1 to see if its there and one to INSERT or UPDATE). The postgreSQL
version hasn't been changed as I don't know if if it has a similar command
as REPLACE that mysql does.
If anyone has any comments on this please let me know. It is based on the
rc3/rc4 but can easily be edited for v1.0
Thanks
Craig Whitmore
--- dbmysql.c.OLD 2002-12-18 11:27:17.0 +1300
+++ dbmysql.c 2002-12-18 12:51:35.0 +1300
@@ -1529,48 +1529,12 @@
char timestr[30];
time_t td;
struct tm tm;
- u64_t id=0;
time(&td); /* get time */
tm = *localtime(&td); /* get components */
strftime(timestr, sizeof(timestr), "%G-%m-%d %H:%M:%S", &tm);
- snprintf(query, DEF_QUERYSIZE, "SELECT idnr FROM pbsp WHERE ipnumber =
'%s'", ip);
- if (db_query_read(query) == -1)
-{
- trace(TRACE_ERROR,"db_log_ip(): could not access ip-log table
(pop/imap-before-smtp): %s",
- mysql_error(&read_conn));
- return -1;
-}
-
- if ((res = mysql_store_result(&read_conn)) == NULL)
-{
- trace(TRACE_ERROR,"db_log_ip(): could not check ip-log
(pop/imap-before-smtp): %s",
- mysql_error(&read_conn));
- return -1;
-}
-
- row = mysql_fetch_row(res);
- id = row ? strtoull(row[0], NULL, 10) : 0;
-
- mysql_free_result(res);
-
- if (id)
-{
- /* this IP is already in the table, update the 'since' field */
- snprintf(query, DEF_QUERYSIZE, "UPDATE pbsp SET since = '%s' WHERE
idnr=%llu",timestr,id);
-
- if (db_query(query) == -1)
- {
- trace(TRACE_ERROR,"db_log_ip(): could not update ip-log
(pop/imap-before-smtp): %s",
- mysql_error(&write_conn));
- return -1;
- }
-}
- else
-{
- /* IP not in table, insert row */
- snprintf(query, DEF_QUERYSIZE, "INSERT INTO pbsp (since, ipnumber)
VALUES ('%s','%s')",
+ snprintf(query, DEF_QUERYSIZE, "REPLACE INTO pbsp (since, ipnumber)
VALUES ('%s','%s')",
timestr, ip);
if (db_query(query) == -1)
@@ -1579,7 +1543,6 @@
mysql_error(&write_conn));
return -1;
}
-}
trace(TRACE_DEBUG,"db_log_ip(): ip [%s] logged\n",ip);
return 0;
Re: [Dbmail] Patch for mysql/dbmysql.c
At 02:00 PM 18/12/2002 +1300, Craig Whitmore wrote: The postgreSQL version hasn't been changed as I don't know if if it has a similar command as REPLACE that mysql does. It doesn't, so no need to worry about that. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [Dbmail] mbox to dbmail
The plan is to use mysql. Eelco van Beek - IC&S wrote: Hi, We once wrote a program to insert 10 Gb of mail into dbmail. The program did this in about 50 minutes. What database are you going to use? Best regards, Eelco
Re: [Dbmail] mbox to dbmail
Hi, the fastest way to import data is to use a "LOAD DATA INFILE" statement in mySQL (for postgresql, use "COPY FROM"). This will bypass a lot of overhead. Be sure to remove all indices and all the restraints you can for they as well take time. Just add them afterwards, it is a lot faster to build up an index for a complete table than to update it time after time. If you're handy with C, look at the code in raw-convert.c. That particular bit of code was used to create TAB-delimited datafiles (usable by mysql and postgresql); the datafiles where read using COPY FROM (it was a postgresql system) to import 10 GB of mail in only 50 minutes. Important postgresql note: you MUST update the sequences afterwards as they are not affected by the copy from command (yes i did forget it :-) regards roel John Heller heeft op woensdag, 18 dec 2002 om 06:35 (Europe/Amsterdam) het volgende geschreven: The plan is to use mysql. Eelco van Beek - IC&S wrote: Hi, We once wrote a program to insert 10 Gb of mail into dbmail. The program did this in about 50 minutes. What database are you going to use? Best regards, Eelco ___ Dbmail mailing list Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
[Dbmail] postgresql disk usage
Hi all, on of our dbmail-using customers runs a postgresql database containing about 10GB of data; there are ~23000 accounts, 5000 of them are being accessed regularly. The database is not growing (only POP is run), about 2 messages are delivered each day. The problem is that postgresql keeps on eating disk space - after a recent crash (due to running a VACUUM FULL on a near-full disk) a backup was restored and the database was shrunk from 65GB to 10GB. Since then, postgresql has resumed it's eating functionality, the size occupied on disk is now some 46 GB while the database had not grown. I would like to ask the postgresql experts on this list some advice: how to reclaim disk space? For now, each night a VACUUM ANALYZE runs on the database. Any chance i can see where exactly postgresql is growing? Would re-building the indices help? regards roel
Re: [Dbmail] postgresql disk usage
Hello Roel, Wednesday, December 18, 2002, 7:23:49 AM, you wrote: RRIS> Hi all, RRIS> on of our dbmail-using customers runs a postgresql database containing RRIS> about 10GB of data; there are ~23000 accounts, 5000 of them are being RRIS> accessed regularly. The database is not growing (only POP is run), RRIS> about 2 messages are delivered each day. RRIS> The problem is that postgresql keeps on eating disk space - after a RRIS> recent crash (due to running a VACUUM FULL on a near-full disk) a RRIS> backup was restored and the database was shrunk from 65GB to 10GB. RRIS> Since then, postgresql has resumed it's eating functionality, the size RRIS> occupied on disk is now some 46 GB while the database had not grown. RRIS> I would like to ask the postgresql experts on this list some advice: RRIS> how to reclaim disk space? For now, each night a VACUUM ANALYZE runs on RRIS> the database. Any chance i can see where exactly postgresql is growing? RRIS> Would re-building the indices help? Rebuilding indices could help but the space recovered would be marginal. If everything else looks ok, my bet is that you could have orphaned Large Objects (i.e. BLOBs). There is a tool named 'vacuumlo' on the contrib dir on the PostgreSQL distribution that is supposed to help. But these orphaned Large Objects should not exist; they mean a bug on the code that handles the database (the Large Object chunks should have been deleted). Please monitor the catalog table pg_largeobject to check if it's the cause of the growth. Best Regards, Steve Howe
Re: [Dbmail] postgresql disk usage
At 11:23 AM 18/12/2002 +0100, Roel Rozendaal - IC&S wrote: The problem is that postgresql keeps on eating disk space Have a look at this link: http://www.rhyme.com.au/manuals/pgsql-7.3/postmaster-tuning-software.html it is an addition to the PG docs that I wrote recently and which has not yet been added to CVS. We had the same problem, and this describes the cause & solution. If you like, I can help in a more detailed fashion offline. I would like to ask the postgresql experts on this list some advice: how to reclaim disk space? Only solution is VACUUM full or backup/restore. However, the above link describes how to at least stop the growth. The problems with having a too-big database are that VACUUM takes a long time, sequential scans take longer and disk space is wasted. It is worth scheduling downtime to fix, but only after some statistics are collected. For now, each night a VACUUM ANALYZE runs on the database. Any chance i can see where exactly postgresql is growing? VACUUM VERBOSE gives more detailed information (see above link). Also, select relname, relpages from pg_class will give you relation sizes. But VACUUM VERBOSE is more likely to give more useful information. Would re-building the indices help? Unlikely, unless there are other problems. The database is not growing (only POP is run), about 2 messages are delivered each day. This means that the most likely problem is as above. Try: VACUUM VERBOSE messageblks This will probably show a very large 'Unused' value in the MESSAGEBLKS or PG_TOAST_nnn table, where nnn is the OID of the MESSAGEBLKS table. To collect useful data, run VACUUM VERBOSE every few hours and keep the logs; run it often enough to collect a representative usage pattern (dbmail-maintenance, day/nigh etc). Once you have that, I can give more specific advice about the best settings (or you can use the link above to work this out for yourself). Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [Dbmail] postgresql disk usage
Hi Philip, Steve, The pg_largeobject table is empty, so probably it does not concern orphaned BLOB's. The "select relname, relpages from pg_class order by relpages desc" shows: relname | relpages -+-- pg_toast_10945937| 3841293 messageblks | 215979 pg_toast_10945937_idx |59190 messages|38039 What exactly is this pg_toast thing? I am somewhat reluctant to run a 'VACUUM xxx' right now as it has the side-effect of raising the load from ca. 0.7 to ca. 7 and turning the database completely irresponsive - it does concern a production machine. regards roel Philip Warner heeft op woensdag, 18 dec 2002 om 12:22 (Europe/Amsterdam) het volgende geschreven: At 11:23 AM 18/12/2002 +0100, Roel Rozendaal - IC&S wrote: The problem is that postgresql keeps on eating disk space Have a look at this link: http://www.rhyme.com.au/manuals/pgsql-7.3/postmaster-tuning- software.html it is an addition to the PG docs that I wrote recently and which has not yet been added to CVS. We had the same problem, and this describes the cause & solution. If you like, I can help in a more detailed fashion offline. I would like to ask the postgresql experts on this list some advice: how to reclaim disk space? Only solution is VACUUM full or backup/restore. However, the above link describes how to at least stop the growth. The problems with having a too-big database are that VACUUM takes a long time, sequential scans take longer and disk space is wasted. It is worth scheduling downtime to fix, but only after some statistics are collected. For now, each night a VACUUM ANALYZE runs on the database. Any chance i can see where exactly postgresql is growing? VACUUM VERBOSE gives more detailed information (see above link). Also, select relname, relpages from pg_class will give you relation sizes. But VACUUM VERBOSE is more likely to give more useful information. Would re-building the indices help? Unlikely, unless there are other problems. The database is not growing (only POP is run), about 2 messages are delivered each day. This means that the most likely problem is as above. Try: VACUUM VERBOSE messageblks This will probably show a very large 'Unused' value in the MESSAGEBLKS or PG_TOAST_nnn table, where nnn is the OID of the MESSAGEBLKS table. To collect useful data, run VACUUM VERBOSE every few hours and keep the logs; run it often enough to collect a representative usage pattern (dbmail-maintenance, day/nigh etc). Once you have that, I can give more specific advice about the best settings (or you can use the link above to work this out for yourself). Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ___ Dbmail mailing list Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] postgresql disk usage
hi philip,
I just checked your link and executed the query
select count(*) from pg_class where relkind in ('t','r');
it got me a value of 38; the system has default config of
max_fsm_relations (100) so that shouldn't be the problem either.
regards roel
Philip Warner heeft op woensdag, 18 dec 2002 om 12:22
(Europe/Amsterdam) het volgende geschreven:
At 11:23 AM 18/12/2002 +0100, Roel Rozendaal - IC&S wrote:
The problem is that postgresql keeps on eating disk space
Have a look at this link:
http://www.rhyme.com.au/manuals/pgsql-7.3/postmaster-tuning-
software.html
it is an addition to the PG docs that I wrote recently and which has
not yet been added to CVS. We had the same problem, and this describes
the cause & solution. If you like, I can help in a more detailed
fashion offline.
I would like to ask the postgresql experts on this list some advice:
how to reclaim disk space?
Only solution is VACUUM full or backup/restore. However, the above
link describes how to at least stop the growth. The problems with
having a too-big database are that VACUUM takes a long time,
sequential scans take longer and disk space is wasted. It is worth
scheduling downtime to fix, but only after some statistics are
collected.
For now, each night a VACUUM ANALYZE runs on the database. Any
chance i can see where exactly postgresql is growing?
VACUUM VERBOSE gives more detailed information (see above link). Also,
select relname, relpages from pg_class
will give you relation sizes. But VACUUM VERBOSE is more likely to
give more useful information.
Would re-building the indices help?
Unlikely, unless there are other problems.
The database is not growing (only POP is run), about 2 messages
are delivered each day.
This means that the most likely problem is as above. Try:
VACUUM VERBOSE messageblks
This will probably show a very large 'Unused' value in the MESSAGEBLKS
or PG_TOAST_nnn table, where nnn is the OID of the MESSAGEBLKS
table.
To collect useful data, run VACUUM VERBOSE every few hours and keep
the logs; run it often enough to collect a representative usage
pattern (dbmail-maintenance, day/nigh etc). Once you have that, I can
give more specific advice about the best settings (or you can use the
link above to work this out for yourself).
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172 | ___ |
Http://www.rhyme.com.au |/ \|
|----
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
___
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] postgresql disk usage
At 12:47 PM 18/12/2002 +0100, Roel Rozendaal - IC&S wrote: pg_toast_10945937| 3841293 ... What exactly is this pg_toast thing? When a text field is too large to fit on a page, it is written to a separate table (one for each real table) in chunks. My guess is that if you do: select relname from pg_class where oid = '10945937' you will get 'messageblks'. If so, then the messageblks toast table is the culprit (and is about 30GB in size). If the guess is wrong, let me know the table name... I am somewhat reluctant to run a 'VACUUM xxx' right now as it has the side-effect of raising the load from ca. 0.7 to ca. 7 and turning the database completely irresponsive - it does concern a production machine. This should not happen. We don't have such a large DB, but the users barely notice a VACUUM - do you know which resource is the problem? Disk IO, CPU or memory? In Linux, eg, if DMA is not enabled for disks, then high I/O stalls the machine - could some similar issue be occurring? Another thought: try changing the nice value for the backend doing the vacuum (in general this is a very bad idea, but it may reduce the impact of the vacuum if it is CPU related). I certainly understand your desire not to upset a production machine; if you are unable to anything else, can you set your next VACCUUM to be a VACUUM VERBOSE ANALYZE? That way we can get some idea of the correct settings to use to at least stop the growth. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [Dbmail] postgresql disk usage
At 01:00 PM 18/12/2002 +0100, Roel Rozendaal - IC&S wrote: it got me a value of 38; the system has default config of max_fsm_relations (100) so that shouldn't be the problem either. Be careful with this. You need to do this for each database (except template0). Try '\l' in psql to get a lict of databases, then connect to each and run the query - the max_fsm_relations should be the sum of them all. 38 is very unlikely to be right. But 100 could still be OK if dbmail is the only other DB. Once you have a value for max_fsm_relations (it's cheap, and the future default will be nearer 1000), you need to set max_fsm_pages (I *know* the default is too low for your requirments) and the VACUUM frequency. If you continue to experience VACUUM killing the server, then you may need to set MAX_FSM_PAGES very high. Which OS and version of PG are you running? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [Dbmail] postgresql disk usage
The only other database is template0, which gives a value of 30 for the query; the OS is linux 2.4.5, postgresql is version 7.2. What MAX_FSM_* settings do you recommend? Philip Warner heeft op woensdag, 18 dec 2002 om 13:14 (Europe/Amsterdam) het volgende geschreven: At 01:00 PM 18/12/2002 +0100, Roel Rozendaal - IC&S wrote: it got me a value of 38; the system has default config of max_fsm_relations (100) so that shouldn't be the problem either. Be careful with this. You need to do this for each database (except template0). Try '\l' in psql to get a lict of databases, then connect to each and run the query - the max_fsm_relations should be the sum of them all. 38 is very unlikely to be right. But 100 could still be OK if dbmail is the only other DB. Once you have a value for max_fsm_relations (it's cheap, and the future default will be nearer 1000), you need to set max_fsm_pages (I *know* the default is too low for your requirments) and the VACUUM frequency. If you continue to experience VACUUM killing the server, then you may need to set MAX_FSM_PAGES very high. Which OS and version of PG are you running? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ___ Dbmail mailing list Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] postgresql disk usage
At 01:00 PM 18/12/2002 +0100, Roel Rozendaal - IC&S wrote: it got me a value of 38; the system has default config of max_fsm_relations (100) so that shouldn't be the problem either. A simple (heavy-handed) approach would be: set max_fsm_relations to 1000 set vacuum frequency to daily get the output of a VACUUM VERBOSE: psql -c "vacuum verbose" dbmail > vacuum.out 2>&1 grep "Changed" vacuum.out | grep -v "Changed 0," add all the 'changed' values together, double it, and set max_fsm_pages to this value. Bear in mind that (max_fsm_pages*4 bytes) of shared memory will be consumed, so if the value is very high it may need kernel config changes (Linux it is dynamic). The reason I say 'double it' is on the assumption that you will have busier days. Also bear in mind that you should run a VACUUM ASAP after starting the server to build the FSM. I do strongly recommend that you VACUUM FULL the database (or backup/restore), then set a VACUUM VERBOSE running hourly (we do it two-hourly, but have a lower load) - and keep the logs to check the settings. You may well find that VACUUMing a cleaner database will put less strain on your server. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [Dbmail] postgresql disk usage
At 02:07 PM 18/12/2002 +0100, Roel Rozendaal - IC&S wrote: What MAX_FSM_* settings do you recommend? I need to see the output of a VACUUM VERBOSE to know a good answer. Otherwise, if you can give me an upper limit on the volume of mail received each day, we could probably go with that. eg. if you receive 8GB of mail each day, and it is all deleted/read/removed by the end of the day, and you only vacuum daily, then I would suggest a minimum of 1,000,000 for MAX_FSM_PAGES, and I would be inclined to set it to 2,000,000 to be safe (this will use 8MB of shared memory). MAX_FSM_RELATIONS is probably OK as is, but increasing it to 250 would give you some insurance. In a high-transience database (email), the basic rule is to set MAX_FSM_PAGES to the number of pages you will consume between vacuums; I would also recommend a VACUUM VERBOSE be done whenever you restart the database server process, to rebuild the FSM. If you have any scheduled downtime, I do also strongly recommend a VACUUM FULL, then try VACUUM and see if it kills the server. If it does not kill the server, schedule VACUUM VERBOSE every hour. Once this has run for a day, you can set MAX_FSM_PAGES to a much lower value. Note that to set the MAX_FSM_* parameters you need to restart the postmaster; so it might be good to do it all together. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [Dbmail] ANNOUNCEMENT: DBMAIL 1.0!!!
On Tuesday 03 December 2002 11:38 am, IC&S Helpdesk wrote: > Yes! Finally :) > > This is the official announcement of dbmail 1.0 stable! > You can download it at our all new dbmail website (www.dbmail.org). > > Major updates: > - imap commandline scanner is now fully RFC compatible > - new server code for both imap and pop servers > - many little bugfixes > > Give dbmail a testrun and let us know how she runs! I found one problem with dbmail 1.0's tarball straight off the site.. dbmail-smtp couldn't find dbmail.conf, because in main.c, it's looking for "dbmail.conf". instead of "/etc/dbmail.conf". I found this while using exim, and setting the current directory, as usual in my setups, to be /tmp. When I set it to /etc, it worked, so I fixed it that way in main.c. -- Eric Renfro Myrddin Computers & Designs - CEO/President Phone: (512) 527-9111 Fax:(512) 527-9034
Re: [Dbmail] postgresql disk usage
Philip Warner heeft op woensdag, 18 dec 2002 om 13:05 (Europe/Amsterdam) het volgende geschreven: At 12:47 PM 18/12/2002 +0100, Roel Rozendaal - IC&S wrote: pg_toast_10945937| 3841293 ... What exactly is this pg_toast thing? When a text field is too large to fit on a page, it is written to a separate table (one for each real table) in chunks. My guess is that if you do: select relname from pg_class where oid = '10945937' you will get 'messageblks'. If so, then the messageblks toast table is the culprit (and is about 30GB in size). If the guess is wrong, let me know the table name... It is the messageblks table. However, the query select sum(blocksize) from messageblks; (which should return the amount of actual data in the table) gives ~10 GB - so where does the extra 20 GB come from? I am somewhat reluctant to run a 'VACUUM xxx' right now as it has the side-effect of raising the load from ca. 0.7 to ca. 7 and turning the database completely irresponsive - it does concern a production machine. This should not happen. We don't have such a large DB, but the users barely notice a VACUUM - do you know which resource is the problem? Disk IO, CPU or memory? In Linux, eg, if DMA is not enabled for disks, then high I/O stalls the machine - could some similar issue be occurring? Another thought: try changing the nice value for the backend doing the vacuum (in general this is a very bad idea, but it may reduce the impact of the vacuum if it is CPU related). The system has high-speed SCSI disks, a PIII 1.2 GHz processor and 512 MB of ram - should be sufficient shouldn't it? I certainly understand your desire not to upset a production machine; if you are unable to anything else, can you set your next VACCUUM to be a VACUUM VERBOSE ANALYZE? That way we can get some idea of the correct settings to use to at least stop the growth. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ___ Dbmail mailing list Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] How scalable is it?
- complex usage reports and traffic details - postfix also uses DB, and again the virtuals are a view constructed based on other business rules. etc etc. In some sense, DBMail is the starting point for a much more usable and functional system of mail handling. You should not just focus on if it will be faster (although, making sure it won't be slower is important). What a great post. You should write a whitepaper!. I was thinking it might be possible to write triggers to manipulate the postfix text files but I did not know you could have postfix read the database directly that's great. Have you thought of using a non IMAP client for employees which taps into the database directly? I was thinking that might be good way to go internal employees and it would bypass whatever IMAP slowness there might be. :wq Tim Uckun US Investigations Services/Due Diligence http://www.diligence.com/
Re[2]: [Dbmail] How scalable is it?
TU> I was thinking it might be possible to write triggers to TU> manipulate the postfix text files but I did not know you could TU> have postfix read the database directly that's great. If you have configured postfix to use the pbsp for authentication, you can use it for virtually ANY table within postfix. I use MySQL to maintain a transport/destination table, so domains can be added without restarting postfix; postfix checks MySQL tables for both access-by-mailfrom and access-by-IP. In fact, only the regular expression filters aren't in SQL tables. Making it work with PostGRES is not quit as easy as doing it with MySQL, because PG support isn't in the main postfix CVS tree or distribution, but it DOES exist. -- Jeff Brenton President, Engineered Software Products, Inc http://espi.com Questionable web page: http://dididahdahdidit.com Liberalism grants you the freedom to advocate any idea*. * Please see http://www.dididahdahdidit.com/except.php for a current list of exceptions
Re: [Dbmail] mbox to dbmail converter
Thanks John mb2db as compiled ok and i got all my folders in mysql i think it was the part about build.sh mb2db sould be included in dbmail and always compiled with the dbmail programs (dbmail-smtp,dmail-pop3detc) Jacques
