[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: |
|
||||||||||||||||||||
| Issue links: |
|
||||||||||||||||||||
| 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. |