RE: Found a possible bug in IDS 11.x... and a nasty one at that... has anyone suffered this?

I have to agree that the issue is interesting. The fact that regardless of the query, the performance was relatively consistent. Writing a query to join itself against itself performs at the same speed as if it was just querying from a single table, or changing the query to use the BETWEEN key word didn't have an effect. The fact that VMWare killed the query performance is yet another reason why not to consider 'server consolidation', or at least VMWare's consolidation with respect to database servers. There are some other questions that we didn't ask... With respect to the index, event though the index was on an integer, how many collisions occurred? While there's 11 million rows, if there's a high frequency of collisions, then a sequential scan could actually perform better. I think you mentioned or suggested clustering your data around the index. That too would boost performance. I agree that this may not be a bug or a design defect. Just an implementation or configuration issue. Date: Mon, 7 Dec 2009 16:15:37 +0000 Subject: Re: Found a possible bug in IDS 11.x... and a nasty one at that... has anyone suffered this? From: domusonline@gmail.com To: informix-list@iiug.org I have to say that this issue is very interesting. I've tried to do some more digging and I have some more observations: - On a VMWare the same query (simple index without any trick) runs in 7m if I start the VM and in 7s (!) if after the first query I stop the instance and restart it. This gives you an idea of the importance of caching in this issue. I even tried to flush the Linux file system cache using; echo 3 > /proc/sys/vm/drop_caches but sometimes it doesn't matter. So I believe it's the host operating system (windows) making the cache. In a real scenario this could be equivalent to the hardware cache. I measured the number of page reads (pages effectively read from the disk) and it's very similar between the two queries using the index ( ~79k-80k). The sequentila scan reads much more. This implies that the engine is not doing anything wrong when using the index without further tricks... I think the performance improvement using the trick is achieved because when we do a pageread (2k) the disk system effectively reads more. And on consequent (sequential) attempts to read the next pages we take advantage of that. I also asked some Oracle DBAs to do some testing and several things poped up that may be interesting: - The oracle tends to privilege the full scan - The full scan works marginally faster than the index scan - The default block size in the database used for testing is 8K (4 times the Informix default page size for Solaris). This can be very relevant, because it reduces the number of data pages that mus be fetched - The number of bufgets is higher when the index is forced - The Oracle is probably running in file system so it may be taking advantage of the file system cache Nevertheless I still think the engine could do a better job by either: - sort the rowids - understand in which cases a sequential scan is effectively better and choose it (I've seen lot's of situations in where this happens, but given that in this case the cache seems to be the key to performance it's probably very hard to give this level of "intelligence" to the optimizer) Regards and thanks a lot for a good exercise of optimization you provided. On Sat, Dec 5, 2009 at 3:39 PM, Fernando Nunes <domusonline@gmail.com> wrote: Fernando Nunes wrote: > fandelau wrote: >> Hello to all! >> I think I've found a nasty bug in IDS 11.xx on Solaris (SunOS 5.10) >> (yes I got the same results in both 11.10.FC3 and 11.50.FC3)... here >> are the details: >> >> I got a table 402 bytes wide, 78 columns all but 1 are either integer >> or float/decimal, with 13.1 million records in it (please find the >> schema below). Table has 1 extent and it is not fragmented. I was >> requested to index 13 of the columns, all single column indexes >> indexes, which were created in a separate dbspace from the data. One >> of the indexes is on a column that has 11 million nulls, the other 2.1 >> million records have what I would call normal cardinality. A simple >> query (on a freshly bounced instance) with a range filter on that >> column returns in about 45 minutes on 11.10 and 22 minutes on 11.50, >> having the query optimizer choose an index path on the index in >> question. When the index was dropped and the same query ran on a seq >> scan, it took 1.5 minutes on either version (freshly bounced)... >> >> select kic_kepler_id, ra, dec, kic_teff from kicplus where >> kic_teff>5000 and kic_teff<6500 >> being kic_teff the indexed column. >> >> I have ran the query as a count, as opposed to fetching any columns >> and it comes back in a few seconds... >> >> select count(*) from kicplus where kic_teff>5000 and >> kic_teff<6500 >> >> So the index is being used correctly and finds the data requested. >> >> I know the cardinality of the column is skewed, but 44/22 minutes? >> >> I have already opened a call with IBM, and they were able to duplicate >> this behavior, and they tried to blame it on engine config. While they >> were doing their thing, I tried this in 2 other servers with different >> configurations (same platform) and gotten the exact same results... >> I'm willing to grant that engine config would slow down the query a >> bit, perhaps from a few seconds to a couple of minutes, 5 minutes >> max... but 44/22 minutes? I'm sorry but I refuse to believe it is >> engine config. >> >> While all this was happening, the appl team ran the same test on a >> vanilla installation of Oracle, no fancy stuff, and it comes back in a >> few seconds. When I told this to the IBM engineer, he stopped on his >> tracks and replied that he was going to involve more resources into >> investigating this issue. >> >> I hope they are really looking into this. Meanwhile, we have decided >> to drop the index on that column and opted for the seq scan, but >> nonetheless this behavior is not acceptable. >> >> Has anyone suffered this issue? Any hints? let me know if I can >> provide anymore details. >> >> Happy Thanksgiving Day to y'all! >> >> >> Ramon >> >> create table "informix".kicplus >> ( >> kic_kepler_id integer, >> kic_tmid integer, >> kic_tm_designation char(50), >> kic_fov_flag integer, >> kct_ktc_flag integer, >> kic_ra float, >> ra float, >> dec float, >> kic_glon float, >> kic_glat float, >> kic_parallax smallfloat, >> kic_pmtotal smallfloat, >> kic_pmra smallfloat, >> kic_pmdec smallfloat, >> kic_umag smallfloat, >> kic_gmag smallfloat, >> kic_rmag smallfloat, >> kic_imag smallfloat, >>

Ian Michael Gumby wrote: > I have to agree that the issue is interesting. > > The fact that VMWare killed the query performance is yet another reason > why not to consider 'server consolidation', or at least VMWare's > consolidation with respect to database servers. > I'm not payed to defend VMWare and that subject is completeley off-topic in this thread. But I find it hard to believe that the though of judging VMWare in a laptop without having the slightest idea of it's configuration or my instance configuration has crossed your mind... It's legitime to compare the different queries in each environment, but going beyond that is not fair. > There are some other questions that we didn't ask... > > > I think you mentioned or suggested clustering your data around the > index. That too would boost performance. > Yes, as I verified and wrote. It's the same thing as the trick of ordering the rowids... Regards.

> From: domusonline@gmail.com > Subject: Re: Found a possible bug in IDS 11.x... and a nasty one at that... has anyone suffered this? > Date: Mon, 7 Dec 2009 23:14:12 +0000 > To: informix-list@iiug.org > > Ian Michael Gumby wrote: > > I have to agree that the issue is interesting. > > > > > The fact that VMWare killed the query performance is yet another reason > > why not to consider 'server consolidation', or at least VMWare's > > consolidation with respect to database servers. > > > > I'm not payed to defend VMWare and that subject is completeley off-topic > in this thread. But I find it hard to believe that the though of judging > VMWare in a laptop without having the slightest idea of it's > configuration or my instance configuration has crossed your mind... > Huh? What makes you think that I'm just judging this on the user's experience or rather your experience on your laptop? That's a piss poor assumption on your part. I'm also judging this on my experience and I've seen some questionable issues and unexplained quirks. Now I know that I'm not the only one, so I do have to say that I don't believe that going the virtualization route makes the most sense. > > There are some other questions that we didn't ask... > > > > > > I think you mentioned or suggested clustering your data around the > > index. That too would boost performance. > > > > Yes, as I verified and wrote. It's the same thing as the trick of > ordering the rowids... > > Which goes back to the issue of the index itself. Since you purport to be a database 'guru', riddle me this... What happens when you have a high number of rows with the same index value? Does the optimizer take that in to consideration? If not, well, then there's part of your answer. But hey! What do I know? Definitely more than I'm allowed to talk about these days. ;-) -G _________________________________________________________________ Windows Live Hotmail is faster and more secure than ever. http://www.microsoft.com/windows/windowslive/hotmail_bl1/hotmail_bl1.aspx?ocid=PID23879::T:WLMTAGL:ON:WL:en-ww:WM_IMHM_1:092009

Ian Michael Gumby wrote: > Since you purport to be a database 'guru', riddle me this... What > happens when you have a high number of rows with the same index value? Really... I don't. I found this interesting and tried to help. My first guess was right and I think I managed to show some proof... That simple.... you don't have to be a guru to have a feeling and test it... > Does the optimizer take that in to consideration? > If not, well, then there's part of your answer. The problem here is that by fetching the data pages in the order you get the rowids from the index, it's terribly slow. And the engine is not fetching more pages than it should... On the other hand, when we fetch them in an ordered way (I manage to do that by altering the index to cluster of by forcing an order on the rowids) we get a real nice performance boost. The reason apparently is the several caches that may be involved (file system if you're using cooked files without DIRECT_IO, or hardware caches). In the case of VMWare the stop/start of the instance and the attempts to flush the filesystem cache between attempts were not able to slow it down. I assume that the VMWare/Windows itself do some caching also. What I think it's amazing is the magnitude of improvement we get. I would expect some improvement like I wrote in my first post, but not as much as I've seen in either virtual and physical machines. After we request page X, the hardware will read from X to X + N and will populate the cache. When we request another page Y, if X < Y < X + N the fetch will come from the cache. And that really speeds up... Of course that different data and different hardware can benefit more or less from this, but it's something to take into account. The number of NULLs or the number of repetitions in the index becomes largely irrelevant after we see that the SELECT COUNT(*) is very fas using the index. But well... The PMR is progressing and the OP will be kept informed of any conclusions. Regards