Hi, the version is Database: MonetDB v11.18.0, I compile from source Jan2014

Thank, Meng ------------------ 原始邮件 ------------------ 发件人: "Martin Kersten";Martin.Kersten@cwi.nl; 发送时间: 2014年4月30日(星期三) 下午5:21 收件人: "users-list"users-list@monetdb.org;

主题: Re: why the same procedure (in shell or in sql) cost so differenttime?

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

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Hi, the elapsed time is 6:30.39, means 6 minutes 30.39 seconds.

Regards, Meng ------------------ Original ------------------ From: "Martin Kersten";Martin.Kersten@cwi.nl; Date: Wed, Apr 30, 2014 05:38 PM To: "users-list"users-list@monetdb.org;

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 .

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";martin@monetdb.org; 发送时间: 2014年4月30日(星期三) 下午5:49 收件人: "Communication channel for MonetDB users"users-list@monetdb.org;

主题: Re: 回复： why the same procedure (in shell or in sql) cost so differenttime?

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";Martin.Kersten@cwi.nl; Date: Wed, Apr 30, 2014 05:38 PM To: "users-list"users-list@monetdb.org; 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

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ě";radovan.biciste@ceosdata.com; 发送时间: 2014年4月30日(星期三) 下午5:32 收件人: "users-list"users-list@monetdb.org;

主题: Re: why the same procedure (in shell or in sql) cost so differenttime?

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