Thread: Function & Update/Insert Problem and Performance

Function & Update/Insert Problem and Performance

From
postgres@countup.de
Date:
Hi there, <br /><br />i m new in this board. I read a little here to find a solution for my problem. But i couldn´t
findsomething. <br /><br />My Problem ist: <br />I m programming a counter in PHP with Postgres as DB. I had 6,000
visitorsacross all day, so everything worked fine first. <br />Yesterday i got 80K Users at my sites, that was the
pointwere all crashed. <br /><br />I have an Intel Celeron 2,2 Server with 1 GB Ram <br />I have PHP in Version 4.2.0
andPostgres 7.3.4 <br />The Connection to DB from php ist over PEAR-DB-Class <br /><br />I wrote a postgres function
whichgets up to 17 parameters such as os, browser, referer and so on <br />This function tries to update a row in the
db/tablewhich matches with the hour of the current datetime <br />If this returns not found then i do an insert <br
/>Soi have a few tables and th e update-insert procedure is done a few times (about 15-17 times). At the end i collect
somedata and return them to show as the counter-banner <br /><br />I called the function with expain analyze, so it
showedsomething around 222 ms duration <br /><br />My first problem ist, what is about transactions? Do i have to care
about?I read that a function is always just one transaction <br />So if something fails, the whole function will be
undonewith a rollback <br /><br />Second problem is my update works just if they are not too much visitors/postmaster
processes...<br />If there are too much postmasters i get too many errors in my data (the update seems not to work, it
doesntfind the exisiting row in the current hour, so it does inserts i think) <br />Do i have to care about
permissions?I have set for example “lock table os in exclusive mode” for all tables i work with <br /><br />The next
problemis, i m looking to get 2,000,000 visitors day <br />So i will have to change something in pos tgres config
right?But what exactly? Max_connectionsnumber, what is else important? Something in apache maybe too? <br /><br />I
hopei can get some ideas, because everything works, except the perfomance and the number of the visitors manipulate
datain wrong way and are making me seek! <br />I know there are too many questions, but every idea of you guys will
helpme, thanks <br /><br />Thank you so far <br /><br />Boris <br />