Re: Error when test on DBT2 Postgresql

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(dot)klaver(at)aklaver(dot)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(at)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(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>>
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>