Обсуждение: maintenance_work_mem impact?

Поиск
Список
Период
Сортировка

maintenance_work_mem impact?

От
Adithya Kumaranchath
Дата:
Hi all,

I have a table approx. 20GB.

I have a create unique index statement.

CREATE UNIQUE INDEX testindex_v1 ON testtable1 (test_index); 

My observations:
maintenance_work_mem = 2G
max_parallel_workers = '16'

The create index completes in 20 minutes.

When I change this:
maintenance_work_mem = 16G
max_parallel_workers = '16'

It completes in 9 minutes. So I can see that I can gain performance by changing this number.

So it is faster but the question I have is it safe to set it to such a high number? I am aware that only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, so it's safe to set this value significantly larger. I have 128GB memory.

  1. Any advice or thoughts?
  2. Is there any other parameter that can accelerate index creation? 

Thanks,
Ad

Re: maintenance_work_mem impact?

От
Laurenz Albe
Дата:
On Tue, 2024-03-19 at 16:05 +0000, Adithya Kumaranchath wrote:
> I have a table approx. 20GB.
>
> CREATE UNIQUE INDEX testindex_v1 ON testtable1 (test_index); 
>
> My observations:
> maintenance_work_mem = 2G
> max_parallel_workers = '16'
>
> The create index completes in 20 minutes.
>
> When I change this:
> maintenance_work_mem = 16G
> max_parallel_workers = '16'
>
> It completes in 9 minutes. So I can see that I can gain performance by changing this number.
>
> So it is faster but the question I have is it safe to set it to such a high number?
> I am aware that onlyone of these operations can be executed at a time by a database
> session, and an installation normally doesn't have many of them running concurrently,
> so it's safe to set this value significantly larger. I have 128GB memory.
>    1. Any advice or thoughts?
>    2. Is there any other parameter that can accelerate index creation? 

It is safe as long as you have enough free memory on the machine.

You can verify with tools like "free" on Linux (look for "available" memory).

Yours,
Laurenz Albe