Thread: background jobs

background jobs

From
Kumar S
Date:
Hello all,
 I am using a laptop and some of my .sql files with
insert statements (300K insert statements) take very
long time.

I user

database=> \i /home/user/../../../insert.sql

this takes a long time. and if i shutdown my laptop,
the server connection is lost . I am using Putty to
connect to my server.

Could any one help to setup a background job where I
can start a job and forget until the next morning to
see that the job is finished.

Should it be something like the following:
myserver$ psql -d mydb -d node2|\i /insert.sql &>

this command is WRONG.  Can any one help me with a
correct one.

Thank you
K

________________________________________________________________________
Yahoo! India Matrimony: Find your partner online. http://yahoo.shaadi.com/india-matrimony/

Re: background jobs

From
Michael Fuhr
Date:
On Sat, Mar 05, 2005 at 09:28:57PM -0800, Kumar S wrote:

>  I am using a laptop and some of my .sql files with
> insert statements (300K insert statements) take very
> long time.

How long is "very long"?  Have you seen the "Performance Tips" chapter
in the documentation, in particular the "Populating a Database" section?

http://www.postgresql.org/docs/8.0/interactive/populate.html

Are you using transactions?  Wrapping a lot of INSERTs in a transaction
should be significantly faster than doing each INSERT as its own
transaction.

Have you considered using COPY instead of INSERT?  COPY should be
significantly faster than INSERT for bulk loads.

> Could any one help to setup a background job where I
> can start a job and forget until the next morning to
> see that the job is finished.
>
> Should it be something like the following:
> myserver$ psql -d mydb -d node2|\i /insert.sql &>

See the psql documentation and your shell's documentation on how to run
a background job and redirect its output; you might also need to use
"nohup".  For example, the following might work in Bourne-like shells
(bash, sh, ksh, etc.):

nohup psql -d mydb -f insert.sql > insert.log 2>&1 &

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: background jobs

From
Kumar S
Date:
Hi ,

thank you very much for your helpful comments.

I cannot use COPY because I am using a many SELECT
statements in INSERT statements.

also one more clarification:

is the syntax correct for a transaction:


BEGIN TRANSACTION;
INSERT .........
INSERT.......
INSERT.......
100 k LINES

in case if want to rollback:

# ROLLBACK;


Thank you.





--- Michael Fuhr <mike@fuhr.org> wrote:
> On Sat, Mar 05, 2005 at 09:28:57PM -0800, Kumar S
> wrote:
>
> >  I am using a laptop and some of my .sql files
> with
> > insert statements (300K insert statements) take
> very
> > long time.
>
> How long is "very long"?  Have you seen the
> "Performance Tips" chapter
> in the documentation, in particular the "Populating
> a Database" section?
>
>
http://www.postgresql.org/docs/8.0/interactive/populate.html
>
> Are you using transactions?  Wrapping a lot of
> INSERTs in a transaction
> should be significantly faster than doing each
> INSERT as its own
> transaction.
>
> Have you considered using COPY instead of INSERT?
> COPY should be
> significantly faster than INSERT for bulk loads.
>
> > Could any one help to setup a background job where
> I
> > can start a job and forget until the next morning
> to
> > see that the job is finished.
> >
> > Should it be something like the following:
> > myserver$ psql -d mydb -d node2|\i /insert.sql &>
>
> See the psql documentation and your shell's
> documentation on how to run
> a background job and redirect its output; you might
> also need to use
> "nohup".  For example, the following might work in
> Bourne-like shells
> (bash, sh, ksh, etc.):
>
> nohup psql -d mydb -f insert.sql > insert.log 2>&1 &
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Re: background jobs

From
"Sean Davis"
Date:
Did you want to give us a sample of what you are trying to do?  Example
table structures, your INSERT statement, and an EXPLAIN on one of them?  If
you are using selects, it could be as simple as indexing some columns to
make this faster....

Sean

----- Original Message -----
From: "Kumar S" <ps_postgres@yahoo.com>
To: "Michael Fuhr" <mike@fuhr.org>
Cc: <pgsql-novice@postgresql.org>
Sent: Sunday, March 06, 2005 11:05 AM
Subject: Re: [NOVICE] background jobs


> Hi ,
>
> thank you very much for your helpful comments.
>
> I cannot use COPY because I am using a many SELECT
> statements in INSERT statements.
>
> also one more clarification:
>
> is the syntax correct for a transaction:
>
>
> BEGIN TRANSACTION;
> INSERT .........
> INSERT.......
> INSERT.......
> 100 k LINES
>
> in case if want to rollback:
>
> # ROLLBACK;
>
>
> Thank you.
>
>
>
>
>
> --- Michael Fuhr <mike@fuhr.org> wrote:
>> On Sat, Mar 05, 2005 at 09:28:57PM -0800, Kumar S
>> wrote:
>>
>> >  I am using a laptop and some of my .sql files
>> with
>> > insert statements (300K insert statements) take
>> very
>> > long time.
>>
>> How long is "very long"?  Have you seen the
>> "Performance Tips" chapter
>> in the documentation, in particular the "Populating
>> a Database" section?
>>
>>
> http://www.postgresql.org/docs/8.0/interactive/populate.html
>>
>> Are you using transactions?  Wrapping a lot of
>> INSERTs in a transaction
>> should be significantly faster than doing each
>> INSERT as its own
>> transaction.
>>
>> Have you considered using COPY instead of INSERT?
>> COPY should be
>> significantly faster than INSERT for bulk loads.
>>
>> > Could any one help to setup a background job where
>> I
>> > can start a job and forget until the next morning
>> to
>> > see that the job is finished.
>> >
>> > Should it be something like the following:
>> > myserver$ psql -d mydb -d node2|\i /insert.sql &>
>>
>> See the psql documentation and your shell's
>> documentation on how to run
>> a background job and redirect its output; you might
>> also need to use
>> "nohup".  For example, the following might work in
>> Bourne-like shells
>> (bash, sh, ksh, etc.):
>>
>> nohup psql -d mydb -f insert.sql > insert.log 2>&1 &
>>
>> --
>> Michael Fuhr
>> http://www.fuhr.org/~mfuhr/
>>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>



Re: background jobs

From
Eduardo Vázquez Rodríguez
Date:
What I understand is that that you want to insert to the database,
moreover leaving the program running without attention. I recomend that
you use the at command. I use it for inserting during the night, even
turning off my laptop and the process still run even if the parent --
the putty session -- dies.

bash$at -m now /~/script
 >ctrl + d --aply
bash$

you can turn off your laptop and the script run as a background job
openning a different bash session "internally"

Hope can help you

Eduardo

Kumar S wrote:

>Hello all,
> I am using a laptop and some of my .sql files with
>insert statements (300K insert statements) take very
>long time.
>
>I user
>
>database=> \i /home/user/../../../insert.sql
>
>this takes a long time. and if i shutdown my laptop,
>the server connection is lost . I am using Putty to
>connect to my server.
>
>Could any one help to setup a background job where I
>can start a job and forget until the next morning to
>see that the job is finished.
>
>Should it be something like the following:
>myserver$ psql -d mydb -d node2|\i /insert.sql &>
>
>this command is WRONG.  Can any one help me with a
>correct one.
>
>Thank you
>K
>
>________________________________________________________________________
>Yahoo! India Matrimony: Find your partner online. http://yahoo.shaadi.com/india-matrimony/
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>

--
If I have seen further it is by standing on the shoulders of the giants
Isaac Newton