problem with paging

John Mazzitelli mazz at redhat.com
Thu Oct 6 12:22:06 UTC 2011


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.

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


More information about the rhq-devel mailing list