Thread: Error when test on DBT2 Postgresql
Hi All,
When I test the DBT2 with a large number of connections, I always get the error:
Error in read.table(file = file, header = header, sep = sep, quote = quote, :
no lines available in input
Calls: read.csv -> read.table
I have tracked the file and see the reason is there are no "TERMINATED" in mix.log.
However, in my observation, driver and client do not close elegantly so they cannot quit with sprintf with the "TERMINATED" message (These threads are closed by killall command).
1. How can I solve the problem? (I have checked the bash script and c code. I don't know why each thread cannot exit from the main loop after the predefined time. It should exit the main loop at time xxxx, which is a pre-computed value, according to the code.)
2. I want to know if my performance is too low.
No matter how I change the parameters, like creating RAM disk, improving the connection number, I always get the similar results.
Here is one of my test results:
Hardware: 4-core, 16GB of memory, two disks, and 1Gb ethernet
Parameters: 10 warehouses, 30 connections.
Response Time (s)
Transaction % Average : 90th % Total Rollbacks %
------------ ----- --------------------- ----------- --------------- -----
Delivery 3.00 0.012 : 0.018 7 0 0.00
New Order 41.63 0.006 : 0.008 97 0 0.00
Order Status 5.58 0.003 : 0.004 13 0 0.00
Payment 47.64 0.003 : 0.004 111 0 0.00
Stock Level 2.15 0.005 : 0.007 5 0 0.00
------------ ----- --------------------- ----------- --------------- -----
118.78 new-order transactions per minute (NOTPM)
0.8 minute duration
0 total unknown errors
97.0 seconds(s) ramping up
It seems only 2 txns/sec. Is that too low? How can I improve the performance?
3. How can I test the performance in distributed mode?
Looking forward to your replies.
Thanks,
Tim
On 11/07/2015 11:27 PM, Tim Chou wrote: > Hi All, > > When I test the DBT2 with a large number of connections, I always get > the error: > > Error in read.table(file = file, header = header, sep = sep, quote = > quote, : > no lines available in input > Calls: read.csv -> read.table > > I have tracked the file and see the reason is there are no "TERMINATED" > in mix.log. > However, in my observation, driver and client do not close elegantly so > they cannot quit with sprintf with the "TERMINATED" message (These > threads are closed by killall command). > > 1. How can I solve the problem? (I have checked the bash script and c > code. I don't know why each thread cannot exit from the main loop after > the predefined time. It should exit the main loop at time xxxx, which is > a pre-computed value, according to the code.) > > 2. I want to know if my performance is too low. > No matter how I change the parameters, like creating RAM disk, improving > the connection number, I always get the similar results. > Here is one of my test results: > Hardware: 4-core, 16GB of memory, two disks, and 1Gb ethernet > Parameters: 10 warehouses, 30 connections. > > Response Time (s) > Transaction % Average : 90th % Total > Rollbacks % > ------------ ----- --------------------- ----------- --------------- > ----- > Delivery 3.00 0.012 : 0.018 7 > 0 0.00 > New Order 41.63 0.006 : 0.008 97 > 0 0.00 > Order Status 5.58 0.003 : 0.004 13 0 > 0.00 > Payment 47.64 0.003 : 0.004 111 > 0 0.00 > Stock Level 2.15 0.005 : 0.007 5 > 0 0.00 > ------------ ----- --------------------- ----------- --------------- > ----- > 118.78 new-order transactions per minute (NOTPM) > 0.8 minute duration > 0 total unknown errors > 97.0 seconds(s) ramping up > > It seems only 2 txns/sec. Is that too low? How can I improve the > performance? > > 3. How can I test the performance in distributed mode? > > Looking forward to your replies. What version of Postgres? Where did get the DBT2 test? How did you run the test? > > Thanks, > Tim -- Adrian Klaver adrian.klaver@aklaver.com
On 11/08/2015 04:50 PM, Tim Chou wrote: Really CCing list this time. > Hi Adrian, > > Thank you for your reply. > > I use git to clone the repository (git clone > http://git.code.sf.net/p/osdldbt/dbt2 osdldbt-dbt2). > DBT2's website I used is > http://sourceforge.net/p/osdldbt/dbt2/ci/master/tree > > I said my performance is very low because I read some papers and their > performance can exceed one thousand of transactions per second. I don't > want to compare with other databases, but I believe that my performance > is too low because only 100+ transactions can be processed. > > I also try to change the data dir to a ram disk. However, the > performance keeps similar. > > Which version of DBT2 and PostgreSQL do you use to test the performance? I have not actually used DBT2. With the information you provided I downloaded it and tried to get it to run here. So far, ultimately unsuccessful using DBT2 or DBT3. I have gotten as far as the load test(loading the data) but then it fails for one reason or another. This is against 9.4. Going to take a break, maybe someone with more experience using DBT can make suggestions to you. > > If I have some wrong understandings, please let me know. > > Thanks, > Tim > > 2015-11-08 17:20 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>: > > On 11/08/2015 01:55 PM, Tim Chou wrote: > > CCing list. > > Hi Adrian, > > The version o Postgres is 9.4.5 > I download the DBT2 from their sourceforge website. > > > What site would that be?: > > When I go here: > > http://osdldbt.sourceforge.net/ > > all the tests up to dbt4 download the same tarball which is for dbt1 > and is from 2003. > > When I go here: > > http://sourceforge.net/projects/osdldbt/files/ > > the latest version is given as: > > dbt3-1.10.tar.bz2 > > > I follow the README file to run the test. (First, create and > load data. > Second, run dbt2-run-workload). > > > In the README for the above I have: > > "Since this kit is derived from a TPC benchmark specification, TPC > Policy on > fair-use must be observed: > > > http://www.tpc.org/information/about/documentation/TPC_Policies_v5.13.htm#_Toc124863463 > > See the QuickStart for a brief introduction on using the kit. > " > > So you will need to provide exact information as what dbt you are > using and where it is coming from. > > > Thanks, > Tim > > 2015-11-08 9:45 GMT-05:00 Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>>: > > > On 11/07/2015 11:27 PM, Tim Chou wrote: > > Hi All, > > When I test the DBT2 with a large number of connections, I > always get > the error: > > Error in read.table(file = file, header = header, sep = > sep, quote = > quote, : > no lines available in input > Calls: read.csv -> read.table > > I have tracked the file and see the reason is there are no > "TERMINATED" > in mix.log. > However, in my observation, driver and client do not close > elegantly so > they cannot quit with sprintf with the "TERMINATED" > message (These > threads are closed by killall command). > > 1. How can I solve the problem? (I have checked the > bash script > and c > code. I don't know why each thread cannot exit from the > main > loop after > the predefined time. It should exit the main loop at > time xxxx, > which is > a pre-computed value, according to the code.) > > 2. I want to know if my performance is too low. > No matter how I change the parameters, like creating > RAM disk, > improving > the connection number, I always get the similar results. > Here is one of my test results: > Hardware: 4-core, 16GB of memory, two disks, and 1Gb > ethernet > Parameters: 10 warehouses, 30 connections. > > Response Time (s) > Transaction % Average : 90th % Total > Rollbacks % > ------------ ----- --------------------- ----------- > --------------- > ----- > Delivery 3.00 0.012 : 0.018 7 > 0 0.00 > New Order 41.63 0.006 : 0.008 97 > 0 0.00 > Order Status 5.58 0.003 : 0.004 13 > 0 > 0.00 > Payment 47.64 0.003 : 0.004 111 > 0 0.00 > Stock Level 2.15 0.005 : 0.007 5 > 0 0.00 > ------------ ----- --------------------- ----------- > --------------- > ----- > 118.78 new-order transactions per minute (NOTPM) > 0.8 minute duration > 0 total unknown errors > 97.0 seconds(s) ramping up > > It seems only 2 txns/sec. Is that too low? How can I > improve the > performance? > > 3. How can I test the performance in distributed mode? > > Looking forward to your replies. > > > What version of Postgres? > > Where did get the DBT2 test? > > How did you run the test? > > > Thanks, > Tim > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian,
Thank you all the time. I also realized that DBT2 has some bugs.
Actually, I have sent an email to DBT2's mailing list. However, no one responded me.
The latency of a txn is not high in my test. But the number of txns processed in one minute are not high.
Thank you.
Best.
Tim
2015-11-08 22:21 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 11/08/2015 04:50 PM, Tim Chou wrote:
Really CCing list this time.Hi Adrian,
Thank you for your reply.
I use git to clone the repository (git clone
http://git.code.sf.net/p/osdldbt/dbt2 osdldbt-dbt2).
DBT2's website I used is
http://sourceforge.net/p/osdldbt/dbt2/ci/master/tree
I said my performance is very low because I read some papers and their
performance can exceed one thousand of transactions per second. I don't
want to compare with other databases, but I believe that my performance
is too low because only 100+ transactions can be processed.
I also try to change the data dir to a ram disk. However, the
performance keeps similar.
Which version of DBT2 and PostgreSQL do you use to test the performance?
I have not actually used DBT2. With the information you provided I downloaded it and tried to get it to run here. So far, ultimately unsuccessful using DBT2 or DBT3. I have gotten as far as the load test(loading the data) but then it fails for one reason or another. This is against 9.4. Going to take a break, maybe someone with more experience using DBT can make suggestions to you.
If I have some wrong understandings, please let me know.
Thanks,
Tim
2015-11-08 17:20 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>:<mailto:adrian.klaver@aklaver.com
On 11/08/2015 01:55 PM, Tim Chou wrote:
CCing list.
Hi Adrian,
The version o Postgres is 9.4.5
I download the DBT2 from their sourceforge website.
What site would that be?:
When I go here:
http://osdldbt.sourceforge.net/
all the tests up to dbt4 download the same tarball which is for dbt1
and is from 2003.
When I go here:
http://sourceforge.net/projects/osdldbt/files/
the latest version is given as:
dbt3-1.10.tar.bz2
I follow the README file to run the test. (First, create and
load data.
Second, run dbt2-run-workload).
In the README for the above I have:
"Since this kit is derived from a TPC benchmark specification, TPC
Policy on
fair-use must be observed:
http://www.tpc.org/information/about/documentation/TPC_Policies_v5.13.htm#_Toc124863463
See the QuickStart for a brief introduction on using the kit.
"
So you will need to provide exact information as what dbt you are
using and where it is coming from.
Thanks,
Tim
2015-11-08 9:45 GMT-05:00 Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com><mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>>:
On 11/07/2015 11:27 PM, Tim Chou wrote:
Hi All,
When I test the DBT2 with a large number of connections, I
always get
the error:
Error in read.table(file = file, header = header, sep =
sep, quote =
quote, :
no lines available in input
Calls: read.csv -> read.table
I have tracked the file and see the reason is there are no
"TERMINATED"
in mix.log.
However, in my observation, driver and client do not close
elegantly so
they cannot quit with sprintf with the "TERMINATED"
message (These
threads are closed by killall command).
1. How can I solve the problem? (I have checked the
bash script
and c
code. I don't know why each thread cannot exit from the
main
loop after
the predefined time. It should exit the main loop at
time xxxx,
which is
a pre-computed value, according to the code.)
2. I want to know if my performance is too low.
No matter how I change the parameters, like creating
RAM disk,
improving
the connection number, I always get the similar results.
Here is one of my test results:
Hardware: 4-core, 16GB of memory, two disks, and 1Gb
ethernet
Parameters: 10 warehouses, 30 connections.
Response Time (s)
Transaction % Average : 90th % Total
Rollbacks %
------------ ----- --------------------- -----------
---------------
-----
Delivery 3.00 0.012 : 0.018 7
0 0.00
New Order 41.63 0.006 : 0.008 97
0 0.00
Order Status 5.58 0.003 : 0.004 13
0
0.00
Payment 47.64 0.003 : 0.004 111
0 0.00
Stock Level 2.15 0.005 : 0.007 5
0 0.00
------------ ----- --------------------- -----------
---------------
-----
118.78 new-order transactions per minute (NOTPM)
0.8 minute duration
0 total unknown errors
97.0 seconds(s) ramping up
It seems only 2 txns/sec. Is that too low? How can I
improve the
performance?
3. How can I test the performance in distributed mode?
Looking forward to your replies.
What version of Postgres?
Where did get the DBT2 test?
How did you run the test?
Thanks,
Tim
--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com>>
--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 11/08/2015 07:40 PM, Tim Chou wrote: > Hi Adrian, > > Thank you all the time. I also realized that DBT2 has some bugs. > Actually, I have sent an email to DBT2's mailing list. However, no one > responded me. > The latency of a txn is not high in my test. But the number of txns > processed in one minute are not high. I have not given up getting it to work and will return, hopefully, with some more information. > > Thank you. > > Best. > Tim > > 2015-11-08 22:21 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>: > > On 11/08/2015 04:50 PM, Tim Chou wrote: > > Really CCing list this time. > > Hi Adrian, > > Thank you for your reply. > > I use git to clone the repository (git clone > http://git.code.sf.net/p/osdldbt/dbt2 osdldbt-dbt2). > DBT2's website I used is > http://sourceforge.net/p/osdldbt/dbt2/ci/master/tree > > I said my performance is very low because I read some papers and > their > performance can exceed one thousand of transactions per second. > I don't > want to compare with other databases, but I believe that my > performance > is too low because only 100+ transactions can be processed. > > I also try to change the data dir to a ram disk. However, the > performance keeps similar. > > Which version of DBT2 and PostgreSQL do you use to test the > performance? > > > I have not actually used DBT2. With the information you provided I > downloaded it and tried to get it to run here. So far, ultimately > unsuccessful using DBT2 or DBT3. I have gotten as far as the load > test(loading the data) but then it fails for one reason or another. > This is against 9.4. Going to take a break, maybe someone with more > experience using DBT can make suggestions to you. > > > If I have some wrong understandings, please let me know. > > Thanks, > Tim > > 2015-11-08 17:20 GMT-05:00 Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>>: > > > On 11/08/2015 01:55 PM, Tim Chou wrote: > > CCing list. > > Hi Adrian, > > The version o Postgres is 9.4.5 > I download the DBT2 from their sourceforge website. > > > What site would that be?: > > When I go here: > > http://osdldbt.sourceforge.net/ > > all the tests up to dbt4 download the same tarball which is > for dbt1 > and is from 2003. > > When I go here: > > http://sourceforge.net/projects/osdldbt/files/ > > the latest version is given as: > > dbt3-1.10.tar.bz2 > > > I follow the README file to run the test. (First, > create and > load data. > Second, run dbt2-run-workload). > > > In the README for the above I have: > > "Since this kit is derived from a TPC benchmark > specification, TPC > Policy on > fair-use must be observed: > > > http://www.tpc.org/information/about/documentation/TPC_Policies_v5.13.htm#_Toc124863463 > > See the QuickStart for a brief introduction on using the kit. > " > > So you will need to provide exact information as what dbt > you are > using and where it is coming from. > > > Thanks, > Tim > > 2015-11-08 9:45 GMT-05:00 Adrian Klaver > <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>>>: > > > On 11/07/2015 11:27 PM, Tim Chou wrote: > > Hi All, > > When I test the DBT2 with a large number of > connections, I > always get > the error: > > Error in read.table(file = file, header = > header, sep = > sep, quote = > quote, : > no lines available in input > Calls: read.csv -> read.table > > I have tracked the file and see the reason is > there are no > "TERMINATED" > in mix.log. > However, in my observation, driver and client > do not close > elegantly so > they cannot quit with sprintf with the > "TERMINATED" > message (These > threads are closed by killall command). > > 1. How can I solve the problem? (I have > checked the > bash script > and c > code. I don't know why each thread cannot exit > from the > main > loop after > the predefined time. It should exit the main > loop at > time xxxx, > which is > a pre-computed value, according to the code.) > > 2. I want to know if my performance is too low. > No matter how I change the parameters, like > creating > RAM disk, > improving > the connection number, I always get the > similar results. > Here is one of my test results: > Hardware: 4-core, 16GB of memory, two disks, > and 1Gb > ethernet > Parameters: 10 warehouses, 30 connections. > > Response Time (s) > Transaction % Average : 90th % > Total > Rollbacks % > ------------ ----- --------------------- > ----------- > --------------- > ----- > Delivery 3.00 0.012 : 0.018 > 7 > 0 0.00 > New Order 41.63 0.006 : 0.008 > 97 > 0 0.00 > Order Status 5.58 0.003 : 0.004 > 13 > 0 > 0.00 > Payment 47.64 0.003 : 0.004 > 111 > 0 0.00 > Stock Level 2.15 0.005 : 0.007 > 5 > 0 0.00 > ------------ ----- --------------------- > ----------- > --------------- > ----- > 118.78 new-order transactions per minute (NOTPM) > 0.8 minute duration > 0 total unknown errors > 97.0 seconds(s) ramping up > > It seems only 2 txns/sec. Is that too low? How > can I > improve the > performance? > > 3. How can I test the performance in > distributed mode? > > Looking forward to your replies. > > > What version of Postgres? > > Where did get the DBT2 test? > > How did you run the test? > > > Thanks, > Tim > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>> > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/08/2015 07:40 PM, Tim Chou wrote:
> Hi Adrian,
>
> Thank you all the time. I also realized that DBT2 has some bugs.
> Actually, I have sent an email to DBT2's mailing list. However, no one
> responded me.
> The latency of a txn is not high in my test. But the number of txns
> processed in one minute are not high.
Well I finally got something to run. Using dbt3 on an old 32bit machine
I got:
aklaver@panda:~> dbt3-run-workload -3 -a pgsql -f 1 -o /tmp/results
SCALE: 1
Using seed: 1109074613
Database is already started: /tmp/pgdata/postmaster.pid.
waiting for server to shut down..... done
server stopped
waiting for server to start.... done
server started
starting system statistics data collection
Mon Nov 9 07:46:17 PST 2015: Throughput Stream 1: Starting RF 1...
Throughput Stream 1: Elapsed time for Refresh Stream 1: 18 seconds
Mon Nov 9 07:46:38 PST 2015: Throughput Stream 1: Starting RF 2...
Throughput Steam 1: Elapsed time for RF 2: 3 seconds
/usr/local/bin/dbt3-throughput-test: line 108: 7337 Terminated
dbt3-$DATABASE-dbstat ${OUTPUT_DIR}
waiting for server to shut down..... done
server stopped
Elapsed time for performance test: 72 seconds
waiting for server to start...... done
server started
Getting system configuration...
Getting query times...
waiting for server to shut down.... done
server stopped
Post processing query results...
throughput = 1189.29
Done!
Not sure what 'throughput = 1189.29' actually means.
>
> Thank you.
>
> Best.
> Tim
>
> 2015-11-08 22:21 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>:
>
> On 11/08/2015 04:50 PM, Tim Chou wrote:
>
> Really CCing list this time.
>
> Hi Adrian,
>
> Thank you for your reply.
>
> I use git to clone the repository (git clone
> http://git.code.sf.net/p/osdldbt/dbt2 osdldbt-dbt2).
> DBT2's website I used is
> http://sourceforge.net/p/osdldbt/dbt2/ci/master/tree
>
> I said my performance is very low because I read some papers and
> their
> performance can exceed one thousand of transactions per second.
> I don't
> want to compare with other databases, but I believe that my
> performance
> is too low because only 100+ transactions can be processed.
>
> I also try to change the data dir to a ram disk. However, the
> performance keeps similar.
>
> Which version of DBT2 and PostgreSQL do you use to test the
> performance?
>
>
> I have not actually used DBT2. With the information you provided I
> downloaded it and tried to get it to run here. So far, ultimately
> unsuccessful using DBT2 or DBT3. I have gotten as far as the load
> test(loading the data) but then it fails for one reason or another.
> This is against 9.4. Going to take a break, maybe someone with more
> experience using DBT can make suggestions to you.
>
>
> If I have some wrong understandings, please let me know.
>
> Thanks,
> Tim
>
> 2015-11-08 17:20 GMT-05:00 Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>>:
>
>
> On 11/08/2015 01:55 PM, Tim Chou wrote:
>
> CCing list.
>
> Hi Adrian,
>
> The version o Postgres is 9.4.5
> I download the DBT2 from their sourceforge website.
>
>
> What site would that be?:
>
> When I go here:
>
> http://osdldbt.sourceforge.net/
>
> all the tests up to dbt4 download the same tarball which is
> for dbt1
> and is from 2003.
>
> When I go here:
>
> http://sourceforge.net/projects/osdldbt/files/
>
> the latest version is given as:
>
> dbt3-1.10.tar.bz2
>
>
> I follow the README file to run the test. (First,
> create and
> load data.
> Second, run dbt2-run-workload).
>
>
> In the README for the above I have:
>
> "Since this kit is derived from a TPC benchmark
> specification, TPC
> Policy on
> fair-use must be observed:
>
>
> http://www.tpc.org/information/about/documentation/TPC_Policies_v5.13.htm#_Toc124863463
>
> See the QuickStart for a brief introduction on using the kit.
> "
>
> So you will need to provide exact information as what dbt
> you are
> using and where it is coming from.
>
>
> Thanks,
> Tim
>
> 2015-11-08 9:45 GMT-05:00 Adrian Klaver
> <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>
> <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>
> <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>
>
> <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>>>:
>
>
> On 11/07/2015 11:27 PM, Tim Chou wrote:
>
> Hi All,
>
> When I test the DBT2 with a large number of
> connections, I
> always get
> the error:
>
> Error in read.table(file = file, header =
> header, sep =
> sep, quote =
> quote, :
> no lines available in input
> Calls: read.csv -> read.table
>
> I have tracked the file and see the reason is
> there are no
> "TERMINATED"
> in mix.log.
> However, in my observation, driver and client
> do not close
> elegantly so
> they cannot quit with sprintf with the
> "TERMINATED"
> message (These
> threads are closed by killall command).
>
> 1. How can I solve the problem? (I have
> checked the
> bash script
> and c
> code. I don't know why each thread cannot exit
> from the
> main
> loop after
> the predefined time. It should exit the main
> loop at
> time xxxx,
> which is
> a pre-computed value, according to the code.)
>
> 2. I want to know if my performance is too low.
> No matter how I change the parameters, like
> creating
> RAM disk,
> improving
> the connection number, I always get the
> similar results.
> Here is one of my test results:
> Hardware: 4-core, 16GB of memory, two disks,
> and 1Gb
> ethernet
> Parameters: 10 warehouses, 30 connections.
>
> Response Time (s)
> Transaction % Average : 90th %
> Total
> Rollbacks %
> ------------ ----- ---------------------
> -----------
> ---------------
> -----
> Delivery 3.00 0.012 : 0.018
> 7
> 0 0.00
> New Order 41.63 0.006 : 0.008
> 97
> 0 0.00
> Order Status 5.58 0.003 : 0.004
> 13
> 0
> 0.00
> Payment 47.64 0.003 : 0.004
> 111
> 0 0.00
> Stock Level 2.15 0.005 : 0.007
> 5
> 0 0.00
> ------------ ----- ---------------------
> -----------
> ---------------
> -----
> 118.78 new-order transactions per minute (NOTPM)
> 0.8 minute duration
> 0 total unknown errors
> 97.0 seconds(s) ramping up
>
> It seems only 2 txns/sec. Is that too low? How
> can I
> improve the
> performance?
>
> 3. How can I test the performance in
> distributed mode?
>
> Looking forward to your replies.
>
>
> What version of Postgres?
>
> Where did get the DBT2 test?
>
> How did you run the test?
>
>
> Thanks,
> Tim
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>
> <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>
> <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>>
>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>
>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi adrian,
Thanks for your help. I think I know why the performance is not good.
I misunderstand the document. So in my test, there are thinking time and keying time for each transaction.
When I remove the thinking time and keying time, the performance become 1500 new order txns/mins.
Your performance also makes sense to me.
Best,
Tim
2015-11-09 10:54 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 11/08/2015 07:40 PM, Tim Chou wrote:Hi Adrian,
Thank you all the time. I also realized that DBT2 has some bugs.
Actually, I have sent an email to DBT2's mailing list. However, no one
responded me.
The latency of a txn is not high in my test. But the number of txns
processed in one minute are not high.
Well I finally got something to run. Using dbt3 on an old 32bit machine I got:
aklaver@panda:~> dbt3-run-workload -3 -a pgsql -f 1 -o /tmp/results
SCALE: 1
Using seed: 1109074613
Database is already started: /tmp/pgdata/postmaster.pid.
waiting for server to shut down..... done
server stopped
waiting for server to start.... done
server started
starting system statistics data collection
Mon Nov 9 07:46:17 PST 2015: Throughput Stream 1: Starting RF 1...
Throughput Stream 1: Elapsed time for Refresh Stream 1: 18 seconds
Mon Nov 9 07:46:38 PST 2015: Throughput Stream 1: Starting RF 2...
Throughput Steam 1: Elapsed time for RF 2: 3 seconds
/usr/local/bin/dbt3-throughput-test: line 108: 7337 Terminated dbt3-$DATABASE-dbstat ${OUTPUT_DIR}
waiting for server to shut down..... done
server stopped
Elapsed time for performance test: 72 seconds
waiting for server to start...... done
server started
Getting system configuration...
Getting query times...
waiting for server to shut down.... done
server stopped
Post processing query results...
throughput = 1189.29
Done!
Not sure what 'throughput = 1189.29' actually means.
Thank you.
Best.
Tim
2015-11-08 22:21 GMT-05:00 Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>:
On 11/08/2015 04:50 PM, Tim Chou wrote:
Really CCing list this time.
Hi Adrian,
Thank you for your reply.
I use git to clone the repository (git clone
http://git.code.sf.net/p/osdldbt/dbt2 osdldbt-dbt2).
DBT2's website I used is
http://sourceforge.net/p/osdldbt/dbt2/ci/master/tree
I said my performance is very low because I read some papers and
their
performance can exceed one thousand of transactions per second.
I don't
want to compare with other databases, but I believe that my
performance
is too low because only 100+ transactions can be processed.
I also try to change the data dir to a ram disk. However, the
performance keeps similar.
Which version of DBT2 and PostgreSQL do you use to test the
performance?
I have not actually used DBT2. With the information you provided I
downloaded it and tried to get it to run here. So far, ultimately
unsuccessful using DBT2 or DBT3. I have gotten as far as the load
test(loading the data) but then it fails for one reason or another.
This is against 9.4. Going to take a break, maybe someone with more
experience using DBT can make suggestions to you.
If I have some wrong understandings, please let me know.
Thanks,
Tim
2015-11-08 17:20 GMT-05:00 Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com><mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>>:
On 11/08/2015 01:55 PM, Tim Chou wrote:
CCing list.
Hi Adrian,
The version o Postgres is 9.4.5
I download the DBT2 from their sourceforge website.
What site would that be?:
When I go here:
http://osdldbt.sourceforge.net/
all the tests up to dbt4 download the same tarball which is
for dbt1
and is from 2003.
When I go here:
http://sourceforge.net/projects/osdldbt/files/
the latest version is given as:
dbt3-1.10.tar.bz2
I follow the README file to run the test. (First,
create and
load data.
Second, run dbt2-run-workload).
In the README for the above I have:
"Since this kit is derived from a TPC benchmark
specification, TPC
Policy on
fair-use must be observed:
http://www.tpc.org/information/about/documentation/TPC_Policies_v5.13.htm#_Toc124863463
See the QuickStart for a brief introduction on using the kit.
"
So you will need to provide exact information as what dbt
you are
using and where it is coming from.
Thanks,
Tim
2015-11-08 9:45 GMT-05:00 Adrian Klaver
<adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>>>:
On 11/07/2015 11:27 PM, Tim Chou wrote:
Hi All,
When I test the DBT2 with a large number of
connections, I
always get
the error:
Error in read.table(file = file, header =
header, sep =
sep, quote =
quote, :
no lines available in input
Calls: read.csv -> read.table
I have tracked the file and see the reason is
there are no
"TERMINATED"
in mix.log.
However, in my observation, driver and client
do not close
elegantly so
they cannot quit with sprintf with the
"TERMINATED"
message (These
threads are closed by killall command).
1. How can I solve the problem? (I have
checked the
bash script
and c
code. I don't know why each thread cannot exit
from the
main
loop after
the predefined time. It should exit the main
loop at
time xxxx,
which is
a pre-computed value, according to the code.)
2. I want to know if my performance is too low.
No matter how I change the parameters, like
creating
RAM disk,
improving
the connection number, I always get the
similar results.
Here is one of my test results:
Hardware: 4-core, 16GB of memory, two disks,
and 1Gb
ethernet
Parameters: 10 warehouses, 30 connections.
Response Time (s)
Transaction % Average : 90th %
Total
Rollbacks %
------------ ----- ---------------------
-----------
---------------
-----
Delivery 3.00 0.012 : 0.018
7
0 0.00
New Order 41.63 0.006 : 0.008
97
0 0.00
Order Status 5.58 0.003 : 0.004
13
0
0.00
Payment 47.64 0.003 : 0.004
111
0 0.00
Stock Level 2.15 0.005 : 0.007
5
0 0.00
------------ ----- ---------------------
-----------
---------------
-----
118.78 new-order transactions per minute (NOTPM)
0.8 minute duration
0 total unknown errors
97.0 seconds(s) ramping up
It seems only 2 txns/sec. Is that too low? How
can I
improve the
performance?
3. How can I test the performance in
distributed mode?
Looking forward to your replies.
What version of Postgres?
Where did get the DBT2 test?
How did you run the test?
Thanks,
Tim
--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>>
--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>
--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
--
Adrian Klaver
adrian.klaver@aklaver.com