pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPlz Heeeelp! performance settings
10+ messages / 4 participants
[nested] [flat]
* Plz Heeeelp! performance settings
@ 2008-08-06 22:12 dforum <dforums@vieonet.com>
0 siblings, 1 reply; 10+ messages in thread
From: dforum @ 2008-08-06 22:12 UTC (permalink / raw)
To: pgsql-performance
Hello to all,
I have a Linux 2.6.24.2-xxxx-std-ipv4-64 #3 SMP Tue Feb 12 12:27:47 CET
2008 x86_64 Intel(R) Xeon(R) CPU X5355 @ 2.66GHz GenuineIntel GNU/Linux
with 8Gb of memory. Using sata II disk in RAID 1 (I known that is bad,
but it would change has quickly I can).
I have a database of 38Go and take 6Go per week.
I have a lot of update and insert, especially in 8 tables. 2 tables are
using for temporary storage, so I right something like 15000 request per
2 minutes and empty it into 10 min.
I'm making some update or select on tables including more than 20
millions of entrance.
I have the following postgresql.conf settings :
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Please note that re-commenting a setting is NOT sufficient to revert it
# to the default value, unless you restart the postmaster.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload". Some settings, such as listen_addresses, require
# a postmaster shutdown and restart to take effect.
#---------------------------------------------------------------------------
# FILE LOCATIONS
#---------------------------------------------------------------------------
# The default values of these variables are driven from the -D command line
# switch or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
#ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file
# If external_pid_file is not explicitly set, no extra pid file is written.
#external_pid_file = '(none)' # write an extra pid file
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = 'xxx.xxx.xxx.xxx' # what IP address(es)
to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
port = 5432
max_connections = 624
# note: increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction). You
# might also need to raise shared_buffers to support more connections.
#superuser_reserved_connections = 2
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#bonjour_name = '' # defaults to the computer name
# - Security & Authentication -
#authentication_timeout = 60 # 1-600, in seconds
#ssl = off
#password_encryption = on
#db_user_namespace = off
# Kerberos
#krb_server_keyfile = ''
#krb_srvname = 'postgres'
#krb_server_hostname = '' # empty string matches any
keytab entry
#krb_caseins_users = off
# - TCP Keepalives -
# see 'man 7 tcp' for details
tcp_keepalives_idle = 300 # TCP_KEEPIDLE, in seconds;
# 0 selects the system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
# 0 selects the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;
# 0 selects the system default
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 250000 # min 16 or max_connections*2,
8KB each
temp_buffers = 500 # min 100, 8KB each
max_prepared_transactions = 200 # can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes of shared
memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 9000 # min 64, size in KB
maintenance_work_mem = 5000 # min 1024, size in KB
max_stack_depth = 8192 # min 100, size in KB
# - Free Space Map -
max_fsm_pages = 100000 # min max_fsm_relations*16, 6
bytes each
max_fsm_relations = 5000 # min 100, ~70 bytes each
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
#preload_libraries = ''
# - Cost-Based Vacuum Delay -
vacuum_cost_delay = 5 # 0-1000 milliseconds
vacuum_cost_page_hit = 10 # 0-10000 credits
vacuum_cost_page_miss = 100 # 0-10000 credits
vacuum_cost_page_dirty = 20 # 0-10000 credits
vacuum_cost_limit = 500 # 0-10000 credits
# - Background writer -
bgwriter_delay = 50 # 10-10000 milliseconds between
rounds
bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers
scanned/round
bgwriter_lru_maxpages = 25 # 0-1000 buffers max written/round
bgwriter_all_percent = 0.333 # 0-100% of all buffers
scanned/round
bgwriter_all_maxpages = 50 # 0-1000 buffers max written/round
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
#fsync = on # turns forced synchronization
on or off
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
wal_buffers = 16 # min 4, 8KB each
commit_delay = 500 # range 0-100000, in microseconds
commit_siblings = 50 # range 1-1000
# - Checkpoints -
checkpoint_segments = 50 # in logfile segments, min 1,
16MB each
checkpoint_timeout = 1800 # range 30-3600, in seconds
checkpoint_warning = 180 # in seconds, 0 is off
# - Archiving -
#archive_command = '' # command to use to archive a
logfile
# segment
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
effective_cache_size = 625000 # typically 8KB each
random_page_cost = 3 # 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)
# - Genetic Query Optimizer -
#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
#default_statistics_target = 10 # range 1-1000
#constraint_exclusion = off
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOINs
#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------
# - Where to Log -
#log_destination = 'stderr' # Valid values are combinations of
# stderr, syslog and eventlog,
# depending on platform.
# This is used when logging to stderr:
#redirect_stderr = off # Enable capturing of stderr
into log
# files
# These are only used if redirect_stderr is on:
#log_directory = 'pg_log' # Directory where log files are
written
# Can be absolute or relative to
PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name pattern.
# Can include strftime() escapes
#log_truncate_on_rotation = off # If on, any existing log file of the same
# name as the new log file will be
# truncated rather than appended
to. But
# such truncation only occurs on
# time-driven rotation, not on
restarts
# or size-driven rotation.
Default is
# off, meaning append to
existing files
# in all cases.
#log_rotation_age = 1440 # Automatic rotation of logfiles
will
# happen after so many minutes.
0 to
# disable.
#log_rotation_size = 10240 # Automatic rotation of logfiles
will
# happen after so many kilobytes
of log
# output. 0 to disable.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# - When to Log -
#client_min_messages = notice # Values, in order of decreasing
detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
#log_min_messages = notice # Values, in order of decreasing
detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
#log_error_verbosity = default # terse, default, or verbose
messages
#log_min_error_statement = panic # Values in order of increasing
severity:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# panic(off)
#log_min_duration_statement = -1 # -1 is disabled, 0 logs all
statements
# and their durations, in
milliseconds.
#silent_mode = off # DO NOT USE without syslog or
# redirect_stderr
# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_line_prefix = '' # Special values:
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = PID
# %t = timestamp (no milliseconds)
# %m = timestamp with milliseconds
# %i = command tag
# %c = session id
# %l = session line number
# %s = session start timestamp
# %x = transaction id
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
#log_statement = 'none' # none, mod, ddl, all
#log_hostname = off
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------
# - Statistics Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
# - Query/Index Statistics Collector -
stats_start_collector = on
#stats_command_string = off
#stats_block_level = off
stats_row_level = on
#stats_reset_on_server_start = off
#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------
autovacuum = on # enable autovacuum subprocess?
autovacuum_naptime = 180 # time between autovacuum runs,
in secs
autovacuum_vacuum_threshold = 100000 # min # of tuple updates before
# vacuum
autovacuum_analyze_threshold = 9000 # min # of tuple updates before
# analyze
autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
# vacuum
autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
# analyze
autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
# autovac, -1 means use
# vacuum_cost_delay
autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovac, -1 means use
# vacuum_cost_limit
#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '$user,public' # schema names
#default_tablespace = '' # a tablespace name, '' uses
# the default
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#statement_timeout = 0 # 0 is disabled, in milliseconds
# - Locale and Formatting -
#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ
# environment setting
#australian_timezones = off
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database
# encoding
# These settings are initialized by initdb -- they might be changed
#lc_messages = 'fr_FR@euro' # locale for system
error message
# strings
#lc_monetary = 'fr_FR@euro' # locale for monetary
formatting
#lc_numeric = 'fr_FR@euro' # locale for number
formatting
#lc_time = 'fr_FR@euro' # locale for time formatting
lc_messages = 'fr_FR.UTF-8' # locale for system
error message
# strings
lc_monetary = 'fr_FR.UTF-8' # locale for monetary
formatting
lc_numeric = 'fr_FR.UTF-8' # locale for number
formatting
lc_time = 'fr_FR.UTF-8' # locale for time formatting
# - Other Defaults -
#explain_pretty_print = on
#dynamic_library_path = '$libdir'
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10
# note: each lock table slot uses ~220 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.
#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------
# - Previous Postgres Versions -
#add_missing_from = off
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = off
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = on
# - Other Platforms & Clients -
#transform_null_equals = off
#---------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#---------------------------------------------------------------------------
#custom_variable_classes = '' # list of custom variable class
names
I'm sure that it could be more optimised. I don't know any thing on WAL,
autovacuum, fsm, bgwriter, kernel process, geqo or planner cost settings.
I'll thanks you all in advance for your precious help
Regards
David
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plz Heeeelp! performance settings
@ 2008-08-07 02:16 Merlin Moncure <mmoncure@gmail.com>
parent: dforum <dforums@vieonet.com>
0 siblings, 1 reply; 10+ messages in thread
From: Merlin Moncure @ 2008-08-07 02:16 UTC (permalink / raw)
To: dforum <dforums@vieonet.com>; +Cc: pgsql-performance
On Wed, Aug 6, 2008 at 6:12 PM, dforum <dforums@vieonet.com> wrote:
> Hello to all,
>
>
> I have a Linux 2.6.24.2-xxxx-std-ipv4-64 #3 SMP Tue Feb 12 12:27:47 CET 2008
> x86_64 Intel(R) Xeon(R) CPU X5355 @ 2.66GHz GenuineIntel GNU/Linux
> with 8Gb of memory. Using sata II disk in RAID 1 (I known that is bad, but
<snip>
this is likely your problem...with fsync on (as you have it), you will
be lucky to get a couple of hundred transactions/sec out of the
database. you are probably just exceeding your operational
capabilities of the hardware so you probably need to upgrade or turn
off fsync (which means data corruption in event of hard crash).
merlin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plz Heeeelp! performance settings
@ 2008-08-07 07:35 dforum <dforums@vieonet.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: dforum @ 2008-08-07 07:35 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
Tx for your reply.
You mean that RAID use fsync method for keeping data's copy.
So you invite me to desactivate fsync to increase the performance ?
Desactivating fsync. my second disk will not be uptodate, so if the
machine crash, I wont be able to get the server working quickly??? But
if I use a second machine to replicate the database, I escape this
problem isn't it ?
If I understand right, could you tell me how to do desactivate fsync
please ?
Best regards
David
Merlin Moncure a écrit :
> On Wed, Aug 6, 2008 at 6:12 PM, dforum <dforums@vieonet.com> wrote:
>
>> Hello to all,
>>
>>
>> I have a Linux 2.6.24.2-xxxx-std-ipv4-64 #3 SMP Tue Feb 12 12:27:47 CET 2008
>> x86_64 Intel(R) Xeon(R) CPU X5355 @ 2.66GHz GenuineIntel GNU/Linux
>> with 8Gb of memory. Using sata II disk in RAID 1 (I known that is bad, but
>>
> <snip>
>
> this is likely your problem...with fsync on (as you have it), you will
> be lucky to get a couple of hundred transactions/sec out of the
> database. you are probably just exceeding your operational
> capabilities of the hardware so you probably need to upgrade or turn
> off fsync (which means data corruption in event of hard crash).
>
> merlin
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plz Heeeelp! performance settings
@ 2008-08-07 08:33 Richard Huxton <dev@archonet.com>
parent: dforum <dforums@vieonet.com>
0 siblings, 1 reply; 10+ messages in thread
From: Richard Huxton @ 2008-08-07 08:33 UTC (permalink / raw)
To: dforum <dforums@vieonet.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
dforum wrote:
> Tx for your reply.
>
> You mean that RAID use fsync method for keeping data's copy.
No, Merlin means PostgreSQL will issue a sync to force WAL to actual disk.
> So you invite me to desactivate fsync to increase the performance ?
He means you might have to if you can't afford new hardware. Is disk
activity the problem? Have you looked at the output of "vmstat" to check?
> Desactivating fsync. my second disk will not be uptodate,
No - the RAID stuff is happening in the operating-system.
> so if the
> machine crash, I wont be able to get the server working quickly???
Not "quickly", perhaps not "at all".
> But
> if I use a second machine to replicate the database, I escape this
> problem isn't it ?
You reduce the chance of a single failure causing disaster.
> If I understand right, could you tell me how to do desactivate fsync
> please ?
There's an "fsync = on" setting in your postgresql.conf, but don't
change it yet.
> I have a database of 38Go and take 6Go per week.
What do you mean by "take 6Go per week"? You update/delete that much
data? It's growing by that amount each week?
> I have a lot of update and insert, especially in 8 tables. 2 tables are
> using for temporary storage, so I right something like 15000 request per
> 2 minutes and empty it into 10 min.
I'm not sure what "15000 request per 2 minutes and empty it into 10 min"
means.
Do you have 7500 requests per minute?
Are these updates?
To the "temporary storage"?
What is this "temporary storage" - an ordinary table?
> I'm making some update or select on tables including more than 20
> millions of entrance.
Again, I'm not sure what this means.
Oh - *important* - which version of PostgreSQL are you running?
Is an upgrade practical?
Looking at your postgresql.conf settings:
max_connections = 624
That's an odd number.
Do you usually have that many connections?
What are they doing? They can't all be active, the machine you've got
wouldn't cope.
shared_buffers = 250000
work_mem = 9000
temp_buffers = 500
These three are important. The shared_buffers are workspace shared
between all backends, and you've allocated about 2GB. You've also set
work_mem=9MB, which is how much each backend can use for a single sort.
That means it can use double or triple that in a complex query. If
you're using temporary tables, then you'll want to make sure the
temp_buffers setting is correct.
I can't say whether these figures are good or bad without knowing how
the database is being used.
effective_cache_size = 625000
That's around 5GB - is that roughly the amount of memory used for
caching (what does free -m say for buffers/cache)?
max_prepared_transactions = 200
Do you use a lot of prepared transactions in two-phase commit?
I'm guessing that you don't.
> I'm sure that it could be more optimised. I don't know any thing on
> WAL,
> autovacuum, fsm, bgwriter, kernel process, geqo or planner cost
> settings.
If you run a "vacuum verbose" it will recommend fsm settings at the end
of its output. I think you probably need to make your autovacuum more
aggressive, but that's something you'll be able to tell by monitoring
your database.
It's quite likely that Merlin's right, and you need better hardware to
cope with the number of updates you're making - that's something where
you need fast disks. However, he's just guessing because you've not told
us enough to tell where the problem really lies.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plz Heeeelp! performance settings
@ 2008-08-07 09:12 dforums <dforums@vieonet.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 10+ messages in thread
From: dforums @ 2008-08-07 09:12 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Richard Huxton a écrit :
> dforum wrote:
>> Tx for your reply.
>>
>> You mean that RAID use fsync method for keeping data's copy.
>
> No, Merlin means PostgreSQL will issue a sync to force WAL to actual
disk.
>
>> So you invite me to desactivate fsync to increase the performance ?
>
> He means you might have to if you can't afford new hardware. Is disk
> activity the problem? Have you looked at the output of "vmstat" to check?
vmstat is giving :
procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free buff cache si so bi bo in cs us
sy id wa
0 2 1540 47388 41684 7578976 0 0 131 259 0 1 9
3 82 7
>
>> Desactivating fsync. my second disk will not be uptodate,
>
> No - the RAID stuff is happening in the operating-system.
>
> > so if the
>> machine crash, I wont be able to get the server working quickly???
>
> Not "quickly", perhaps not "at all".
Oups
>
> > But
>> if I use a second machine to replicate the database, I escape this
>> problem isn't it ?
> You reduce the chance of a single failure causing disaster.
Not clear this reply. It's scare me ....
>
>> If I understand right, could you tell me how to do desactivate fsync
>> please ?
>
> There's an "fsync = on" setting in your postgresql.conf, but don't
> change it yet.
OK
>
> > I have a database of 38Go and take 6Go per week.
>
> What do you mean by "take 6Go per week"? You update/delete that much
> data? It's growing by that amount each week?
YES
>
> > I have a lot of update and insert, especially in 8 tables. 2
tables are
> > using for temporary storage, so I right something like 15000
request per
> > 2 minutes and empty it into 10 min.
>
> I'm not sure what "15000 request per 2 minutes and empty it into 10 min"
> means.
I insert 15000 datas every 2 min and delete 15000 every 10 min in those
tables
>
> Do you have 7500 requests per minute?
should be that, But in fact I'm not treating the datas in real time, and
I buffer the datas and push the data into the database every 2 min
> Are these updates?
during the delete the data are aggregated in other tables which make updates
> To the "temporary storage"?
> What is this "temporary storage" - an ordinary table?
Yes, I thied to use temporary tables but I never been able to connect
this tables over 2 different session/connection, seems that is a
functionnality of postgresql, or a misunderstanding from me.
>
> > I'm making some update or select on tables including more than 20
> > millions of entrance.
>
> Again, I'm not sure what this means.
To aggregate the data, I have to check the presence of others
information that are stores in 2 tables which includes 24 millions of
entrance.
>
>
> Oh - *important* - which version of PostgreSQL are you running?
8.1.11
> Is an upgrade practical?
We are working of trying to upgrade to 8.3.3, but we are not yet ready
for such migration
>
>
> Looking at your postgresql.conf settings:
>
> max_connections = 624
>
> That's an odd number.
Now we could decrease this number, it's not so much usefull for now. we
could decrease is to 350.
> Do you usually have that many connections?
> What are they doing? They can't all be active, the machine you've got
> wouldn't cope.
>
> shared_buffers = 250000
> work_mem = 9000
> temp_buffers = 500
>
> These three are important. The shared_buffers are workspace shared
> between all backends, and you've allocated about 2GB. You've also set
> work_mem=9MB, which is how much each backend can use for a single sort.
> That means it can use double or triple that in a complex query
(i now about it).
If
> you're using temporary tables, then you'll want to make sure the
> temp_buffers setting is correct.
I need help for that, I don't know
>
> I can't say whether these figures are good or bad without knowing how
> the database is being used.
>
> effective_cache_size = 625000
>
> That's around 5GB - is that roughly the amount of memory used for
> caching (what does free -m say for buffers/cache)?
total used free shared buffers cached
Mem: 7984 7828 156 0 38 7349
-/+ buffers/cache: 440 7544
Swap: 509 1 508
>
> max_prepared_transactions = 200
>
> Do you use a lot of prepared transactions in two-phase commit?
> I'm guessing that you don't.
I don't
>
> > I'm sure that it could be more optimised. I don't know any thing on
> > WAL,
> > autovacuum, fsm, bgwriter, kernel process, geqo or planner cost
> > settings.
>
> If you run a "vacuum verbose" it will recommend fsm settings at the end
> of its output. I think you probably need to make your autovacuum more
> aggressive, but that's something you'll be able to tell by monitoring
> your database.
>
> It's quite likely that Merlin's right, and you need better hardware to
> cope with the number of updates you're making - that's something where
> you need fast disks. However, he's just guessing because you've not told
> us enough to tell where the problem really lies.
>
Hope that new information will give you more information to help me.
Regards
david
--
<http://www.1st-affiliation.fr;
*David Bigand
*Président Directeur Générale*
*51 chemin des moulins
73000 CHAMBERY - FRANCE
Web : htttp://www.1st-affiliation.fr
Email : david@1st-affiliation.com
Tel. : +33 479 696 685
Mob. : +33 666 583 836
Skype : firstaffiliation_support
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plz Heeeelp! performance settings
@ 2008-08-07 10:40 Richard Huxton <dev@archonet.com>
parent: dforums <dforums@vieonet.com>
0 siblings, 1 reply; 10+ messages in thread
From: Richard Huxton @ 2008-08-07 10:40 UTC (permalink / raw)
To: dforums <dforums@vieonet.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
dforums wrote:
> vmstat is giving :
> procs -----------memory---------- ---swap-- -----io---- --system--
> ----cpu----
> r b swpd free buff cache si so bi bo in cs us sy
> id wa
> 0 2 1540 47388 41684 7578976 0 0 131 259 0 1 9
> 3 82 7
This system is practically idle. Either you're not measuring it at a
useful time, or there isn't a performance problem.
> > > But
> >> if I use a second machine to replicate the database, I escape this
> >> problem isn't it ?
> > You reduce the chance of a single failure causing disaster.
> Not clear this reply. It's scare me ....
If server A fails, you still have server B. If server A fails so that
replication stops working and you don't notice, server B won't help any
more.
> > What do you mean by "take 6Go per week"? You update/delete that much
> > data? It's growing by that amount each week?
> YES
That wasn't a yes/no question. Please choose one of:
Are you updating 6Go per week?
Are you adding 6Go per week?
> > I'm not sure what "15000 request per 2 minutes and empty it into 10 min"
> > means.
> I insert 15000 datas every 2 min and delete 15000 every 10 min in those
> tables
> >
> > Do you have 7500 requests per minute?
> should be that, But in fact I'm not treating the datas in real time, and
> I buffer the datas and push the data into the database every 2 min
> > Are these updates?
> during the delete the data are aggregated in other tables which make
> updates
OK, so every 2 minutes you run one big query that adds 15000 rows.
Every 10 minutes you run one big query that deletes 15000 rows.
> > To the "temporary storage"?
>
> > What is this "temporary storage" - an ordinary table?
> Yes, I thied to use temporary tables but I never been able to connect
> this tables over 2 different session/connection, seems that is a
> functionnality of postgresql, or a misunderstanding from me.
That's correct - temporary tables are private to a backend (connection).
> > > I'm making some update or select on tables including more than 20
> > > millions of entrance.
> >
> > Again, I'm not sure what this means.
>
> To aggregate the data, I have to check the presence of others
> information that are stores in 2 tables which includes 24 millions of
> entrance.
OK. I assume you're happy with the plans you are getting on these
queries, since you've not provided any information about them.
> > Oh - *important* - which version of PostgreSQL are you running?
> 8.1.11
> > Is an upgrade practical?
> We are working of trying to upgrade to 8.3.3, but we are not yet ready
> for such migration
OK
> > Looking at your postgresql.conf settings:
> >
> > max_connections = 624
> >
> > That's an odd number.
> Now we could decrease this number, it's not so much usefull for now. we
> could decrease is to 350.
I don't believe you've got 350 active connections either. It will be
easier to help if you can provide some useful information.
> > effective_cache_size = 625000
> >
> > That's around 5GB - is that roughly the amount of memory used for
> > caching (what does free -m say for buffers/cache)?
> total used free shared buffers cached
> Mem: 7984 7828 156 0 38 7349
> -/+ buffers/cache: 440 7544
> Swap: 509 1 508
Not far off - free is showing 7349MB cached. You're not running 350
clients there though - you're only using 440MB of RAM.
I don't see anything to show a performance problem from these emails.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plz Heeeelp! performance settings
@ 2008-08-07 13:30 dforums <dforums@vieonet.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 10+ messages in thread
From: dforums @ 2008-08-07 13:30 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
The performance problem is really only on the insertion and even more on
the treatment for the aggregation.
To treat the 3000 entrances and to insert, or update the tables it needs
10 minutes.
As I told you I inject 14000 query every 2 minutes, and it needs 10
minutes to treat 3000 of those query.
As you can easly understand it's a big narrow section.
I'm not doing the treatment in ones, cause I can't, but all is managed
by procedure.
> That wasn't a yes/no question. Please choose one of:
> Are you updating 6Go per week? most of update
> Are you adding 6Go per week? less of injection,
This action depend if the data are already present in the database.
>
> OK. I assume you're happy with the plans you are getting on these
> queries, since you've not provided any information about them.
The plan seems ok as it use index as well.
here is the plan :
explain analyse SELECT "insertUpdateTracks"(137,2605, 852, ('2008-08-06
19:28:54'::text)::date,3,'dailydisplay',2,NULL);
INFO: method 1
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=1.151..1.151
rows=1 loops=1)
Total runtime: 1.160 ms
(2 lignes)
Has you can see the runtime processs for an update in this table.
multiplying this per 10000, it is too long.
regards
david
Richard Huxton a écrit :
> dforums wrote:
>> vmstat is giving :
>> procs -----------memory---------- ---swap-- -----io---- --system--
>> ----cpu----
>> r b swpd free buff cache si so bi bo in cs us
>> sy id wa
>> 0 2 1540 47388 41684 7578976 0 0 131 259 0 1 9
>> 3 82 7
>
> This system is practically idle. Either you're not measuring it at a
> useful time, or there isn't a performance problem.
>
>> > > But
>> >> if I use a second machine to replicate the database, I escape this
>> >> problem isn't it ?
>> > You reduce the chance of a single failure causing disaster.
>> Not clear this reply. It's scare me ....
>
> If server A fails, you still have server B. If server A fails so that
> replication stops working and you don't notice, server B won't help any
> more.
>
>> > What do you mean by "take 6Go per week"? You update/delete that much
>> > data? It's growing by that amount each week?
>> YES
>
> That wasn't a yes/no question. Please choose one of:
> Are you updating 6Go per week?
> Are you adding 6Go per week?
>
>> > I'm not sure what "15000 request per 2 minutes and empty it into 10
>> min"
>> > means.
>> I insert 15000 datas every 2 min and delete 15000 every 10 min in
>> those tables
>> >
>> > Do you have 7500 requests per minute?
>> should be that, But in fact I'm not treating the datas in real time,
>> and I buffer the datas and push the data into the database every 2 min
>> > Are these updates?
>> during the delete the data are aggregated in other tables which make
>> updates
>
> OK, so every 2 minutes you run one big query that adds 15000 rows.
> Every 10 minutes you run one big query that deletes 15000 rows.
>
>> > To the "temporary storage"?
>>
>> > What is this "temporary storage" - an ordinary table?
>> Yes, I thied to use temporary tables but I never been able to connect
>> this tables over 2 different session/connection, seems that is a
>> functionnality of postgresql, or a misunderstanding from me.
>
> That's correct - temporary tables are private to a backend (connection).
>
>> > > I'm making some update or select on tables including more than 20
>> > > millions of entrance.
>> >
>> > Again, I'm not sure what this means.
>>
>> To aggregate the data, I have to check the presence of others
>> information that are stores in 2 tables which includes 24 millions of
>> entrance.
>
> OK. I assume you're happy with the plans you are getting on these
> queries, since you've not provided any information about them.
>
>> > Oh - *important* - which version of PostgreSQL are you running?
>> 8.1.11
>> > Is an upgrade practical?
>> We are working of trying to upgrade to 8.3.3, but we are not yet ready
>> for such migration
>
> OK
>
>> > Looking at your postgresql.conf settings:
>> >
>> > max_connections = 624
>> >
>> > That's an odd number.
>> Now we could decrease this number, it's not so much usefull for now.
>> we could decrease is to 350.
>
> I don't believe you've got 350 active connections either. It will be
> easier to help if you can provide some useful information.
>
>> > effective_cache_size = 625000
>> >
>> > That's around 5GB - is that roughly the amount of memory used for
>> > caching (what does free -m say for buffers/cache)?
>> total used free shared buffers cached
>> Mem: 7984 7828 156 0 38 7349
>> -/+ buffers/cache: 440 7544
>> Swap: 509 1 508
>
> Not far off - free is showing 7349MB cached. You're not running 350
> clients there though - you're only using 440MB of RAM.
>
>
> I don't see anything to show a performance problem from these emails.
>
--
<http://www.1st-affiliation.fr;
*David Bigand
*Président Directeur Générale*
*51 chemin des moulins
73000 CHAMBERY - FRANCE
Web : htttp://www.1st-affiliation.fr
Email : david@1st-affiliation.com
Tel. : +33 479 696 685
Mob. : +33 666 583 836
Skype : firstaffiliation_support
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plz Heeeelp! performance settings
@ 2008-08-07 14:12 Richard Huxton <dev@archonet.com>
parent: dforums <dforums@vieonet.com>
1 sibling, 1 reply; 10+ messages in thread
From: Richard Huxton @ 2008-08-07 14:12 UTC (permalink / raw)
To: dforums <dforums@vieonet.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
dforums wrote:
> The performance problem is really only on the insertion and even more on
> the treatment for the aggregation.
>
> To treat the 3000 entrances and to insert, or update the tables it needs
> 10 minutes.
>
> As I told you I inject 14000 query every 2 minutes, and it needs 10
> minutes to treat 3000 of those query.
Sorry - I still don't understand. What is this "treatment" you are doing?
> >
> > OK. I assume you're happy with the plans you are getting on these
> > queries, since you've not provided any information about them.
>
> The plan seems ok as it use index as well.
> here is the plan :
>
> explain analyse SELECT "insertUpdateTracks"(137,2605, 852, ('2008-08-06
> 19:28:54'::text)::date,3,'dailydisplay',2,NULL);
> INFO: method 1
> QUERY PLAN
> ------------------------------------------------------------------------------------
>
> Result (cost=0.00..0.01 rows=1 width=0) (actual time=1.151..1.151
> rows=1 loops=1)
> Total runtime: 1.160 ms
There's nothing to do with an index here - this is a function call.
> Has you can see the runtime processs for an update in this table.
>
> multiplying this per 10000, it is too long.
So - are you calling this function 14000 times to inject your data?
You're doing this in one transaction, yes?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plz Heeeelp! performance settings
@ 2008-08-07 15:05 Merlin Moncure <mmoncure@gmail.com>
parent: dforums <dforums@vieonet.com>
1 sibling, 0 replies; 10+ messages in thread
From: Merlin Moncure @ 2008-08-07 15:05 UTC (permalink / raw)
To: dforums <dforums@vieonet.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
On Thu, Aug 7, 2008 at 9:30 AM, dforums <dforums@vieonet.com> wrote:
> The performance problem is really only on the insertion and even more on the
> treatment for the aggregation.
>
> To treat the 3000 entrances and to insert, or update the tables it needs 10
> minutes.
>
> As I told you I inject 14000 query every 2 minutes, and it needs 10 minutes
> to treat 3000 of those query.
>
> As you can easly understand it's a big narrow section.
>
> I'm not doing the treatment in ones, cause I can't, but all is managed by
> procedure.
>
>> That wasn't a yes/no question. Please choose one of:
>> Are you updating 6Go per week? most of update
>> Are you adding 6Go per week? less of injection,
>
> This action depend if the data are already present in the database.
>
>
>>
>> OK. I assume you're happy with the plans you are getting on these
>> queries, since you've not provided any information about them.
>
> The plan seems ok as it use index as well.
> here is the plan :
>
> explain analyse SELECT "insertUpdateTracks"(137,2605, 852, ('2008-08-06
> 19:28:54'::text)::date,3,'dailydisplay',2,NULL);
> INFO: method 1
> QUERY PLAN
> ------------------------------------------------------------------------------------
> Result (cost=0.00..0.01 rows=1 width=0) (actual time=1.151..1.151 rows=1
> loops=1)
> Total runtime: 1.160 ms
> (2 lignes)
>
> Has you can see the runtime processs for an update in this table.
>
> multiplying this per 10000, it is too long.
>
please don't top-post (put your reply after previous comments).
With fsync on, you are lucky to get 10k inserts in 10 minutes on
single sata 1. The basic issue is that after each time function runs
postgesql tells disk drive to flush, guaranteeing data safety. You
have few different options here:
*) group multiple inserts into single transaction
*) modify function to take multiple 'update' records at once.
*) disable fsync (extremely unsafe as noted)
*) upgrade to 8.3 and disable synchronized_commit (the 'fsync lite', a
good compromise between fsync on/off)
merlin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Plz Heeeelp! performance settings
@ 2008-08-07 15:08 Richard Huxton <dev@archonet.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 10+ messages in thread
From: Richard Huxton @ 2008-08-07 15:08 UTC (permalink / raw)
To: dforums <dforums@vieonet.com>; +Cc: pgsql-performance
dforums wrote:
> The delete is global, the procedure is called for each line/tracks.
> > So - are you calling this function 14000 times to inject your data?
> > You're doing this in one transaction, yes?
> NO I have to make it 14000 times cause, I use some inserted information
> for other insert to make links between data.
Why does that stop you putting all 14000 calls in one transaction?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2008-08-07 15:08 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-08-06 22:12 Plz Heeeelp! performance settings dforum <dforums@vieonet.com>
2008-08-07 02:16 ` Merlin Moncure <mmoncure@gmail.com>
2008-08-07 07:35 ` dforum <dforums@vieonet.com>
2008-08-07 08:33 ` Richard Huxton <dev@archonet.com>
2008-08-07 09:12 ` dforums <dforums@vieonet.com>
2008-08-07 10:40 ` Richard Huxton <dev@archonet.com>
2008-08-07 13:30 ` dforums <dforums@vieonet.com>
2008-08-07 14:12 ` Richard Huxton <dev@archonet.com>
2008-08-07 15:08 ` Richard Huxton <dev@archonet.com>
2008-08-07 15:05 ` Merlin Moncure <mmoncure@gmail.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