X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3E9EE8B9D95 for ; Thu, 3 Feb 2005 13:16:01 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30715-06 for ; Thu, 3 Feb 2005 13:15:49 +0000 (GMT) Received: from morework.geizhals.at (home.geizhals.at [213.229.14.34]) by svr1.postgresql.org (Postfix) with ESMTP id E14D88B9E89 for ; Thu, 3 Feb 2005 13:15:50 +0000 (GMT) Received: from localhost (morework [127.0.0.1]) by morework.geizhals.at (Postfix) with ESMTP id 857C4DEBC1; Thu, 3 Feb 2005 14:15:50 +0100 (CET) Received: from [192.168.0.2] (chello080110242194.117.11.tuwien.teleweb.at [80.110.242.194]) by morework.geizhals.at (Postfix) with ESMTP; Thu, 3 Feb 2005 14:15:50 +0100 (CET) Message-ID: <42022406.1050400@geizhals.at> Date: Thu, 03 Feb 2005 14:15:50 +0100 From: "Marinos J. Yannikos" User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Oleg Bartunov Cc: pgsql-performance@postgresql.org Subject: Re: GiST indexes and concurrency (tsearch2) References: <4201AEB1.4000504@geizhals.at> <4202053B.10207@geizhals.at> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new-20030616-p10 at geizhals.at X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200502/64 X-Sequence-Number: 10393 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=','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