SEO

Hi,

according to
http://www.postgresql.org/docs/8.0/i...mitations.html ,
concurrent access to GiST indexes isn't possible at the moment. I
haven't read the thesis mentioned there, but I presume that concurrent
read access is also impossible. Is there any workaround for this, esp.
if the index is usually only read and not written to?

It seems to be a big problem with tsearch2, when multiple clients are
hammering the db (we have a quad opteron box here that stays 75% idle
despite an apachebench with concurrency 10 stressing the php script that
uses tsearch2, with practically no disk accesses)

Regards,
Marinos
--
Dipl.-Ing. Marinos Yannikos, CEO
Preisvergleich Internet Services AG
Obere Donaustraße 63/2, A-1020 Wien
Tel./Fax: (+431) 5811609-52/-55

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

> It seems to be a big problem with tsearch2, when multiple clients are
> hammering the db (we have a quad opteron box here that stays 75% idle
> despite an apachebench with concurrency 10 stressing the php script that
> uses tsearch2, with practically no disk accesses)

Concurrency with READs is fine - but you can only have one WRITE going
at once.

Chris

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Thu, 3 Feb 2005, Marinos J. Yannikos wrote:

> Hi,
>
> according to http://www.postgresql.org/docs/8.0/i...mitations.html
> , concurrent access to GiST indexes isn't possible at the moment. I haven't
> read the thesis mentioned there, but I presume that concurrent read access is
> also impossible. Is there any workaround for this, esp. if the index is
> usually only read and not written to?

there are should no problem with READ access.

>
> It seems to be a big problem with tsearch2, when multiple clients are
> hammering the db (we have a quad opteron box here that stays 75% idle despite
> an apachebench with concurrency 10 stressing the php script that uses
> tsearch2, with practically no disk accesses)

I'm willing to see some details: version, query, explain analyze.




>
> Regards,
> Marinos
>

Regards,
Oleg
__________________________________________________ ___________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

"Marinos J. Yannikos" <mjy@geizhals.at> writes:
> I can't seem to find out where the bottleneck is, but it doesn't seem to
> be CPU or disk. "top" shows that postgres processes are frequently in
> this state:

> 6701 postgres 16 0 204m 58m 56m S 9.3 0.2 0:06.96 semtimedo
> ^^^^^^^^^

What's the platform exactly (hardware and OS)?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Marinos,

what if you construct "apachebench & Co" free script and see if
the issue still exists. There are could be many issues doesn't
connected to postgresql and tsearch2.

Oleg

On Thu, 3 Feb 2005, Marinos J. Yannikos wrote:

> Oleg Bartunov wrote:
>> On Thu, 3 Feb 2005, Marinos J. Yannikos wrote:
>>> concurrent access to GiST indexes isn't possible at the moment. I [...]
>>
>> there are should no problem with READ access.
>
> OK, thanks everyone (perhaps it would make sense to clarify this in the
> manual).
>
>> I'm willing to see some details: version, query, explain analyze.
>
> 8.0.0
>
> Query while the box is idle:
>
> explain analyze select count(*) from fr_offer o, fr_merchant m where idxfti
> @@ to_tsquery('ranz & mc') and eur >= 70 and m.m_id=o.m_id;
>
> Aggregate (cost=2197.48..2197.48 rows=1 width=0) (actual time=88.052..88.054
> rows=1 loops=1)
> -> Merge Join (cost=2157.42..2196.32 rows=461 width=0) (actual
> time=88.012..88.033 rows=3 loops=1)
> Merge Cond: ("outer".m_id = "inner".m_id)
> -> Index Scan using fr_merchant_pkey on fr_merchant m
> (cost=0.00..29.97 rows=810 width=4) (actual time=0.041..1.233 rows=523
> loops=1)
> -> Sort (cost=2157.42..2158.57 rows=461 width=4) (actual
> time=85.779..85.783 rows=3 loops=1)
> Sort Key: o.m_id
> -> Index Scan using idxfti_idx on fr_offer o
> (cost=0.00..2137.02 rows=461 width=4) (actual time=77.957..85.754 rows=3
> loops=1)
> Index Cond: (idxfti @@ '\'ranz\' & \'mc\''::tsquery)
> Filter: (eur >= 70::double precision)
>
> Total runtime: 88.131 ms
>
> now, while using apachebench (-c10), "top" says this:
>
> Cpu0 : 15.3% us, 10.0% sy, 0.0% ni, 74.7% id, 0.0% wa, 0.0% hi, 0.0% si
> Cpu1 : 13.3% us, 11.6% sy, 0.0% ni, 75.1% id, 0.0% wa, 0.0% hi, 0.0% si
> Cpu2 : 16.9% us, 9.6% sy, 0.0% ni, 73.4% id, 0.0% wa, 0.0% hi, 0.0% si
> Cpu3 : 18.7% us, 14.0% sy, 0.0% ni, 67.0% id, 0.0% wa, 0.0% hi, 0.3% si
>
> (this is with shared_buffers = 2000; a larger setting makes almost no
> difference for overall performance: although according to "top" system time
> goes to ~0 and user time to ~25%, the system still stays 70-75% idle)
>
> vmstat:
>
> r b swpd free buff cache si so bi bo in cs us sy id
> wa
> 2 0 0 8654316 64908 4177136 0 0 56 35 279 286 5 1 94
> 0
> 2 0 0 8646188 64908 4177136 0 0 0 0 1156 2982 15 10 75
> 0
> 2 0 0 8658412 64908 4177136 0 0 0 0 1358 3098 19 11 70
> 0
> 1 0 0 8646508 64908 4177136 0 0 0 104 1145 2070 13 12 75
> 0
>
> so the script's execution speed is apparently not limited by the CPUs.
>
> The query execution times go up like this while apachebench is running (and
> the system is 75% idle):
>
> Aggregate (cost=2197.48..2197.48 rows=1 width=0) (actual
> time=952.661..952.663 rows=1 loops=1)
> -> Merge Join (cost=2157.42..2196.32 rows=461 width=0) (actual
> time=952.621..952.641 rows=3 loops=1)
> Merge Cond: ("outer".m_id = "inner".m_id)
> -> Index Scan using fr_merchant_pkey on fr_merchant m
> (cost=0.00..29.97 rows=810 width=4) (actual time=2.078..3.338 rows=523
> loops=1)
> -> Sort (cost=2157.42..2158.57 rows=461 width=4) (actual
> time=948.345..948.348 rows=3 loops=1)
> Sort Key: o.m_id
> -> Index Scan using idxfti_idx on fr_offer o
> (cost=0.00..2137.02 rows=461 width=4) (actual time=875.643..948.301 rows=3
> loops=1)
> Index Cond: (idxfti @@ '\'ranz\' & \'mc\''::tsquery)
> Filter: (eur >= 70::double precision)
> Total runtime: 952.764 ms
>
> I can't seem to find out where the bottleneck is, but it doesn't seem to be
> CPU or disk. "top" shows that postgres processes are frequently in this
> state:
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ WCHAN COMMAND
> 6701 postgres 16 0 204m 58m 56m S 9.3 0.2 0:06.96 semtimedo
> ^^^^^^^^^
> postmaste
>
> Any hints are appreciated...
>
> Regards,
> Marinos
>

Regards,
Oleg
__________________________________________________ ___________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

"Marinos J. Yannikos" <mjy@geizhals.at> writes:
> according to
> http://www.postgresql.org/docs/8.0/i...mitations.html ,
> concurrent access to GiST indexes isn't possible at the moment. I
> haven't read the thesis mentioned there, but I presume that concurrent
> read access is also impossible.

You presume wrong ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Marinos Yannikos <mjy@geizhals.at> writes:
> This is really baffling me, it looks like a kernel issue of some sort
> (I'm only guessing though). I found this old posting:
> http://archives.postgresql.org/pgsql...2/msg00836.php - is
> this still applicable?

That seems to be an early report of what we now recognize as the
"context swap storm" problem, and no we don't have a solution yet.
I'm not completely convinced that you're seeing the same thing,
but if you're seeing a whole lot of semops then it could well be.

I set up a test case consisting of two backends running the same
tsearch2 query over and over --- nothing fancy, just one of the ones
from the tsearch2 regression test:
SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty');
I used gdb to set breakpoints at PGSemaphoreLock and PGSemaphoreTryLock,
which are the only two functions that can possibly block on a semop
call. On a single-processor machine, I saw maybe one hit every couple
of seconds, all coming from contention for the BufMgrLock or sometimes
the LockMgrLock. So unless I've missed something, there's not anything
in tsearch2 or gist per se that is causing lock conflicts. You said
you're testing a quad-processor machine, so it could be that you're
seeing the same lock contention issues that we've been trying to figure
out for the past year ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Oleg Bartunov schrieb:
> Marinos,
>
> what if you construct "apachebench & Co" free script and see if
> the issue still exists. There are could be many issues doesn't
> connected to postgresql and tsearch2.
>

Some more things I tried:
- data directory on ramdisk (tmpfs) - no effect
- database connections either over Unix domain sockets or TCP - no effect
- CLUSTER on gist index - approx. 20% faster queries, but CPU usage
still hovers around 25% (75% idle)
- preemptible kernel - no effect

This is really baffling me, it looks like a kernel issue of some sort
(I'm only guessing though). I found this old posting:
http://archives.postgresql.org/pgsql...2/msg00836.php - is
this still applicable? I don't see an unusually high number of context
switches, but the processes seem to be spending some time in
"semtimedop" (even though the TAS assembly macros are definetely being
compiled-in).

If you are interested, I can probably provide an account on one of our
identically configured boxes by Monday afternoon (GMT+1) with the same
database and benchmarking utility.

Regards,
Marinos

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Marinos Yannikos <mjy@geizhals.at> writes:
> Some more things I tried:

You might try the attached patch (which I just applied to HEAD).
It cuts down the number of acquisitions of the BufMgrLock by merging
adjacent bufmgr calls during a GIST index search. I'm not hugely
hopeful that this will help, since I did something similar to btree
last spring without much improvement for context swap storms involving
btree searches ... but it seems worth trying.

regards, tom lane

*** src/backend/access/gist/gistget.c.orig Fri Dec 31 17:45:27 2004
--- src/backend/access/gist/gistget.c Sat Feb 5 14:19:52 2005
***************
*** 60,69 ****
BlockNumber blk;
IndexTuple it;

b = ReadBuffer(s->indexRelation, GISTP_ROOT);
p = BufferGetPage(b);
po = (GISTPageOpaque) PageGetSpecialPointer(p);
- so = (GISTScanOpaque) s->opaque;

for (;
{
--- 60,70 ----
BlockNumber blk;
IndexTuple it;

+ so = (GISTScanOpaque) s->opaque;
+
b = ReadBuffer(s->indexRelation, GISTP_ROOT);
p = BufferGetPage(b);
po = (GISTPageOpaque) PageGetSpecialPointer(p);

for (;
{
***************
*** 75,86 ****

while (n < FirstOffsetNumber || n > maxoff)
{
! ReleaseBuffer(b);
! if (so->s_stack == NULL)
return false;

! stk = so->s_stack;
! b = ReadBuffer(s->indexRelation, stk->gs_blk);
p = BufferGetPage(b);
po = (GISTPageOpaque) PageGetSpecialPointer(p);
maxoff = PageGetMaxOffsetNumber(p);
--- 76,89 ----

while (n < FirstOffsetNumber || n > maxoff)
{
! stk = so->s_stack;
! if (stk == NULL)
! {
! ReleaseBuffer(b);
return false;
+ }

! b = ReleaseAndReadBuffer(b, s->indexRelation, stk->gs_blk);
p = BufferGetPage(b);
po = (GISTPageOpaque) PageGetSpecialPointer(p);
maxoff = PageGetMaxOffsetNumber(p);
***************
*** 89,94 ****
--- 92,98 ----
n = OffsetNumberPrev(stk->gs_child);
else
n = OffsetNumberNext(stk->gs_child);
+
so->s_stack = stk->gs_parent;
pfree(stk);

***************
*** 116,123 ****
it = (IndexTuple) PageGetItem(p, PageGetItemId(p, n));
blk = ItemPointerGetBlockNumber(&(it->t_tid));

! ReleaseBuffer(b);
! b = ReadBuffer(s->indexRelation, blk);
p = BufferGetPage(b);
po = (GISTPageOpaque) PageGetSpecialPointer(p);
}
--- 120,126 ----
it = (IndexTuple) PageGetItem(p, PageGetItemId(p, n));
blk = ItemPointerGetBlockNumber(&(it->t_tid));

! b = ReleaseAndReadBuffer(b, s->indexRelation, blk);
p = BufferGetPage(b);
po = (GISTPageOpaque) PageGetSpecialPointer(p);
}
***************
*** 137,142 ****
--- 140,147 ----
BlockNumber blk;
IndexTuple it;

+ so = (GISTScanOpaque) s->opaque;
+
blk = ItemPointerGetBlockNumber(&(s->currentItemData));
n = ItemPointerGetOffsetNumber(&(s->currentItemData));

***************
*** 148,154 ****
b = ReadBuffer(s->indexRelation, blk);
p = BufferGetPage(b);
po = (GISTPageOpaque) PageGetSpecialPointer(p);
- so = (GISTScanOpaque) s->opaque;

for (;
{
--- 153,158 ----
***************
*** 157,176 ****

while (n < FirstOffsetNumber || n > maxoff)
{
! ReleaseBuffer(b);
! if (so->s_stack == NULL)
return false;

! stk = so->s_stack;
! b = ReadBuffer(s->indexRelation, stk->gs_blk);
p = BufferGetPage(b);
- maxoff = PageGetMaxOffsetNumber(p);
po = (GISTPageOpaque) PageGetSpecialPointer(p);

if (ScanDirectionIsBackward(dir))
n = OffsetNumberPrev(stk->gs_child);
else
n = OffsetNumberNext(stk->gs_child);
so->s_stack = stk->gs_parent;
pfree(stk);

--- 161,183 ----

while (n < FirstOffsetNumber || n > maxoff)
{
! stk = so->s_stack;
! if (stk == NULL)
! {
! ReleaseBuffer(b);
return false;
+ }

! b = ReleaseAndReadBuffer(b, s->indexRelation, stk->gs_blk);
p = BufferGetPage(b);
po = (GISTPageOpaque) PageGetSpecialPointer(p);
+ maxoff = PageGetMaxOffsetNumber(p);

if (ScanDirectionIsBackward(dir))
n = OffsetNumberPrev(stk->gs_child);
else
n = OffsetNumberNext(stk->gs_child);
+
so->s_stack = stk->gs_parent;
pfree(stk);

***************
*** 198,205 ****
it = (IndexTuple) PageGetItem(p, PageGetItemId(p, n));
blk = ItemPointerGetBlockNumber(&(it->t_tid));

! ReleaseBuffer(b);
! b = ReadBuffer(s->indexRelation, blk);
p = BufferGetPage(b);
po = (GISTPageOpaque) PageGetSpecialPointer(p);

--- 205,211 ----
it = (IndexTuple) PageGetItem(p, PageGetItemId(p, n));
blk = ItemPointerGetBlockNumber(&(it->t_tid));

! b = ReleaseAndReadBuffer(b, s->indexRelation, blk);
p = BufferGetPage(b);
po = (GISTPageOpaque) PageGetSpecialPointer(p);


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Tom Lane wrote:
> You might try the attached patch (which I just applied to HEAD).
> It cuts down the number of acquisitions of the BufMgrLock by merging
> adjacent bufmgr calls during a GIST index search. [...]

Thanks - I applied it successfully against 8.0.0, but it didn't seem to
have a noticeable effect. I'm still seeing more or less exactly 25% CPU
usage by postgres processes and identical query times (measured with the
Perl script I posted earlier).

Regards,
Marinos
--
Dipl.-Ing. Marinos Yannikos, CEO
Preisvergleich Internet Services AG
Obere Donaustrasse 63, A-1020 Wien
Tel./Fax: (+431) 5811609-52/-55

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)