Thread: Pgsql - Red Hat Linux - VS MySQL VS MSSQL

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

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

> 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

> 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

> 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

> 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

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

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?"

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..:-)

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
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

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
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

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   |
+------------------------------+---------------------------------------------+