Skip site navigation (1) Skip section navigation (2)

Function & Update/Insert Problem and Performance

From: postgres(at)countup(dot)de
To: pgsql-performance(at)postgresql(dot)org
Subject: Function & Update/Insert Problem and Performance
Date: 2004-01-22 15:13:23
Message-ID: 20040122151323.7FA355A816F@kserver17.erfurt12.de (view raw or flat)
Thread:
Lists: pgsql-performance
<html><head></head><body bgcolor="#ffffff" text="#000000" link="#000000" alink="#000000" vlink="#000000" marginwidth=10 marginheight=10 topmargin=10 leftmargin=10>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 find something.
<br>
<br>My Problem ist:
<br>I m programming a counter in PHP with Postgres as DB. I had 6,000 visitors across all day, so everything worked fine first.
<br>Yesterday i got 80K Users at my sites, that was the point were 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 and Postgres 7.3.4
<br>The Connection to DB from php ist over PEAR-DB-Class
<br> 
<br>I wrote a postgres function which gets up to 17 parameters such as os, browser, referer and so on
<br>This function tries to update a row in the db/table which matches with the hour of the current datetime
<br>If this returns not found then i do an insert
<br>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
<br>
<br>I called the function with expain analyze, so it showed something 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 undone with 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 doesnt find 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 problem is, 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 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!
<br>I know there are too many questions, but every idea of you guys will help me, thanks
<br>
<br>Thank you so far
<br>
<br>Boris
<br></body></html>


Attachment: unknown_filename
Description: text/html (2.4 KB)

pgsql-performance by date

Next:From: pginfoDate: 2004-01-22 15:31:34
Subject: Re: Trigger performance
Previous:From: pginfoDate: 2004-01-22 09:35:17
Subject: Trigger performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group