pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedi need help
3+ messages / 2 participants
[nested] [flat]
* i need help
@ 2004-12-12 05:33 Philip Michael D Vargas <pmdv@comclark.com>
2004-12-14 23:24 ` Re: i need help Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 3+ messages in thread
From: Philip Michael D Vargas @ 2004-12-12 05:33 UTC (permalink / raw)
To: pgsql-admin
Good day to all ...
I hope any one can give me an advice ... to optimize my database...
im having a problem when i'm backing up my DB and also using vacuum for my
DB...
my CPU load goes up.... and no one can use my DB... most of the transaction
comes from web server..
I just need ur good advice ...
thank you
please check my postgresql.conf
--
#
#
# Connection Parameters
#
tcpip_socket = true
#ssl = false
max_connections = 300
superuser_reserved_connections = 100
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 = 600 # min max_connections*2 or 16, 8KB each
#max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
#max_fsm_pages = 10000 # 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 = 1024 # min 64, size in KB
#vacuum_mem = 8192 # 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
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 = 1024 # 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 = false
log_statement = true
log_duration = true
log_timestamp = true
#log_min_error_statement = panic # 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 = 2 # 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 = false
stats_reset_on_server_start = false
stats_command_string = false
stats_row_level = false
stats_block_level = false
#
# 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
#
# Locale settings
#
# (initialized by initdb -- may be changed)
LC_MESSAGES = 'en_US.UTF-8'
LC_MONETARY = 'en_US.UTF-8'
LC_NUMERIC = 'en_US.UTF-8'
LC_TIME = 'en_US.UTF-8'
-----------------
here is my diskspace..
/dev/sdb1 3526172 1132784 2214268 34% /
/dev/sda1 248895 8796 227249 4% /boot
none 2005700 0 2005700 0% /dev/shm
/dev/md0 65757260 50992580 11424376 82% /var
/dev/sdc1 17409840 13521548 3003916 82% /backup
----------------
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: i need help
2004-12-12 05:33 i need help Philip Michael D Vargas <pmdv@comclark.com>
@ 2004-12-14 23:24 ` Simon Riggs <simon@2ndquadrant.com>
2004-12-15 00:45 ` Re: i need help Philip Michael D Vargas <pmdv@comclark.com>
0 siblings, 1 reply; 3+ messages in thread
From: Simon Riggs @ 2004-12-14 23:24 UTC (permalink / raw)
To: Philip Michael D Vargas <pmdv@comclark.com>; +Cc: pgsql-admin
On Sun, 2004-12-12 at 05:33, Philip Michael D Vargas wrote:
> Good day to all ...
>
> I hope any one can give me an advice ... to optimize my database...
>
> im having a problem when i'm backing up my DB and also using vacuum for my
> DB...
> my CPU load goes up.... and no one can use my DB... most of the transaction
> comes from web server..
>
> I just need ur good advice ...
Consider increasing shared_buffers, but consider what your RAM is before
you do that.
You'll need to give reasonable details if you want good help. The
specific details are important in knowing what might be causing your
problem.
There is much good advice available already and the manuals are good
too...
--
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: i need help
2004-12-12 05:33 i need help Philip Michael D Vargas <pmdv@comclark.com>
2004-12-14 23:24 ` Re: i need help Simon Riggs <simon@2ndquadrant.com>
@ 2004-12-15 00:45 ` Philip Michael D Vargas <pmdv@comclark.com>
0 siblings, 0 replies; 3+ messages in thread
From: Philip Michael D Vargas @ 2004-12-15 00:45 UTC (permalink / raw)
To: pgsql-admin
Oh..
Sorry about the details
I'm using a ASUS machine with dual processor... 4gb memory...
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sdb1 3526172 1132784 2214268 34% /
/dev/sda1 248895 8796 227249 4% /boot
none 2005700 0 2005700 0% /dev/shm
/dev/md0 65757260 52334548 10082408 84% /var
/dev/sdc1 17409840 12740248 3785216 78% /backup
thank you for your reply...
----- Original Message -----
From: "Simon Riggs" <simon@2ndquadrant.com>
To: "Philip Michael D Vargas" <pmdv@comclark.com>
Cc: <pgsql-admin@postgresql.org>
Sent: Wednesday, December 15, 2004 7:24 AM
Subject: Re: [ADMIN] i need help
> On Sun, 2004-12-12 at 05:33, Philip Michael D Vargas wrote:
> > Good day to all ...
> >
> > I hope any one can give me an advice ... to optimize my database...
> >
> > im having a problem when i'm backing up my DB and also using vacuum for
my
> > DB...
> > my CPU load goes up.... and no one can use my DB... most of the
transaction
> > comes from web server..
> >
> > I just need ur good advice ...
>
> Consider increasing shared_buffers, but consider what your RAM is before
> you do that.
>
> You'll need to give reasonable details if you want good help. The
> specific details are important in knowing what might be causing your
> problem.
>
> There is much good advice available already and the manuals are good
> too...
>
> --
> Best Regards, Simon Riggs
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-12-15 00:45 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-12 05:33 i need help Philip Michael D Vargas <pmdv@comclark.com>
2004-12-14 23:24 ` Simon Riggs <simon@2ndquadrant.com>
2004-12-15 00:45 ` Philip Michael D Vargas <pmdv@comclark.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox