Not a Benchmark... but sort of...

Hi, we are developing an application where data which can be called time series is stored and retrieved into/from a database. The amount of data can be quite high and it is crucial that the incoming stream of data is stored as "real-time" as possible. Part of our job is to select the "best" database system for the job. Among other things (ease of installation / backup /...) performance in exactly this scenario will be an important attribute. The list of possible candidates has been defined together with the customer and containes Informix, Oracle, MySQL and Postgres. The hardware to use has been defined too. Right now we are developing a test program which will act like our final application and some test cases. We are in the process of defining attributes we want to measure and compare. The good thing is that in this scenario the datamodel is very simple. This list contains for example: - connect time - insert time - insert time difference when using prepared/unprepared statements - same for updates - same for selects - effect of single threaded / multithreaded selects/inserts/updates - effect of high number of indexes on inserts - rollback time We will not optimise the databases for the job. What we are interested in is significant differences. It is absolutely clear to me that this is NOT a benchmark and that it will NOT deliver any results about the database systems involved in general. I have two questions: 1. To those of you who did something like that before: can you give me any hints / pointers /comments for helping me to avoid stupid mistakes? 2. Would the results of something like that be worth publishing? And if so, will I have to fear threats on my life, money, my reproductive organs or other things I value? Regards, Dirk -- -- -- Dipl.-Math. Dirk Gunsth'vel -- -professional services- -- -- Dirk Gunsth'vel IT Systemanalyse - GunCon -- Hammer Str. 13 -- D-48153 Muenster -- phone: +49 (0) 251 28446- 0 -- fax: +49 (0) 251 28446-55 -- web: http://www.GunCon.de -- email: info@GunCon.de -- UStId: DE 189527667 -- -- 'One now understands why some animals eat their young.' -- (Andrew in 'Bicentennial Man' 1999)

Dirk Gunsthövel wrote: > Hi, > > we are developing an application where data which can be called > time series is stored and retrieved into/from a database. The > amount of data can be quite high and it is crucial that the incoming > stream of data is stored as "real-time" as possible. > > Part of our job is to select the "best" database system for the job. > Among other things (ease of installation / backup /...) performance > in exactly this scenario will be an important attribute. > > The list of possible candidates has been defined together with the > customer and containes Informix, Oracle, MySQL and Postgres. > > The hardware to use has been defined too. > > Right now we are developing a test program which will act like > our final application and some test cases. > > We are in the process of defining attributes we want to measure > and compare. The good thing is that in this scenario the datamodel is > very simple. This list contains for example: > - connect time > - insert time > - insert time difference when using prepared/unprepared statements > - same for updates > - same for selects > - effect of single threaded / multithreaded selects/inserts/updates > - effect of high number of indexes on inserts > - rollback time > > We will not optimise the databases for the job. What we are > interested in is significant differences. > > It is absolutely clear to me that this is NOT a benchmark and that > it will NOT deliver any results about the database systems > involved in general. > > I have two questions: > 1. To those of you who did something like that before: can you > give me any hints / pointers /comments for helping me to avoid > stupid mistakes? > Use short-ish transactions. 1,000 rows / commit, rather than 10,000 rows / commit. ;o) If you can use the TimeSeries DataBlade, do so. Based on a similar unpublished benchmark that I was involved in recently, we found that MySQL and IDS ran exactly the same speed, but IDS + TimeSeries ran more than twice as fast. You also don't need as many indexes, because of the way TimeSeries accesses data. I am especially pleased that your benchmark does not include deletes, which are a bit sucky in TS. > 2. Would the results of something like that be worth publishing? > And if so, will I have to fear threats on my life, money, my > reproductive organs or other things I value? > You will find that most vendors can sue you for publishing comparative benchmark results without their written consent. So read the EULA very carefully. -- Cheers, Obnoxio the Clown http://obotheclown.blogspot.com

> > We are in the process of defining attributes we want to measure > and compare. The good thing is that in this scenario the datamodel is > very simple. This list contains for example: > - connect time > - insert time > - insert time difference when using prepared/unprepared statements > - same for updates > - same for selects > - effect of single threaded / multithreaded selects/inserts/updates > - effect of high number of indexes on inserts > - rollback time > Any concurrency testing or is this all basic atomics testing ? In all the databases, what happens with 1 user is not what happens with 100 which is not what happens with 1000 which is not what happens with 10,000.

Hi, I guessed that publishing involved law issues. Does anybody know how it is with ibm/informix. After a first look I was not able to find something in the Eula... Your comment about commit after n records is a very good one. I will define a new test parameter (i.e. the n in 'commit every n rows') with the programmer tomorrow. Thanks, Dirk -- -- -- Dipl.-Math. Dirk Gunsth'vel -- -professional services- -- -- Dirk Gunsth'vel IT Systemanalyse - GunCon -- Hammer Str. 13 -- D-48153 Muenster -- phone: +49 (0) 251 28446- 0 -- fax: +49 (0) 251 28446-55 -- web: http://www.GunCon.de -- email: info@GunCon.de -- UStId: DE 189527667 -- -- 'One now understands why some animals eat their young.' -- (Andrew in 'Bicentennial Man' 1999) "Obnoxio The Clown" <obnoxio@serendipita.com> schrieb im Newsbeitrag news:mailman.110.1223796424.874.informix-list@iiug.org... Dirk Gunsth'vel wrote: > Hi, > > we are developing an application where data which can be called > time series is stored and retrieved into/from a database. The > amount of data can be quite high and it is crucial that the incoming > stream of data is stored as "real-time" as possible. > > Part of our job is to select the "best" database system for the job. > Among other things (ease of installation / backup /...) performance > in exactly this scenario will be an important attribute. > > The list of possible candidates has been defined together with the > customer and containes Informix, Oracle, MySQL and Postgres. > > The hardware to use has been defined too. > > Right now we are developing a test program which will act like > our final application and some test cases. > > We are in the process of defining attributes we want to measure > and compare. The good thing is that in this scenario the datamodel is > very simple. This list contains for example: > - connect time > - insert time > - insert time difference when using prepared/unprepared statements > - same for updates > - same for selects > - effect of single threaded / multithreaded selects/inserts/updates > - effect of high number of indexes on inserts > - rollback time > > We will not optimise the databases for the job. What we are > interested in is significant differences. > > It is absolutely clear to me that this is NOT a benchmark and that > it will NOT deliver any results about the database systems > involved in general. > > I have two questions: > 1. To those of you who did something like that before: can you > give me any hints / pointers /comments for helping me to avoid > stupid mistakes? > Use short-ish transactions. 1,000 rows / commit, rather than 10,000 rows / commit. ;o) If you can use the TimeSeries DataBlade, do so. Based on a similar unpublished benchmark that I was involved in recently, we found that MySQL and IDS ran exactly the same speed, but IDS + TimeSeries ran more than twice as fast. You also don't need as many indexes, because of the way TimeSeries accesses data. I am especially pleased that your benchmark does not include deletes, which are a bit sucky in TS. > 2. Would the results of something like that be worth publishing? > And if so, will I have to fear threats on my life, money, my > reproductive organs or other things I value? > You will find that most vendors can sue you for publishing comparative benchmark results without their written consent. So read the EULA very carefully. -- Cheers, Obnoxio the Clown http://obotheclown.blogspot.com

Hola :-) Thanks for your reply. You are completely right and this is exactly the reason why this is not standard oltp. There is only one process delivering data in the scenario. One more process is making some calculations and a third one is producing output in a batch-like manner. So no/only little an well defined concurrency - if this wouldnt be the case I guess I wouldnt even think about trying something like that... Dirk -- -- Dipl.-Math. Dirk Gunsth'vel -- -professional services- -- -- Dirk Gunsth'vel IT Systemanalyse - GunCon -- Hammer Str. 13 -- D-48153 Muenster -- phone: +49 (0) 251 28446- 0 -- fax: +49 (0) 251 28446-55 -- web: http://www.GunCon.de -- email: info@GunCon.de -- UStId: DE 189527667 -- -- 'One now understands why some animals eat their young.' -- (Andrew in 'Bicentennial Man' 1999) ----- Original Message ----- From: "Mark Townsend" <markbtownsend@sbcglobal.net> Newsgroups: comp.databases.informix Sent: Sunday, October 12, 2008 3:00 PM Subject: Re: Not a Benchmark... but sort of... > >> >> We are in the process of defining attributes we want to measure >> and compare. The good thing is that in this scenario the datamodel is >> very simple. This list contains for example: >> - connect time >> - insert time >> - insert time difference when using prepared/unprepared statements >> - same for updates >> - same for selects >> - effect of single threaded / multithreaded selects/inserts/updates >> - effect of high number of indexes on inserts >> - rollback time >> > > Any concurrency testing or is this all basic atomics testing ? In all the > databases, what happens with 1 user is not what happens with 100 which is > not what happens with 1000 which is not what happens with 10,000.

On Oct 12, 2:26 am, Obnoxio The Clown <obno...@serendipita.com> wrote: > Dirk Gunsthövel wrote: > > Hi, > > > we are developing an application where data which can be called > > time series is stored and retrieved into/from a database. The > > amount of data can be quite high and it is crucial that the incoming > > stream of data is stored as "real-time" as possible. > > > Part of our job is to select the "best" database system for the job. > > Among other things (ease of installation / backup /...) performance > > in exactly this scenario will be an important attribute. > > > The list of possible candidates has been defined together with the > > customer and containes Informix, Oracle, MySQL and Postgres. > > > The hardware to use has been defined too. > > > Right now we are developing a test program which will act like > > our final application and some test cases. > > > We are in the process of defining attributes we want to measure > > and compare. The good thing is that in this scenario the datamodel is > > very simple. This list contains for example: > > - connect time > > - insert time > > - insert time difference when using prepared/unprepared statements > > - same for updates > > - same for selects > > - effect of single threaded / multithreaded selects/inserts/updates > > - effect of high number of indexes on inserts > > - rollback time > > > We will not optimise the databases for the job. What we are > > interested in is significant differences. > > > It is absolutely clear to me that this is NOT a benchmark and that > > it will NOT deliver any results about the database systems > > involved in general. > > > I have two questions: > > 1. To those of you who did something like that before: can you > > give me any hints / pointers /comments for helping me to avoid > > stupid mistakes? > > Use short-ish transactions. 1,000 rows / commit, rather than 10,000 rows > / commit. ;o) > > If you can use the TimeSeries DataBlade, do so. Based on a similar > unpublished benchmark that I was involved in recently, we found that > MySQL and IDS ran exactly the same speed, but IDS + TimeSeries ran more > than twice as fast. You also don't need as many indexes, because of the > way TimeSeries accesses data. > > I am especially pleased that your benchmark does not include deletes, > which are a bit sucky in TS. Huh? TimeSeries data types aren't usually deleted unless you want to delete the entire series. So if you were following stock trades, you don't want to delete an individual trade, but the entire set of trades on a given stock. If anything you'd want to be able to flag the data as bad and skip it during processing. (Yes, bad trades do go through the system.) Time Series should kick the butt of any other database engine. And if you tie it in to the real time loader datablade, you'll get even better throughput.

Dirk Gunsth'vel wrote: > Hi, > > I guessed that publishing involved law issues. Does anybody > know how it is with ibm/informix. After a first look I was > not able to find something in the Eula... IBM has not such limitations. It's commonly knows as a "DeWitt clause": http://www.allbusiness.com/technology/computer-software/4092403-1.html Interestingly there have been lawsuits around the similar matter outside of DBMS where courts have toppled such clauses. So this whole thing appears to be rather toothless with few calling the bluff... Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

Ian Michael Gumby wrote: > On Oct 12, 2:26 am, Obnoxio The Clown <obno...@serendipita.com> wrote: > >> Dirk Gunsthövel wrote: >> >>> Hi, >>> >>> we are developing an application where data which can be called >>> time series is stored and retrieved into/from a database. The >>> amount of data can be quite high and it is crucial that the incoming >>> stream of data is stored as "real-time" as possible. >>> >>> Part of our job is to select the "best" database system for the job. >>> Among other things (ease of installation / backup /...) performance >>> in exactly this scenario will be an important attribute. >>> >>> The list of possible candidates has been defined together with the >>> customer and containes Informix, Oracle, MySQL and Postgres. >>> >>> The hardware to use has been defined too. >>> >>> Right now we are developing a test program which will act like >>> our final application and some test cases. >>> >>> We are in the process of defining attributes we want to measure >>> and compare. The good thing is that in this scenario the datamodel is >>> very simple. This list contains for example: >>> - connect time >>> - insert time >>> - insert time difference when using prepared/unprepared statements >>> - same for updates >>> - same for selects >>> - effect of single threaded / multithreaded selects/inserts/updates >>> - effect of high number of indexes on inserts >>> - rollback time >>> >>> We will not optimise the databases for the job. What we are >>> interested in is significant differences. >>> >>> It is absolutely clear to me that this is NOT a benchmark and that >>> it will NOT deliver any results about the database systems >>> involved in general. >>> >>> I have two questions: >>> 1. To those of you who did something like that before: can you >>> give me any hints / pointers /comments for helping me to avoid >>> stupid mistakes? >>> >> Use short-ish transactions. 1,000 rows / commit, rather than 10,000 rows >> / commit. ;o) >> >> If you can use the TimeSeries DataBlade, do so. Based on a similar >> unpublished benchmark that I was involved in recently, we found that >> MySQL and IDS ran exactly the same speed, but IDS + TimeSeries ran more >> than twice as fast. You also don't need as many indexes, because of the >> way TimeSeries accesses data. >> >> I am especially pleased that your benchmark does not include deletes, >> which are a bit sucky in TS. >> > > Huh? > > TimeSeries data types aren't usually deleted unless you want to delete > the entire series. > Usually. Please note that word. -- Cheers, Obnoxio the Clown http://obotheclown.blogspot.com