Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id F32AB2E0047 for ; Thu, 22 Nov 2007 10:10:08 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 80959-02 for ; Thu, 22 Nov 2007 10:09:52 -0400 (AST) Received: from mrqout2.tiscali.it (mrqout2a.tiscali.it [195.130.225.14]) by postgresql.org (Postfix) with ESMTP id 74DAB2E003A for ; Thu, 22 Nov 2007 10:10:02 -0400 (AST) Received: from [10.39.115.29] by mrq-2 with esmtp (Exim) id 1IvCkg-00036P-97; Thu, 22 Nov 2007 15:10:00 +0100 Received: from ps21 (10.39.75.91) by mail-10.mail.tiscali.sys (7.3.121) id 472FF5C1000629F9 for pgsql-performance@postgresql.org; Thu, 22 Nov 2007 15:09:52 +0100 Message-ID: <25297330.1195740592813.JavaMail.root@ps21> Date: Thu, 22 Nov 2007 15:09:52 +0100 (CET) From: "giuseppe-r@tiscali.it" Reply-To: "giuseppe-r@tiscali.it" To: pgsql-performance@postgresql.org Subject: tuning for TPC-C benchmark MIME-Version: 1.0 Content-Type: text/plain;charset="UTF-8" Content-Transfer-Encoding: quoted-printable xOriginalSenderIP: 213.215.169.84 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.001 tagged_above=0 required=5 tests=BAYES_50=0.001, SARE_RAND_1=2 X-Spam-Level: ** X-Archive-Number: 200711/273 X-Sequence-Number: 27688 Hello all, I'm doing tests on various Database and in particular I'm running a=20 comparison between Oracle 10g and Postgres 8.1 on a dedicated server=20 with 2 processors Dual-Core AMD Opteron 2218 2.6 GHz, 4GB of memory=20 and Debian GNU / Linux version 2.6.18-5. Performance is very similar up=20 to 30 users, but from 40 onwards with Postgres fall quickly. That is=20 not what happens with Oracle that comes to 600 users. Can you help me=20 with the tuning ? Thanks a lot My postgresql.conf configuration is: #--------------------------------------------------------------------------= - # FILE LOCATIONS #--------------------------------------------------------------------------= - # The default values of these variables are driven from the -D command=20 line # switch or PGDATA environment variable, represented here as=20 ConfigDir. #data_directory =3D 'ConfigDir' # use data in another=20 directory hba_file =3D '/etc/postgresql/8.1/main/pg_hba.conf' # host-based=20 authentication file ident_file =3D '/etc/postgresql/8.1/main/pg_ident.conf' # IDENT=20 configuration file # If external_pid_file is not explicitly set, no extra pid file is=20 written. external_pid_file =3D '/var/run/postgresql/8.1-main.pid' #=20 write an extra pid file #--------------------------------------------------------------------------= - # CONNECTIONS AND AUTHENTICATION #--------------------------------------------------------------------------= - # - Connection Settings - #listen_addresses =3D 'localhost' # what IP address(es) to=20 listen on; # comma- separated list of addresses; # defaults to=20 'localhost', '*' =3D all listen_addresses =3D '*' port =3D 5432 max_connections =3D 220 # note: increasing max_connections costs ~400 bytes of shared memory=20 per # connection slot, plus lock space (see max_locks_per_transaction). =20 You # might also need to raise shared_buffers to support more connections. #superuser_reserved_connections =3D 2 unix_socket_directory =3D '/var/run/postgresql' #unix_socket_group =3D '' #unix_socket_permissions =3D 0777 # octal #bonjour_name =3D '' # defaults to the computer=20 name # - Security & Authentication - #authentication_timeout =3D 60 # 1-600, in seconds ssl =3D true #password_encryption =3D on #db_user_namespace =3D off # Kerberos #krb_server_keyfile =3D '' #krb_srvname =3D 'postgres' #krb_server_hostname =3D '' # empty string matches any=20 keytab entry #krb_caseins_users =3D off # - TCP Keepalives - # see 'man 7 tcp' for details #tcp_keepalives_idle =3D 0 # TCP_KEEPIDLE, in seconds; # 0 selects the=20 system default #tcp_keepalives_interval =3D 0 # TCP_KEEPINTVL, in seconds; # 0 selects=20 the system default #tcp_keepalives_count =3D 0 # TCP_KEEPCNT; # 0 selects=20 the system default #--------------------------------------------------------------------------= - # RESOURCE USAGE (except WAL) #--------------------------------------------------------------------------= - # - Memory - shared_buffers =3D 49152 # min 16 or max_connections*2,=20 8KB each, 384MB temp_buffers =3D 1000 # min 100, 8KB each max_prepared_transactions =3D 350 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of=20 shared memory # per transaction slot, plus lock space (see=20 max_locks_per_transaction). work_mem =3D 1024 # min 64, size in=20 KB maintenance_work_mem =3D 524288 # min 1024, size in KB, -512 MB- max_stack_depth =3D 6144 # min 100, size in KB # - Free Space Map - max_fsm_pages =3D 58000 # min max_fsm_relations*16, 6=20 bytes each max_fsm_relations =3D 3000 # min 100, ~70 bytes each # - Kernel Resource Usage - #max_files_per_process =3D 1000 # min 25 #preload_libraries =3D '' # - Cost-Based Vacuum Delay - #vacuum_cost_delay =3D 0 # 0-1000 milliseconds #vacuum_cost_page_hit =3D 1 # 0-10000 credits #vacuum_cost_page_miss =3D 10 # 0-10000 credits #vacuum_cost_page_dirty =3D 20 # 0-10000 credits #vacuum_cost_limit =3D 200 # 0-10000 credits # - Background writer - #bgwriter_delay =3D 5000 # 10-10000 milliseconds=20 between rounds bgwriter_lru_percent =3D 0 # 0-100% of LRU buffers=20 scanned/round bgwriter_lru_maxpages =3D 0 # 0-1000 buffers max=20 written/round bgwriter_all_percent =3D 0 # 0-100% of all buffers=20 scanned/round bgwriter_all_maxpages =3D 0 # 0-1000 buffers max=20 written/round #--------------------------------------------------------------------------= - # WRITE AHEAD LOG #--------------------------------------------------------------------------= - # - Settings - fsync =3D off # turns forced synchronization=20 on or off #wal_sync_method =3D fsync # the default is the first=20 option # supported by the operating=20 system: # open_datasync # fdatasync # fsync # fsync_writethrough # open_sync #full_page_writes =3D on # recover from partial page=20 writes #wal_buffers =3D 8 # min 4, 8KB each #commit_delay =3D 5 # range 0-100000, in=20 microseconds #commit_siblings =3D 5 # range 1-1000 # - Checkpoints - checkpoint_segments =3D 100 # in logfile segments, min 1,=20 16MB each checkpoint_timeout =3D 1800 # range 30-3600, in seconds #checkpoint_warning =3D 30 # in seconds, 0 is off # - Archiving - #archive_command =3D '' # command to use to archive a=20 logfile # segment #--------------------------------------------------------------------------= - # QUERY TUNING #--------------------------------------------------------------------------= - # - Planner Method Configuration - #enable_bitmapscan =3D on #enable_hashagg =3D on #enable_hashjoin =3D on #enable_indexscan =3D on #enable_mergejoin =3D on #enable_nestloop =3D on #enable_seqscan =3D on #enable_sort =3D on #enable_tidscan =3D on # - Planner Cost Constants - effective_cache_size =3D 196608 # typically 8KB each #random_page_cost =3D 4 # units are one sequential=20 page fetch # cost #cpu_tuple_cost =3D 0.01 # (same) #cpu_index_tuple_cost =3D 0.001 # (same) #cpu_operator_cost =3D 0.0025 # (same) # - Genetic Query Optimizer - #geqo =3D on #geqo_threshold =3D 12 #geqo_effort =3D 5 # range 1-10 #geqo_pool_size =3D 0 # selects default based on=20 effort #geqo_generations =3D 0 # selects default based on=20 effort #geqo_selection_bias =3D 2.0 # range 1.5-2.0 # - Other Planner Options - #default_statistics_target =3D 10 # range 1-1000 #constraint_exclusion =3D off #from_collapse_limit =3D 8 #join_collapse_limit =3D 8 # 1 disables collapsing of=20 explicit # JOINs #--------------------------------------------------------------------------= - # ERROR REPORTING AND LOGGING #--------------------------------------------------------------------------= - # - Where to Log - #log_destination =3D 'stderr' # Valid values are=20 combinations of # stderr, syslog and eventlog, # depending on platform. # This is used when logging to stderr: #redirect_stderr =3D off # Enable capturing of stderr=20 into log # files # These are only used if redirect_stderr is on: #log_directory =3D 'pg_log' # Directory where log files=20 are written # Can be absolute or relative=20 to PGDATA #log_filename =3D 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name=20 pattern. # Can include strftime()=20 escapes #log_truncate_on_rotation =3D off # If on, any existing log file of the=20 same # name as the new log file=20 will be # truncated rather than=20 appended to. But # such truncation only occurs=20 on # time-driven rotation, not on=20 restarts # or size-driven rotation.=20 Default is # off, meaning append to=20 existing files # in all cases. #log_rotation_age =3D 1440 # Automatic rotation of=20 logfiles will # happen after so many=20 minutes. 0 to # disable. #log_rotation_size =3D 10240 # Automatic rotation of=20 logfiles will # happen after so many=20 kilobytes of log # output. 0 to disable. # These are relevant when logging to syslog: #syslog_facility =3D 'LOCAL0' #syslog_ident =3D 'postgres' # - When to Log - #client_min_messages =3D notice # Values, in order of=20 decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # log # notice # warning # error #log_min_messages =3D notice # Values, in order of=20 decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic #log_error_verbosity =3D default # terse, default, or verbose=20 messages #log_min_error_statement =3D panic # Values in order of=20 increasing severity: # debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # panic(off) #log_min_duration_statement =3D -1 # -1 is disabled, 0 logs all=20 statements # and their durations, in=20 milliseconds. #silent_mode =3D off # DO NOT USE without syslog or # redirect_stderr # - What to Log - #debug_print_parse =3D off #debug_print_rewritten =3D off #debug_print_plan =3D off #debug_pretty_print =3D off #log_connections =3D off #log_disconnections =3D off #log_duration =3D off log_line_prefix =3D '%t ' # Special values: # %u =3D user name # %d =3D database name # %r =3D remote host and port # %h =3D remote host # %p =3D PID # %t =3D timestamp (no=20 milliseconds) # %m =3D timestamp with=20 milliseconds # %i =3D command tag # %c =3D session id # %l =3D session line number # %s =3D session start=20 timestamp # %x =3D transaction id # %q =3D stop here in non- session # processes # %% =3D '%' # e.g. '<%u%%%d> ' #log_statement =3D 'none' # none, mod, ddl, all #log_hostname =3D off #--------------------------------------------------------------------------= - # RUNTIME STATISTICS #--------------------------------------------------------------------------= - # - Statistics Monitoring - #log_parser_stats =3D off #log_planner_stats =3D off #log_executor_stats =3D off #log_statement_stats =3D off # - Query/Index Statistics Collector - stats_start_collector =3D off stats_command_string =3D off stats_block_level =3D off stats_row_level =3D off stats_reset_on_server_start =3D off #--------------------------------------------------------------------------= - # AUTOVACUUM PARAMETERS #--------------------------------------------------------------------------= - autovacuum =3D off # enable autovacuum=20 subprocess? #autovacuum_naptime =3D 60 # time between autovacuum=20 runs, in secs #autovacuum_vacuum_threshold =3D 1000 # min # of tuple updates=20 before # vacuum #autovacuum_analyze_threshold =3D 500 # min # of tuple updates=20 before # analyze #autovacuum_vacuum_scale_factor =3D 0.4 # fraction of rel size before # vacuum #autovacuum_analyze_scale_factor =3D 0.2 # fraction of rel size before # analyze #autovacuum_vacuum_cost_delay =3D -1 # default vacuum cost delay=20 for # autovac, -1 means use # vacuum_cost_delay #autovacuum_vacuum_cost_limit =3D -1 # default vacuum cost limit=20 for # autovac, -1 means use # vacuum_cost_limit #--------------------------------------------------------------------------= - # CLIENT CONNECTION DEFAULTS #--------------------------------------------------------------------------= - # - Statement Behavior - #search_path =3D '$user,public' # schema names #default_tablespace =3D '' # a tablespace name, '' uses # the default #check_function_bodies =3D on #default_transaction_isolation =3D 'read committed' #default_transaction_read_only =3D off #statement_timeout =3D 0 # 0 is disabled, in=20 milliseconds # - Locale and Formatting - #datestyle =3D 'iso, mdy' #timezone =3D unknown # actually, defaults to TZ # environment setting #australian_timezones =3D off #extra_float_digits =3D 0 # min -15, max 2 #client_encoding =3D sql_ascii # actually, defaults to=20 database # encoding # These settings are initialized by initdb -- they might be changed lc_messages =3D 'it_IT.UTF-8' # locale for system=20 error message # strings lc_monetary =3D 'it_IT.UTF-8' # locale for monetary=20 formatting lc_numeric =3D 'it_IT.UTF-8' # locale for number=20 formatting lc_time =3D 'it_IT.UTF-8' # locale for time=20 formatting # - Other Defaults - #explain_pretty_print =3D on #dynamic_library_path =3D '$libdir' #--------------------------------------------------------------------------= - # LOCK MANAGEMENT #--------------------------------------------------------------------------= - #deadlock_timeout =3D 1000 # in milliseconds #max_locks_per_transaction =3D 64 # min 10 # note: each lock table slot uses ~220 bytes of shared memory, and=20 there are # max_locks_per_transaction * (max_connections +=20 max_prepared_transactions) # lock table slots. #--------------------------------------------------------------------------= - # VERSION/PLATFORM COMPATIBILITY #--------------------------------------------------------------------------= - # - Previous Postgres Versions - #add_missing_from =3D off #backslash_quote =3D safe_encoding # on, off, or safe_encoding #default_with_oids =3D off #escape_string_warning =3D off #regex_flavor =3D advanced # advanced, extended, or basic #sql_inheritance =3D on # - Other Platforms & Clients - #transform_null_equals =3D off #--------------------------------------------------------------------------= - # CUSTOMIZED OPTIONS #--------------------------------------------------------------------------= - #custom_variable_classes =3D '' # list of custom variable=20 class names ________________________________________________ Tiscali Voce 8 Mega (Telefono+Adsl). Attiva entro il 22/11/07: chiami in tu= tta Italia e navighi SENZA LIMITI A SOLI 4,95=E2=82=AC AL MESE FINO AL 31/0= 3/2008!=20 Dal 1=C2=B0 aprile 2008 paghi 28,95 =E2=82=AC al mese. http://abbonati.tiscali.it/telefono-adsl/prodotti/tc/voce8mega/