In some email I received from Gianni Mariani <[EMAIL PROTECTED]> on Wed, 02 Jul 2003 00:00:16 -0700, wrote:
> lou wrote: > > >In some email I received from Gianni Mariani <[EMAIL PROTECTED]> on Tue, 01 > >Jul 2003 > >22:25:11 -0700, wrote : > > > ... > > >not true in PgSQL 7.3.3 works fine with > 64 conn, > >alter the value in PGDATA/postgresql.conf + alter the buffer size 2xMAX_CONN > >I dont see a point hardcoding something like this?:) > > > > > Good point. I hadn't noticed this. Kewel. > [...] > >> > >>a) dbmail could use transactions (BEGIN/COMMIT blocks) and it would be > >>faster on Postgresql because the constraints need only be checked at the > >>commit. > >wey, how exactly is dbmail scheme slow? > > > Too many indexes. I think you could achieve the same effect with fewer > indexes hence less updates to indexes. yes, there are more indeces than normal which makes the hashtable quite crowded, I dont get the reason why there are indeces on some of the auto increment values. > >and how exactly using transaction will make it > >faster, please elaborate? > > Try a test. Doing a bunch of updates in a single transaction is faster > than doing multiple transactions. okay, since PgSQL will wrap a single query inside a transaction in general, <lock tables;> start trans; insert_message(id,blahblah); insert_messageblks(id,blahblah); commit; <unlock tables;> so this things limits to one transaction, rather than insert_message(id,...); insert_messageblks(id,..); which is going to perform 2 transactions. actually I havent looked too deep in the recent 7.3 PgSQL branch to find out new performance goodies, but i will soon. > >I suppose rewriting the queries might speed up things a little > >bit. it's quite possible to wrap some queries into transactions, but I cant > >see _this_ > >really good reason why this should be implemented at this point (safe > >model). > > > Transactions are safer. It means that if you do multiple updates, > you're only going to commit completed ones. Obviously, 'everything or nothing' approach rock ;) > >>b) dbmail could also use stored procedures in Postgresql. This would > >>eliminate a bunch of processing on the server. [...] > >why? it will break portability with other servers like mysql. Also that > >means all the > >load to be pushed to the database server rather than the client (dbmail > >server). > >(MySQL have stored procedures in 4.0.x or?) > > > Because pgsql procedures automagically prepare statements and hence > there is a lot less "planning" going on. yes, but you can make pgsql analyze queries :) > Also, you really don't want the C code to know too much about the > schema. (Encapsulation peeve on mine). yes, but can you make it learn, or write it so generically. I'm trying to find the balance between pgsql/mysql db APIs in dbmail and basically merge them in one.. hope to find some time in the next few months;) cheers
