Re: Simple select hangs while CPU close to 100%
От | Pavel Stehule |
---|---|
Тема | Re: Simple select hangs while CPU close to 100% |
Дата | |
Msg-id | 162867790707251206m74773feby92f224b2de8040f5@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Simple select hangs while CPU close to 100% ("Jozsef Szalay" <jszalay@storediq.com>) |
Список | pgsql-performance |
hello show me, please, output from vacuum verbose table Pavel 2007/7/25, Jozsef Szalay <jszalay@storediq.com>: > Our application is such that any update to the database is done by a > single session in a batch process using bulk load. The frequency of > these usually larger scale updates is variable but an update runs every > 2-3 days on average. > > Originally a plain VACUUM ANALYZE was executed on every affected table > after every load. > > VACUUM FULL ANALYZE is scheduled to run on a weekly basis. > > I do understand the need for vacuuming. Nevertheless I expect Postgres > to return data eventually even if I do not vacuum. In my case, the > simple SELECT COUNT(*) FROM table; statement on a table that had around > 100K "live" rows has not returned the result for more than 6 hours after > which I manually killed it. > > Jozsef > > > -----Original Message----- > From: Bill Moran [mailto:wmoran@collaborativefusion.com] > Sent: Wednesday, July 25, 2007 1:12 PM > To: Jozsef Szalay > Cc: Pavel Stehule; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Simple select hangs while CPU close to 100% > > 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. > > I read over my previous reply and picked up on something else ... > > What is your vacuum _policy_? i.e. how often do you vacuum/analyze? > The fact that you had to do a vacuum full to get things back under > control tends to suggest that your current vacuum schedule is not > aggressive enough. > > An explicit vacuum of this table after the large delete/insert may > be helpful. > > > -----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 по дате отправления: