Horton

Thread

  1. Re: Oracle vs PostgreSQL in real life

    Alex Avriette <a_avriette@acs.org> — 2002-02-27T17:32:14Z

    The "test" is a big batch that computes stuffs in the database. Here are the
timings of both Oracle and PG (7.2) :

Oracle on NT 4 : 45 minuts to go , 1200 tps (yes one thousand and two
hundred
tps)

Linux Red Hat 7.2 with PostgreSQL 7.2 : hours to go (statistically, 45
hours),
80 tps (eighty tps).

---

Jean-Paul, I think the problem here is not having postgres configured
properly. I am in a similar situation here where we are migrating data from
postgres into oracle. Postgres has been as much as 40x faster than Oracle in
many situations here. Note also that our oracle instance is on a quad
processor Sun 280R, and our postgres 'instance' is on a p3/1ghz. Iterating
over 440,000 xml 'text' fields in oracle takes about 4 days. In postgres it
takes 8 hours. Iterating over a 3.5M row table is just inconceivable for
oracle, and I do it in postgres all the time.

My suspicion is that our oracle instance is not tuned very well, and the
code that is manipulating the database (in this case perl) is much smarter
for postgres (we have separate developers to do perl-oracle interfaces).

Postgres is a fantastic, fast database. But you really must configure it,
and code intelligently to use it.

-alex

  2. Re: Oracle vs PostgreSQL in real life

    Jean-Paul ARGUDO <jean-paul.argudo@idealx.com> — 2002-02-27T17:44:53Z

    Okay,

To answer many replies (thanks!), I'll try to put more details:

* DELL server
	P3 600 MHZ 
	256 M ram
	RAID 5 

* kernel

Linux int2412 2.4.9-21SGI_XFS_1.0.2 #1 Thu Feb 7 16:50:37 CET 2002 i686 unknown	

with aacraid-cox because aacraid had poor perfs with this server (at 1st we
tought about raid5 problems)

* postgresql.conf : here are _all_ uncomented parameters:

tcpip_socket = true
max_connections = 16
port = 5432

shared_buffers = 19000          # 2*max_connections, min 16
max_fsm_relations = 200         # min 10, fsm is free space map
max_fsm_pages = 12000           # min 1000, fsm is free space map
max_locks_per_transaction = 256 # min 10
wal_buffers = 24                # min 4

sort_mem = 8192            # min 32
vacuum_mem = 8192          # min 1024


wal_debug = 0             # range 0-16

fsync = true

silent_mode = true
log_connections = false
log_timestamp = false
log_pid = false

debug_level = 0 # range 0-16

debug_print_query = false
debug_print_parse = false
debug_print_rewritten = false
debug_print_plan = false
debug_pretty_print = false
show_parser_stats = false
show_planner_stats = false
show_executor_stats = false
show_query_stats = false

transform_null_equals = true

* /proc parameters:

proc/sys/kernel/shmall => 184217728   (more than 130M)
proc/sys/kernel/shmall => 184217728  

* we made a bunch of vmstat logs too, we made graphics to understand, all in a
  postscript file, with gun graph ... this is very interesting, but as I dont
know if attachments are autorized here, please tell me if I can post it too. It
shows swap in/out, memory, I/O, etc.. 


Thanks for your support!


-- 
Jean-Paul ARGUDO

  3. Re: Oracle vs PostgreSQL in real life

    Mattew T. O'Connor <matthew@rh71.postgresql.org> — 2002-02-27T23:35:33Z

    > shared_buffers = 19000          # 2*max_connections, min 16

This number sounds too high.  If you only have 256M RAM, this is using over 
150 of it.  Are you swapping alot?  What is the load on the server while it's 
runing?

  4. Re: Oracle vs PostgreSQL in real life

    bpalmer <bpalmer@crimelabs.net> — 2002-02-28T14:00:05Z

    > many situations here. Note also that our oracle instance is on a quad
> processor Sun 280R, and our postgres 'instance' is on a p3/1ghz. Iterating

A 280r is a 2 way system,  not 4 way (hence the 2 in 280).

- Brandon

----------------------------------------------------------------------------
 c: 646-456-5455                                            h: 201-798-4983
 b. palmer,  bpalmer@crimelabs.net           pgp:crimelabs.net/bpalmer.pgp5

  5. Re: Oracle vs PostgreSQL in real life

    Michael Meskes <meskes@postgresql.org> — 2002-03-01T08:03:12Z

    On Wed, Feb 27, 2002 at 06:44:53PM +0100, Jean-Paul ARGUDO wrote:
> To answer many replies (thanks!), I'll try to put more details:
> ... 
> Linux int2412 2.4.9-21SGI_XFS_1.0.2 #1 Thu Feb 7 16:50:37 CET 2002 i686 unknown	

But you know that kernels up to 2.4.10 had huge problems with virtual
memory, don#t you. I'd recommend testing it either on 2.4.17 (which seems to
run stable for me) or, if you want to be sure and do not need SMP, use
2.2.20.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

  6. Re: Oracle vs PostgreSQL in real life

    Michael Tiemann <tiemann@redhat.com> — 2002-03-01T12:19:23Z

    The number 2.4.9-21 corresponds to the (Red Hat) kernel I'm running right now. 
  Yes, 2.4.X as released from kernel.org had huge problems with virtual memory 
(for virually all values of X), but many of these problems have been addressed 
by keeping the kernel relatively frozen and just working on VM problems (which 
is one of the things we've been doing at Red Hat).  I'm not saying we've got it 
totally nailed just yet, but I want to present the view that some branches of 
the Linux kernel *have* been given the attention they need to avoid some of the 
well-known problems that linux.org kernels are (essentially--through Linus's 
law) designed to find.

M

Michael Meskes wrote:
> On Wed, Feb 27, 2002 at 06:44:53PM +0100, Jean-Paul ARGUDO wrote:
> 
>>To answer many replies (thanks!), I'll try to put more details:
>>... 
>>Linux int2412 2.4.9-21SGI_XFS_1.0.2 #1 Thu Feb 7 16:50:37 CET 2002 i686 unknown	
>>
> 
> But you know that kernels up to 2.4.10 had huge problems with virtual
> memory, don#t you. I'd recommend testing it either on 2.4.17 (which seems to
> run stable for me) or, if you want to be sure and do not need SMP, use
> 2.2.20.
> 
> Michael
>