Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0043587
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Retail Modules] Web POScriticalhave not tried2020-03-26 10:412020-03-26 13:10
ReporterALopeteguiView Statuspublic 
Assigned ToRetail 
PriorityurgentResolutionopenFixed in Version
StatusnewFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned To
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0043587: Master data load pagination with offset needs too much memory

DescriptionThe master data pagination uses offset method to make a pagination. This method with high number of rows makes the query slower in each pagination if the data to load does't fit in memory cache.
offset instructs the databases skip the first N results of a query. However, the database must still fetch these rows from the disk and bring them in order before it can send the following ones.
More info in the links:
https://use-the-index-luke.com/no-offset [^]
https://use-the-index-luke.com/sql/partial-results/fetch-next-page [^]

In one client which needs to charge 2Million of product characteristics value, needs 75 minutes to finish the load, caused because of the combination of insufficient memory and offset pagination issue. Because it takes more time for each pagination.
Steps To ReproduceHaving a big amount of master data, por example 500.000 products, and a database server with 8gb RAM, postgres with 2gb shared buffers.

Do full login and check the logs, the pagination will take more time for each offset or will need at least to use more memory.
Proposed SolutionThe proposed solution is also explained in the link above.
Instead of doing offset, doing Seek Method which consists on using the values of the previous page as a delimiter.

Offset method: Explain example: https://explain.depesz.com/s/k2te [^]
  Select M_Product_Ch_Value_ID.*
  from M_Product_Ch_Value_ID
  order by m_product_id
  limit 30000 offset 60000

Seek Method: Explain example: https://explain.depesz.com/s/jRF6 [^]
  Select M_Product_Ch_Value_ID.*
  from M_Product_Ch_Value_ID
  where m_product_id > last_page_product_id
  order by m_product_id
  limit 30000
  
TagsPerformance
Attached Filesdiff file icon paginationbyidprodcharvalue.diff [^] (1,123 bytes) 2020-03-26 13:09 [Show Content]

- Relationships Relation Graph ] Dependency Graph ]

-  Notes
(0118833)
marvintm (developer)
2020-03-26 13:10

Attached a patch with a fix candidate implementation. It needs to be throughly tested, both functionally and in terms of performance improvement.

- Issue History
Date Modified Username Field Change
2020-03-26 10:41 ALopetegui New Issue
2020-03-26 10:41 ALopetegui Assigned To => Retail
2020-03-26 10:41 ALopetegui Triggers an Emergency Pack => No
2020-03-26 10:41 ALopetegui Tag Attached: Performance
2020-03-26 10:43 martinsdan Issue Monitored: martinsdan
2020-03-26 10:44 ALopetegui Summary Master data load pagination with offset needs needs too much memory => Master data load pagination with offset needs too much memory
2020-03-26 10:58 ALopetegui Type design defect => defect
2020-03-26 13:09 marvintm File Added: paginationbyidprodcharvalue.diff
2020-03-26 13:10 marvintm Note Added: 0118833


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker