pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSort causes system to freeze
5+ messages / 4 participants
[nested] [flat]
* Sort causes system to freeze
@ 2008-12-02 05:49 Craig James <craig_james@emolecules.com>
0 siblings, 3 replies; 5+ messages in thread
From: Craig James @ 2008-12-02 05:49 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sort causes system to freeze
@ 2008-12-02 08:27 Craig James <craig_james@emolecules.com>
parent: Craig James <craig_james@emolecules.com>
2 siblings, 0 replies; 5+ messages in thread
From: Craig James @ 2008-12-02 08:27 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sort causes system to freeze
@ 2008-12-02 09:59 Richard Huxton <dev@archonet.com>
parent: Craig James <craig_james@emolecules.com>
2 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2008-12-02 09:59 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Don't reply to another message when starting a new thread. People will
miss your message.
Craig James wrote:
> Maybe this is an obviously dumb thing to do, but it looked reasonable to
> me.
Looks reasonable here too - except I'm not sure what I'd do with 2
million rows of sorted table in my console. I'm guessing you're piping
the output into something.
> 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.
Nothing should cause that to your machine. I've never seen "top" just
freeze unless you set up some sort of fork-bomb and ramp the load up so
fast it can't cope. Oh, and nice-ing the client isn't going to do
anything to the backend actually doing the sorting.
> The sort operation never finishes -- it's always killed by the system.
> Once it dies, everything returns to normal.
You're running out of memory then. It'll be the out-of-memory killer
(assuming you're on Linux).
> This is 8.3.0. (Yes, I'll upgrade soon.)
Make "soon" more urgent than it has been up to now - no point in risking
all your data to some already fixed bug is there? Unless you've been
carefully tracking the release notes and have established that there's
no need in your precise scenario.
> Is this a known bug, or do I
> have to rewrite this query somehow? Maybe add indexes to all four
> columns being sorted?
Indexes won't necessarily help if you're sorting the whole table. Maybe
if you had one on all four columns.
> => explain select * from plus order by supplier_id, compound_id, units,
> price;
> max_connections = 1000
> shared_buffers = 2000MB
> work_mem = 256MB
So can you support (1000 * 256 * 2) + 2000 MB of RAM?
> effective_cache_size = 4GB
...while leaving 4GB free for disk caching?
> Machine: Dell, 8x64-bit CPUs, 8GB ram, Perc6i battery-backed RAID
> controller, 8 disks as RAID10
It appears not. Remember that work_mem is not only per-connection, a
single query can use multiples of it (hence the *2 above). If you
genuinely have a lot of connections I'd drop it down to (say) 4MB to
make sure you don't swap on a regular basis (should probably be even
lower to be truly safe).
Then, for the odd case when you need a large value, issue a SET work_mem
before the query.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sort causes system to freeze
@ 2008-12-02 10:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig James <craig_james@emolecules.com>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2008-12-02 10:34 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James <craig_james@emolecules.com> writes:
> 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.
trace_sort output might be informative.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sort causes system to freeze
@ 2008-12-02 11:54 PFC <lists@peufeu.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 5+ messages in thread
From: PFC @ 2008-12-02 11:54 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
>> Maybe this is an obviously dumb thing to do, but it looked reasonable to
>> me.
>
> Looks reasonable here too - except I'm not sure what I'd do with 2
> million rows of sorted table in my console. I'm guessing you're piping
> the output into something.
Probably it's psql that is choking from buffering the rows.
If you want to fetch that huge amount of data into a user application, a
CURSOR is the best way to do so.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-12-02 11:54 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-02 05:49 Sort causes system to freeze Craig James <craig_james@emolecules.com>
2008-12-02 08:27 ` Craig James <craig_james@emolecules.com>
2008-12-02 09:59 ` Richard Huxton <dev@archonet.com>
2008-12-02 11:54 ` PFC <lists@peufeu.com>
2008-12-02 10:34 ` Tom Lane <tgl@sss.pgh.pa.us>
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