From: Craig James <craig_james@emolecules.com>
To: pgsql-performance@postgresql.org
Subject: Sort causes system to freeze
Date: Mon, 01 Dec 2008 21:49:12 -0800
Message-ID: <4934CC58.1050309@emolecules.com> (raw)
In-Reply-To: <b72893ad0811270925n2ea1a586ked1d3c1fdaf8dba8@mail.gmail.com>
References: <b72893ad0811252007h33cf477dx54d5468705facaaa@mail.gmail.com>
<8763maouy9.fsf@oxford.xeocode.com>
<b72893ad0811270925n2ea1a586ked1d3c1fdaf8dba8@mail.gmail.com>
Maybe this is an obviously dumb thing to do, 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig_james@emolecules.com
Subject: Re: Sort causes system to freeze
In-Reply-To: <4934CC58.1050309@emolecules.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox