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
RE: Need support on tuning at the time of index creation
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.prad...@ericsson.com Ericsson DLF IT Park - II, DLF SEZ Block 1A, Rajarhat 700156, Kolkata, West Bengal India ericsson.com -Original Message- From: Ron 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.
RE: Need support on tuning at the time of index creation
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.prad...@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 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 +, 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. |_|_) || | | | h...@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!"
RE: Need support on tuning at the time of index creation
Currently maintenance_work_mem set to 25 GB. Regards, [cid:image002.png@01D5D84D.47F72BC0]<http://www.ericsson.com/> Sandip Pradhan Tech Lead BDGS SD IN BSS EOC_ECM 1 Mobile: 9830880856 sandip.prad...@ericsson.com<mailto:sandip.prad...@ericsson.com> Ericsson DLF IT Park - II, DLF SEZ Block 1A, Rajarhat 700156, Kolkata, West Bengal India ericsson.com<http://www.ericsson.com/> From: Jayadevan M Sent: Friday, January 31, 2020 3:30 PM To: Sandip Pradhan Cc: Ron ; pgsql-general@lists.postgresql.org Subject: Re: Need support on tuning at the time of index creation On Fri, Jan 31, 2020 at 3:22 PM Sandip Pradhan mailto:sandip.prad...@ericsson.com>> wrote: Hi Ron, Thanks for your time. We are using the version 9.5.9.14. May be you could try tweaking maintenance_work_mem? Regards, Jayadevan
