Thread: Connections per second?

Hi,

I'm writing a small but must-be-fast cgi program that for each hit it gets, it reads an indexed table in a postgres
databaseand writes a log to a file based on the result. Any idea how many hits a second I can get to before things
startcrashing, or queuing up too much, etc? And will postgres be one of the first to fall? Do any of you think it can
handle2000 hits a second (what I think I could get at peak times) - and what would it need to do so? Persistent
connections?Are there any examples or old threads on writing a similar program in C with libpq? 

Thanks,

Ale

--
Alejandro Fernandez
Electronic Group Interactive
--+34-65-232-8086--

Depending on the size of the table and how much RAM you have, and on your OS,
you may find the entire table cached in RAM, which would be ideal.  You should
use persistent connections if at all possible. You didn't mention what web
server you're using, but if you're using Apache, you may want to write an Apache
module that will maintain a persistent connection for each apache child process;
that will also keep your program loaded in memory so it doesn't have to be
reloaded on each request. I would also be concerned about write speed to the log
file; not sure where that will peak.

Hope this helps,

Wes Sheldahl



Alejandro Fernandez <ale%electronic-group.com@interlock.lexmark.com> on
04/23/2002 10:51:40 AM

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  [GENERAL] Connections per second?


Hi,

I'm writing a small but must-be-fast cgi program that for each hit it gets, it
reads an indexed table in a postgres database and writes a log to a file based
on the result. Any idea how many hits a second I can get to before things start
crashing, or queuing up too much, etc? And will postgres be one of the first to
fall? Do any of you think it can handle 2000 hits a second (what I think I could
get at peak times) - and what would it need to do so? Persistent connections?
Are there any examples or old threads on writing a similar program in C with
libpq?

Thanks,

Ale

--
Alejandro Fernandez
Electronic Group Interactive
--+34-65-232-8086--

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

And aolserver is another.  It will let you write modules in C so you don't have to take the TCL interpreter hit, small
thoughit is. 
 - Ian


>>> Doug McNaught <doug@wireboard.com> 04/23/02 09:16AM >>>
Alejandro Fernandez <ale@e-group.org> writes:

> Hi,
>
> I'm writing a small but must-be-fast cgi program that for each hit
> it gets, it reads an indexed table in a postgres database and writes
> a log to a file based on the result. Any idea how many hits a second
> I can get to before things start crashing, or queuing up too much,
> etc? And will postgres be one of the first to fall? Do any of you
> think it can handle 2000 hits a second (what I think I could get at
> peak times) - and what would it need to do so? Persistent
> connections? Are there any examples or old threads on writing a
> similar program in C with libpq?

Doing it as CGI is going to have two big performance penalties:

1) Kernel and system overhead for starting of a new process per hit,
   plus interpreter startup if you're using a scripting language
2) Overhead in Postgres for creating a database connection from scratch

Doing it in C will only eliminate the interpreter startup.

You really want a non-CGI solution (such as mod_perl) and you really
want persistent connections (Apache::DBI is one solution that works
with mod_perl).  Java servlets with a connection pooling library would
also work.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Hi,

Thanks for your replies.

I think persistence is what I'm looking for then! Would you have any pointers on how to inform myself on getting
connectionsto be persistent? Yes, it is apache. Write speed, well, I think I'd need some pretty fast hard drives...
MaybeIDE drives, with raid 0...  

So:

1)How do I get connections to postgresql be persistent?
2)How do I get all the other connections to be persistent too?? I would welcome a url or something that might help!
(perhapsoff list if it's off topic!) 

Thanks,

Ale

On Tue, 23 Apr 2002 12:26:46 -0400
wsheldah@lexmark.com wrote:

> server you're using, but if you're using Apache, you may want to write an Apache
> module that will maintain a persistent connection for each apache child process;

Alejandro Fernandez <ale@e-group.org> writes:

> I think persistence is what I'm looking for then! Would you have any
> pointers on how to inform myself on getting connections to be
> persistent? Yes, it is apache. Write speed, well, I think I'd need
> some pretty fast hard drives... Maybe IDE drives, with raid 0...

I'm pretty sure Apache::DBI does this, in conjunction with mod_perl.

-Doug