Re: [HACKERS] Re: INSERT/UPDATE waiting (another example) - Mailing list pgsql-sql

Tom Lane wrote:
> Wayne Piekarski <wayne@senet.com.au> writes:
> > We are experiencing exactly the same problem as above - during the day,
> > all of a sudden Postgres will completely jam up, with all processing in
> > one of the following states: (from ps -axwwwwww)
> 
> It seems possible that the hashtable bugs I fixed a couple months ago
> are rising up to bite you.  (Basically, the shared hashtables that
> contain things like locks and buffers would go nuts if there got to be
> more than 256 entries ... and it sure sounds like your installation is
> big enough that it could have, eg, more than 256 active locks when
> under load.)  One quick thing you might try to test this is to reduce
> the postmaster's -B setting to less than 256 (if you have it set that
> high) and see if stability improves.

Currently, I start up postmaster with -B 192, which I guess puts it below
the value of 256 which causes problems. Apart from when I got past 256
buffers, does the patch fix anything else that might be causing problems?

Just for everyones information, the system contains about 80 tables and
129 indexes. There is about 700 mb of data sprayed over all the tables,
although some have more rows than others. At any one time during the day,
we have about 8 to 10 active postgres connections, half of them are
connected to daemons which continuously sent updates and inserts into the
system, the rest of them are very quick queries from CGI programs. The
problems we experience are always during the day, when the CGI programs
are hammering the database - we don't ever have a problem at night when
the staff go home. 

The whole thing runs 24 hours a day, 7 days a week. Most of the tables
rarely get vacuumed (they have tens of thousands of rows and only inserts
get done to them - the optimiser makes good choices for most of these) -
however we have 5 tables which get vacuum at midnight each day, we drop
all the indexes, vacuum, then recreate. If we don't do the index thing,
the vacuum can take tens of minutes, which is not acceptable - the tables
contain about 20000 rows, each of which gets updated about 3 times during 
the day. I sent an email a while back about vacuum performance, and this
hack is the only way around it.

If any other programs try to query the four tables getting vacuumed then I
get into real trouble. I wish I could do soemthing like:

BEGIN;
LOCK TABLE x;
DROP INDEX x_idx;
VACUUM ANALYZE x;
CREATE INDEX x_idx;
END;

I've seen a #define which looked like it enabled this kind of thing, but
I'm not sure if it is safe to use.


> What I will do is send the patch to Wayne in a separate message, and
> also cc: it to the PATCHES list --- anyone else who needs it can get it
> from there.  Please let us know if this helps, Wayne.

During the week when I get a chance I will trial the patch and see if it
has any affect on the problems we are having. It is very wierd and
impossible to reproduce on demand as it is related to the number of
queries and the load of the machine at the time.

Hopefully I will have some results for this by the end of the week.



While I'm asking some questions here, I should tell you about some of the
other wierd things I've encountered, many of them are related to shared
memory and hash tables, which is making me think more and more that all
the problems I am having are somehow related.

For large tables, when I perform joins, I repeatedly get hash table out of
memory errors. So I have two tables, one called unix, with 20000 rows, and
another called services, with 80000 rows - I am producing a result which
contains about 20000 rows in it as well, so there is lots of data moving
around.

In most cases, the problem occurs when the optimiser mistakenly choses to
use seq scan rather than index scan. To get around these problems, we
initially tried increasing the -B value to larger values (This was a long
time ago but we had problems, it may have been more than 256 which fits in
with what Tom Lane said). Every time we kept increasing the number of
buffers but it got to the point where I was annoyed that the optimiser was
making bad decisions, and I was at a loss on what to do. So I then
discovered the COST_INDEX and COST_HEAP variables, which I set to:

set COST_INDEX = '0'; set COST_HEAP = '99999999';

The optimiser then used index scan for almost anything where possible, the
explain output looked really expensive, but the queries actually executed
properly even with small -B values. So this is what I do to make these big
queries work. There are a few cases where the above set statements
actually cause hash table out of memory as well, so you set them back to
the defaults and then it usually works ok :)

I know the above is a hack but I needed to get out of a jam and that was
the only way I could think of doing it. Are there any other join methods
besides hash join? I thought that lets say I have two tables, A and B,
both with a column called ID which is indexed, and i do a join on A.id and
B.id it can use a more efficient means of joining using indexes rather
than reading both tables into memory and join there?

Here are some explain statements for a big join:


reactor=> explain select unix.username from unix where unix.snum =
services.snum
NOTICE:  QUERY PLAN:

Hash Join  (cost=6191.62 size=43361 width=20) ->  Seq Scan on services  (cost=2204.91 size=43361 width=4) ->  Hash
(cost=0.00size=0 width=0)       ->  Seq Scan on unix  (cost=1212.26 size=20311 width=16)
 



reactor=> set COST_INDEX = '0';
SET VARIABLE
reactor=> set COST_HEAP = '999999999';
SET VARIABLE
reactor=> explain select unix.username from unix where unix.snum =
services.snum;
NOTICE:  QUERY PLAN:

Hash Join  (cost=30000000.00 size=43361 width=20) ->  Index Scan using unix_snum_inv_index on unix
(cost=20311001006080.00 size=20311 width=16) ->  Hash  (cost=0.00 size=0 width=0)       ->  Index Scan using
services_snum_inv_indexon services
 
(cost=43360999964672.00 size=43361 width=4)              

I would assume that the above one which uses indexes would be a lot
better, but why did the optimiser chose the seq scan - do the indexes help
when doing joins and at the same time all rows are being returned back? I
understand that the optimiser will choose not to use indexes if it feels
that it will return most of the rows anyway and so a seq scan is better.


------


One other problem related to the shared memory buffers is every so often,
the postmaster will die with shared memory errors, and device full. This
happens very rarely (once every one to two weeks) but it happens, and I
figured that it might be related to the number of buffers I've started up
with. Note that this problem is not varied by changing the -B value, so I
don't think its my FreeBSD setup.




So I hope someone finds the above useful, I've been reading the mailing
lists a lot and I've heard about developers discovering bugs in locking,
indexes, and vacuum in 6.5, but I wasn't sure if they were applicable to
6.4.2 as well, so I figured I should tell someone just in case.


Sorry about the length of this email, but I had a lot of things to cover.  
Thanks for your help everyone, I look forward to hearing from you ...

Wayne

------------------------------------------------------------------------------
Wayne Piekarski                               Tel:     (08) 8221 5221
Research & Development Manager                Fax:     (08) 8221 5220
SE Network Access Pty Ltd                     Mob:     0407 395 889
222 Grote Street                              Email:   wayne@senet.com.au
Adelaide SA 5000                              WWW:     http://www.senet.com.au