Thread: [GENERAL] Stuck in a vacuum.
Dear all, I started did the following on a table (blast_hits) with approximately 400,000 rows: BEGIN; UPDATE blast_hits SET hit_id=hit_id+400000 WHERE hit_id<=208611; this was taking much too long (and I realised it was useless anyway)... so aborted with Control C). then did an END; QUESTION 1: there was an index on hit_id (and on two other fields in this table of five fields) should I have delete a) the one on hit_it, b) all of them or c) none of them before trying the update? Since I thought that probably left the table a bit messed up, I started a: VACUUM blast_hits; It's using 95% of the cpu and seems to be going nowhere (at least not in the 30 minutes it has been running so far). QUESTION 2: What do I do now? Is there any way I can kill the VACUUM or will they be the final nail in the table's coffin? please help as my teeth are begining to suffer from extensive gnashing! S. +-------------------------+--------------------------------------+ | Stuart Rison | Ludwig Institute for Cancer Research | +-------------------------+ 91 Riding House Street | | Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. | | Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk | +-------------------------+--------------------------------------+
Stuart Rison wrote: > > Since I thought that probably left the table a bit messed up, I started a: > > VACUUM blast_hits; > > It's using 95% of the cpu and seems to be going nowhere (at least not in > the 30 minutes it has been running so far). > > QUESTION 2: What do I do now? Is there any way I can kill the VACUUM or > will they be the final nail in the table's coffin? VACUUM uses transactions so there shouldn't be problems with its stopping. After that try to drop all indices over blash_hits and re-vacuum (in verbose mode). BTW, PG version? Vadim
>Stuart Rison wrote: >> >> HI Vadim, >> >> Version 6.4.0 on an old, old (100Mhz) Indigo 2 running IRIX 5.3. >> >> I have to say that every query I've performed so far on the 400,000 row >> table has been painfully slow -I haven't dare do any joins with an 18,069 >> row table but I might try today- and so... >> >> any suggestions on how to tweak the system (e.g. options when starting >> postmaster) would be welcome. > >-B ?? >The higher the better. no really big on memory/buffer issues, I have 64MB memory... how big can I make -B? I am presuming that you can't just willy-nilly increase the size of -B and that the extent you can increase it by is dependant on available memory. >Also, I don't know has Indigo TEST AND SET or not. >400,000 rows is not so much for PG. Yes, I thought 400,000 rows should not be too much of a problem. I don't know what TEST AND SET is/are! How can I check if I have them and what do they do anyway? >> I'm not actually considering upgrading to 6.5 but could well be convinced >> if that means a dramatic increase in speed! > >I'm not sure. But it's better in multi-user environment. I was also under the impression that it had a greatly improved query optimizer. Would it make an difference with two-way and three-way joins? regards, S. +-------------------------+--------------------------------------+ | Stuart Rison | Ludwig Institute for Cancer Research | +-------------------------+ 91 Riding House Street | | Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. | | Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk | +-------------------------+--------------------------------------+
On Tue, 6 Jul 1999, Vadim Mikheev wrote: > Stuart Rison wrote: > > > > Since I thought that probably left the table a bit messed up, I started a: > > > > VACUUM blast_hits; > > > > It's using 95% of the cpu and seems to be going nowhere (at least not in > > the 30 minutes it has been running so far). > > > > QUESTION 2: What do I do now? Is there any way I can kill the VACUUM or > > will they be the final nail in the table's coffin? > > VACUUM uses transactions so there shouldn't be problems with > its stopping. > After that try to drop all indices over blash_hits and re-vacuum > (in verbose mode). Vadim - any chance I can get my question answered? It is slightly more than academic to me as we now have several Pg production databases running here in my shop...I know you're busy but I am *not* a C programmer and altho I've read your listing I am still in the dark. To recap, one of tables caused vacuum to abort with a fatal bit of stderr. This generated a stale lock which I manually removed. I iterated thru this ps a few times until I realized that I had to drop table and re-create. A simple delete from and copy did not resolve the issue. (Pg ver 6.3.2.) This is a puzzle to me as this table was small...it is now growing daily. Can you tell me: 1) what causes bufmgr.c to barf and kill vacuum when something is returned as `-2', ie `PINNED'? 2) how alarmed should I be? 3) is there a recommended fix (and/or a deprecated fix? ;-) How can I avoid TIA, Tom Here is the stderr I mentioned: NOTICE: BlowawayRelationBuffers(tx_plan, 4): block 4 is referenced (private 0, last 0, global 1) FATAL 1: VACUUM (vc_rpfheap): BlowawayRelationBuffers returned -2 NOTICE: BlowawayRelationBuffers(tx_plan, 4): block 4 is referenced (private 0, last 0, global 1) FATAL 1: VACUUM (vc_rpfheap): BlowawayRelationBuffers returned -2 NOTICE: BlowawayRelationBuffers(tx_plan, 4): block 4 is referenced (private 0, last 0, global 1) FATAL 1: VACUUM (vc_rpfheap): BlowawayRelationBuffers returned -2 NOTICE: BlowawayRelationBuffers(tx_plan, 0): block 4 is referenced (private 0, last 0, global 1) FATAL 1: VACUUM (vc_vacheap): BlowawayRelationBuffers returned -2 ------- North Richmond Community Mental Health Center ------- Thomas Good MIS Coordinator Vital Signs: tomg@ { admin | q8 } .nrnet.org Phone: 718-354-5528 Fax: 718-354-5056 /* Member: Computer Professionals For Social Responsibility */
Thomas Good wrote: > > Vadim - any chance I can get my question answered? It is slightly more > than academic to me as we now have several Pg production databases running > here in my shop...I know you're busy but I am *not* a C programmer and altho > I've read your listing I am still in the dark. Sorry. Well. > 1) what causes bufmgr.c to barf and kill vacuum when something is returned > as `-2', ie `PINNED'? Buffer was pinned by someone, but shouldn't be. This could be caused by buffer leak problem. It's known that FATAL may cause buffer leak. Did you see FATALs before vacuum? > > 2) how alarmed should I be? Shouldn't. No damage is expected in this case. > > 3) is there a recommended fix (and/or a deprecated fix? ;-) How can I avoid Stop postmaster. Sure that there is no backend running. Start postmaster. vacuum. Vadim
Re: Improving the speed of an UPDATE (evolved from Re: [GENERAL] Stuck in a vacuum.)
From
Stuart Rison
Date:
>Stuart Rison wrote: >> I am presuming that you can't just willy-nilly increase the size of -B and >> that the extent you can increase it by is dependant on available memory. > ^^^^^^^^^^^^^^^^ >It depends on available _shared_ memory. >-B 256 or -B 512 is nice. well I'm currently running my postmaster with -B 512. Would it help if I went to -B 1024? Or can I even do so (is there a way of checking the maximum -B the system could support)? >> >> >Also, I don't know has Indigo TEST AND SET or not. > >Look in .../pgsql/src/include/os.h checked. if have #define HAS_TEST_AND-SET >> >400,000 rows is not so much for PG. >> >> Yes, I thought 400,000 rows should not be too much of a problem. I don't > >BTW, did you setup indices? Did you use EXPLAIN for your queries? Table = blast_hits +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | hit_id | int4 default nextval ( 'hit_id_s | 4 | | query_id | text | var | | pdb_id | char() | 4 | | chain | char() | 1 | | fragment | char() | 1 | | high_score | int2 | 2 | | prob_score | float8 | 8 | | number_hsps | int2 | 2 | | species | char() | 3 | +----------------------------------+----------------------------------+-------+ Indices: blast_hits_prob_score_idx hit_id_idx query_id_idx all indices are b-trees. and it contains 400,000 rows. my query which took very long (over 15minutes and the I killed it) was: UPDATE blast_hits SET hit_id=hit_id+400000 WHERE hit_id<=208000; functions=> explain update blast_hits set hit_id=hit_id+400000 where hit_id<=208000; NOTICE: QUERY PLAN: Index Scan using hit_id_idx on blast_hits (cost=8302.12 size=125503 width=82) EXPLAIN functions=> explain update blast_hits set hit_id=hit_id+400000; NOTICE: QUERY PLAN: Seq Scan on blast_hits (cost=17100.73 size=376507 width=82) EXPLAIN I guess the question here is, is an UPDATE akin to a INSERT or not? Because if it is, I am under the impression that index radically slow down inserts and so perhaps I should drop all indices before the UPDATE and the rebuild them after... but if it isn't, then the index is in fact being used by the UPDATE with the WHERE clause. >> I was also under the impression that it had a greatly improved query >> optimizer. Would it make an difference with two-way and three-way joins? > >Yes. It may well be time for the big move! regards, Stuart. +-------------------------+--------------------------------------+ | Stuart Rison | Ludwig Institute for Cancer Research | +-------------------------+ 91 Riding House Street | | Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. | | Fax. (0171) 878 4040 | stuart@ludwig.ucl.ac.uk | +-------------------------+--------------------------------------+
On Tue, 6 Jul 1999, Vadim Mikheev wrote: Vadim - thanks very much for the reply! Sorry to take up your time on this... Dobri vecher, Tom > > 1) what causes bufmgr.c to barf and kill vacuum when something is returned > > as `-2', ie `PINNED'? > > Buffer was pinned by someone, but shouldn't be. This could be caused > by buffer leak problem. It's known that FATAL may cause buffer leak. > Did you see FATALs before vacuum? > > > > > 2) how alarmed should I be? > > Shouldn't. No damage is expected in this case. > > > > > 3) is there a recommended fix (and/or a deprecated fix? ;-) How can I avoid > > Stop postmaster. Sure that there is no backend running. > Start postmaster. vacuum. ------- North Richmond Community Mental Health Center ------- Thomas Good MIS Coordinator Vital Signs: tomg@ { admin | q8 } .nrnet.org Phone: 718-354-5528 Fax: 718-354-5056 /* Member: Computer Professionals For Social Responsibility */