Latest News
Start your free PG Nitrous Trial Get started ›
Come play in the new interactive Swarm64 DA query sandbox start here ›
Unleash PostgreSQL Parallel Query Read more ›

Introducing the Swarm64 DA PostgreSQL Settings Advisor

Posted in News, PostgreSQL, Product

Swarm64 DA 5.5 is released, and with it, we completed the development of the settings advisor. The settings advisor is a tool in the database that informs you in an explicit way which settings you should modify in your database and operating system to get great performance out of Swarm64 DA and PostgreSQL. First introduced in version 5.2, it has been continuously improved during the last months covering all the important settings in a PostgreSQL deployment.

The reason behind the settings advisor is simple. Tuning your database is very difficult, and many people tend to use the default PostgreSQL configuration (or a slightly modified version of it), which is usually tuned for transactional workloads and clearly underperforms for analytics.

For example, the setting max_parallel_workers_per_gather defaults to 2 in native PostgreSQL. For most of the queries, this represents the level of parallelism that can be achieved, i.e., the query will never use more than two virtual cores, hence not fully utilizing the dozens of cores available in modern servers.

Swarm64 DA aggressively parallelizes the queries to offer large accelerations and make better use of the available hardware. Therefore, we recommend that max_parallel_workers_per_gather is set to a value that is the 75% of the virtual cores, so queries exploit all the resources of the processor thanks to Swarm64 DA and finish much faster.

The Swarm64 DA settings advisor makes its tuning recommendations every time that you execute an EXPLAIN command, or when Swarm64 DA is installed or updated. In these cases, you see a message like the following:

Suboptimal settings found. 
 Run 'SELECT * FROM swarm64da.show_advice();' for more information. 

Following the instructions and calling the swarm64da.show_advice() function, you get a specific list of settings that can be improved with their recommended values and an explanation:

dbwork_mem128MB 2048MBA value between 512MB and 1577MB (10% of the total memory).
dbmax_parallel_workers_per_gather210Set it to 10 or higher ccording to the number of physical cores in the system. This is the maximum degree of parallelism that a query can achieve.
dbdefault_statistics_target10025002500 or higher to have better table statistics and more optimal plans. Run ANALYZE after increasing this value to update the statistics immediately.
ostransparent_hugepages_enabledalwaysneverAvoid transparent huge pages for optimal performance.

In this example, we are advised to tune the value of three settings:

  • max_parallel_workers_per_gather must be increased to increase the potential parallelism level of a query.
  • work_mem should be increased, otherwise queries that process a large number of rows will execute slow disk-based algorithms instead of fast memory-based algorithms.
  • A larger default_statistics_target value improves table statistics so the planner better estimates the number of rows, resulting in better query plans.

You can also see that the settings advisor recommends disabling the transparent hugepages (an operating system setting not a PostgreSQL setting), which is a common recommendation for PostgreSQL.

The complete list of settings covered by the advisor is available via the swarm64da.show_checked_settings() function, and in the Swarm64 DA documentation.

The settings advisor is also able to emit query-specific recommendations, for example if work_mem is too low for a query, then the IO usage is detected, which means that the query might be using a disk-based algorithm. In such case you see the following advice when executing a query with EXPLAIN ANALYZE:

Excessive disk I/O detected during query execution. 
  Increase 'work_mem' to lower disk usage and speed up query execution. 

At the moment, this query-specific work_mem advice is only available if the Swarm64 DA performance analyzer is active, another new feature of Swarm64 DA 5.5 that we will cover in another blog post.

As you can see, the settings advisor helps you to reduce configuration errors during the installation of Swarm64 DA and have a better-tuned system that delivers lightning-fast queries. But this is not the end of the settings advisor, we have more ideas to continue improving it, such as more query-specific advice, adaptation to your workload, or a way to directly apply its suggestions.

In addition to the improved settings advisor and the performance analyzer, Swarm64 DA 5.5 also comes with more guidance to correctly setup your system, several improvements for smaller datasets so you get acceleration in more cases, and several bugfixes.

Get and try Swarm64 DA 5.5 yourself for free and check the documentation for extra details!