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