Re: performance problem on big tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Total RAM on your host is 5GB, really?  Before touching anything else, increase your RAM.  That will be your big performance boost right there.  Then, you can "up" your effective_cache_size and maintenance_work_mem.

Regards,
Michael Vitale

Monday, August 14, 2017 9:24 AM

I have performance issues with two big tables. Those tables are located on an oracle remote database. I'm running the quert : insert into local_postgresql_table select * from oracle_remote_table.

The first table has 45M records and its size is 23G. The import of the data from the oracle remote database is taking 1 hour and 38 minutes. After that I create 13 regular indexes on the table and it takes 10 minutes per table ->2 hours and 10 minutes in total.

The second table has 29M records and its size is 26G. The import of the data from the oracle remote database is taking 2 hours and 30 minutes. The creation of the indexes takes 1 hours and 30 minutes (some are indexes on one column and the creation takes 5 min and some are indexes on multiples column and it takes 11 min.

Those operation are very problematic for me and I'm searching for a solution to improve the performance. The parameters I assigned :

min_parallel_relation_size = 200MB
max_parallel_workers_per_gather = 5
max_worker_processes = 8
effective_cache_size = 2500MB
work_mem = 16MB
maintenance_work_mem = 1500MB
shared_buffers = 2000MB
RAM : 5G
CPU CORES : 8

-I tried running select count(*) from table in oracle and in postgresql the running time is almost equal.

-Before importing the data I drop the indexes and the constraints.

-I tried to copy a 23G file from the oracle server to the postgresql server and it took me 12 minutes.

Please advice how can I continue ? How can I improve something in this operation ?



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux