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.

Search Discussions

  • Grzegorz Jaśkiewicz at Apr 8, 2010 at 9:32 am
    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 at Apr 8, 2010 at 1:16 pm

    2010/4/8 Grzegorz Jaśkiewicz <gryzman@gmail.com>:
    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 at Apr 8, 2010 at 7:44 pm

    2010/4/8 Merlin Moncure <mmoncure@gmail.com>:
    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 at Apr 8, 2010 at 8:08 pm
    Kind of off-topic, but I've found that putting the history table on a separate spindle (using a separate tablespace) also helps improve performance.

    --Richard


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

    2010/4/8 Merlin Moncure <mmoncure@gmail.com>:
    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 (pgsql-performance@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-performance
  • Greg Smith at Apr 9, 2010 at 4:16 am

    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
    greg@2ndQuadrant.com www.2ndQuadrant.us
  • Merlin Moncure at Apr 9, 2010 at 3:06 pm

    2010/4/9 Greg Smith <greg@2ndquadrant.com>:
    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 at Apr 9, 2010 at 3:13 pm
    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 <mmoncure@gmail.com>
    2010/4/9 Greg Smith <greg@2ndquadrant.com>:
    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 (pgsql-performance@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-performance
  • Greg Smith at Apr 9, 2010 at 2:32 am

    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
    greg@2ndQuadrant.com www.2ndQuadrant.us
  • Greg Smith at Apr 9, 2010 at 4:04 pm
    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
    greg@2ndQuadrant.com www.2ndQuadrant.us
  • Scott Marlowe at Apr 9, 2010 at 4:28 pm

    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 at Apr 9, 2010 at 4:30 pm

    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 at Apr 9, 2010 at 4:45 pm

    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 at Apr 10, 2010 at 6:54 pm

    2010/4/9 Scott Marlowe <scott.marlowe@gmail.com>:
    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 at Apr 14, 2010 at 1:22 pm
    <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 at Apr 14, 2010 at 1:30 pm
    That really sounds like hardware issue. The I/O causes the system to freeze
    basically.
    Happens sometimes on cheaper hardware.
  • Krzysztof Kardas at Apr 14, 2010 at 7:02 pm
    W dniu 14 kwietnia 2010 15:30 użytkownik Grzegorz Jaśkiewicz
    <gryzman@gmail.com> 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
  • Kevin Grittner at Apr 14, 2010 at 1:50 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedApr 8, '10 at 9:23a
activeApr 14, '10 at 7:02p
posts18
users9
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase