why the same procedure (in shell or in sql) cost so different time?
Hi All, I want to execute a batch of small sql stored procedures, for convenience, i put them totally in another big stored procedure: gwac_uniquecatalog, it cost 58 minutes: #!/bin/bash time mclient -d mydb -s "CALL gwac_uniquecatalog(99);" real 58m3.518s The time is so long that I tried to execute the small stored procedures one by one in SHELL, it only cost 6.5 minutes /usr/bin/time ./gwac_uniquecatalog.sh 99 2.82user 3.25system 6:30.39elapsed 1%CPU (0avgtext+0avgdata 10880maxresident)k 0inputs+0outputs (0major+980011minor)pagefaults 0swaps my question is why did this dramatic difference happen and how to reduce the time of big stored procedure? =========================================================== the content of the big stored procedure: gwac_uniquecatalog is CREATE PROCEDURE gwac_uniquecatalog(imgid int) BEGIN DECLARE imgid_t int; SET imgid_t =1; CALL insert_1_to_1_assoc(imgid_t); --对第一幅图 only insert into assoc is enough SET imgid_t = imgid_t +1; WHILE (imgid_t<=imgid) DO CALL insert_tempuniquecatalog(imgid_t,10.0); --10 is match radius 对第二幅图及以后的图 CALL find_n_to_m(); CALL insert_1_to_n_unique(); CALL insert_new_1_to_n_assoc(); CALL insert_1_to_n_assoc(); CALL delete_1_to_n_inactive_assoc(); CALL flag_1_to_n_inactive_uniq(); CALL flag_1_to_n_inactive_tempuniq(); CALL insert_1_to_1_assoc(imgid_t); CALL update_1_to_1_uniq(); CALL insert_new_uniq(imgid_t); CALL insert_new_assoc(imgid_t); SET imgid_t = imgid_t +1; END WHILE; END; =========================================== the SHELL code is: #!/bin/bash imgid_t=1 /usr/bin/time -f %e mclient -d mydb -s "CALL insert_1_to_1_assoc(imgid_t -le $1 ] do /usr/bin/time -f %e mclient -d mydb -s " CALL insert_tempuniquecatalog($imgid_t,10.0);" mclient -d mydb -s " CALL find_n_to_m();" mclient -d mydb -s " CALL insert_1_to_n_unique(); " mclient -d mydb -s " CALL insert_new_1_to_n_assoc();" mclient -d mydb -s " CALL insert_1_to_n_assoc();" mclient -d mydb -s " CALL delete_1_to_n_inactive_assoc();" mclient -d mydb -s " CALL flag_1_to_n_inactive_uniq();" mclient -d mydb -s " CALL flag_1_to_n_inactive_tempuniq();" mclient -d mydb -s " CALL insert_1_to_1_assoc($imgid_t);" mclient -d mydb -s " CALL update_1_to_1_uniq();" mclient -d mydb -s " CALL insert_new_uniq($imgid_t);" mclient -d mydb -s " CALL insert_new_assoc(imgid_t ((++imgid_t)) done Thanks very much! Best regards, Meng
Hi What are the specs of your MonetDB version and platform? Beware about the difference between hot and cold query runs. A TRACE might indicate where the costs are. regards, Martin On 30/04/14 10:59, integrity wrote:
Hi All, I want to execute a batch of small sql stored procedures, for convenience, i put them totally in another big stored procedure: gwac_uniquecatalog, it cost 58 minutes: #!/bin/bash time mclient -d mydb -s "CALL gwac_uniquecatalog(99);" real 58m3.518s
The time is so long that I tried to execute the small stored procedures one by one in SHELL, it only cost 6.5 minutes /usr/bin/time ./gwac_uniquecatalog.sh 99 2.82user 3.25system 6:30.39elapsed 1%CPU (0avgtext+0avgdata 10880maxresident)k 0inputs+0outputs (0major+980011minor)pagefaults 0swaps
my question is why did this dramatic difference happen and how to reduce the time of big stored procedure? =========================================================== the content of the big stored procedure: gwac_uniquecatalog is CREATE PROCEDURE gwac_uniquecatalog(imgid int) BEGIN DECLARE imgid_t int; SET imgid_t =1; CALL insert_1_to_1_assoc(imgid_t); --对第一幅图 only insert into assoc is enough SET imgid_t = imgid_t +1;
WHILE (imgid_t<=imgid) DO CALL insert_tempuniquecatalog(imgid_t,10.0); --10 is match radius 对第二幅图及以后的图 CALL find_n_to_m();
CALL insert_1_to_n_unique(); CALL insert_new_1_to_n_assoc(); CALL insert_1_to_n_assoc(); CALL delete_1_to_n_inactive_assoc(); CALL flag_1_to_n_inactive_uniq(); CALL flag_1_to_n_inactive_tempuniq();
CALL insert_1_to_1_assoc(imgid_t); CALL update_1_to_1_uniq();
CALL insert_new_uniq(imgid_t); CALL insert_new_assoc(imgid_t); SET imgid_t = imgid_t +1; END WHILE; END; =========================================== the SHELL code is: #!/bin/bash imgid_t=1
/usr/bin/time -f %e mclient -d mydb -s "CALL insert_1_to_1_assoc(imgid_t -le $1 ] do /usr/bin/time -f %e mclient -d mydb -s " CALL insert_tempuniquecatalog($imgid_t,10.0);" mclient -d mydb -s " CALL find_n_to_m();" mclient -d mydb -s " CALL insert_1_to_n_unique(); " mclient -d mydb -s " CALL insert_new_1_to_n_assoc();" mclient -d mydb -s " CALL insert_1_to_n_assoc();" mclient -d mydb -s " CALL delete_1_to_n_inactive_assoc();" mclient -d mydb -s " CALL flag_1_to_n_inactive_uniq();" mclient -d mydb -s " CALL flag_1_to_n_inactive_tempuniq();" mclient -d mydb -s " CALL insert_1_to_1_assoc($imgid_t);" mclient -d mydb -s " CALL update_1_to_1_uniq();" mclient -d mydb -s " CALL insert_new_uniq($imgid_t);" mclient -d mydb -s " CALL insert_new_assoc(imgid_t
((++imgid_t)) done
Thanks very much! Best regards,
Meng _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi,
the version is Database: MonetDB v11.18.0, I compile from source Jan2014
Thank, Meng
------------------ 原始邮件 ------------------
发件人: "Martin Kersten";
Hi All, I want to execute a batch of small sql stored procedures, for convenience, i put them totally in another big stored procedure: gwac_uniquecatalog, it cost 58 minutes: #!/bin/bash time mclient -d mydb -s "CALL gwac_uniquecatalog(99);" real 58m3.518s
The time is so long that I tried to execute the small stored procedures one by one in SHELL, it only cost 6.5 minutes /usr/bin/time ./gwac_uniquecatalog.sh 99 2.82user 3.25system 6:30.39elapsed 1%CPU (0avgtext+0avgdata 10880maxresident)k 0inputs+0outputs (0major+980011minor)pagefaults 0swaps
my question is why did this dramatic difference happen and how to reduce the time of big stored procedure? =========================================================== the content of the big stored procedure: gwac_uniquecatalog is CREATE PROCEDURE gwac_uniquecatalog(imgid int) BEGIN DECLARE imgid_t int; SET imgid_t =1; CALL insert_1_to_1_assoc(imgid_t); --对第一幅图 only insert into assoc is enough SET imgid_t = imgid_t +1;
WHILE (imgid_t<=imgid) DO CALL insert_tempuniquecatalog(imgid_t,10.0); --10 is match radius 对第二幅图及以后的图 CALL find_n_to_m();
CALL insert_1_to_n_unique(); CALL insert_new_1_to_n_assoc(); CALL insert_1_to_n_assoc(); CALL delete_1_to_n_inactive_assoc(); CALL flag_1_to_n_inactive_uniq(); CALL flag_1_to_n_inactive_tempuniq();
CALL insert_1_to_1_assoc(imgid_t); CALL update_1_to_1_uniq();
CALL insert_new_uniq(imgid_t); CALL insert_new_assoc(imgid_t); SET imgid_t = imgid_t +1; END WHILE; END; =========================================== the SHELL code is: #!/bin/bash imgid_t=1
/usr/bin/time -f %e mclient -d mydb -s "CALL insert_1_to_1_assoc(imgid_t -le $1 ] do /usr/bin/time -f %e mclient -d mydb -s " CALL insert_tempuniquecatalog($imgid_t,10.0);" mclient -d mydb -s " CALL find_n_to_m();" mclient -d mydb -s " CALL insert_1_to_n_unique(); " mclient -d mydb -s " CALL insert_new_1_to_n_assoc();" mclient -d mydb -s " CALL insert_1_to_n_assoc();" mclient -d mydb -s " CALL delete_1_to_n_inactive_assoc();" mclient -d mydb -s " CALL flag_1_to_n_inactive_uniq();" mclient -d mydb -s " CALL flag_1_to_n_inactive_tempuniq();" mclient -d mydb -s " CALL insert_1_to_1_assoc($imgid_t);" mclient -d mydb -s " CALL update_1_to_1_uniq();" mclient -d mydb -s " CALL insert_new_uniq($imgid_t);" mclient -d mydb -s " CALL insert_new_assoc(imgid_t
((++imgid_t)) done
Thanks very much! Best regards,
Meng _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
On 30/04/14 11:27, integrity wrote:
Hi, ...
On 30/04/14 10:59, integrity wrote:
Hi All, I want to execute a batch of small sql stored procedures, for convenience, i put them totally in another big stored procedure: gwac_uniquecatalog, it cost 58 minutes: #!/bin/bash time mclient -d mydb -s "CALL gwac_uniquecatalog(99);" real 58m3.518s
The time is so long that I tried to execute the small stored
procedures one by one in SHELL, it only cost 6.5 minutes
/usr/bin/time ./gwac_uniquecatalog.sh 99 2.82user 3.25system 6:30.39elapsed 1%CPU (0avgtext+0avgdata 10880maxresident)k 0inputs+0outputs (0major+980011minor)pagefaults 0swaps
What were the numbers of /usr/bin/time for the long run?
Hi,
the elapsed time is 6:30.39, means 6 minutes 30.39 seconds.
Regards, Meng
------------------ Original ------------------
From: "Martin Kersten";
Hi, ...
On 30/04/14 10:59, integrity wrote:
Hi All, I want to execute a batch of small sql stored procedures, for convenience, i put them totally in another big stored procedure: gwac_uniquecatalog, it cost 58 minutes: #!/bin/bash time mclient -d mydb -s "CALL gwac_uniquecatalog(99);" real 58m3.518s
The time is so long that I tried to execute the small stored
procedures one by one in SHELL, it only cost 6.5 minutes
/usr/bin/time ./gwac_uniquecatalog.sh 99 2.82user 3.25system 6:30.39elapsed 1%CPU (0avgtext+0avgdata 10880maxresident)k 0inputs+0outputs (0major+980011minor)pagefaults 0swaps
What were the numbers of /usr/bin/time for the long run?
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list .
On 30/04/14 11:43, integrity wrote:
Hi, the elapsed time is 6:30.39, means 6 minutes 30.39 seconds. No, you have to look at the inputs/output and pagefaults numbers in both runs. This will show if you run hot/cold
Having concurrent load on your machine also may have an impact. For example, browsers can take a substantial amount of memory and cpu away from database processing. To understand this, you can look at the output of 'uptime' and 'top'
Regards, Meng ------------------ Original ------------------ *From: * "Martin Kersten";
; *Date: * Wed, Apr 30, 2014 05:38 PM *To: * "users-list" ; *Subject: * Re: 回复: why the same procedure (in shell or in sql) cost so differenttime? On 30/04/14 11:27, integrity wrote:
Hi, ...
On 30/04/14 10:59, integrity wrote:
Hi All, I want to execute a batch of small sql stored procedures, for convenience, i put them totally in another big stored procedure: gwac_uniquecatalog, it cost 58 minutes: #!/bin/bash time mclient -d mydb -s "CALL gwac_uniquecatalog(99);" real 58m3.518s
The time is so long that I tried to execute the small stored
procedures one by one in SHELL, it only cost 6.5 minutes
/usr/bin/time ./gwac_uniquecatalog.sh 99 2.82user 3.25system 6:30.39elapsed 1%CPU (0avgtext+0avgdata 10880maxresident)k 0inputs+0outputs (0major+980011minor)pagefaults 0swaps
What were the numbers of /usr/bin/time for the long run?
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list .
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
now I am reruning the big procedure :
nohup /usr/bin/time mclient -d mydb -s "CALL gwac_uniquecatalog(99)" >log0430-1800.txt 2>&1 &
I "snapshot" the top command result as follows:
top - 18:00:54 up 26 days, 21:08, 11 users, load average: 0.99, 0.87, 0.67
Tasks: 277 total, 2 running, 274 sleeping, 1 stopped, 0 zombie
Cpu(s): 12.5%us, 0.1%sy, 0.0%ni, 86.9%id, 0.5%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 16310048k total, 2147148k used, 14162900k free, 272480k buffers
Swap: 33554424k total, 452k used, 33553972k free, 1057720k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
21916 mengw 20 0 1613m 136m 12m S 100.8 0.9 8:41.01 mserver5
1 root 20 0 19356 1444 1132 S 0.0 0.0 0:03.42 init
2 root 20 0 0 0 0 S 0.0 0.0 0:01.61 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 0:02.32 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 0:02.02 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
6 root RT 0 0 0 0 S 0.0 0.0 0:01.55 watchdog/0
7 root RT 0 0 0 0 S 0.0 0.0 0:00.72 migration/1
8 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/1
9 root 20 0 0 0 0 S 0.0 0.0 0:02.10 ksoftirqd/1
mserver5 use cpu 100.8% percent, so there is no concurrent process.
Regards,
Meng
------------------ 原始邮件 ------------------
发件人: "Martin Kersten";
Hi, I'm not an expert. But I would suspect transaction processing could cause it. The big procedure is running in one transaction everything, the shell script commits every execution of a procedure. Radovan On 04/30/2014 10:59 AM, integrity wrote:
Hi All, I want to execute a batch of small sql stored procedures, for convenience, i put them totally in another big stored procedure: gwac_uniquecatalog, it cost 58 minutes: #!/bin/bash time mclient -d mydb -s "CALL gwac_uniquecatalog(99);" real 58m3.518s
The time is so long that I tried to execute the small stored procedures one by one in SHELL, it only cost 6.5 minutes /usr/bin/time ./gwac_uniquecatalog.sh 99 2.82user 3.25system 6:30.39elapsed 1%CPU (0avgtext+0avgdata 10880maxresident)k 0inputs+0outputs (0major+980011minor)pagefaults 0swaps
my question is why did this dramatic difference happen and how to reduce the time of big stored procedure? =========================================================== the content of the big stored procedure: gwac_uniquecatalog is CREATE PROCEDURE gwac_uniquecatalog(imgid int) BEGIN DECLARE imgid_t int; SET imgid_t =1; CALL insert_1_to_1_assoc(imgid_t); --对第一幅图 only insert into assoc is enough SET imgid_t = imgid_t +1;
WHILE (imgid_t<=imgid) DO CALL insert_tempuniquecatalog(imgid_t,10.0); --10 is match radius 对第二幅图及以后的图 CALL find_n_to_m();
CALL insert_1_to_n_unique(); CALL insert_new_1_to_n_assoc(); CALL insert_1_to_n_assoc(); CALL delete_1_to_n_inactive_assoc(); CALL flag_1_to_n_inactive_uniq(); CALL flag_1_to_n_inactive_tempuniq();
CALL insert_1_to_1_assoc(imgid_t); CALL update_1_to_1_uniq();
CALL insert_new_uniq(imgid_t); CALL insert_new_assoc(imgid_t); SET imgid_t = imgid_t +1; END WHILE; END; =========================================== the SHELL code is: #!/bin/bash imgid_t=1
/usr/bin/time -f %e mclient -d mydb -s "CALL insert_1_to_1_assoc(imgid_t -le $1 ] do /usr/bin/time -f %e mclient -d mydb -s " CALL insert_tempuniquecatalog($imgid_t,10.0);" mclient -d mydb -s " CALL find_n_to_m();" mclient -d mydb -s " CALL insert_1_to_n_unique(); " mclient -d mydb -s " CALL insert_new_1_to_n_assoc();" mclient -d mydb -s " CALL insert_1_to_n_assoc();" mclient -d mydb -s " CALL delete_1_to_n_inactive_assoc();" mclient -d mydb -s " CALL flag_1_to_n_inactive_uniq();" mclient -d mydb -s " CALL flag_1_to_n_inactive_tempuniq();" mclient -d mydb -s " CALL insert_1_to_1_assoc($imgid_t);" mclient -d mydb -s " CALL update_1_to_1_uniq();" mclient -d mydb -s " CALL insert_new_uniq($imgid_t);" mclient -d mydb -s " CALL insert_new_assoc(imgid_t
((++imgid_t)) done
Thanks very much! Best regards,
Meng _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- __________________________ Radovan Bičiště ceos data s.r.o. Pouchovská 153 500 03 Hradec Králové Czech Republic mobil CZ: +420 601 563 014 skype: rbiciste
Hi,
I guess so, because when the procedure is running, I was doing "select count(*) from uniquecatalog" from another mclient, the result is always the same :3654 rows, while in shell, the result is increasing every seconds.
Meng
------------------ 原始邮件 ------------------
发件人: "Radovan Bičiště";
Hi All, I want to execute a batch of small sql stored procedures, for convenience, i put them totally in another big stored procedure: gwac_uniquecatalog, it cost 58 minutes: #!/bin/bash time mclient -d mydb -s "CALL gwac_uniquecatalog(99);" real 58m3.518s
The time is so long that I tried to execute the small stored procedures one by one in SHELL, it only cost 6.5 minutes /usr/bin/time ./gwac_uniquecatalog.sh 99 2.82user 3.25system 6:30.39elapsed 1%CPU (0avgtext+0avgdata 10880maxresident)k 0inputs+0outputs (0major+980011minor)pagefaults 0swaps
my question is why did this dramatic difference happen and how to reduce the time of big stored procedure? =========================================================== the content of the big stored procedure: gwac_uniquecatalog is CREATE PROCEDURE gwac_uniquecatalog(imgid int) BEGIN DECLARE imgid_t int; SET imgid_t =1; CALL insert_1_to_1_assoc(imgid_t); --对第一幅图 only insert into assoc is enough SET imgid_t = imgid_t +1;
WHILE (imgid_t<=imgid) DO CALL insert_tempuniquecatalog(imgid_t,10.0); --10 is match radius 对第二幅图及以后的图 CALL find_n_to_m();
CALL insert_1_to_n_unique(); CALL insert_new_1_to_n_assoc(); CALL insert_1_to_n_assoc(); CALL delete_1_to_n_inactive_assoc(); CALL flag_1_to_n_inactive_uniq(); CALL flag_1_to_n_inactive_tempuniq();
CALL insert_1_to_1_assoc(imgid_t); CALL update_1_to_1_uniq();
CALL insert_new_uniq(imgid_t); CALL insert_new_assoc(imgid_t); SET imgid_t = imgid_t +1; END WHILE; END; =========================================== the SHELL code is: #!/bin/bash imgid_t=1
/usr/bin/time -f %e mclient -d mydb -s "CALL insert_1_to_1_assoc(imgid_t -le $1 ] do /usr/bin/time -f %e mclient -d mydb -s " CALL insert_tempuniquecatalog($imgid_t,10.0);" mclient -d mydb -s " CALL find_n_to_m();" mclient -d mydb -s " CALL insert_1_to_n_unique(); " mclient -d mydb -s " CALL insert_new_1_to_n_assoc();" mclient -d mydb -s " CALL insert_1_to_n_assoc();" mclient -d mydb -s " CALL delete_1_to_n_inactive_assoc();" mclient -d mydb -s " CALL flag_1_to_n_inactive_uniq();" mclient -d mydb -s " CALL flag_1_to_n_inactive_tempuniq();" mclient -d mydb -s " CALL insert_1_to_1_assoc($imgid_t);" mclient -d mydb -s " CALL update_1_to_1_uniq();" mclient -d mydb -s " CALL insert_new_uniq($imgid_t);" mclient -d mydb -s " CALL insert_new_assoc(imgid_t
((++imgid_t)) done
Thanks very much! Best regards,
Meng _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- __________________________ Radovan Bičiště ceos data s.r.o. Pouchovská 153 500 03 Hradec Králové Czech Republic mobil CZ: +420 601 563 014 skype: rbiciste _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (4)
-
integrity
-
Martin Kersten
-
Martin Kersten
-
Radovan Bičiště