Re: [HACKERS] Stored procedure issue

On Dec 2, 2007 7:40 AM, Dragan Zubac <moroncic(at)yahoo(dot)com> wrote:

> Hello
>
> I have a stored procedure which does the billing stuff
> in our system,it works ok,but if I put in
> production,where there is some 5-10 billing events per
> second,the whole database slows down. It won't even
> drop some test table,reindex,vacuum,things which were
> done before in the blink of an eye. If I stop the
> application which calls the procedure,all is back to
> normal.
>
> We didn't implement any special locking mechanism in
> the procedure,all is default. The procedure is
> updating user's balance in table 'users'. On the other
> hand a couple of 'heavy load' table has foreign keys
> pointing to table 'users'.
>
> Is it the matter of concurency and some locking issue
> or maybe the existing of all those foreign keys
> pointing to table 'users',or maybe something else
> which we're not aware at the moment ?

Can you please post your procedure and explain plan of the SQL which the
procedure uses to do the billing stuff . There can be a zillion reasons for
the performance problems you are seeing, but the email does not provide
enough information.

>
> Sincerely
>
> Pera
>
>
>
> ____________________________________________________________________________________
> Be a better sports nut! Let your teams follow you
> with Yahoo Mobile. Try it now.
> http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>

--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar