Início / dba / Perguntas / 251375
Accepted
dwenzel
Asked: 2019-10-18 06:38:00 +0800 CST

Não é possível ver o progresso do processo, máquina ficando lenta tentando importar 500 milhões de registros

  • 772

Ambiente de trabalho: PostgreSQL 12.0, Ubuntu 18.10, 4GB de memória, CPU i5-3230M @ 2.60GHz × 4, SO 64 bits

Estou tentando importar um arquivo .csv com mais de 500 milhões de registros em uma tabela de banco de dados PostgreSQL, usando meu PC. (estamos testando no meu PC até funcionar, depois faremos em um servidor)

Minha empresa está migrando do Firebird para o Postgres, então essa deve ser uma tarefa única. Depois disso, esperamos inserir cerca de 200 mil registros por dia.

Criar tabela com (SQL gerado a partir do banco de dados original do firebird)

     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 é gerado usando 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"

Eu converto raw_regdata.out para utf8 para trabalhar com codificação postgres. Livrar-se de alguns caracteres (ã,º,etc)

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

Eu segui as diretrizes do Postgres sobre como importar dados grandes, como:

  • Eliminando todos os índices;
  • Aumentando Maintenance_work_mem (maintenance_work_mem = 512 MB)
  • Aumentando max_wal_size (max_wal_size = 4 GB)
  • Configurando wal_level = mínimo
  • Configurando max_wal_senders = 0

Depois de conectar ao cluster Postgres, uso COPY para importar os dados

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

E aí vem o problema, não consigo ver se o comando está progredindo ou não, meu PC fica muito lento depois de algum tempo e simplesmente congela .

1ª Tentativa: deixe rodar por 2-3 horas, depois elimine o processo (Ctrl+c no terminal), pelos logs vejo que estava progredindo (linha 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;

Mas eu tento selecionar algo e não consigo nada, acho que como o Copy só confirma após todo o processo terminar, ele apenas desfaz tudo e acabo com uma tabela vazia

2ª Tentativa: deixe rodar 24 horas (mesmo que meu PC esteja totalmente bloqueado), depois mate o processo novamente, esperando ver mais progresso nos logs, mas para minha surpresa não vejo nenhum log semelhante, apenas os mesmos 3 avisos e mais uma vez:

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

Existe uma maneira de monitorar o andamento (em qual linha está trabalhando no momento), e é normal ficar uma máquina morta enquanto o processo está ativo?

edit: Olhando no banco de dados original do firebird, posso ver que a chave primária não está classificada: insira a descrição da imagem aqui

postgresql import

2 respostas

  1. Best Answer

    Não há uma maneira de monitorar o progresso. v12 adicionou monitoramento de progresso para algumas ações, mas não para COPY.

    O que eu fiz nesta situação é usar strace -s 1024 -y -p <backend pid>para obter um rastreamento do processo de carregamento. Isso mostrará quais dados ele está lendo do arquivo "/home/dani/Documents/utf8_regdata.out" (junto com várias outras coisas). Então você pode simplesmente pegar uma chave primária recente desse fluxo e fgrep -n ,<primarykey> /home/dani/Documents/utf8_regdata.outver em qual número de linha ela está. Não é o procedimento mais gracioso, mas é melhor do que ficar completamente no escuro.

    Se você não descartar a chave primária e os dados não forem classificados na ordem da chave primária ao carregá-los, quando o tamanho do índice da chave primária exceder algum limite, você obterá um congestionamento de E/S extremo. Isso explicaria as "estatísticas obsoletas" e o "trabalhador demorou muito para iniciar", e o sistema também pode ficar tão lento que parece estar morto (especialmente se você estiver usando uma GUI).

    O limite de colapso de desempenho para o tamanho do índice geralmente estará em algum lugar entre shared_buffere greatest(shared_buffers,RAM - shared_buffers), mas exatamente onde dependerá das versões e configurações do kernel.

    • 1

  2. Outra forma é usar a função count_estimate para ter uma ideia do progresso contando os registros com umSELECT count_estimate('SELECT * FROM REGDATA')

    • 0

relate perguntas