manifoldcf-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Karl Wright <daddy...@gmail.com>
Subject Re: Timeout problems with web crawling
Date Tue, 23 Apr 2013 14:22:00 GMT
Hi Erlend,

If you have access to psql and can connect to the production database, I'd
like to explore whether the relatively recent change I made for MySQL is in
fact breaking PostgreSQL stuffer thread queries.

First, read this page:
http://www.postgresql.org/docs/current/static/indexes-ordering.html

What we're trying to achieve is that the planner use the index whose first
column is docpriority.  The way we will need to determine that is to open
up psql, and "explain" the query in question, and then modify the query to
see if it begins to plan correctly.

In psql, you will need to assess the query in question.  To do that,
execute the following:

EXPLAIN SELECT t0.id,t0.jobid,t0.dochash,t0.docid,t0.status,t0.failtime,t0.failcount,t0.priorityset
FROM jobqueue t0  WHERE t0.status IN ('P','G') AND t0.checkaction='R' AND
t0.checktime<=1366628406182 AND
    EXISTS(SELECT 'x' FROM jobs t1 WHERE t1.status IN ('A','a') AND
t1.id=t0.jobid
AND t1.priority=5) AND
    NOT EXISTS(SELECT 'x' FROM jobqueue t2 WHERE t2.dochash=t0.dochash AND
t2.status IN ('A','F','a','f','D','d') AND t2.jobid!=t0.jobid) AND
    NOT EXISTS(SELECT 'x' FROM prereqevents t3,events t4 WHERE t0.id=t3.owner
AND t3.eventname=t4.name) ORDER BY t0.docpriority ASC LIMIT 100;


The production job MUST be running or it is a meaningless exercise.

What you want to see is that the outermost action is NOT a sort; instead,
you want an

Index Scan using i1362584563121

If you get something else, try changing the ORDER BY clause to include
NULLS LAST or NULLS FIRST to see if that
changes anything.  If not, I may ask you to execute a sample query or
two and tell me what it returns, so I can assess

 whether the data is messed up in some way.

Karl






On Tue, Apr 23, 2013 at 10:03 AM, Erlend GarĂ¥sen <e.f.garasen@usit.uio.no>wrote:

> On 23.04.13 15.30, Karl Wright wrote:
>
>  What version of Postgresql is this?  Given the same Postgresql version,
>>
>
> 9.1.9
>
> "allow_system_table_mods";"**off"
> "application_name";"pgAdmin III - Query Tool"
> "archive_command";"/local/opt/**pgsql-9.1/bin/pgclarchive_wal.**sh -P %p
> -F %f -S dbpg-search"
> "archive_mode";"on"
> "archive_timeout";"0"
> "array_nulls";"on"
> "authentication_timeout";"**1min"
> "autovacuum";"on"
> "autovacuum_analyze_scale_**factor";"0.1"
> "autovacuum_analyze_threshold"**;"50"
> "autovacuum_freeze_max_age";"**200000000"
> "autovacuum_max_workers";"3"
> "autovacuum_naptime";"1min"
> "autovacuum_vacuum_cost_delay"**;"20ms"
> "autovacuum_vacuum_cost_limit"**;"-1"
> "autovacuum_vacuum_scale_**factor";"0.2"
> "autovacuum_vacuum_threshold";**"50"
> "backslash_quote";"safe_**encoding"
> "bgwriter_delay";"200ms"
> "bgwriter_lru_maxpages";"100"
> "bgwriter_lru_multiplier";"2"
> "block_size";"8192"
> "bonjour";"off"
> "bonjour_name";""
> "bytea_output";"escape"
> "check_function_bodies";"on"
> "checkpoint_completion_target"**;"0.5"
> "checkpoint_segments";"128"
> "checkpoint_timeout";"5min"
> "checkpoint_warning";"30s"
> "client_encoding";"UNICODE"
> "client_min_messages";"notice"
> "commit_delay";"0"
> "commit_siblings";"5"
> "constraint_exclusion";"**partition"
> "cpu_index_tuple_cost";"0.005"
> "cpu_operator_cost";"0.0025"
> "cpu_tuple_cost";"0.01"
> "cursor_tuple_fraction";"0.1"
> "custom_variable_classes";""
> "DateStyle";"ISO, MDY"
> "db_user_namespace";"off"
> "deadlock_timeout";"1s"
> "debug_assertions";"off"
> "debug_pretty_print";"on"
> "debug_print_parse";"off"
> "debug_print_plan";"off"
> "debug_print_rewritten";"off"
> "default_statistics_target";"**100"
> "default_tablespace";""
> "default_text_search_config";"**pg_catalog.english"
> "default_transaction_**deferrable";"off"
> "default_transaction_**isolation";"read committed"
> "default_transaction_read_**only";"off"
> "default_with_oids";"off"
> "effective_cache_size";"**16093MB"
> "effective_io_concurrency";"1"
> "enable_bitmapscan";"on"
> "enable_hashagg";"on"
> "enable_hashjoin";"on"
> "enable_indexscan";"on"
> "enable_material";"on"
> "enable_mergejoin";"on"
> "enable_nestloop";"on"
> "enable_seqscan";"on"
> "enable_sort";"on"
> "enable_tidscan";"on"
> "escape_string_warning";"on"
> "exit_on_error";"off"
> "extra_float_digits";"0"
> "from_collapse_limit";"8"
> "fsync";"on"
> "full_page_writes";"on"
> "geqo";"on"
> "geqo_effort";"5"
> "geqo_generations";"0"
> "geqo_pool_size";"0"
> "geqo_seed";"0"
> "geqo_selection_bias";"2"
> "geqo_threshold";"12"
> "gin_fuzzy_search_limit";"0"
> "hot_standby";"off"
> "hot_standby_feedback";"off"
> "ignore_system_indexes";"off"
> "integer_datetimes";"on"
> "IntervalStyle";"postgres"
> "join_collapse_limit";"8"
> "krb_caseins_users";"off"
> "krb_srvname";"postgres"
> "lc_collate";"C"
> "lc_ctype";"C"
> "lc_messages";"C"
> "lc_monetary";"C"
> "lc_numeric";"C"
> "lc_time";"C"
> "listen_addresses";"dbpg-**search.uio.no <http://dbpg-search.uio.no>"
> "lo_compat_privileges";"off"
> "local_preload_libraries";""
> "log_autovacuum_min_duration";**"-1"
> "log_checkpoints";"off"
> "log_connections";"off"
> "log_destination";"stderr"
> "log_disconnections";"off"
> "log_duration";"off"
> "log_error_verbosity";"**default"
> "log_executor_stats";"off"
> "log_file_mode";"0600"
> "log_hostname";"off"
> "log_line_prefix";"[%m] [dbpg-search] [%d:%u] [%r] [%p] [%x] "
> "log_lock_waits";"off"
> "log_min_duration_statement";"**-1"
> "log_min_error_statement";"**info"
> "log_min_messages";"notice"
> "log_parser_stats";"off"
> "log_planner_stats";"off"
> "log_rotation_age";"0"
> "log_rotation_size";"0"
> "log_statement";"none"
> "log_statement_stats";"off"
> "log_temp_files";"-1"
> "log_timezone";"Europe/Oslo"
> "log_truncate_on_rotation";"**on"
> "logging_collector";"on"
> "maintenance_work_mem";"128MB"
> "max_connections";"600"
> "max_files_per_process";"1000"
> "max_function_args";"100"
> "max_identifier_length";"63"
> "max_index_keys";"32"
> "max_locks_per_transaction";"**64"
> "max_pred_locks_per_**transaction";"64"
> "max_prepared_transactions";"**0"
> "max_stack_depth";"4MB"
> "max_standby_archive_delay";"**30s"
> "max_standby_streaming_delay";**"30s"
> "max_wal_senders";"0"
> "password_encryption";"on"
> "port";"5432"
> "post_auth_delay";"0"
> "pre_auth_delay";"0"
> "quote_all_identifiers";"off"
> "random_page_cost";"2"
> "replication_timeout";"1min"
> "restart_after_crash";"on"
> "search_path";""$user",public"
> "segment_size";"1GB"
> "seq_page_cost";"1"
> "server_encoding";"UTF8"
> "server_version";"9.1.9"
> "server_version_num";"90109"
> "session_replication_role";"**origin"
> "shared_buffers";"384MB"
> "silent_mode";"off"
> "sql_inheritance";"on"
> "ssl";"on"
> "ssl_renegotiation_limit";"**512MB"
> "standard_conforming_strings";**"off"
> "statement_timeout";"0"
> "superuser_reserved_**connections";"3"
> "synchronize_seqscans";"on"
> "synchronous_commit";"on"
> "synchronous_standby_names";""
> "syslog_facility";"local0"
> "syslog_ident";"postgres"
> "tcp_keepalives_count";"9"
> "tcp_keepalives_idle";"7200"
> "tcp_keepalives_interval";"75"
> "temp_buffers";"8MB"
> "temp_tablespaces";""
> "TimeZone";"Europe/Oslo"
> "timezone_abbreviations";"**Default"
> "trace_notify";"off"
> "trace_recovery_messages";"**log"
> "trace_sort";"off"
> "track_activities";"on"
> "track_activity_query_size";"**1024"
> "track_counts";"on"
> "track_functions";"none"
> "transaction_deferrable";"off"
> "transaction_isolation";"read committed"
> "transaction_read_only";"off"
> "transform_null_equals";"off"
> "unix_socket_group";""
> "unix_socket_permissions";"**0700"
> "update_process_title";"on"
> "vacuum_cost_delay";"0"
> "vacuum_cost_limit";"200"
> "vacuum_cost_page_dirty";"20"
> "vacuum_cost_page_hit";"1"
> "vacuum_cost_page_miss";"10"
> "vacuum_defer_cleanup_age";"0"
> "vacuum_freeze_min_age";"**50000000"
> "vacuum_freeze_table_age";"**150000000"
> "wal_block_size";"8192"
> "wal_buffers";"12MB"
> "wal_keep_segments";"0"
> "wal_level";"archive"
> "wal_receiver_status_interval"**;"10s"
> "wal_segment_size";"16MB"
> "wal_sender_delay";"1s"
> "wal_sync_method";"fdatasync"
> "wal_writer_delay";"200ms"
> "work_mem";"16MB"
> "xmlbinary";"base64"
> "xmloption";"content"
> "zero_damaged_pages";"off"
>
>
>
> --
> Erlend GarĂ¥sen
> Center for Information Technology Services
> University of Oslo
> P.O. Box 1086 Blindern, N-0317 OSLO, Norway
> Ph: (+47) 22840193, Fax: (+47) 22852970, Mobile: (+47) 91380968, VIP:
> 31050
>

Mime
View raw message