[Spacewalk-list] CPU bound postgresql process ? Why and how to stop it?
Boyd, Robert
Robert.Boyd at peoplefluent.com
Fri Dec 13 22:37:09 UTC 2013
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<mailto: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> [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<mailto: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<mailto: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> [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<mailto: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<mailto: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<mailto:Spacewalk-list at redhat.com>
https://www.redhat.com/mailman/listinfo/spacewalk-list
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20131213/cd9615b2/attachment.htm>
More information about the Spacewalk-list
mailing list