Stored procedure issue

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 12/01/07 20:40, Dragan Zubac 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 ?

Are you using transactions?

Are the tables properly indexed?

Are the queries in the SP using the indexes properly?

Did you do all the testing on a tiny database.

Is the SP written as efficiently? (Think of ways to refactor it in
order to get the same results with less effort.)

- --
Ron Johnson, Jr.
Jefferson LA USA

%SYSTEM-F-FISH, my hovercraft is full of eels
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHUh9nS9HxQb37XmcRAjPTAJ4jRUZUaF+j2KAB3+lBY6A3ROfynACfawWT
0QN026Ncl/Iag2M6E1kfjUg=
=RlXy
-----END PGP SIGNATURE-----

On Dec 2, 2007 7:40 AM, Dragan Zubac <moroncic@yahoo.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

Hello

Please find in attachment stored procedure
(proc_uni.txt),as well as description of tables
involved in calculations.
The idea for procedure is to find longest prefix match
for destination number,try to find it in table
'billing' for particular users,find the price,and
insert message into history and inqueue table,as well
as to decreace the user's balance in table 'users'.
Would it help to put all prefices,prices data in some
sort of cache and let procedure first try to match
with data from cache and if it can't find to try to
get data from table itself from hard disk ?

I'm looking for some solution where this procedure can
operate at higher loads and to leave other parts of
database operational as much as it could.

Sincerely

Pera

--- Usama Dar <munir.usama@gmail.com> wrote:

On Dec 2, 2007 7:40 AM, Dragan Zubac
<moroncic@yahoo.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

____________________________________________________________________________________
Get easy, one-click access to your favorites.
Make Yahoo! your homepage.
http://www.yahoo.com/r/hs

Hello

Here's the stored procedure itself,as well as the
related tables involved in it's calculations.
The idea for procedure is to find longest prefix match
for destination number,try to find it in table
'billing' for particular users,find the price,and
insert message into history and inqueue table,as well
as to decreace the user's balance in table 'users'.
Would it help to put all prefices,prices data in some
sort of cache and let procedure first try to match
with data from cache and if it can't find to try to
get data from table itself from hard disk ?

I'm looking for some solution where this procedure can
operate at higher loads and to leave other parts of
database operational as much as it could.

--Procedure---

create type dajbre as (status int,id bigint);

CREATE OR REPLACE FUNCTION
proc_uni(integer,integer,inet,text,integer,integer,text,integer,integer,
text,int, int,boolean,text) RETURNS setof dajbre AS '

DECLARE

uid alias for $1;
pid alias for $2;
ip_i alias for $3;
s_number alias for $4;
s_ton_i alias for $5;
s_npi_i alias for $6;
d_number alias for $7;
d_ton_i alias for $8;
d_npi_i alias for $9;
mess alias for $10;
dcs_i alias for $11;
esm_i alias for $12;
delivery_i alias for $13;
u_mess_id_i alias for $14;

r dajbre%rowtype;

prefixfound boolean;
prefixprice billing.price%TYPE;
dest_num_len int;
tmp_dest_number text;
tmp_user_bal numeric;
tmp_returnval int;
novi_status int;
tmp_his_id bigint;
tmp_u_mess_id_i text;

begin

dest_num_len := char_length(d_number);
tmp_dest_number := d_number;
prefixfound := false;

while dest_num_len > 0 loop

select into prefixprice price from billing
where u_id=uid and prefix=tmp_dest_number;

if not found then
tmp_dest_number := substring
(tmp_dest_number from 1 for dest_num_len-1);
dest_num_len :=
char_length(tmp_dest_number);
else
prefixfound := true;
exit;
end if;
end loop;

if prefixfound=false then
tmp_returnval :=11;
novi_status :=11;
else if prefixprice = 0 then
tmp_returnval :=11;
novi_status :=50;
else select into tmp_user_bal maxsms-cursms from
users where id=uid;
if tmp_user_bal < prefixprice then
tmp_returnval :=11;
novi_status :=51;
else
tmp_returnval :=0;
end if;
end if;
end if;

if tmp_returnval = 0 then

insert into history
(ip,source,dest,message,dcs,esm,s_ton,s_npi,d_ton,d_npi,u_id,delivery,price,p_id,u_mess_id)
values
(ip_i,s_number,d_number,decode(mess,''base64''),dcs_i,esm_i,s_ton_i,s_npi_i,d_ton_i,d_npi_i,uid,delivery_i,prefixprice,pid,u_mess_id_i);

tmp_his_id := currval(''history_id_seq'');

if pid = 2 then
if u_mess_id_i = 0 then
tmp_u_mess_id_i := '''';
else
tmp_u_mess_id_i := u_mess_id_i;
end if;
else if pid = 3 then
tmp_u_mess_id_i := tmp_his_id ;
end if;
end if;

update history set u_mess_id = tmp_u_mess_id_i where
id = tmp_his_id;
update users set cursms=cursms+ prefixprice where
id=uid;

insert into inqueue(id, u_id) values (tmp_his_id,
uid);

r.status := 0;
r.id := tmp_his_id;
return next r;

else

insert into rejected
(ip,source,dest,message,dcs,esm,s_ton,s_npi,d_ton,d_npi,status,u_id,delivery,u_mess_id)
values
(ip_i,s_number,d_number,decode(mess,''base64''),dcs_i,esm_i,s_ton_i,s_npi_i,d_ton_i,d_npi_i,novi_status,uid,delivery_i,u_mess_id_i);

r.status := 11;
r.id := 0;
return next r;

end if;

return;

end;

' language 'plpgsql';
---------------------

---Billing table-----
Table "public.billing"

Column | Type |
Modifiers
------------+----------------+------------------------------------------------------
id | integer | not null default
nextval('billing_id_seq'::regclass)
u_id | integer | not null
prefix | text |
operator | integer |
price | numeric(20,10) |
comment | text |
new_prefix | boolean | default false
Indexes:
"billing_pkey" PRIMARY KEY, btree (id)
"bil_uid" btree (u_id)
Foreign-key constraints:
"$1" FOREIGN KEY (u_id) REFERENCES users(id)
"$2" FOREIGN KEY ("operator") REFERENCES
operators(id)
---------------------
----Users table------
Column | Type |
Modifiers
--------------------+----------------+----------------------------------------------------
id | integer | not null
default nextval('users_id_seq'::regclass)
username | text | not null
password | text | not null
name | text |
email | text |
mobile | text |
phone | text |
company | text |
ownnum | text |
reseller | boolean | default false
reseller_id | integer | default 1
url | bytea | not null
maxsmpp | smallint | default 2
maxucp | smallint | default 1
http_enabled | boolean | default true
smpp_enabled | boolean | default true
ucp_enabled | boolean | default true
enabled | boolean | default true
comment | text |
priority | smallint | default 1
cursms | numeric(20,10) | default 0
maxsms | numeric(20,10) | default 0
address | text |
fax | text |
techname | text |
techemail | text |
techphone | text |
finname | text |
finemail | text |
finphone | text |
url_u | text |
send_daily_balance | boolean | default true
currency | integer | default 1
country | integer | default 0
em_email | text |
em_phone | text |
log | boolean | default false
postpay | boolean | default false
sale_category | text |
poen | numeric(20,10) |
commission | numeric(20,10) |
desktop | boolean | default false
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_username_key" UNIQUE, btree (username)
Foreign-key constraints:
"users_sale_category_fkey" FOREIGN KEY
(sale_category) REFERENCES sale_categories(id)
-----------------------------

----Inqueue table------------
Table "public.inqueue"
Column | Type | Modifiers
--------+-----------------------------+---------------
id | bigint | not null
time | timestamp without time zone | default now()
u_id | integer |
Indexes:
"inqueue_date" btree ("time")
"inqueue_idx" btree (id)
Foreign-key constraints:
"$3" FOREIGN KEY (u_id) REFERENCES users(id)

--------------------------------

----------History table---------
Table
"public.history"

Column | Type |
Modifiers
-----------+-----------------------------+--------------------------------------------------------------
id | bigint | not null
default nextval(('history_id_seq'::text)::regclass)
date | date | default
now()
time | time without time zone | default
now()
source | text | not null
dest | text | not null
message | bytea |
dcs | integer | default 0
esm | integer | default 0
s_ton | smallint | default 1
s_npi | smallint | default 1
d_ton | smallint | default 1
d_npi | smallint | default 1
status | integer | default -1
u_id | integer |
mess_id | text |
d_date | timestamp without time zone |
provider | integer | default -1
delivery | boolean | default
true
p_id | integer |
msg_type | integer | default 1
ip | inet |
u_mess_id | text |
priority | smallint | default 2
price | numeric(20,10) |
Indexes:
"hist_pkey" PRIMARY KEY, btree (id)
"hist_date" btree (date)
"hist_dest" btree (dest)
"hist_dr" btree (date, mess_id, provider)
"hist_mess_id" btree (mess_id)
"hist_uid_date" btree (u_id, date)
"hist_users" btree (u_id)
Foreign-key constraints:
"hist_msgtype" FOREIGN KEY (msg_type) REFERENCES
msg_type(id)
"hist_pid" FOREIGN KEY (p_id) REFERENCES
protocols(id)
"hist_provider" FOREIGN KEY (provider) REFERENCES
providers(id)
"hist_uid1" FOREIGN KEY (u_id) REFERENCES
users(id)

--------------------------------
-----Rejected table--------------
Table
"public.rejected"

Column | Type |
Modifiers
-----------+------------------------+-----------------------------------------------------
id | bigint |
date | date | default
('now'::text)::date
time | time without time zone | default
('now'::text)::time(6) with time zone
source | text |
dest | text |
message | bytea |
dcs | integer |
esm | integer |
s_ton | smallint |
s_npi | smallint |
d_ton | smallint |
d_npi | smallint |
status | integer |
u_id | integer |
delivery | boolean |
p_id | integer |
ip | inet |
u_mess_id | text |
ajdi | bigint | not null default
nextval('rejected_ajdi'::regclass)
Indexes:
"rejected_pkey" PRIMARY KEY, btree (ajdi)
"rejected_temp_date" btree (date)

---------------------------------

Sincerely

Pera

--- Usama Dar <munir.usama@gmail.com> wrote:

On Dec 2, 2007 7:40 AM, Dragan Zubac
<moroncic@yahoo.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

____________________________________________________________________________________
Be a better pen pal.
Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/

Hello

What I have noticed is that when I don't use procedure
at all,there's only 2-5 locks in pg_locks,after I
start application which uses stored procedure the
number in pg_locks increase rapidly to steady 75 even
to 130 at certain moments.

Any clue why procedure usage might increase locks so
heavily ?

Sincerely

--- Dragan Zubac <moroncic@yahoo.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 ?

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

____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

Hello

On 03/12/2007, Dragan Zubac <moroncic@yahoo.com> wrote:

Hello

What I have noticed is that when I don't use procedure
at all,there's only 2-5 locks in pg_locks,after I
start application which uses stored procedure the
number in pg_locks increase rapidly to steady 75 even
to 130 at certain moments.

Any clue why procedure usage might increase locks so
heavily ?

Sincerely

upgrade to 8.2? There is shared lock and there are less problems with
locks. But I am not sure if this solves your problem. General
protection before locks is all things with maximal speed. Are your
queris well optimazed?

Regards
Pavel Stehule

Show quoted text

--- Dragan Zubac <moroncic@yahoo.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 ?

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

____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly