Re: Simple select hangs while CPU close to 100% - Mailing list pgsql-performance
From | Bill Moran |
---|---|
Subject | Re: Simple select hangs while CPU close to 100% |
Date | |
Msg-id | 20070725135920.b3bb41e4.wmoran@collaborativefusion.com Whole thread Raw |
In response to | Re: Simple select hangs while CPU close to 100% ("Jozsef Szalay" <jszalay@storediq.com>) |
List | pgsql-performance |
In response to "Jozsef Szalay" <jszalay@storediq.com>: > Hi Pavel, > > Yes I did vacuum. In fact the only way to "fix" this problem is > executing a "full" vacuum. The plain vacuum did not help. Based on the information, I would expect that this problem is the result of improper PG tuning, or inadequate server sizing (RAM, etc). A table changing 100,000 rows shouldn't cause enough bloat to hurt count(*)'s performance significantly, unless something else is wrong. Some quick piddling around shows that tables with over a million rows can count(*) the whole table in about 1/2 second on a system 2G of RAM. A table with 13 mil takes a min and a half. We haven't specifically tuned this server for count(*) performance, as it's not a priority for this database, so I expect the performance drop for the 13 mil database is a result of exhausting shared_buffers and hitting the disks. > -----Original Message----- > From: Pavel Stehule [mailto:pavel.stehule@gmail.com] > Sent: Sunday, July 22, 2007 10:53 AM > To: Jozsef Szalay > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Simple select hangs while CPU close to 100% > > Hello > > did you vacuum? > > It's good technique do vacuum table after remove bigger number of rows. > > Regards > Pavel Stehule > > 2007/7/22, Jozsef Szalay <jszalay@storediq.com>: > > > > > > > > > > I'm having this very disturbing problem. I got a table with about > 100,000 > > rows in it. Our software deletes the majority of these rows and then > bulk > > loads another 100,000 rows into the same table. All this is happening > within > > a single transaction. I then perform a simple "select count(*) from > ..." > > statement that never returns. In the mean time, the backend Postgres > process > > is taking close to 100% of the CPU. The hang-up does not always happen > on > > the same statement but eventually it happens 2 out of 3 times. If I > dump and > > then restore the schema where this table resides the problem is gone > until > > the next time we run through the whole process of deleting, loading > and > > querying the table. > > > > > > > > There is no other activity in the database. All requested locks are > granted. > > > > > > > > Has anyone seen similar behavior? > > > > > > > > Some details: > > > > > > > > Postgres v 8.1.2 > > > > Linux Fedora 3 > > > > > > > > shared_buffers = 65536 > > > > temp_buffers = 32768 > > > > work_mem = 131072 > > > > maintenance_work_mem = 131072 > > > > max_stack_depth = 8192 > > > > max_fsm_pages = 40000 > > > > wal_buffers = 16 > > > > checkpoint_segments = 16 > > > > > > > > > > > > top reports > > > > > > > > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND > > > > 19478 postgres 25 0 740m 721m 536m R 99.7 4.4 609:41.16 > postmaster > > > > > > > > ps -ef | grep postgres reports > > > > > > > > postgres 19478 8061 99 00:11 ? 10:13:03 postgres: user dbase > [local] > > SELECT > > > > > > > > strace -p 19478 > > > > no system calls reported > > > > > > > > > > > > Thanks for the help! > > > > Jozsef > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > > > > > > -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023 **************************************************************** IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. ****************************************************************
pgsql-performance by date: