Question
BPM Company
NL
Last activity: 29 Oct 2019 11:55 EDT
How can increase in toast bytes of DB table pr_operators be explained?
Using PDC, I made a custom report to see why the pr_operators table on our production environment is so big. It contains around 60K records but takes a Total Bytes of around 21GB. I found that last month the Estimate Row Count and Live Rows did not increase that much, but the Toast Bytes increased a lot in 1 day.
Possible reason/related issue for this is that we have a lot of REST calls internally and that the service operator is saved almost constantly.
I've ready about TOAST https://www.postgresql.org/docs/9.5/storage-toast.html and https://wiki.postgresql.org/wiki/TOAST and the general idea about this, but I could use some more context here.
My questions:
- The Total Bytes (in PDC on class PegaAES-Data-DBTableUsage) is the sum of Table Bytes, Toast Bytes and Index Bytes. What are exactly Toast Bytes? How can you see more info on the space used for it?
- What could be reasons for the toast bytes to increase so much in a day, while the number of records is gradually increasing. Can this be explained by a lot of resaves of service operators?
- How can you decrease the Toast Bytes? It went from 13GB to 20GB and stayed like that;
- Is the Total Bytes representative for the RDS Disk Space that is used? When can it differ?
***Edited by Moderator Marissa to update platform capability tags****
***Moderator Edit-Vidyaranjan: Updated SR details***
Using PDC, I made a custom report to see why the pr_operators table on our production environment is so big. It contains around 60K records but takes a Total Bytes of around 21GB. I found that last month the Estimate Row Count and Live Rows did not increase that much, but the Toast Bytes increased a lot in 1 day.
Possible reason/related issue for this is that we have a lot of REST calls internally and that the service operator is saved almost constantly.
I've ready about TOAST https://www.postgresql.org/docs/9.5/storage-toast.html and https://wiki.postgresql.org/wiki/TOAST and the general idea about this, but I could use some more context here.
My questions:
- The Total Bytes (in PDC on class PegaAES-Data-DBTableUsage) is the sum of Table Bytes, Toast Bytes and Index Bytes. What are exactly Toast Bytes? How can you see more info on the space used for it?
- What could be reasons for the toast bytes to increase so much in a day, while the number of records is gradually increasing. Can this be explained by a lot of resaves of service operators?
- How can you decrease the Toast Bytes? It went from 13GB to 20GB and stayed like that;
- Is the Total Bytes representative for the RDS Disk Space that is used? When can it differ?
***Edited by Moderator Marissa to update platform capability tags****
***Moderator Edit-Vidyaranjan: Updated SR details***
PEG
GB
Have you performed a VACUUM on this table recently?
https://www.postgresql.org/docs/9.1/sql-vacuum.html
Pegasystems Inc.
US
As Laurence has already posted it seems like this table has not be vacuumed recently. Every time an operator logs in the pr_operators table record for that operator will get updated with the last signed on time. If you have operators being used for things like soap or rest services you should look to shut off the update of pr_operators which would happen each time the service is invoked.
BPM Company
NL
Is it possible to run the VACUUM statement on PegaCloud without support of GCS? I could create an RDB rule for this. Or should it be requested as a change request?
The vacuum details of the table: Last Analysis = okt 25 at 16:42 / Last Vacuum = okt 25 at 16:42 / Last Automatic Analysis = okt 28 at 01:11 / Last Autovacuum = okt 28 at 01:12.
We have an SR open for the issue with service operators saving all the time but we didn't receive a working solution (SR-D53165). Today we found a solution on PDN setting the extAssignFlag property avoiding the resave.
PEG
GB
I can't answer this. But I'm not sure if you would have permissions to run this directly. So I think you may need to raise Cloud ticket either to do this or verify whether you can.
Also going forward it looks like you've identified a means to avoid the problem.