Обсуждение: Need support on tuning at the time of index creation

Dear Sir/Madam,

One of our ericsson product used backend db as postgresql 9. We are facing following performance issues where we need some support from your side.
We are having 10 tables and we are inserting around 150 million to 250 million records on each of those tables. After that we need to create 29 indexes includung primary key index and other types of indexes. Currently it is taking almost 3.5 to 4 hrs.

Please let us know how we can tune the perfirmance so that we can complete this task in 30-40 mins.

Note: We cannot change the db version.

Thanks & Regards,
Sandip Pradhan

On 1/27/20 5:10 AM, Sandip Pradhan wrote:
> Dear Sir/Madam,
>
> One of our ericsson product used backend db as postgresql 9. We are facing 
> following performance issues where we need some support from your side.
> We are having 10 tables and we are inserting around 150 million to 250 
> million records on each of those tables. After that we need to create 29 
> indexes includung primary key index and other types of indexes. Currently 
> it is taking almost 3.5 to 4 hrs.
>
> Please let us know how we can tune the perfirmance so that we can complete 
> this task in 30-40 mins.
>
> Note: We cannot change the db version.

There are seven versions of "postgresql 9", and multiple patch releases of 
each.  Please specify which version you're running.

-- 
Angular momentum makes the world go 'round.

On 2020-01-27 11:10:36 +0000, Sandip Pradhan wrote:
> One of our ericsson product used backend db as postgresql 9. We are facing
> following performance issues where we need some support from your side.
> We are having 10 tables and we are inserting around 150 million to 250 million
> records on each of those tables. After that we need to create 29 indexes
> includung primary key index and other types of indexes. Currently it is taking
> almost 3.5 to 4 hrs.

It is hard to give advice on what you could do differently if you don't
tell us what you do.

How are you inserting those rows?
How are you creating the indexes?

Especially: Are you doing things serially or in parallel?

Also performance depends a lot on hardware, so faster CPUs (or more
cores) and faster disks/ssds might help, too.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Hi Ron,

Thanks for your time.

We are using the version 9.5.9.14.

Regards,


 
Sandip Pradhan 
Tech Lead
 
BDGS SD IN BSS EOC_ECM 1
Mobile: 9830880856
sandip.pradhan@ericsson.com
 
Ericsson
DLF IT Park - II, DLF SEZ Block 1A, Rajarhat
700156, Kolkata, West Bengal
India
ericsson.com

-----Original Message-----
From: Ron <ronljohnsonjr@gmail.com> 
Sent: Monday, January 27, 2020 4:57 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: Need support on tuning at the time of index creation

On 1/27/20 5:10 AM, Sandip Pradhan wrote:
> Dear Sir/Madam,
>
> One of our ericsson product used backend db as postgresql 9. We are 
> facing following performance issues where we need some support from your side.
> We are having 10 tables and we are inserting around 150 million to 250 
> million records on each of those tables. After that we need to create 
> 29 indexes includung primary key index and other types of indexes. 
> Currently it is taking almost 3.5 to 4 hrs.
>
> Please let us know how we can tune the perfirmance so that we can 
> complete this task in 30-40 mins.
>
> Note: We cannot change the db version.

There are seven versions of "postgresql 9", and multiple patch releases of each.  Please specify which version you're
running.

--
Angular momentum makes the world go 'round.

Hi Peter,

Thanks for your time.

Out Postgresql version: 9.5.9.14.

We are using COPY command to insert rows into the tables. While running COPY command, all indexes dropped.

After successfully inserting all the rows, we are trying to create all those indexes.

Example of index creation script:
CREATE INDEX cwt_sr_assocact_entityref
  ON cwt_sr_associationact
  USING btree
  (entityapplicationcontext COLLATE pg_catalog."default", entitytype COLLATE pg_catalog."default", entitydn COLLATE
pg_catalog."default");

CREATE INDEX ix_sr_assoc_customerrelateddn
  ON cwt_sr_associationact
  USING btree
  (customerrelateddn COLLATE pg_catalog."default");

Running indexes in parallel.

Please find the hardware detail of the server:
OS: Red Hat Enterprise Linux 7.4
RAM: 125 GB
CPU Core: 36

Set maintenance_work_mem to 25GB

Please let me know if you need any further information.

Regards,


 
Sandip Pradhan
Tech Lead
 
BDGS SD IN BSS EOC_ECM 1
Mobile: 9830880856
sandip.pradhan@ericsson.com
 
Ericsson
DLF IT Park - II, DLF SEZ Block 1A, Rajarhat
700156, Kolkata, West Bengal
India
ericsson.com

-----Original Message-----
From: Peter J. Holzer <hjp-pgsql@hjp.at>
Sent: Tuesday, January 28, 2020 4:16 AM
To: pgsql-general@lists.postgresql.org
Subject: Re: Need support on tuning at the time of index creation

On 2020-01-27 11:10:36 +0000, Sandip Pradhan wrote:
> One of our ericsson product used backend db as postgresql 9. We are
> facing following performance issues where we need some support from your side.
> We are having 10 tables and we are inserting around 150 million to 250
> million records on each of those tables. After that we need to create
> 29 indexes includung primary key index and other types of indexes.
> Currently it is taking almost 3.5 to 4 hrs.

It is hard to give advice on what you could do differently if you don't tell us what you do.

How are you inserting those rows?
How are you creating the indexes?

Especially: Are you doing things serially or in parallel?

Also performance depends a lot on hardware, so faster CPUs (or more
cores) and faster disks/ssds might help, too.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   |
https://protect2.fireeye.com/v1/url?k=228fefe0-7e0635f0-228faf7b-0cc47ad93e2a-0af0cf5ca8f30246&q=1&e=467afc4c-b87f-4d98-9a57-bf0596fd612a&u=http%3A%2F%2Fwww.hjp.at%2F
|      challenge!" 

On 2020-01-31 10:13:20 +0000, Sandip Pradhan wrote:
> Thanks for your time.
>
> Out Postgresql version: 9.5.9.14.
>
> We are using COPY command to insert rows into the tables. While running COPY command, all indexes dropped.
>

So I take it that the copy is fast, but the building of indexes is slow
(actually I'm not sure whether less than 50 seconds per index is slow
for tables of this size, but it's too slow for your application)

> After successfully inserting all the rows, we are trying to create all those indexes.
>
> Example of index creation script:
> CREATE INDEX cwt_sr_assocact_entityref
>   ON cwt_sr_associationact
>   USING btree
>   (entityapplicationcontext COLLATE pg_catalog."default", entitytype COLLATE pg_catalog."default", entitydn COLLATE
pg_catalog."default");
>
> CREATE INDEX ix_sr_assoc_customerrelateddn
>   ON cwt_sr_associationact
>   USING btree
>   (customerrelateddn COLLATE pg_catalog."default");
>
> Running indexes in parallel.
>
> Please find the hardware detail of the server:
> OS: Red Hat Enterprise Linux 7.4
> RAM: 125 GB
> CPU Core: 36
>
> Set maintenance_work_mem to 25GB

Ideally, you would want to use all the cores and each process should use
as much RAM as possible. However, if each process uses 25 GB, you can
only fit 4 of them (not 5 because other stuff needs memory, too), in RAM
at the same time, leaving 32 of your 36 cores idle. OTOH, for 36
processes, each would have to be restricted to about 3 GB, which doesn't
sound ideal, either. So there's probably a sweet spot somewhere in the
middle.

What I would do:

To save time, I would test only with a single table (there are 29
indexes on each table and that's almost certainly more than the optimal
degree of parallelism, so we won't get anything from processing several
tables at once).

To establish a baseline, I'd look for the optimal parallelism with your
current parameters: Build the indexes with 1, 2, 4, 8, 16, 29 processes in
parallel. Stop if it gets noticably slower, especially if it starts to
swap heavily. Then bisect between the two best ones. Note the results.

Then reduce maintenance_work_mem to 50 % and repeat the experiment.
Does it get better?

If it gets better (or at least not much worse), reduce
maintenance_work_mem again and repeat.

Eventually you should find the optimal combination.

To reach your goal of 40 minutes for all 10 tables, your optimal run for
a single table must be about 4 minutes. If you don't get close to that
you will probably have to look for different solutions.

As already mentioned, the speed of I/O makes a lot of difference. If you
don't use SSDs yet, you should. If you do use SSDs, maybe you can get
faster ones? You might also investigate putting pgsql_tmp on a RAM disk.

You could also try changing synchronous_commit and/or
fsync/full_page_writes (the latter only if you can tolerate losing your
DB in a crash).

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"