X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0D11CD1B174 for ; Wed, 28 Jul 2004 19:20:15 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 92007-06 for ; Wed, 28 Jul 2004 22:20:06 +0000 (GMT) Received: from ganymede.hub.org (u46n208.hfx.eastlink.ca [24.222.46.208]) by svr1.postgresql.org (Postfix) with ESMTP id B29A8D1B170 for ; Wed, 28 Jul 2004 19:20:00 -0300 (ADT) Received: by ganymede.hub.org (Postfix, from userid 1000) id 68A8D33DCE; Wed, 28 Jul 2004 19:20:02 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 5FCBB33CA0 for ; Wed, 28 Jul 2004 19:20:02 -0300 (ADT) X-Return-Path: X-Received: from localhost ([unix socket]) by ganymede.hub.org (Cyrus v2.2.6) with LMTPA; Wed, 28 Jul 2004 17:28:05 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 5878E36D57 for ; Wed, 28 Jul 2004 17:25:34 -0300 (ADT) X-Received: from mail.postgresql.org [200.46.204.71] by localhost with IMAP (fetchmail-6.2.5) for scrappy@localhost (single-drop); Wed, 28 Jul 2004 17:25:34 -0300 (ADT) X-Received: from svr1.postgresql.org ([unix socket]) by svr1.postgresql.org (Cyrus v2.2.6) with LMTPA; Wed, 28 Jul 2004 17:21:39 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 63FA0D1B76B for ; Wed, 28 Jul 2004 17:21:39 -0300 (ADT) X-Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 39213-09 for ; Wed, 28 Jul 2004 20:21:40 +0000 (GMT) X-Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) by svr1.postgresql.org (Postfix) with ESMTP id ECB78D1B735 for ; Wed, 28 Jul 2004 17:19:49 -0300 (ADT) X-Original-To: pgsql-performance-postgresql.org-owner@localhost.postgresql.org X-Delivered-To: pgsql-performance-postgresql.org-owner@localhost.postgresql.org X-Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E1105D1B743 for ; Wed, 28 Jul 2004 17:19:46 -0300 (ADT) X-Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 39213-06 for ; Wed, 28 Jul 2004 20:19:26 +0000 (GMT) X-Received: from www.postgresql.com (www.postgresql.com [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id DB2F1D1B745 for ; Wed, 28 Jul 2004 17:17:36 -0300 (ADT) X-Received: from asdc003.abovesecurite.lan (unknown [206.162.148.235]) by www.postgresql.com (Postfix) with ESMTP id B0404CF5765 for ; Wed, 28 Jul 2004 14:08:26 -0300 (ADT) X-Received: from develavoie ([206.162.148.230]) by asdc003.abovesecurite.lan over TLS secured channel with Microsoft SMTPSVC(6.0.3790.0); Wed, 28 Jul 2004 13:13:08 -0400 From: "Stephane Tessier" To: Subject: my boss want to migrate to ORACLE Date: Wed, 28 Jul 2004 13:08:06 -0400 Message-ID: <000001c474c5$73e2cfc0$4e00020a@develavoie> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01C474A3.ECD3A0C0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook CWS, Build 9.0.6604 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 Importance: Normal X-OriginalArrivalTime: 28 Jul 2004 17:13:08.0781 (UTC) FILETIME=[277915D0:01C474C6] X-Virus-Scanned: by amavisd-new at hub.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-DCC: : X-Spam-Pyzor: ReSent-Date: Wed, 28 Jul 2004 19:19:56 -0300 (ADT) Resent-From: "Marc G. Fournier" Resent-To: pgsql-performance@postgresql.org ReSent-Subject: my boss want to migrate to ORACLE ReSent-Message-ID: <20040728191956.Q792@ganymede.hub.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.8 tagged_above=0.0 required=5.0 tests=HTML_30_40, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200407/222 X-Sequence-Number: 7613 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C474A3.ECD3A0C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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 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 # # 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 # #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 ------=_NextPart_000_0001_01C474A3.ECD3A0C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi=20 everyone,
 
somebody = can help=20 me??????? my boss want to migrate to ORACLE................
 
we have a= BIG=20 problem of performance,it's slow....
we use po= stgres 7.3=20 for php security application with approximately 4 millions of insertio= n by=20 day and 4 millions of delete and update
and archi= ve db with=20 40 millions of archived stuff...
 
we have 1= 0 databases=20 for our clients and a centralized database for the general=20 stuff.
 
database= =20 specs:
 
double XE= ON 2.4 on=20 DELL PowerEdge2650
2 gigs of= =20 RAM
5 SCSI Dr= ive RAID 5=20 15rpm
 
tasks:
 
4 million= s of=20 transactions by day
160 open = connection=20 24 hours by day 7 days by week
pg_autova= cuum=20 running 24/7
reindex o= n=20 midnight
 
postgresql.conf:
 
tcpip_soc= ket =3D=20 true
#ssl =3D false
 
max_conne= ctions =3D=20 256
#superuser_reserved_connections =3D 2
 
#port =3D= 5432=20
#hostname_lookup =3D false
#show_source_port =3D false
=
 
#unix_socket_directory =3D ''
#unix_socket_gr= oup =3D=20 ''
#unix_socket_permissions =3D 0777 # octal
 
#virtual_= host =3D=20 ''
 
#krb_serv= er_keyfile=20 =3D ''
 

#
#       Shared Memory=20 Size
#
#shared_buffers =3D=20 256           # min=20 max_connections*2 or 16, 8KB each
#shared_buffers =3D=20 196000           &nb= sp;   =20 # min max_connections*2 or 16, 8KB each
shared_buffers =3D=20 128000         # min max_connection= s*2=20 or 16, 8KB each
#max_fsm_relations =3D 1000     = ; =20 # min 10, fsm is free space map, ~40 bytes
max_fsm_pages =3D=20 1000000         # min 1000, fsm is = free=20 space map, ~6 bytes
#max_locks_per_transaction =3D 64 # min 10
#wal_b= uffers=20 =3D=20 8            &n= bsp;  =20 # min 4, typically 8KB each
 
#
#       Non-shared Memory=20 Sizes
#
#sort_mem =3D 32168       # min= 64,=20 size in KB
#vacuum_mem =3D=20 8192            = ; =20 # min 1024, size in KB
vacuum_mem =3D=20 65536           &nbs= p; =20 # min 1024, size in KB
 
#
#       Write-ahead log=20 (WAL)
#
#checkpoint_segments =3D 3      = ; =20 # in logfile segments, min 1, 16MB each
#checkpoint_timeout =3D=20 300       # range 30-3600, in=20 seconds
#
#commit_delay =3D=20 0            &n= bsp; =20 # range 0-100000, in microseconds
#commit_siblings =3D=20 5            # range= =20 1-1000
#
#fsync =3D true
#wal_sync_method =3D=20 fsync        # the default varies across= =20 platforms:
#          =             &nb= sp;       =20 # fsync, fdatasync, open_sync, or open_datasync
#wal_debug =3D=20 0            &n= bsp;    =20 # range 0-16
 

#
#       Optimizer=20 Parameters
#
#enable_seqscan =3D true
#enable_indexscan =3D=20 true
#enable_tidscan =3D true
#enable_sort =3D true
#enable_nestlo= op =3D=20 true
#enable_mergejoin =3D true
#enable_hashjoin =3D true
 
#effective_cache_size =3D 1000    # typically 8KB=20 each
effective_cache_size =3D 196608   # typically 8KB=20 each
#random_page_cost =3D=20 4           # units are o= ne=20 sequential page fetch cost
#cpu_tuple_cost =3D=20 0.01          #=20 (same)
#cpu_index_tuple_cost =3D 0.001   #=20 (same)
#cpu_operator_cost =3D 0.0025     # (same)
 
#default_statistics_target =3D 10 # range 1-1000
 
#
#       GEQO Optimizer=20 Parameters
#
#geqo =3D true
#geqo_selection_bias =3D=20 2.0      # range 1.5-2.0
#geqo_threshold =3D=20 11
#geqo_pool_size =3D=20 0             #= =20 default based on tables in statement,=20
            = ;            &n= bsp;      =20 # range 128-1024
#geqo_effort =3D 1
#geqo_generations =3D=20 0
#geqo_random_seed =3D=20 -1          # auto-compute=20 seed
 

#
#       Message=20 display
#
server_min_messages =3Dnotice     # Val= ues, in=20 order of decreasing=20 detail:
          &nbs= p;            &= nbsp;       =20 #   debug5, debug4, debug3, debug2,=20 debug1,
          &nbs= p;            &= nbsp;       =20 #   info, notice, warning, error, log,=20 fatal,
           = ;            &n= bsp;       =20 #   panic
client_min_messages =3Dnotice    = ; #=20 Values, in order of decreasing=20 detail:
          &nbs= p;            &= nbsp;       =20 #   debug5, debug4, debug3, debug2,=20 debug1,
          &nbs= p;            &= nbsp;       =20 #   log, info, notice, warning, error
#silent_mode =3D false
 
#log_connections =3Dtrue
#log_pid =3Dtrue
#log_statement=20 =3Dtrue
#log_duration =3Dtrue
#log_timestamp =3Dtrue
 
log_min_error_statement =3Derror
# Values in order of increasing=20 severity:
          &n= bsp;            = ;         =20 #   debug5, debug4, debug3, debug2,=20 debug1,
          &nbs= p;            &= nbsp;        =20 #   info, notice, warning, error, panic(off)
 
#debug_print_parse =3D false
#debug_print_rewritten =3D=20 false
#debug_print_plan =3D false
#debug_pretty_print =3D false
 
#explain_pretty_print =3D true
 
# requires USE_ASSERT_CHECKING
#debug_assertions =3D true
 

#
#       Syslog
#
syslog = =3D=20 0            &n= bsp;        =20 # range 0-2
syslog_facility =3D 'LOCAL0'
syslog_ident =3D 'postgres'<= /DIV>
 

#
#      =20 Statistics
#
show_parser_stats =3D false
show_planner_stats=20 =3Dfalse
show_executor_stats =3D false
show_statement_stats =3Dfalse =
 
# requires BTREE_BUILD_STATS
#show_btree_build_stats =3D false
 

#
#       Access statistics=20 collection
#
stats_start_collector =3D true
stats_reset_on_server_= start =3D=20 false
stats_command_string =3D true
stats_row_level =3D true=20
stats_block_level =3D true
 

#
#       Lock=20 Tracing
#
#trace_notify =3D false
 
# requires LOCK_DEBUG
#trace_locks =3D false
#trace_userlocks = =3D=20 false
#trace_lwlocks =3D false
#debug_deadlocks =3D=20 false
#trace_lock_oidmin =3D 16384
#trace_lock_table =3D 0
 

#
#       Misc
#
#autocommi= t =3D=20 true
#dynamic_library_path =3D '$libdir'
#search_path =3D=20 '$user,public'
#datestyle =3D 'iso, us'
#timezone =3D=20 unknown           &n= bsp;=20 # actually, defaults to TZ environment setting
#australian_timezones =3D= =20 false
#client_encoding =3D sql_ascii    # actually, defau= lts to=20 database encoding
#authentication_timeout =3D 60    # 1-6= 00, in=20 seconds
#deadlock_timeout =3D 1000      &n= bsp; #=20 in milliseconds
#default_transaction_isolation =3D 'read=20 committed'
#max_expr_depth =3D=20 10000         # min=20 10
#max_files_per_process =3D 1000   # min 25
#password_enc= ryption=20 =3D true
#sql_inheritance =3D true
#transform_null_equals =3D=20 false
#statement_timeout =3D=20 0          # 0 is disabled, in= =20 milliseconds
#db_user_namespace =3D false
************************************************= ***************************************************************************= ****************************
 

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

 
------=_NextPart_000_0001_01C474A3.ECD3A0C0--