From ssutjiono@wc-group.com Thu Apr 11 13:20:09 2002 Received: from qmsql.netservers.net (hpmail2.netservers.net [209.196.128.168]) by postgresql.org (Postfix) with SMTP id 94D56476547 for ; Thu, 11 Apr 2002 09:20:09 -0400 (EDT) Received: (qmail 65960 invoked from network); 11 Apr 2002 13:19:57 -0000 Received: from unknown (HELO AARON) (66.26.63.73) by hpmail2.netservers.net with SMTP; 11 Apr 2002 13:19:57 -0000 Message-ID: <003801c1e15a$c03c5450$110a010a@headquarters.wcgroup.com> From: "Samuel J. Sutjiono" To: , Subject: Transactional vs. Read-only (Retrieval) database Date: Thu, 11 Apr 2002 09:14:02 -0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0035_01C1E139.38DCBB60" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200204/441 X-Sequence-Number: 23157 This is a multi-part message in MIME format. ------=_NextPart_000_0035_01C1E139.38DCBB60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'd like to get some opinions if there are any benefits (i.e. in terms of p= erformance) of creating two separate databases, one for transactions (inser= t, update, delete) and the other one is for retrieval/search (select). Thanks in advance for your input. Sam _________________________________________________ Expand your wireless world with Arkdom PLUS http://www.arkdom.com/ ------=_NextPart_000_0035_01C1E139.38DCBB60 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I'd like to get some opinions if there are= =20 any benefits (i.e. in terms of performance) of creating two separ= ate=20 databases, one for transactions (insert, update, delete) and the other= one=20 is for retrieval/search (select).
 
Thanks in advance for your input.
Sam
_________________________________________________
 Expand = your=20 wireless world with Arkdom PLUS
 http://www.arkdom.com/
= ------=_NextPart_000_0035_01C1E139.38DCBB60-- From jm.poure@freesurf.fr Thu Apr 11 14:25:26 2002 Received: from www1.translationforge.com (unknown [212.11.36.58]) by postgresql.org (Postfix) with ESMTP id ACF6D476B82; Thu, 11 Apr 2002 10:25:26 -0400 (EDT) Received: from linus.translationforge.com ([192.168.1.102]) by www1.translationforge.com (8.12.1/8.12.1) with ESMTP id g3BEPa83018802; Thu, 11 Apr 2002 16:25:36 +0200 Content-Type: text/plain; charset="iso-8859-1" From: Jean-Michel POURE Reply-To: jm.poure@freesurf.fr Organization: Translationforge To: "Samuel J. Sutjiono" , , Subject: Re: Transactional vs. Read-only (Retrieval) database Date: Thu, 11 Apr 2002 16:25:34 +0200 X-Mailer: KMail [version 1.4] References: <003801c1e15a$c03c5450$110a010a@headquarters.wcgroup.com> In-Reply-To: <003801c1e15a$c03c5450$110a010a@headquarters.wcgroup.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200204111625.34863.jm.poure@freesurf.fr> X-Archive-Number: 200204/445 X-Sequence-Number: 23161 Le Jeudi 11 Avril 2002 15:14, Samuel J. Sutjiono a =E9crit : > I'd like to get some opinions if there are any benefits (i.e. in terms of > performance) of creating two separate databases, one for transactions > (insert, update, delete) and the other one is for retrieval/search > (select). Dear Samuel, You do not need to create two separate databases. It is possible to write= =20 server-side code to copy static data (read-only) to other tables. This can = be=20 done using triggers and/or cron jobs. A more sopisticated solution would be to store LEFT JOIN query results in t= he=20 original table using triggers. For example : you have tables COMPANY and=20 EMPLOYEE. Instead of running a LEFT JOIN on EMPLOYEE->COMPANY, it is possib= le=20 to store COMPANY name in table employee using a trigger. Then, you don't need LEFT JOINS any more, simple SELECTs are enough, which = is=20 fantastic for heavy loaded databases. Same as for many operations. Try the EXPLAIN clause on a query. If too heav= y,=20 try to write some code to simplify the query using triggers... PostgreSQL is the most advanced open-source database available today. It ha= s=20 several server-side languages (plpgsql, plperl, plpython). Maybe you should= =20 try plpgsql which is easy to learn. Well-optimized, PostgreSQL can run 10 times faster than well-known open-sou= rce=20 databases. If you need to test server-side programming, do not hesitate to= =20 download and install pgAdmin2 (http://pgadmin.postgresql.org), PostgreSQL= =20 Windows GUI. Cheers, Jean-Michel From josh@agliodbs.com Thu Apr 11 23:16:30 2002 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id E0E0A4764AB; Thu, 11 Apr 2002 19:16:30 -0400 (EDT) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.9) with HTTP id 1236976; Thu, 11 Apr 2002 16:16:32 -0700 From: "Josh Berkus" Subject: Re: [SQL] Transactional vs. Read-only (Retrieval) database To: "Samuel J. Sutjiono" , , X-Mailer: CommuniGate Pro Web Mailer v.3.5.9 Date: Thu, 11 Apr 2002 16:16:32 -0700 Message-ID: In-Reply-To: <003801c1e15a$c03c5450$110a010a@headquarters.wcgroup.com> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200204/606 X-Sequence-Number: 23321 Sam, > I'd like to get some opinions if there are any benefits (i.e. in > terms of performance) of creating two separate databases, one for > transactions (insert, update, delete) and the other one is for > retrieval/search (select). No. Not in Postgresql, unless you wanted to get really exotic. Also, keep in mind that you cannot join tables from seperate databases in postgresql. There are *lots* of performance advantages to be had by managing disk access and memory carefully. But not through seperate databases. -Josh Berkus From ssutjiono@wc-group.com Fri Apr 12 14:57:40 2002 Received: from qmsql.netservers.net (hpmail2.netservers.net [209.196.128.168]) by postgresql.org (Postfix) with SMTP id 80A874759F2 for ; Fri, 12 Apr 2002 10:57:40 -0400 (EDT) Received: (qmail 11824 invoked from network); 12 Apr 2002 14:57:44 -0000 Received: from unknown (HELO AARON) (66.26.63.73) by hpmail2.netservers.net with SMTP; 12 Apr 2002 14:57:44 -0000 Message-ID: <00ce01c1e231$929619f0$110a010a@headquarters.wcgroup.com> From: "Samuel J. Sutjiono" To: "Josh Berkus" , , References: Subject: Re: [SQL] Transactional vs. Read-only (Retrieval) database Date: Fri, 12 Apr 2002 10:51:47 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200204/503 X-Sequence-Number: 23218 Hi Josh, Thank you very much for your input. About managing disk access and memory, do you know how to ensure the "read-only (to do the search on)" tables(i.e. product catalog) loaded and stay loaded in memory (provided buffer size is big enough for those tables) ? Are you familiar with RAMDISK ? On different subject, do you know how to monitor the number of connections in PostgreSQL ? Regards, Samuel ----- Original Message ----- From: "Josh Berkus" To: "Samuel J. Sutjiono" ; ; Sent: Thursday, April 11, 2002 7:16 PM Subject: Re: [SQL] Transactional vs. Read-only (Retrieval) database > Sam, > > > I'd like to get some opinions if there are any benefits (i.e. in > > terms of performance) of creating two separate databases, one for > > transactions (insert, update, delete) and the other one is for > > retrieval/search (select). > > No. Not in Postgresql, unless you wanted to get really exotic. Also, > keep in mind that you cannot join tables from seperate databases in > postgresql. > > There are *lots* of performance advantages to be had by managing disk > access and memory carefully. But not through seperate databases. > > -Josh Berkus > From josh@agliodbs.com Fri Apr 12 17:12:06 2002 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id 81545475914 for ; Fri, 12 Apr 2002 13:12:06 -0400 (EDT) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.9) with HTTP id 1320478; Fri, 12 Apr 2002 10:12:04 -0700 From: "Josh Berkus" Subject: Re: Transactional vs. Read-only (Retrieval) database To: "Samuel J. Sutjiono" , X-Mailer: CommuniGate Pro Web Mailer v.3.5.9 Date: Fri, 12 Apr 2002 10:12:04 -0700 Message-ID: In-Reply-To: <00ce01c1e231$929619f0$110a010a@headquarters.wcgroup.com> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200204/153 X-Sequence-Number: 7418 Samuel, > About managing disk access and memory, do you know how to ensure the > "read-only (to do the search on)" tables(i.e. product catalog) loaded > and > stay loaded in memory (provided buffer size is big enough for those > tables) > ? Are you familiar with RAMDISK ? Yeah, I know about doing this with MySQL. However, Postgres is a transactional database (MySQL is not), and as such it's a little trickier. My suggestion would be to create the RAMDISK as a mounted volume, and then copy the tables there. For that matter, I'd put your transaction log (WAL_FILES) on a different RAMDISK, which would speed up reads and writes considerably. As far as I know, nobody has ever tried this before. It would be extremely risky ... if you lost power, the entire database would be dead and not retrieveable except from backup ... but in theory could be very, very fast. A much less experimental approach would be to install a really good RAID array for your machine on a very fast SCSI controller, or two. Where you go from here depends on what you have more of ... time or money. If it's time, please experiment with RAMDISKs and write up your tests for Techdocs. You can get a considerable amount of help from the lists if you promise to write up your results as a HOWTO. If you have more money than time, hire PostgreSQL Inc. or RedHat to help you build the server and maximize performance. If you have neither time nor money, I'd say use MySQL on a RAMDISK for the read-only tables and do your joins in your interface code. The RAMDISK technique is well documented and tested on MySQL, and after all that's what MySQL was built for ... fast, read-only access. > On different subject, do you know how to monitor the number of > connections > in PostgreSQL ? Depends on how you're connecting. -Josh Berkus