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