Thread: my boss want to migrate to ORACLE

Stephane Tessier, CISSP
Development Team Leader
450-430-8166 X:206

On Thu, 29 Jul 2004 03:08 am, Stephane Tessier wrote:
> Hi everyone,
>
> somebody can help me??????? my boss want to migrate to
> ORACLE................
>
> we have a BIG problem of performance,it's slow....
> we use postgres 7.3 for php security application with approximately 4
> millions of insertion by day and 4 millions of delete and update
> and archive db with 40 millions of archived stuff...
This is heavy update.  as I say below, what is the vacuum setup like?
>
> we have 10 databases for our clients and a centralized database for the
> general stuff.
>
> database specs:
>
> double XEON 2.4 on DELL PowerEdge2650
> 2 gigs of RAM
> 5 SCSI Drive RAID 5 15rpm
>
> tasks:
>
> 4 millions of transactions by day
> 160 open connection 24 hours by day 7 days by week
> pg_autovacuum running 24/7
> reindex on midnight
Where is your pg_autovacuum config?  how often is it set to vacuum? and
analyze for that matter.


> postgresql.conf:
>
> tcpip_socket = true
> #ssl = false
>
> max_connections = 256
> #superuser_reserved_connections = 2
>
> #port = 5432
> #hostname_lookup = false
> #show_source_port = false
>
> #unix_socket_directory = ''
> #unix_socket_group = ''
> #unix_socket_permissions = 0777 # octal
>
> #virtual_host = ''
>
> #krb_server_keyfile = ''
>
>
> #
> #       Shared Memory Size
> #
> #shared_buffers = 256           # min max_connections*2 or 16, 8KB each
> #shared_buffers = 196000                # min max_connections*2 or 16, 8KB
> each
> shared_buffers = 128000         # min max_connections*2 or 16, 8KB each
> #max_fsm_relations = 1000       # min 10, fsm is free space map, ~40 bytes
> max_fsm_pages = 1000000         # min 1000, fsm is free space map, ~6 bytes
> #max_locks_per_transaction = 64 # min 10
> #wal_buffers = 8                # min 4, typically 8KB each
I would assume given heavy update you need more WAL buffers, but then I don't
know a lot.
>
> #
> #       Non-shared Memory Sizes
> #
> #sort_mem = 32168       # min 64, size in KB
> #vacuum_mem = 8192              # min 1024, size in KB
> vacuum_mem = 65536              # min 1024, size in KB
>
> #
> #       Write-ahead log (WAL)
> #
> #checkpoint_segments = 3        # in logfile segments, min 1, 16MB each
> #checkpoint_timeout = 300       # range 30-3600, in seconds
3 checkpoint_segments is too low for the number of inserts/delete/updates you
are doing.  you need a much larger check_point, something like 10+  but the
tuning docs will give you a better idea.

> #
> #commit_delay = 0               # range 0-100000, in microseconds
> #commit_siblings = 5            # range 1-1000
> #
> #fsync = true
> #wal_sync_method = fsync        # the default varies across platforms:
> #                               # fsync, fdatasync, open_sync, or
> open_datasync
> #wal_debug = 0                  # range 0-16
>
>
> #
> #       Optimizer Parameters
> #
> #enable_seqscan = true
> #enable_indexscan = true
> #enable_tidscan = true
> #enable_sort = true
> #enable_nestloop = true
> #enable_mergejoin = true
> #enable_hashjoin = true
>
> #effective_cache_size = 1000    # typically 8KB each
> effective_cache_size = 196608   # typically 8KB each
> #random_page_cost = 4           # units are one sequential page fetch cost
> #cpu_tuple_cost = 0.01          # (same)
> #cpu_index_tuple_cost = 0.001   # (same)
> #cpu_operator_cost = 0.0025     # (same)
>
> #default_statistics_target = 10 # range 1-1000
>
> #
> #       GEQO Optimizer Parameters
> #
> #geqo = true
> #geqo_selection_bias = 2.0      # range 1.5-2.0
> #geqo_threshold = 11
> #geqo_pool_size = 0             # default based on tables in statement,
>                                 # range 128-1024
> #geqo_effort = 1
> #geqo_generations = 0
> #geqo_random_seed = -1          # auto-compute seed
>
>
> #
> #       Message display
> #
> server_min_messages =notice     # Values, in order of decreasing detail:
>                                 #   debug5, debug4, debug3, debug2, debug1,
>                                 #   info, notice, warning, error, log,
> fatal,
>                                 #   panic
> client_min_messages =notice     # Values, in order of decreasing detail:
>                                 #   debug5, debug4, debug3, debug2, debug1,
>                                 #   log, info, notice, warning, error
> #silent_mode = false
>
> #log_connections =true
> #log_pid =true
> #log_statement =true
> #log_duration =true
> #log_timestamp =true
>
> log_min_error_statement =error
> # Values in order of increasing severity:
>                                  #   debug5, debug4, debug3, debug2,
> debug1, #   info, notice, warning, error, panic(off)
>
> #debug_print_parse = false
> #debug_print_rewritten = false
> #debug_print_plan = false
> #debug_pretty_print = false
>
> #explain_pretty_print = true
>
> # requires USE_ASSERT_CHECKING
> #debug_assertions = true
>
>
> #
> #       Syslog
> #
> syslog = 0                      # range 0-2
> syslog_facility = 'LOCAL0'
> syslog_ident = 'postgres'
>
>
> #
> #       Statistics
> #
> show_parser_stats = false
> show_planner_stats =false
> show_executor_stats = false
> show_statement_stats =false
>
> # requires BTREE_BUILD_STATS
> #show_btree_build_stats = false
>
>
> #
> #       Access statistics collection
> #
> stats_start_collector = true
> stats_reset_on_server_start = false
> stats_command_string = true
> stats_row_level = true
> stats_block_level = true
>
>
> #
> #       Lock Tracing
> #
> #trace_notify = false
>
> # requires LOCK_DEBUG
> #trace_locks = false
> #trace_userlocks = false
> #trace_lwlocks = false
> #debug_deadlocks = false
> #trace_lock_oidmin = 16384
> #trace_lock_table = 0
>
>
> #
> #       Misc
> #
> #autocommit = true
> #dynamic_library_path = '$libdir'
> #search_path = '$user,public'
> #datestyle = 'iso, us'
> #timezone = unknown             # actually, defaults to TZ environment
> setting
> #australian_timezones = false
> #client_encoding = sql_ascii    # actually, defaults to database encoding
> #authentication_timeout = 60    # 1-600, in seconds
> #deadlock_timeout = 1000        # in milliseconds
> #default_transaction_isolation = 'read committed'
> #max_expr_depth = 10000         # min 10
> #max_files_per_process = 1000   # min 25
> #password_encryption = true
> #sql_inheritance = true
> #transform_null_equals = false
> #statement_timeout = 0          # 0 is disabled, in milliseconds
> #db_user_namespace = false
>
> ***************************************************************************
>*
> ***************************************************************************
>
> Stephane Tessier, CISSP
> Development Team Leader
> 450-430-8166 X:206

Quoting Stephane Tessier <stephane.tessier@abovesecurity.com>:


General parameter suggestions:

> shared_buffers = 128000         # min max_connections*2 or 16, 8KB each
> effective_cache_size = 196608   # typically 8KB each

Try reducing shared_buffers (say 30000). There has been much discussion regards
setting this parameter - most folks see minimal gains, or even performance
*loss* with settings higher than 30000.

> #sort_mem = 32168       # min 64, size in KB

Depending on what queries you are experiencing problems with, you might want to
set this parameter to something. However with 160 connections you need to be
careful, as each one uses this much memory (if it needs to sort).

> #wal_buffers = 8

Try this in the 100-1000 range

> #checkpoint_segments = 3

Try this quite a lot higher - say 10-50.


Pg_autovacuum:

I have not used this, so can't really give any help about setup. However you can
check how well it is working by selecting relname, reltuples, relpages out of
pg_class, and see if you have relations with stupidly low numbers of rows per
page (clearly you need to be ANALYZing reasonably frequently for this query to
give accurate information).

Other Comments:

To provide you with more help, we need to know what is slow. Particulary what
queries are the most troublesome (post EXPLAIN ANALYZE of them). You can
identify these by setting the log_statement and log_duration parameters.

Finally: (comment for your boss), it is *not* a given that ORACLE will perform
any better.....

regards

Mark

A furthur thought or two:

- you are *sure* that it is Postgres that is slow? (could be Php...or your
  machine could be out of some resource - see next 2 points)
- is your machine running out of cpu or memory?
- is your machine seeing huge io transfers or long io waits?
- are you running Php on this machine as well as Postgres?
- what os (and what release) are you running? (guessing Linux but...)

As an aside, they always say this but: Postgres 7.4 generally performs better
than 7.3...so an upgrade could be worth it - *after* you have solved/identified
the other issues.

best wishes

Mark

Quoting Stephane Tessier <stephane.tessier@abovesecurity.com>:

> Hi everyone,
>
> somebody can help me??????? my boss want to migrate to
> ORACLE................
>
> we have a BIG problem of performance,it's slow....
> we use postgres 7.3 for php security application with approximately 4
> millions of insertion by day and 4 millions of delete and update
> and archive db with 40 millions of archived stuff...
>
> we have 10 databases for our clients and a centralized database for the
> general stuff.
>
> database specs:
>
> double XEON 2.4 on DELL PowerEdge2650
> 2 gigs of RAM
> 5 SCSI Drive RAID 5 15rpm
>
> tasks:
>
> 4 millions of transactions by day
> 160 open connection 24 hours by day 7 days by week
> pg_autovacuum running 24/7
> reindex on midnight

On Jul 28, 2004, at 1:08 PM, Stephane Tessier wrote:

> we have a BIG problem of performance,it's slow....

Can you isolate which part is slow? (log_min_duration is useful for
finding your slow running queries)

> we use postgres 7.3 for php security application with approximately 4
> millions of insertion by day and 4 millions of delete and update

That is pretty heavy write volume.   Are these updates done in batches
or "now and then"?  If they are done in batches you could speed them up
by wrapping them inside a transaction.

> #shared_buffers = 256           # min max_connections*2 or 16, 8KB each
> #shared_buffers = 196000                # min max_connections*2 or 16,
> 8KB each
> shared_buffers = 128000         # min max_connections*2 or 16, 8KB each
>
Too much. Generally over 10000 will stop benefitting you.

> #wal_buffers = 8                # min 4, typically 8KB each

Might want to bump this up

> #checkpoint_segments = 3        # in logfile segments, min 1, 16MB each

Given your write volume, increase this up a bit.. oh.. 20 or 30 of them
will help a lot.
But it will use 16*30MB of disk space.

Oracle is *NOT* a silver bullet.
It will not instantly make your problems go away.

I'm working on a project porting some things to Oracle and as a test I
also ported it to Postgres.  And you know what? Postgres is running
about 30% faster than Oracle.  The Oracle lovers here are not too happy
with that one :)  Just so you know..

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

I think with your help guys I'll do it!

I'm working on it!

I'll work on theses issues:

we have space for more ram(we use 2 gigs on possibility of 3 gigs)
iowait is very high 98% --> look like postgresql wait for io access
raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each
write on disk
use more transactions (we have a lot of insert/update without transaction).
cpu look like not running very hard

*php is not running on the same machine
*redhat enterprise 3.0 ES
*the version of postgresql is 7.3.4(using RHDB from redhat)
*pg_autovacuum running at 12 and 24 hour each day



-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of
markir@coretech.co.nz
Sent: 29 juillet, 2004 23:00
To: Stephane Tessier
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] my boss want to migrate to ORACLE


A furthur thought or two:

- you are *sure* that it is Postgres that is slow? (could be Php...or your
  machine could be out of some resource - see next 2 points)
- is your machine running out of cpu or memory?
- is your machine seeing huge io transfers or long io waits?
- are you running Php on this machine as well as Postgres?
- what os (and what release) are you running? (guessing Linux but...)

As an aside, they always say this but: Postgres 7.4 generally performs
better
than 7.3...so an upgrade could be worth it - *after* you have
solved/identified
the other issues.

best wishes

Mark

Quoting Stephane Tessier <stephane.tessier@abovesecurity.com>:

> Hi everyone,
>
> somebody can help me??????? my boss want to migrate to
> ORACLE................
>
> we have a BIG problem of performance,it's slow....
> we use postgres 7.3 for php security application with approximately 4
> millions of insertion by day and 4 millions of delete and update
> and archive db with 40 millions of archived stuff...
>
> we have 10 databases for our clients and a centralized database for the
> general stuff.
>
> database specs:
>
> double XEON 2.4 on DELL PowerEdge2650
> 2 gigs of RAM
> 5 SCSI Drive RAID 5 15rpm
>
> tasks:
>
> 4 millions of transactions by day
> 160 open connection 24 hours by day 7 days by week
> pg_autovacuum running 24/7
> reindex on midnight



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

Stephane wrote:
Hi everyone,
 
somebody can help me??????? my boss want to migrate to ORACLE................

#fsync = true
[snip]

Are you using battery baked RAID?

Your problem is probably due to the database syncing all the time.  With fsync one, you get 1 sync per transaction that
updates,deletes, etc.  4 million writes/day = 46 writes/sec avg.  Of course, writes will be very bursty, and when you
getover 100 you are going to have problems even on 15k system.  All databases have this problem, including Oracle.
KeepingWAL and data on separate volumes helps a lot.  It's quicker and easier to use hardware solution tho. 

If you want to run fsync on with that much I/O, consider using a battery backed raid controller that caches writes.
Thiswill make a *big* difference.  A quick'n'dirty test is to turn fsync off for a little while to see if this fixes
yourperformance problems. 

Merlin

On Fri, 2004-07-30 at 07:56, Stephane Tessier wrote:
> I think with your help guys I'll do it!
>
> I'm working on it!
>
> I'll work on theses issues:
>
> we have space for more ram(we use 2 gigs on possibility of 3 gigs)
> iowait is very high 98% --> look like postgresql wait for io access
> raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each
> write on disk

Just get battery backed cache on your RAID controller.  RAID0 is way too
unreliable for a production environment.  One disk dies and all your
data is just gone.

> use more transactions (we have a lot of insert/update without transaction).
> cpu look like not running very hard
>
> *php is not running on the same machine
> *redhat enterprise 3.0 ES
> *the version of postgresql is 7.3.4(using RHDB from redhat)
> *pg_autovacuum running at 12 and 24 hour each day
>
>
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of
> markir@coretech.co.nz
> Sent: 29 juillet, 2004 23:00
> To: Stephane Tessier
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] my boss want to migrate to ORACLE
>
>
> A furthur thought or two:
>
> - you are *sure* that it is Postgres that is slow? (could be Php...or your
>   machine could be out of some resource - see next 2 points)
> - is your machine running out of cpu or memory?
> - is your machine seeing huge io transfers or long io waits?
> - are you running Php on this machine as well as Postgres?
> - what os (and what release) are you running? (guessing Linux but...)
>
> As an aside, they always say this but: Postgres 7.4 generally performs
> better
> than 7.3...so an upgrade could be worth it - *after* you have
> solved/identified
> the other issues.
>
> best wishes
>
> Mark
>
> Quoting Stephane Tessier <stephane.tessier@abovesecurity.com>:
>
> > Hi everyone,
> >
> > somebody can help me??????? my boss want to migrate to
> > ORACLE................
> >
> > we have a BIG problem of performance,it's slow....
> > we use postgres 7.3 for php security application with approximately 4
> > millions of insertion by day and 4 millions of delete and update
> > and archive db with 40 millions of archived stuff...
> >
> > we have 10 databases for our clients and a centralized database for the
> > general stuff.
> >
> > database specs:
> >
> > double XEON 2.4 on DELL PowerEdge2650
> > 2 gigs of RAM
> > 5 SCSI Drive RAID 5 15rpm
> >
> > tasks:
> >
> > 4 millions of transactions by day
> > 160 open connection 24 hours by day 7 days by week
> > pg_autovacuum running 24/7
> > reindex on midnight
>
>
>
> ---------------------------(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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

Stephane Tessier wrote:

>I think with your help guys I'll do it!
>
>I'm working on it!
>
>I'll work on theses issues:
>
>we have space for more ram(we use 2 gigs on possibility of 3 gigs)
>iowait is very high 98% --> look like postgresql wait for io access
>raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each
>write on disk
>use more transactions (we have a lot of insert/update without transaction).
>cpu look like not running very hard
>
>*php is not running on the same machine
>*redhat enterprise 3.0 ES
>*the version of postgresql is 7.3.4(using RHDB from redhat)
>*pg_autovacuum running at 12 and 24 hour each day
>
>
What do you mean by "pg_autovacuum running at 12 and 24 hour each day"?

On Fri, 30 Jul 2004, Matthew T. O'Connor wrote:

> Stephane Tessier wrote:
>
> >I think with your help guys I'll do it!
> >
> >I'm working on it!
> >
> >I'll work on theses issues:
> >
> >we have space for more ram(we use 2 gigs on possibility of 3 gigs)
> >iowait is very high 98% --> look like postgresql wait for io access
> >raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each
> >write on disk
> >use more transactions (we have a lot of insert/update without transaction).
> >cpu look like not running very hard
> >
> >*php is not running on the same machine
> >*redhat enterprise 3.0 ES
> >*the version of postgresql is 7.3.4(using RHDB from redhat)
> >*pg_autovacuum running at 12 and 24 hour each day
> >
> >
> What do you mean by "pg_autovacuum running at 12 and 24 hour each day"?

I suspect he means at 1200 and 2400 each day (i.e noon and midnight).

--
Dan Langille - http://www.langille.org/

pg_autovacuum is a daemon, not something that get's run twice a day.
I think that's what the question Matthew was getting @.  I'm not sure
what would happen to performance if pg_autovacuum was launched twice a
day from cron, but you could end up in an ugly situation if it starts
up.

--brian

On Jul 30, 2004, at 12:11 PM, Dan Langille wrote:

> On Fri, 30 Jul 2004, Matthew T. O'Connor wrote:
>
>> Stephane Tessier wrote:
>>
>>> I think with your help guys I'll do it!
>>>
>>> I'm working on it!
>>>
>>> I'll work on theses issues:
>>>
>>> we have space for more ram(we use 2 gigs on possibility of 3 gigs)
>>> iowait is very high 98% --> look like postgresql wait for io access
>>> raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for
>>> each
>>> write on disk
>>> use more transactions (we have a lot of insert/update without
>>> transaction).
>>> cpu look like not running very hard
>>>
>>> *php is not running on the same machine
>>> *redhat enterprise 3.0 ES
>>> *the version of postgresql is 7.3.4(using RHDB from redhat)
>>> *pg_autovacuum running at 12 and 24 hour each day
>>>
>>>
>> What do you mean by "pg_autovacuum running at 12 and 24 hour each
>> day"?
>
> I suspect he means at 1200 and 2400 each day (i.e noon and midnight).
>
> --
> Dan Langille - http://www.langille.org/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend

After a long battle with technology, stephane.tessier@abovesecurity.com ("Stephane Tessier"), an earthling, wrote:
> I think with your help guys I'll do it!
>
> I'm working on it!
>
> I'll work on theses issues:
>
> we have space for more ram(we use 2 gigs on possibility of 3 gigs)

That _may_ help; not completely clear.

> iowait is very high 98% --> look like postgresql wait for io access

In that case, if you haven't got a RAID controller with battery backed
cache, then that should buy you a BIG boost in performance.  Maybe
$1500 USD; that could be money FABULOUSLY well spent.

> raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each
> write on disk

I try to avoid talking about RAID levels, and leave them to others
:-).

Sticking WAL on a solid state disk would be WAY COOL; you almost
certainly are hitting WAL really hard, which eventually cooks disks.
What is unfortunate is that there doesn't seem to be a "low end" 1GB
SSD; I'd hope that would cost $5K, and that might give a bigger boost
than the battery-backed RAID controller with lotsa cache.

> use more transactions (we have a lot of insert/update without
> transaction).

That'll help unless you get the RAID controller, in which case WAL
updates become much cheaper.

> cpu look like not running very hard

Not surprising.

> *php is not running on the same machine
> *redhat enterprise 3.0 ES
> *the version of postgresql is 7.3.4(using RHDB from redhat)

All makes sense.  It would be attractive to move to 7.4.2 or 7.4.3;
they're really quite a lot faster.  If there's no option to migrate
quickly, then 7.5 has interesting cache management changes that ought
to help even more, particularly with your vacuuming issues :-).

But it's probably better to get two incremental changes; migrating to
7.4, and being able to tell the boss "That improved performance by
x%", and then doing _another_ upgrade that _also_ improves things
should provide a pretty compelling argument in favour of keeping up
the good work with PostgreSQL.

> *pg_autovacuum running at 12 and 24 hour each day

That really doesn't make sense.

The point of pg_autovacuum is for it to run 24 hours a day.

If you kick it off twice, once at 11:59, then stop it, and then once
at 23:59, and then stop it, it shouldn't actually do any work.  Or
have you set it up with a 'sleep period' of ~12 hours?
--
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://www.ntlug.org/~cbbrowne/sgml.html
The *Worst* Things  to Say to a  Police Officer: Hey, is that  a 9 mm?
That's nothing compared to this .44 magnum.

On Fri, 2004-07-30 at 19:22, Christopher Browne wrote:
> After a long battle with technology, stephane.tessier@abovesecurity.com ("Stephane Tessier"), an earthling, wrote:
> > I think with your help guys I'll do it!
> >
> > I'm working on it!
> >
> > I'll work on theses issues:
> >
> > we have space for more ram(we use 2 gigs on possibility of 3 gigs)
>
> That _may_ help; not completely clear.
>
> > iowait is very high 98% --> look like postgresql wait for io access
>
> In that case, if you haven't got a RAID controller with battery backed
> cache, then that should buy you a BIG boost in performance.  Maybe
> $1500 USD; that could be money FABULOUSLY well spent.
>
> > raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each
> > write on disk
>
> I try to avoid talking about RAID levels, and leave them to others
> :-).

FYI, in a previous post on this topic, the original poster put up a top
output that showed the machine using 2 gigs of swap with 150 Meg for
kernel cache, and all the memory being used by a few postgresql
processes.  The machine was simply configured to give WAY too much
memory to shared buffers and sort mem and this was likely causing the
big slowdown.

Adding a battery backed caching RAID controller and properly configuring
postgresql.conf should get him into the realm of reasonable performance.

I checked and we have a 128 megs battery backed cache on the raid
controller...

-----Original Message-----
From: Scott Marlowe [mailto:smarlowe@qwest.net]
Sent: 30 juillet, 2004 11:15
To: Stephane Tessier
Cc: markir@coretech.co.nz; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] my boss want to migrate to ORACLE


On Fri, 2004-07-30 at 07:56, Stephane Tessier wrote:
> I think with your help guys I'll do it!
>
> I'm working on it!
>
> I'll work on theses issues:
>
> we have space for more ram(we use 2 gigs on possibility of 3 gigs)
> iowait is very high 98% --> look like postgresql wait for io access
> raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each
> write on disk

Just get battery backed cache on your RAID controller.  RAID0 is way too
unreliable for a production environment.  One disk dies and all your
data is just gone.

> use more transactions (we have a lot of insert/update without
transaction).
> cpu look like not running very hard
>
> *php is not running on the same machine
> *redhat enterprise 3.0 ES
> *the version of postgresql is 7.3.4(using RHDB from redhat)
> *pg_autovacuum running at 12 and 24 hour each day
>
>
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of
> markir@coretech.co.nz
> Sent: 29 juillet, 2004 23:00
> To: Stephane Tessier
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] my boss want to migrate to ORACLE
>
>
> A furthur thought or two:
>
> - you are *sure* that it is Postgres that is slow? (could be Php...or your
>   machine could be out of some resource - see next 2 points)
> - is your machine running out of cpu or memory?
> - is your machine seeing huge io transfers or long io waits?
> - are you running Php on this machine as well as Postgres?
> - what os (and what release) are you running? (guessing Linux but...)
>
> As an aside, they always say this but: Postgres 7.4 generally performs
> better
> than 7.3...so an upgrade could be worth it - *after* you have
> solved/identified
> the other issues.
>
> best wishes
>
> Mark
>
> Quoting Stephane Tessier <stephane.tessier@abovesecurity.com>:
>
> > Hi everyone,
> >
> > somebody can help me??????? my boss want to migrate to
> > ORACLE................
> >
> > we have a BIG problem of performance,it's slow....
> > we use postgres 7.3 for php security application with approximately 4
> > millions of insertion by day and 4 millions of delete and update
> > and archive db with 40 millions of archived stuff...
> >
> > we have 10 databases for our clients and a centralized database for the
> > general stuff.
> >
> > database specs:
> >
> > double XEON 2.4 on DELL PowerEdge2650
> > 2 gigs of RAM
> > 5 SCSI Drive RAID 5 15rpm
> >
> > tasks:
> >
> > 4 millions of transactions by day
> > 160 open connection 24 hours by day 7 days by week
> > pg_autovacuum running 24/7
> > reindex on midnight
>
>
>
> ---------------------------(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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

Stephane Tessier wrote:

> I checked and we have a 128 megs battery backed cache on the raid
> controller...

>>we have space for more ram(we use 2 gigs on possibility of 3 gigs)
>>iowait is very high 98% --> look like postgresql wait for io access
>>raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each
>>write on disk
>
> Just get battery backed cache on your RAID controller.  RAID0 is way too
> unreliable for a production environment.  One disk dies and all your
> data is just gone.

I'm the one who sent the e-mail about RAID 5's 4 writes, but I suggested
you look at RAID 10, not RAID 0.

--

  James Thornton
______________________________________________________
Internet Business Consultant, http://jamesthornton.com

oups,

i changed for RAID 10(strip and mirror)....

-----Original Message-----
From: James Thornton [mailto:james@jamesthornton.com]
Sent: 2 aout, 2004 17:32
To: Stephane Tessier
Cc: 'Scott Marlowe'; markir@coretech.co.nz;
pgsql-performance@postgresql.org
Subject: Re: [PERFORM] my boss want to migrate to ORACLE


Stephane Tessier wrote:

> I checked and we have a 128 megs battery backed cache on the raid
> controller...

>>we have space for more ram(we use 2 gigs on possibility of 3 gigs)
>>iowait is very high 98% --> look like postgresql wait for io access
>>raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each
>>write on disk
>
> Just get battery backed cache on your RAID controller.  RAID0 is way too
> unreliable for a production environment.  One disk dies and all your
> data is just gone.

I'm the one who sent the e-mail about RAID 5's 4 writes, but I suggested
you look at RAID 10, not RAID 0.

--

  James Thornton
______________________________________________________
Internet Business Consultant, http://jamesthornton.com

It may be worth pricing up expansion options e.g. 256M or more.
The other path to consider is changing RAID5 -> RAID10 if your card supports it.

However, I would recommend reducing that shared_buffers setting and doing your
performance measurements *again* - before changing anything else. This is
because you want to ensure that all your io hammering is not just because you
are making the machine swap (by giving postgres too much memory as Scott
mentioned)!

Quoting Stephane Tessier <stephane.tessier@abovesecurity.com>:

> I checked and we have a 128 megs battery backed cache on the raid
> controller...

Is it set to write back or write through?  Also, you may want to look at
lowering the stripe size.  The default on many RAID controllers is 128k,
but for PostgreSQL 8k to 32k seems a good choice.  But that's not near
as important as the cache setting being write back.

On Mon, 2004-08-02 at 15:18, Stephane Tessier wrote:
> I checked and we have a 128 megs battery backed cache on the raid
> controller...
>
> -----Original Message-----
> From: Scott Marlowe [mailto:smarlowe@qwest.net]
> Sent: 30 juillet, 2004 11:15
> To: Stephane Tessier
> Cc: markir@coretech.co.nz; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] my boss want to migrate to ORACLE
>
>
> On Fri, 2004-07-30 at 07:56, Stephane Tessier wrote:
> > I think with your help guys I'll do it!
> >
> > I'm working on it!
> >
> > I'll work on theses issues:
> >
> > we have space for more ram(we use 2 gigs on possibility of 3 gigs)
> > iowait is very high 98% --> look like postgresql wait for io access
> > raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each
> > write on disk
>
> Just get battery backed cache on your RAID controller.  RAID0 is way too
> unreliable for a production environment.  One disk dies and all your
> data is just gone.
>
> > use more transactions (we have a lot of insert/update without
> transaction).
> > cpu look like not running very hard
> >
> > *php is not running on the same machine
> > *redhat enterprise 3.0 ES
> > *the version of postgresql is 7.3.4(using RHDB from redhat)
> > *pg_autovacuum running at 12 and 24 hour each day
> >
> >
> >
> > -----Original Message-----
> > From: pgsql-performance-owner@postgresql.org
> > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of
> > markir@coretech.co.nz
> > Sent: 29 juillet, 2004 23:00
> > To: Stephane Tessier
> > Cc: pgsql-performance@postgresql.org
> > Subject: Re: [PERFORM] my boss want to migrate to ORACLE
> >
> >
> > A furthur thought or two:
> >
> > - you are *sure* that it is Postgres that is slow? (could be Php...or your
> >   machine could be out of some resource - see next 2 points)
> > - is your machine running out of cpu or memory?
> > - is your machine seeing huge io transfers or long io waits?
> > - are you running Php on this machine as well as Postgres?
> > - what os (and what release) are you running? (guessing Linux but...)
> >
> > As an aside, they always say this but: Postgres 7.4 generally performs
> > better
> > than 7.3...so an upgrade could be worth it - *after* you have
> > solved/identified
> > the other issues.
> >
> > best wishes
> >
> > Mark
> >
> > Quoting Stephane Tessier <stephane.tessier@abovesecurity.com>:
> >
> > > Hi everyone,
> > >
> > > somebody can help me??????? my boss want to migrate to
> > > ORACLE................
> > >
> > > we have a BIG problem of performance,it's slow....
> > > we use postgres 7.3 for php security application with approximately 4
> > > millions of insertion by day and 4 millions of delete and update
> > > and archive db with 40 millions of archived stuff...
> > >
> > > we have 10 databases for our clients and a centralized database for the
> > > general stuff.
> > >
> > > database specs:
> > >
> > > double XEON 2.4 on DELL PowerEdge2650
> > > 2 gigs of RAM
> > > 5 SCSI Drive RAID 5 15rpm
> > >
> > > tasks:
> > >
> > > 4 millions of transactions by day
> > > 160 open connection 24 hours by day 7 days by week
> > > pg_autovacuum running 24/7
> > > reindex on midnight
> >
> >
> >
> > ---------------------------(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
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
>
>

Regarding Raid5 at all, you might want to look at http://www.baarf.com

""Stephane Tessier"" <stephane.tessier@abovesecurity.com> wrote in message
news:001f01c4763c$fcef40f0$4e00020a@develavoie...
> I think with your help guys I'll do it!
>
> I'm working on it!
>
> I'll work on theses issues:
>
> we have space for more ram(we use 2 gigs on possibility of 3 gigs)
> iowait is very high 98% --> look like postgresql wait for io access
> raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each
....