Обсуждение: Stored procedure issue
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
-----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:
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.
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 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';
Вложения
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 > --- 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 >