Hi, It might well be that in my particular case, I don't have anything to worry about the connection time per each user most likely won't kill me or even cause problems at first. But I am trying to build a system, and I don't want to skip any reasonable efficences I can build in from the start.
In my situation the user will only be doing inserts from the mod_perl side, so I am thinking I can make use of something like Apache::File or some kind of tmp file where I will store the tab delimited data for the user insert, and on the cleanup handler phase does a COPY FROM file which is a Postgress thing to the user's table. I need for that data to be available to that same user by JDBC pretty much immediatly. But does a copy from a file like this work better than a persistent connection but one that is doing insert after insert which is not nearly as efficent as the bulk copy? As I understand it the COPY from after a droping indexes would be the fastest way to insert data into a table for postgress, period. I guess I will just have to test it out and see. The other thing I am wondering about, is that even if I have a bunch of users, why couldn't I just make my persistent connection user have rights to all of the other user tables? Well, I guess I could do that, but I am expecting one of you will tell me why that is a bad idea :) Something about it seem too easy after all of this. I was tending to want to use the DBs security also for the same reasons, so maybe that is what bothers me about the above. Thanks, this discussion has helped me out tremendously, just to know others have the same problem even. Eric >First, I'll suggest that there are hopefully other areas you can look at >optimizing that will get you a bigger bang for your time - in my test >environment (old hardware), it takes 7.4 ms per >disconnect/reconnect/rebind and 4.8 ms per rebind. Admittedly, I'm >dealing with LDAP instead of SQL, and I've no idea how they compare. > >If the TCP connection were retained, this could still be a significant >win. *Any* reduction in the connection overhead is an improvement. If >there are a million connects per day, and this saves a milli-second per >connect (believable to me, as at least three packets don't need to be >sent - syn, syn ack, and fin. My TCP's a bit fuzzy, but I think there's >a couple more, and there's also the mod_perl disconnect/reconnect >overhead), that's over 15 minutes of response time and about 560,000,000 >bits of network bandwidth (assuming the DB is not on the same machine) >saved. Admittedly, at 100Mb/s, that's only 6 seconds. > >It may, in some cases, still be necessary to move access control from >the DB into ones application, so one can maintain a single connection >which never rebinds, but I think it's better to utilize the security in >the DB instead of coding ones own - more eyes have looked over it. >We're talking about a fairly small unit of time; it may very well be >better to throw money if you are near your performance limit. > >Ed > > http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone)
