Re: my boss want to migrate to ORACLE

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