Postgres Query Plan Live Lock

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



We have been running into a (live lock?) issue on our production Postgres instance causing queries referencing a particular table to become extremely slow and our application to lock up.

This tends to occur on a particular table that gets a lot of queries against it after a large number of deletes. When this happens, the following symptoms occur when queries referencing that table are run (even it we stop the deleting):

SELECT * FROM table_name LIMIT 10;  -- takes ~45 seconds to complete
EXPLAIN SELECT * FROM table_name LIMIT 10;  -- takes ~45 seconds to complete the explain query, the query plan looks reasonable
EXPLAIN SELECT * FROM table_name LIMIT 10;  -- takes ~45 seconds to complete the explain analyze query, query plan looks reasonable, timing stats says query took sub millisecond time to complete

SELECT * FROM another_table LIMIT 10; -- takes sub millisecond time
EXPLAIN * FROM another_table LIMIT 10; -- takes sub millisecond time, query plan looks reasonable

This behavior only stops and the queries go back to taking sub millisecond time if we take the application issuing the SELECTs offline and wait for the active queries to finish (or terminate them).

There is not a particularly large load on the database machine at the time, neither are there a particularly large number of wal logs being written (although there is a burst of wal log writes immediately after the queue is cleared).

table_name stats:
~ 400,000,000 rows
We are deleting 10,000,000s of rows in 100,000 row increments over a few days time prior/during this slowdown.
Simultaneously a web app is querying this table continuously.

table_name has 4 btree indexes on it (one of which is set to CLUSTER) and one foreign key constraint.

The obvious workaround is to not delete so much data on the table on our production database, but we would like to figure out why Postgres is live locking this table. Do you have any ideas why this is happening and how to prevent it while still doing mass deletes on the table?

-------------------------------------------------------------------------

System information:

Postgres Version - PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
OS - Ubuntu 12.04 LTS

Autovacuum is on.

--------------------------------------------------------------------------

SELECT name, current_setting(name), source
  FROM pg_settings
  WHERE source NOT IN ('default');
             name             |             current_setting              |        source        
------------------------------+------------------------------------------+----------------------
 application_name             | psql                                     | client
 archive_command              | /bin/true                                | configuration file
 archive_mode                 | on                                       | configuration file
 bytea_output                 | escape                                   | configuration file
 checkpoint_completion_target | 0.9                                      | configuration file
 checkpoint_segments          | 24                                       | configuration file
 client_encoding              | UTF8                                     | session
 DateStyle                    | ISO, MDY                                 | configuration file
 default_text_search_config   | pg_catalog.english                       | configuration file
 effective_cache_size         | 54GB                                     | configuration file
 effective_io_concurrency     | 2                                        | configuration file
 listen_addresses             | *                                        | configuration file
 log_checkpoints              | on                                       | configuration file
 log_connections              | on                                       | configuration file
 log_disconnections           | on                                       | configuration file
 log_hostname                 | on                                       | configuration file
 log_line_prefix              | %t                                       | configuration file
 logging_collector            | on                                       | configuration file
 maintenance_work_mem         | 256MB                                    | configuration file
 max_connections              | 600                                      | configuration file
 max_stack_depth              | 2MB                                      | environment variable
 max_wal_senders              | 3                                        | configuration file
 random_page_cost             | 1.75                                     | configuration file
 server_encoding              | UTF8                                     | override
 shared_buffers               | 12GB                                     | configuration file
 synchronous_commit           | off                                      | configuration file
 tcp_keepalives_idle          | 180                                      | configuration file
 track_activity_query_size    | 8192                                     | configuration file
 transaction_deferrable       | off                                      | override
 transaction_isolation        | read committed                           | override
 transaction_read_only        | off                                      | override
 vacuum_freeze_min_age        | 20000000                                 | configuration file
 vacuum_freeze_table_age      | 800000000                                | configuration file
 wal_buffers                  | 16MB                                     | override
 wal_keep_segments            | 16384                                    | configuration file
 wal_level                    | hot_standby                              | configuration file
 wal_writer_delay             | 330ms                                    | configuration file
 work_mem                     | 512MB                                    | configuration file


--
Thank You,
Pweaver (pweaver@xxxxxxxxxxx)

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux