Обсуждение: PostgreSQL with Zabbix - problem of newbe

От:
Krzysztof Kardas
Дата:

Hi,
I am using zabbix monitoring software. The backbone database for
zabbix is postgresql 8.1 installed od linux.

Database server has 3GB of RAM, 1 CPU Dual Core and 2 SAS disks in RAID 1.

Zabbix makes a lot of inserts and updates on database.  The problem is
that when autovaccum starts the database freezes.
I am trying to make better performance, I have read a lot of documents
and sites about performance tunning but still no luck.

My current database variables:

 add_missing_from                | off
  | Automatically adds missing table references to FROM
 clauses.
 archive_command                 | unset
  | WAL archiving command.
 australian_timezones            | off
  | Interprets ACST, CST, EST, and SAT as Australian ti
me zones.
 authentication_timeout          | 60
  | Sets the maximum time in seconds to complete client
 authentication.
 autovacuum                      | on
  | Starts the autovacuum subprocess.
 autovacuum_analyze_scale_factor | 0.1
  | Number of tuple inserts, updates or deletes prior t
o analyze as a fraction of reltuples.
 autovacuum_analyze_threshold    | 5000
  | Minimum number of tuple inserts, updates or deletes
 prior to analyze.
 autovacuum_naptime              | 60
  | Time to sleep between autovacuum runs, in seconds.
 autovacuum_vacuum_cost_delay    | -1
  | Vacuum cost delay in milliseconds, for autovacuum.
 autovacuum_vacuum_cost_limit    | -1
  | Vacuum cost amount available before napping, for au
tovacuum.
 autovacuum_vacuum_scale_factor  | 0.2
  | Number of tuple updates or deletes prior to vacuum
as a fraction of reltuples.
 autovacuum_vacuum_threshold     | 100000
  | Minimum number of tuple updates or deletes prior to
 vacuum.
 backslash_quote                 | safe_encoding
  | Sets whether "\'" is allowed in string literals.
 bgwriter_all_maxpages           | 5
  | Background writer maximum number of all pages to fl
ush per round
 bgwriter_all_percent            | 0.333
  | Background writer percentage of all buffers to flus
h per round
 bgwriter_delay                  | 200
  | Background writer sleep time between rounds in mill
iseconds
 bgwriter_lru_maxpages           | 5
  | Background writer maximum number of LRU pages to fl
ush per round
 bgwriter_lru_percent            | 1
  | Background writer percentage of LRU buffers to flus
h per round
 block_size                      | 8192
  | Shows size of a disk block
 bonjour_name                    | unset
  | Sets the Bonjour broadcast service name.
 check_function_bodies           | on
  | Check function bodies during CREATE FUNCTION.
 checkpoint_segments             | 32
  | Sets the maximum distance in log segments between a
utomatic WAL checkpoints.
 checkpoint_timeout              | 300
  | Sets the maximum time in seconds between automatic
WAL checkpoints.
 checkpoint_warning              | 30
  | Logs if filling of checkpoint segments happens more
 frequently than this (in seconds).
 client_encoding                 | UTF8
  | Sets the client's character set encoding.
 client_min_messages             | notice
  | Sets the message levels that are sent to the client
.
 commit_delay                    | 0
  | Sets the delay in microseconds between transaction
commit and flushing WAL to disk.
 commit_siblings                 | 5
  | Sets the minimum concurrent open transactions befor
e performing commit_delay.
 config_file                     | /var/lib/pgsql/data/postgresql.conf
  | Sets the server's main configuration file.
 constraint_exclusion            | off
  | Enables the planner to use constraints to optimize
queries.
 cpu_index_tuple_cost            | 0.001
  | Sets the planner's estimate of processing cost for
each index tuple (row) during index scan.
 cpu_operator_cost               | 0.0025
  | Sets the planner's estimate of processing cost of e
ach operator in WHERE.
 cpu_tuple_cost                  | 0.01
  | Sets the planner's estimate of the cost of processi
ng each tuple (row).
 custom_variable_classes         | unset
  | Sets the list of known custom variable classes.
 data_directory                  | /var/lib/pgsql/data
  | Sets the server's data directory.
 DateStyle                       | ISO, MDY
  | Sets the display format for date and time values.
 db_user_namespace               | off
  | Enables per-database user names.
 deadlock_timeout                | 1000
  | The time in milliseconds to wait on lock before che
cking for deadlock.
 debug_pretty_print              | off
  | Indents parse and plan tree displays.
 debug_print_parse               | off
  | Prints the parse tree to the server log.
 debug_print_plan                | off
  | Prints the execution plan to server log.
 debug_print_rewritten           | off
  | Prints the parse tree after rewriting to server log
.
 default_statistics_target       | 100
  | Sets the default statistics target.
 default_tablespace              | unset
  | Sets the default tablespace to create tables and in
dexes in.
 default_transaction_isolation   | read committed
  | Sets the transaction isolation level of each new tr
ansaction.
 default_transaction_read_only   | off
  | Sets the default read-only status of new transactio
ns.
 default_with_oids               | off
  | Create new tables with OIDs by default.
 dynamic_library_path            | $libdir
  | Sets the path for dynamically loadable modules.
 effective_cache_size            | 190000
  | Sets the planner's assumption about size of the dis
k cache.
 enable_bitmapscan               | on
  | Enables the planner's use of bitmap-scan plans.
 enable_hashagg                  | on
  | Enables the planner's use of hashed aggregation pla
ns.
 enable_hashjoin                 | on
  | Enables the planner's use of hash join plans.
 enable_indexscan                | on
  | Enables the planner's use of index-scan plans.
 enable_mergejoin                | on
  | Enables the planner's use of merge join plans.
 enable_nestloop                 | on
  | Enables the planner's use of nested-loop join plans
.
 enable_seqscan                  | on
  | Enables the planner's use of sequential-scan plans.
 enable_sort                     | on
  | Enables the planner's use of explicit sort steps.
 enable_tidscan                  | on
  | Enables the planner's use of TID scan plans.
 escape_string_warning           | off
  | Warn about backslash escapes in ordinary string lit
erals.
 explain_pretty_print            | on
  | Uses the indented output format for EXPLAIN VERBOSE
.
 external_pid_file               | unset
  | Writes the postmaster PID to the specified file.
 extra_float_digits              | 0
  | Sets the number of digits displayed for floating-po
int values.
 from_collapse_limit             | 8
  | Sets the FROM-list size beyond which subqueries are
 not collapsed.
 fsync                           | on
  | Forces synchronization of updates to disk.
 full_page_writes                | on
  | Writes full pages to WAL when first modified after
a checkpoint.
 geqo                            | on
  | Enables genetic query optimization.
 geqo_effort                     | 5
  | GEQO: effort is used to set the default for other G
EQO parameters.
 geqo_generations                | 0
  | GEQO: number of iterations of the algorithm.
 geqo_pool_size                  | 0
  | GEQO: number of individuals in the population.
 geqo_selection_bias             | 2
  | GEQO: selective pressure within the population.
 geqo_threshold                  | 12
  | Sets the threshold of FROM items beyond which GEQO
is used.
 hba_file                        | /var/lib/pgsql/data/pg_hba.conf
  | Sets the server's "hba" configuration file
 ident_file                      | /var/lib/pgsql/data/pg_ident.conf
  | Sets the server's "ident" configuration file
 integer_datetimes               | off
  | Datetimes are integer based.
 join_collapse_limit             | 8
  | Sets the FROM-list size beyond which JOIN construct
s are not flattened.
 krb_caseins_users               | off
  | Sets whether Kerberos user names should be treated
as case-insensitive.
 krb_server_hostname             | unset
  | Sets the hostname of the Kerberos server.
 krb_server_keyfile              |
FILE:/etc/sysconfig/pgsql/krb5.keytab | Sets the location of the
Kerberos server key file.
 krb_srvname                     | postgres
  | Sets the name of the Kerberos service.
 lc_collate                      | pl_PL.UTF-8
  | Shows the collation order locale.
 lc_ctype                        | pl_PL.UTF-8
  | Shows the character classification and case convers
ion locale.
 lc_messages                     | pl_PL.UTF-8
  | Sets the language in which messages are displayed.
 lc_monetary                     | pl_PL.UTF-8
  | Sets the locale for formatting monetary amounts.
 lc_numeric                      | pl_PL.UTF-8
  | Sets the locale for formatting numbers.
 lc_time                         | pl_PL.UTF-8
  | Sets the locale for formatting date and time values
.
 listen_addresses                | *
  | Sets the host name or IP address(es) to listen to.
 log_connections                 | off
  | Logs each successful connection.
 log_destination                 | stderr
  | Sets the destination for server log output.
 log_directory                   | pg_log
  | Sets the destination directory for log files.
 log_disconnections              | off
  | Logs end of a session, including duration.
 log_duration                    | off
  | Logs the duration of each completed SQL statement.
 log_error_verbosity             | default
  | Sets the verbosity of logged messages.
 log_executor_stats              | off
  | Writes executor performance statistics to the serve
r log.
 log_filename                    | postgresql-%a.log
  | Sets the file name pattern for log files.
 log_hostname                    | off
  | Logs the host name in the connection logs.
 log_line_prefix                 | unset
  | Controls information prefixed to each log line
 log_min_duration_statement      | -1
  | Sets the minimum execution time in milliseconds abo
ve which statements will be logged.
 log_min_error_statement         | panic
  | Causes all statements generating error at or above
this level to be logged.
 log_min_messages                | notice
  | Sets the message levels that are logged.
 log_parser_stats                | off
  | Writes parser performance statistics to the server
log.
 log_planner_stats               | off
  | Writes planner performance statistics to the server
 log.
 log_rotation_age                | 1440
  | Automatic log file rotation will occur after N minu
tes
 log_rotation_size               | 0
  | Automatic log file rotation will occur after N kilo
bytes
 log_statement                   | none
  | Sets the type of statements logged.
 log_statement_stats             | off
  | Writes cumulative performance statistics to the ser
ver log.
 log_truncate_on_rotation        | on
  | Truncate existing log files of same name during log
 rotation.
 maintenance_work_mem            | 256000
  | Sets the maximum memory to be used for maintenance
operations.
 max_connections                 | 400
  | Sets the maximum number of concurrent connections.
 max_files_per_process           | 1000
  | Sets the maximum number of simultaneously open file
s for each server process.
 max_fsm_pages                   | 1000000
  | Sets the maximum number of disk pages for which fre
e space is tracked.
 max_fsm_relations               | 1000
  | Sets the maximum number of tables and indexes for w
hich free space is tracked.
 max_function_args               | 100
  | Shows the maximum number of function arguments.
 max_identifier_length           | 63
  | Shows the maximum identifier length
 max_index_keys                  | 32
  | Shows the maximum number of index keys.
 max_locks_per_transaction       | 64
  | Sets the maximum number of locks per transaction.
 max_prepared_transactions       | 100
  | Sets the maximum number of simultaneously prepared
transactions.
 max_stack_depth                 | 10240
  | Sets the maximum stack depth, in kilobytes.
 password_encryption             | off
  | Encrypt passwords.
 port                            | 5432
  | Sets the TCP port the server listens on.
 pre_auth_delay                  | 0
  | no description available
 preload_libraries               | unset
  | Lists shared libraries to preload into server.
 random_page_cost                | 3
  | Sets the planner's estimate of the cost of a nonseq
uentially fetched disk page.
 redirect_stderr                 | on
  | Start a subprocess to capture stderr output into lo
g files.
 regex_flavor                    | advanced
  | Sets the regular expression "flavor".
 search_path                     | $user,public
  | Sets the schema search order for names that are not
 schema-qualified.
 server_encoding                 | UTF8
  | Sets the server (database) character set encoding.
 server_version                  | 8.1.11
  | Shows the server version.
 shared_buffers                  | 95000
  | Sets the number of shared memory buffers used by th
e server.
 silent_mode                     | off
  | Runs the server silently.
 sql_inheritance                 | on
  | Causes subtables to be included by default in vario
us commands.
 ssl                             | off
  | Enables SSL connections.
 standard_conforming_strings     | off
  | '...' strings treat backslashes literally.
 statement_timeout               | 0
  | Sets the maximum allowed duration (in milliseconds)
 of any statement.
 stats_block_level               | on
  | Collects block-level statistics on database activit
y.
 stats_command_string            | on
  | Collects statistics about executing commands.
 stats_reset_on_server_start     | off
  | Zeroes collected statistics on server restart.
 stats_row_level                 | on
  | Collects row-level statistics on database activity.
 stats_start_collector           | on
  | Starts the server statistics-collection subprocess.
 superuser_reserved_connections  | 2
  | Sets the number of connection slots reserved for su
perusers.
 syslog_facility                 | LOCAL0
  | Sets the syslog "facility" to be used when syslog e
nabled.
 syslog_ident                    | postgres
  | Sets the program name used to identify PostgreSQL m
essages in syslog.
 tcp_keepalives_count            | 0
  | Maximum number of TCP keepalive retransmits.
 tcp_keepalives_idle             | 0
  | Seconds between issuing TCP keepalives.
 tcp_keepalives_interval         | 0
  | Seconds between TCP keepalive retransmits.
 temp_buffers                    | 1000
  | Sets the maximum number of temporary buffers used b
y each session.
 TimeZone                        | Poland
  | Sets the time zone for displaying and interpreting
time stamps.
 trace_notify                    | off
  | Generates debugging output for LISTEN and NOTIFY.
 trace_sort                      | off
  | Emit information about resource usage in sorting.
 transaction_isolation           | read committed
  | Sets the current transaction's isolation level.
 transaction_read_only           | off
  | Sets the current transaction's read-only status.
 transform_null_equals           | off
  | Treats "expr=NULL" as "expr IS NULL".
 unix_socket_directory           | unset
  | Sets the directory where the Unix-domain socket wil
l be created.
 unix_socket_group               | unset
  | Sets the owning group of the Unix-domain socket.
 unix_socket_permissions         | 511
  | Sets the access permissions of the Unix-domain sock
et.
 vacuum_cost_delay               | 10
  | Vacuum cost delay in milliseconds.
 vacuum_cost_limit               | 200
  | Vacuum cost amount available before napping.
 vacuum_cost_page_dirty          | 20
  | Vacuum cost for a page dirtied by vacuum.
 vacuum_cost_page_hit            | 1
  | Vacuum cost for a page found in the buffer cache.
 vacuum_cost_page_miss           | 10
  | Vacuum cost for a page not found in the buffer cach
e.
 wal_buffers                     | 2000
  | Sets the number of disk-page buffers in shared memo
ry for WAL.
 wal_sync_method                 | fdatasync
  | Selects the method used for forcing WAL updates out
 to disk.
 work_mem                        | 1600000
  | Sets the maximum memory to be used for query worksp
aces.
 zero_damaged_pages              | off
  | Continues processing past damaged page headers.
(163 rows)

I would be very grateful for any help.

Greetings for all.

От:
Grzegorz Jaśkiewicz
Дата:

starting with 8.3, there's this new feature called HOT, which helps a lot when you do loads of updates.
Plus writer is much quicker (30-40% sometimes), and autovacuum behaves much nicer.
Bottom line, upgrade to 8.3, 8.1 had autovacuum disabled by default for a reason.

От:
Merlin Moncure
Дата:

2010/4/8 Grzegorz Jaśkiewicz <>:
> starting with 8.3, there's this new feature called HOT, which helps a lot
> when you do loads of updates.
> Plus writer is much quicker (30-40% sometimes), and autovacuum behaves much
> nicer.
> Bottom line, upgrade to 8.3, 8.1 had autovacuum disabled by default for a
> reason.

postgresql 8.2: autovacuum enabled by default
postgresql 8.3: HOT (reduces update penalty -- zabbix does a lot of updates)

previous to 8.2, to get good performance on zabbix you need to
aggressively vacuum the heavily updated tables yourself.

merlin

От:
Robert Haas
Дата:

2010/4/8 Merlin Moncure <>:
> previous to 8.2, to get good performance on zabbix you need to
> aggressively vacuum the heavily updated tables yourself.

Generally if you DON'T vacuum aggressively enough, then vacuums will
take a really long and painful amount of time, perhaps accounting for
the "hang" the OP observed.  There's really no help for it but to
sweat it out once, and then do it frequently enough afterward that it
doesn't become a problem.

...Robert

От:
Richard Yen
Дата:

Kind of off-topic, but I've found that putting the history table on a separate spindle (using a separate tablespace)
alsohelps improve performance. 

--Richard



On Apr 8, 2010, at 12:44 PM, Robert Haas wrote:

> 2010/4/8 Merlin Moncure <>:
>> previous to 8.2, to get good performance on zabbix you need to
>> aggressively vacuum the heavily updated tables yourself.
>
> Generally if you DON'T vacuum aggressively enough, then vacuums will
> take a really long and painful amount of time, perhaps accounting for
> the "hang" the OP observed.  There's really no help for it but to
> sweat it out once, and then do it frequently enough afterward that it
> doesn't become a problem.
>
> ...Robert
>
> --
> Sent via pgsql-performance mailing list ()
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


От:
Greg Smith
Дата:

Krzysztof Kardas wrote:
> My current database variables:
>

That is way too much stuff to sort through.  Try this instead, to only
get the values you've set to something rather than every single one:

select name,unit,current_setting(name) from pg_settings where
source='configuration file' ;

Also, a snapshot of output from "vmstat 1" during some period when the
server is performing badly would be very helpful to narrow down what's
going on.

The easy answer to your question is simply that autovacuum is terrible
on PG 8.1.  You can tweak it to do better, but that topic isn't covered
very well in the sort of tuning guides you'll find floating around.
This is because most of the people who care about this sort of issue
have simply upgraded to a later version where autovacuum is much better.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
   www.2ndQuadrant.us


От:
Greg Smith
Дата:

Merlin Moncure wrote:
> postgresql 8.2: autovacuum enabled by default
> postgresql 8.3: HOT (reduces update penalty -- zabbix does a lot of updates)
>

autovacuum wasn't enabled by default until 8.3.  It didn't really work
all that well out of the box until the support for multiple workers was
added in that version, along with some tweaking to its default
parameters.  There's also a lot more logging information available, both
the server logs and the statistics tables, to watch what it's doing that
were added in 8.3.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
   www.2ndQuadrant.us


От:
Merlin Moncure
Дата:

2010/4/9 Greg Smith <>:
> Merlin Moncure wrote:
>>
>> postgresql 8.2: autovacuum enabled by default
>> postgresql 8.3: HOT (reduces update penalty -- zabbix does a lot of
>> updates)
>>
>
> autovacuum wasn't enabled by default until 8.3.  It didn't really work all
> that well out of the box until the support for multiple workers was added in
> that version, along with some tweaking to its default parameters.  There's
> also a lot more logging information available, both the server logs and the
> statistics tables, to watch what it's doing that were added in 8.3.

you're right!  iirc it was changed at the last minute...

merlin

От:
Scott Mead
Дата:

The OP is using: 

autovacuum_vacuum_threshold     | 100000

That means that vacuum won't consider a table to be 'vacuum-able' until after 100k changes.... that's nowhere near aggressive enough.  Probably what's happening is that when autovacuum finally DOES start on a table, it just takes forever. 

--Scott



2010/4/9 Merlin Moncure <>
2010/4/9 Greg Smith <>:
> Merlin Moncure wrote:
>>
>> postgresql 8.2: autovacuum enabled by default
>> postgresql 8.3: HOT (reduces update penalty -- zabbix does a lot of
>> updates)
>>
>
> autovacuum wasn't enabled by default until 8.3.  It didn't really work all
> that well out of the box until the support for multiple workers was added in
> that version, along with some tweaking to its default parameters.  There's
> also a lot more logging information available, both the server logs and the
> statistics tables, to watch what it's doing that were added in 8.3.

you're right!  iirc it was changed at the last minute...

merlin

--
Sent via pgsql-performance mailing list ()
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

От:
Greg Smith
Дата:

Off-list message that should have made it onto here, from Krzysztof:

I have changed PostgreSQL to 8.3. I think that the database is really working faster.  New settings:

              name               | unit |  current_setting
---------------------------------+------+-------------------
 autovacuum                      |      | on
 autovacuum_analyze_scale_factor |      | 0.1
 autovacuum_analyze_threshold    |      | 5000
 autovacuum_freeze_max_age       |      | 200000000
 autovacuum_max_workers          |      | 3
 autovacuum_naptime              | s    | 1min
 autovacuum_vacuum_cost_delay    | ms   | 20ms
 autovacuum_vacuum_cost_limit    |      | -1
 autovacuum_vacuum_scale_factor  |      | 0.2
 autovacuum_vacuum_threshold     |      | 5000
 checkpoint_segments             |      | 32
 constraint_exclusion            |      | off
 deadlock_timeout                | ms   | 1min
 default_statistics_target       |      | 100
 from_collapse_limit             |      | 8
 join_collapse_limit             |      | 8
 log_autovacuum_min_duration     | ms   | 0
 maintenance_work_mem            | kB   | 256MB
 max_connections                 |      | 400
 max_fsm_pages                   |      | 2048000
 max_locks_per_transaction       |      | 64
 max_prepared_transactions       |      | 100
 max_stack_depth                 | kB   | 20MB
 random_page_cost                |      | 4
 shared_buffers                  | 8kB  | 760MB
 statement_timeout               | ms   | 0
 temp_buffers                    | 8kB  | 32768
 vacuum_cost_delay               | ms   | 0
 vacuum_cost_limit               |      | 200
 vacuum_cost_page_dirty          |      | 20
 vacuum_cost_page_hit            |      | 1
 vacuum_cost_page_miss           |      | 10
 wal_buffers                     | 8kB  | 16MB
 work_mem                        | kB   | 1600MB


I trimmed the above a bit to focus on the performance related
parameters.  Just doing the 8.3 upgrade has switched over to sane
autovacuum settings now, which should improve things significantly.

The main problem with this configuration is that work_mem is set to an
unsafe value--1.6GB.  With potentially 400 connections and about 2GB of
RAM free after starting the server, work_mem='4MB' is as large as you
can safely set this.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
   www.2ndQuadrant.us


От:
Scott Marlowe
Дата:

On Fri, Apr 9, 2010 at 10:03 AM, Greg Smith <> wrote:

> The main problem with this configuration is that work_mem is set to an
> unsafe value--1.6GB.  With potentially 400 connections and about 2GB of RAM
> free after starting the server, work_mem='4MB' is as large as you can safely
> set this.

> maintenance_work_mem            | kB   | 256MB

Note that 256MB maintenance_work_mem on a machine with 3 autovac
threads and only 2 Gig free is kinda high too.

От:
Merlin Moncure
Дата:

On Fri, Apr 9, 2010 at 12:03 PM, Greg Smith <> wrote:
> The main problem with this configuration is that work_mem is set to an
> unsafe value--1.6GB.  With potentially 400 connections and about 2GB of RAM
> free after starting the server, work_mem='4MB' is as large as you can safely
> set this.

if you need more work_mem for this or that and also need to serve a
lot of connections, you can always set it locally (1.6GB is still too
high though -- maybe 64mb if you need to do a big sort or something
like that).

Another path to take is to install pgbouncer, which at 400 connections
is worth considering -- but only if your client stack doesn't use
certain features that require a private database session.  zabbix will
_probably_ work because it is db portable software (still should check
however).

merlin

От:
Scott Marlowe
Дата:

On Fri, Apr 9, 2010 at 10:30 AM, Merlin Moncure <> wrote:
> On Fri, Apr 9, 2010 at 12:03 PM, Greg Smith <> wrote:
>> The main problem with this configuration is that work_mem is set to an
>> unsafe value--1.6GB.  With potentially 400 connections and about 2GB of RAM
>> free after starting the server, work_mem='4MB' is as large as you can safely
>> set this.
>
> if you need more work_mem for this or that and also need to serve a
> lot of connections, you can always set it locally (1.6GB is still too
> high though -- maybe 64mb if you need to do a big sort or something
> like that).
>
> Another path to take is to install pgbouncer, which at 400 connections
> is worth considering -- but only if your client stack doesn't use
> certain features that require a private database session.  zabbix will
> _probably_ work because it is db portable software (still should check
> however).

Also remember you can set it by user or by db, depending on your
needs.  I had a server that had a reporting db and an app db.  The app
db was set to 1 or 2 Meg work_mem, and the reporting db that had only
one or two threads ever run at once was set to 128Meg.  Worked
perfectly for what we needed.

От:
Krzysztof Kardas
Дата:

2010/4/9 Scott Marlowe <>:
> On Fri, Apr 9, 2010 at 10:30 AM, Merlin Moncure <> wrote:
>> On Fri, Apr 9, 2010 at 12:03 PM, Greg Smith <> wrote:
>>> The main problem with this configuration is that work_mem is set to an
>>> unsafe value--1.6GB.  With potentially 400 connections and about 2GB of RAM
>>> free after starting the server, work_mem='4MB' is as large as you can safely
>>> set this.
>>
>> if you need more work_mem for this or that and also need to serve a
>> lot of connections, you can always set it locally (1.6GB is still too
>> high though -- maybe 64mb if you need to do a big sort or something
>> like that).
>>
>> Another path to take is to install pgbouncer, which at 400 connections
>> is worth considering -- but only if your client stack doesn't use
>> certain features that require a private database session.  zabbix will
>> _probably_ work because it is db portable software (still should check
>> however).
>
> Also remember you can set it by user or by db, depending on your
> needs.  I had a server that had a reporting db and an app db.  The app
> db was set to 1 or 2 Meg work_mem, and the reporting db that had only
> one or two threads ever run at once was set to 128Meg.  Worked
> perfectly for what we needed.
>

Thanks for all Your advices. I will set up new parameters on Monday
morning and see how it perform.

Greetings for all PostgreSQL Team

--
Krzysztof Kardas

От:
Krzysztof Kardas
Дата:

<cut>
Hi all.

Well I have used all Your recomendations but I still have no luck with
performance tunning. The machine has a moments thas was utilized in
100%. The problem was I/O on disks. CPU's were busy on system
interrupts.

I have started again to look of I/O performance tunning and I have changed a

synchronous_commit = off

Ofcourse with risk that if there will be a power failure I will lose
some data. But this is acceptable.

This caused a monumental performance jump. From a machine that is
utilized on 100%, machine is now sleeping and doing nothing. I have
executed some sqls on huge tables like history and all has executed
like lightning. Comparing to MySQL, PostgreSQL in this configuration
is about 30 - 40% faster in serving data. Housekeeper is about 2 to 3
times faster!!!!

Many thanks to all helpers and all PostgreSQL team.

--
Greeting
Krzysztof Kardas

От:
Grzegorz Jaśkiewicz
Дата:

That really sounds like hardware issue. The I/O causes the system to freeze basically. 
Happens sometimes on cheaper hardware. 

От:
"Kevin Grittner"
Дата:

Krzysztof Kardas <> wrote:

> synchronous_commit = off

> This caused a monumental performance jump. From a machine that is
> utilized on 100%, machine is now sleeping and doing nothing. I
> have executed some sqls on huge tables like history and all has
> executed like lightning. Comparing to MySQL, PostgreSQL in this
> configuration is about 30 - 40% faster in serving data.
> Housekeeper is about 2 to 3 times faster!!!!

If you have a good RAID controller with battery backup for the
cache, and it's configured to write-back, this setting shouldn't
make very much difference.  Next time you're looking at hardware for
a database server, I strongly recommend you get such a RAID
controller and make sure it is configured to write-back.

Anyway, I'm glad to hear that things are working well for you now!

-Kevin

От:
Krzysztof Kardas
Дата:

W dniu 14 kwietnia 2010 15:30 użytkownik Grzegorz Jaśkiewicz
<> napisał:
> That really sounds like hardware issue. The I/O causes the system to freeze
> basically.
> Happens sometimes on cheaper hardware.
>

Probably You have right because this is HS21 Blade Server. And as You
know blades are cheap and good. Why blades are good - because they are
cheap (quoting IBM salesman). I know this hardware is not made for
databases but for now I do not have any other server. Firmware on this
current server is very old and it should be upgraded and there are
many other things to do. VMWare machines (currently I have ESX 3.5,
vSphere 4 is based od 64bit RedHat5 system and is much faster that 3.5
but migration process is not even planned) has still to low
performance for database solutions (of course in using vmdk, not RAW
device mapping or Virtual WWN solution for accessing LUN-s).

As more I am reading than more I see that the file system is wrong
partitioned. For example - all logs and database files are on the same
volume, and that is not right.
Kevin Grittner also mentioned about write back function on the
controller. LSI controllers for blades has that function as far as I
know. I have to check it if that option is turned on.

As I mentioned - I am not familiar with databases so I have made some
mistakes but I am very happy for the effects how fast now Zabbix
works, and how easy PostgreSQL reclaims space. I think it was a good
decision and maybe I will try to interest some people in my company in
PostgreSQL instate of Oracle XE.

Once more time - many thanks to all :)

--
Greetings
Krzysztof Kardas