[Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
Paul Robert Marino
prmarino1 at gmail.com
Fri Dec 13 22:53:02 UTC 2013
Ill compare your configs with some of my note when I get into the
office on Sunday Im sure there is more that can be tweaked but I dont
have all the notes I wrote up on to tune this stuff handy right now.
Incidentally would it be helpful to nay one else if I created a Wiki
page on the subject of tuning PostgreSQL for Spacewalk.
On Fri, Dec 13, 2013 at 5:45 PM, Paul Robert Marino <prmarino1 at gmail.com> wrote:
> oh sorry I was reading it on my phone so the formatting was weird I misread it.
>
>
>
> On Fri, Dec 13, 2013 at 5:37 PM, Boyd, Robert
> <Robert.Boyd at peoplefluent.com> wrote:
>> That’s a configuration setting – not an error message.
>>
>>
>>
>> From: spacewalk-list-bounces at redhat.com
>> [mailto:spacewalk-list-bounces at redhat.com] On Behalf Of Paul Robert Marino
>> Sent: Friday, December 13, 2013 4:27 PM
>> To: spacewalk-list at redhat.com
>> Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to
>> stop it?
>>
>>
>>
>> Well that message at the end of the output you posted "processing past
>> damaged page headers." concerns me that means your database might have a
>> very serious problem. The safest thing to do would be to back up the
>> database via pgdump then drop and reload it. I would also do a fsck on the
>> volume if I were you.
>>
>>
>> -- Sent from my HP Pre3
>>
>>
>>
>> ________________________________
>>
>> On Dec 13, 2013 9:33, Boyd, Robert <Robert.Boyd at peoplefluent.com> wrote:
>>
>> I made the constraint exclusion change in the config options. However I’m
>> still seeing this query running 100% cpu:
>>
>>
>>
>> 16384 | spaceschema | 6216 | 16388 | spaceuser | SELECT DISTINCT
>> snv.server_id AS server_id, S.name, S.release, SA.name as arch,
>>
>> | f | 2013-12-10 19:42:33.097029-05 |
>> 2013-12-13 07:24:49.616359-05 | 2013-12-10 19:42:32.648538-05 | 127.0.0.1
>> | 36044
>>
>> : urn.user_id
>>
>> : FROM (
>>
>> : --
>>
>> : select
>> rhnChannelErrata.errata_id, rhnChannelErrata.channel_id,
>> rhnServerChannel.server_id, rhnErrataPackage.package_id
>>
>> : from
>> rhnChannelErrata, rhnErrataPackage, rhnChannelNewestPackage, rhnPackageEVR,
>>
>> :
>> rhnServerChannel, rhnServerPackage, rhnPackageUpgradeArchCompat
>>
>> : where
>> rhnChannelErrata.errata_id = rhnErrataPackage.errata_id
>>
>> : --
>>
>> : and
>> rhnChannelErrata.channel_id = rhnChannelNewestPackage.channel_id
>>
>> : and rhnErrataPackage.package_id =
>> rhnChannelNewestPackage.package_id
>>
>> : --
>>
>> : and
>> rhnChannelErrata.channel_id = rhnServerChannel.channel_id
>>
>> : and
>> rhnChannelNewestPackage.name_id = rhnServerPackage.name_id
>>
>> : and
>> rhnServerChannel.server_id = rhnServerPackage.server_id
>>
>> : --
>>
>> : and
>> rhnChannelNewestPackage.evr_id = rhnPackageEVR.id
>>
>> : --
>>
>> : and
>> rhnServerPackage.package_arch_id =
>> rhnPackageUpgradeArchCompat.package_arch_id
>>
>> : and
>> rhnPackageUpgradeArchCompat.pack
>>
>>
>>
>> Here is a dump of my configuration settings:
>>
>>
>>
>> spaceschema=# show all;
>>
>> name | setting |
>> description
>>
>>
>>
>> ---------------------------------+---------------------------------------+-----------------------------------------------------------------------------------------------------
>>
>> --------------------------
>>
>> add_missing_from | off |
>> Automatically adds missing table references to FROM clauses.
>>
>> allow_system_table_mods | off |
>> Allows modifications of the structure of system tables.
>>
>> archive_command | (disabled) |
>> Sets the shell command that will be called to archive a WAL file.
>>
>> archive_mode | off |
>> Allows archiving of WAL files using archive_command.
>>
>> archive_timeout | 0 |
>> Forces a switch to the next xlog file if a new file has not been started
>> within N seconds.
>>
>> array_nulls | on |
>> Enable input of NULL elements in arrays.
>>
>> authentication_timeout | 1min |
>> Sets the maximum allowed time to complete client authentication.
>>
>> autovacuum | on |
>> Starts the autovacuum subprocess.
>>
>> autovacuum_analyze_scale_factor | 0.1 |
>> Number of tuple inserts, updates or deletes prior to analyze as a fraction
>> of reltuples.
>>
>> autovacuum_analyze_threshold | 50 |
>> Minimum number of tuple inserts, updates or deletes prior to analyze.
>>
>> autovacuum_freeze_max_age | 200000000 |
>> Age at which to autovacuum a table to prevent transaction ID wraparound.
>>
>> autovacuum_max_workers | 3 |
>> Sets the maximum number of simultaneously running autovacuum worker
>> processes.
>>
>> autovacuum_naptime | 1min |
>> Time to sleep between autovacuum runs.
>>
>> autovacuum_vacuum_cost_delay | 20ms |
>> Vacuum cost delay in milliseconds, for autovacuum.
>>
>> autovacuum_vacuum_cost_limit | -1 |
>> Vacuum cost amount available before napping, for autovacuum.
>>
>> autovacuum_vacuum_scale_factor | 0.2 |
>> Number of tuple updates or deletes prior to vacuum as a fraction of
>> reltuples.
>>
>> autovacuum_vacuum_threshold | 50 |
>> Minimum number of tuple updates or deletes prior to vacuum.
>>
>> backslash_quote | safe_encoding |
>> Sets whether "\'" is allowed in string literals.
>>
>> bgwriter_delay | 200ms |
>> Background writer sleep time between rounds.
>>
>> bgwriter_lru_maxpages | 100 |
>> Background writer maximum number of LRU pages to flush per round.
>>
>> bgwriter_lru_multiplier | 2 |
>> Multiple of the average buffer usage to free per round.
>>
>> block_size | 8192 |
>> Shows the size of a disk block.
>>
>> bonjour_name | |
>> Sets the Bonjour broadcast service name.
>>
>> check_function_bodies | on |
>> Check function bodies during CREATE FUNCTION.
>>
>> checkpoint_completion_target | 0.7 |
>> Time spent flushing dirty buffers during checkpoint, as fraction of
>> checkpoint interval.
>>
>> checkpoint_segments | 8 |
>> Sets the maximum distance in log segments between automatic WAL checkpoints.
>>
>> checkpoint_timeout | 5min |
>> Sets the maximum time between automatic WAL checkpoints.
>>
>> checkpoint_warning | 30s |
>> Enables warnings if checkpoint segments are filled more frequently than
>> this.
>>
>> 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 before performing
>> commit_delay.
>>
>> config_file | /var/lib/pgsql/data/postgresql.conf |
>> Sets the server's main configuration file.
>>
>> constraint_exclusion | on |
>> Enables the planner to use constraints to optimize queries.
>>
>> cpu_index_tuple_cost | 0.005 |
>> Sets the planner's estimate of the cost of processing each index entry
>> during an index scan.
>>
>> cpu_operator_cost | 0.0025 |
>> Sets the planner's estimate of the cost of processing each operator or
>> function call.
>>
>> cpu_tuple_cost | 0.01 |
>> Sets the planner's estimate of the cost of processing each tuple (row).
>>
>> cursor_tuple_fraction | 0.1 |
>> Sets the planner's estimate of the fraction of a cursor's rows that will be
>> retrieved.
>>
>> custom_variable_classes | |
>> 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 | 1s |
>> Sets the time to wait on a lock before checking for deadlock.
>>
>> debug_assertions | off |
>> Turns on various assertion checks.
>>
>> debug_pretty_print | on |
>> Indents parse and plan tree displays.
>>
>> debug_print_parse | off |
>> Logs each query's parse tree.
>>
>> debug_print_plan | off |
>> Logs each query's execution plan.
>>
>> debug_print_rewritten | off |
>> Logs each query's rewritten parse tree.
>>
>> default_statistics_target | 400 |
>> Sets the default statistics target.
>>
>> default_tablespace | |
>> Sets the default tablespace to create tables and indexes in.
>>
>> default_text_search_config | pg_catalog.english |
>> Sets default text search configuration.
>>
>> default_transaction_isolation | read committed |
>> Sets the transaction isolation level of each new transaction.
>>
>> default_transaction_read_only | off |
>> Sets the default read-only status of new transactions.
>>
>> 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 | 2816MB |
>> Sets the planner's assumption about the size of the disk cache.
>>
>> effective_io_concurrency | 1 |
>> Number of simultaneous requests that can be handled efficiently by the disk
>> subsystem.
>>
>> enable_bitmapscan | on |
>> Enables the planner's use of bitmap-scan plans.
>>
>> enable_hashagg | on |
>> Enables the planner's use of hashed aggregation plans.
>>
>> 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 | on |
>> Warn about backslash escapes in ordinary string literals.
>>
>> external_pid_file | |
>> Writes the postmaster PID to the specified file.
>>
>> extra_float_digits | 0 |
>> Sets the number of digits displayed for floating-point values.
>>
>> from_collapse_limit | 10 |
>> 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 GEQO 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.
>>
>> gin_fuzzy_search_limit | 0 |
>> Sets the maximum allowed result for exact search by GIN.
>>
>> 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.
>>
>> ignore_system_indexes | off |
>> Disables reading from system indexes.
>>
>> integer_datetimes | on |
>> Datetimes are integer based.
>>
>> IntervalStyle | postgres |
>> Sets the display format for interval values.
>>
>> join_collapse_limit | 10 |
>> Sets the FROM-list size beyond which JOIN constructs are not flattened.
>>
>> krb_caseins_users | off |
>> Sets whether Kerberos and GSSAPI user names should be treated as
>> case-insensitive.
>>
>> 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 | en_US.UTF-8 |
>> Shows the collation order locale.
>>
>> lc_ctype | en_US.UTF-8 |
>> Shows the character classification and case conversion locale.
>>
>> lc_messages | en_US.UTF-8 |
>> Sets the language in which messages are displayed.
>>
>> lc_monetary | en_US.UTF-8 |
>> Sets the locale for formatting monetary amounts.
>>
>> lc_numeric | en_US.UTF-8 |
>> Sets the locale for formatting numbers.
>>
>> lc_time | en_US.UTF-8 |
>> Sets the locale for formatting date and time values.
>>
>> listen_addresses | localhost |
>> Sets the host name or IP address(es) to listen to.
>>
>> local_preload_libraries | |
>> Lists shared libraries to preload into each backend.
>>
>> log_autovacuum_min_duration | 0 |
>> Sets the minimum execution time above which autovacuum actions will be
>> logged.
>>
>> log_checkpoints | off |
>> Logs each checkpoint.
>>
>> 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 server 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 | |
>> Controls information prefixed to each log line.
>>
>> log_lock_waits | off |
>> Logs long lock waits.
>>
>> log_min_duration_statement | -1 |
>> Sets the minimum execution time above which statements will be logged.
>>
>> log_min_error_statement | error |
>> Causes all statements generating error at or above this level to be logged.
>>
>> log_min_messages | warning |
>> 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 | 1d |
>> Automatic log file rotation will occur after N minutes.
>>
>> log_rotation_size | 0 |
>> Automatic log file rotation will occur after N kilobytes.
>>
>> log_statement | none |
>> Sets the type of statements logged.
>>
>> log_statement_stats | off |
>> Writes cumulative performance statistics to the server log.
>>
>> log_temp_files | -1 |
>> Log the use of temporary files larger than this number of kilobytes.
>>
>> log_timezone | US/Eastern |
>> Sets the time zone to use in log messages.
>>
>> log_truncate_on_rotation | on |
>> Truncate existing log files of same name during log rotation.
>>
>> logging_collector | on |
>> Start a subprocess to capture stderr output and/or csvlogs into log files.
>>
>> maintenance_work_mem | 224MB |
>> Sets the maximum memory to be used for maintenance operations.
>>
>> max_connections | 600 |
>> Sets the maximum number of concurrent connections.
>>
>> max_files_per_process | 1000 |
>> Sets the maximum number of simultaneously open files for each server
>> process.
>>
>> 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 | 0 |
>> Sets the maximum number of simultaneously prepared transactions.
>>
>> max_stack_depth | 2MB |
>> Sets the maximum stack depth, in kilobytes.
>>
>> password_encryption | on |
>> Encrypt passwords.
>>
>> port | 5432 |
>> Sets the TCP port the server listens on.
>>
>> post_auth_delay | 0 |
>> Waits N seconds on connection startup after authentication.
>>
>> pre_auth_delay | 0 |
>> Waits N seconds on connection startup before authentication.
>>
>> random_page_cost | 4 |
>> Sets the planner's estimate of the cost of a nonsequentially fetched disk
>> page.
>>
>> regex_flavor | advanced |
>> Sets the regular expression "flavor".
>>
>> search_path | "$user",public |
>> Sets the schema search order for names that are not schema-qualified.
>>
>> segment_size | 1GB |
>> Shows the number of pages per disk file.
>>
>> seq_page_cost | 1 |
>> Sets the planner's estimate of the cost of a sequentially fetched disk page.
>>
>> server_encoding | UTF8 |
>> Sets the server (database) character set encoding.
>>
>> server_version | 8.4.18 |
>> Shows the server version.
>>
>> server_version_num | 80418 |
>> Shows the server version as an integer.
>>
>> session_replication_role | origin |
>> Sets the session's behavior for triggers and rewrite rules.
>>
>> shared_buffers | 896MB |
>> Sets the number of shared memory buffers used by the server.
>>
>> shared_preload_libraries | |
>> Lists shared libraries to preload into server.
>>
>> silent_mode | off |
>> Runs the server silently.
>>
>> sql_inheritance | on |
>> Causes subtables to be included by default in various commands.
>>
>> ssl | off |
>> Enables SSL connections.
>>
>> ssl_ciphers | ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH |
>> Sets the list of allowed SSL ciphers.
>>
>> ssl_renegotiation_limit | 512MB |
>> Set the amount of traffic to send and receive before renegotiating the
>> encryption keys.
>>
>> standard_conforming_strings | off |
>> Causes '...' strings to treat backslashes literally.
>>
>> statement_timeout | 0 |
>> Sets the maximum allowed duration of any statement.
>>
>> stats_temp_directory | pg_stat_tmp |
>> Writes temporary statistics files to the specified directory.
>>
>> superuser_reserved_connections | 3 |
>> Sets the number of connection slots reserved for superusers.
>>
>> synchronize_seqscans | on |
>> Enable synchronized sequential scans.
>>
>> synchronous_commit | on |
>> Sets immediate fsync at commit.
>>
>> syslog_facility | local0 |
>> Sets the syslog "facility" to be used when syslog enabled.
>>
>> syslog_ident | postgres |
>> Sets the program name used to identify PostgreSQL messages in syslog.
>>
>> tcp_keepalives_count | 0 |
>> Maximum number of TCP keepalive retransmits.
>>
>> tcp_keepalives_idle | 0 |
>> Time between issuing TCP keepalives.
>>
>> tcp_keepalives_interval | 0 |
>> Time between TCP keepalive retransmits.
>>
>> temp_buffers | 1024 |
>> Sets the maximum number of temporary buffers used by each session.
>>
>> temp_tablespaces | |
>> Sets the tablespace(s) to use for temporary tables and sort files.
>>
>> TimeZone | US/Eastern |
>> Sets the time zone for displaying and interpreting time stamps.
>>
>> timezone_abbreviations | Default |
>> Selects a file of time zone abbreviations.
>>
>> trace_notify | off |
>> Generates debugging output for LISTEN and NOTIFY.
>>
>> trace_sort | off |
>> Emit information about resource usage in sorting.
>>
>> track_activities | on |
>> Collects information about executing commands.
>>
>> track_activity_query_size | 1024 |
>> Sets the size reserved for pg_stat_activity.current_query, in bytes.
>>
>> track_counts | on |
>> Collects statistics on database activity.
>>
>> track_functions | none |
>> Collects function-level statistics on database activity.
>>
>> 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 | |
>> Sets the directory where the Unix-domain socket will be created.
>>
>> unix_socket_group | |
>> Sets the owning group of the Unix-domain socket.
>>
>> unix_socket_permissions | 511 |
>> Sets the access permissions of the Unix-domain socket.
>>
>> update_process_title | on |
>> Updates the process title to show the active SQL command.
>>
>> vacuum_cost_delay | 0 |
>> 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 cache.
>>
>> vacuum_freeze_min_age | 50000000 |
>> Minimum age at which VACUUM should freeze a table row.
>>
>> vacuum_freeze_table_age | 150000000 |
>> Age at which VACUUM should scan whole table to freeze tuples.
>>
>> wal_block_size | 8192 |
>> Shows the block size in the write ahead log.
>>
>> wal_buffers | 4MB |
>> Sets the number of disk-page buffers in shared memory for WAL.
>>
>> wal_segment_size | 16MB |
>> Shows the number of pages per write ahead log segment.
>>
>> wal_sync_method | fdatasync |
>> Selects the method used for forcing WAL updates to disk.
>>
>> wal_writer_delay | 200ms |
>> WAL writer sleep time between WAL flushes.
>>
>> work_mem | 20MB |
>> Sets the maximum memory to be used for query workspaces.
>>
>> xmlbinary | base64 |
>> Sets how binary values are to be encoded in XML.
>>
>> xmloption | content |
>> Sets whether XML data in implicit parsing and serialization operations is to
>> be considered as docume
>>
>> nts or content fragments.
>>
>> zero_damaged_pages | off |
>> Continues processing past damaged page headers.
>>
>> (195 rows)
>>
>>
>>
>> Please advise if there’s something I missed or could give more of a bump
>> than I did already.
>>
>>
>>
>> From: spacewalk-list-bounces at redhat.com
>> [mailto:spacewalk-list-bounces at redhat.com] On Behalf Of Paul Robert Marino
>> Sent: Wednesday, December 11, 2013 4:18 PM
>> To: spacewalk-list at redhat.com
>> Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to
>> stop it?
>>
>>
>>
>> What about the config options I think the constraint exclusion would help
>> you because it effectively allows the planner attempt to do the same thing
>> as the patch.
>>
>> -- Sent from my HP Pre3
>>
>>
>>
>> ________________________________
>>
>> On Dec 11, 2013 16:03, Boyd, Robert <Robert.Boyd at peoplefluent.com> wrote:
>>
>> Thank you for the suggestions.
>>
>> I had previously done periodic pgtune passes. I managed to do some tuning of
>> config settings as you suggested for postgres and get a pass through
>> vacuuming and reindexing. The performance of the server seems to be a bit
>> crisper. However it didn't eliminate the problem with a process going CPU
>> bound. I'm still waiting to hear back on my question about when I might be
>> able to apply the updates that Michael Mraka pointed to.
>>
>> Robert
>>
>> -----Original Message-----
>> From: spacewalk-list-bounces at redhat.com
>> [mailto:spacewalk-list-bounces at redhat.com] On Behalf Of Paul Robert Marino
>> Sent: Monday, December 09, 2013 4:25 PM
>> To: spacewalk-list at redhat.com
>> Subject: Re: [Spacewalk-list] CPU bound postgresql process ? Why and how to
>> stop it?
>>
>> sorry for the delayed responce on this I thought I sent it but its been
>> sitting in my drafts folder
>>
>> On Wed, Dec 4, 2013 at 11:21 AM, Boyd, Robert <Robert.Boyd at peoplefluent.com>
>> wrote:
>>> I have several hundred servers in spacewalk. How much time should I allow
>>> for a window to run this db maintenance?
>> that depends on how fragmented the tables and free space in the tables are
>> which is a direct correlation to how many changes have been made since
>> autovacuume has cleaned it up.
>> my facilities are 24x7 365 mission critical but on the weekend no changes
>> are allowed to any systems so I have a cron job that does it at midnight on
>> the first Saturday of each month and I don't really time it.
>>
>> if its the first time you are doing it I would schedule 4 hours.
>> 1 hour if you skip the reindex and second vacuum analyze. the reindex is
>> whats really time consuming and not really required.
>>
>>
>>>
>>> And how does this relate to the bugzilla that Michael mentioned?
>>
>> Yes and no they are and are not related
>> Not doing the maintenance I mentioned would significantly exacerbate the
>> issue in the bugzilla ticket.
>> this is because it seems to be a planner issue and the planer utilizes the
>> statistics created by the analyze process to figure out how to handle
>> complex queries like this.
>>
>> additionally you could more quickly help it if you tune the planner for
>> example in ~postgres/data/postgresql.conf adjusting the size of
>> effective_cache_size can be extremely helpful.
>>
>> Also turning on constraint_exclusion in the same file is helpfull with
>> spacewalk! it means the planner takes longer but can automatically figure
>> out things to exclude in subqueries and joins based on constraints in the
>> other parts of the overall query so the resulting query can be faster.
>> by default constraint_exclusion is turned off in PostgreSQL because if you
>> don't do a lot of joins and conditional sub queries it will hurt you
>> performance but in the case of spacewalk its a significant help.
>>
>> the query planner in PostgreSQL is a complex subject but the major things to
>> look at for spacewalk tuning are
>>
>> shared_buffers (increase a lot)
>> work_mem (increase a lot if possible)
>> effective_cache_size (definitely increase a lot) default_statistics_target
>> (increase a little or a lot but the down size is it makes analyze take
>> longer the more you increase it and there is a point of diminishing returns)
>> constraint_exclusion (enable this) from_collapse_limit (increase slightly)
>> join_collapse_limit (increase slightly)
>>
>> Also you you want to speed up vacuum, autovacuum, and reindex, and analyze
>> operations increasing the maintenance_work_mem is helpful for that but keep
>> in mind that autovacuums may use that during normal database operation so be
>> careful not to make it too high so for example on my production boxes I have
>> it set to 1GB
>>
>>>
>>> Michael, how long before the spacewalk-java update will move from nightly
>>> to production release?
>>>
>>> Thank you both for your assistance!
>>>
>>> Robert
>>
>>
>> _______________________________________________
>> Spacewalk-list mailing list
>> Spacewalk-list at redhat.com
>> https://www.redhat.com/mailman/listinfo/spacewalk-list
>>
>>
>> _______________________________________________
>> Spacewalk-list mailing list
>> Spacewalk-list at redhat.com
>> https://www.redhat.com/mailman/listinfo/spacewalk-list
More information about the Spacewalk-list
mailing list