Re: GiST indexes and concurrency (tsearch2)

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