Fact Tables are growing to 250G per Day making then unusable. What are our options to manage this

Not applicable

Current State

  • APIs Usage is very high
  • Fact Tabled in Postgres Analytics POD are growing to 250G per day
  • Impacting the System performance and rendering unusable for custom reporting

What is the Solution other than to scale the PG servers Vertically?

Can we have have Fact Tables created more than once a day ? Has anyone attempted this ?

Are there any Big D options ?

Solved Solved
0 5 459
1 ACCEPTED SOLUTION

a) it needs to run as a separate linux process on the PG master

SELECT
  schemaname, relname,
  last_vacuum, last_autovacuum,
  vacuum_count, autovacuum_count  -- not available on 9.0 and earlierFROM pg_stat_user_tables;
$ ps -axww | grep autovacuum
24352??  Ss      1:05.33 postgres: autovacuum launcher process  (postgres)

https://stackoverflow.com/questions/19826467/how-can-i-tell-if-postgresqls-autovacuum-is-running-on-...

b) good.

c) See here: make sure that the recurring jobs are setup and run daily

https://docs.apigee.com/private-cloud/latest/recurring-analytics-services-maintenance-tasks

d) either use linux iostat or iowait. Reference:

http://bencane.com/2012/08/06/troubleshooting-high-io-wait-in-linux/

e) see above first. We have ways to increase it, but it is not addressing the underlying capacity resource bottleneck in the DB.

If you are stuck, please get in touch with support and they will help you.

Kind regards

View solution in original post

5 REPLIES 5

Yes, scale up the PG array.

Or, move to the Apigee Edge SaaS.

Cant move to SaaS due to regulatory requirements ..

Any good suggestions on best way to scale up the PG Array

Hi Ajay,

which OPDK version do you have ?

Few checks:
a) make sure that autovacuum process is running in PG (older versions have it disabled)
b) "qpid-stat -q" queue size of the qpid server nodes at peak API throughput: is it close to zero or do you see build up ?

c) what is your current retention strategy for the fact tables ?

d) do you have enough raw Disk I/O throughput for the DB queries that perform sequential table scans (the custom reports).

e) do you know how to increase the TimeOut on the UI when querying these custom reports ?

Let me know your responses and will guide you through the next steps.

Regards,

Nicola


Nicola

Thanks for providing these questions .. we are using Apigee 4.17.05

a) make sure that autovacuum process is running in PG (older versions have it disabled)

>>> We are going to check this. Should we enable it if it is disabled ? What will be the impact of this ?


b) "qpid-stat -q" queue size of the qpid server nodes at peak API throughput: is it close to zero or do you see build up ?

>>>> It is close to 0

c) what is your current retention strategy for the fact tables ?

>>>> 7 days or less

d) do you have enough raw Disk I/O throughput for the DB queries that perform sequential table scans (the custom reports).

>>> How can we find out this information

e) do you know how to increase the TimeOut on the UI when querying these custom reports ?

>>>> No. Can you kindly point me to the required setting

a) it needs to run as a separate linux process on the PG master

SELECT
  schemaname, relname,
  last_vacuum, last_autovacuum,
  vacuum_count, autovacuum_count  -- not available on 9.0 and earlierFROM pg_stat_user_tables;
$ ps -axww | grep autovacuum
24352??  Ss      1:05.33 postgres: autovacuum launcher process  (postgres)

https://stackoverflow.com/questions/19826467/how-can-i-tell-if-postgresqls-autovacuum-is-running-on-...

b) good.

c) See here: make sure that the recurring jobs are setup and run daily

https://docs.apigee.com/private-cloud/latest/recurring-analytics-services-maintenance-tasks

d) either use linux iostat or iowait. Reference:

http://bencane.com/2012/08/06/troubleshooting-high-io-wait-in-linux/

e) see above first. We have ways to increase it, but it is not addressing the underlying capacity resource bottleneck in the DB.

If you are stuck, please get in touch with support and they will help you.

Kind regards