Crippling Performance Issues: RelStorage/PostgreSQL DB with 44M+ ZODB Objects

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!

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:

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.

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

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.

If you delete objects, they wont get deleted from RelStorage without packing, do you pack your DB regularly?

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

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

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.

When did you first notice your site slowing down? Was it gradual or sudden? Do you have historical performance data?

Is an external indexing solution the best workaround here? Ideally I'd also like to understand:

  1. Why we have so many objects considering we only have ~200-250k records including our samples, analysis requests, users, etc
  2. Why our app seems to be scanning/querying them all on every request (including login requests)
  3. 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.

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 know 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)
This should not happen is probably an implementation error.

ad 3)
~200-250k records is less than the stored Analysis records, so there might be garbage left in the database (which I can not tell without digging into the system).
If so it is possible to clean it up after figuring out what exactly happens here.

I am pretty sure this can be fixed, but it may take some effort to find the cause.

@joel did you try a clear and rebuild of the catalog? Maybe on a copy of the site...