firebird-test

[Firebird-test] Re: [Firebird-devel] TPC-R load / run test

[Mark O'D. <mar...@lu...>]

Hi Tord

That sounds bloody terrific, a thrashing program one of the key testing 
bits we were missing.  And it helps to have it based on the standards stuff.

I've cc'ed this to firebird-test and will definately have a good look at 
it in the next couple of days.

Personally Im more interested in the testing perspective, in that it 
allows us to check for problems, but I assume the figures also allow us 
to compare to other systems.  Any idea how we fare?

Cheers

Mark




Tord Hammer wrote:

>Hi,
>
>I have tried to get the TPC-R benchmark running with FireBird.
>
>
>The homepage is at http://www.tpc.org/tpcr/
>
>I have changed the dbgen utility to work with interbase and fixed
>some bugs in the progress indicator. 
>
>I have also changed all the queries.
>
>I have removed the parameters and inserted the values given in
>tpcr_current.pdf.
>I have changed the inner-select syntax to using a view.
>I have changed the case syntax to a stored procedure.
>I have also changed some queries which confused the optimizer.
>I have tried to find suitable indexes.
>
>Since I dont have included the important parts of the benchmark
>(refreshing test, throughput test, power test) and I have changed
>the queries too much, so you cant use it to get a correct benchmark
>result.
>
>But I think its good enough to stress test the engine.
>
>You can download the linux-version from:
>http://www.hammer-software.de/fb/tpcr-firebird.tar.gz
>
>The original sourcecode can be found at 
>http://www.tpc.org/tpcr/default.asp#spec
>
>If you want only the original queries than look at
>http://www.hammer-software.de/fb/tpcr-queries.tar.gz
>
>
>After extracting of tpcr-firebird.tar.gz, you can generate
>the tpcr.gdb with the command:
>    ./maketpc.sh
>
>Useful parameters are -h for help, -p for path-prefix (if you
>dont have enough room at /tmp) and -s for scale factor.
>
>The queries can be executed with
>    ./runtpc.sh
>
>Now my results:
>
>with scale 0.1 (results in a 150 MB database), the queries need
>about 5-10 seconds each, with 100-200 seconds for query 20 and 21.
>
>for scale 1.0 and the parameter-values from tpcr_current.pdf
>I could check the returned rows as well:
>
>maketpc run about two hours.
>
>query  | execution time (seconds) | correct result
>-------+--------------------------+---------------
>   1   |          700             |       yes
>   2   |          250             |       yes
>   3   |          700             |        no
>   4   |          300             |       yes
>   5   |          300             |       yes
>   6   |          300             |       yes
>   7   |          700             |       yes
>   8   |          500             |       yes
>   9   |         6500             |       yes
>  10   |          400             |        no
>-------+--------------------------+---------------
>  11   |          150             |       yes
>  12   |          350             |        no
>  13   |         1800             |        no
>  14   |          200             |       yes
>  15   |          570             |       yes
>  16   |           70             |       yes
>  17   |          150             |        no
>  18   |          350             |       yes
>  19   |          700             |       yes
>  20   |       still running      |       n/a
>-------+--------------------------+---------------
>  21   |       still running      |       n/a
>  22   |       still running      |       n/a
>
>
>The system is linux 2.2.16 (from SuSE 7.0)
>AthlonB 1200, 256 GB RAM
>one big 20 GB ext2-partition at the second half of a
>40 GB hard disk.
>FireBird Classic Build 347
>Buffers = 10000
>
>
>Comments, better queries or better indexes welcome.
>
>
>Tord
>
>
>
>
>_______________________________________________
>Firebird-devel mailing list
>Fir...@li...
>http://lists.sourceforge.net/lists/listinfo/firebird-devel
>


-- 
Your database needs YOU!
http://firebird.sourceforge.net
GPL: free software today - and still free tomorrow

[Firebird-test] Re: [Firebird-devel] TPC-R load / run test

[Mark O'D. <mar...@lu...>]

I write to you under difficult circumstance, my machine is slugishly
slow, there is next to no space left in my /tmp directory, and the
characters are slow to appear on the screen as I type.  What does this mean?

Well it means I've been able to run Tord's TPC-R benchmarks (mind you I
should have read the instructions I didn't have 2gig spare on my root
partition).  fortunately it came with a scale option so I could get by
with somewhat less.

I get a few errors, but basically it's there, next week (moving on from
dejaTCS testing, and TCS testing of 1.0.0. Beta2 linux builds) I will be
back to looking at the testing the >2Gbyte databases for fb linux
edition.  I have a (spare?) dual 1ghz machine there with a 40gig disk
space that I can use to test them, and Im sure these test are something
I can use...

Thanks Tord for giving me the ability to trash my linux box :-).

Cheers

Mark



Tord Hammer wrote:

 >Hi,
 >
 >I have tried to get the TPC-R benchmark running with FireBird.
 >
 >You can download the linux-version from:
 >http://www.hammer-software.de/fb/tpcr-firebird.tar.gz
 >


 >
 >The original sourcecode can be found at
 >http://www.tpc.org/tpcr/default.asp#spec
 >
 >If you want only the original queries than look at
 >http://www.hammer-software.de/fb/tpcr-queries.tar.gz
 >
 >
 >After extracting of tpcr-firebird.tar.gz, you can generate
 >the tpcr.gdb with the command:
 >    ./maketpc.sh
 >
 >Useful parameters are -h for help, -p for path-prefix (if you
 >dont have enough room at /tmp) and -s for scale factor.
 >
 >The queries can be executed with
 >    ./runtpc.sh
 >
 >Now my results:
 >
 >with scale 0.1 (results in a 150 MB database), the queries need
 >about 5-10 seconds each, with 100-200 seconds for query 20 and 21.
 >
 >for scale 1.0 and the parameter-values from tpcr_current.pdf
 >I could check the returned rows as well:
 >
 >maketpc run about two hours.
 >
 >query  | execution time (seconds) | correct result
 >-------+--------------------------+---------------
 >   1   |          700             |       yes
 >   2   |          250             |       yes
 >   3   |          700             |        no
 >   4   |          300             |       yes
 >   5   |          300             |       yes
 >   6   |          300             |       yes
 >   7   |          700             |       yes
 >   8   |          500             |       yes
 >   9   |         6500             |       yes
 >  10   |          400             |        no
 >-------+--------------------------+---------------
 >  11   |          150             |       yes
 >  12   |          350             |        no
 >  13   |         1800             |        no
 >  14   |          200             |       yes
 >  15   |          570             |       yes
 >  16   |           70             |       yes
 >  17   |          150             |        no
 >  18   |          350             |       yes
 >  19   |          700             |       yes
 >  20   |       still running      |       n/a
 >-------+--------------------------+---------------
 >  21   |       still running      |       n/a
 >  22   |       still running      |       n/a
 >
 >
 >The system is linux 2.2.16 (from SuSE 7.0)
 >AthlonB 1200, 256 GB RAM
 >one big 20 GB ext2-partition at the second half of a
 >40 GB hard disk.
 >FireBird Classic Build 347
 >Buffers = 10000
 >
 >
 >Comments, better queries or better indexes welcome.
 >
 >
 >Tord
 >
 >
 >
 >
 >_______________________________________________
 >Firebird-devel mailing list
 >Fir...@li...
 >http://lists.sourceforge.net/lists/listinfo/firebird-devel
 >


-- 
Your database needs YOU!
http://firebird.sourceforge.net
GPL: free software today - and still free tomorrow

Re: [Firebird-test] Re: [Firebird-devel] TPC-R load / run test

[Tord H. <th...@ha...>]

> Personally Im more interested in the testing perspective, in that it
> allows us to check for problems, but I assume the figures also allow
us
> to compare to other systems.  Any idea how we fare?

This is hard to guess, since the small "databases" (like postgres,
mysql,
and so on) didnt run the tpc benchmarks. And the results at tpc.org
use database sizes which are nearly impossible to handle with
firebird.
So I think, it can only be used to stress test the engine, when I
found
the reason, why the queries 3, 10, 12, 13 and 17 returned the wrong
result set.

But back to the tpc-r:

There are only two results submited at the tpc.org homepage. I have
downloaded the last result, dated March 6, 2000.

The benchmark run at scale 1000, which means 1000 Gigabytes worth of
data.

With only one query executing (== power test), all queries finished
after 3 hours 27 minutes.

In the second test (== throuhput test) 7 parallel thread were
executed.
Everyone did the full set of 22 queries. At the same time, a 8rd
thread
did 1,500,000 inserts into the orders table and about 5,250,000
inserts
into the lineitem table, after that the thread delete 1,500,000
entries
in the orders table together with the linked entries in the lineitem
table.

The 7 threads finished after about 6-9 hours, the 8rd thread
after 21 hours.

Load time for the database (including sending 1000 Gigabytes to the
database server and building all the indexes) was 84 hours 15 minutes.

The database server was a server with 16 nodes, each containing
4 pentium iii/550 and 4 gb ram. The TOC for 5 years (including system
price and support for 5 years) was about 13 million dollars :)



There are more results for the TPC-C benchmark, but this one is quite
difficult to convert, since this benchmark simulates terminals (with
think and key time). But FYI, I have downloaded the result from
the best non-cluster system as well.

In this test, a compaq Alphaserver with 32 cpus and 256 GB memory and
22 Terrabytes disk-space could handle 27 millions new-order (keying
in a new order from a customer) and 26 millions payment (commit
paying of a bill) transactions in two hours.

Remember the bug with transaction numbers, Ann fixed some days ago ?
With 1k pages the limit was about 130 million transactions; this
beast would hit the limit in about 4 hours :))


A little update to my last message:

> >query  | execution time (seconds) | correct result
> >-------+--------------------------+---------------
> >  20   |       still running      |       n/a

after 4 hours, this query runs still at 100% cpu and
nearly no disk activity.


Tord

Re: [Firebird-test] Re: [Firebird-devel] TPC-R load / run test

[Mark O'D. <mar...@lu...>]

Tord Hammer wrote:

>
>The database server was a server with 16 nodes, each containing
>4 pentium iii/550 and 4 gb ram. The TOC for 5 years (including system
>price and support for 5 years) was about 13 million dollars :)
>
Shwing off :-), I seem to remember an article that NEC TerraData had 
actually yet to sell any of their high end multiprocessor nt boxes, 
perhaps the price tag explains that.

Thanks Tord, I certainly will enjoy having a look at this in the next 
few days.

Cheers

Mark

-- 
Your database needs YOU!
http://firebird.sourceforge.net
GPL: free software today - and still free tomorrow