[Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?

Paul Robert Marino prmarino1 at gmail.com
Fri Dec 13 22:45:03 UTC 2013


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