From balazs@neusolutions.com Sun Jul 13 06:36:13 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id C4971CB56E for ; Sun, 13 Jul 2003 06:36:13 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 37973-03 for ; Sun, 13 Jul 2003 06:36:03 +0000 (GMT) Received: from v2.intertune.com (unknown [209.75.26.20]) by svr1.postgresql.org (Postfix) with ESMTP id 4336ACB56A for ; Sun, 13 Jul 2003 03:35:41 -0300 (ADT) Received: from home01 (66-75-1-38.san.rr.com [66.75.1.38]) (authenticated) by v2.intertune.com (8.11.6/8.11.6) with ESMTP id h6D6wXi29850 for ; Sat, 12 Jul 2003 23:58:33 -0700 From: "Balazs Wellisch" To: Subject: Pgsql - Red Hat Linux - VS MySQL VS MSSQL Date: Sat, 12 Jul 2003 23:35:38 -0700 Message-ID: <001001c34908$fc98bc60$0600a8c0@home01> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0011_01C348CE.5039E460" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2616 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=HTML_60_70, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200307/192 X-Sequence-Number: 2485 This is a multi-part message in MIME format. ------=_NextPart_000_0011_01C348CE.5039E460 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi all, I'm in the process of initiating a movement in our company to move towards open source software use. As part of this movement I will be recommending PostgreSQL as an alternative to the currently used MSSQL. I'm going with PostgreSQL over MySQL because of the much more complete feature set it provides. (After having used MSSQL for quite some time not having triggers, foreign keys, sub selects, etc. is not an option.) However, to be able to justify the move I will have to demonstrate that PostgreSQL is up to par with MSSQL and MySQL when it comes to performance. After having read through the docs and the lists it seems obvious that PostgreSQL is not configured for high performance out of the box. I don't have months to learn the ins and outs of PostgreSQL performance tuning so I looked around to see if there are any preconfigured solutions out there. I found that Red Hat Database 2.1 comes with PostgreSQL installed. However, as far as I can tell it comes with postgreSQL 7.2 and it requires Red Hat 8.0 or Red Hat Advanced Server which is based on Red Hat 7.2. Would I be better off installing Red Hat 9.0 and PostgreSQL 7.3 and try to performance tune the installation myself, or should I buy Red Hat Advanced Server and install Red Hat Database 2.1? (Let's say money is no object) So, does anyone here have any experience using RH AS and DB 2.1? Any advice would be much appreciated. TIA Balazs ------=_NextPart_000_0011_01C348CE.5039E460 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi all,

 

I’m in the process of initiating a movement in our company to move towards open source software use. As part of this movement I will be recommending PostgreSQL as an alternati= ve to the currently used MSSQL. I’m going with Postgre= SQL over MySQL because of the much more complete fe= ature set it provides. (After having used MSSQL for quite some time not having triggers, foreign keys, sub selects, etc. is not an option.)

 

However, to be able to justify the move I will have to demonstrate that PostgreSQL is up to par with M= SSQL and MySQL when it comes to performance. After h= aving read through the docs and the lists it seems obvious that PostgreSQL is not configured for high performance out of the box. I don’t have months to learn the ins and outs of PostgreSQL performance tuning so I looked around to see if there are any preconfigured solutions out there.

 

I found that Red Hat Database 2.1 comes with PostgreSQL installed. However, as far as I can tell it comes with postgreSQL 7.2 and it requires Red H= at 8.0 or Red Hat Advanced Server which is based on Red Hat 7.2. Would I be better= off installing Red Hat 9.0 and PostgreSQL 7.3 and t= ry to performance tune the installation myself, or sho= uld I buy Red Hat Advanced Server and install Red Hat Database 2.1? (Let’s = say money is no object)

 

So, does anyone here have any experience using = RH = = AS a= nd DB 2.1?

 

Any advice would be much appreciated.<= /font>

 

TIA

 

Balazs

 

------=_NextPart_000_0011_01C348CE.5039E460-- From shridhar_daithankar@persistent.co.in Sun Jul 13 10:20:42 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id 2EF28CB9B0 for ; Sun, 13 Jul 2003 10:20:42 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 04747-09 for ; Sun, 13 Jul 2003 10:21:00 +0000 (GMT) Received: from smtp.pspl.co.in (unknown [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id 57C55CB95C for ; Sun, 13 Jul 2003 07:20:39 -0300 (ADT) Received: (from root@localhost) by smtp.pspl.co.in (8.12.9/8.12.9) id h6DANoVG011387 for ; Sun, 13 Jul 2003 15:53:50 +0530 Received: from daithan.intranet.pspl.co.in (daithan.intranet.pspl.co.in [192.168.7.161]) (authenticated bits=0) by smtp.pspl.co.in (8.12.9/8.12.9) with ESMTP id h6DANoKr011374 for ; Sun, 13 Jul 2003 15:53:50 +0530 From: Shridhar Daithankar To: Subject: Re: Pgsql - Red Hat Linux - VS MySQL VS MSSQL Date: Sun, 13 Jul 2003 15:51:57 +0530 User-Agent: KMail/1.5.2 References: <001001c34908$fc98bc60$0600a8c0@home01> In-Reply-To: <001001c34908$fc98bc60$0600a8c0@home01> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200307131551.57688.shridhar_daithankar@nospam.persistent.co.in> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/194 X-Sequence-Number: 2487 On Sunday 13 July 2003 12:05, Balazs Wellisch wrote: > Hi all, > However, to be able to justify the move I will have to demonstrate that > PostgreSQL is up to par with MSSQL and MySQL when it comes to > performance. After having read through the docs and the lists it seems > obvious that PostgreSQL is not configured for high performance out of > the box. I don't have months to learn the ins and outs of PostgreSQL > performance tuning so I looked around to see if there are any > preconfigured solutions out there. If postgresql performance is going to be a concern, concurrency considerations with mysql will be even bigger concern. Postgresql can be tuned. For achieving good concurrency with mysql, you might have to redesign your app. In general, this list can help you to tune the things. Shouldn't be that big concern. > > I found that Red Hat Database 2.1 comes with PostgreSQL installed. > However, as far as I can tell it comes with postgreSQL 7.2 and it > requires Red Hat 8.0 or Red Hat Advanced Server which is based on Red > Hat 7.2. Would I be better off installing Red Hat 9.0 and PostgreSQL 7.3 > and try to performance tune the installation myself, or should I buy Red > Hat Advanced Server and install Red Hat Database 2.1? (Let's say money > is no object) I would rather vote for RH-AS with postgresql 7.4 devel. Former for it's big-app tunings out of the box and later for it's performance. Of course best way is to try it out yourself. Even vanilaa distro. on good hardware should be plenty good.. Shridhar From mail@joeconway.com Sun Jul 13 17:36:10 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 8FB0CCBA74 for ; Sun, 13 Jul 2003 17:36:10 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 33360-10 for ; Sun, 13 Jul 2003 17:36:32 +0000 (GMT) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by svr1.postgresql.org (Postfix) with ESMTP id 3394CCBA70 for ; Sun, 13 Jul 2003 14:36:09 -0300 (ADT) Received: from [192.168.5.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.0.6) with ESMTP-TLS id 1823507; Sun, 13 Jul 2003 10:03:34 -0700 Message-ID: <3F119743.2050803@joeconway.com> Date: Sun, 13 Jul 2003 10:30:43 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Balazs Wellisch Cc: pgsql-performance@postgresql.org Subject: Re: Pgsql - Red Hat Linux - VS MySQL VS MSSQL References: <001001c34908$fc98bc60$0600a8c0@home01> In-Reply-To: <001001c34908$fc98bc60$0600a8c0@home01> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/196 X-Sequence-Number: 2489 Balazs Wellisch wrote: > I don't have months to learn the ins and outs of PostgreSQL > performance tuning so I looked around to see if there are any > preconfigured solutions out there. I don't know of a preconfigured solution. Generally speaking, the best configuration will be highly dependent on your hardware, data, and application. > Hat Advanced Server and install Red Hat Database 2.1? (Let's say money > is no object) There are many Linux and other OS distributions that will work just fine. You may need to tweak a few kernel configuration parameters, but that's not too difficult; see: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=kernel-resources.html I would *not* use the default version of Postgres shipped with any particular distribution. Use 7.3.3 because that is the latest released version. Or, as Shridhar mentioned in his post, the are a number of pretty significant performance improvements in 7.4 (which is in feature freeze and scheduled to go into beta on 21 July). If you are in an exploratory/test phase rather than production right now, I'd say use the 7.4 beta for your comparisons. If money is truly not a problem, but time is, my advice is to hire a consultant. There are probably several people on this list that can fill that role for you. Otherwise read the archives and ask lots of specific questions. Joe From balazs@neusolutions.com Sun Jul 13 19:30:57 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 5C31DCBAA8 for ; Sun, 13 Jul 2003 19:30:57 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 39863-07 for ; Sun, 13 Jul 2003 19:30:47 +0000 (GMT) Received: from v2.intertune.com (unknown [209.75.26.20]) by svr1.postgresql.org (Postfix) with ESMTP id 9C7CECB539 for ; Sun, 13 Jul 2003 16:30:24 -0300 (ADT) Received: from home01 (66-75-1-38.san.rr.com [66.75.1.38]) (authenticated) by v2.intertune.com (8.11.6/8.11.6) with ESMTP id h6DJrVi04001 for ; Sun, 13 Jul 2003 12:53:31 -0700 Message-ID: <007301c34975$41874050$0600a8c0@home01> From: "Balazs Wellisch" To: References: <001001c34908$fc98bc60$0600a8c0@home01> <200307131551.57688.shridhar_daithankar@nospam.persistent.co.in> Subject: Re: Pgsql - Red Hat Linux - VS MySQL VS MSSQL Date: Sun, 13 Jul 2003 12:30:44 -0700 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.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/199 X-Sequence-Number: 2492 > On Sunday 13 July 2003 12:05, Balazs Wellisch wrote: > > Hi all, > > However, to be able to justify the move I will have to demonstrate that > > PostgreSQL is up to par with MSSQL and MySQL when it comes to > > performance. After having read through the docs and the lists it seems > > obvious that PostgreSQL is not configured for high performance out of > > the box. I don't have months to learn the ins and outs of PostgreSQL > > performance tuning so I looked around to see if there are any > > preconfigured solutions out there. > > If postgresql performance is going to be a concern, concurrency > considerations with mysql will be even bigger concern. Postgresql can be > tuned. For achieving good concurrency with mysql, you might have to redesign > your app. > Yes, we still may use MySQL in certain situations, but we are looking at PostgreSQL for concurrency and other reasons such as the much more complete set of features it provides. And now that we found PostgreSQL Manager (http://www.ems-hitech.com/pgmanager) it's even up to par with MSSQL in ease of use! > In general, this list can help you to tune the things. Shouldn't be that big > concern. > That's good to hear! > > > > I found that Red Hat Database 2.1 comes with PostgreSQL installed. > > However, as far as I can tell it comes with postgreSQL 7.2 and it > > requires Red Hat 8.0 or Red Hat Advanced Server which is based on Red > > Hat 7.2. Would I be better off installing Red Hat 9.0 and PostgreSQL 7.3 > > and try to performance tune the installation myself, or should I buy Red > > Hat Advanced Server and install Red Hat Database 2.1? (Let's say money > > is no object) > > I would rather vote for RH-AS with postgresql 7.4 devel. Former for it's > big-app tunings out of the box and later for it's performance. > Could you enumerate what those settings are? What should I be looking at as far as kernel, file system, etc. goes? > Of course best way is to try it out yourself. Even vanilaa distro. on good > hardware should be plenty good.. > > Shridhar > Thank you for your advice. It's much appriciated. Balazs From balazs@neusolutions.com Sun Jul 13 19:42:11 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 75D3ACBAAF for ; Sun, 13 Jul 2003 19:42:11 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 80384-02 for ; Sun, 13 Jul 2003 19:42:32 +0000 (GMT) Received: from v2.intertune.com (unknown [209.75.26.20]) by svr1.postgresql.org (Postfix) with ESMTP id 2C012CBAAE for ; Sun, 13 Jul 2003 16:42:09 -0300 (ADT) Received: from home01 (66-75-1-38.san.rr.com [66.75.1.38]) (authenticated) by v2.intertune.com (8.11.6/8.11.6) with ESMTP id h6DK5Gi04031 for ; Sun, 13 Jul 2003 13:05:16 -0700 Message-ID: <008801c34976$e58a4ac0$0600a8c0@home01> From: "Balazs Wellisch" To: References: <001001c34908$fc98bc60$0600a8c0@home01> <1058100557.19798.20.camel@haggis> Subject: Re: Pgsql - Red Hat Linux - VS MySQL VS MSSQL Date: Sun, 13 Jul 2003 12:42:29 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-13" Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/200 X-Sequence-Number: 2493 > On Sun, 2003-07-13 at 01:35, Balazs Wellisch wrote: > > Hi all, > > > > > > > > Iÿm in the process of initiating a movement in our company to move > > towards open source software use. As part of this movement I will be > > recommending PostgreSQL as an alternative to the currently used MSSQL. > > Iÿm going with PostgreSQL over MySQL because of the much more complete > > feature set it provides. (After having used MSSQL for quite some time > > not having triggers, foreign keys, sub selects, etc. is not an > > option.) > > Note that I've read a couple of times from Tom Lane (one of the > core team) that FKs are a serous performance drag, so I'd drop > them after the s/w has been in production long enough to work > out the kinks. > That's interesting, I didn't know that. Any idea how much of a performance drag we're talking about? > > However, to be able to justify the move I will have to demonstrate > > that PostgreSQL is up to par with MSSQL and MySQL when it comes to > > performance. After having read through the docs and the lists it seems > > obvious that PostgreSQL is not configured for high performance out of > > the box. I donÿt have months to learn the ins and outs of PostgreSQL > > performance tuning so I looked around to see if there are any > > preconfigured solutions out there. > > http://www.varlena.com/GeneralBits/ > http://www.varlena.com/GeneralBits/Tidbits/perf.html > http://www.varlena.com/GeneralBits/Tidbits/annotated_conf_e.html > Those links are great!!! Thank you for bringing them to my attantion. And a BIG thank you to the authors (Josh Berkus & Shridhar Daithankar) for making this available. I've been looking for an authoritative and comprehensive source for performance tuning tips but haven't found much except for little tidbits here and there. This is very nice. > Me, I'd install Debian, but I understand the comfort level created > by RH. > Don't know much about Debian, but we've been working with RH for years. I've had nothing but good experiences with them. (Except maybe for RH8) The new Enterprise direction they're going in is exectly what we need. Longer testing cycles and better tuned distributions are good for businesses like us. We don't necessarily need the latest and greates we need the latest and most stable to guarantee the highest return on our investment. But, this discussion is for another list... :) Thanks for your advice. This list has proved to be a great asset so far. Balazs From balazs@neusolutions.com Sun Jul 13 19:50:48 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id 330B6CBAA8 for ; Sun, 13 Jul 2003 19:50:48 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 94227-09 for ; Sun, 13 Jul 2003 19:51:08 +0000 (GMT) Received: from v2.intertune.com (unknown [209.75.26.20]) by svr1.postgresql.org (Postfix) with ESMTP id C08CFCB539 for ; Sun, 13 Jul 2003 16:50:45 -0300 (ADT) Received: from home01 (66-75-1-38.san.rr.com [66.75.1.38]) (authenticated) by v2.intertune.com (8.11.6/8.11.6) with ESMTP id h6DKDri04055 for ; Sun, 13 Jul 2003 13:13:53 -0700 Message-ID: <009901c34978$19697270$0600a8c0@home01> From: "Balazs Wellisch" To: References: <20030713110341.A16172@bacon> <20030713143721.A18011@bacon> Subject: Re: Pgsql - Red Hat Linux - VS MySQL VS MSSQL Date: Sun, 13 Jul 2003 12:51:02 -0700 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.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/201 X-Sequence-Number: 2494 > The most important thing seems to be to increase shared_buffers. On my > RH7.3 machine here, Linux is configured with shmmax = 32MB which allows me > a value of just under 4000 for shared_buffers (3900 works, 3950 doesn't). > If your selects return large amounts of data, you'll probably also need to > increase sort_mem (I use a value of 1024 so a query would have to return > more that 1MB of data before the sort (assuming there is a order by clause > to cause a sort) starts paging stuff out disk. > > > > I found that Red Hat Database 2.1 comes with PostgreSQL installed. > > However, as far as I can tell it comes with postgreSQL 7.2 and it > > requires Red Hat 8.0 or Red Hat Advanced Server which is based on Red > > Hat 7.2. Would I be better off installing Red Hat 9.0 and PostgreSQL 7.3 > > and try to performance tune the installation myself, or should I buy Red > > Hat Advanced Server and install Red Hat Database 2.1? (Let's say money > > is no object) > > > Alternatively, you simply compile 7.3.3 from source. I've upgraded most my > machines that way. > Unfortunatelly, compiling from source is not really an option for us. We use RPMs only to ease the installation and upgrade process. We have over a hundred servers to maintaine and having to compile and recompile software everytime a new release comes out would be waaaaay too much work. > > > > So, does anyone here have any experience using RH AS and DB 2.1? > > Are RH still selling DB 2.1? I can't find it listed on their web site. > -- Yes, it's available for free download. The documentation is here: http://www.redhat.com/docs/manuals/database/. I'd welcome your oppinions on this product. Thank you for your comments. Balazs From balazs@neusolutions.com Sun Jul 13 20:04:29 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id 4207DCBABC for ; Sun, 13 Jul 2003 20:04:29 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 39361-01 for ; Sun, 13 Jul 2003 20:04:50 +0000 (GMT) Received: from v2.intertune.com (unknown [209.75.26.20]) by svr1.postgresql.org (Postfix) with ESMTP id DB24BCB539 for ; Sun, 13 Jul 2003 17:04:27 -0300 (ADT) Received: from home01 (66-75-1-38.san.rr.com [66.75.1.38]) (authenticated) by v2.intertune.com (8.11.6/8.11.6) with ESMTP id h6DKRZi04079 for ; Sun, 13 Jul 2003 13:27:35 -0700 Message-ID: <009d01c3497a$039b8f30$0600a8c0@home01> From: "Balazs Wellisch" To: References: <001001c34908$fc98bc60$0600a8c0@home01> <3F119743.2050803@joeconway.com> Subject: Re: Pgsql - Red Hat Linux - VS MySQL VS MSSQL Date: Sun, 13 Jul 2003 13:04:48 -0700 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.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/203 X-Sequence-Number: 2496 > There are many Linux and other OS distributions that will work just > fine. You may need to tweak a few kernel configuration parameters, but > that's not too difficult; see: > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=kernel-resources.html > Yes, I looked at the online documentation but found it a little too generic. Although it gives me good idea of where to look to adjust performance related parameters I need a little more specific advise. I just don't have the time to tweak and test different configurations for months to see what works and what doesn't. Ideally, I'd love to run my own benchmarks and become an expert at postgresql, but unfortunately in the real world I have deadlines to meet and clients to appease. So, I was hoping someone would have some real world experiences to share running postgresql on RH in an enterprise environment. > I would *not* use the default version of Postgres shipped with any > particular distribution. Use 7.3.3 because that is the latest released > version. Or, as Shridhar mentioned in his post, the are a number of > pretty significant performance improvements in 7.4 (which is in feature > freeze and scheduled to go into beta on 21 July). If you are in an > exploratory/test phase rather than production right now, I'd say use the > 7.4 beta for your comparisons. > Well, I could start by testing 7.4, however I'd have to go back to the stable version once we're ready to use it a production environment. So, I might as well stick with eveluating the production version. > If money is truly not a problem, but time is, my advice is to hire a > consultant. There are probably several people on this list that can fill > that role for you. Otherwise read the archives and ask lots of specific > questions. > Once we're ready to go with postgresql in a production environment we may indeed need to hire a consultant. Any suggestions whom I should contact? (We're in the San Diego area) Thank you for your advice. Balazs From mail@joeconway.com Sun Jul 13 20:30:55 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 6B5D1CBACB for ; Sun, 13 Jul 2003 20:30:55 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 85324-05 for ; Sun, 13 Jul 2003 20:31:15 +0000 (GMT) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by svr1.postgresql.org (Postfix) with ESMTP id CB733CBAC4 for ; Sun, 13 Jul 2003 17:30:52 -0300 (ADT) Received: from [192.168.5.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.0.6) with ESMTP-TLS id 1823566; Sun, 13 Jul 2003 12:58:15 -0700 Message-ID: <3F11C037.3070008@joeconway.com> Date: Sun, 13 Jul 2003 13:25:27 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Balazs Wellisch Cc: pgsql-performance@postgresql.org Subject: Re: Pgsql - Red Hat Linux - VS MySQL VS MSSQL References: <001001c34908$fc98bc60$0600a8c0@home01> <3F119743.2050803@joeconway.com> <009d01c3497a$039b8f30$0600a8c0@home01> In-Reply-To: <009d01c3497a$039b8f30$0600a8c0@home01> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/204 X-Sequence-Number: 2497 Balazs Wellisch wrote: >>I would *not* use the default version of Postgres shipped with any >>particular distribution. Use 7.3.3 because that is the latest released >>version. Or, as Shridhar mentioned in his post, the are a number of >>pretty significant performance improvements in 7.4 (which is in feature >>freeze and scheduled to go into beta on 21 July). If you are in an >>exploratory/test phase rather than production right now, I'd say use the >>7.4 beta for your comparisons. > > Well, I could start by testing 7.4, however I'd have to go back to the > stable version once we're ready to use it a production environment. So, I > might as well stick with eveluating the production version. How soon do you think you'll be in production? PostgreSQL beta testing usually seems to run about 2 months or so -- if you won't be in production before October, it is a good bet that Postgres 7.4 will be out or at least in release candidate by then. But it really depends on your specific application. If you use lots of "WHERE foo IN (SELECT ...)" type queries, you'll need to rewrite them in 7.3.3 or earlier, but in 7.4 they will probably work fine. Also, if you do much in the way of aggregate queries for reporting, 7.4 will likely give you a significant performance boost. >>If money is truly not a problem, but time is, my advice is to hire a >>consultant. There are probably several people on this list that can fill >>that role for you. Otherwise read the archives and ask lots of specific >>questions. > > Once we're ready to go with postgresql in a production environment we may > indeed need to hire a consultant. Any suggestions whom I should contact? > (We're in the San Diego area) > Um, actually, I live in the San Diego area ;-) Joe From jim@nasby.net Sun Jul 13 22:31:55 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 724C3CB56A for ; Sun, 13 Jul 2003 22:31:55 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 14912-01 for ; Sun, 13 Jul 2003 22:32:12 +0000 (GMT) Received: from flake.decibel.org (unknown [66.143.173.58]) by svr1.postgresql.org (Postfix) with SMTP id A03A7CBB14 for ; Sun, 13 Jul 2003 19:31:50 -0300 (ADT) Received: (qmail 75849 invoked by uid 1001); 13 Jul 2003 22:32:14 -0000 Date: Sun, 13 Jul 2003 17:32:14 -0500 From: "Jim C. Nasby" To: Balazs Wellisch Cc: pgsql-performance@postgresql.org Subject: Re: Pgsql - Red Hat Linux - VS MySQL VS MSSQL Message-ID: <20030713223214.GJ5877@nasby.net> References: <20030713110341.A16172@bacon> <20030713143721.A18011@bacon> <009901c34978$19697270$0600a8c0@home01> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <009901c34978$19697270$0600a8c0@home01> User-Agent: Mutt/1.4.1i X-Operating-System: FreeBSD 4.8-RELEASE i386 X-Distributed: Join the Effort! http://www.distributed.net X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/206 X-Sequence-Number: 2499 On Sun, Jul 13, 2003 at 12:51:02PM -0700, Balazs Wellisch wrote: > > Alternatively, you simply compile 7.3.3 from source. I've upgraded most my > > machines that way. > > > > Unfortunatelly, compiling from source is not really an option for us. We use > RPMs only to ease the installation and upgrade process. We have over a > hundred servers to maintaine and having to compile and recompile software > everytime a new release comes out would be waaaaay too much work. If you aren't settled on OS yet, take a look at FreeBSD, or one of the linuxes that have better app management. Keeping pgsql up-to-date using ports on FreeBSD is pretty painless (for that matter, so is keeping the OS itself up-to-date). -- Jim C. Nasby, Database Consultant jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" From shridhar_daithankar@persistent.co.in Mon Jul 14 06:36:19 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 9F2CECBB45 for ; Mon, 14 Jul 2003 06:36:19 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 55299-04 for ; Mon, 14 Jul 2003 06:36:40 +0000 (GMT) Received: from smtp.pspl.co.in (unknown [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id 922A9CBB38 for ; Mon, 14 Jul 2003 03:36:14 -0300 (ADT) Received: (from root@localhost) by smtp.pspl.co.in (8.12.9/8.12.9) id h6E6dhNV013042 for ; Mon, 14 Jul 2003 12:09:43 +0530 Received: from daithan.intranet.pspl.co.in (daithan.intranet.pspl.co.in [192.168.7.161]) (authenticated bits=0) by smtp.pspl.co.in (8.12.9/8.12.9) with ESMTP id h6E6dgKr013029 for ; Mon, 14 Jul 2003 12:09:42 +0530 From: Shridhar Daithankar To: Subject: Re: Pgsql - Red Hat Linux - VS MySQL VS MSSQL Date: Mon, 14 Jul 2003 12:07:32 +0530 User-Agent: KMail/1.5.2 References: <20030713110341.A16172@bacon> <20030713143721.A18011@bacon> <009901c34978$19697270$0600a8c0@home01> In-Reply-To: <009901c34978$19697270$0600a8c0@home01> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200307141207.32187.shridhar_daithankar@nospam.persistent.co.in> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/214 X-Sequence-Number: 2507 On Monday 14 July 2003 01:21, Balazs Wellisch wrote: > Unfortunatelly, compiling from source is not really an option for us. We > use RPMs only to ease the installation and upgrade process. We have over a > hundred servers to maintaine and having to compile and recompile software > everytime a new release comes out would be waaaaay too much work. Use checkinstall. Simple. Google for more information. Making your own rpms isn't that big deal..:-) From andrew@libertyrms.info Mon Jul 14 11:43:38 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id DF78CCBC91 for ; Mon, 14 Jul 2003 11:43:38 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 74266-05 for ; Mon, 14 Jul 2003 11:43:59 +0000 (GMT) Received: from mail.libertyrms.com (unknown [209.167.124.227]) by svr1.postgresql.org (Postfix) with ESMTP id 8123ECBC7B for ; Mon, 14 Jul 2003 08:43:36 -0300 (ADT) Received: from [10.1.2.130] (helo=dba2) by mail.libertyrms.com with esmtp (Exim 3.22 #3 (Debian)) id 19c1kP-0004cm-00 for ; Mon, 14 Jul 2003 07:44:01 -0400 Received: by dba2 (Postfix, from userid 1019) id E22FBCEC9; Mon, 14 Jul 2003 07:44:24 -0400 (EDT) Date: Mon, 14 Jul 2003 07:44:24 -0400 From: Andrew Sullivan To: pgsql-performance@postgresql.org Subject: Re: Pgsql - Red Hat Linux - VS MySQL VS MSSQL Message-ID: <20030714114424.GH24166@libertyrms.info> Mail-Followup-To: Andrew Sullivan , pgsql-performance@postgresql.org References: <001001c34908$fc98bc60$0600a8c0@home01> <1058100557.19798.20.camel@haggis> <008801c34976$e58a4ac0$0600a8c0@home01> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <008801c34976$e58a4ac0$0600a8c0@home01> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/216 X-Sequence-Number: 2509 On Sun, Jul 13, 2003 at 12:42:29PM -0700, Balazs Wellisch wrote: > > On Sun, 2003-07-13 at 01:35, Balazs Wellisch wrote: > > Note that I've read a couple of times from Tom Lane (one of the > > core team) that FKs are a serous performance drag, so I'd drop > > them after the s/w has been in production long enough to work > > out the kinks. > > > > That's interesting, I didn't know that. Any idea how much of a performance > drag we're talking about? Foreign keys in any database are going to cost you something, because they require a lookup in other tables. The big hit from FKs in PostgreSQL used to be that they caused deadlocks in older versions. I _think_ this is fixed by default in 7.3.3; if not, there's a patch floating around for the problem. The repair is definitely in 7.4. That said, if speed is your goal, FKs are always going to be a cost for you. OTOH, people who try to handle this sort of thing in the application come to regret it. You probably want to look somewhere else to solve your performance difficulties from FKs. A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada M2P 2A8 +1 416 646 3304 x110 From andrew@libertyrms.info Mon Jul 14 11:44:45 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 28039CBF0B for ; Mon, 14 Jul 2003 11:44:45 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 87586-01 for ; Mon, 14 Jul 2003 11:45:05 +0000 (GMT) Received: from mail.libertyrms.com (unknown [209.167.124.227]) by svr1.postgresql.org (Postfix) with ESMTP id E78B7CBF7C for ; Mon, 14 Jul 2003 08:44:42 -0300 (ADT) Received: from [10.1.2.130] (helo=dba2) by mail.libertyrms.com with esmtp (Exim 3.22 #3 (Debian)) id 19c1lU-0004d2-00 for ; Mon, 14 Jul 2003 07:45:08 -0400 Received: by dba2 (Postfix, from userid 1019) id 8901ACEC9; Mon, 14 Jul 2003 07:45:31 -0400 (EDT) Date: Mon, 14 Jul 2003 07:45:31 -0400 From: Andrew Sullivan To: pgsql-performance@postgresql.org Subject: Re: Pgsql - Red Hat Linux - VS MySQL VS MSSQL Message-ID: <20030714114531.GI24166@libertyrms.info> Mail-Followup-To: Andrew Sullivan , pgsql-performance@postgresql.org References: <20030713110341.A16172@bacon> <20030713143721.A18011@bacon> <009901c34978$19697270$0600a8c0@home01> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <009901c34978$19697270$0600a8c0@home01> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/217 X-Sequence-Number: 2510 On Sun, Jul 13, 2003 at 12:51:02PM -0700, Balazs Wellisch wrote: > > Unfortunatelly, compiling from source is not really an option for us. We use > RPMs only to ease the installation and upgrade process. We have over a > hundred servers to maintaine and having to compile and recompile software > everytime a new release comes out would be waaaaay too much work. It's not clear that the RPMs will help you in ease of upgrade. More precisely, be real sure you dump your database before upgrading major versions (e.g. 7.3.x to 7.4.x). A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada M2P 2A8 +1 416 646 3304 x110 From paul@tmsl.demon.co.uk Mon Jul 14 14:07:50 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id A6E1DCBFF7 for ; Mon, 14 Jul 2003 14:07:50 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 80422-10 for ; Mon, 14 Jul 2003 14:08:10 +0000 (GMT) Received: from anchor-post-39.mail.demon.net (anchor-post-39.mail.demon.net [194.217.242.80]) by svr1.postgresql.org (Postfix) with ESMTP id 598C4CBFF6 for ; Mon, 14 Jul 2003 11:07:48 -0300 (ADT) Received: from tmsl-adsl.demon.co.uk ([80.177.114.181] helo=bacon.tmsl.demon.co.uk) by anchor-post-39.mail.demon.net with esmtp (Exim 3.36 #2) id 19c3yT-00018C-0U; Mon, 14 Jul 2003 15:06:41 +0100 Date: Mon, 14 Jul 2003 15:06:39 +0100 From: Paul Thomas To: Balazs Wellisch Cc: "pgsql-performance @ postgresql . org" Subject: Re: Pgsql - Red Hat Linux - VS MySQL VS MSSQL Message-ID: <20030714150639.A20427@bacon> References: <20030714122050.C19680@bacon> Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset=ISO-8859-1 In-Reply-To: <20030714122050.C19680@bacon>; from paul@tmsl.demon.co.uk on Mon, Jul 14, 2003 at 12:20:50 +0100 X-Mailer: Balsa 1.2.3 Lines: 30 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/219 X-Sequence-Number: 2512 On 13/07/2003 20:51 Balazs Wellisch wrote: > [snip] > > > So, does anyone here have any experience using RH AS and DB 2.1? > > > > Are RH still selling DB 2.1? I can't find it listed on their web site. > > -- > > Yes, it's available for free download. The documentation is here: > http://www.redhat.com/docs/manuals/database/. I'd welcome your oppinions > on > this product. > > Thank you for your comments. It looks like they just wrote a number of GUI versions of the command line utilities. From what I can tell, its still a standard postgresql database behind the scenes. -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+