joel
March 7, 2025, 5:24am
1
Hi everyone,
I'm hoping to get some advice from the community on a performance issue we're experiencing with our BIKA LIMS (Plone 4.x) site. We're using RelStorage to store our ZODB data in PostgreSQL, and the object_state table has grown to an extremely large size (approximately 44 million rows). This is causing severe performance problems, making the system almost unusable.
Performance Issues:
Login Time: Up to 2 minutes.
BIKA Samples Page Load Time: Up to 3 minutes.
PostgreSQL Query Logs: Show that the application appears to be scanning the object_state table one record at a time during these slow operations:SELECT encode(state, 'base64'), tid FROM object_state WHERE zoid = xxxx
Here's a summary of our setup and what we've tried so far:
Application: BIKA LIMS (Plone 4.x based)
Plone Version: 4.x (Zope 2.13.21)
Storage: RelStorage (PostgreSQL)
object_state Size: Approximately 44 million rows
keep-history = false: This is explicitly set in our relpack.cfg and buildout.cfg.Packing: We are using a relstorage_pack script daily.
Hanging Transactions: Restarting the Zope instance does not shrink the object_state table, suggesting that hanging transactions are not the primary cause.
Vacuum/Analyze: We have run VACUUM ANALYZE object_state;, and autovacuum is enabled, but the performance improvement has been minimal.
Key Findings:
SELECT COUNT(DISTINCT zoid) FROM object_state; returns approximately 44 million, indicating that we genuinely have a very large number of distinct objects.SELECT zoid FROM object_state GROUP BY zoid HAVING COUNT(DISTINCT tid) > 1; returns 0 rows, indicating that no object has more than one distinct tid (i.e., no history is being retained).
The Puzzle:
Given keep-history = false and the fact that no objects have multiple versions, we are struggling to understand why we have so many objects in the first place. It seems highly unusual to have 44 million objects, and the performance is crippling our system.
We're open to any suggestions or advice on how to diagnose and address this performance crisis - is there anything else that is recommended for us to try?
Thanks in advance for your help!
yurj
March 7, 2025, 8:58am
2
Do you have versioning enabled? How many objects do you have from the plone catalog? ZODB object caches?
Did you search in the forum? I've found this that maybe can give some hint:
When I check the current SQL statement that is running on the database server, it is always this:
SELECT
zoid,
tid,
prev_tid,
current_object_state.state
FROM
temp_store
JOIN current_object_state
USING (zoid)
WHERE (tid <> prev_tid)
If I call this manually it needs round about 35 seconds and returns an empty list. It is not clear to me what it does. But it does this for every transaction if sure will take a while. I could imagine that there are some index…
but seems to be related to the speed of the query. Anyway, adding an index can speed up that query while you're looking for the real problem.
seanupton
March 7, 2025, 4:43pm
3
In my opinion, you need to export a filestorage for analysis using zodbconvert utility shipped with RelStorage, then use the analyze script provided with ZODB on that filestorage, like:
./bin/zopepy -m ZODB.scripts.analyze Data.fs
You need more information, this should be a good starting point.
2 Likes
joel
March 9, 2025, 11:48pm
4
Hi Yuri,
Thanks for the reply. We do not have versioning enabled.
I did come across the thread you linked in my research but in this case unfortunately their solution won't apply to us. Our relstorage table doesn't include a current_object table and the other relevant tables already have sensible indexes configured.
jensens
March 10, 2025, 12:36am
5
joel:
Given keep-history = false and the fact that no objects have multiple versions, we are struggling to understand why we have so many objects in the first place. It seems highly unusual to have 44 million objects, and the performance is crippling our system.
If you delete objects, they wont get deleted from RelStorage without packing, do you pack your DB regularly?
joel
March 10, 2025, 12:38am
6
Hey Sean,
Thanks for the suggestion to export and further analyse the data.
The zopepy output is quite large and I'm not completely sure what I'm looking at, but I've pasted it below. Is there antything out of the ordinary you can see here?
Processed 44297922 records in 701001 transactions
Average record size is 364.22 bytes
Average transaction size is 23016.18 bytes
Types used:
Class Name Count TBytes Pct AvgSize
---------------------------------------------- ------- --------- ----- -------
App.ApplicationManager.ApplicationManager 1 107 0.0% 107.00
App.Product.ProductFolder 1 34 0.0% 34.00
BTrees.IIBTree.IIBTree 51087 12074344 0.1% 236.35
BTrees.IIBTree.IIBucket 195148 131724324 0.8% 675.00
BTrees.IIBTree.IISet 1374772 541239798 3.4% 393.69
BTrees.IIBTree.IITreeSet 21005995 1038681352 6.4% 49.45
BTrees.IOBTree.IOBTree 748203 144177135 0.9% 192.70
BTrees.IOBTree.IOBucket 3589611 4519398933 28.0% 1259.02
BTrees.Length.Length 9584 268859 0.0% 28.05
BTrees.OIBTree.OIBTree 5368 41362287 0.3% 7705.34
BTrees.OIBTree.OIBucket 686278 1158743293 7.2% 1688.45
BTrees.OOBTree.OOBTree 36130 56731882 0.4% 1570.22
BTrees.OOBTree.OOBucket 1076373 1137688734 7.1% 1056.97
BTrees.OOBTree.OOSet 358116 36207975 0.2% 101.11
OFS.Application.Application 1 1956 0.0% 1956.00
OFS.DTMLMethod.DTMLMethod 1 1375 0.0% 1375.00
OFS.Folder.Folder 4872909 611756272 3.8% 125.54
OFS.Image.File 311 9180840 0.1% 29520.39
OFS.Image.Image 4 44660 0.0% 11165.00
OFS.Image.Pdata 434 30731593 0.2% 70810.12
Persistence.mapping.PersistentMapping 2157170 1512845001 9.4% 701.31
...ATContentTypes.content.document.ATDocument 19 26471 0.0% 1393.21
...ATContentTypes.content.folder.ATFolder 1 1983 0.0% 1983.00
...ATContentTypes.content.newsitem.ATNewsItem 2 2754 0.0% 1377.00
Products.ATContentTypes.tool.atct.ATCTTool 1 2148 0.0% 2148.00
Products.ATContentTypes.tool.topic.TopicIndex 58 12948 0.0% 223.24
...Archetypes.ArchetypeTool.ArchetypeTool 1 7927 0.0% 7927.00
Products.Archetypes.BaseUnit.BaseUnit 3076219 527780019 3.3% 171.57
Products.Archetypes.ReferenceEngine.Reference 1042389 369792803 2.3% 354.76
...ReferenceEngine.ReferenceBaseCatalog 1 518 0.0% 518.00
...Archetypes.ReferenceEngine.ReferenceCatalog 1 311 0.0% 311.00
Products.Archetypes.UIDCatalog.UIDBaseCatalog 1 495 0.0% 495.00
Products.Archetypes.UIDCatalog.UIDCatalog 1 291 0.0% 291.00
...Archetypes.references.HoldingReference 2753557 1006932396 6.2% 365.68
...BTreeFolder2.BTreeFolder2.BTreeFolder2 1 206 0.0% 206.00
...CMFActionIcons.ActionIconsTool.ActionIcon 7 1968 0.0% 281.14
Products.CMFCore.ActionInformation.Action 46 22154 0.0% 481.61
...CMFCore.ActionInformation.ActionCategory 8 4171 0.0% 521.38
...CMFCore.ActionInformation.ActionInformation 271 86862 0.0% 320.52
...CachingPolicyManager.CachingPolicyManager 1 280 0.0% 280.00
...ContentTypeRegistry.ContentTypeRegistry 1 477 0.0% 477.00
...ContentTypeRegistry.ExtensionPredicate 6 735 0.0% 122.50
...ContentTypeRegistry.MajorMinorPredicate 11 1364 0.0% 124.00
Products.CMFCore.DirectoryView.DirectoryView 34 6145 0.0% 180.74
Products.CMFCore.Expression.Expression 1003 94814 0.0% 94.53
...CMFCore.TypesTool.FactoryTypeInformation 15 13844 0.0% 922.93
Products.CMFDefault.MetadataTool.ElementSpec 6 864 0.0% 144.00
...MetadataTool.MetadataElementPolicy 6 522 0.0% 87.00
...CMFDefault.MetadataTool.MetadataSchema 1 145 0.0% 145.00
Products.CMFDiffTool.CMFDiffTool.CMFDiffTool 1 552 0.0% 552.00
...fti.DynamicViewTypeInformation 123 96716 0.0% 786.31
...CMFEditions.ArchivistTool.ArchivistTool 1 106 0.0% 106.00
Products.CMFEditions.ArchivistTool.ObjectData 521 117523 0.0% 225.57
...CopyModifyMergeRepositoryTool 1 1124 0.0% 1124.00
...KeepLastNVersionsTool.KeepLastNVersionsTool 1 122 0.0% 122.00
...ModifierRegistryTool.ModifierRegistryTool 1 1786 0.0% 1786.00
...CMFEditions.Modifiers.ConditionalModifier 3 891 0.0% 297.00
...Modifiers.ConditionalTalesModifier 11 4044 0.0% 367.64
...ReferenceFactoriesTool 1 124 0.0% 124.00
...AbortVersioningOfLargeFilesAndImages 1 266 0.0% 266.00
...SkipVersioningOfLargeFilesAndImages 1 263 0.0% 263.00
...VersionPolicies.ATVersionOnEditPolicy 1 157 0.0% 157.00
...CMFEditions.VersionPolicies.VersionPolicy 1 139 0.0% 139.00
...CMFEditions.ZVCStorageTool.ShadowHistory 165 32218 0.0% 195.26
...CMFEditions.ZVCStorageTool.ShadowStorage 1 112 0.0% 112.00
...CMFEditions.ZVCStorageTool.ZVCAwareWrapper 521 109591 0.0% 210.35
...CMFEditions.ZVCStorageTool.ZVCStorageTool 1 283 0.0% 283.00
...historyidhandlertool.HistoryIdHandlerTool 1 120 0.0% 120.00
...CMFFormController.FormAction.FormAction 6 2276 0.0% 379.33
...FormAction.FormActionContainer 1 390 0.0% 390.00
...CMFFormController.FormAction.FormActionKey 8 841 0.0% 105.12
...FormController.FormController 1 353 0.0% 353.00
...FormValidator.FormValidator 2 330 0.0% 165.00
...FormValidator.FormValidatorContainer 1 275 0.0% 275.00
...FormValidator.FormValidatorKey 4 400 0.0% 100.00
...CMFPlone.ActionIconsTool.ActionIconsTool 1 628 0.0% 628.00
Products.CMFPlone.ActionsTool.ActionsTool 1 791 0.0% 791.00
Products.CMFPlone.CalendarTool.CalendarTool 1 162 0.0% 162.00
Products.CMFPlone.CatalogTool.CatalogTool 1 682 0.0% 682.00
...CMFPlone.DiscussionTool.DiscussionTool 1 105 0.0% 105.00
Products.CMFPlone.FactoryTool.FactoryTool 1 1370 0.0% 1370.00
Products.CMFPlone.InterfaceTool.InterfaceTool 1 150 0.0% 150.00
Products.CMFPlone.MetadataTool.MetadataTool 1 237 0.0% 237.00
Products.CMFPlone.MigrationTool.MigrationTool 1 165 0.0% 165.00
...CMFPlone.PloneControlPanel.PloneConfiglet 66 27953 0.0% 423.53
...PloneControlPanel.PloneControlPanel 1 1345 0.0% 1345.00
Products.CMFPlone.PloneTool.PloneTool 1 132 0.0% 132.00
Products.CMFPlone.Portal.PloneSite 1 21779 0.0% 21779.00
...CMFPlone.PropertiesTool.PropertiesTool 1 567 0.0% 567.00
...PropertiesTool.SimpleItemWithProperties 4 10594 0.0% 2648.50
...QuickInstallerTool.QuickInstallerTool 1 1115 0.0% 1115.00
...CMFPlone.RegistrationTool.RegistrationTool 1 190 0.0% 190.00
Products.CMFPlone.SkinsTool.SkinsTool 1 2306 0.0% 2306.00
...TranslationServiceTool 1 179 0.0% 179.00
Products.CMFPlone.TypesTool.TypesTool 1 9903 0.0% 9903.00
Products.CMFPlone.URLTool.URLTool 1 157 0.0% 157.00
Products.CMFPlone.UndoTool.UndoTool 1 156 0.0% 156.00
Products.CMFPlone.WorkflowTool.WorkflowTool 1 2242 0.0% 2242.00
...InstalledProduct.InstalledProduct 12 15549 0.0% 1295.75
...UniqueIdAnnotationTool.UniqueIdAnnotation 678 64410 0.0% 95.00
...UniqueIdAnnotationTool 1 119 0.0% 119.00
...UniqueIdGeneratorTool.UniqueIdGeneratorTool 1 134 0.0% 134.00
...UniqueIdHandlerTool.UniqueIdHandlerTool 1 113 0.0% 113.00
...DCWorkflow.DCWorkflow.DCWorkflowDefinition 23 22657 0.0% 985.09
Products.DCWorkflow.Guard.Guard 113 11691 0.0% 103.46
Products.DCWorkflow.Scripts.Scripts 23 1794 0.0% 78.00
Products.DCWorkflow.States.StateDefinition 88 30627 0.0% 348.03
Products.DCWorkflow.States.States 23 7620 0.0% 331.30
...DCWorkflow.Transitions.TransitionDefinition 101 51703 0.0% 511.91
Products.DCWorkflow.Transitions.Transitions 23 8516 0.0% 370.26
...DCWorkflow.Variables.VariableDefinition 115 40758 0.0% 354.42
Products.DCWorkflow.Variables.Variables 23 8947 0.0% 389.00
...DCWorkflow.Worklists.WorklistDefinition 3 1314 0.0% 438.00
Products.DCWorkflow.Worklists.Worklists 23 2340 0.0% 101.74
...ExtendedPathIndex.ExtendedPathIndex 4 1568 0.0% 392.00
Products.GenericSetup.tool.SetupTool 1 8895 0.0% 8895.00
Products.MailHost.MailHost.MailHost 1 299 0.0% 299.00
...MimetypesRegistry.MimeTypeItem.MimeTypeItem 1278 273663 0.0% 214.13
...MimeTypesRegistry.MimeTypesRegistry 1 726 0.0% 726.00
...mime_types.mtr_mimetypes.application_msword 1 144 0.0% 144.00
...mtr_mimetypes.application_octet_stream 1 167 0.0% 167.00
...mime_types.mtr_mimetypes.application_rtf 1 109 0.0% 109.00
...mime_types.mtr_mimetypes.text_html 1 139 0.0% 139.00
...mime_types.mtr_mimetypes.text_html_safe 1 101 0.0% 101.00
...mime_types.mtr_mimetypes.text_plain 1 187 0.0% 187.00
...mime_types.mtr_mimetypes.text_pre_plain 1 101 0.0% 101.00
...mime_types.mtr_mimetypes.text_python 1 96 0.0% 96.00
...mime_types.mtr_mimetypes.text_rest 1 96 0.0% 96.00
...mime_types.mtr_mimetypes.text_structured 1 102 0.0% 102.00
...mtr_mimetypes.text_web_intelligent 1 107 0.0% 107.00
...mime_types.mtr_mimetypes.text_web_markdown 1 104 0.0% 104.00
...mime_types.mtr_mimetypes.text_web_textile 1 103 0.0% 103.00
...mime_types.mtr_mimetypes.text_wiki 1 96 0.0% 96.00
...mime_types.mtr_mimetypes.text_xml 1 135 0.0% 135.00
...ZopePageTemplate.ZopePageTemplate 1 373 0.0% 373.00
...PasswordResetTool.PasswordResetTool 1 181 0.0% 181.00
...PloneLanguageTool.LanguageTool.LanguageTool 1 589 0.0% 589.00
Products.PlonePAS.plugins.autogroup.AutoGroup 1 198 0.0% 198.00
...cookie_handler.ExtendedCookieAuthHelper 2 371 0.0% 185.50
Products.PlonePAS.plugins.group.GroupManager 1 229 0.0% 229.00
...plugins.local_role.LocalRolesManager 1 105 0.0% 105.00
...plugins.passwordpolicy.PasswordPolicyPlugin 1 139 0.0% 139.00
...property.ZODBMutablePropertyProvider 1 198 0.0% 198.00
...PlonePAS.plugins.role.GroupAwareRoleManager 1 200 0.0% 200.00
...PlonePAS.plugins.ufactory.PloneUserFactory 1 115 0.0% 115.00
Products.PlonePAS.plugins.user.UserManager 1 228 0.0% 228.00
Products.PlonePAS.tools.groupdata.GroupData 13 962 0.0% 74.00
...PlonePAS.tools.groupdata.GroupDataTool 1 324 0.0% 324.00
Products.PlonePAS.tools.groups.GroupsTool 1 148 0.0% 148.00
Products.PlonePAS.tools.memberdata.MemberData 66 4954 0.0% 75.06
...PlonePAS.tools.memberdata.MemberDataTool 1 1038 0.0% 1038.00
...PlonePAS.tools.membership.MembershipTool 1 148 0.0% 148.00
...PluggableAuthService.PluggableAuthService 2 3525 0.0% 1762.50
...ChallengeProtocolChooser 2 366 0.0% 183.00
...HTTPBasicAuthHelper.HTTPBasicAuthHelper 2 296 0.0% 148.00
...RecursiveGroupsPlugin.RecursiveGroupsPlugin 1 163 0.0% 163.00
...RequestTypeSniffer.RequestTypeSniffer 2 244 0.0% 122.00
...plugins.ZODBRoleManager.ZODBRoleManager 1 202 0.0% 202.00
...plugins.ZODBUserManager.ZODBUserManager 1 248 0.0% 248.00
...BooleanIndex.BooleanIndex.BooleanIndex 7 2880 0.0% 411.43
...PluginIndexes.DateIndex.DateIndex.DateIndex 26 9055 0.0% 348.27
...DateRangeIndex.DateRangeIndex 1 433 0.0% 433.00
...FieldIndex.FieldIndex.FieldIndex 148 50783 0.0% 343.13
...KeywordIndex.KeywordIndex.KeywordIndex 14 4958 0.0% 354.14
...PluginIndexes.UUIDIndex.UUIDIndex.UUIDIndex 1 331 0.0% 331.00
...PluginRegistry.PluginRegistry 2 3590 0.0% 1795.00
Products.PortalTransforms.Transform.Transform 25 11703 0.0% 468.12
...TransformEngine.TransformTool 1 1635 0.0% 1635.00
...tools.CSSRegistry.CSSRegistryTool 1 7307 0.0% 7307.00
...tools.CSSRegistry.Stylesheet 73 10950 0.0% 150.00
...tools.JSRegistry.JSRegistryTool 1 16926 0.0% 16926.00
...tools.JSRegistry.JavaScript 148 22052 0.0% 149.00
...Sessions.BrowserIdManager.BrowserIdManager 1 322 0.0% 322.00
...SessionDataManager.SessionDataManager 1 234 0.0% 234.00
...SessionDataManagerTraverser 1 156 0.0% 156.00
...VirtualHostMonster.VirtualHostMonster 1 92 0.0% 92.00
...SiteErrorLog.SiteErrorLog.SiteErrorLog 2 310 0.0% 155.00
...AcceleratedHTTPCacheManager 1 322 0.0% 322.00
...RAMCacheManager.RAMCacheManager 2 698 0.0% 349.00
Products.TinyMCE.utility.TinyMCE 1 2568 0.0% 2568.00
Products.ZCTextIndex.OkapiIndex.OkapiIndex 10 3810 0.0% 381.00
Products.ZCTextIndex.ZCTextIndex.PLexicon 6 3191 0.0% 531.83
Products.ZCTextIndex.ZCTextIndex.ZCTextIndex 10 2659 0.0% 265.90
Products.ZCatalog.Catalog.Catalog 4 10714 0.0% 2678.50
...ZODBMountPoint.MountedObject.MountedObject 1 136 0.0% 136.00
Products.ZopeVersionControl.EventLog.EventLog 165 17325 0.0% 105.00
Products.ZopeVersionControl.EventLog.LogEntry 521 265862 0.0% 510.29
...ZopeVersionControl.Utility.VersionInfo 877 146528 0.0% 167.08
...VersionHistory.BranchInfo 165 36465 0.0% 221.00
...ZopeRepository.ZopeRepository 1 264 0.0% 264.00
...ZopeVersionControl.ZopeVersion.ZopeVersion 521 100539 0.0% 192.97
...ZopeVersionHistory.ZopeVersionHistory 165 47010 0.0% 284.91
ZODB.blob.Blob 78812 1655052 0.0% 21.00
bika.lims.catalog.BikaAnalysisCatalog 1 440 0.0% 440.00
bika.lims.catalog.BikaCatalog 1 423 0.0% 423.00
bika.lims.catalog.BikaSetupCatalog 1 434 0.0% 434.00
bika.lims.content.analysis.Analysis 712988 1041379047 6.5% 1460.58
...content.analysiscategory.AnalysisCategory 8 6513 0.0% 814.12
...content.analysisprofile.AnalysisProfile 5 4827 0.0% 965.40
...content.analysisrequest.AnalysisRequest 88970 284356251 1.8% 3196.09
...AnalysisRequestsFolder 1 1326 0.0% 1326.00
...content.analysisservice.AnalysisService 610 982511 0.0% 1610.67
bika.lims.content.arreport.ARReport 60348 1591471796 9.9% 26371.57
bika.lims.content.batch.Batch 9053 14556862 0.1% 1607.96
bika.lims.content.batchfolder.BatchFolder 1 1304 0.0% 1304.00
bika.lims.content.bikasetup.BikaSetup 1 2957 0.0% 2957.00
bika.lims.content.calculation.Calculation 11 13378 0.0% 1216.18
bika.lims.content.client.Client 1 2602 0.0% 2602.00
bika.lims.content.clientfolder.ClientFolder 1 1362 0.0% 1362.00
bika.lims.content.contact.Contact 21 31735 0.0% 1511.19
bika.lims.content.container.Container 3 2435 0.0% 811.67
bika.lims.content.containertype.ContainerType 1 702 0.0% 702.00
bika.lims.content.department.Department 6 4833 0.0% 805.50
bika.lims.content.instrument.Instrument 14 24875 0.0% 1776.79
...InstrumentCertification 14 15280 0.0% 1091.43
...lims.content.instrumenttype.InstrumentType 11 9169 0.0% 833.55
bika.lims.content.invoicefolder.InvoiceFolder 1 1220 0.0% 1220.00
bika.lims.content.labcontact.LabContact 7 10256 0.0% 1465.14
bika.lims.content.laboratory.Laboratory 1 2264 0.0% 2264.00
bika.lims.content.labproduct.LabProduct 1 877 0.0% 877.00
bika.lims.content.manufacturer.Manufacturer 8 6613 0.0% 826.62
bika.lims.content.method.Method 47 51170 0.0% 1088.72
bika.lims.content.methods.Methods 1 1336 0.0% 1336.00
bika.lims.content.preservation.Preservation 1 883 0.0% 883.00
...content.pricelistfolder.PricelistFolder 1 1266 0.0% 1266.00
bika.lims.content.queryfolder.QueryFolder 1 1351 0.0% 1351.00
...content.referenceanalysis.ReferenceAnalysis 18454 23672378 0.1% 1282.78
...referencedefinition.ReferenceDefinition 12 17460 0.0% 1455.00
...content.referencesample.ReferenceSample 12 1305482 0.0% 108790.17
...ReferenceSamplesFolder 1 1284 0.0% 1284.00
bika.lims.content.report.Report 240 197006 0.0% 820.86
bika.lims.content.reportfolder.ReportFolder 1 1529 0.0% 1529.00
bika.lims.content.sample.Sample 88995 149552186 0.9% 1680.46
...content.samplepartition.SamplePartition 88995 109413633 0.7% 1229.44
bika.lims.content.samplepoint.SamplePoint 1632 1276543 0.0% 782.20
bika.lims.content.samplesfolder.SamplesFolder 1 1299 0.0% 1299.00
bika.lims.content.sampletype.SampleType 2718 2450012 0.0% 901.40
bika.lims.content.subgroup.SubGroup 2 1658 0.0% 829.00
bika.lims.content.supplier.Supplier 8 12815 0.0% 1601.88
...content.supplyorderfolder.SupplyOrderFolder 1 1226 0.0% 1226.00
bika.lims.content.worksheet.Worksheet 3687 6537481 0.0% 1773.12
...content.worksheetfolder.WorksheetFolder 1 1307 0.0% 1307.00
...content.worksheettemplate.WorksheetTemplate 9 9339 0.0% 1037.67
...bika_analysiscategories.AnalysisCategories 1 1019 0.0% 1019.00
...bika_analysisprofiles.AnalysisProfiles 1 1007 0.0% 1007.00
...bika_analysisservices.AnalysisServices 1 1211 0.0% 1211.00
...bika_analysisspecs.AnalysisSpecs 1 994 0.0% 994.00
...controlpanel.bika_arpriorities.ARPriorities 1 977 0.0% 977.00
...controlpanel.bika_artemplates.ARTemplates 1 978 0.0% 978.00
...bika_attachmenttypes.AttachmentTypes 1 1117 0.0% 1117.00
...controlpanel.bika_batchlabels.BatchLabels 1 972 0.0% 972.00
...controlpanel.bika_calculations.Calculations 1 1064 0.0% 1064.00
...controlpanel.bika_containers.Containers 1 964 0.0% 964.00
...bika_containertypes.ContainerTypes 1 993 0.0% 993.00
...controlpanel.bika_departments.Departments 1 975 0.0% 975.00
...controlpanel.bika_instruments.Instruments 1 971 0.0% 971.00
...bika_instrumenttypes.InstrumentTypes 1 1000 0.0% 1000.00
...controlpanel.bika_labcontacts.LabContacts 1 972 0.0% 972.00
...controlpanel.bika_labproducts.LabProducts 1 972 0.0% 972.00
...bika_manufacturers.Manufacturers 1 985 0.0% 985.00
...bika_preservations.Preservations 1 985 0.0% 985.00
...ReferenceDefinitions 1 1035 0.0% 1035.00
...bika_sampleconditions.SampleConditions 1 1007 0.0% 1007.00
...bika_samplematrices.SampleMatrices 1 990 0.0% 990.00
...controlpanel.bika_samplepoints.SamplePoints 1 971 0.0% 971.00
...controlpanel.bika_sampletypes.SampleTypes 1 972 0.0% 972.00
...bika_samplingdeviations.SamplingDeviations 1 1021 0.0% 1021.00
...controlpanel.bika_srtemplates.SRTemplates 1 984 0.0% 984.00
...bika_storagelocations.StorageLocations 1 1007 0.0% 1007.00
...lims.controlpanel.bika_subgroups.SubGroups 1 958 0.0% 958.00
...lims.controlpanel.bika_suppliers.Suppliers 1 956 0.0% 956.00
...bika_worksheettemplates.WorksheetTemplates 1 1021 0.0% 1021.00
...wine.controlpanel.bika_cultivars.Cultivars 1 949 0.0% 949.00
bika.wine.controlpanel.bika_regions.Regions 1 937 0.0% 937.00
...bika_storageconditions.StorageConditions 1 1006 0.0% 1006.00
...TransportConditions 1 1020 0.0% 1020.00
...wine.controlpanel.bika_winetypes.WineTypes 1 949 0.0% 949.00
...workspace.WorkspaceLocalRoleManager 1 96 0.0% 96.00
...registry.PersistentComponents 1 1844 0.0% 1844.00
persistent.list.PersistentList 1508 3816459 0.0% 2530.81
persistent.mapping.PersistentMapping 11768 858402 0.0% 72.94
plone.app.blob.field.BlobWrapper 78812 10566318 0.1% 134.07
plone.app.controlpanel.markup.WickedSettings 1 53 0.0% 53.00
...app.customerize.tool.ViewTemplateContainer 1 179 0.0% 179.00
plone.app.discussion.tool.CommentingTool 1 147 0.0% 147.00
plone.app.folder.nogopip.GopipIndex 3 470 0.0% 156.67
plone.app.i18n.locales.countries.Countries 1 136 0.0% 136.00
...app.i18n.locales.languages.ContentLanguages 1 165 0.0% 165.00
...i18n.locales.languages.MetadataLanguages 1 167 0.0% 167.00
...app.portlets.portlets.calendar.Assignment 3 695 0.0% 231.67
plone.app.portlets.portlets.classic.Assignment 1 293 0.0% 293.00
plone.app.portlets.portlets.events.Assignment 96 20682 0.0% 215.44
plone.app.portlets.portlets.login.Assignment 1 223 0.0% 223.00
...app.portlets.portlets.navigation.Assignment 1 345 0.0% 345.00
plone.app.portlets.portlets.news.Assignment 96 20298 0.0% 211.44
plone.app.portlets.portlets.recent.Assignment 97 19200 0.0% 197.94
plone.app.portlets.portlets.review.Assignment 97 18036 0.0% 185.94
...GroupDashboardPortletAssignmentMapping 8 2820 0.0% 352.50
...portlets.storage.PortletAssignmentMapping 64 16386 0.0% 256.03
...storage.UserPortletAssignmentMapping 274 92619 0.0% 338.03
...app.redirector.storage.RedirectionStorage 1 288 0.0% 288.00
plone.app.registry.registry.Registry 1 350 0.0% 350.00
...storage.ViewletSettingsStorage 1 348 0.0% 348.00
...engine.assignments.RuleAssignmentManager 2 358 0.0% 179.00
plone.contentrules.engine.storage.RuleStorage 1 314 0.0% 314.00
plone.keyring.keyring.Keyring 1 460 0.0% 460.00
...mimetype.text_plone_outputfilters_html 1 93 0.0% 93.00
plone.portlets.manager.PortletManager 6 1918 0.0% 319.67
plone.portlets.registration.PortletType 12 3126 0.0% 260.50
plone.portlets.storage.PortletCategoryMapping 18 5076 0.0% 282.00
plone.registry.field.ASCIILine 6 2438 0.0% 406.33
plone.registry.field.Bool 132 53121 0.0% 402.43
plone.registry.field.Choice 3 2087 0.0% 695.67
plone.registry.field.Dict 1 800 0.0% 800.00
plone.registry.field.DottedName 41 10988 0.0% 268.00
plone.registry.field.Int 2 1126 0.0% 563.00
plone.registry.field.List 43 20072 0.0% 466.79
plone.registry.field.Text 64 25464 0.0% 397.88
plone.registry.field.TextLine 194 75977 0.0% 391.63
plone.registry.field.Tuple 3 1951 0.0% 650.33
plone.session.plugins.session.SessionPlugin 1 92 0.0% 92.00
webdav.LockItem.LockItem 595 163803 0.0% 275.30
...PersistentAdapterRegistry 2 7049 0.0% 3524.50
zope.ramcache.ram.RAMCache 1 288 0.0% 288.00
============================================== ======= ========= ===== =======
Total Transactions 701001 22.48k
Total Records 44297922 15756217k 100.0% 364.22
Current Objects 44297922 15756217k 100.0% 364.22
joel
March 10, 2025, 12:43am
7
Hi Jens,
Yes we use relstorage_pack and pack our database nightly. Here is the pack script as well as the relpack config file we use:
I have begun to run zodbpack over the same configuration file which seems to take way longer. I didn't let it finish as once I realised there were no objects with multiple tid's I figured it wouldn't do anything.
For reference, the zodbpack command was on track to pack the database in about 40 hours - it was averaging analyzing 1 million rows per hour.
The relstorage_pack command we run every night (on the same relpack config file) finishes in just a couple of hours, they must be doing something different...
EDIT: I also tried packing my Data.fs output from the zodbconvert command I ran for @seanupton , but I got the following output:INFO The database has already been packed to a later time or no changes have been made since the last pack - Indicating that our nightly packing does seem to be working.
#!/usr/bin/python
# EASY-INSTALL-ENTRY-SCRIPT: 'relstorage-packer==2.1','console_scripts','relstorage_pack'
__requires__ = 'relstorage-packer==2.1'
import sys
from pkg_resources import load_entry_point
if __name__ == '__main__':
sys.exit(
load_entry_point('relstorage-packer==2.1', 'console_scripts', 'relstorage_pack')()
)
<relstorage>
create-schema false
keep-history false
shared-blob-dir true
blob-dir var/files
<postgresql>
dsn dbname='bikadb' user='username' host='127.0.0.1' password='password'
</postgresql>
</relstorage>
1 Like
jensens
March 12, 2025, 11:56am
8
The IITreeSet count is half of your objects! Also the other BTrees-classes are plenty. This looks like a huge catalog with many indexes?
Further below I see
148x FieldIndex (!)
26x DateIndex
14x KeywordIndex
I tend to recommend to switch to some external indexing strategy, like with Solr or ElasticSearch.
tkimnguyen
March 13, 2025, 3:18am
9
When did you first notice your site slowing down? Was it gradual or sudden? Do you have historical performance data?
joel
March 23, 2025, 11:17pm
10
Is an external indexing solution the best workaround here? Ideally I'd also like to understand:
Why we have so many objects considering we only have ~200-250k records including our samples, analysis requests, users, etc
Why our app seems to be scanning/querying them all on every request (including login requests)
If it's possible to reduce the amount of objects whilst not losing data
We no longer have much Zope/Plone knowledge within our team and I think this is the deepest it's been looked at for a long while so I'm flying pretty blind here unfortunately.
jensens
March 24, 2025, 9:52am
11
ad 1)
Reading your output, you have 712988 bika lims "Analysis" records in your Database responsible to fill for 6.5% of your database. Additional there are 88970 AnalysisRequests and 60348 AnalysysReports and 88995 Samples and SamplePartitions. These are the not shocking numbers. It should be totally fine for an application build on top of Plone.
As told above, the database index system (catalog) seems highly bloated. I do not now bika lims, but reading your numbers there is an overly extensive use of indexes in there, responsible for large numbers of objects. IITreeSets are used in indexes and are respsonsible for 21005995 objects (of a toal of 44297922 objects).
There are 3589611 IOBucket objects consuming 28% of the database space. Without deeper analysis I can not tell if they are catalog related or do contains actual data, broken down in several objects (siunce the avg size of each is 1259 Bytes) which would be totally fine.
ad 2)
ad 3)
I am pretty sure this can be fixed, but it may take some effort to find the cause.
yurj
March 24, 2025, 11:01am
12
@joel did you try a clear and rebuild of the catalog? Maybe on a copy of the site...