pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marinos J. Yannikos <mjy@geizhals.at>
To: Oleg Bartunov <oleg@sai.msu.su>
Cc: pgsql-performance@postgresql.org
Subject: Re: GiST indexes and concurrency (tsearch2)
Date: Thu, 03 Feb 2005 14:15:50 +0100
Message-ID: <42022406.1050400@geizhals.at> (raw)
In-Reply-To: <Pine.GSO.4.62.0502031514090.20328@ra.sai.msu.su>
References: <4201AEB1.4000504@geizhals.at>
	<Pine.GSO.4.62.0502031255350.20328@ra.sai.msu.su>
	<4202053B.10207@geizhals.at>
	<Pine.GSO.4.62.0502031514090.20328@ra.sai.msu.su>

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



view thread (17+ messages)  latest in thread

Message-ID: <42022406.1050400@geizhals.at>
Permalink:  ../42022406.1050400@geizhals.at/
Also on:    postgresql.org/message-id/42022406.1050400@geizhals.at

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mjy@geizhals.at, oleg@sai.msu.su
  Subject: Re: GiST indexes and concurrency (tsearch2)
  In-Reply-To: <42022406.1050400@geizhals.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox