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