0

Working environment: PostgreSQL 12.0, Ubuntu 18.10, 4GB memory, i5-3230M CPU @ 2.60GHz × 4, Os 64 bits

I'm trying to import a .csv file with more than 500 million records into a PostgreSQL db table, using my PC. (we're testing on my PC until it works, after that we will do it on a server)

My company is migrating from Firebird to Postgres, so this should be one time task. After that, we expect to insert ~200k records each day.

Create table with (SQL generated from original firebird database)

     CREATE TABLE REGDATA
     (
        CODIGO integer NOT NULL,
        DTAREG timestamp NOT NULL,
        PERIOD integer NOT NULL,
        FLDCODIGO integer,
        REGVALUE double precision,
        CLICODIGO integer,
        SITCODIGO integer,
        CONSTRAINT PK_REGDATA PRIMARY KEY (CODIGO)
     );

.csv is generated using FBExport 1.9

./fbexport -Sc -D /opt/firebird/bin/measures.fdb -H localhost -U user -P password -F /home/dani/Documents/raw_regdata.out -Q "SELECT * FROM REGDATA"

I convert raw_regdata.out to utf8 to work with postgres encoding. Getting rid of some characters (ã,º,etc)

iconv -c -t utf8 /home/dani/Documents/raw_regdata.out > /home/dani/Documents/utf8_regdata.out

I've followed Postgres guidelines on how to import large data, like:

  • Dropping all Indexes;
  • Increasing maintenance_work_mem (maintenance_work_mem = 512MB)
  • Increasing max_wal_size (max_wal_size = 4GB)
  • Setting wal_level = minimal
  • Setting max_wal_senders = 0

After connecting to Postgres cluster, I use COPY to import the data

COPY REGDATA(CODIGO,DTAREG,PERIOD,FLDCODIGO,REGVALUE,CLICODIGO,SITCODIGO) from '/home/dani/Documents/utf8_regdata.out' DELIMITER ',' CSV HEADER;

And here comes the problem, I cant see if the command is progressing or not, my PC gets very slow after some time and it just freezes.

1st Attempt: let it run for 2-3 hours, then killed the process (Ctrl+c on terminal), from logs I see that it was progressing (line 131158327):

2019-10-16 10:28:05.657 -03 [9258] postgres@measures ERROR:  canceling statement due to user request
2019-10-16 10:28:05.657 -03 [9258] postgres@measures CONTEXT:  COPY regdata, line 131158327: ""178865944","13.03.2015 12:10:00","600","22439","358.60000000000002","9","37""
2019-10-16 10:28:05.657 -03 [9258] postgres@measures STATEMENT:  COPY REGDATA(CODIGO,DTAREG,PERIOD,FLDCODIGO,REGVALUE,CLICODIGO,SITCODIGO) from '/home/dani/Documents/utf8_regdata.out' DELIMITER ',' CSV HEADER;

But I try to select something and get nothing, I think since Copy only commits after the whole process finish, it just undo everything and I end up with a empty table

2nd Attempt: let it run 24 hours (even if my PC is totally locked), then kill the process again, hoping to see more progress on logs, but for my surprise I dont see any similar logs, only the same 3 warnings over and over again:

2019-10-16 17:42:31.061 -03 [5646] LOG:  using stale statistics instead of current ones because stats collector is not responding
.
.
.
2019-10-17 06:10:31.423 -03 [2734] WARNING:  worker took too long to start; canceled
2019-10-17 06:57:19.150 -03 [5964] WARNING:  autovacuum worker started without a worker entry
2019-10-17 08:04:47.445 -03 [2327] LOG:  starting PostgreSQL 12.0 (Ubuntu 12.0-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
2019-10-17 08:04:47.622 -03 [2327] LOG:  listening on IPv4 address "127.0.0.1", port 5412
2019-10-17 08:04:48.048 -03 [2327] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5412"
2019-10-17 08:04:53.879 -03 [2546] LOG:  database system was interrupted; last known up at 2019-10-16 15:47:25 -03
2019-10-17 08:05:10.887 -03 [2546] LOG:  database system was not properly shut down; automatic recovery in progress
2019-10-17 08:05:11.534 -03 [2546] LOG:  redo starts at 14/B8254908
2019-10-17 08:05:11.847 -03 [2546] LOG:  invalid record length at 14/B8260848: wanted 24, got 0
2019-10-17 08:05:11.847 -03 [2546] LOG:  redo done at 14/B82607D0
2019-10-17 08:05:16.417 -03 [2327] LOG:  database system is ready to accept connections

There's a way of monitoring the progress (which line it is currently working on), and it is normal to get a dead machine while the process is active?


edit: Looking on original firebird database, I can see primary key is not sorted: enter image description here

  • "Is there a way of monitoring the progress" - no, unfortunately not. But you might want to have a look at pgloader which is able to do loads like that in parallel. But given the specs of your machine, that would probably make it even worse. Your harddisk speed will be the most limiting factor for this – a_horse_with_no_name Oct 17 at 14:48
  • Did you drop the primary key? Is the data being loaded sorted (or at least clumped) by the primary key? If "no" to both, then you are dirtying random leaf pages from a large and growing index at a furious pace, and this is will cause extreme io congestion. – jjanes Oct 17 at 15:09
  • I've dropped the primary key. And I dont think data is completely sorted by primary key on original database, there's chunks of records that skips primary key to some high number then come back to normal again. My initial guess was something about miss configuration on postgres cluster, or maybe wrong data format since I used the generated SQL from firebird – dwenzel Oct 17 at 16:07
  • If there are no indexes, primary key, triggers, or foreign key constraints, then I don't know what might be going on. Without those, the performance might be slow, but should not collapse the system, even if your config is not optimal. You might get sluggishness at the end of checkpoints (and turning log_checkpoints=on might be helpful for diagnosis). – jjanes Oct 17 at 16:56
  • I don't know anything about firebird exports, but if PostgreSQL doesn't like the format it uses I would expect it to throw an error much earlier in the process. – jjanes Oct 17 at 17:02
1

There is no built in way to monitor the progress. v12 has added progress monitoring for some actions, but not for COPY.

What I have done in this situation is use strace -s 1024 -y -p <backend pid> to get a trace of the loading process. This will show what data it is reading from the "/home/dani/Documents/utf8_regdata.out" file (along with a bunch of other stuff). Then you can just grab a recent primary key out of that stream, and do fgrep -n ,<primarykey> /home/dani/Documents/utf8_regdata.out to see what line number it is at. It is not the most graceful procedure, but is better than being completely in the dark.

If you did not drop the primary key and the data is not sorted in primary key order when you load it, then once the size of the primary key index exceeds some threshold you will get extreme IO congestion. That would explain the "stale statistics" and the "worker took too long to start", and the system can also become so slow that it appears to be dead (especially if you are using a GUI).

The threshold of performance collapse for the index size is generally going to be somewhere between shared_buffer and greatest(shared_buffers,RAM - shared_buffers), but exactly where will depend on kernel versions and settings.

  • In order to use commands on terminal (strace, fgrep...), I need to solve this performance issue first. shared_buffers = 131072. I have changed max_wal_size = 3GB now and I'm ready to do another test. I would use something to split the original file into 3-4 parts, but I dont think such thing exists, not with FBExport... – dwenzel Oct 17 at 16:43
  • Right, once the system has become unresponsive there isn't much you can do. I don't think there is a way around this. – jjanes Oct 17 at 16:47
  • If you are using Linux, there is the "split" command to split files into chunks. But if you have embedded newlines in the data, it might produce invalid chunks if it happens to split a multi-line record. – jjanes Oct 17 at 17:04
  • Used "split -l 100000000 utf8_regdata.out" to split into 100 million lines chunks, and my PC is starting to tremble, main file has 37GB and first chunk currently got 4GB... will respond in some time if that works or not – dwenzel Oct 17 at 17:23
  • After ~1h the process ended with result "COPY 99999999", it ignored Header line so I think its a success! Appreciate your help! – dwenzel Oct 17 at 18:12
0

Another way is to use count_estimate function to get an idea of the progress counting the records with a SELECT count_estimate('SELECT * FROM REGDATA')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.