[FOLIO-1702] FOLIO: Database under heavy load causing system super slow Created: 09/Jan/19  Updated: 03/Jun/20  Resolved: 14/Jan/19

Status: Closed
Project: FOLIO
Components: None
Affects versions: None
Fix versions: None

Type: Bug Priority: P1
Reporter: Hongwei Ji Assignee: Unassigned
Resolution: Done Votes: 0
Labels: bugfest_q42018
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Attachments: PNG File Screen Shot 2019-01-09 at 4.59.32 PM.png     PNG File after-fix_Screen Shot 2019-01-09 at 5.06.47 PM.png    
Issue links:
Relates
relates to UIREQ-191 Requests: Searching fails Closed
relates to UIREQ-189 Requests: "request type" filter searc... Closed
relates to UIREQ-197 Requests: status filters not working Closed
relates to UIREQ-199 Requests: search is very slow for "Op... Closed
Sprint:

 Description   

It was reported that system was very slow to not usable. By looking at database usage, noticed that CPU was constantly 100%. Most CPU intensive queries look like below. Note it was truncated by AWS because it is way too long. Adding an index on item table `(lower(f_unaccent(jsonb ->> 'id'::text)))` seems to make the issue go way.

SELECT COUNT(*) FROM fs00001000_mod_inventory_storage.items_mt_view  WHERE (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((lower(f_unaccent(items_mt_view.jsonb->>'id')) LIKE lower(f_unaccent('62c28f91-bf80-43e5-a897-1a7e14ac08e6'))) OR (lower(f_unaccent(items_mt_view.jsonb->>'id')) LIKE lower(f_unaccent('255e1914-5820-4c2c-a089-3567fe55a12b')))) OR (lower(f_unaccent(items_mt_view.jsonb->>'id')) LIKE lower(f_unaccent('94e65f87-c05f-4b07-9dba-76279e662307') ...


 Comments   
Comment by Hongwei Ji [ 14/Jan/19 ]

Just checked the latest q4 Vagrant, it looks like the index is there. So this should be all set.

Generated at Thu Feb 08 23:15:16 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.