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 <[email protected]> wrote: > That’s a configuration setting – not an error message. > > > > From: [email protected] > [mailto:[email protected]] On Behalf Of Paul Robert Marino > Sent: Friday, December 13, 2013 4:27 PM > To: [email protected] > 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 <[email protected]> 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: [email protected] > [mailto:[email protected]] On Behalf Of Paul Robert Marino > Sent: Wednesday, December 11, 2013 4:18 PM > To: [email protected] > 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 <[email protected]> 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: [email protected] > [mailto:[email protected]] On Behalf Of Paul Robert Marino > Sent: Monday, December 09, 2013 4:25 PM > To: [email protected] > 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 <[email protected]> > 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 > [email protected] > https://www.redhat.com/mailman/listinfo/spacewalk-list > > > _______________________________________________ > Spacewalk-list mailing list > [email protected] > https://www.redhat.com/mailman/listinfo/spacewalk-list _______________________________________________ Spacewalk-list mailing list [email protected] https://www.redhat.com/mailman/listinfo/spacewalk-list
