Thread: Optimzing Postgresql

Hi,

I am deciding between MySQL and Postgres. I'm leaning towards Postgres mainly due the widely publicized speed when using transactions. However, I am not able to find any good books / resources for tuning/ optimizing the database. Is there a book like "High Performance MySQL" for Postgres that teaches what the different parameters are and how to tune them?
Or do most techniques covered in the High Performance Mysql apply to Postgres too?

Thanks,

Ram

Ram Ravichandran wrote:
> Hi,
>
> I am deciding between MySQL and Postgres. I'm leaning towards Postgres
> mainly due the widely publicized speed when using transactions.
Everything except for a couple of actions in Postgresql are wrapped in
transactions and can be rollback,  you can not turn it off like in MySQL.
> However, I am not able to find any good books / resources for tuning/
> optimizing the database. Is there a book like "High Performance MySQL"
> for Postgres that teaches what the different parameters are and how to
> tune them?
> Or do most techniques covered in the High Performance Mysql apply to
> Postgres too?
I can not comment on a book i have never read so i have no idea what is
between the cover of said book.  I don't know if it is specific to MySQL
or is general enough to apply to all databases. I would think the book
is specific to MySQL.

Tunning Postgresql  performance is really quit painless just very time
consuming.
Greg Smith has written allot stuff that covers the parameters in postgresql
http://www.westnet.com/~gsmith/

Here's another resource.
http://www.postgresqldocs.org/wiki/Performance_Optimization

On May 24, 2008, at 11:57 AM, Ram Ravichandran wrote:

> Hi,
>
> I am deciding between MySQL and Postgres. I'm leaning towards
> Postgres mainly due the widely publicized speed when using
> transactions. However, I am not able to find any good books /
> resources for tuning/ optimizing the database. Is there a book like
> "High Performance MySQL" for Postgres that teaches what the
> different parameters are and how to tune them?

The postgresql manual is good.

http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm is a
five minute tuning overview.

http://www.powerpostgresql.com/PerfList/ is a good overview of basic
tuning, written for 8.0 but still pretty applicable.

Hang out on the pgsql-performance mailing list and see what other
people do - "How do I tune a database for X" comes up pretty
regularly, and gets good answers, so trolling through the mailing list
archive can give some very good advice.

>
> Or do most techniques covered in the High Performance Mysql apply to
> Postgres too?

Probably not. Mysql has a very different philosophy to postgresql. And
some approaches that are suggested to work around performance issue on
mysql may actually harm performance on other databases.

Cheers,
   Steve

On 5/24/08, Ram Ravichandran <ramkaka@gmail.com> wrote:
> I am deciding between MySQL and Postgres. I'm leaning towards Postgres
> mainly due the widely publicized speed when using transactions. However, I
> am not able to find any good books / resources for tuning/ optimizing the
> database. Is there a book like "High Performance MySQL" for Postgres that
> teaches what the different parameters are and how to tune them?
>  Or do most techniques covered in the High Performance Mysql apply to
> Postgres too?

There's no book, that I know, dedicated to tuning PostgreSQL
performance, but the PostgreSQL book by Korry Douglas has a big
section on tuning and statistics gathering:

  http://www.amazon.com/PostgreSQL-Developers-Library-Korry-Douglas/dp/0672327562

There are plenty of overlaps between tuning PostgreSQL and tuning any
other database, such as using the right RAID setup to optimize I/O.
PostgreSQL has a bunch of parameters for controlling buffer sizes,
write-ahead logging, sort memory and so on. Here's a decent overview:

  http://www.powerpostgresql.com/PerfList

When looking for material, make sure it's updated to the 8.x series,
which drastically changed the way PostgreSQL manages its cache
buffers.

Alexander.

Steve Atkins wrote:

> Hang out on the pgsql-performance mailing list and see what other people
> do - "How do I tune a database for X" comes up pretty regularly, and
> gets good answers, so trolling through the mailing list archive can give
> some very good advice.
>

Aside from the hardware and server options that can be adjusted - the
sql you use can make a big difference to performance as well.

That goes from good table design and index usage through to select
statement structure as well.

The mailing lists are often a good place to get help with selects that
perform slowly.



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

On Sat, May 24, 2008 at 11:57 AM, Ram Ravichandran <ramkaka@gmail.com> wrote:
> ..."High Performance MySQL" ...
BTW: The current version of this book is (somewhat) out of date, and
the next version will be released in next few months.

--
Rob Wultsch
wultsch@gmail.com

On Sat, May 24, 2008 at 12:57 PM, Ram Ravichandran <ramkaka@gmail.com> wrote:
> Hi,
>
> I am deciding between MySQL and Postgres. I'm leaning towards Postgres
> mainly due the widely publicized speed when using transactions. However, I
> am not able to find any good books / resources for tuning/ optimizing the
> database. Is there a book like "High Performance MySQL" for Postgres that
> teaches what the different parameters are and how to tune them?
> Or do most techniques covered in the High Performance Mysql apply to
> Postgres too?

Aside from all the really good advice you've received so far, another
vital step is running the latest version of pgsql.  8.3 has a lot of
improvements, and look for 8.4 to be the same way.