pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PostgreSQL vs. MySQL
71+ messages / 30 participants
[nested] [flat]

* PostgreSQL vs. MySQL
@ 2003-07-04 10:03 Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 11:12 ` Re: PostgreSQL vs. MySQL Richard Huxton <dev@archonet.com>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 16:14 ` Re: PostgreSQL vs. MySQL Achilleus Mantzios <achill@matrix.gatewaynet.com>
  0 siblings, 3 replies; 71+ messages in thread

From: Rafal Kedziorski @ 2003-07-04 10:03 UTC (permalink / raw)
  To: pgsql-performance

Hi,

has anybody tested PostgreSQL 7.3.x tables agains MySQL 4.0.12/13 with InnoDB?


Regards,
Rafal 




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
@ 2003-07-04 11:12 ` Richard Huxton <dev@archonet.com>
  2 siblings, 0 replies; 71+ messages in thread

From: Richard Huxton @ 2003-07-04 11:12 UTC (permalink / raw)
  To: Rafal Kedziorski <rafcio@polonium.de>; pgsql-performance

On Friday 04 Jul 2003 11:03 am, Rafal Kedziorski wrote:
> Hi,
>
> has anybody tested PostgreSQL 7.3.x tables agains MySQL 4.0.12/13 with
> InnoDB?

Lots of people probably. The big problem is that unless the tester's setup 
matches your intended usage the results are of little worth.

For the tests to be meaningful, you need the same:
 - hardware
 - OS
 - query complexity
 - usage patterns
 - tuning options

I'd suggest running your own tests with real data where possible. Just to make 
the situation more interesting, the best way to solve a problem in PG isn't 
necessarily the same in MySQL.



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
@ 2003-07-04 12:27 ` Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:36   ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:50   ` Re: PostgreSQL vs. MySQL Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2003-07-04 13:59   ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
  2003-07-06 00:11   ` Re: PostgreSQL vs. MySQL Hannu Krosing <hannu@tm.ee>
  2 siblings, 5 replies; 71+ messages in thread

From: Brian Tarbox @ 2003-07-04 12:27 UTC (permalink / raw)
  To: Rafal Kedziorski <rafcio@polonium.de>; pgsql-performance

I recently took a system from MySQL to Postgres.  Same HW, SW, same data.
The major operations where moderately complex queries (joins on 8 tables).

The results we got was that Postgres was fully 3 times slower than MySql.
We were on this  list a fair bit looking for answers and tried all the
standard answers.  It was still much  much much slower.

Brian Tarbox

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Rafal
Kedziorski
Sent: Friday, July 04, 2003 6:03 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] PostgreSQL vs. MySQL


Hi,

has anybody tested PostgreSQL 7.3.x tables agains MySQL 4.0.12/13 with
InnoDB?


Regards,
Rafal


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-04 12:36   ` Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 12:43     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  4 siblings, 1 reply; 71+ messages in thread

From: Michael Mattox @ 2003-07-04 12:36 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; Rafal Kedziorski <rafcio@polonium.de>; pgsql-performance

> I recently took a system from MySQL to Postgres.  Same HW, SW, same data.
> The major operations where moderately complex queries (joins on 8 tables).
>
> The results we got was that Postgres was fully 3 times slower than MySql.
> We were on this  list a fair bit looking for answers and tried all the
> standard answers.  It was still much  much much slower.

I'm curious what the usage was.  How many concurrent processes were
performing the complex queries?  I've heard that Postgres does better when
the number of concurrent users is high and MySQL does better when the number
is low.  I have no idea if that is true or not.

Michael





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:36   ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
@ 2003-07-04 12:43     ` Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:46       ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-07 17:35       ` Re: PostgreSQL vs. MySQL scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 2 replies; 71+ messages in thread

From: Brian Tarbox @ 2003-07-04 12:43 UTC (permalink / raw)
  To: michael.mattox@verideon.com; Rafal Kedziorski <rafcio@polonium.de>; pgsql-performance

I'm actually leaving this list but I can answer this question.  Our results
were with a single user and we were running Inodb.  We were running on
RedHat 8.0 / 9.0 with vanilla linux settings.

Brian

-----Original Message-----
From: Michael Mattox [mailto:michael.mattox@verideon.com]
Sent: Friday, July 04, 2003 8:36 AM
To: Brian Tarbox; Rafal Kedziorski; pgsql-performance@postgresql.org
Subject: RE: [PERFORM] PostgreSQL vs. MySQL


> I recently took a system from MySQL to Postgres.  Same HW, SW, same data.
> The major operations where moderately complex queries (joins on 8 tables).
>
> The results we got was that Postgres was fully 3 times slower than MySql.
> We were on this  list a fair bit looking for answers and tried all the
> standard answers.  It was still much  much much slower.

I'm curious what the usage was.  How many concurrent processes were
performing the complex queries?  I've heard that Postgres does better when
the number of concurrent users is high and MySQL does better when the number
is low.  I have no idea if that is true or not.

Michael




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:36   ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 12:43     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-04 12:46       ` Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 12:51         ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  1 sibling, 1 reply; 71+ messages in thread

From: Michael Mattox @ 2003-07-04 12:46 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; Rafal Kedziorski <rafcio@polonium.de>; pgsql-performance

> I'm actually leaving this list but I can answer this question.
> Our results
> were with a single user and we were running Inodb.  We were running on
> RedHat 8.0 / 9.0 with vanilla linux settings.

That's funny, you make a statement that Postgres was 3 times slower than
MySQL and then you promptly leave the list!  Just kidding.

It'd be interesting to see what happens if you test your system with a
hundred users.  If it's a webapp you can use JMeter to do this really
easily.

Michael





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:36   ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 12:43     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:46       ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
@ 2003-07-04 12:51         ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  0 siblings, 0 replies; 71+ messages in thread

From: Shridhar Daithankar @ 2003-07-04 12:51 UTC (permalink / raw)
  To: pgsql-performance

On Friday 04 July 2003 18:16, Michael Mattox wrote:
> > I'm actually leaving this list but I can answer this question.
> > Our results
> > were with a single user and we were running Inodb.  We were running on
> > RedHat 8.0 / 9.0 with vanilla linux settings.
>
> That's funny, you make a statement that Postgres was 3 times slower than
> MySQL and then you promptly leave the list!  Just kidding.
>
> It'd be interesting to see what happens if you test your system with a
> hundred users.  If it's a webapp you can use JMeter to do this really
> easily.

Hundred users is a later scenario. I am curious about "vanilla linux settings" 
What does that mean.

 Postgresql communmity would always like to help who need it but this thread 
so far gives me impression that OP isn't willing to provide sufficient 
information..

 Shridhar




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:36   ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 12:43     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-07 17:35       ` scott.marlowe <scott.marlowe@ihs.com>
  2003-07-07 18:16         ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@world.std.com>
  2003-07-20 23:23         ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 2 replies; 71+ messages in thread

From: scott.marlowe @ 2003-07-07 17:35 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; +Cc: michael.mattox@verideon.com; Rafal Kedziorski <rafcio@polonium.de>; pgsql-performance

On Fri, 4 Jul 2003, Brian Tarbox wrote:

> I'm actually leaving this list but I can answer this question.  Our results
> were with a single user and we were running Inodb.  We were running on
> RedHat 8.0 / 9.0 with vanilla linux settings.

Hi Brian, I just wanted to add that if you aren't testing your setup for 
multiple users, you are doing yourself a disservice.  The performance of 
your app with one user is somewhat interesting, the performance of the 
system with a dozen or a hundred users is of paramount importance.

A server that dies under heavy parallel load is useless, no matter how 
fast it ran when tested for one user.  Conversely, one would prefer a 
server that was a little slow for single users but can hold up under load.

When I first built my test box a few years ago, I tested postgresql / 
apache / php at 100 or more parallel users.  That's where things start 
getting ugly, and you've got to test for it now, before you commit to a 
platform.

Postgresql is designed to work on anything out of the box, which means 
it's not optimized for high performance, but for running on old Sparc 2s 
with 128 meg of ram.  If you're going to test it against MySQL, be fair to 
yourself and performance tune them both before testing, they're 
performance on vanilla linux with vanilla configuration tuning teachs you 
little about how they'll behave in production on heavy iron.

Good luck on your testing, and please, don't quit testing at the first 
sign one or the other is faster, be throrough and complete, including 
heavy parallel load testing with reads AND writes.  Know the point at 
which each system begins to fail / become unresponsive, and how they 
behave in overload.





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:36   ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 12:43     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-07 17:35       ` Re: PostgreSQL vs. MySQL scott.marlowe <scott.marlowe@ihs.com>
@ 2003-07-07 18:16         ` Brian Tarbox <btarbox@world.std.com>
  2003-07-07 19:58           ` Re: PostgreSQL vs. MySQL scott.marlowe <scott.marlowe@ihs.com>
  2003-07-20 23:24           ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 2 replies; 71+ messages in thread

From: Brian Tarbox @ 2003-07-07 18:16 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; Brian Tarbox <btarbox@theworld.com>; +Cc: michael.mattox@verideon.com; Rafal Kedziorski <rafcio@polonium.de>; pgsql-performance

Oddly enough, the particular application in question will have an extremely
small user base...perhaps a few simultainous users at most.

As to the testing, I neglected to say early in this thread that my manager
instructed me _not_ to do further performance testing...so as a good
consultant I complied.  I'm not going to touch if that was a smart
instruction to give :-)

Brian


-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Monday, July 07, 2003 1:35 PM
To: Brian Tarbox
Cc: michael.mattox@verideon.com; Rafal Kedziorski;
pgsql-performance@postgresql.org
Subject: Re: [PERFORM] PostgreSQL vs. MySQL


On Fri, 4 Jul 2003, Brian Tarbox wrote:

> I'm actually leaving this list but I can answer this question.  Our
results
> were with a single user and we were running Inodb.  We were running on
> RedHat 8.0 / 9.0 with vanilla linux settings.

Hi Brian, I just wanted to add that if you aren't testing your setup for
multiple users, you are doing yourself a disservice.  The performance of
your app with one user is somewhat interesting, the performance of the
system with a dozen or a hundred users is of paramount importance.

A server that dies under heavy parallel load is useless, no matter how
fast it ran when tested for one user.  Conversely, one would prefer a
server that was a little slow for single users but can hold up under load.

When I first built my test box a few years ago, I tested postgresql /
apache / php at 100 or more parallel users.  That's where things start
getting ugly, and you've got to test for it now, before you commit to a
platform.

Postgresql is designed to work on anything out of the box, which means
it's not optimized for high performance, but for running on old Sparc 2s
with 128 meg of ram.  If you're going to test it against MySQL, be fair to
yourself and performance tune them both before testing, they're
performance on vanilla linux with vanilla configuration tuning teachs you
little about how they'll behave in production on heavy iron.

Good luck on your testing, and please, don't quit testing at the first
sign one or the other is faster, be throrough and complete, including
heavy parallel load testing with reads AND writes.  Know the point at
which each system begins to fail / become unresponsive, and how they
behave in overload.




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:36   ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 12:43     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-07 17:35       ` Re: PostgreSQL vs. MySQL scott.marlowe <scott.marlowe@ihs.com>
  2003-07-07 18:16         ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@world.std.com>
@ 2003-07-07 19:58           ` scott.marlowe <scott.marlowe@ihs.com>
  1 sibling, 0 replies; 71+ messages in thread

From: scott.marlowe @ 2003-07-07 19:58 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@world.std.com>; +Cc: Brian Tarbox <btarbox@theworld.com>; michael.mattox@verideon.com; Rafal Kedziorski <rafcio@polonium.de>; pgsql-performance

On Mon, 7 Jul 2003, Brian Tarbox wrote:

> Oddly enough, the particular application in question will have an extremely
> small user base...perhaps a few simultainous users at most.
> 
> As to the testing, I neglected to say early in this thread that my manager
> instructed me _not_ to do further performance testing...so as a good
> consultant I complied.  I'm not going to touch if that was a smart
> instruction to give :-)

But remember, you can always rename your performance testing as 
compliance testing and then it's ok, as long as you don't keep any 
detailed records about the time it took to run the "compliance testing" 
queries.

Definitely look at the output from explain analyze select ... to see what 
the planner THINKS the query is gonna cost versus what it really costs.  
If you see a huge difference between, say estimated rows and actual rows, 
or some other value, it points to the analyzer not getting the right data 
for the planner.  You can adjust the percentage of a table sampled with 
alter table to force more data into analyze.




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:36   ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 12:43     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-07 17:35       ` Re: PostgreSQL vs. MySQL scott.marlowe <scott.marlowe@ihs.com>
  2003-07-07 18:16         ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@world.std.com>
@ 2003-07-20 23:24           ` Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 0 replies; 71+ messages in thread

From: Bruce Momjian @ 2003-07-20 23:24 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@world.std.com>; +Cc: scott.marlowe <scott.marlowe@ihs.com>; Brian Tarbox <btarbox@theworld.com>; michael.mattox@verideon.com, Rafal Kedziorski <rafcio@polonium.de>; pgsql-performance

Brian Tarbox wrote:
> Oddly enough, the particular application in question will have an extremely
> small user base...perhaps a few simultainous users at most.
> 
> As to the testing, I neglected to say early in this thread that my manager
> instructed me _not_ to do further performance testing...so as a good
> consultant I complied.  I'm not going to touch if that was a smart
> instruction to give :-)

Performance is probably 'good enough', and you can revisit it later when
you have more time.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:36   ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 12:43     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-07 17:35       ` Re: PostgreSQL vs. MySQL scott.marlowe <scott.marlowe@ihs.com>
@ 2003-07-20 23:23         ` Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 0 replies; 71+ messages in thread

From: Bruce Momjian @ 2003-07-20 23:23 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Brian Tarbox <btarbox@theworld.com>; michael.mattox@verideon.com, Rafal Kedziorski <rafcio@polonium.de>; pgsql-performance


I think the issue with multiple users is that a car is good for moving a
few people, but it can't move lots of large boxes. A truck can move
large boxes, but it can't move a few people efficiently.  PostgreSQL is
more like a truck, while MySQL is more like a car.

As an aside, I think Solaris is slower than other OS's because it is
built to scale efficiently to many CPU's, and that takes a performance
hit in a machine with just a few CPU's, though they are working on
tuning those cases.

Of course, this is all just a generalization.

---------------------------------------------------------------------------

scott.marlowe wrote:
> On Fri, 4 Jul 2003, Brian Tarbox wrote:
> 
> > I'm actually leaving this list but I can answer this question.  Our results
> > were with a single user and we were running Inodb.  We were running on
> > RedHat 8.0 / 9.0 with vanilla linux settings.
> 
> Hi Brian, I just wanted to add that if you aren't testing your setup for 
> multiple users, you are doing yourself a disservice.  The performance of 
> your app with one user is somewhat interesting, the performance of the 
> system with a dozen or a hundred users is of paramount importance.
> 
> A server that dies under heavy parallel load is useless, no matter how 
> fast it ran when tested for one user.  Conversely, one would prefer a 
> server that was a little slow for single users but can hold up under load.
> 
> When I first built my test box a few years ago, I tested postgresql / 
> apache / php at 100 or more parallel users.  That's where things start 
> getting ugly, and you've got to test for it now, before you commit to a 
> platform.
> 
> Postgresql is designed to work on anything out of the box, which means 
> it's not optimized for high performance, but for running on old Sparc 2s 
> with 128 meg of ram.  If you're going to test it against MySQL, be fair to 
> yourself and performance tune them both before testing, they're 
> performance on vanilla linux with vanilla configuration tuning teachs you 
> little about how they'll behave in production on heavy iron.
> 
> Good luck on your testing, and please, don't quit testing at the first 
> sign one or the other is faster, be throrough and complete, including 
> heavy parallel load testing with reads AND writes.  Know the point at 
> which each system begins to fail / become unresponsive, and how they 
> behave in overload.
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-04 12:53   ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:11     ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  2003-07-04 13:20     ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:21     ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  4 siblings, 4 replies; 71+ messages in thread

From: Shridhar Daithankar @ 2003-07-04 12:53 UTC (permalink / raw)
  To: pgsql-performance

On Friday 04 July 2003 17:57, Brian Tarbox wrote:
> I recently took a system from MySQL to Postgres.  Same HW, SW, same data.
> The major operations where moderately complex queries (joins on 8 tables).
>
> The results we got was that Postgres was fully 3 times slower than MySql.
> We were on this  list a fair bit looking for answers and tried all the
> standard answers.  It was still much  much much slower.

This invites the slew of questions thereof. Can you provide more information 
on

1. Hardware
2. Postgresql version
3. Postgresql tuning you did
4. data size
5. nature of queries
6. mysql benchmarks to rate against.

Unless you provide these, it's difficult to help..

 Shridhar




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-04 13:11     ` Rod Taylor <rbt@rbt.ca>
  3 siblings, 0 replies; 71+ messages in thread

From: Rod Taylor @ 2003-07-04 13:11 UTC (permalink / raw)
  To: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>; +Cc: pgsql-performance

> Unless you provide these, it's difficult to help..

http://archives.postgresql.org/pgsql-performance/2003-05/msg00299.php

Note the thread with Tom and Brian.

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-04 13:20     ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:45       ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  3 siblings, 1 reply; 71+ messages in thread

From: Shridhar Daithankar @ 2003-07-04 13:20 UTC (permalink / raw)
  To: pgsql-performance

On 4 Jul 2003 at 9:11, Rod Taylor wrote:

> > Unless you provide these, it's difficult to help..
> 
> http://archives.postgresql.org/pgsql-performance/2003-05/msg00299.php

Well, even in that thread there wasn't enough information I asked for in other 
mail. It was bit too vague to be a comfortable DB tuning problem.

Am I reading the thread wrong? Please correct me.


Bye
 Shridhar

--
Ahead warp factor one, Mr. Sulu.




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:20     ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-04 13:45       ` Rod Taylor <rbt@rbt.ca>
  2003-07-04 14:23         ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 71+ messages in thread

From: Rod Taylor @ 2003-07-04 13:45 UTC (permalink / raw)
  To: shridhar_daithankar@persistent.co.in; +Cc: pgsql-performance

On Fri, 2003-07-04 at 09:20, Shridhar Daithankar wrote:
> On 4 Jul 2003 at 9:11, Rod Taylor wrote:
> 
> > > Unless you provide these, it's difficult to help..
> > 
> > http://archives.postgresql.org/pgsql-performance/2003-05/msg00299.php
> 
> Well, even in that thread there wasn't enough information I asked for in other 
> mail. It was bit too vague to be a comfortable DB tuning problem.

Completely too little information, and it stopped with Tom asking for
additional information. I don't think Brian has any interest in being
helped. Many here would be more than happy to do so if the information
were to flow.

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:20     ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:45       ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
@ 2003-07-04 14:23         ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-07-04 16:10           ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  0 siblings, 1 reply; 71+ messages in thread

From: Tom Lane @ 2003-07-04 14:23 UTC (permalink / raw)
  To: Rod Taylor <rbt@rbt.ca>; +Cc: pgsql-performance

Rod Taylor <rbt@rbt.ca> writes:
>> It was bit too vague to be a comfortable DB tuning problem.

> Completely too little information, and it stopped with Tom asking for
> additional information.

There was something awfully fishy about that.  Brian was saying that he
got a seqscan plan out of "WHERE foo = 100", where foo is an integer
primary key.  That's just not real credible, at least not once you get
past the couple of standard issues that were mentioned in the thread.
And we never did get word one of information about his join problems.

> I don't think Brian has any interest in being helped.

I suspect he'd made up his mind already.  Which is his privilege, but
it'd be nice to have some clue what the problem was ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:20     ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:45       ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  2003-07-04 14:23         ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-04 16:10           ` Brian Tarbox <btarbox@theworld.com>
  2003-07-04 16:22             ` Re: PostgreSQL vs. MySQL Bjoern Metzdorf <bm@turtle-entertainment.de>
  2003-07-04 17:05             ` Re: PostgreSQL vs. MySQL Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2003-07-04 17:08             ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 17:12             ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
  0 siblings, 4 replies; 71+ messages in thread

From: Brian Tarbox @ 2003-07-04 16:10 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Rod Taylor <rbt@rbt.ca>; +Cc: shridhar_daithankar@persistent.co.in; pgsql-performance

> I don't think Brian has any interest in being helped.
>I suspect he'd made up his mind already.


With all due respect Tom, I don't think I'm the one demonstrating a closed
mind.
Rather than trying to figure out whats going on in my head, how about
figuring out whats going on in my database?  :-)

I'm answering every question I can.  I supplied HW info because someone
asked, and then Tom said: "The hardware and platform details you gave mean
nothing to me...".  Which would you like guys??

I am not allowed to share schemas...sorry but thats what the contract says.
The queries represent code, thus intellectual property, thus I can't post
them.

I posted an Explain output at some point and was told my database was too
small to be fast.  So, I added 10,000 records, vacummed, and my selects were
still the same speed.

How many people on this list have asked for a tuning/performance doc?  I
hear that there is one coming soon.  Thats great.  Saying RTM is fine too,
if the manual is clear.  Look at Michael Mattox's thread on this very topic
on 6/24.  Michael said:
"I think the biggest area of confusion for me was that the various
parameters
are very briefly described and no context is given for their parameters.?

Shridhar then suggested he change OSes, upgrade his kernel (with specific
patches), get different HW, etc.  That goes a bit beyond casual tuning.


I'm not saying (and never did say) that postgres could not be fast.  All I
ever said was that with the same minimal effort applied to both DBs,
postgres was slower.

I really wasn't looking for battle this fine day....I'm going outside to
BBQ!  (and if you conclude from that that I'm not interested in this or
that, there's nothing I can do about that.  It is a beautiful day out and
bbq does sound more fun than this list.  sorry)

Brian




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:20     ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:45       ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  2003-07-04 14:23         ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
  2003-07-04 16:10           ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-04 16:22             ` Bjoern Metzdorf <bm@turtle-entertainment.de>
  2003-07-04 16:39               ` Re: PostgreSQL vs. MySQL Kevin Schroeder <kschroeder@mirageworks.com>
  2003-07-04 21:14               ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  3 siblings, 2 replies; 71+ messages in thread

From: Bjoern Metzdorf @ 2003-07-04 16:22 UTC (permalink / raw)
  To: pgsql-performance

> I'm not saying (and never did say) that postgres could not be fast.
> All I ever said was that with the same minimal effort applied to both
> DBs, postgres was slower.

Afaik, your original posting said postgresql was 3 times slower than mysql
and that you are going to leave this list now. This implied that you have
made your decision between postgresql and mysql, taking mysql because it is
faster.

Now you say your testing setup has minimal effort applied. Well, it is not
very surprising that mysql is faster in standard configurations. As Shridhar
pointed out, postgresql has very conservative default values, so that it
starts on nearly every machine.

If I was your client and gave you the task to choose a suitable database for
my application and you evaluated suitable databases this way, then something
is seriously wrong with your work.

Regards,
Bjoern





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:20     ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:45       ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  2003-07-04 14:23         ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
  2003-07-04 16:10           ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 16:22             ` Re: PostgreSQL vs. MySQL Bjoern Metzdorf <bm@turtle-entertainment.de>
@ 2003-07-04 16:39               ` Kevin Schroeder <kschroeder@mirageworks.com>
  1 sibling, 0 replies; 71+ messages in thread

From: Kevin Schroeder @ 2003-07-04 16:39 UTC (permalink / raw)
  To: pgsql-performance

My goodness people!!  If you are just going to bash people who are trying to
learn PostgreSQL then you have no chance of ever getting new people using
it!  Cut out this crap and do what this list is meant to do, which is, I'm
assuming, helping people figure out why their installations aren't running
as fast as they would like.  This is pathetic!!

Kevin

----- Original Message ----- 
From: "Bjoern Metzdorf" <bm@turtle-entertainment.de>
To: "Postgresql Performance" <pgsql-performance@postgresql.org>
Sent: Friday, July 04, 2003 11:22 AM
Subject: Re: [PERFORM] PostgreSQL vs. MySQL


> > I'm not saying (and never did say) that postgres could not be fast.
> > All I ever said was that with the same minimal effort applied to both
> > DBs, postgres was slower.
>
> Afaik, your original posting said postgresql was 3 times slower than mysql
> and that you are going to leave this list now. This implied that you have
> made your decision between postgresql and mysql, taking mysql because it
is
> faster.
>
> Now you say your testing setup has minimal effort applied. Well, it is not
> very surprising that mysql is faster in standard configurations. As
Shridhar
> pointed out, postgresql has very conservative default values, so that it
> starts on nearly every machine.
>
> If I was your client and gave you the task to choose a suitable database
for
> my application and you evaluated suitable databases this way, then
something
> is seriously wrong with your work.
>
> Regards,
> Bjoern
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:20     ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:45       ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  2003-07-04 14:23         ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
  2003-07-04 16:10           ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 16:22             ` Re: PostgreSQL vs. MySQL Bjoern Metzdorf <bm@turtle-entertainment.de>
@ 2003-07-04 21:14               ` Brian Tarbox <btarbox@theworld.com>
  2003-07-04 22:24                 ` Re: PostgreSQL vs. MySQL Bjoern Metzdorf <bm@turtle-entertainment.de>
  1 sibling, 1 reply; 71+ messages in thread

From: Brian Tarbox @ 2003-07-04 21:14 UTC (permalink / raw)
  To: Bjoern Metzdorf <bm@turtle-entertainment.de>; pgsql-performance

>Afaik, your original posting said postgresql was 3 times slower than mysql
>and that you are going to leave this list now. This implied that you have
>made your decision between postgresql and mysql, taking mysql because it is
>faster.

Well, that shows what you get for making implications.  The client is
sticking with postgres and we are coding around the issue in other ways.


>If I was your client and gave you the task to choose a suitable database
for
>my application and you evaluated suitable databases this way, then
something
>is seriously wrong with your work.
>
>Regards,
>Bjoern

Glad to see you're not getting personal with this. Ad hominin attacks are
for folks with no better answers.

Please go read the posts by Kevin Schroeder and Arjen va der Meijden before
slinging any more 'help'.

over and out.








^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:20     ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:45       ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  2003-07-04 14:23         ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
  2003-07-04 16:10           ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 16:22             ` Re: PostgreSQL vs. MySQL Bjoern Metzdorf <bm@turtle-entertainment.de>
  2003-07-04 21:14               ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-04 22:24                 ` Bjoern Metzdorf <bm@turtle-entertainment.de>
  2003-07-05 16:39                   ` Re: PostgreSQL vs. MySQL Grega Bremec <gregab@noviforum.si>
  0 siblings, 1 reply; 71+ messages in thread

From: Bjoern Metzdorf @ 2003-07-04 22:24 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; pgsql-performance

>> Afaik, your original posting said postgresql was 3 times slower than
>> mysql and that you are going to leave this list now. This implied
>> that you have made your decision between postgresql and mysql,
>> taking mysql because it is faster.
>
> Well, that shows what you get for making implications.  The client is
> sticking with postgres and we are coding around the issue in other
> ways.

As many other guys here pointed out, there are numerous ways to tune
postgresql for maximum performance. If you are willing to share more
information about your particular project, we might be able to help you out
and optimize your application, without the need to code around the issue as
much as you may be doing right now.
Even if it is not possible for you to share enough information, there are a
lot of places where you can read about performance tuning (if not in the
docs then in the archives).

>> If I was your client and gave you the task to choose a suitable
>> database for my application and you evaluated suitable databases
>> this way, then something is seriously wrong with your work.
>>
> Glad to see you're not getting personal with this. Ad hominin attacks
> are for folks with no better answers.

Yep, you're right. Sorry for that, I didn't mean to get personal. I was
somehow irritated that you come here, post your database comparison and want
to leave right afterwards, without going into detail (what should be the
case normally).

Again our offer: Post (possibly obfuscated) schema information, and we will
certainly be able to help you with performance tuning.

Regards,
Bjoern




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:20     ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:45       ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  2003-07-04 14:23         ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
  2003-07-04 16:10           ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 16:22             ` Re: PostgreSQL vs. MySQL Bjoern Metzdorf <bm@turtle-entertainment.de>
  2003-07-04 21:14               ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 22:24                 ` Re: PostgreSQL vs. MySQL Bjoern Metzdorf <bm@turtle-entertainment.de>
@ 2003-07-05 16:39                   ` Grega Bremec <gregab@noviforum.si>
  0 siblings, 0 replies; 71+ messages in thread

From: Grega Bremec @ 2003-07-05 16:39 UTC (permalink / raw)
  To: pgsql-performance; +Cc: Brian Tarbox <btarbox@theworld.com>

...and on Sat, Jul 05, 2003 at 12:24:18AM +0200, Bjoern Metzdorf used the keyboard:
> >> Afaik, your original posting said postgresql was 3 times slower than
> >> mysql and that you are going to leave this list now. This implied
> >> that you have made your decision between postgresql and mysql,
> >> taking mysql because it is faster.
> >
> > Well, that shows what you get for making implications.  The client is
> > sticking with postgres and we are coding around the issue in other
> > ways.
> 
> As many other guys here pointed out, there are numerous ways to tune
> postgresql for maximum performance. If you are willing to share more
> information about your particular project, we might be able to help you out
> and optimize your application, without the need to code around the issue as
> much as you may be doing right now.
> Even if it is not possible for you to share enough information, there are a
> lot of places where you can read about performance tuning (if not in the
> docs then in the archives).
> 

Also, I should think the clients would not be too offended if Brian posted
some hint about the actual quantity of data involved here, both the total
expected database size and some info about the estimated "working set" size,
such as a sum of sizes of tables most commonly used in JOIN queries and the
percentage of data being shuffled around in those. Are indexes big? Are
there any multicolumn indexes in use? Lots of sorting expected? Lots of
UPDATEs/INSERTs/DELETEs?

Also, it would be helpful to know just how normalized the database is, to
provide some advice about possible query optimization, which could again
prove helpful in speeding the machinery up.

Another useful piece of information would be the amount of memory consumed
by other applications vs. the amount of memory reserved by the OS for cache,
and the nature of those other applications running - are they big cache
consumers, such as Apache with static content and a large load would be,
or do they keep a low profile?

I think this would, in combination with the information already posted, such
as the amount of memory and I/O subsystem info, at least enable us to advise
about the recommended shared_buffers, effective_cache_size, sort_mem,
vacuum_mem, and others, without compromising the intellectual property of
Brian's clients.

> > over and out.

I CC'd this post over to you, Brian, 'cause this signoff made me rather
unsure as to whether or not you're still on the list. Hope you don't mind.

Sincerely,
-- 
    Grega Bremec
    System Administration & Development Support
    grega.bremec-at-noviforum.si
    http://najdi.si/
    http://www.noviforum.si/



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:20     ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:45       ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  2003-07-04 14:23         ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
  2003-07-04 16:10           ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-04 17:05             ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2003-07-04 18:15               ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  3 siblings, 1 reply; 71+ messages in thread

From: Stephan Szabo @ 2003-07-04 17:05 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Rod Taylor <rbt@rbt.ca>; shridhar_daithankar@persistent.co.in; pgsql-performance


On Fri, 4 Jul 2003, Brian Tarbox wrote:

> > I don't think Brian has any interest in being helped.
> >I suspect he'd made up his mind already.
>
>
> With all due respect Tom, I don't think I'm the one demonstrating a closed
> mind.
> Rather than trying to figure out whats going on in my head, how about
> figuring out whats going on in my database?  :-)

Well, in the case of getting a sequential scan on something like
select * from foo where col=10;
where col is a primary key, the things I can think of to check
are does select * from foo where col='10'; give a different plan?

In general for cases where you can't post queries or schema we're kinda
stuck and not really able to give intelligent advice since it's often
schema/query specific, so the general questions/comments are things like
(which you've probably heard, but I think they should get put into this
thread if only to move the thread towards usefulness)

What is the relative costs/plan if you set enable_seqscan to false before
explain analyzing the query?  If there are places you think that it should
be able to do an index scan and it still doesn't, make sure that there
aren't cross datatype issues (especially with int constants).

Also, using explain analyze, where is the time being taken, it's often not
where the cost factor would expect it.

Do the row estimates match reality in the explain analyze output, if not
does analyzing help, if not does raising the statistics target (to say 50,
100, 1000) with alter table and then analyzing help?

Does vacuuming help, what about vacuum full?  If the latter does and the
former doesn't, you may need to look at raising the fsm settings.

If shared_buffers is less than 1000, does setting it to something between
1000-8000 raise performance?

How much memory does the machine have that's being used for caching, if
it's alot, try raising effective_cache_size to see if that helps the
choice of plan by making a more reasonable guess as to cache hit rates.

Are there any sorts in the query, if so, how large would expect the result
set that's being sorted to be, can you afford to make sort_mem cover that
(either permanently by changing conf files or before the query with a set
command)?

Is it possible to avoid some sorts in the plan with a multi-column index?

For 7.3 and earlier, does the query use IN or =ANY, if so it might help to
try to convert to an exists form.

Does the query use any mix/max aggregates, it might help to look for a
workaround, this is one case that is truly slow.



PostgreSQL really does require more than minimal optimization at start,
effective_cache_size, shared_buffers, sort_mem and the fsm settings really
need to be set at a level for the machine/queries you have.  Without the
queries we can't be too specific. Big speed losses I can think of are the
datatype mismatch confusion, followed quickly by row estimates that don't
match reality (generally requiring a greater statistics target on the
column) and issues with correlation (I'm not really sure there's a good
solution for this currently, maybe someone will know -- I've not run into
it really on anything I've looked at).




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:20     ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:45       ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  2003-07-04 14:23         ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
  2003-07-04 16:10           ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 17:05             ` Re: PostgreSQL vs. MySQL Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-07-04 18:15               ` Arjen van der Meijden <acm@tweakers.net>
  0 siblings, 0 replies; 71+ messages in thread

From: Arjen van der Meijden @ 2003-07-04 18:15 UTC (permalink / raw)
  To: 'Stephan Szabo' <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-performance

Why is such a simple list of questions not somewhere in the
documentation? :(

Of course a few of your questions are relatively case-dependent, but the
others are very general. Such information should be in the documentation
and easy to access :)

Regards,

Arjen

> Stephan Szabo wrote a nice list of helpful questions






^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:20     ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:45       ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  2003-07-04 14:23         ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
  2003-07-04 16:10           ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-04 17:08             ` Andrew Sullivan <andrew@libertyrms.info>
  3 siblings, 0 replies; 71+ messages in thread

From: Andrew Sullivan @ 2003-07-04 17:08 UTC (permalink / raw)
  To: pgsql-performance

On Fri, Jul 04, 2003 at 12:10:46PM -0400, Brian Tarbox wrote:
> I am not allowed to share schemas...sorry but thats what the contract says.
> The queries represent code, thus intellectual property, thus I can't post
> them.

If you ask for help, but say, "I can't tell you anything," no-one
will be able to help you.  

I think what people are reacting to angrily is that you complain that
PostgreSQL is slow, it appears you haven't tuned it correctly, and
you're not willing to share with anyone what you did.  In that case,
you shouldn't be reporting, "MySQL was faster that PostgreSQL for
me." You should at most be reporting, "MySQL was faster than
PostgreSQL for me, but I haven't any idea how to tune PostgreSQL, and
didn't know how to learn to do so."  That, at least, gives people a
fighting chance to evaluate the utility of your report.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:20     ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 13:45       ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  2003-07-04 14:23         ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
  2003-07-04 16:10           ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-04 17:12             ` Josh Berkus <josh@agliodbs.com>
  3 siblings, 0 replies; 71+ messages in thread

From: Josh Berkus @ 2003-07-04 17:12 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; +Cc: pgsql-performance

Brian,

Howdy!  I'm Josh Berkus, I'm also on the Core Team for PostgreSQL, and I 
wanted to give some closure on your issue before you quit with a bad taste in 
your mouth.

Your posting hit a sore point in the collective PostgreSQL community, so you 
got a strong reaction from several people on the list -- probably out of 
proportion to your posting.  

Or, to put it another way, you posted something intended to offend people out 
of your frustration, and got a very offended reaction back.

> Rather than trying to figure out whats going on in my head, how about
> figuring out whats going on in my database?  :-)
> I am not allowed to share schemas...sorry but thats what the contract says.
> The queries represent code, thus intellectual property, thus I can't post
> them.

I think you recognize, now, that this list cannot help you under those 
circumstances?   

A significant portion of my income derives from clients who need tuning help 
under NDA.  If, however, you don't need any capabilites that PostgreSQL has 
which MySQL doesn't, hiring a consultant would not be money well spent.

> I really wasn't looking for battle this fine day....I'm going outside to
> BBQ!  (and if you conclude from that that I'm not interested in this or
> that, there's nothing I can do about that.  It is a beautiful day out and
> bbq does sound more fun than this list.  sorry)

No arguments there ... wish I didn't have to work :-(

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-04 14:07     ` Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:34       ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  2003-07-04 14:35       ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
  2003-07-05 23:40       ` Re: PostgreSQL vs. MySQL Hilmar Lapp <hlapp@gmx.net>
  3 siblings, 4 replies; 71+ messages in thread

From: Brian Tarbox @ 2003-07-04 14:07 UTC (permalink / raw)
  To: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>; pgsql-performance

Ok, I'll give more data :-)

Under both MySql and Postgres the tests were run on a variety of systems,
all with similar results.  My own personal testing was done on a P4 2.4Mhz,
512 mb memory, latest production versions of each database.  By vanilla
RedHat I mean that I installed RH on a clean system, said install everything
and did no customization of RH settings.
We had about 40 tables in the db, with joined queries on about 8-12 tables.
Some tables had 10,000 records, some 1000 records, other tables had dozens
of records.  There were indexes on all join fields, and all join fields were
listed as foriegn keys.  All join fields were unique primary keys in their
home table (so the index distribution would be very spread out).  I'm not
permitted to post the actual tables as per company policy.

I did no tuning of MySql.  The only tuning for PG was to vacuum and vacuum
analyze.

I'll also mention that comments like this one are not productive:

>I don't think Brian has any interest in being helped.

Please understand the limits of how much information a consultant can submit
to an open list like this about a client's confidential information.  I've
answered every question I _can_ answer and when I get hostility in response
all I can do is sigh and move on.
I'm sorry if Shridhar is upset that I can't validate his favorite db but ad
hominin comments aren't helpful.

Brian


-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Shridhar
Daithankar
Sent: Friday, July 04, 2003 8:54 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] PostgreSQL vs. MySQL


On Friday 04 July 2003 17:57, Brian Tarbox wrote:
> I recently took a system from MySQL to Postgres.  Same HW, SW, same data.
> The major operations where moderately complex queries (joins on 8 tables).
>
> The results we got was that Postgres was fully 3 times slower than MySql.
> We were on this  list a fair bit looking for answers and tried all the
> standard answers.  It was still much  much much slower.

This invites the slew of questions thereof. Can you provide more information
on

1. Hardware
2. Postgresql version
3. Postgresql tuning you did
4. data size
5. nature of queries
6. mysql benchmarks to rate against.

Unless you provide these, it's difficult to help..

 Shridhar


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-04 14:28       ` Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 14:49         ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:55         ` Re: PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  3 siblings, 3 replies; 71+ messages in thread

From: Andrew Sullivan @ 2003-07-04 14:28 UTC (permalink / raw)
  To: pgsql-performance

On Fri, Jul 04, 2003 at 10:07:46AM -0400, Brian Tarbox wrote:
> 512 mb memory, latest production versions of each database.  By vanilla
> RedHat I mean that I installed RH on a clean system, said install everything
> and did no customization of RH settings.

Does that include no customization of the Postgres settings?  

> We had about 40 tables in the db, with joined queries on about 8-12 tables.

SELECTs only?  because. . .

> of records.  There were indexes on all join fields, and all join fields were
> listed as foriegn keys.  All join fields were unique primary keys in their

. . .you know that FK constraints in Postgres are not cheap, right?

> I did no tuning of MySql.  The only tuning for PG was to vacuum and vacuum
> analyze.

This appears to be a "yes" answer to my question above.  Out of the
box, PostgreSQL is set up to be able to run on a 1992-vintage SGI
Indy with 8 M of RAM (ok, I may be exaggerating, but only by a bit);
it is not tuned for performance.  Running without even tweaking the
shared buffers is guaranteed to get you lousy performance.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
@ 2003-07-04 14:35         ` Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 14:49           ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2 siblings, 2 replies; 71+ messages in thread

From: Michael Mattox @ 2003-07-04 14:35 UTC (permalink / raw)
  To: Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance

> This appears to be a "yes" answer to my question above.  Out of the
> box, PostgreSQL is set up to be able to run on a 1992-vintage SGI
> Indy with 8 M of RAM (ok, I may be exaggerating, but only by a bit);
> it is not tuned for performance.  Running without even tweaking the
> shared buffers is guaranteed to get you lousy performance.

I see this as a major problem.  How many people run postgres, decide it's
too slow and give up without digging into the documentation or coming to
this group?  This seems to be pretty common.  Even worst, they tell 10
others how slow Postgres is and then it gets a bad reputation.

In my opinion the defaults should be set up for a typical database server
machine.

Michael





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
@ 2003-07-04 14:49           ` Rod Taylor <rbt@rbt.ca>
  2003-07-04 20:39             ` Re: PostgreSQL vs. MySQL johnnnnnn <john@phaedrusdeinus.org>
  1 sibling, 1 reply; 71+ messages in thread

From: Rod Taylor @ 2003-07-04 14:49 UTC (permalink / raw)
  To: michael.mattox@verideon.com; +Cc: Andrew Sullivan <andrew@libertyrms.info>; PostgreSQL Development <pgsql-hackers@postgresql.org>

Moving to -hackers.

> In my opinion the defaults should be set up for a typical database server
> machine.

Ok.. thats fair.  The first problem would be to define typical for
current PostgreSQL installations, and typical for non-postgresql
installations (the folks we want to convert).

After that, do we care if it works with the typical OS installation or
with all default OS installations (think shared memory settings)?


I agree, this is something we should tackle somewhere along the 7.5
timeline.

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 14:49           ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
@ 2003-07-04 20:39             ` johnnnnnn <john@phaedrusdeinus.org>
  2003-07-04 21:28               ` Re: PostgreSQL vs. MySQL Tim Conrad <conrad@external.timconrad.org>
  0 siblings, 1 reply; 71+ messages in thread

From: johnnnnnn @ 2003-07-04 20:39 UTC (permalink / raw)
  To: Rod Taylor <rbt@rbt.ca>; pgsql-hackers@postgresql.org

On Fri, Jul 04, 2003 at 10:49:01AM -0400, Rod Taylor wrote:
> > In my opinion the defaults should be set up for a typical database
> > server machine.
> 
> Ok.. thats fair.  The first problem would be to define typical for
> current PostgreSQL installations, and typical for non-postgresql
> installations (the folks we want to convert).

It's been a while since the last one of these discussions, so stop me
if this has been suggested before, but...

Do we actually want to have a default configuration file?

Seriously, if we provide, say, 4 or 5 files based on various system
assumptions (conf.MINIMAL, conf.AVERAGE, conf.MULTIDISK, or whatever),
then we might be able to get away with not providing an actual
default. Change the installation instructions to say

>>>
PostgreSQL requires a configuration file, which it expects to be
located in $DIR. Provided are several example configurations (in
$DIR/eg/). If you're just starting with PostrgreSQL, we recommend
reading through those and selecting one which most closely matches
your machine.

If you're in doubt as to which file to use, try $AVERAGE. If you're
still having difficulty getting PostgreSQL to run, try
$MINIMAL. $MINIMAL should work on every supported platform, but is not
optimized for modern hardware -- PostgreSQL will not run well in this
configuration.
<<<

This makes the installation process slightly less simple, but only in
the way that we want it to be. That is, it forces the end user to the
realization that there actually is configuration to be done, and
forces them into a minimally interactive way to deal with it.

It also doesn't require any kernel-test coding, or really any
development at all, so we should theoretically be able to get it
finished and ready to go more quickly.

Thoughts?

-johnnnnnnnnnnn



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 14:49           ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  2003-07-04 20:39             ` Re: PostgreSQL vs. MySQL johnnnnnn <john@phaedrusdeinus.org>
@ 2003-07-04 21:28               ` Tim Conrad <conrad@external.timconrad.org>
  0 siblings, 0 replies; 71+ messages in thread

From: Tim Conrad @ 2003-07-04 21:28 UTC (permalink / raw)
  To: pgsql-hackers@postgresql.org

Being new to Postgres, I understand how frustrating tuning is. I've been
working on some very basic queries, and trying to get some decent performance.
I know the problem isn't with io on the system, since I can use other tests
that far exceed the amount of data being written through postgres, so I can
only assume that the  rdbms needs to be tuned.

even beyond doing different configuration files (as mysql does, fyi), just
having 'guidelines' for where to start with tuning  the various items in
postresql.conf would be helpful. Something like

foo_val = 100 # 0-1024; higher numbers for more complex queries

I don't think that'd be too difficult. Anyone who's worked with a database
of any type, understands that tuning needs to happen.

Having the three different sizes of servers (as suggested below) would be 
helpful. MySQL does this, and i've used their default configurations in the
past to help me troubleshoot probllems i was having with a complex query on
sub-standard hardware. After changing some of the values around, the query
ran slowly, but it actually ran, whereas before, it didn't. 

Slightly off subject, as I've been advocating use of Postgres, and people 
have been trying it, some of the quirks that one runs into when goinng from 
rdbms to rdbms are frustrating as well. Granted, these things happen with
going from any rdbms to another, but it'd be nice if there were a guide to say
something like, in MySQL, you use 'show tables from tablename', and in 
Postgres, you use \d tablename to achieve the same results.

Just my $.02 worth.

Tim


On Fri, Jul 04, 2003 at 03:39:57PM -0500, johnnnnnn wrote:
> On Fri, Jul 04, 2003 at 10:49:01AM -0400, Rod Taylor wrote:
> > > In my opinion the defaults should be set up for a typical database
> > > server machine.
> > 
> > Ok.. thats fair.  The first problem would be to define typical for
> > current PostgreSQL installations, and typical for non-postgresql
> > installations (the folks we want to convert).
> 
> It's been a while since the last one of these discussions, so stop me
> if this has been suggested before, but...
> 
> Do we actually want to have a default configuration file?
> 
> Seriously, if we provide, say, 4 or 5 files based on various system
> assumptions (conf.MINIMAL, conf.AVERAGE, conf.MULTIDISK, or whatever),
> then we might be able to get away with not providing an actual
> default. Change the installation instructions to say
> 
> >>>
> PostgreSQL requires a configuration file, which it expects to be
> located in $DIR. Provided are several example configurations (in
> $DIR/eg/). If you're just starting with PostrgreSQL, we recommend
> reading through those and selecting one which most closely matches
> your machine.
> 
> If you're in doubt as to which file to use, try $AVERAGE. If you're
> still having difficulty getting PostgreSQL to run, try
> $MINIMAL. $MINIMAL should work on every supported platform, but is not
> optimized for modern hardware -- PostgreSQL will not run well in this
> configuration.
> <<<
> 
> This makes the installation process slightly less simple, but only in
> the way that we want it to be. That is, it forces the end user to the
> realization that there actually is configuration to be done, and
> forces them into a minimally interactive way to deal with it.
> 
> It also doesn't require any kernel-test coding, or really any
> development at all, so we should theoretically be able to get it
> finished and ready to go more quickly.
> 
> Thoughts?
> 
> -johnnnnnnnnnnn
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
@ 2003-07-04 15:26           ` Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 15:28             ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  1 sibling, 2 replies; 71+ messages in thread

From: Andrew Sullivan @ 2003-07-04 15:26 UTC (permalink / raw)
  To: pgsql-performance

On Fri, Jul 04, 2003 at 04:35:03PM +0200, Michael Mattox wrote:

> I see this as a major problem.  How many people run postgres, decide it's
> too slow and give up without digging into the documentation or coming to
> this group?  This seems to be pretty common.  Even worst, they tell 10
> others how slow Postgres is and then it gets a bad reputation.

There have been various proposals to do things of this sort.  But
there are always problems with it.  For instance, on many OSes,
Postgres would not run _at all_ when you first compiled it if its
defaults were set more agressively.  Then how many people would
complain, "It just doesn't work," and move on without asking about
it?

I cannot, for the life of me, understand how anyone can install some
software which is supposed to provide meaningful results under
production conditions, and not bother to read even the basic
"quickstart"-type stuff that is kicking around.  There is _no secret_
that Postgres is configured as a toy out of the box.  One presumes
that DBAs are hired to do _some_ little bit of work.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
@ 2003-07-04 15:28             ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 15:42               ` Re: PostgreSQL vs. MySQL Kevin Schroeder <kschroeder@mirageworks.com>
  1 sibling, 1 reply; 71+ messages in thread

From: Shridhar Daithankar @ 2003-07-04 15:28 UTC (permalink / raw)
  To: pgsql-performance

On Friday 04 July 2003 20:56, Andrew Sullivan wrote:
> On Fri, Jul 04, 2003 at 04:35:03PM +0200, Michael Mattox wrote:
> > I see this as a major problem.  How many people run postgres, decide it's
> > too slow and give up without digging into the documentation or coming to
> > this group?  This seems to be pretty common.  Even worst, they tell 10
> > others how slow Postgres is and then it gets a bad reputation.
>
> There have been various proposals to do things of this sort.  But
> there are always problems with it.  For instance, on many OSes,
> Postgres would not run _at all_ when you first compiled it if its
> defaults were set more agressively.  Then how many people would
> complain, "It just doesn't work," and move on without asking about
> it?

There was a proposal to ship various postgresql.conf.sample like one for large 
servers, one for medium, one for update intensive purpose etc.

I was thinking over it. Actaully we could tweak initdb script to be 
interactiev and get inputs from users and tune it accordingly. Of course it 
would be nowhere near the admin reading the docs. but at least it won't fall 
flat on performance groundas the way falls now.

 Shridhar




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 15:28             ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-04 15:42               ` Kevin Schroeder <kschroeder@mirageworks.com>
  0 siblings, 0 replies; 71+ messages in thread

From: Kevin Schroeder @ 2003-07-04 15:42 UTC (permalink / raw)
  To: pgsql-performance

That would be something that I'd like to see.  Being new to PostgreSQL some
of the basics of tuning the database were a little hard to find.  The reason
people go with MySQL is because it's fast and easy to use.  That's why I had
been using it for years.  Then when a problem came along and I couldn't use
MySQL I checked out PostgreSQL and found that it would fill the gap, but I
had been able to get by on doing very little in terms of administration for
MySQL (which performed well for me) and I was expecting PostgreSQL to be
similar.  As with many people I have the hat of DB admin, server admin,
programmer and designer and the less I have to do in any of those areas
makes my life a lot easier.

When I first started using PostgreSQL I installed it and entered my data
without any thought of having to tune it because I never had to before.  If
there were some program that could be inserted to the end of the make
process or something it might help dimwits like me :-) realize that there
was more that needs to be done once the installation has been completed.

Kevin


----- Original Message ----- 
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: <pgsql-performance@postgresql.org>
Sent: Friday, July 04, 2003 10:28 AM
Subject: Re: [PERFORM] PostgreSQL vs. MySQL


> On Friday 04 July 2003 20:56, Andrew Sullivan wrote:
> > On Fri, Jul 04, 2003 at 04:35:03PM +0200, Michael Mattox wrote:
> > > I see this as a major problem.  How many people run postgres, decide
it's
> > > too slow and give up without digging into the documentation or coming
to
> > > this group?  This seems to be pretty common.  Even worst, they tell 10
> > > others how slow Postgres is and then it gets a bad reputation.
> >
> > There have been various proposals to do things of this sort.  But
> > there are always problems with it.  For instance, on many OSes,
> > Postgres would not run _at all_ when you first compiled it if its
> > defaults were set more agressively.  Then how many people would
> > complain, "It just doesn't work," and move on without asking about
> > it?
>
> There was a proposal to ship various postgresql.conf.sample like one for
large
> servers, one for medium, one for update intensive purpose etc.
>
> I was thinking over it. Actaully we could tweak initdb script to be
> interactiev and get inputs from users and tune it accordingly. Of course
it
> would be nowhere near the admin reading the docs. but at least it won't
fall
> flat on performance groundas the way falls now.
>
>  Shridhar
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
@ 2003-07-04 18:07             ` Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  1 sibling, 1 reply; 71+ messages in thread

From: Arjen van der Meijden @ 2003-07-04 18:07 UTC (permalink / raw)
  To: 'Andrew Sullivan' <andrew@libertyrms.info>; pgsql-performance

> Andrew Sullivan wrote:

> I cannot, for the life of me, understand how anyone can 
> install some software which is supposed to provide meaningful 
> results under production conditions, and not bother to read 
> even the basic "quickstart"-type stuff that is kicking 
> around.
Then please point out where it sais, in the documentation, that the
value for the shared_memory of 64 is too low and that 4000 is a nice
value to start with?

Please, also point out the part of the documentation that explains how
high the fsm-settings should be, what the impact of a lower or higher
sort_mem-setting is, what kind of value the effective_cache_size should
have and the best way to determine that.

If you can find the above in the default-documentation, like the
"getting started"-documents or the administration documentation, than be
so kind to give direct links or quotes to that. I was unable to find
that, now in a 15 minute search in the docs themselves and I have read
most part of them (in the past)...

Especially in chapter 10 "Performance hints" I was surprised not to see
such information, although it could be considered an administration
task, but there it wasn't in chapter 10 (monitoring database usage)
either.


I'm sorry to put this in a such a confronting manner, but you simply
can't expect people to search for information that they don't know the
existence of... Actually, that doesn't appear to exist, at least not on
the places you'd expect that information to be placed. I, myself, have
read Bruce's document on performance tuning, but even that document
doesn't provide the detail of information that can be read in this
mailing-list.

Having said that, this list only has 461 subscribers and I can hardly
believe that that are _all_ users of postgresql, as long as it's not the
default way of trying to gather data, it shouldn't be expected that
anyone actually tries to find his information in this list.

Anyway, I saw that there has been done some effort to create a document
that does describe such parameters, I'd be happy to see and read that :)

> There is _no secret_ that Postgres is configured as 
> a toy out of the box.  One presumes that DBAs are hired to do 
> _some_ little bit of work.
I don't see it on the frontpage, nor in the documentation. Anyway, see
above :)

Regards,

Arjen

Btw, I've tried to tune my postgresql database using the administration
and tech documents, and saw quite a few queries run quite a lot faster
on mysql, I'll try to set up a more useful test environment and supply
this list with information to allow me to tune it to run more or less
equal to mysql. I do see 3x runs, even with the shared memory and sort
mem settings cranked up and having done little to none tuning on mysql
:)






^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
@ 2003-07-04 18:28               ` Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:37                 ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
  2003-08-17 00:36                 ` Re: [PERFORM] PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  2003-10-08 17:28                 ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 3 replies; 71+ messages in thread

From: Andrew Sullivan @ 2003-07-04 18:28 UTC (permalink / raw)
  To: pgsql-performance

On Fri, Jul 04, 2003 at 08:07:18PM +0200, Arjen van der Meijden wrote:
> > Andrew Sullivan wrote:
> > results under production conditions, and not bother to read 
> > even the basic "quickstart"-type stuff that is kicking 
> > around.
> Then please point out where it sais, in the documentation, that the
> value for the shared_memory of 64 is too low and that 4000 is a nice
> value to start with?

I think I did indeed speak too soon, as the criticism is a fair one:
nowhere in the installation instructions or the "getting started"
docs does it say that you really ought to do some tuning once you
have the system installed.  Can I suggest for the time being that
something along these lines should go in 14.6.3, "Tuning the
installation":

---snip---
By default, PostgreSQL is configured to run on minimal hardware.  As
a result, some tuning of your installation will be necessary before
using it for anything other than extremely small databases.  At the
very least, it will probably be necessary to increase your shared
buffers setting.  See Chapter 16 for details on what tuning options
are available to you.
---snip---

> I'm sorry to put this in a such a confronting manner, but you simply
> can't expect people to search for information that they don't know the
> existence of.

No need to apologise; I think you're right.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
@ 2003-07-04 18:37                 ` Josh Berkus <josh@agliodbs.com>
  2003-07-04 19:18                   ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 1 reply; 71+ messages in thread

From: Josh Berkus @ 2003-07-04 18:37 UTC (permalink / raw)
  To: Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance

People:

> I think I did indeed speak too soon, as the criticism is a fair one:
> nowhere in the installation instructions or the "getting started"
> docs does it say that you really ought to do some tuning once you
> have the system installed.  Can I suggest for the time being that
> something along these lines should go in 14.6.3, "Tuning the
> installation":
> 
> ---snip---
> By default, PostgreSQL is configured to run on minimal hardware.  As
> a result, some tuning of your installation will be necessary before
> using it for anything other than extremely small databases.  At the
> very least, it will probably be necessary to increase your shared
> buffers setting.  See Chapter 16 for details on what tuning options
> are available to you.
> ---snip---

I think we actually need much more than this.  Kaarel on the Advocacy list has 
volunteered to try to extend our "getting started" section to encompass some 
basic tuning stuff.  Of course, more people would be better.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:37                 ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
@ 2003-07-04 19:18                   ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-07-04 19:20                     ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
  2003-07-04 19:29                     ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  0 siblings, 2 replies; 71+ messages in thread

From: Tom Lane @ 2003-07-04 19:18 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
>> ---snip---
>> By default, PostgreSQL is configured to run on minimal hardware.  As
>> a result, some tuning of your installation will be necessary before
>> using it for anything other than extremely small databases.  At the
>> very least, it will probably be necessary to increase your shared
>> buffers setting.  See Chapter 16 for details on what tuning options
>> are available to you.
>> ---snip---

> I think we actually need much more than this.

I am about to propose a patch that will cause the default shared_buffers
to be more realistic, say 1000, on machines where the kernel will allow
it.  Not sure if people will let me get away with applying it
post-feature-freeze, but if so that would change the terms of this
debate noticeably.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:37                 ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
  2003-07-04 19:18                   ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-04 19:20                     ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 71+ messages in thread

From: Josh Berkus @ 2003-07-04 19:20 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance

Tom,

> I am about to propose a patch that will cause the default shared_buffers
> to be more realistic, say 1000, on machines where the kernel will allow
> it.  Not sure if people will let me get away with applying it
> post-feature-freeze, but if so that would change the terms of this
> debate noticeably.

+1

-- 
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology 	josh@agliodbs.com
    and data management solutions 	(415) 565-7293
   for law firms, small businesses 	 fax 621-2533
    and non-profit organizations. 	San Francisco




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:37                 ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
  2003-07-04 19:18                   ` Re: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-07-04 19:29                     ` Rod Taylor <rbt@rbt.ca>
  1 sibling, 0 replies; 71+ messages in thread

From: Rod Taylor @ 2003-07-04 19:29 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Josh Berkus <josh@agliodbs.com>; Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance

> I am about to propose a patch that will cause the default shared_buffers
> to be more realistic, say 1000, on machines where the kernel will allow
> it.  Not sure if people will let me get away with applying it
> post-feature-freeze, but if so that would change the terms of this
> debate noticeably.

It's not a feature change, it's a bug fix -- bug being an oversight.

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: [PERFORM] PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
@ 2003-08-17 00:36                 ` Bruce Momjian <pgman@candle.pha.pa.us>
  2003-08-17 15:06                   ` Re: [PERFORM] PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2 siblings, 1 reply; 71+ messages in thread

From: Bruce Momjian @ 2003-08-17 00:36 UTC (permalink / raw)
  To: Andrew Sullivan <andrew@libertyrms.info>; +Cc: PostgreSQL-documentation <pgsql-docs@postgresql.org>


Do we need to add a mention of the need for tuning to the install docs?

---------------------------------------------------------------------------

Andrew Sullivan wrote:
> On Fri, Jul 04, 2003 at 08:07:18PM +0200, Arjen van der Meijden wrote:
> > > Andrew Sullivan wrote:
> > > results under production conditions, and not bother to read 
> > > even the basic "quickstart"-type stuff that is kicking 
> > > around.
> > Then please point out where it sais, in the documentation, that the
> > value for the shared_memory of 64 is too low and that 4000 is a nice
> > value to start with?
> 
> I think I did indeed speak too soon, as the criticism is a fair one:
> nowhere in the installation instructions or the "getting started"
> docs does it say that you really ought to do some tuning once you
> have the system installed.  Can I suggest for the time being that
> something along these lines should go in 14.6.3, "Tuning the
> installation":
> 
> ---snip---
> By default, PostgreSQL is configured to run on minimal hardware.  As
> a result, some tuning of your installation will be necessary before
> using it for anything other than extremely small databases.  At the
> very least, it will probably be necessary to increase your shared
> buffers setting.  See Chapter 16 for details on what tuning options
> are available to you.
> ---snip---
> 
> > I'm sorry to put this in a such a confronting manner, but you simply
> > can't expect people to search for information that they don't know the
> > existence of.
> 
> No need to apologise; I think you're right.
> 
> A
> 
> -- 
> ----
> Andrew Sullivan                         204-4141 Yonge Street
> Liberty RMS                           Toronto, Ontario Canada
> <andrew@libertyrms.info>                              M2P 2A8
>                                          +1 416 646 3304 x110
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: [PERFORM] PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-08-17 00:36                 ` Re: [PERFORM] PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-08-17 15:06                   ` Andrew Sullivan <andrew@libertyrms.info>
  2003-09-26 17:47                     ` Re: [PERFORM] PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 71+ messages in thread

From: Andrew Sullivan @ 2003-08-17 15:06 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Andrew Sullivan <andrew@libertyrms.info>; PostgreSQL-documentation <pgsql-docs@postgresql.org>

On Sat, Aug 16, 2003 at 08:36:57PM -0400, Bruce Momjian wrote:
> 
> Do we need to add a mention of the need for tuning to the install docs?

Wouldn't be a bad idea, as far as I'm concerned.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: [PERFORM] PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-08-17 00:36                 ` Re: [PERFORM] PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  2003-08-17 15:06                   ` Re: [PERFORM] PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
@ 2003-09-26 17:47                     ` Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 0 replies; 71+ messages in thread

From: Bruce Momjian @ 2003-09-26 17:47 UTC (permalink / raw)
  To: Andrew Sullivan <andrew@libertyrms.info>; +Cc: PostgreSQL-documentation <pgsql-docs@postgresql.org>

Andrew Sullivan wrote:
> On Sat, Aug 16, 2003 at 08:36:57PM -0400, Bruce Momjian wrote:
> > 
> > Do we need to add a mention of the need for tuning to the install docs?
> 
> Wouldn't be a bad idea, as far as I'm concerned.

OK, I added a 'Tuning' section to the install instructions.  I can make
adjustments.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Attachments:

/bjm/diff (text/plain, 1.3K) download Index: doc/src/sgml/installation.sgml =================================================================== RCS file: /cvsroot/pgsql-server/doc/src/sgml/installation.sgml,v retrieving revision 1.143 diff -c -c -r1.143 installation.sgml *** doc/src/sgml/installation.sgml 13 Sep 2003 17:01:09 -0000 1.143 --- doc/src/sgml/installation.sgml 26 Sep 2003 17:40:53 -0000 *************** *** 1156,1161 **** --- 1156,1181 ---- <title>Post-Installation Setup</title> <sect2> + <title>Tuning</title> + + <indexterm> + <primary>tuning</primary> + </indexterm> + + <para> + By default, <productname>PostgreSQL</> is configured to run on minimal + hardware. This allows it to start up with almost any hardware + configuration. However, the default configuration is not designed for + optimum performance. To achieve optimum performance, several server + variables must be adjusted, the two most common being + <varname>shared_buffers</varname> and <varname> sort_mem</varname> + mentioned in <xref linkend="runtime-config-resource-memory">. Other + paramters in <xref linkend="runtime-config-resource"> also affect + performance. + </para> + </sect2> + + <sect2> <title>Shared Libraries</title> <indexterm>
^ permalink raw reply [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
@ 2003-10-08 17:28                 ` Bruce Momjian <pgman@candle.pha.pa.us>
  2003-10-08 17:49                   ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
  2003-10-09 09:53                   ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2 siblings, 2 replies; 71+ messages in thread

From: Bruce Momjian @ 2003-10-08 17:28 UTC (permalink / raw)
  To: Andrew Sullivan <andrew@libertyrms.info>; +Cc: pgsql-performance

Andrew Sullivan wrote:
> On Fri, Jul 04, 2003 at 08:07:18PM +0200, Arjen van der Meijden wrote:
> > > Andrew Sullivan wrote:
> > > results under production conditions, and not bother to read 
> > > even the basic "quickstart"-type stuff that is kicking 
> > > around.
> > Then please point out where it sais, in the documentation, that the
> > value for the shared_memory of 64 is too low and that 4000 is a nice
> > value to start with?
> 
> I think I did indeed speak too soon, as the criticism is a fair one:
> nowhere in the installation instructions or the "getting started"
> docs does it say that you really ought to do some tuning once you
> have the system installed.  Can I suggest for the time being that
> something along these lines should go in 14.6.3, "Tuning the
> installation":
> 
> ---snip---
> By default, PostgreSQL is configured to run on minimal hardware.  As
> a result, some tuning of your installation will be necessary before
> using it for anything other than extremely small databases.  At the
> very least, it will probably be necessary to increase your shared
> buffers setting.  See Chapter 16 for details on what tuning options
> are available to you.
> ---snip---
> 
> > I'm sorry to put this in a such a confronting manner, but you simply
> > can't expect people to search for information that they don't know the
> > existence of.
> 
> No need to apologise; I think you're right.

Agreed.  Text added to install docs:

   <para>
    By default, <productname>PostgreSQL</> is configured to run on minimal
    hardware.  This allows it to start up with almost any hardware
    configuration. However, the default configuration is not designed for
    optimum performance. To achieve optimum performance, several server
    variables must be adjusted, the two most common being
    <varname>shared_buffers</varname> and <varname> sort_mem</varname>
    mentioned in <![%standalone-include[the documentation]]>
    <![%standalone-ignore[<xref linkend="runtime-config-resource-memory">]]>.
    Other parameters in <![%standalone-include[the documentation]]>
    <![%standalone-ignore[<xref linkend="runtime-config-resource">]]>
    also affect performance.
   </para>

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-10-08 17:28                 ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-10-08 17:49                   ` Josh Berkus <josh@agliodbs.com>
  2003-10-08 17:58                     ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 1 reply; 71+ messages in thread

From: Josh Berkus @ 2003-10-08 17:49 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; Andrew Sullivan <andrew@libertyrms.info>; +Cc: pgsql-performance

Bruce,

> Agreed.  Text added to install docs:
>
>    <para>
>     By default, <productname>PostgreSQL</> is configured to run on minimal
>     hardware.  This allows it to start up with almost any hardware
>     configuration. However, the default configuration is not designed for
>     optimum performance. To achieve optimum performance, several server
>     variables must be adjusted, the two most common being
>     <varname>shared_buffers</varname> and <varname> sort_mem</varname>
>     mentioned in <![%standalone-include[the documentation]]>
>     <![%standalone-ignore[<xref
> linkend="runtime-config-resource-memory">]]>. Other parameters in
> <![%standalone-include[the documentation]]> <![%standalone-ignore[<xref
> linkend="runtime-config-resource">]]> also affect performance.
>    </para>

What would you think of adding a condensed version of my and Shridhar's guide 
to the install docs?  I think I can offer a 3-paragraph version which would 
cover the major points of setting PostgreSQL.conf.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-10-08 17:28                 ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  2003-10-08 17:49                   ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
@ 2003-10-08 17:58                     ` Bruce Momjian <pgman@candle.pha.pa.us>
  2003-10-08 18:05                       ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 71+ messages in thread

From: Bruce Momjian @ 2003-10-08 17:58 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance

Josh Berkus wrote:
> Bruce,
> 
> > Agreed.  Text added to install docs:
> >
> >    <para>
> >     By default, <productname>PostgreSQL</> is configured to run on minimal
> >     hardware.  This allows it to start up with almost any hardware
> >     configuration. However, the default configuration is not designed for
> >     optimum performance. To achieve optimum performance, several server
> >     variables must be adjusted, the two most common being
> >     <varname>shared_buffers</varname> and <varname> sort_mem</varname>
> >     mentioned in <![%standalone-include[the documentation]]>
> >     <![%standalone-ignore[<xref
> > linkend="runtime-config-resource-memory">]]>. Other parameters in
> > <![%standalone-include[the documentation]]> <![%standalone-ignore[<xref
> > linkend="runtime-config-resource">]]> also affect performance.
> >    </para>
> 
> What would you think of adding a condensed version of my and Shridhar's guide 
> to the install docs?  I think I can offer a 3-paragraph version which would 
> cover the major points of setting PostgreSQL.conf.

Yes, I think that is a good idea --- now, does it go in the install
docs, or in the docs next to each GUC item?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-10-08 17:28                 ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  2003-10-08 17:49                   ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
  2003-10-08 17:58                     ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-10-08 18:05                       ` Josh Berkus <josh@agliodbs.com>
  2003-10-08 18:15                         ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  2003-10-08 18:28                         ` Re: PostgreSQL vs. MySQL Neil Conway <neilc@samurai.com>
  2003-10-08 19:58                         ` Re: PostgreSQL vs. MySQL Vivek Khera <khera@kcilink.com>
  0 siblings, 3 replies; 71+ messages in thread

From: Josh Berkus @ 2003-10-08 18:05 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance

Bruce,

> Yes, I think that is a good idea --- now, does it go in the install
> docs, or in the docs next to each GUC item?

Hmmm ... both, I think.   The Install Docs should have:

"Here are the top # things you will want to adjust in your PostgreSQL.conf:
1) Shared_buffers <link>
2) Sort_mem <link>
3) effective_cache_size <link>
4) random_page_cost <link>
5) Fsync <link>
etc."

Then next to each of these items in the Docs, I add 1-2 sentences about how to 
set that item.

Hmmm ... do we have similar instructions for setting connection options and 
pg_hba.conf?  We should have a P telling people they need to do this.

Barring an objection, I'll get to work on this.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-10-08 17:28                 ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  2003-10-08 17:49                   ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
  2003-10-08 17:58                     ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  2003-10-08 18:05                       ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
@ 2003-10-08 18:15                         ` Bruce Momjian <pgman@candle.pha.pa.us>
  2 siblings, 0 replies; 71+ messages in thread

From: Bruce Momjian @ 2003-10-08 18:15 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance


Totally agree.

---------------------------------------------------------------------------

Josh Berkus wrote:
> Bruce,
> 
> > Yes, I think that is a good idea --- now, does it go in the install
> > docs, or in the docs next to each GUC item?
> 
> Hmmm ... both, I think.   The Install Docs should have:
> 
> "Here are the top # things you will want to adjust in your PostgreSQL.conf:
> 1) Shared_buffers <link>
> 2) Sort_mem <link>
> 3) effective_cache_size <link>
> 4) random_page_cost <link>
> 5) Fsync <link>
> etc."
> 
> Then next to each of these items in the Docs, I add 1-2 sentences about how to 
> set that item.
> 
> Hmmm ... do we have similar instructions for setting connection options and 
> pg_hba.conf?  We should have a P telling people they need to do this.
> 
> Barring an objection, I'll get to work on this.
> 
> -- 
> Josh Berkus
> Aglio Database Solutions
> San Francisco
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-10-08 17:28                 ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  2003-10-08 17:49                   ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
  2003-10-08 17:58                     ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  2003-10-08 18:05                       ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
@ 2003-10-08 18:28                         ` Neil Conway <neilc@samurai.com>
  2 siblings, 0 replies; 71+ messages in thread

From: Neil Conway @ 2003-10-08 18:28 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Andrew Sullivan <andrew@libertyrms.info>; pgsql-performance

On Wed, 2003-10-08 at 14:05, Josh Berkus wrote:
> Hmmm ... both, I think.   The Install Docs should have:
> 
> "Here are the top # things you will want to adjust in your PostgreSQL.conf:
> 1) Shared_buffers <link>
> 2) Sort_mem <link>
> 3) effective_cache_size <link>
> 4) random_page_cost <link>
> 5) Fsync <link>
> etc."

> Barring an objection, I'll get to work on this.

I think this kind of information belongs in the documentation proper,
not in the installation instructions. I think you should put this kind
of tuning information in the "Performance Tips" chapter, and include a
pointer to it in the installation instructions.

-Neil





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-10-08 17:28                 ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  2003-10-08 17:49                   ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
  2003-10-08 17:58                     ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
  2003-10-08 18:05                       ` Re: PostgreSQL vs. MySQL Josh Berkus <josh@agliodbs.com>
@ 2003-10-08 19:58                         ` Vivek Khera <khera@kcilink.com>
  2 siblings, 0 replies; 71+ messages in thread

From: Vivek Khera @ 2003-10-08 19:58 UTC (permalink / raw)
  To: pgsql-performance

>>>>> "JB" == Josh Berkus <josh@agliodbs.com> writes:

JB> Hmmm ... both, I think.   The Install Docs should have:

JB> "Here are the top # things you will want to adjust in your PostgreSQL.conf:
JB> 1) Shared_buffers <link>
JB> 2) Sort_mem <link>
JB> 3) effective_cache_size <link>
JB> 4) random_page_cost <link>
JB> 5) Fsync <link>
JB> etc."

Add:

max_fsm_relations (perhaps it is ok with current default)
max_fsm_pages

I don't think you really want to diddle with fsync in the name of
speed at the cost of safety.

and possibly:

checkpoint_segments (if you do a lot of writes to the DB for extended
                    durations of time)  With 7.4 it warns you in the
                    logs if you should increase this.

-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:35         ` Re: PostgreSQL vs. MySQL Michael Mattox <michael.mattox@verideon.com>
  2003-07-04 15:26           ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 18:07             ` Re: PostgreSQL vs. MySQL Arjen van der Meijden <acm@tweakers.net>
  2003-07-04 18:28               ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-10-08 17:28                 ` Re: PostgreSQL vs. MySQL Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-10-09 09:53                   ` Andrew Sullivan <andrew@libertyrms.info>
  1 sibling, 0 replies; 71+ messages in thread

From: Andrew Sullivan @ 2003-10-09 09:53 UTC (permalink / raw)
  To: pgsql-performance

On Wed, Oct 08, 2003 at 01:28:53PM -0400, Bruce Momjian wrote:
> 
> Agreed.  Text added to install docs:

[&c.]

I think this is just right.  It tells a user where to find the info
needed, doesn't reproduce it all over the place, and still points out
that this is something you'd better do.  Combined with the new
probe-to-set-shared-buffers bit at install time, I think the reports
of 400 billion times worse performance than MySQL will probably
diminish.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
@ 2003-07-04 14:49         ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 15:06           ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
  2 siblings, 1 reply; 71+ messages in thread

From: Shridhar Daithankar @ 2003-07-04 14:49 UTC (permalink / raw)
  To: pgsql-performance

On 4 Jul 2003 at 16:35, Michael Mattox wrote:

> I see this as a major problem.  How many people run postgres, decide it's
> too slow and give up without digging into the documentation or coming to
> this group?  This seems to be pretty common.  Even worst, they tell 10
> others how slow Postgres is and then it gets a bad reputation.
> 
> In my opinion the defaults should be set up for a typical database server
> machine.

Well, there are few major reasons defaults are the way they are and the reason 
it hurts the way they are

1. Postgresql expects to start on every machine on which it can run. Now some 
of the arcane platforms need kernel recompilation to raise SHMMAX and defaults 
to 1MB.

2. Postgresql uses shared memory being process based architecture. Mysql uses 
process memory being threaded application. It does not need  kernel settings to 
work and usually works best it can.

3. We expect users/admins to be reading docs. If one does not read docs, it 
does not matter what defaults are. Sooner or later, it is going to fall on it's 
face.

4. Unlike likes of Oracle, postgresql does not pre-claim resources and starts 
hogging the system, replacing OS whereever possible. No it does not work that 
way..

One thing always strikes me. Lot of people(Not you Michael!..:-)) would 
complain that postgresql is slow and needs tweaking are not bothered by the 
fact that oracle needs almost same kind of and same amount of tweaking to get 
somewhere. Perception matterrs a lot.

I would have whined for java as well but this is not the forum for that..:-)

On a positive note, me and Josh are finishing a bare bone performance article 
that would answer lot of your questions. I am counting on you to provide 
valuable feedback. I expect it out tomorrow or on sunday..Josh will confirm 
that..


 
Bye
 Shridhar

--
Theorem: a cat has nine tails.Proof:	No cat has eight tails. A cat has one tail 
more than no cat.	Therefore, a cat has nine tails.




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:49         ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-04 15:06           ` Rod Taylor <rbt@rbt.ca>
  2003-07-04 15:18             ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  0 siblings, 1 reply; 71+ messages in thread

From: Rod Taylor @ 2003-07-04 15:06 UTC (permalink / raw)
  To: shridhar_daithankar@persistent.co.in; +Cc: pgsql-performance

> 2. Postgresql uses shared memory being process based architecture. Mysql uses 
> process memory being threaded application. It does not need  kernel settings to 
> work and usually works best it can.

MySQL has other issues with the kernel due to their threading choice 
such as memory limits per process, or poor threaded SMP support on some
platforms (inability for a single process to use more than one CPU at a 
time regardless of thread count).

Threads aren't an easy way around kernel limitations, which is probably
why Apache has gone for a combination of the two -- but of course that
adds complexity.

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
  2003-07-04 14:49         ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 15:06           ` Re: PostgreSQL vs. MySQL Rod Taylor <rbt@rbt.ca>
@ 2003-07-04 15:18             ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  0 siblings, 0 replies; 71+ messages in thread

From: Shridhar Daithankar @ 2003-07-04 15:18 UTC (permalink / raw)
  To: pgsql-performance

On Friday 04 July 2003 20:36, Rod Taylor wrote:
> > 2. Postgresql uses shared memory being process based architecture. Mysql
> > uses process memory being threaded application. It does not need  kernel
> > settings to work and usually works best it can.
>
> MySQL has other issues with the kernel due to their threading choice
> such as memory limits per process, or poor threaded SMP support on some
> platforms (inability for a single process to use more than one CPU at a
> time regardless of thread count).
>
> Threads aren't an easy way around kernel limitations, which is probably
> why Apache has gone for a combination of the two -- but of course that
> adds complexity.

Correct. It's not debate about whether threading is better or not. But it 
certainly affects the default way with which these two applications work.

 Shridhar




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 14:28       ` Re: PostgreSQL vs. MySQL Andrew Sullivan <andrew@libertyrms.info>
@ 2003-07-04 14:55         ` Rafal Kedziorski <rafcio@polonium.de>
  2 siblings, 0 replies; 71+ messages in thread

From: Rafal Kedziorski @ 2003-07-04 14:55 UTC (permalink / raw)
  To: pgsql-performance

hi,

At 20:19 04.07.2003 +0530, Shridhar Daithankar wrote:
[...]

>On a positive note, me and Josh are finishing a bare bone performance article

where will be this article published?

>that would answer lot of your questions. I am counting on you to provide
>valuable feedback. I expect it out tomorrow or on sunday..Josh will confirm
>that..


Rafal




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-04 14:34       ` Rod Taylor <rbt@rbt.ca>
  3 siblings, 0 replies; 71+ messages in thread

From: Rod Taylor @ 2003-07-04 14:34 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; +Cc: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>; pgsql-performance

> Please understand the limits of how much information a consultant can submit
> to an open list like this about a client's confidential information.  I've
> answered every question I _can_ answer and when I get hostility in response
> all I can do is sigh and move on.

Is there any chance you could show us an EXPLAIN ANALYZE output of the
poor performing query in question?

> I'm sorry if Shridhar is upset that I can't validate his favorite db but ad
> hominin comments aren't helpful.

It was me who gave the comment based upon previous threads which
requested information that had gone unanswered (not even a response
stating such information could not be provided).

The database you describe is quite small, so I'm not surprised MySQL
does well with it. That said, it isn't normal to experience poor
performance with PostgreSQL unless you've stumbled upon a poor spot (IN
based sub-queries used to be poor performing, aggregates can be slow,
mismatched datatypes, etc.).

Output of EXPLAIN ANALYZE of a contrived query representative of the
type of work done (that demonstrates the problem) with renamed tables
and columns would go a long way to helping us help you.

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-04 14:35       ` Tom Lane <tgl@sss.pgh.pa.us>
  3 siblings, 0 replies; 71+ messages in thread

From: Tom Lane @ 2003-07-04 14:35 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; +Cc: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>; pgsql-performance

"Brian Tarbox" <btarbox@theworld.com> writes:
> I'm not permitted to post the actual tables as per company policy.

Nobody wants to see your data, only the table schemas and queries.  If
you feel that even that contains some sensitive information, just rename
the table and field names to something meaningless.  But the kinds of
problems I am interested in finding out about require seeing the column
datatypes and the form of the queries.  The hardware and platform
details you gave mean nothing to me (and probably not to anyone else
either, given that you were comparing to MySQL on the same platform).

> I did no tuning of MySql.  The only tuning for PG was to vacuum and vacuum
> analyze.

If you didn't at least bump up shared_buffers, you were deliberately
skewing the results against Postgres.  Surely you can't have been
subscribed to pgsql-performance very long without knowing that the
default postgresql.conf settings are set up for a toy installation.

> all I can do is sigh and move on.

You're still looking for reasons not to answer our questions, aren't
you?  Do you actually want to find out what the problem was here?
If not, you're wasting our list bandwidth.  I'd like to find out,
if only so I can try to fix it in future releases, but without useful
information I'll just have to write this off as an unsubstantiated report.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-04 14:07     ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-05 23:40       ` Hilmar Lapp <hlapp@gmx.net>
  3 siblings, 0 replies; 71+ messages in thread

From: Hilmar Lapp @ 2003-07-05 23:40 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; +Cc: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>; pgsql-performance


On Friday, July 4, 2003, at 07:07  AM, Brian Tarbox wrote:

> We had about 40 tables in the db, with joined queries on about 8-12 
> tables.
>

A while ago a tested a moderately complex schema on MySQL, Pg, and 
Oracle. I usually heavily normalize schemas and then define views as a 
denormalized API, which sends MySQL to the book of toys already. The 
views more often than not would join anywhere from 6-12 tables, using 
plain (as opposed to compound) foreign keys to primary key straight 
joins.

I noticed that Pg was more than an order of magnitude slower for joins 
 > 8 tables than Oracle. I won't claim that none of this can have been 
due to lack of tuning. My point is the following though. After I dug in 
it turned out that of the 4 secs Pg needed to execute the query it 
spent 3.9 secs in the planner. The execution plan Pg came up with was 
pretty good - it just needed an extraordinary amount of time to arrive 
at it, spoiling its own results.

Asking this list I then learned how to tweak GEQO such that it would 
pick up the planning and do it faster than it would otherwise. I was 
able to get the planner time down to a quarter - still a multitude of 
the actual execution time.

I was told on this list that query planning suffers from combinatorial 
explosion very quickly - and I completely buy that. It's just - Oracle 
planned the same query in a fraction of a second, using the cost-based 
optimizer, on a slower machine. I've seen it plan 15-table joins in 
much less than a second, and I have no idea how it would do that. In 
addition, once you've prepared a query in Oracle, the execution plan is 
pre-compiled.

If I were a CS student I'd offer myself to the hall of humiliation and 
set out to write a fast query planner for Pg ...

	-hilmar
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
  2003-07-04 12:53   ` Re: PostgreSQL vs. MySQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-04 14:21     ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  3 siblings, 0 replies; 71+ messages in thread

From: Shridhar Daithankar @ 2003-07-04 14:21 UTC (permalink / raw)
  To: pgsql-performance; Brian Tarbox <btarbox@theworld.com>

On 4 Jul 2003 at 10:07, Brian Tarbox wrote:

> Ok, I'll give more data :-)
> 
> Under both MySql and Postgres the tests were run on a variety of systems,
> all with similar results.  My own personal testing was done on a P4 2.4Mhz,
> 512 mb memory, latest production versions of each database.  By vanilla
> RedHat I mean that I installed RH on a clean system, said install everything
> and did no customization of RH settings.
> We had about 40 tables in the db, with joined queries on about 8-12 tables.
> Some tables had 10,000 records, some 1000 records, other tables had dozens
> of records.  There were indexes on all join fields, and all join fields were
> listed as foriegn keys.  All join fields were unique primary keys in their
> home table (so the index distribution would be very spread out).  I'm not
> permitted to post the actual tables as per company policy.
> 
> I did no tuning of MySql.  The only tuning for PG was to vacuum and vacuum
> analyze.

No wonder pg bombed out so badly. In fact I am surprised it was slower only by 
factor of 3. 

Rule of thumb is if you have more than 1K records in any table, you got to tune 
postgresql.conf. I don't think I need to elaborate what difference tuning in 
postgresql.conf can make.

> 
> I'll also mention that comments like this one are not productive:
> 
> >I don't think Brian has any interest in being helped.
> 
> Please understand the limits of how much information a consultant can submit
> to an open list like this about a client's confidential information.  I've
> answered every question I _can_ answer and when I get hostility in response
> all I can do is sigh and move on.

Well, definition of threshold of hostile response differ from person to person. 
That is understood but by internet standards, I don't think you have received 
any hostile response. But that's not the topic I would like to continue to 
discuss.

What I would suggest you is to look at some other performance problem 
description submitted earlier. I don't think these guys have permission to 
disclose sensitive data either but they did everything they could in their 
limits.

Look at, http://archives.postgresql.org/pgsql-performance/2003-06/msg00134.php 
and the thread thereof. You can reach there from 
http://archives.postgresql.org/pgsql-performance/2003-06/threads.php

There is a reason why Michael got so many and so detailed responses. Within 
your limits, I am sure you could have posted more and earlier rather than 
posting details when original thread is long gone.


> I'm sorry if Shridhar is upset that I can't validate his favorite db but ad
> hominin comments aren't helpful.

I have no problems personally if postgresql does not work with you. The very 
first reason I stick with postgresql is that it works best for me. The moment 
it does not work for somebody else, there is a potential problem which I would 
like to rectify ASAP. That is the idea of getting on lists and forums.

It's not about product as much it is about helping each other.

And certainly. I have posted weirder qeuries here and I disagree that you 
couldn't post more. However this is a judgement from what you have posted and 
by all chances it is wrong. Never mind that.

At the end, it's the problem and solution that matters. Peace..

Bye
 Shridhar

--
Murphy's Laws:	(1) If anything can go wrong, it will.	(2) Nothing is as easy as 
it looks.	(3) Everything takes longer than you think it will.




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-04 13:50   ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  4 siblings, 0 replies; 71+ messages in thread

From: Christopher Kings-Lynne @ 2003-07-04 13:50 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; +Cc: Rafal Kedziorski <rafcio@polonium.de>; pgsql-performance

> I recently took a system from MySQL to Postgres.  Same HW, SW, same data.
> The major operations where moderately complex queries (joins on 8 tables).
>
> The results we got was that Postgres was fully 3 times slower than MySql.
> We were on this  list a fair bit looking for answers and tried all the
> standard answers.  It was still much  much much slower.

I have never found a query in MySQL that was faster than one in
PostgreSQL.

Chris





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-04 13:59   ` Tom Lane <tgl@sss.pgh.pa.us>
  4 siblings, 0 replies; 71+ messages in thread

From: Tom Lane @ 2003-07-04 13:59 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; +Cc: Rafal Kedziorski <rafcio@polonium.de>; pgsql-performance

"Brian Tarbox" <btarbox@theworld.com> writes:
> I recently took a system from MySQL to Postgres.  Same HW, SW, same data.
> The major operations where moderately complex queries (joins on 8 tables).

> The results we got was that Postgres was fully 3 times slower than MySql.
> We were on this  list a fair bit looking for answers and tried all the
> standard answers.  It was still much  much much slower.

Could we see the details?  It's not very fair to not give us a chance to
learn about problems.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
  2003-07-04 12:27 ` Re: PostgreSQL vs. MySQL Brian Tarbox <btarbox@theworld.com>
@ 2003-07-06 00:11   ` Hannu Krosing <hannu@tm.ee>
  4 siblings, 0 replies; 71+ messages in thread

From: Hannu Krosing @ 2003-07-06 00:11 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; +Cc: Rafal Kedziorski <rafcio@polonium.de>; pgsql-performance

Brian Tarbox kirjutas R, 04.07.2003 kell 15:27:
> I recently took a system from MySQL to Postgres.  Same HW, SW, same data.
> The major operations where moderately complex queries (joins on 8 tables).
> The results we got was that Postgres was fully 3 times slower than MySql.

For each and every query ??

> We were on this  list a fair bit looking for answers and tried all the
> standard answers. 

Could you post the list of "standard answers" you tried ?

>  It was still much  much much slower.

Was this with InnoDB ?

what kind of joins were they (i.e 
"FROM a JOIN b on a.i=b.i" 
or "FROM a,b WHERE a.i = b.i" ?

What was the ratio of planning time to actual execution time in pgsql?

Where the queries originally optimized for MySQL ?

----------------
Hannu



^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs. MySQL
  2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
@ 2003-07-04 16:14 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
  2 siblings, 0 replies; 71+ messages in thread

From: Achilleus Mantzios @ 2003-07-04 16:14 UTC (permalink / raw)
  To: Rafal Kedziorski <rafcio@polonium.de>; +Cc: pgsql-performance


PostgreSQL (as being a really advanced RDBMS),
generally requires some tuning in order to get
the best performance.

Your best bet is to try both.

Also check to see IF mysql has
-Referential integrity
-subselects
-transactions
-(other usefull features like arrays,user defined types,etc..)
(its probable that you will need some of the above)

On Fri, 4 Jul 2003, Rafal Kedziorski wrote:

> Hi,
> 
> has anybody tested PostgreSQL 7.3.x tables agains MySQL 4.0.12/13 with InnoDB?
> 
> 
> Regards,
> Rafal 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

-- 
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-210-8981112
fax:    +30-210-8981877
email:  achill at matrix dot gatewaynet dot com
        mantzios at softlab dot ece dot ntua dot gr




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs MySQL
@ 2003-10-09 17:30 David Griffiths <dgriffiths@boats.com>
  2003-10-09 18:02 ` Re: PostgreSQL vs MySQL Jeff <threshar@torgo.978.org>
  2003-10-09 19:58 ` Re: PostgreSQL vs MySQL Robert Treat <xzilla@users.sourceforge.net>
  2003-10-10 12:59 ` Re: PostgreSQL vs MySQL Thomas Swan <tswan@idigx.com>
  0 siblings, 3 replies; 71+ messages in thread

From: David Griffiths @ 2003-10-09 17:30 UTC (permalink / raw)
  To: pgsql-performance

This is a timely thread for myself, as I'm in the middle of testing both
databases as an Oracle replacement.
 
As of this moment, I know more about MySQL (tuning, setup, features)
than I do about Postgres. Not because I like MySQL more, but because
 
1) the MySQL docs are better (sorry - I found them easier to read, and
more comprehensive; I had an easier time finding the answers I needed)
2) there are more web pages devoted to MySQL (probably because it has a
bit more market share)
3) there are more books on MySQL at the bookstore (I haven't had a
chance to pick up Bruce's book yet; it might be all the book I'd ever
need)
4) we looked at MySQL first (we needed replication, and eRServer had not
been open-sourced when we started looking)
 
With regards to #1, I'd like to specifically mention tuning - the docs
at http://www.postgresql.org/docs/7.3/static/runtime-config.html
<http://www.postgresql.org/docs/7.3/static/runtime-config.html;  give a
basic explanation of the different options, but much more is needed for
tuning. I'm running into a problem with an update statement (that uses a
select in a sub-query) in Postgres - it's taking  hours to run (the
equiv, using a multi-table update statement in MySQL instead of a
sub-query, takes all of 2 seconds). I'll be posting it later once I do
more reading to make sure I've done as much as I can to solve it myself.
 
I really agree with this post:
 
"I guess my point is simply this: instead of saying: "okay we use
default settings that will run on _old_ hardware too" we should go for a
little script that creates a "still save but much better" config file.
There's just no point in setting SHARED_BUFFERS to something like 16
(what's the current default?) if the PC has >= 1 GB of RAM. Setting it
to 8192 would still be save, but 512 times better...  ;-) (IIRC 8192
would take 64 MB of RAM, which should be save if you leave the default
MAX_CONNECTIONS.)" It provides examples, and some real numbers to help
someone new to the database take an initial crack at tuning. Remember,
you're trying to compete with the big-guys (Oracle, etc), so providing
info that an Oracle DBA needs is pretty critical. I'm currently at a
complete loss for tuning Postgres (it seems to do things very
differently than both Oracle and MySQL).
 
 
I also have to admit a bit of irritation reading this thread; there is a
fair number of incorrect statements on this thread that, while not
wrong, definately aren't right:
 
"Speed depends on the nature of use and the complexity of queries.  If
you are doing updates of related tables, ACID is of vital importance and
MySQL doesn't provide it."
MySQL has ACID in InnoDB. I've found that MySQL is actually very fast on
complex queries w/InnoDB (six tables, 1 million rows, two of the joins
are outer-joins. In fact, I can get InnoDB to be almost as fast as
MyISAM. Complex updates are also very very fast. We have not tried
flooding either database with dozens of complex statements from multiple
clients; that's coming soon, and from what I've read, MySQL won't do too
well.
 
"using InnoDB tables (the only way to have foreign keys, transactions,
and row level locking for MySQL) makes MySQL slower and adds complexity
to tuning the database"
Adding this: "innodb_flush_method=O_DSYNC" to the my.cnf made InnoDB as
fast as MyISAM in our tests. It doesn't turn off disk flushing; it's
just a flush method that might work better with different kernels and
drives; it's one of those "play with this and see if it helps"
parameters; there are lots of those in Postgres, it seems. There are 10
variables for tuning InnoDB (and you don't have to tune for MyISAM, so
it's actually a six-of-one, half-dozen-of-the-other). Setup between the
two seems to be about the same.
 
"PostgreSQL supports constraints. MySQL doesn't; programmers need to
take care of that from the client side"
Again, InnoDB supports constraints.
 
"Transactions: We've been here before. Suffice to say, MySQL+InnoDB is
almost there. Plain ol' MySQL doesn't have it, which tells you something
about their philosophy towards database design."
InnoDB supports transactions very nicely, has the equivalent of WAL, and
one thing I really like: a tablespace (comprised of data files that can
be spread around multiple hard drives), and in a month or so, InnoDB
will support multiple tablespaces.
 
 
To be fair, here are a few MySQL "bad-things" that weren't mentioned:
 
1) InnoDB can't do a hot-backup with the basic backup tools. To
hot-backup an InnoDB database, you need to pay $450 US per database per
year ($1150 per database perpetual) for a proprietary hot-backup tool
2) InnoDB can't do full-text searching.
3) I see alot more corrupt-database bugs on the MySQL lists (most are
MyISAM, but a few InnoDB bugs pop up from time to time) - way more than
I see on the Postgres lists.
4) There are some really cranky people on the MySQL lists; the Postgres
lists seem to be much more effective (esp. with people like Tom Lane).
Maybe it's because they get alot of dumb questions, as people unfamiliar
with databases turn to MySQL first?
 
Maybe the Postgres community needs an anti-FUD individual or two; people
that know both databases, and can provide the proper information for
answering questions like this. A section in the docs would help as well.
Yes, I know many of the people advocating Postgres do not want to
compare themselves to MySQL (but rather to Oracle, Sybase, DB2, etc) ,
but the volume of responses on a thread like this indicates that the
comparison is going to happen regardless. Better to nip it in the bud
quickly than let it go on over 3-4 days.
 
One last observation: someone looking at both databases, reading those
posts, might get a bad impression of Postgres based on the inconsistency
and incorrectness of some of the statements made about MySQL. If a
salesperson provides misinformation about a competitors product and you
find out about it, that salesperson has most likely lost a customer.
 
Anyway, I hope I haven't offended anyone - I'm not trying to troll or
flame, but rather just give some constructive criticism from someone
outside both the MySQL and Postgres camps.
 
David
 

^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs MySQL
  2003-10-09 17:30 Re: PostgreSQL vs MySQL David Griffiths <dgriffiths@boats.com>
@ 2003-10-09 18:02 ` Jeff <threshar@torgo.978.org>
  2003-10-09 19:25   ` Re: PostgreSQL vs MySQL scott.marlowe <scott.marlowe@ihs.com>
  2 siblings, 1 reply; 71+ messages in thread

From: Jeff @ 2003-10-09 18:02 UTC (permalink / raw)
  To: David Griffiths <dgriffiths@boats.com>; +Cc: pgsql-performance

On Thu, 9 Oct 2003, David Griffiths wrote:

> 1) the MySQL docs are better (sorry - I found them easier to read, and
> more comprehensive; I had an easier time finding the answers I needed)

Huh. I had the opposite experience. Each to his own.
I think everybody agrees PG needs a better tuning doc (or pointers to it,
or something).

> "Speed depends on the nature of use and the complexity of queries.  If
> you are doing updates of related tables, ACID is of vital importance and
> MySQL doesn't provide it."

I don't know if you looked at my presentation. But in preparation for it I
checked out MySQL 4.0.x[most recent stable]. I found that I violates the C
in acid in some places. ie you can insert a date of 0000/00/00 and have it
sit there and be fine.  Perhaps this is the fault of mysql's timestamp
type.

> MyISAM. Complex updates are also very very fast. We have not tried
> flooding either database with dozens of complex statements from multiple
> clients;

You don't need complex statements to topple mysql over in high
concurrency. I was doing fairly simple queries with 20 load generators -
it didn't like it.  Not at all (mysql: 650 seconds pg: 220)

> 3) I see alot more corrupt-database bugs on the MySQL lists (most are
> MyISAM, but a few InnoDB bugs pop up from time to time) - way more than
> I see on the Postgres lists.

I saw this as well. I was seeing things in the changelog as late as
september (this year) about fixing bugs that cause horrific corruption.
That doesn't make me feel comfy.  Remember - in reality InnoDB is still
very new.  The PG stuff has been tinkered with for years.  I like
innovation and new things, but in some cases, I prefer the old code
that has been looked at for years.


--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/





^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs MySQL
  2003-10-09 17:30 Re: PostgreSQL vs MySQL David Griffiths <dgriffiths@boats.com>
  2003-10-09 18:02 ` Re: PostgreSQL vs MySQL Jeff <threshar@torgo.978.org>
@ 2003-10-09 19:25   ` scott.marlowe <scott.marlowe@ihs.com>
  2003-10-09 19:56     ` Re: PostgreSQL vs MySQL Jason Hihn <jhihn@paytimepayroll.com>
  0 siblings, 1 reply; 71+ messages in thread

From: scott.marlowe @ 2003-10-09 19:25 UTC (permalink / raw)
  To: Jeff <threshar@torgo.978.org>; +Cc: David Griffiths <dgriffiths@boats.com>; pgsql-performance

On Thu, 9 Oct 2003, Jeff wrote:

> On Thu, 9 Oct 2003, David Griffiths wrote:
> 
> > 1) the MySQL docs are better (sorry - I found them easier to read, and
> > more comprehensive; I had an easier time finding the answers I needed)
> 
> Huh. I had the opposite experience. Each to his own.
> I think everybody agrees PG needs a better tuning doc (or pointers to it,
> or something).

I think the issue is that Postgresql documentation is oriented towards DBA 
types, who already understand databases in general, so they can find what 
they want, while MySQL docs are oriented towards dbms newbies, who don't 
know much, if anything, about databases.




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs MySQL
  2003-10-09 17:30 Re: PostgreSQL vs MySQL David Griffiths <dgriffiths@boats.com>
  2003-10-09 18:02 ` Re: PostgreSQL vs MySQL Jeff <threshar@torgo.978.org>
  2003-10-09 19:25   ` Re: PostgreSQL vs MySQL scott.marlowe <scott.marlowe@ihs.com>
@ 2003-10-09 19:56     ` Jason Hihn <jhihn@paytimepayroll.com>
  0 siblings, 0 replies; 71+ messages in thread

From: Jason Hihn @ 2003-10-09 19:56 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; Jeff <threshar@torgo.978.org>; +Cc: David Griffiths <dgriffiths@boats.com>; pgsql-performance

I concur 100%. PostgreSQL was big and scary and MySQL seemed cute and
cuddly, warm and fuzzy. Then I took my undergrad CS RDBMS course (a course
that focused on designing the backend software), and only then was I ready
to appreciate and wield the battle axe that is PostgreSQL.

He also let me use PostgreSQL for my final project (the standard was
Oracle). I got an A. :)

I do have to admit that I prefer OSS (and docs) better than proprietary. I
had some Informix work and that was not fun at all. So even though the MySQL
is pink fuzzy bunnies, PostgreSQL is at least a brown fuzzy bunny [to me
anyway].

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of
> scott.marlowe
> Sent: Thursday, October 09, 2003 3:26 PM
> To: Jeff
> Cc: David Griffiths; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] PostgreSQL vs MySQL
>
>
> On Thu, 9 Oct 2003, Jeff wrote:
>
> > On Thu, 9 Oct 2003, David Griffiths wrote:
> >
> > > 1) the MySQL docs are better (sorry - I found them easier to read, and
> > > more comprehensive; I had an easier time finding the answers I needed)
> >
> > Huh. I had the opposite experience. Each to his own.
> > I think everybody agrees PG needs a better tuning doc (or
> pointers to it,
> > or something).
>
> I think the issue is that Postgresql documentation is oriented
> towards DBA
> types, who already understand databases in general, so they can find what
> they want, while MySQL docs are oriented towards dbms newbies, who don't
> know much, if anything, about databases.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs MySQL
  2003-10-09 17:30 Re: PostgreSQL vs MySQL David Griffiths <dgriffiths@boats.com>
@ 2003-10-09 19:58 ` Robert Treat <xzilla@users.sourceforge.net>
  2 siblings, 0 replies; 71+ messages in thread

From: Robert Treat @ 2003-10-09 19:58 UTC (permalink / raw)
  To: David Griffiths <dgriffiths@boats.com>; +Cc: pgsql-performance

On Thu, 2003-10-09 at 13:30, David Griffiths wrote:
> I also have to admit a bit of irritation reading this thread; there is a
> fair number of incorrect statements on this thread that, while not
> wrong, definately aren't right:
>  
> "Speed depends on the nature of use and the complexity of queries.  If
> you are doing updates of related tables, ACID is of vital importance and
> MySQL doesn't provide it."
> MySQL has ACID in InnoDB. 

Actually it only kinda sorta has acid.  As Jeff mentioned, and it can be
expanded upon, mysql has a nasty habit of transforming invalid data into
something that will insert into a table and not telling you about it. I
think Josh mentioned reports that it ignores some constraint
definitions.  And then theres the whole mixing MyISAM and InnoDB tables
completely breaks the ability to rollback transactions...

>  
> "using InnoDB tables (the only way to have foreign keys, transactions,
> and row level locking for MySQL) makes MySQL slower and adds complexity
> to tuning the database"
> Adding this: "innodb_flush_method=O_DSYNC" to the my.cnf made InnoDB as
> fast as MyISAM in our tests. It doesn't turn off disk flushing; it's
> just a flush method that might work better with different kernels and
> drives; it's one of those "play with this and see if it helps"
> parameters; there are lots of those in Postgres, it seems. There are 10
> variables for tuning InnoDB (and you don't have to tune for MyISAM, so
> it's actually a six-of-one, half-dozen-of-the-other). Setup between the
> two seems to be about the same.

Well, I've yet to see MySQL benchmark themselves vs. the big boys using
InnoDB tables, I'm only guessing that it's because those tables are
slower. (Well, guessing and calling upon experience) Sure there may be
work arounds, but that does add a certain complexity. (Bonus for us,
PostgreSQL is just complex from the get go :-P )

>  
> "PostgreSQL supports constraints. MySQL doesn't; programmers need to
> take care of that from the client side"
> Again, InnoDB supports constraints.
>  

We've seen evidence it doesn't. If they've fixed this great. Of course
I'll quote from the mysql docs 

"InnoDB allows you to drop any table even though that would break the
foreign key constraints which reference the table."  

last I knew it did this silently and without warning. there are other
issues as well, so it's support is relative...

> "Transactions: We've been here before. Suffice to say, MySQL+InnoDB is
> almost there. Plain ol' MySQL doesn't have it, which tells you something
> about their philosophy towards database design."
> InnoDB supports transactions very nicely, has the equivalent of WAL, and
> one thing I really like: a tablespace (comprised of data files that can
> be spread around multiple hard drives), and in a month or so, InnoDB
> will support multiple tablespaces.
>  

Just don't mix InnoDB and MyISAM tables together or you could end up in
a world of trouble... its unfortunate that this breaks one of the main
keys to building a DBMS, namely hiding implementation details from the
end users. 

> Maybe the Postgres community needs an anti-FUD individual or two; people
> that know both databases, and can provide the proper information for
> answering questions like this. 

Well, among the major advocacy folk we do have a mantra about no FUD,
but these are public lists so we cant really stop people from posting. 
Of course this overlooks the fact that different people interpret
different information differently. (heh)  Take this quote I saw posted
in a non postgresql forum a while back: "MySQL doesn't fully support
subqueries" which of course created a slew of posts about FUD and
postgresql users being idiots. If course, when the posted responded back
with the question "Can mysql do subselects in the SELECT, FROM, and
WHERE clauses like postgresql, and nest subselects within those
subselects?" it stopped everyone in their tracks...

> A section in the docs would help as well.

In the docs no, on techdocs, maybe. 

> Yes, I know many of the people advocating Postgres do not want to
> compare themselves to MySQL (but rather to Oracle, Sybase, DB2, etc) ,
> but the volume of responses on a thread like this indicates that the
> comparison is going to happen regardless. Better to nip it in the bud
> quickly than let it go on over 3-4 days.
>  

It was due to the help of postgresql users that the following site has
become available: http://sql-info.de/mysql/gotchas.html
I'd suggest you look it over if your trying to evaluate a switch from
Oracle to MySQL.

And anyone is welcome, actually encouraged, to correct erroneous
information they see posted about any system on these lists. God bless
if you're willing to try and follow every list every day to watch for
these types of posts. 


> One last observation: someone looking at both databases, reading those
> posts, might get a bad impression of Postgres based on the inconsistency
> and incorrectness of some of the statements made about MySQL. If a
> salesperson provides misinformation about a competitors product and you
> find out about it, that salesperson has most likely lost a customer.
> 

Unfortunate that you'd attribute anyone who posts on these lists as a
sales person for postgresql...

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL




^ permalink  raw  reply  [nested|flat] 71+ messages in thread

* Re: PostgreSQL vs MySQL
  2003-10-09 17:30 Re: PostgreSQL vs MySQL David Griffiths <dgriffiths@boats.com>
@ 2003-10-10 12:59 ` Thomas Swan <tswan@idigx.com>
  2 siblings, 0 replies; 71+ messages in thread

From: Thomas Swan @ 2003-10-10 12:59 UTC (permalink / raw)
  To: David Griffiths <dgriffiths@boats.com>; +Cc: pgsql-performance

David Griffiths wrote:

> This is a timely thread for myself, as I'm in the middle of testing 
> both databases as an Oracle replacement.
>  
> As of this moment, I know more about MySQL (tuning, setup, features) 
> than I do about Postgres. Not because I like MySQL more, but because
>  
> 1) the MySQL docs are better (sorry - I found them easier to read, and 
> more comprehensive; I had an easier time finding the answers I needed)
> 2) there are more web pages devoted to MySQL (probably because it has 
> a bit more market share)
> 3) there are more books on MySQL at the bookstore (I haven't had a 
> chance to pick up Bruce's book yet; it might be all the book I'd ever 
> need)
> 4) we looked at MySQL first (we needed replication, and eRServer had 
> not been open-sourced when we started looking)
>  
> With regards to #1, I'd like to specifically mention tuning - the docs 
> at http://www.postgresql.org/docs/7.3/static/runtime-config.html give 
> a basic explanation of the different options, but much more is needed 
> for tuning. I'm running into a problem with an update statement (that 
> uses a select in a sub-query) in Postgres - it's taking  hours to run 
> (the equiv, using a multi-table update statement in MySQL instead of a 
> sub-query, takes all of 2 seconds). I'll be posting it later once I do 
> more reading to make sure I've done as much as I can to solve it myself.


David,

I think you have valid observations.  And the issue regarding 
replication has been quite a hot topic on occasion in the developer 
lists.   I'm hoping at some point it would become part of the standard 
PostgreSQL package; but point in time recovery, PITR, is needed as a 
stepping stone to providing that functionality.

Have you attempted the multi table update inside of a transaction for 
PostgreSQL yet and thus assuring the all of your updates are only 
visible after the commit?   Depending on the design and the nature of 
the updates, their could be a race condition if the updates on one table 
are utilized by another process before the rest of the updates have 
completed.

Sets of updates in a single transaction can improve performance as well.

>  
> I really agree with this post:
>  
> "I guess my point is simply this: instead of saying: "okay we use 
> default settings that will run on _old_ hardware too" we should go for 
> a little script that creates a "still save but much better" config 
> file. There's just no point in setting SHARED_BUFFERS to something 
> like 16 (what's the current default?) if the PC has >= 1 GB of RAM. 
> Setting it to 8192 would still be save, but 512 times better...  ;-) 
> (IIRC 8192 would take 64 MB of RAM, which should be save if you leave 
> the default MAX_CONNECTIONS.)" It provides examples, and some real 
> numbers to help someone new to the database take an initial crack at 
> tuning. Remember, you're trying to compete with the big-guys (Oracle, 
> etc), so providing info that an Oracle DBA needs is pretty critical. 
> I'm currently at a complete loss for tuning Postgres (it seems to do 
> things very differently than both Oracle and MySQL).
>  
>  
> I also have to admit a bit of irritation reading this thread; there is 
> a fair number of incorrect statements on this thread that, while not 
> wrong, definately aren't right:
>  
> "Speed depends on the nature of use and the complexity of queries.  If 
> you are doing updates of related tables, ACID is of vital importance 
> and MySQL doesn't provide it."
> MySQL has ACID in InnoDB. I've found that MySQL is actually very fast 
> on complex queries w/InnoDB (six tables, 1 million rows, two of the 
> joins are outer-joins. In fact, I can get InnoDB to be almost as fast 
> as MyISAM. Complex updates are also very very fast. We have not tried 
> flooding either database with dozens of complex statements from 
> multiple clients; that's coming soon, and from what I've read, MySQL 
> won't do too well.
>  
> "using InnoDB tables (the only way to have foreign keys, transactions, 
> and row level locking for MySQL) makes MySQL slower and adds 
> complexity to tuning the database"
> Adding this: "innodb_flush_method=O_DSYNC" to the my.cnf made InnoDB 
> as fast as MyISAM in our tests. It doesn't turn off disk flushing; 
> it's just a flush method that might work better with different kernels 
> and drives; it's one of those "play with this and see if it helps" 
> parameters; there are lots of those in Postgres, it seems. There are 
> 10 variables for tuning InnoDB (and you don't have to tune for MyISAM, 
> so it's actually a six-of-one, half-dozen-of-the-other). Setup between 
> the two seems to be about the same.
>  
> "PostgreSQL supports constraints. MySQL doesn't; programmers need to 
> take care of that from the client side"
> Again, InnoDB supports constraints.
>  
> "Transactions: We've been here before. Suffice to say, MySQL+InnoDB is 
> almost there. Plain ol' MySQL doesn't have it, which tells you 
> something about their philosophy towards database design."
> InnoDB supports transactions very nicely, has the equivalent of WAL, 
> and one thing I really like: a tablespace (comprised of data files 
> that can be spread around multiple hard drives), and in a month or so, 
> InnoDB will support multiple tablespaces.
>  
>  
> To be fair, here are a few MySQL "bad-things" that weren't mentioned:
>  
> 1) InnoDB can't do a hot-backup with the basic backup tools. To 
> hot-backup an InnoDB database, you need to pay $450 US per database 
> per year ($1150 per database perpetual) for a proprietary hot-backup tool
> 2) InnoDB can't do full-text searching.
> 3) I see alot more corrupt-database bugs on the MySQL lists (most are 
> MyISAM, but a few InnoDB bugs pop up from time to time) - way more 
> than I see on the Postgres lists.
> 4) There are some really cranky people on the MySQL lists; the 
> Postgres lists seem to be much more effective (esp. with people like 
> Tom Lane). Maybe it's because they get alot of dumb questions, as 
> people unfamiliar with databases turn to MySQL first?
>  
> Maybe the Postgres community needs an anti-FUD individual or two; 
> people that know both databases, and can provide the proper 
> information for answering questions like this. A section in the docs 
> would help as well. Yes, I know many of the people advocating Postgres 
> do not want to compare themselves to MySQL (but rather to Oracle, 
> Sybase, DB2, etc) , but the volume of responses on a thread like this 
> indicates that the comparison is going to happen regardless. Better to 
> nip it in the bud quickly than let it go on over 3-4 days.
>  
> One last observation: someone looking at both databases, reading those 
> posts, might get a bad impression of Postgres based on the 
> inconsistency and incorrectness of some of the statements made about 
> MySQL. If a salesperson provides misinformation about a competitors 
> product and you find out about it, that salesperson has most likely 
> lost a customer.
>  
> Anyway, I hope I haven't offended anyone - I'm not trying to troll or 
> flame, but rather just give some constructive criticism from someone 
> outside both the MySQL and Postgres camps.
>  
> David
>  






^ permalink  raw  reply  [nested|flat] 71+ messages in thread


end of thread, other threads:[~2003-10-10 12:59 UTC | newest]

Thread overview: 71+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-04 10:03 PostgreSQL vs. MySQL Rafal Kedziorski <rafcio@polonium.de>
2003-07-04 11:12 ` Richard Huxton <dev@archonet.com>
2003-07-04 12:27 ` Brian Tarbox <btarbox@theworld.com>
2003-07-04 12:36   ` Michael Mattox <michael.mattox@verideon.com>
2003-07-04 12:43     ` Brian Tarbox <btarbox@theworld.com>
2003-07-04 12:46       ` Michael Mattox <michael.mattox@verideon.com>
2003-07-04 12:51         ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-07 17:35       ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-07 18:16         ` Brian Tarbox <btarbox@world.std.com>
2003-07-07 19:58           ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-20 23:24           ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-07-20 23:23         ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-07-04 12:53   ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-04 13:11     ` Rod Taylor <rbt@rbt.ca>
2003-07-04 13:20     ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-04 13:45       ` Rod Taylor <rbt@rbt.ca>
2003-07-04 14:23         ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-04 16:10           ` Brian Tarbox <btarbox@theworld.com>
2003-07-04 16:22             ` Bjoern Metzdorf <bm@turtle-entertainment.de>
2003-07-04 16:39               ` Kevin Schroeder <kschroeder@mirageworks.com>
2003-07-04 21:14               ` Brian Tarbox <btarbox@theworld.com>
2003-07-04 22:24                 ` Bjoern Metzdorf <bm@turtle-entertainment.de>
2003-07-05 16:39                   ` Grega Bremec <gregab@noviforum.si>
2003-07-04 17:05             ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-07-04 18:15               ` Arjen van der Meijden <acm@tweakers.net>
2003-07-04 17:08             ` Andrew Sullivan <andrew@libertyrms.info>
2003-07-04 17:12             ` Josh Berkus <josh@agliodbs.com>
2003-07-04 14:07     ` Brian Tarbox <btarbox@theworld.com>
2003-07-04 14:28       ` Andrew Sullivan <andrew@libertyrms.info>
2003-07-04 14:35         ` Michael Mattox <michael.mattox@verideon.com>
2003-07-04 14:49           ` Rod Taylor <rbt@rbt.ca>
2003-07-04 20:39             ` johnnnnnn <john@phaedrusdeinus.org>
2003-07-04 21:28               ` Tim Conrad <conrad@external.timconrad.org>
2003-07-04 15:26           ` Andrew Sullivan <andrew@libertyrms.info>
2003-07-04 15:28             ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-04 15:42               ` Kevin Schroeder <kschroeder@mirageworks.com>
2003-07-04 18:07             ` Arjen van der Meijden <acm@tweakers.net>
2003-07-04 18:28               ` Andrew Sullivan <andrew@libertyrms.info>
2003-07-04 18:37                 ` Josh Berkus <josh@agliodbs.com>
2003-07-04 19:18                   ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-04 19:20                     ` Josh Berkus <josh@agliodbs.com>
2003-07-04 19:29                     ` Rod Taylor <rbt@rbt.ca>
2003-08-17 00:36                 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-17 15:06                   ` Andrew Sullivan <andrew@libertyrms.info>
2003-09-26 17:47                     ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-10-08 17:28                 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-10-08 17:49                   ` Josh Berkus <josh@agliodbs.com>
2003-10-08 17:58                     ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-10-08 18:05                       ` Josh Berkus <josh@agliodbs.com>
2003-10-08 18:15                         ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-10-08 18:28                         ` Neil Conway <neilc@samurai.com>
2003-10-08 19:58                         ` Vivek Khera <khera@kcilink.com>
2003-10-09 09:53                   ` Andrew Sullivan <andrew@libertyrms.info>
2003-07-04 14:49         ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-04 15:06           ` Rod Taylor <rbt@rbt.ca>
2003-07-04 15:18             ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-04 14:55         ` Rafal Kedziorski <rafcio@polonium.de>
2003-07-04 14:34       ` Rod Taylor <rbt@rbt.ca>
2003-07-04 14:35       ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-05 23:40       ` Hilmar Lapp <hlapp@gmx.net>
2003-07-04 14:21     ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-04 13:50   ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-07-04 13:59   ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-06 00:11   ` Hannu Krosing <hannu@tm.ee>
2003-07-04 16:14 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-10-09 17:30 Re: PostgreSQL vs MySQL David Griffiths <dgriffiths@boats.com>
2003-10-09 18:02 ` Re: PostgreSQL vs MySQL Jeff <threshar@torgo.978.org>
2003-10-09 19:25   ` Re: PostgreSQL vs MySQL scott.marlowe <scott.marlowe@ihs.com>
2003-10-09 19:56     ` Re: PostgreSQL vs MySQL Jason Hihn <jhihn@paytimepayroll.com>
2003-10-09 19:58 ` Re: PostgreSQL vs MySQL Robert Treat <xzilla@users.sourceforge.net>
2003-10-10 12:59 ` Re: PostgreSQL vs MySQL Thomas Swan <tswan@idigx.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox