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
--
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 7: don't forget to increase your free space map settings

Oleg Bartunov wrote:
> 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.
>

Yes, the problem persists - I wrote a small perl script that forks 10
chils processes and executes the same queries in parallel without any
php/apachebench involved:

--- 8< ---
#!/usr/bin/perl
use DBI;
$n=10;
$nq=100;
$sql="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;";

sub reaper { my $waitedpid = wait; $running--; $SIG{CHLD} = \&reaper; }
$SIG{CHLD} = \&reaper;

for $i (1..$n)
{
if (fork() > 0) { $running++; }
else
{
my
$dbh=DBI->connect('dbi:Pg:host=daedalus;dbname=<censored>', 'root','',{
AutoCommit => 1 }) || die "!db";
for my $j (1..$nq)
{
my $sth=$dbh->prepare($sql);
$r=$sth->execute() or print STDERR $dbh->errstr();
}
exit 0;
}
}
while ($running > 0)
{
sleep 1;
print "Running: $running\n";
}
--- >8 ---

Result (now with shared_buffers = 20000, hence less system and more user
time):

Cpu0 : 25.1% us, 0.0% sy, 0.0% ni, 74.9% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu1 : 18.3% us, 0.0% sy, 0.0% ni, 81.7% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu2 : 27.8% us, 0.3% sy, 0.0% ni, 71.9% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu3 : 23.5% us, 0.3% sy, 0.0% ni, 75.9% id, 0.0% wa, 0.0% hi, 0.3% si

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ WCHAN
COMMAND
7571 postgres 16 0 204m 62m 61m R 10.6 0.2 0:01.97 -
postmaste
7583 postgres 16 0 204m 62m 61m S 9.6 0.2 0:02.06 semtimedo
postmaste
7586 postgres 16 0 204m 62m 61m S 9.6 0.2 0:02.00 semtimedo
postmaste
7575 postgres 16 0 204m 62m 61m S 9.3 0.2 0:02.12 semtimedo
postmaste
7578 postgres 16 0 204m 62m 61m R 9.3 0.2 0:02.05 -
postmaste

i.e., virtually no difference. With 1000 queries and 10 in parallel, the
apachebench run takes 60.674 seconds and the perl script 59.392 seconds.

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

Do you have anything performing any updates or inserts to this table,
even if it does not update the gist column, even if it does not update
anything ?

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

http://archives.postgresql.org

On Thu, 3 Feb 2005, Tom Lane wrote:

> "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)?
>

it should be 'semtimedop'


> regards, tom lane
>

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 6: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane schrieb:
> What's the platform exactly (hardware and OS)?

Hardware: http://www.appro.com/product/server_1142h.asp
- SCSI version, 2 x 146GB 10k rpm disks in software RAID-1
- 32GB RAM

OS: Linux 2.6.10-rc3, x86_64, debian GNU/Linux distribution

- CONFIG_K8_NUMA is currently turned off (no change, but now all CPUs
have ~25% load, previously one was 100% busy and the others idle)

- CONFIG_GART_IOMMU=y (but no change, tried both settings)
[other kernel options didn't seem to be relevant for tweaking at the
moment, mostly they're "safe defaults"]

The PostgreSQL data directory is on an ext2 filesystem.

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