Thread: Postgresql performance and tuning questions
Greetings,
We are beginning a project to use Postgresql 7.4.2 on zLinux SLES9
The application of interest is uPortal 2.4.3.
I'm interested in learning about any changes you've made to your Postgresql DB to prepare it for uPortal? What sizing changes were made? Were there any performance and tuning changes made than what was delivered with Linux? If so, what were the metrics used to determine the appropriate settings? At what workloads levels have you been seeing?
I'd like to get this type of information from any organization that uses this application with Postgresql. I'd will be willing to share my results and findings as well.
TIA, Bob
Bob Given
Database Administrator
Boston University
617-353-7906
bgiven@bu.edu
"Given, Robert A" <bgiven@bu.edu> writes:
> We are beginning a project to use Postgresql 7.4.2 on zLinux SLES9
> The application of interest is uPortal 2.4.3.
Why are you not starting with a reasonably up-to-date version of
Postgres?
regards, tom lane
Our philosophy is to use products that have been established, tested and
stable. We tend to not go with newer versions until they have time to
mature and have the bugs worked out. This is the version of the DB that
was packaged with the version of Linux we're using. So our thinking is
that we will go with that until we learn better. Our experience is more
with mainframes where habitually we proceed methodically. (That
translates to: slower than what we see with open source.)
Is there an impelling reason why we should migrate to a newer version
before we begin to use, observe, and experience Postgres of our own?
Thanks, Bob
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, February 10, 2006 10:16 AM
To: Given, Robert A
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgresql performance and tuning questions
"Given, Robert A" <bgiven@bu.edu> writes:
> We are beginning a project to use Postgresql 7.4.2 on zLinux SLES9 The
> application of interest is uPortal 2.4.3.
Why are you not starting with a reasonably up-to-date version of
Postgres?
regards, tom lane
"Given, Robert A" <bgiven@bu.edu> writes: > Our philosophy is to use products that have been established, tested and > stable. We tend to not go with newer versions until they have time to > mature and have the bugs worked out. This is the version of the DB that > was packaged with the version of Linux we're using. So our thinking is > that we will go with that until we learn better. It is time for you to learn better, right now. 7.4.2 is old and has numerous known bugs. There is a valid argument for sticking to the 7.4.* series, having to do with avoiding any possible application-compatibility issues for apps that've only been tested with 7.4.*, but there is no reason other than vendor laziness to still be on a two-year-old subrelease in the 7.4.* series. 7.4 is currently at 7.4.11 (by Tuesday it will be 7.4.12) and there are a significant number of security holes and risk-of-data-loss bugs fixed in those updates. See the release history at http://developer.postgresql.org/docs/postgres/release.html and then ask yourself if you still trust your Linux vendor's judgment so much. Or you can "learn better" the hard way, by losing data. Your choice. regards, tom lane
On Fri, 2006-02-10 at 09:36, Given, Robert A wrote: > Our philosophy is to use products that have been established, tested and > stable. We tend to not go with newer versions until they have time to > mature and have the bugs worked out. This is the version of the DB that > was packaged with the version of Linux we're using. So our thinking is > that we will go with that until we learn better. Our experience is more > with mainframes where habitually we proceed methodically. (That > translates to: slower than what we see with open source.) > > Is there an impelling reason why we should migrate to a newer version > before we begin to use, observe, and experience Postgres of our own? But you're not running the least buggy version of the 7.4 branch. The 7.4 branch is up to 7.4.9. If you are runing 7.4.2 then you haven't installed all the updates supplied by Suse for their distribution (or Suse backports bug fixes from 7.4.9 to 7.4.2, which I kinda doubt) which means you likely haven't upgraded ANY of the packages that came with it, which means your system, if it is put on the internet, will likely be hacked within days, if not hours. I would recommend looking into at least 8.0. It's been out for a year, it's quite stable, and has many features like point in time recovery that make it a very good choice for production. > Thanks, Bob > > > > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Friday, February 10, 2006 10:16 AM > To: Given, Robert A > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Postgresql performance and tuning questions > > "Given, Robert A" <bgiven@bu.edu> writes: > > We are beginning a project to use Postgresql 7.4.2 on zLinux SLES9 The > > > application of interest is uPortal 2.4.3. > > Why are you not starting with a reasonably up-to-date version of > Postgres? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
Zitat von Scott Marlowe <smarlowe@g2switchworks.com>: > But you're not running the least buggy version of the 7.4 branch. The > 7.4 branch is up to 7.4.9. If you are runing 7.4.2 then you haven't > installed all the updates supplied by Suse for their distribution (or > Suse backports bug fixes from 7.4.9 to 7.4.2, which I kinda doubt) which > means you likely haven't upgraded ANY of the packages that came with it, > which means your system, if it is put on the internet, will likely be > hacked within days, if not hours. SuSE always only do backports of fixes and never increase the version number. They only appended a self made "build" number which increases. So it is really possible that all the fixes are in while the version stays the same. > I would recommend looking into at least 8.0. It's been out for a year, > it's quite stable, and has many features like point in time recovery > that make it a very good choice for production. No doubt about this one. Regards Andreas
lst_hoe01@kwsoft.de writes:
> Zitat von Scott Marlowe <smarlowe@g2switchworks.com>:
>> But you're not running the least buggy version of the 7.4 branch. The
>> 7.4 branch is up to 7.4.9. If you are runing 7.4.2 then you haven't
>> installed all the updates supplied by Suse for their distribution (or
>> Suse backports bug fixes from 7.4.9 to 7.4.2, which I kinda doubt) which
>> means you likely haven't upgraded ANY of the packages that came with it,
>> which means your system, if it is put on the internet, will likely be
>> hacked within days, if not hours.
> SuSE always only do backports of fixes and never increase the version
> number. They only appended a self made "build" number which increases.
> So it is really possible that all the fixes are in while the version
> stays the same.
Considering that we only put bug fixes into stable branches, SuSE is not
doing anyone any favors by applying such a policy (if indeed that's what
they are doing). They're just making more work for themselves, as well
as making it harder for users to tell what's fixed or not fixed.
regards, tom lane
I do understand your school of thinking. But let me say this, from just a user point of view who had used 7.4.x, 8.0, and 8.1, I'd highly recommend to start off with 8.1. I can comment on performance in particular among other great stuff such as scalability, robustness etc. The performance in 8.1 compared with previous releases (even 8.0) is remarkable and frankly like the difference between day and light, specially when it comes to complex queries in large databases. From a technical point of view, you can view the docs to find out more about the great improvements with 8.1, such as http://www.postgresql.org/docs/whatsnew Just my two cents :) ---- Husam -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Given, Robert A Sent: Friday, February 10, 2006 7:37 AM To: Tom Lane Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Postgresql performance and tuning questions Our philosophy is to use products that have been established, tested and stable. We tend to not go with newer versions until they have time to mature and have the bugs worked out. This is the version of the DB that was packaged with the version of Linux we're using. So our thinking is that we will go with that until we learn better. Our experience is more with mainframes where habitually we proceed methodically. (That translates to: slower than what we see with open source.) Is there an impelling reason why we should migrate to a newer version before we begin to use, observe, and experience Postgres of our own? Thanks, Bob -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Friday, February 10, 2006 10:16 AM To: Given, Robert A Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Postgresql performance and tuning questions "Given, Robert A" <bgiven@bu.edu> writes: > We are beginning a project to use Postgresql 7.4.2 on zLinux SLES9 The > application of interest is uPortal 2.4.3. Why are you not starting with a reasonably up-to-date version of Postgres? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend ********************************************************************** This message contains confidential information intended only for the use of the addressee(s) named above and may containinformation that is legally privileged. If you are not the addressee, or the person responsible for delivering itto the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictlyprohibited. If you have received this message by mistake, please immediately notify us by replying to the messageand delete the original message immediately thereafter. Thank you. FADLD Tag **********************************************************************
On Thu, 2006-02-16 at 10:39, Tomeh, Husam wrote: > I do understand your school of thinking. But let me say this, from just > a user point of view who had used 7.4.x, 8.0, and 8.1, I'd highly > recommend to start off with 8.1. I can comment on performance in > particular among other great stuff such as scalability, robustness etc. > The performance in 8.1 compared with previous releases (even 8.0) is > remarkable and frankly like the difference between day and light, > specially when it comes to complex queries in large databases. From a > technical point of view, you can view the docs to find out more about > the great improvements with 8.1, such as > http://www.postgresql.org/docs/whatsnew And I'd like to add that of all the pieces of software I've ever used, none has consistently improved in terms of stability, performance, and features as well as the PostgreSQL project. Not one. It's the only project I've ever used that every new version has improved on all three of those areas every single time, and the gotchas from new versions have been small to non-existent. In fact, I'd say that bosses who were anti-new software versions have been far more of a problem than new versions of PostgreSQL has ever been. :) And I understand too, the aversion to new versions. I've been bitten by changes to new versions like php, apache, perl, java, and dozens of other packages. They are nothing like PostgreSQL.
On 2/12/06, lst_hoe01@kwsoft.de <lst_hoe01@kwsoft.de> wrote: > SuSE always only do backports of fixes and never increase the version > number. They only appended a self made "build" number which increases. > So it is really possible that all the fixes are in while the version > stays the same. Not true. SLES9 (SuSE Linux Enterprise 9), starts off with 7.4.2, but it is at 7.4.8 as of Service Pack 3. Their Online Updates would probably track the bugfix releases closer.