pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Oleg Bartunov <oleg@sai.msu.su>
To: pgsql-hackers@postgreSQL.org
Cc: pgsql-sql@postgreSQL.org
Subject: inserts/updates problem under stressing !
Date: Sat, 24 Jul 1999 13:48:29 +0400 (MSD)
Message-ID: <Pine.GSO.3.96.SK.990724121543.18633v-100000@ra> (raw)

I did some benchmarks of my Web site and notice I lost some hits
which I accumulate in postgres (6.5.1) database on Linux 2.0.36 system

Here is what I had before testing - 181 hits for msg_id=1463

  1463|  181|Sat 24 Jul 12:12:24 1999 MSD|Sat 24 Jul 12:12:34 1999 MSD
(11 rows)

12:12[zeus]:/usr/local/apache/bin>ab -c 20 -n 200 http://astronet.sai.msu.su/db/pubs.html\?msg_id=1463; psql discovery -c 'select * from hits where msg_id=1463;'

After running 20 concurent connections, total number requests  of 200 I 
expected hit count must be increased by 200, but some hits doesn't recorded.
test reports all requests completed successfully and there were nothing
wrong in apache error logs. It's interesting that sometimes I got even
*more* hits than expected ! I didn't noticed any problem if I use smaller
number of concurrent connections. 
I didn't use explicit locking - just insert/update into table using
plpgsql function. Do I need something special to take care many concurrent
inserts/updates ?

	Regards,

		Oleg



Here is my test results:


This is ApacheBench, Version 1.3
Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright (c) 1998-1999 The Apache Group, http://www.apache.org/

Server Software:        Apache/1.3.6
Server Hostname:        astronet.sai.msu.su
Server Port:            80

Document Path:          /db/pubs.html?msg_id=1463
Document Length:        3564 bytes

Concurrency Level:      20
Time taken for tests:   10.120 seconds
Complete requests:      200
Failed requests:        0
Total transferred:      769800 bytes
HTML transferred:       712800 bytes
Requests per second:    19.76
Transfer rate:          76.07 kb/s received

Connnection Times (ms)
              min   avg   max
Connect:        0    58   380
Processing:    58   734  4919
Total:         58   792  5299
msg_id|count|first_access                |last_access
------+-----+----------------------------+----------------------------
  1463|  370|Sat 24 Jul 12:12:24 1999 MSD|Sat 24 Jul 12:13:24 1999 MSD
(1 row)
         ^^^^
     must be 381

Here is a entry from apache config file:

--------------------------------
PerlModule Apache::HitsDBI
<Location /db>
   PerlLogHandler Apache::HitsDBI
</Location>

---------------------------------
package Apache::HitsDBI;
use Apache::Constants qw(:common);

use strict;
# preloaded in startup.pl
#use DBI ();

sub handler {
    my $orig = shift;
    my $url  = $orig->uri;
    my $args = $orig->args();
    if ( $url =~ /pubs\.html/ && $args =~ /msg_id=(\d+)/ ) {
      my $dbh = DBI->connect("dbi:Pg:dbname=discovery") || die DBI->errstr;
      my $sth = $dbh->do("SELECT acc_hits($1)") || die $dbh->errstr;
    } 
    return OK;
}

1;
__END__

-------------------------------
create table hits ( 
  msg_id int4 not null,
  count  int4 not null,
  first_access datetime default now(),
  last_access datetime
);
create index idx_hits on hits(msg_id);

CREATE FUNCTION "acc_hits" (int4) RETURNS int4 AS '
Declare
    keyval      Alias For $1;
    cnt         int4;
    curtime     datetime;
Begin
    curtime := ''now'';
    Select count into cnt from hits where msg_id = keyval;
    if Not Found then
        cnt := 1;
        -- first_access inserted on default, last_access is NULL
        Insert Into hits (msg_id,count) values (keyval, cnt);
    else
        cnt := cnt + 1;
        Update hits set count = cnt,last_access = curtime where msg_id = keyval;
    End If;
    return cnt;
End;
' LANGUAGE 'plpgsql';

---------------------------------


_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83






view thread (11+ messages)  latest in thread

Message-ID: <Pine.GSO.3.96.SK.990724121543.18633v-100000@ra>
Permalink:  ../Pine.GSO.3.96.SK.990724121543.18633v-100000@ra/
Also on:    postgresql.org/message-id/Pine.GSO.3.96.SK.990724121543.18633v-100000@ra

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: oleg@sai.msu.su, pgsql-hackers@postgreSQL.org
  Subject: Re: inserts/updates problem under stressing !
  In-Reply-To: <Pine.GSO.3.96.SK.990724121543.18633v-100000@ra>
  References: <Pine.GSO.3.96.SK.990724121543.18633v-100000@ra>

* 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