Re: PG Admin 4.28 is crashing while viewing 10M row data

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.

shared_buffers = 2048MB (default: 32MB) - 25% of physical
memory is 2GB ( You system size 8GB physical memory)
effective_cache_size = 4096MB (default: 128MB) - I recommend to go
for higher value , so that index scan will be used instead of sequential
scan.
checkpoint_segment = 32 (default: 3) - in 9.5
checkpoint_segments but in Version 12 is max_size_size
max_wal_size = 32 (default 1GB) - increase the value
for heavy load, but FYI it will increase the recovery if incase system
crashed.
checkpoint_completion_target = 0.9 (default: 0.5) - the target of
checkpoint completion time increase to 0.9
default_statistics_target = 1000 (default: 100) - fraction of total
time between checkpoints.
work_mem = 200MB (default: 1MB)
maintainance_work_mem = 1GB (default: 16MB) - this is for
vacuum, creating index. since your table old table with index then i would
recomment this parameter to be higher.

step 2:-

select name, setting from pg_settings where name = '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.blogspot.in/
<http://dbatocloudsolution.blogspot.in/> | E: dbatocloud17(at)gmail(dot)com

On Tue, Nov 24, 2020 at 4:08 PM Sachin Kumar <sachinkumaras(at)gmail(dot)com>
wrote:

> Hi Ashok,
>
> it's an old table where an index is already created.
>
> postgresql database version 12 and 13 1 @ each server.
>
> all settings are on the default configuration.
>
> server configuration.
>
> 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(at)gmail(dot)com> 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 = ? (default: 32MB)
>> effective_cache_size = ? (default: 128MB)
>> checkpoint_segment = ? (default: 3)
>> checkpoint_completion_target = ? (default: 0.5)
>> default_statistics_target = ? (default: 100)
>> work_mem = ? (default: 1MB)
>> maintainance_work_mem = ? (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.blogspot.in/
>> <http://dbatocloudsolution.blogspot.in/> | E: dbatocloud17(at)gmail(dot)com
>>
>>
>> On Mon, Nov 23, 2020 at 10:36 AM Sachin Kumar <sachinkumaras(at)gmail(dot)com>
>> wrote:
>>
>>> HI Ashok,
>>>
>>> Please find the command i am using to upload 10 million data into a
>>> table.
>>>
>>> *copy command to update records*
>>> copy hk_card_master_test from
>>> 'C:/inetpub/wwwroot/cards/media/static/rawsheet_001-000.csv' 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.
>>> I am using the analyze command for other queries.
>>>
>>> I am not aware of the Vacuum command functionality. Please if you can
>>> help in understanding Vacuum query.
>>>
>>>
>>> On Mon, Nov 23, 2020 at 10:23 AM dbatoCloud Solution <
>>> dbatocloud17(at)gmail(dot)com> 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 .
>>>>
>>>> if the postgresql is running on Linux then run it by batch with
>>>> intermediate commit.
>>>>
>>>>
>>>>
>>>> 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.blogspot.in/
>>>> <http://dbatocloudsolution.blogspot.in/> | E: dbatocloud17(at)gmail(dot)com
>>>>
>>>>
>>>> On Sun, Nov 22, 2020 at 10:25 PM David G. Johnston <
>>>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>>>
>>>>>
>>>>> On Sunday, November 22, 2020, Sachin Kumar <sachinkumaras(at)gmail(dot)com>
>>>>> 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. PGadmin
>>>>>> 4.28 which I am using in chrome is getting crashed. I tried it 4 times and
>>>>>> it crashed 3 times. After viewing this 10M row the other queries are very
>>>>>> running slow.
>>>>>> 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.
>>>>>>
>>>>>>
>>>>> Stop and restart the program?
>>>>>
>>>>> What do you hope to do with 10M full records displayed on screen (even
>>>>> if pagination is involved...)?
>>>>>
>>>>> David J.
>>>>>
>>>>>
>>>>
>>>
>>> --
>>>
>>> Best Regards,
>>> Sachin Kumar
>>>
>>
>
> --
>
> Best Regards,
> Sachin Kumar
>