Thread: PostgreSQL with Zabbix - problem of newbe
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.
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.
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.
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
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
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 <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
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
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
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
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:you're right! iirc it was changed at the last minute...
>>
>> 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.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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
On Fri, Apr 9, 2010 at 10:03 AM, Greg Smith <greg@2ndquadrant.com> 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.
On Fri, Apr 9, 2010 at 12:03 PM, Greg Smith <greg@2ndquadrant.com> 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
On Fri, Apr 9, 2010 at 10:30 AM, Merlin Moncure <mmoncure@gmail.com> wrote: > On Fri, Apr 9, 2010 at 12:03 PM, Greg Smith <greg@2ndquadrant.com> 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.
2010/4/9 Scott Marlowe <scott.marlowe@gmail.com>: > On Fri, Apr 9, 2010 at 10:30 AM, Merlin Moncure <mmoncure@gmail.com> wrote: >> On Fri, Apr 9, 2010 at 12:03 PM, Greg Smith <greg@2ndquadrant.com> 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
<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
That really sounds like hardware issue. The I/O causes the system to freeze basically.
Happens sometimes on cheaper hardware.
Krzysztof Kardas <krzychk2@gmail.com> 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
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