Re: [HACKERS] v6.1 buffers and performance - Mailing list pgsql-hackers

Thomas G. Lockhart wrote:
>
> Here is an update on postgres backend buffer performance (and problems).
>
> I ran the regression tests with several sizes for the "-B" option for
> postmaster. The best performance was for the run with the largest value
> of -B. However, smaller values of -B seem to give results indicating a
> large memory leak or garbage collection problem in the backend.
>
...skipped
>
> In all cases, if I repeat the regression test without restarting
> postmaster, the elapsed time becomes significantly higher. For runs with
> "-B 16", a second run of the regression test starts failing midway
> through with messages indicating that buffer space is exhausted.
>
> I am running on a RedHat Linux box with i686 processor(s).
>
> Vadim (and others?), can you see this feature in your regression runs? I
> am still sitting on Robert Withrow's Purify runs on the regression
> tests. Would those be helpful? Since the regression tests have changed
> so much since v6.0, and since many features tested in the new regression
> tests are not available in v6.0, I don't know if it would be useful to
> try running the new regression tests with the old release. I haven't
> tried distilling the problem down to a single test, because I don't
> really know what to look for or what is causing the symptoms.

Tom, I posted message 23 May about this problem:
- ---------------------

Thomas G. Lockhart wrote:
>
> I seem to be seeing a buffer memory leak on my Linux box which
> becomes apparent only with several consecutive runs of the regression
> test. Can someone else reproduce this? Any suggestions on how
> to collect more information to help with debugging? (I don't know
> anything about this part of the code so don't know if I can actually
> *do* the debugging, but it's a start...)
>
> I've included below a summary of my results. Notice the increasing
> elapsed time (6 minutes 41.91 seconds for the first case) for
> consecutive runs. Note also that this time goes back down to this
> nominal value if postmaster is killed (control-c) and restarted.
>
> If I decrease the buffer parameter to "-B 16" (from the claimed "-B 64"
> default value) then the regression tests fail during the second
> iteration.

There is bug in executor concerning SQL-functions which return
SETOF results.

Buffer leak comes from queries like

SELECT p.name, p.hobbies.name, p.hobbies.equipment.name FROM person p;
                 ^^^^^^^         ^^^^^^^ ^^^^^^^^^
                 these funcs created in create_function_2.sql
in misc.sql

Also, queries return dummy results:

regression=> SELECT p.name, p.hobbies.name, p.hobbies.equipment.name
FROM person p;
name |name       |name
- -----+-----------+-------------
mike |posthacking|advil
joe  |basketball |peet's coffee
sally|basketball |hightops
(3 rows)

regression=> SELECT p.hobbies.equipment.name, p.name, p.hobbies.name
FROM person p;
name    |name |name
- --------+-----+-----------
advil   |mike |posthacking
hightops|joe  |basketball
hightops|sally|basketball
(3 rows)

Funny ?

Valid result:
regression=> SELECT person.name as who, hobbies_r.name as hobby,
equipment_r.name as need_in
from person, hobbies_r, equipment_r
where person.name = hobbies_r.person and equipment_r.hobby = hobbies_r.name;
who  |hobby      |need_in
- -----+-----------+-------------
mike |posthacking|advil
mike |posthacking|peet's coffee
joe  |basketball |hightops
sally|basketball |hightops
(4 rows)

 - just like pointed in comments in misc.sql

All comes from executor/execQual.c:ExecTargetList()

            if ((IsA(expr,Iter)) && (*isDone))
                return (HeapTuple)NULL;

 - what if there are > 1 Iters, which return different number
of results (as in examples above) - one is DONE, but other(s) are not:

1. backend doesn't restore PrivateRefCount-s and so doesn't unpin
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   buffers properly;
2. JOE, which likes basketball, need in "peet's coffee"
   (from MIKE, which likes posthacking, as we do) instead of "hightops".

Bug presents in 1.09 too... I don't want to fix it now.

Vadim

------------------------------