Re: Re - new datablade announced for informix
A follow-up to an announcement of the CopperEye index DataBlade for Informix. The question raised was how the optimizer knows to use the new index instead of a standard B-tree; Paul Watson answered that it doesn't have to choose, because the CopperEye index replaces the B-tree entirely. He cited a test loading ~20M rows into an indexed real-world table: about 14 hours with a standard B-tree versus just over 3 hours with CopperEye. 'Data Goob' called that slow, quoting much faster DB2-on-Linux load figures, but never supplied comparable hardware/table details or his own with/without-CopperEye numbers, so the comparison went unresolved. A final question about what the 'catch' of the technology is got no answer in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
"Paul Watson" <paul@oninit.com> wrote in message news:40D92FBD.B2B7A62A@oninit.com... > AFAIK it does work with Oracle or at least can. With Informix > we've seen 75% reduction in load times when compared with std b-tree > indexes. how does the optimizer know how to use this index and not std B-tree index.
'Cos the standard B-tree index is not there anymore, it is replaced One of our tests was to load about 20M rows into an empty but indexed table. The standard b-tree index table took 14 hours, the copper eye index table took just over 3 hours. The table was a 'real' table i.e. one from one of our applications rkusenet wrote: > > "Paul Watson" <paul@oninit.com> wrote in message news:40D92FBD.B2B7A62A@oninit.com... > > AFAIK it does work with Oracle or at least can. With Informix > > we've seen 75% reduction in load times when compared with std b-tree > > indexes. > > how does the optimizer know how to use this index and not std B-tree > index. -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #
OMG, that's muddy slow. We did a load of 122 Million rows in DB2 on Linux and it only took 25 minutes, 287 byte row, 76,315 records/sec. That was on a 12-partition 3-server set-up ( 4 cpus each ), without splitting the data up, it all came from one file. We did another table, 60 million row, 129 byte record, and it took 9 minutes. Both tables were 'real'. We took another one, 53 million rows, 97 byte record, and it took 5 minutes, 2 minutes for the clustered index. Compare with typically/average 6 hours to load these tables in SQL-Server on our 16-cpu 'wintel mainframe'. We anticipate even faster loads once we split the data up across the 3 nodes. Interesting that the speed is good on Intel Linux. We're hearing cool stuff about Power5, the latest Power chip coming out in mid-July, which is going to blow everything away according to what IBM is saying. We can hardly wait to try one of the new 4-cpu Power5 machines, which are supposed to be like mini-mainframes. Really makes Intel systems look primitive. "Paul Watson" <paul@oninit.com> wrote in message news:40D95D61.9DB61CDC@oninit.com... > 'Cos the standard B-tree index is not there anymore, it is replaced > One of our tests was to load about 20M rows into an empty but > indexed table. The standard b-tree index table took 14 hours, the > copper eye index table took just over 3 hours. The table was a 'real' > table i.e. one from one of our applications > > > > rkusenet wrote: > > > > "Paul Watson" <paul@oninit.com> wrote in message news:40D92FBD.B2B7A62A@oninit.com... > > > AFAIK it does work with Oracle or at least can. With Informix > > > we've seen 75% reduction in load times when compared with std b-tree > > > indexes. > > > > how does the optimizer know how to use this index and not std B-tree > > index. > > -- > Paul Watson # > Oninit Ltd # Growing old is mandatory > Tel: +44 1436 672201 # Growing up is optional > Fax: +44 1436 678693 # > Mob: +44 7818 003457 # > www.oninit.com #
Data Goob wrote: > > OMG, that's muddy slow. Really, what kit was it running on? [cutting] > 'Cos the standard B-tree index is not there anymore, it is replaced > One of our tests was to load about 20M rows into an empty but > indexed table. The standard b-tree index table took 14 hours, the > copper eye index table took just over 3 hours. The table was a 'real' > table i.e. one from one of our applications > > -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #
Kit? "Paul Watson" <paul@oninit.com> wrote in message news:40D96DE6.887CA799@oninit.com... > Data Goob wrote: > > > > OMG, that's muddy slow. > > Really, what kit was it running on? > > [cutting] > > 'Cos the standard B-tree index is not there anymore, it is replaced > > One of our tests was to load about 20M rows into an empty but > > indexed table. The standard b-tree index table took 14 hours, the > > copper eye index table took just over 3 hours. The table was a 'real' > > table i.e. one from one of our applications > > > > > > -- > Paul Watson # > Oninit Ltd # Growing old is mandatory > Tel: +44 1436 672201 # Growing up is optional > Fax: +44 1436 678693 # > Mob: +44 7818 003457 # > www.oninit.com #
I was curious how you determined it was 'muddy slow' when you have no idea about the table structure, index structure, or the hardware the tests were run on. Having that information then I could see how you could make a valid comparison but without it - well ...... Data Goob wrote: > > Kit? > > "Paul Watson" ?paul@oninit.com? wrote in message news:40D96DE6.887CA799@oninit.com... > ? Data Goob wrote: > ? ? > ? ? OMG, that's muddy slow. > ? > ? Really, what kit was it running on? > ? > ? [cutting] > ? ? 'Cos the standard B-tree index is not there anymore, it is replaced > ? ? One of our tests was to load about 20M rows into an empty but > ? ? indexed table. The standard b-tree index table took 14 hours, the > ? ? copper eye index table took just over 3 hours. The table was a 'real' > ? ? table i.e. one from one of our applications > ? ? > ? ? > ? > ? -- > ? Paul Watson # > ? Oninit Ltd # Growing old is mandatory > ? Tel: +44 1436 672201 # Growing up is optional > ? Fax: +44 1436 678693 # > ? Mob: +44 7818 003457 # > ? www.oninit.com # -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #
14 hours? 3 hours? I can achieve better without the CopperEye. ;-) Even on one server, and better yet, I know we did this test on a single 1-cpu box and did our largest load in 39 minutes. "Paul Watson" <paul@oninit.com> wrote in message news:40D97A54.94FDEEDD@oninit.com... > I was curious how you determined it was 'muddy slow' when you have > no idea about the table structure, index structure, or the hardware > the tests were run on. Having that information then I could see how > you could make a valid comparison but without it - well ...... > > Data Goob wrote: > > > > Kit? > > > > "Paul Watson" ?paul@oninit.com? wrote in message news:40D96DE6.887CA799@oninit.com... > > ? Data Goob wrote: > > ? ? > > ? ? OMG, that's muddy slow. > > ? > > ? Really, what kit was it running on? > > ? > > ? [cutting] > > ? ? 'Cos the standard B-tree index is not there anymore, it is replaced > > ? ? One of our tests was to load about 20M rows into an empty but > > ? ? indexed table. The standard b-tree index table took 14 hours, the > > ? ? copper eye index table took just over 3 hours. The table was a 'real' > > ? ? table i.e. one from one of our applications > > ? ? > > ? ? > > ? > > ? -- > > ? Paul Watson # > > ? Oninit Ltd # Growing old is mandatory > > ? Tel: +44 1436 672201 # Growing up is optional > > ? Fax: +44 1436 678693 # > > ? Mob: +44 7818 003457 # > > ? www.oninit.com # > > -- > Paul Watson # > Oninit Ltd # Growing old is mandatory > Tel: +44 1436 672201 # Growing up is optional > Fax: +44 1436 678693 # > Mob: +44 7818 003457 # > www.oninit.com #
Impressive, another 11 CPUs and two extra servers and you gained 14 minutes > Even on one server, and better yet, I know we did this test > on a single 1-cpu box and did our largest load in 39 minutes. > <quote> We did a load of 122 Million rows in DB2 on Linux and it only took 25 minutes, 287 byte row, 76,315 records/sec. That was on a 12-partition 3-server set-up ( 4 cpus each ), without splitting the data up, it all came from one file. </quote> I'm still curious though .... <quote> > ? I was curious how you determined it was 'muddy slow' when you have > ? no idea about the table structure, index structure, or the hardware > ? the tests were run on. Having that information then I could see how > ? you could make a valid comparison but without it - well ...... </quote> Data Goob wrote: > > 14 hours? 3 hours? > > I can achieve better without the CopperEye. I'm happy for you, could you please post your with and without CopperEye results [cutting] -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #
Paul Watson <paul@oninit.com> wrote in message news:<40D95D61.9DB61CDC@oninit.com>... > 'Cos the standard B-tree index is not there anymore, it is replaced > One of our tests was to load about 20M rows into an empty but > indexed table. The standard b-tree index table took 14 hours, the > copper eye index table took just over 3 hours. The table was a 'real' > table i.e. one from one of our applications what's the catch? (there's always a catch)