Re: my boss want to migrate to ORACLE - Mailing list pgsql-performance

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