Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kiBUd-0003JS-AO for pgsql-admin@arkaria.postgresql.org; Thu, 26 Nov 2020 07:15:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kiBUc-0000UA-8Z for pgsql-admin@arkaria.postgresql.org; Thu, 26 Nov 2020 07:15:02 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kiBUb-0000Tk-OZ for pgsql-admin@lists.postgresql.org; Thu, 26 Nov 2020 07:15:02 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kiBUY-0004tf-Aw for pgsql-admin@lists.postgresql.org; Thu, 26 Nov 2020 07:15:00 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id C8FC73804B8 for ; Thu, 26 Nov 2020 08:14:55 +0100 (CET) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id QPWrRHapaiCk for ; Thu, 26 Nov 2020 08:14:55 +0100 (CET) Received: from [10.169.49.51] (dynamic-046-114-105-051.46.114.pool.telefonica.de [46.114.105.51]) by rs.plausibolo.de (Postfix) with ESMTPSA id 1751C3802E8 for ; Thu, 26 Nov 2020 08:14:54 +0100 (CET) Date: Thu, 26 Nov 2020 08:14:52 +0100 User-Agent: K-9 Mail for Android In-Reply-To: References: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----S2GA3I3UTLXXW5V8W2LSNWJNEGHSOT" Content-Transfer-Encoding: 7bit Subject: Re: PG Admin 4.28 is crashing while viewing 10M row data To: pgsql-admin@lists.postgresql.org From: Holger Jakobs Message-ID: <3DBC1D09-28AE-4B02-9052-8C31ED434647@jakobs.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------S2GA3I3UTLXXW5V8W2LSNWJNEGHSOT Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Why not check why the server didn't start? For sure that isn't pgadmin rela= ted=2E Have a look at your system log=2E It will show where your typo in the conf= ig file lies=2E File name and lime number can be found there=2E Don't search in Postgresql's log, bout the system log! Am 26=2E November 2020 06:30:57 MEZ schrieb Sachin Kumar : >Hi Team, > >After doing all these changes the DB services stopped and not started >when >I tried manually=2E I have to roll back all these changes and reinstall >PGAdmin which wastes a lot of time for the team to fix these issues=2E > >error statement :- could not connect to server: Connection refused >(0x0000274D/10061) Is the server running on host "localhost" >(127=2E0=2E0=2E1) >and accepting TCP/IP connections on port 5432? > >Kindly share tried and tested solution > > > >On Wed, Nov 25, 2020 at 7:22 AM dbatoCloud Solution > >wrote: > >> Hi Sachin - try the below steps and let me know >> >> Hi Sachin, >> Step 1:- Configure these below parameters before the bulk inserts >then >> restart the server=2E >> >> shared_buffers =3D 2048MB (default: 32MB) - 25% of >physical >> memory is 2GB ( You system size 8GB physical memory) >> effective_cache_size =3D 4096MB (default: 128MB) - I recommend >to >> go for higher value , so that index scan will be used instead of >sequential >> scan=2E >> checkpoint_segment =3D 32 (default: 3) - in 9=2E5 >> checkpoint_segments but in Version 12 is max_size_size >> max_wal_size =3D 32 (default 1GB) - increase the >> value for heavy load, but FYI it will increase the recovery if >incase >> system crashed=2E >> checkpoint_completion_target =3D 0=2E9 (default: 0=2E5) - the targ= et >of >> checkpoint completion time increase to 0=2E9 >> default_statistics_target =3D 1000 (default: 100) - fraction of >total >> time between checkpoints=2E >> work_mem =3D 200MB (default: 1MB) >> maintainance_work_mem =3D 1GB (default: 16MB) - this is for >> vacuum, creating index=2E since your table old table with index then i >would >> recomment this parameter to be higher=2E >> >> step 2:- >> >> select name, setting from pg_settings where name =3D 'autovacuum' ; >> >> if the output says "on" then no need to vacuum your table else do the >> below >> >> VACUUM FULL hk_card_master_test ; >> >> >> >> >> >> Thanks & Best Wishes, >> >> Ashok >> >> >> >> >> >-------------------------------------------------------------------------= -------------------------------------------- >> >> Ashokkumar Mani *(OCP12c/11g/10g/9i, AWS SAA, M103)* >> >> *Dubai** , UAE* *| BLR , INDIA* >> >> M: *+971 54 723 0075 *| *+91 90086 70302 | *WApp :* +91 81975 99922* >> >> W: https://dbatocloudsolution=2Eblogspot=2Ein/ >> | E: dbatocloud17@gmail=2E= com >> >> >> On Tue, Nov 24, 2020 at 4:08 PM Sachin Kumar > >> wrote: >> >>> Hi Ashok, >>> >>> it's an old table where an index is already created=2E >>> >>> postgresql database version 12 and 13 1 @ each server=2E >>> >>> all settings are on the default configuration=2E >>> >>> server configuration=2E >>> >>> Operating system :- Windows (Windows Server 2016 Datacenter) >>> Size (2 vcpus, 8 GiB memory) >>> >>> On Mon, Nov 23, 2020 at 8:30 PM dbatoCloud Solution < >>> dbatocloud17@gmail=2Ecom> wrote: >>> >>>> Hi Sachin, >>>> sorry for, delayed response, >>>> >>>> Why don't you run batch by batch? >>>> >>>> could you please confirm the below parameters values:- Since your >doing >>>> bulk load I need the below details to recommend then I would >suggest for a >>>> vacuum? >>>> hk_card_master_test > has it created newly or old >table >>>> with index was already existed? please confirm? >>>> postgresql database version ? >>>> shared_buffers =3D ? (default: 32MB) >>>> effective_cache_size =3D ? (default: 128MB) >>>> checkpoint_segment =3D ? (default: 3) >>>> checkpoint_completion_target =3D ? (default: 0=2E5) >>>> default_statistics_target =3D ? (default: 100) >>>> work_mem =3D ? (default: 1MB) >>>> maintainance_work_mem =3D ? (default: 16MB) >>>> >>>> >>>> >>>> >>>> Thanks & Best Wishes, >>>> >>>> Ashok >>>> >>>> >>>> >>>> >>>> >-------------------------------------------------------------------------= -------------------------------------------- >>>> >>>> Ashokkumar Mani *(OCP12c/11g/10g/9i, AWS SAA, M103)* >>>> >>>> *Dubai** , UAE* *| BLR , INDIA* >>>> >>>> M: *+971 54 723 0075 *| *+91 90086 70302 | *WApp :* +91 81975 >99922* >>>> >>>> W: https://dbatocloudsolution=2Eblogspot=2Ein/ >>>> | E: >dbatocloud17@gmail=2Ecom >>>> >>>> >>>> On Mon, Nov 23, 2020 at 10:36 AM Sachin Kumar > >>>> wrote: >>>> >>>>> HI Ashok, >>>>> >>>>> Please find the command i am using to upload 10 million data into >a >>>>> table=2E >>>>> >>>>> *copy command to update records* >>>>> copy hk_card_master_test from >>>>> 'C:/inetpub/wwwroot/cards/media/static/rawsheet_001-000=2Ecsv' with >delimiter >>>>> E'\t' csv header encoding 'ISO_8859_5' >>>>> >>>>> I am not using the Vacuum command during the update? NO, I am not >>>>> Vacuuming this large table=2E >>>>> I am using the analyze command for other queries=2E >>>>> >>>>> I am not aware of the Vacuum command functionality=2E Please if you >can >>>>> help in understanding Vacuum query=2E >>>>> >>>>> >>>>> On Mon, Nov 23, 2020 at 10:23 AM dbatoCloud Solution < >>>>> dbatocloud17@gmail=2Ecom> wrote: >>>>> >>>>>> Hi Sachin, >>>>>> >>>>>> Let me know-how did you give a commit statement during update? >Did you >>>>>> try use Vacuum and analyze command during update? are you >>>>>> frequently Vacuuming this table large table =2E >>>>>> >>>>>> if the postgresql is running on Linux then run it by batch with >>>>>> intermediate commit=2E >>>>>> >>>>>> >>>>>> >>>>>> Thanks & Best Wishes, >>>>>> >>>>>> Ashok >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> Thanks & Best Wishes, >>>>>> >>>>>> Ashok >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >-------------------------------------------------------------------------= -------------------------------------------- >>>>>> >>>>>> Ashokkumar Mani *(OCP12c/11g/10g/9i, AWS SAA, M103)* >>>>>> >>>>>> *Dubai** , UAE* *| BLR , INDIA* >>>>>> >>>>>> M: *+971 54 723 0075 *| *+91 90086 70302 | *WApp :* +91 81975 >99922* >>>>>> >>>>>> W: https://dbatocloudsolution=2Eblogspot=2Ein/ >>>>>> | E: >dbatocloud17@gmail=2Ecom >>>>>> >>>>>> >>>>>> On Sun, Nov 22, 2020 at 10:25 PM David G=2E Johnston < >>>>>> david=2Eg=2Ejohnston@gmail=2Ecom> wrote: >>>>>> >>>>>>> >>>>>>> On Sunday, November 22, 2020, Sachin Kumar > >>>>>>> wrote: >>>>>>> >>>>>>>> Hi Expert, >>>>>>>> I am updating 10 Million records using COPY command in a table, >the >>>>>>>> table is updated in a minute, but when I am trying to view all >row=2E PGadmin >>>>>>>> 4=2E28 which I am using in chrome is getting crashed=2E I tried i= t >4 times and >>>>>>>> it crashed 3 times=2E After viewing this 10M row the other >queries are very >>>>>>>> running slow=2E >>>>>>>> is there any process to clear the buffer in PGAdmin or process >>>>>>>> through which the quey in PGAdmin runs at its normal speed? >>>>>>>> Please guide me on how to fix this issue=2E >>>>>>>> >>>>>>>> >>>>>>> Stop and restart the program? >>>>>>> >>>>>>> What do you hope to do with 10M full records displayed on screen >>>>>>> (even if pagination is involved=2E=2E=2E)? >>>>>>> >>>>>>> David J=2E >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> -- >>>>> >>>>> Best Regards, >>>>> Sachin Kumar >>>>> >>>> >>> >>> -- >>> >>> Best Regards, >>> Sachin Kumar >>> >> > >--=20 > >Best Regards, >Sachin Kumar --=20 Holger Jakobs, Bergisch Gladbach +49 178 9759012 - sent from mobile, therefore short - ------S2GA3I3UTLXXW5V8W2LSNWJNEGHSOT Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Why not check why the server didn't start? For sur= e that isn't pgadmin related=2E

Have a look at your system log=2E It= will show where your typo in the config file lies=2E File name and lime nu= mber can be found there=2E

Don't search in Postgresql's log, bout th= e system log!


Am 26=2E November 2020 = 06:30:57 MEZ schrieb Sachin Kumar <sachinkumaras@gmail=2Ecom>:
Hi Team,

After doing all these changes= the DB services stopped and not started when I tried manually=2E I have to= roll back all these changes and reinstall PGAdmin which wastes a lot = of time for the team to fix these issues=2E

error = statement :- could not connect to server: Connection refused (0x0= 000274D/10061) Is the server running on host "localhost" (127=2E0=2E0=2E1) = and accepting TCP/IP connections on port 5432?

Kin= dly share tried and tested solution



On We= d, Nov 25, 2020 at 7:22 AM dbatoCloud Solution <dbatocloud17@gmail=2Ecom> wrote:
Hi Sachin - try the below steps and let me know

Hi Sachin,
Step 1:-  Configure these belo= w parameters before the bulk inserts then restart the server=2E
shared_buffers =3D 2048MB (default: 32MB)         = ;      -  25% of physical memory is 2GB ( You system si= ze 8GB physical memory)
effective_cache_size =3D 4096MB (default: 128MB)=         -   I recommend to go for higher value , s= o that index scan will be used instead of sequential scan=2E
checkpoint_= segment =3D 32 (default: 3)             =     -     in 9=2E5 checkpoint_segments but in Version = 12 is max_size_size
max_wal_size       =3D 32 (default 1G= B)                 -  inc= rease the value for heavy  load, but FYI it will increase the recovery= if incase system crashed=2E
checkpoint_completion_target =3D 0=2E9 (de= fault: 0=2E5)      - the target of checkpoint completio= n time increase to 0=2E9
default_statistics_target =3D 1000 (default: 1= 00)       - fraction of total time between checkpoints=2E work_mem =3D 200MB (default: 1MB)
maintainance_work_mem =3D 1GB (defau= lt: 16MB)           -  this is for vacuum, cr= eating index=2E since your table old table with index then i would recommen= t this parameter to be higher=2E

step 2:-

select name, settin= g from pg_settings where name =3D 'autovacuum' ;

if the output says = "on" then no need to vacuum your table else do the below

VACUUM FUL= L hk_card_master_test  ;

 

 

Thanks & Be= st Wishes,

Ashok

 

---------------------------------------------------= ------------------------------------------------------------------

Ashokkumar Mani  (OCP12c/11g/10g/9i, AWS SAA, M103)

Dubai , UAE | BLR , INDIA

M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922

W: https://dbatocloudsolution=2Eblogspot=2Ein/ | E: dbatocloud17@gmail= =2Ecom



On Tue, Nov 24, 2020 at 4:08 PM Sachin Kumar <sachinkumaras@g= mail=2Ecom> wrote:
Hi Ashok,

it's an old tabl= e where an index is already created=2E

postgresql database version 12 and 13 1 @ each = server=2E

all settings are on the default configur= ation=2E

server configuration=2E

Operating system :- Win= dows (Windows Server 2016 Datacenter)
Size (2 vcpus, 8 GiB memory)<= br>

On Mon, Nov 23, 2020 at 8:30 PM dbatoCloud Solution <dbatocloud17@gmail=2Ecom= > wrote:
Hi S= achin, 
sorry for, delayed response,
<= font face=3D"Arial">
Why don't you run batch by batch? 

could you please confirm the below par= ameters values:- Since your doing bulk load I need the below details to rec= ommend then I would suggest for a vacuum? 
hk_card_master_test      =         > has it created newly or old table with ind= ex was already existed?  please confirm? 
postgresq= l database version                &= nbsp;     ?  
shared_buffers =3D               =           ? (default: 32MB)
effective_cache_si= ze =3D ? (default: 128MB)
checkpoint_segment =3D ? (default: 3)
check= point_completion_target =3D ? (default: 0=2E5)
default_statistics_target= =3D ? (default: 100)
work_mem =3D ? (default: 1MB)
maintainance_work= _mem =3D ? (default: 16MB)


 

Thanks & Be= st Wishes,

Ashok

 

---------------------------------------------------= ------------------------------------------------------------------

Ashokkumar Mani  (OCP12c/11g/10g/9i, AWS SAA, M103)

Dubai , UAE | BLR , INDIA

M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922

W: https://dbatocloudsolution=2Eblogspot=2Ein/ | E: dbatocloud17@gmail= =2Ecom



On Mon, Nov 23, 2020 at 10:36 AM Sachin Kumar <sachinkumaras@= gmail=2Ecom> wrote:
HI Ashok,

Please find = ;the command i am using to upload 10 million data into a table=2E

copy command to update records
copy hk_car= d_master_test from 'C:/inetpub/wwwroot/cards/media/static/rawsheet_001-000= =2Ecsv' with delimiter E'\t' csv header encoding 'ISO_8859_5'

<= /div>
I am not using the Vacuum command during the update? NO, I  am not Vacuuming this l= arge table=2E
I am using the analyze command for other queries=2E
I am not aware of the Vacuum command functionality=2E Please if you c= an help in understanding Vacuum query=2E 

<= /div>

On Mon, Nov 23, 2020 at 10:23 AM dbatoCloud Solution <dbatocloud17@gmail=2Ecom> wrote:
<= div dir=3D"ltr">
Hi Sachin,

Let me know-how did you give a = commit statement during update? Did you try use Vacuum and analyze command = during update? are you frequently Vacuuming this table large tabl= e =2E

if the postgresql is running on Linux then r= un it by batch with intermediate commit=2E 

 

Thanks & Be= st Wishes,

Ashok

 



On Sun, Nov 22, 2020 at 10:25 PM David G=2E Johnston= <= david=2Eg=2Ejohnston@gmail=2Ecom> wrote:

On Sunday, November 22, 2020, Sachin= Kumar <s= achinkumaras@gmail=2Ecom> wrote:
Hi Expert,
I am updating 10 Mill= ion records using COPY command in a table, the table is updated in a minute= , but when I am trying to view all row=2E PGadmin 4=2E28 which I am using i= n chrome is getting crashed=2E I tried it 4 times and it crashed 3 times=2E= After viewing this 10M row the other queries are very running slow=2E
= is there any process to clear the buffer in PGAdmin or process through w= hich the quey in PGAdmin runs at its normal speed?
Please guide me on= how to fix this issue=2E


Stop and restart the program?

What do you hope to do with 10M full records displayed on screen (even if= pagination is involved=2E=2E=2E)?

David J=2E
 


--

Best Regards,
Sachin Kumar


--

Best Regards,
Sachin Kumar


--

Best Regards,
Sachin Kumar

--
Holger Jakobs, Bergisch Gladbach
+49 178 = 9759012
- sent from mobile, therefore short -
------S2GA3I3UTLXXW5V8W2LSNWJNEGHSOT--