Message-ID: <4934F174.4010406@emolecules.com>
Date: Tue, 02 Dec 2008 00:27:32 -0800
From: Craig James <craig_james@emolecules.com>
User-Agent: Thunderbird 2.0.0.18 (Macintosh/20081105)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Sort causes system to freeze
References: <b72893ad0811252007h33cf477dx54d5468705facaaa@mail.gmail.com>	
	<8763maouy9.fsf@oxford.xeocode.com>
	<b72893ad0811270925n2ea1a586ked1d3c1fdaf8dba8@mail.gmail.com>
	<4934CC58.1050309@emolecules.com>
In-Reply-To: <4934CC58.1050309@emolecules.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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