problem with paging

Charles Crouch ccrouch at redhat.com
Thu Oct 6 13:20:11 UTC 2011



----- Original Message -----
> AFAIK, it affected one other place (the related query when getting
> the
> RESOURCE configuration update map). I fixed that too.
> 
> I didn't scour the rest of the code base looking to see if we do this
> elsewhere. Its possible we have this problem in other places.
> 

You can guess where I'm going :-)...

We need to scour the rest of the code base looking to see if we do this elsewhere 


> On 10/05/2011 08:52 PM, Charles Crouch wrote:
> > so how many other queries does this impact?
> >
> > ----- Original Message -----
> >> for the record, this problem also occurred with postgres.
> >>
> >> and adding ORDER BY fixes the problem on both :)
> >>
> >> Thanks for the tip.
> >>
> >> On 10/05/2011 07:04 PM, John Mazzitelli wrote:
> >>> ok, I'll try that
> >>>
> >>> On 10/05/2011 06:40 PM, Charles Crouch wrote:
> >>>> Are you running on Oracle?
> >>>>
> >>>> If so, then it sounds like we're running into a variation of the
> >>>> problem described here:
> >>>> http://www.jroller.com/sjivan/entry/hibernate_and_oracle_pagination_gotcha
> >>>> i.e. we're asking Oracle to page the results of a query which
> >>>> doesn't have consistent row ordering.
> >>>>
> >>>> Looking at the named query
> >>>> Configuration.QUERY_GET_PLUGIN_CONFIG_MAP_BY_GROUP_ID I'm not
> >>>> seeing any ordering at all, so oracle is free to return the rows
> >>>> in any order it wishes, each time we ask for a new page.
> >>>> Obviously during this process, if you have a sufficiently large
> >>>> result set, some results are never getting returned in any page,
> >>>> in their place we're getting rows which have been returned
> >>>> before, which then get eaten in the 'results' HashMap. So if you
> >>>> hit this issue you'll always end up with fewer rows being
> >>>> reported than expected.
> >>>>
> >>>> So I think we need to make sure that for every query we use with
> >>>> paging that it has an order by clause. I presume oracle is
> >>>> deterministic about sorting on columns with duplicate values in,
> >>>> otherwise we'll have to order by a unique field.
> >>>>
> >>>> In fact I just found a very succinct recommendation from
> >>>> http://community.jboss.org/wiki/Pagination: "Always put an order
> >>>> by clause when paging on Oracle: we have found that on some
> >>>> instances the records are resorted between one page fetch and
> >>>> the
> >>>> subsequent one if no order by is specified."
> >>>>
> >>>> Cheers
> >>>> Charles
> >>>>
> >>>> ----- Original Message -----
> >>>>> While fixing some perf issues and writing this unit test
> >>>>> supporting
> >>>>> large groups, I came across the following bug, but I don't know
> >>>>> what's
> >>>>> going on. Someone enlighten me.
> >>>>>
> >>>>> First, here's some code in
> >>>>> ConfigurationManagerBean.getPersistedPluginConfigurationsForCompatibleGroup
> >>>>> - notice it LOOKS like it wants to chunk the db load via
> >>>>> paging,
> >>>>> but
> >>>>> it
> >>>>> really doesn't:
> >>>>>
> >>>>>             // Configurations are very expensive to load, so
> >>>>>             load
> >>>>>             'em in
> >>>>> chunks to ease the strain on the DB.
> >>>>>             PageControl pageControl = new PageControl(0, 20);
> >>>>>             Query query =
> >>>>> entityManager.createNamedQuery(Configuration.QUERY_GET_PLUGIN_CONFIG_MAP_BY_GROUP_ID);
> >>>>>             query.setParameter("resourceGroupId",
> >>>>>             compatibleGroup.getId());
> >>>>>
> >>>>>             Map<Integer, Configuration>    results = new
> >>>>>             HashMap<Integer,
> >>>>> Configuration>((int) count);
> >>>>>             int rowsProcessed = 0;
> >>>>>             while (true) {
> >>>>>                 List<Object[]>    pagedResults =
> >>>>>                 query.getResultList();
> >>>>>
> >>>>>                 if (pagedResults.size()<= 0) {
> >>>>>                     break;
> >>>>>                 }
> >>>>>
> >>>>>                 for (Object[] result : pagedResults) {
> >>>>>                     results.put((Integer) result[0],
> >>>>>                     (Configuration)
> >>>>> result[1]);
> >>>>>                 }
> >>>>>
> >>>>>                 rowsProcessed += pagedResults.size();
> >>>>>                 if (rowsProcessed>= count) {
> >>>>>                     break;
> >>>>>                 }
> >>>>>                 pageControl.setPageNumber(pageControl.getPageNumber()
> >>>>>                 +
> >>>>>                 1);
> >>>>> // advance the page
> >>>>>                 PersistenceUtility.setDataPage(query,
> >>>>>                 pageControl); //
> >>>>> retrieve one page at a time // MAZZ ASKS - WHY IS THIS
> >>>>> HERE?!?!?
> >>>>>             }
> >>>>>             return results;
> >>>>>
> >>>>> Look at the line that sets the data page (see the "MAZZ ASKS"
> >>>>> comment).
> >>>>> This is wrong. It should be up at the top as the first line in
> >>>>> the
> >>>>> while
> >>>>> loop (and in fact, the analogous method in this SLSB for
> >>>>> resource
> >>>>> configuration DOES have it in that spot - its almost identical
> >>>>> code,
> >>>>> the
> >>>>> pattern is the same, but that setDataPage in this method is in
> >>>>> the
> >>>>> wrong
> >>>>> spot).  So, in fact, this chunk of code does NOT do paging, it
> >>>>> really
> >>>>> loads everything in one big chunk.
> >>>>>
> >>>>> OK, fine, no problem - I fix this to match the same way its
> >>>>> done
> >>>>> in
> >>>>> getPersistedResourceConfigurationsForCompatibleGroup. I step
> >>>>> through
> >>>>> in
> >>>>> a debugger and, yup, it is chunking now. I see the paging
> >>>>> happening
> >>>>> as
> >>>>> the while loop actually does its thing.
> >>>>>
> >>>>> BUT!! IT DOESN'T WORK. I'm getting bad data! In the original
> >>>>> "broken"
> >>>>> state (where it didn't do paging), I was getting 1010 results
> >>>>> (which
> >>>>> is
> >>>>> correct in my case). So even though it didn't chunk, it was
> >>>>> still
> >>>>> correct - I got all the data correctly. But when I "fix" the
> >>>>> code, I
> >>>>> don't get 1010 results - sometimes I got 850, sometimes 1004,
> >>>>> sometimes
> >>>>> 945 - its not consistent, but most times I don't get the full
> >>>>> amount
> >>>>> of
> >>>>> results back! So the paging "works" but I don't get the proper
> >>>>> data
> >>>>> back!
> >>>>>
> >>>>> So, there are two things:
> >>>>>
> >>>>> 1) why is paging not working? What could cause this?
> >>>>> 2) This must mean the other SLSB method
> >>>>> getPersistedResourceConfigurationsForCompatibleGroup is ALSO
> >>>>> BROKEN
> >>>>> and
> >>>>> we don't know it (because it uses the "fixed" way to do the
> >>>>> paging).
> >>>>> I
> >>>>> suspect we just haven't looked closely to notice. I noticed
> >>>>> because I
> >>>>> now have a unit test that actually tests to make sure I get
> >>>>> back
> >>>>> the
> >>>>> proper number of rows back. Where else do we use this paging
> >>>>> (that is
> >>>>> really broken)? This could be bad.
> >>>>>
> >>>>> Note that paging is only broken when I have large # rows (like
> >>>>> over
> >>>>> 500). I don't see the problem for small number of rows (like
> >>>>> 100
> >>>>> or
> >>>>> less).
> >>>>> _______________________________________________
> >>>>> rhq-devel mailing list
> >>>>> rhq-devel at lists.fedorahosted.org
> >>>>> https://fedorahosted.org/mailman/listinfo/rhq-devel
> >>>>>
> >>>> _______________________________________________
> >>>> rhq-devel mailing list
> >>>> rhq-devel at lists.fedorahosted.org
> >>>> https://fedorahosted.org/mailman/listinfo/rhq-devel
> >>> _______________________________________________
> >>> rhq-devel mailing list
> >>> rhq-devel at lists.fedorahosted.org
> >>> https://fedorahosted.org/mailman/listinfo/rhq-devel
> >> _______________________________________________
> >> rhq-devel mailing list
> >> rhq-devel at lists.fedorahosted.org
> >> https://fedorahosted.org/mailman/listinfo/rhq-devel
> >>
> > _______________________________________________
> > rhq-devel mailing list
> > rhq-devel at lists.fedorahosted.org
> > https://fedorahosted.org/mailman/listinfo/rhq-devel
> _______________________________________________
> rhq-devel mailing list
> rhq-devel at lists.fedorahosted.org
> https://fedorahosted.org/mailman/listinfo/rhq-devel
> 


More information about the rhq-devel mailing list