Version 2021

Tuning

The following section describes tuning PostgreSQL for analytical workload based on internal tests at MicroStrategy.

Parameter Operation

Initial Value

Changed To

Performance Gain

% Execution Time Of Original Value

shared_buffer

128 MB

1280 MB

Metadata garbage collection time reduces from 5724 seconds to 47 seconds.

0.82%

work_mem

4 MB

50 MB

Metadata garbage collection time reduces from 47 seconds to 17 seconds.

36.2%

maintenance_work_mem

 

64MB

1536MB

Four times performance gain on the index creation on platform_analytics_wh.access_transactions table.

25%

max_parallel_maintenance_workers

2

8

random_page_cost

4

1

Execution time of query “select tran_id from access_transactions as a join temp_lu_object as b on a.object_id=b.object_id limit 1000;” in Platform Analytics is reduced from 214185 ms to 2.3 ms.

<0.01%

enable_nestloop

on

off

Table scan time reduced from 585 seconds to 8 seconds.

1.36%

vacuum

NA

NA

Table size reduced from 110 GB to 1.5 MB after vacuuming the table platform_analytics_wh.access_transactions

<0.01%

See KB485086: Best practices for performance tuning based on PostgreSQL for details.

ODBC Versions 2 and 3

Starting in MicroStrategy 2021 Update 1, MicroStrategy uses ODBC call 3.x for PostgreSQL by default.
For a single cube publish, using 3.x ODBC calls produced an approximate 30% performance improvement over using 2.0 ODBC calls.

The test shown below is based on a four million row dataset which shows the throughput improvement with ODBC call 3.5 compared to ODBC call 2.0.

throughput1.png

You can change the ODBC version at the MicroStrategy database instance level by right-clicking the database instance and selecting the Use 3.x ODBC calls option.

calls.png

Data Engine Version Upgrade Since MicroStrategy 2021

During MicroStrategy performance testing with a data engine version of 2021 using MicroStrategy 2021, there was an overall 35% performance gain compared to data engine version 10.4.

Starting in MicroStrategy 2021, the default data engine version is 2021. See KB485086: Data Engine changes in the MicroStrategy 2021 release for more information.

For example, with a data engine version of 2020 and above, MicroStrategy pushes filters down to the database for execution to minimize the intermediate result set size. This pushdown can significantly improve the performance of dossier execution. For more details, see KB483573: Metric Qualification Filter Not Pushed Down in ConnectLive Leads to Performance Degradation.

The following chart shows the throughput change with different data engine versions. The throughput of data engine version 2021 is higher than data engine version 10.4. The test was executed with 50 concurrent connections.