Performance tuning advises because exporting operationplan is very slowly

[sgfisme]

Hi, there,

I setup the frePPLe and PostgreSQL on two K8s pods, and tune the PostgreSQL based on PGTune suggestions. I tried to load data into the frePPLe and executed plan. The exporting data spent 183 mins and the logs as below:
`INFO Start step 401 'Export plan' at 09:31:11

INFO Start step export2 1 'Exporting resource plans' at 09:31:11

INFO Start step export1 1 'Exporting operationplans' at 09:31:11

INFO Finished 'Exporting resource plans' at 09:31:11

INFO Start step export2 2 'Exporting problems' at 09:31:11

INFO Finished 'Exporting problems' at 09:35:54

INFO Start step export2 3 'Exporting constraints' at 09:35:54

INFO Finished 'Exporting constraints' at 09:36:58

INFO Finished 'Exporting operationplans' at 12:21:40

INFO Start step export1 2 'Exporting operationplan materials' at 12:21:40

INFO Finished 'Exporting operationplan materials' at 12:22:12

INFO Start step export1 3 'Exporting operationplan resources' at 12:22:12

INFO Finished 'Exporting operationplan resources' at 12:22:12

INFO Start step export1 4 'Exporting demand pegging' at 12:22:12

INFO Finished 'Exporting demand pegging' at 12:34:33

INFO Start step export1 5 'Compute period of cover' at 12:34:33

INFO Finished 'Compute period of cover' at 12:34:43

INFO Finished 'Export plan' at 12:34:43 `

Exporting operationplan is very slowly that export 7505742 records. And I tried to analyze postgresql log for exporting operationplan.

• COPY tmp_operationplan FROM stdin WITH DELIMITER AS '' NULL AS '\N': 97 mins
• update operationplan from tmp_operationplan: 2 mins
• insert into operationplan from tmp_operationplan: 53 mins

Could anybody give some tuning advises about frePPLe and PostgreSQL?

[jdetaeye]

Here are some real life example for the operationplan export:

• example 1: 120k records in 30 seconds
• example 2: 310k records in 70 seconds

The number of records you're exporting is bigger than this. Some tweaking may be required to speed this up. I'm 100% sure there is low hanging fruit to drastically change the timing.

• COPY tmp_operationplan FROM stdin WITH DELIMITER AS '' NULL AS '\N': 97 mins => there is some exported information (such as full pegging information) that may not be needed in your use case. Experiment with deactivating the export of some fields.
• update operationplan from tmp_operationplan: 2 mins. => not much to be done here.
• insert into operationplan from tmp_operationplan: 53 mins => This is pure database work. It can be sped up by temporarily disabling some foreign key database constraints, adding some index, ... Use postgreql tools to understand the bottleneck here.

[sgfisme]

Thanks, @jdetaey.
I am interested in your suggestion about deactivating the export of some fields. Could I config on the frePPle UI? Or do I have to modify the source code and rebuild them?

Could it improve performance through modifying the PostgreSQL parameters?

The parameter on PostgreSQL as below:

max_connections = 200
shared_buffers = 24GB
effective_cache_size = 72GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 31457kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

[jdetaeye]

For customizing exported fields:
Try removing some fields from this dictionary that is exported into the "operationplan.plan" jsonb field. Or just export an empty dictionary here as a test.

frepple/freppledb/output/commands.py

Line 381 in 4f72d23

pln = {

For the postgresql tuning;
Since the step that takes 53 minutes involves a temporary table and you have plenty of memory on the database server, you can consider increasing the parameter temp_buffers. https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY

[sgfisme]

Thanks, @jdetaeye

Could you give us any hint on how did exported data to the temp table very slowly?

There are different things involved in this export: 1) planning engine preparing the data, 2) python passing data from the planning engine to postgresql and 3) postgresql processing the copy-from data stream. It appears that 3) isn't the bottleneck in your case. Earlier I already provided you hints on how you can reduce the work on 1).

So copy-from data on 3) that the 1) and 2) in-process?

And know why frePPLe choose to export data to temp table first, not export data to the actual table directly or export to physical table.

Only operationplans in the status "approved" or "confirmed" are going through the tmp_operationplan table. The "proposed" records (normally the majority of all operationplans) go directly into the operationplan table. The approved&confirmed operationplans need to update existing records in the database, while the proposed ones are inserts. Hence the difference.

Do you really have 7M approved operationplans? Maybe you are using an older frepple release <6.18.0 in which all exported records still pass through the temp table?

We deploy release 6.19.0 on our K8s environment.

[sgfisme]

Hi, @jdetaeye
Based on your suggestion, I tried to separate 3 steps to get their execution time separately.

The result is as below:

1. Get operationplans in 0.0 seconds
2. Generated operationplans as JSON in 0.0 seconds
3. Exported operationplans in 5964.50 seconds

And I tried to get the data size as below:

1. Original dataset: 88 bytes
2. JSON object: 55 bytes

It seems that the workload is still in the copy_from to generate data. Could you give me some suggestions on how to prepare data first and then export data?

[jdetaeye]

Your measurements aren't correct.

I wrote:

1. planning engine preparing the data, 2) python passing data from the planning engine to postgresql and 3) postgresql processing the copy-from data stream.

These are not sequential steps. It's more like data pipeline that streams data from planning engine memory to the database. The steps run concurrently. The speed of the data pipeline is the limited by the speed of the slowest component in the pipe. Which is likely to be the first one.

[sgfisme]

Thanks, @jdetaeye

These are not sequential steps. It's more like data pipeline that streams data from planning engine memory to the database. The steps run concurrently. The speed of the data pipeline is the limited by the speed of the slowest component in the pipe. Which is likely to be the first one.

As you mentioned above, leveraging multi-thread can speed up the exporting data?

[jdetaeye]

It could be recoded to run in multiple parallel data pipelines, but that's not a trivial code change.

I'd more be inclined to optimize&speed up the bottleneck step: either by trimming away some of the exported json data that you don't need as output anyway, either by moving some of the extraction logic from slow interpreted python code towards much faster compiled C++ code.