Обсуждение: [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

>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 */