Обсуждение: Running concurrent txns and measuring the timings in Postgres
Hi,
Is there a standard procedure to execute two or more concurrent txns at the same time? I understand that if we want to run concurrent txns, we need to execute them from different psql sessions. But how do we make sure that they begin execution almost at the same time.
Also, I'm interested in measuring the time taken across all executing txns, i.e. the time from the start of the earliest txns till the end of the last txn.
Best,
-SB
On 7/24/19 9:54 AM, Souvik Bhattacherjee wrote: > Hi, > > Is there a standard procedure to execute two or more concurrent txns at > the same time? I understand that if we want to run concurrent txns, we > need to execute them from different psql sessions. But how do we make > sure that they begin execution almost at the same time. Well different sessions be they psql or some other client. That would be the difficulty, determining what is submitting the transaction. > > Also, I'm interested in measuring the time taken across all executing > txns, i.e. the time from the start of the earliest txns till the end of > the last txn. It would help to know what problem you are trying to solve? > > Best, > -SB -- Adrian Klaver adrian.klaver@aklaver.com
> It would help to know what problem you are trying to solve?
Multiple txns are inserting tuples into a table concurrently. Wanted to measure
the total time taken to complete the insertion process. Some txns overlap with
others on the tuples they insert. Duplicate tuples are not inserted.
On Wed, Jul 24, 2019 at 1:58 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 7/24/19 9:54 AM, Souvik Bhattacherjee wrote:
> Hi,
>
> Is there a standard procedure to execute two or more concurrent txns at
> the same time? I understand that if we want to run concurrent txns, we
> need to execute them from different psql sessions. But how do we make
> sure that they begin execution almost at the same time.
Well different sessions be they psql or some other client. That would be
the difficulty, determining what is submitting the transaction.
>
> Also, I'm interested in measuring the time taken across all executing
> txns, i.e. the time from the start of the earliest txns till the end of
> the last txn.
It would help to know what problem you are trying to solve?
>
> Best,
> -SB
--
Adrian Klaver
adrian.klaver@aklaver.com
On Jul 24, 2019, at 1:22 PM, Souvik Bhattacherjee <kivuosb@gmail.com> wrote:> It would help to know what problem you are trying to solve?Multiple txns are inserting tuples into a table concurrently. Wanted to measurethe total time taken to complete the insertion process. Some txns overlap withothers on the tuples they insert. Duplicate tuples are not inserted.
On 7/24/19 12:22 PM, Souvik Bhattacherjee wrote: > > It would help to know what problem you are trying to solve? > > Multiple txns are inserting tuples into a table concurrently. Wanted to > measure > the total time taken to complete the insertion process. Some txns > overlap with > others on the tuples they insert. Duplicate tuples are not inserted. The duplicate elimination is being handled by ON CONFLICT or some custom process in the code generating the transactions? If the transactions are being created from a single app/script could you not just use 'timing' to mark the beginning of the transactions and the end and record that somewhere(db table and/or file)? > > On Wed, Jul 24, 2019 at 1:58 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 7/24/19 9:54 AM, Souvik Bhattacherjee wrote: > > Hi, > > > > Is there a standard procedure to execute two or more concurrent > txns at > > the same time? I understand that if we want to run concurrent > txns, we > > need to execute them from different psql sessions. But how do we > make > > sure that they begin execution almost at the same time. > > Well different sessions be they psql or some other client. That > would be > the difficulty, determining what is submitting the transaction. > > > > > Also, I'm interested in measuring the time taken across all > executing > > txns, i.e. the time from the start of the earliest txns till the > end of > > the last txn. > > It would help to know what problem you are trying to solve? > > > > > Best, > > -SB > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
> The duplicate elimination is being handled by ON CONFLICT or some custom
> process in the code generating the transactions?
Yes, we used ON CONFLICT for that. Thanks btw.
> If the transactions are being created from a single app/script could you
> not just use 'timing' to mark the beginning of the transactions and the
> end and record that somewhere(db table and/or file)?
> not just use 'timing' to mark the beginning of the transactions and the
> end and record that somewhere(db table and/or file)?
So did you mean to say that I need to get the timestamps of the beginning/end
of the txn since \timing only produces elapsed time? Surely that would solve the
problem but I'm not sure how to get that done in Postgres.
I wanted to check to see if there are simpler ways to get this done in Postgres
before trying out something similar to Rob's suggestion or yours.
On Wed, Jul 24, 2019 at 4:12 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 7/24/19 12:22 PM, Souvik Bhattacherjee wrote:
> > It would help to know what problem you are trying to solve?
>
> Multiple txns are inserting tuples into a table concurrently. Wanted to
> measure
> the total time taken to complete the insertion process. Some txns
> overlap with
> others on the tuples they insert. Duplicate tuples are not inserted.
The duplicate elimination is being handled by ON CONFLICT or some custom
process in the code generating the transactions?
If the transactions are being created from a single app/script could you
not just use 'timing' to mark the beginning of the transactions and the
end and record that somewhere(db table and/or file)?
>
> On Wed, Jul 24, 2019 at 1:58 PM Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 7/24/19 9:54 AM, Souvik Bhattacherjee wrote:
> > Hi,
> >
> > Is there a standard procedure to execute two or more concurrent
> txns at
> > the same time? I understand that if we want to run concurrent
> txns, we
> > need to execute them from different psql sessions. But how do we
> make
> > sure that they begin execution almost at the same time.
>
> Well different sessions be they psql or some other client. That
> would be
> the difficulty, determining what is submitting the transaction.
>
> >
> > Also, I'm interested in measuring the time taken across all
> executing
> > txns, i.e. the time from the start of the earliest txns till the
> end of
> > the last txn.
>
> It would help to know what problem you are trying to solve?
>
> >
> > Best,
> > -SB
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 7/24/19 1:42 PM, Souvik Bhattacherjee wrote: > > The duplicate elimination is being handled by ON CONFLICT or some custom > > process in the code generating the transactions? > > Yes, we used ON CONFLICT for that. Thanks btw. > > > If the transactions are being created from a single app/script could you > > not just use 'timing' to mark the beginning of the transactions and the > > end and record that somewhere(db table and/or file)? > > So did you mean to say that I need to get the timestamps of the > beginning/end > of the txn since \timing only produces elapsed time? Surely that would > solve the > problem but I'm not sure how to get that done in Postgres. > > I wanted to check to see if there are simpler ways to get this done in > Postgres > before trying out something similar to Rob's suggestion or yours. > Well it depends on the part you have not filled in, what client(s) you are using and how the transactions are being generated? -- Adrian Klaver adrian.klaver@aklaver.com
>Well it depends on the part you have not filled in, what client(s) you
> are using and how the transactions are being generated?
Using a psql client and txns are generated manually at this point. Each txn is
stored separately in a .sql file and are fired from different psql sessions, if that
helps.
On Wed, Jul 24, 2019 at 4:44 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 7/24/19 1:42 PM, Souvik Bhattacherjee wrote:
> > The duplicate elimination is being handled by ON CONFLICT or some custom
> > process in the code generating the transactions?
>
> Yes, we used ON CONFLICT for that. Thanks btw.
>
> > If the transactions are being created from a single app/script could you
> > not just use 'timing' to mark the beginning of the transactions and the
> > end and record that somewhere(db table and/or file)?
>
> So did you mean to say that I need to get the timestamps of the
> beginning/end
> of the txn since \timing only produces elapsed time? Surely that would
> solve the
> problem but I'm not sure how to get that done in Postgres.
>
> I wanted to check to see if there are simpler ways to get this done in
> Postgres
> before trying out something similar to Rob's suggestion or yours.
>
Well it depends on the part you have not filled in, what client(s) you
are using and how the transactions are being generated?
--
Adrian Klaver
adrian.klaver@aklaver.com
On 7/24/19 1:52 PM, Souvik Bhattacherjee wrote: > >Well it depends on the part you have not filled in, what client(s) you > > are using and how the transactions are being generated? > > Using a psql client and txns are generated manually at this point. Each > txn is > stored separately in a .sql file and are fired from different psql > sessions, if that > helps. > A quick demo: psql -d production -U postgres -c "\timing" -c "select line_id, category from avail_headers order by line_id;" Timing is on. Time: 0.710 ms > On Wed, Jul 24, 2019 at 4:44 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 7/24/19 1:42 PM, Souvik Bhattacherjee wrote: > > > The duplicate elimination is being handled by ON CONFLICT or > some custom > > > process in the code generating the transactions? > > > > Yes, we used ON CONFLICT for that. Thanks btw. > > > > > If the transactions are being created from a single app/script > could you > > > not just use 'timing' to mark the beginning of the > transactions and the > > > end and record that somewhere(db table and/or file)? > > > > So did you mean to say that I need to get the timestamps of the > > beginning/end > > of the txn since \timing only produces elapsed time? Surely that > would > > solve the > > problem but I'm not sure how to get that done in Postgres. > > > > I wanted to check to see if there are simpler ways to get this > done in > > Postgres > > before trying out something similar to Rob's suggestion or yours. > > > > Well it depends on the part you have not filled in, what client(s) you > are using and how the transactions are being generated? > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
I got this thing running and hopefully works as expected. The txns are
stored in insert_txn1.sql, insert_txn2.sql, ...
Please let me know if you find any issues with this.
Script is attached.
On Wed, Jul 24, 2019 at 5:11 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 7/24/19 1:52 PM, Souvik Bhattacherjee wrote:
> >Well it depends on the part you have not filled in, what client(s) you
> > are using and how the transactions are being generated?
>
> Using a psql client and txns are generated manually at this point. Each
> txn is
> stored separately in a .sql file and are fired from different psql
> sessions, if that
> helps.
>
A quick demo:
psql -d production -U postgres -c "\timing" -c "select line_id, category
from avail_headers order by line_id;"
Timing is on.
Time: 0.710 ms
> On Wed, Jul 24, 2019 at 4:44 PM Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 7/24/19 1:42 PM, Souvik Bhattacherjee wrote:
> > > The duplicate elimination is being handled by ON CONFLICT or
> some custom
> > > process in the code generating the transactions?
> >
> > Yes, we used ON CONFLICT for that. Thanks btw.
> >
> > > If the transactions are being created from a single app/script
> could you
> > > not just use 'timing' to mark the beginning of the
> transactions and the
> > > end and record that somewhere(db table and/or file)?
> >
> > So did you mean to say that I need to get the timestamps of the
> > beginning/end
> > of the txn since \timing only produces elapsed time? Surely that
> would
> > solve the
> > problem but I'm not sure how to get that done in Postgres.
> >
> > I wanted to check to see if there are simpler ways to get this
> done in
> > Postgres
> > before trying out something similar to Rob's suggestion or yours.
> >
>
> Well it depends on the part you have not filled in, what client(s) you
> are using and how the transactions are being generated?
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Вложения
On 7/24/19 2:24 PM, Souvik Bhattacherjee wrote: > I got this thing running and hopefully works as expected. The txns are > stored in insert_txn1.sql, insert_txn2.sql, ... > Please let me know if you find any issues with this. > Script is attached. > > I'm hardly a BASH guru so someone else will need to comment, but on the surface it looks good to me. -- Adrian Klaver adrian.klaver@aklaver.com
On 25/7/19 5:24 AM, Souvik Bhattacherjee wrote: > I got this thing running and hopefully works as expected. The txns are > stored in insert_txn1.sql, insert_txn2.sql, ... > Please let me know if you find any issues with this. > Script is attached. > Even if you're using the ancient Bash version 3 (AFAIK only macOS still uses it out of the box), about half the lines are unnecessary: #!/bin/bash SECONDS=0 for i in {1..4} do psql -d mydb -f insert_txn${i}.sql & done wait echo "Elapsed time: $SECONDS secs" Read the bash man page to understand that SECONDS "magic", and why I didn't bother with PIDs at all. I also fixed a variable-dereferencing bug in your original script ("insert_txn[$i].sql" literally expands to "insert_txn[1].sql", etc.) Best Regards, Adrian