Each master of database optimization are:
Following a performance problems in the current development, to improve performance, please and thank you very much.
Now there is a corresponding four tables CSV file, corresponding to each table, there are seven CSV file, a total of 28 file,
28 CSV file size is 16 g, one of the tables of the import data about 200 million rows,
Use posgresql copy command will import data into posgresql database,
Now using Java to write a program, using threads, thread pool management up 28 threads at the same time, the copy command,
Run the application environment is centos 16 g memory 12 kenel 24 CPU thread,
Hard disk write speed is 200 megabits per second
Now the average time to complete the import task is 1400 seconds, nearly more than 23 minutes,
With iostat command found IO utilization rate is 100%, basically feel performance bottlenecks in IO,
The CPU utilization rate is 20%.

Customer actual data quantity should be 70 times the amount of the data, it is nearly 27 hours
Customer will not accept,

Haven't dealt with this amount of data into database, because of adopting posgresql copy command
Is the bulk import, have what method to improve performance, hope expert guidance,

CodePudding user response:

You master database

The above problem, can you provide the optimization idea,
Thank you very much

CodePudding user response:

Your database performance tuning bosses

The above problem, can you provide the optimization idea,
Thank you very much

CodePudding user response:

Now read using PostgreSQL copy command using multithreading CSV file data into multiple tables, each table data quantity is over one hundred million, fields related relation between each table,
How to ensure the consistency of the data
Each thread USES the new database connection, there is no guarantee that the consistency of the data, if the transaction management, sharing a database connection,
Multithreading can concurrent
Hope and