Re: Sort causes system to freeze

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

 



Craig James wrote:
Maybe this is an obviously dumb thing to do,

... and it was.  I answered my own question: The problem came from using psql(1) to do something I should have done with pg_dump.

but it looked reasonable to me. The problem is, the seemingly simple sort below causes a fairly powerful computer to completely freeze for 5-10 minutes. During the sort, you can't login, you can't use any shell sessions you already have open, the Apache server barely works, and even if you do "nice -20 top" before you start the sort, the top(1) command comes to a halt while the sort is proceeding! As nearly as I can tell, the sort operation is causing a swap storm of some sort -- nothing else in my many years of UNIX/Linux experience can cause a "nice -20" process to freeze.

The sort operation never finishes -- it's always killed by the system. Once it dies, everything returns to normal.

This is 8.3.0. (Yes, I'll upgrade soon.) Is this a known bug, or do I have to rewrite this query somehow? Maybe add indexes to all four columns being sorted?

Thanks!
Craig


=> explain select * from plus order by supplier_id, compound_id, units, price; QUERY PLAN -----------------------------------------------------------------------
Sort  (cost=5517200.48..5587870.73 rows=28268100 width=65)
  Sort Key: supplier_id, compound_id, units, price
  ->  Seq Scan on plus  (cost=0.00..859211.00 rows=28268100 width=65)

=> \d plus       Table "emol_warehouse_1.plus"
Column | Type | Modifiers ---------------+---------------+----------- supplier_id | integer | supplier_name | text | compound_id | text | amount | text | units | text | price | numeric(12,2) | currency | text | description | text | sku | text | Indexes:
   "i_plus_compound_id" btree (supplier_id, compound_id)
   "i_plus_supplier_id" btree (supplier_id)


max_connections = 1000
shared_buffers = 2000MB
work_mem = 256MB
max_fsm_pages = 1000000
max_fsm_relations = 5000
synchronous_commit = off
#wal_sync_method = fdatasync
wal_buffers = 256kB
checkpoint_segments = 30
effective_cache_size = 4GB

Machine: Dell, 8x64-bit CPUs, 8GB ram, Perc6i battery-backed RAID controller, 8 disks as RAID10

Craig


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

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

  Powered by Linux