Leon's questions

Estamos usando o PostgreSQL-13 como nosso servidor principal e encontramos um gargalo de desempenho.

O hardware inclui 2 CPUs (AMD EPYC9754 com 128 núcleos e 256 threads cada), 128 GB de memória, hardware RAID0 inclui 2 * 4T SSD (Samsung990Pro).

Acho que o servidor PG não consegue usar todo o potencial do hardware.

Antes de fazer esta pergunta aqui, eu fiz as seguintes coisas:

1. Verifique minha consulta SQL (usando exatamente a chave primária, sem varredura de sequência);
2. Confirmar se meu programa roda corretamente (embora eu tenha escrito a mesma lógica em Python e C++, ambos rodam lentamente);
3. Configurar meu servidor PG para usar o HugePage (configurei 80 GB de memória HugePage no total e permiti que o servidor PG use 64 GB shared_buffers);
4. Aumentar os limites de memória de cada trabalhador PG( work_mem, temp_buffers,...);
5. Aumentar o número de trabalhadores paralelos( max_worker_processes, max_parallel_workers_per_gather, max_parallel_workers,...);
6. Ligar force_parallel_mode;
7. Diminua o custo de io e o custo da CPU cfg( random_page_cost=1.01, cpu_tuple_cost=0.0025, cpu_index_tuple_cost=0.001,...);
8. Maximizado default_statistics_targetpara 1000;
9. Desabilitar replicações;
10. 32 ou 64 processos de inicialização se conectam paralelamente ao servidor e consultam diferentes linhas de uma mesma tabela com diferentes chaves primárias.
11. Todas as consultas são somente leitura e NÃO há operações de inserção/atualização/DDL durante os testes;

O que estou esperando:

1. O servidor PG usa pelo menos 50% dos recursos de CPU/memória;
2. Não há nenhum processo único bloqueando outras consultas;
3. O rendimento total basicamente aumenta linearmente conforme a quantidade de recursos que aloquei ao servidor PG (talvez eu seja ingênuo?);

Meus testes:

1. A definição da tabela:

   CREATE TABLE IF NOT EXISTS ob_lots(
       t_stamp INT8 NOT NULL,
       trd_sec INT4 NOT NULL,
       o_level INT2 NOT NULL,
       i_olots INT4 NOT NULL,
       f_olots FLOAT4 NULL,
   CONSTRAINT prk_ob_lots PRIMARY KEY( t_stamp, o_level ) );
   

2. A pergunta:

   SELECT f_olots, t_stamp, trd_sec FROM ob_lots
   WHERE t_stamp BETWEEN $1 AND $2
   ORDER BY t_stamp DESC, o_level DESC
   LIMIT 4096;
   

3. Programa de teste: Inicie muitas instâncias do meu programa que consultam aleatoriamente linhas da tabela acima paralelamente com o SQL de consulta acima. Quando o número de clientes estava aumentando de 2 para 4, ou de 4 para 8, ou de 8 para 16, observamos que o rendimento total quase dobrou todas as vezes. Mas de 16,32 ou mais, o rendimento total nunca mudou.

4. Explicação SQL:

   Gather  (cost=1000.28..1002.41 rows=1 width=18) (actual time=6.840..9.232 rows=0 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   Single Copy: true
   Buffers: shared hit=8
   ->  Limit  (cost=0.28..2.31 rows=1 width=18) (actual time=0.033..0.033 rows=0 loops=1)
           Buffers: shared hit=8
           ->  Index Scan Backward using prk_ob_lots_sc5555 on ob_lots_sc5555  (cost=0.28..2.31 rows=1 width=18) (actual time=0.031..0.031 rows=0 loops=1)
               Index Cond: ((t_stamp >= 123) AND (t_stamp <= 456))
               Buffers: shared hit=8
   Planning:
   Buffers: shared hit=109
   Planning Time: 0.759 ms
   Execution Time: 9.274 ms
   

As seguintes coisas me parecem estranhas:

1. Há um processo PG que usa quase 100% de um único núcleo de CPU, e os outros usam muito menos;
2. NÃO há io, NENHUMA troca durante o teste, e muitos recursos de memória/CPU/io estão livres/ociosos. Algo parece que todos os outros trabalhadores estão esperando pelo processo PG mestre (imagino que um use 100% da CPU), e resulta em um gargalo;
3. O banco de dados de teste usou cerca de 4 GB de espaço em disco, pequeno o suficiente para ser mantido inteiramente na memória pelo PG. De fato, como observamos, não há nenhuma operação de io;

O que está fazendo? Por que todos os trabalhadores estão esperando por um único processo?

Esta é a parte principal da minha configuração PG (eu apenas listo as linhas que alterei):

max_connections = 2048

buffers compartilhados = 64 GB

huge_pages = ligado

temp_buffers = 256 MB

max_prepared_transactions = 256

work_mem = 256 MB

manutenção_trabalho_mem = 16 GB

memória_de_trabalho_de_vácuo_automático = -1

tipo_de_memória_compartilhada_dinâmica = posix

simultaneidade_io_efetiva = 1000

manutenção_io_concorrência = 1000

max_worker_processes = 256

max_parallel_maintenance_workers = 256

max_parallel_workers_per_gather = 256

parallel_leader_participation = ligado

max_parallel_workers = 256

fsync = desligado

synchronous_commit = desligado

full_page_writes = desativado

wal_compression = ativado

wal_buffers = -1

atraso_do_escritor_wal = 10000ms

wal_writer_flush_after = 1 GB

atraso_de_commit = 100000

commit_siblings = 128

tempo_limite_do_ponto_de_verificação = 1d

max_wal_size = 128 GB

tamanho_min_wal = 32 GB

meta_de_conclusão_do_ponto_de_verificação = 1.0

checkpoint_flush_after = 0

aviso_do_ponto_de_verificação = 0

max_wal_senders = 0

custo_da_página_seq = 1,0

custo_da_página_aleatória = 1,01

custo_tupla_da_cpu = 0,0025

custo_da_tupla_do_índice_da_cpu = 0,001

custo_operador_cpu = 0,00125

tamanho_do_cache_efetivo = 64 GB

meta_estatística_padrão = 1000

force_parallel_mode = ativado

autovacuum = ligado

E as saídas de tope iotop:

topo - 16:38:16 acima 4:09, 2 usuários, média de carga: 14,16, 9,14, 3,97

Número: 1581 no total, 2 em execução, 1573 dormindo, 0 parado, 6 zumbis

%Cpu(s): 3,5 us, 4,3 sy, 0,0 ni, 92,1 id, 0,0 wa, 0,0 hi, 0,1 si, 0,0 st

GiB Mem: 125,6 total, 34,6 grátis, 82,9 usado, 9,1 buff/cache

GiB Swap: 1,0 total, 1,0 grátis, 0,0 usado. 42,6 disponível Mem

进程号 USER PR NI VIRT RES SHR SWAP %CPU %MEM TIME+ COMMAND

31159 leon 20 0 4654,2m 105,1m 12,5m 0,0m S 152,7 0,1 7:09,93 carga

3186 postgres 0 -20 66,7g 18,6m 16,2m 0,0m R 99,5 0,0 5:03.16 postgres #é o mestre?

3192 postgres 0 -20 80,4m 6,8m 3,6m 0,0m S 8,2 0,0 0:24,97 postgres

32218 postgres 0 -20 66,7g 13,5m 9,9m 0,0m S 5,8 0,0 0:12,90 postgres

31217 postgres 0 -20 66,7g 13,4m 9,9m 0,0m S 5,3 0,0 0:12,74 postgres

31234 postgres 0 -20 66,7g 13,5m 9,9m 0,0m S 5,3 0,0 0:12,74 postgres

(muitos muitos processos postgres...)

Total de LEITURA DE DISCO: 0,00 B/s | Total de GRAVAÇÃO DE DISCO: 0,00 B/s

LEITURA DE DISCO atual: 0,00 B/s | GRAVAÇÃO DE DISCO atual: 0,00 B/s

TID  PRIO  USER     DISK READ DISK WRITE>    COMMAND

  1 be/4 root        0.00 B/s    0.00 B/s init

  2 be/4 root        0.00 B/s    0.00 B/s [kthreadd]

  3 be/0 root        0.00 B/s    0.00 B/s [rcu_gp]

  4 be/0 root        0.00 B/s    0.00 B/s [rcu_par_gp]

  5 be/0 root        0.00 B/s    0.00 B/s [slub_flushwq]

Por muitos anos, usei a variável de ambiente $PGUSERquando trabalhei com ferramentas de cliente, como psql/pg_dump/pg_restore.

Recentemente eu montei um novo servidor PG13, a env var $PGUSERNÃO entra em vigor , quando eu tento usar as ferramentas do cliente em um terminal do servidor. Eu recebo uma mensagem como esta:

leon@mamba:~$ echo $PGUSER

postgres

leon@mamba:~$ psql

psql: erro: falha na conexão com o servidor no soquete "/var/run/postgresql/.s.PGSQL.5432":

FATAL: papel "leon" não existe

Se eu usar as ferramentas em um terminal de outro computador físico, a variável env entra em vigor normalmente.

Claro, a var foi definida em ambas as máquinas.

Meu pg_hba.conf:

# TIPO MÉTODO DE ENDEREÇO ​​DE USUÁRIO DO BANCO DE DADOS

hospedar todos todos 127.0.0.1/32 confiança

hospedar todos todos 192.168.xx/16 md5

# Conexões locais IPv6:

hospedar todos todos ::1/128 confiança

Estou usando o PostgreSQL-13.0.

Existe uma maneira de obter a hora de criação ou a hora da última modificação de um banco de dados?

Obrigado!

Estou criando uma tabela no Postgresql-11, e preciso criar uma chave primária para ela ao mesmo tempo, para evitar que dados duplicados sejam inseridos antes da criação do PK, então quero que essas duas operações sejam realizadas dentro de uma mesma transação.

Eu sei que isso pode dar certo:

CREATE TABLE IF NOT EXISTS my_tab (
    my_key INT8 NOT NULL,
    PRIMARY KEY ( my_key ) );

Mas também quero definir um nome para este PK, em vez do nome padrão, para facilitar a manutenção.

Se eu fizer da seguinte maneira:

CREATE TABLE IF NOT EXISTS my_tab ( my_key INT8 NOT NULL );
ALTER TABLE my_tab ADD CONSTRAINT custom_pk_name_my_tab PRIMARY KEY ( my_key );

Estou preocupado que essas duas declarações sejam feitas em duas transações diferentes. ou seja, existe alguma probabilidade de alguns dados duplicados serem inseridos antes da criação da PK?

E ainda mais, que tal se a mesa já existisse? Se a tabe já existir, a segunda instrução falhará?

O SQL acima é executado em meu programa C++, que é um serviço daemon de longa data. Não quero que ele trave sem a participação humana por causa de uma falha de SQL.

Como conseguir isso? Obrigado!

Já procurei na documentação do PostgreSQL e Google, mas não encontrei a função pg_get_tabledef, while pg_get_indexdefexist.

Não há pg_get_tabledef? Talvez seja muito difícil de ser implementado? mas o pgAdmin4 pode fornecer scripts DDL muito bonitos/claros/limpos para todos os objetos em um banco de dados. Como isso acontece?

Na verdade, preciso comparar as definições de várias tabelas de dois bancos de dados ( como comparar rapidamente as definições de várias tabelas de dois bancos de dados PostgreSQL? ).

Embora existam pg_dump --section=pre-datae pg_dump --section=post-datapossam despejar todas as definições, mas comparar os resultados ainda seria um trabalho enorme. Eu preciso descobrir exatamente qual estrutura de tabelas foi alterada.

Então, estou esperando pg_get_tabledef, como pg_get_indexdef, mas para tabelas. É estranho que não exista tal função, enquanto pg_get_indexdefexiste.

Você poderia me dar alguma dica? obrigado!

Tenho centenas de tabelas com a mesma estrutura em um banco de dados Postgresql-11 e preciso realizar uma mesma estatística em cada uma delas.

Como a lógica das estatísticas reais é muito complexa, para simplificar minha pergunta aqui, digamos que eu precise calcular MAX/MIN/MEAN/STDDEV de cada um.

Não quero editar SQLs individuais para cada tabela, uma a uma, pois, nesse caso, codificaria centenas de linhas de SQL muito semelhantes, com uma única diferença no nome da tabela.

Então usei o SQL dinâmico em uma função para fazer o cálculo de uma tabela por vez, assim:

DROP FUNCTION IF EXISTS get_features_for;
CREATE OR REPLACE FUNCTION get_features_for(
    IN  table_name VARCHAR,
    OUT result_ RECORD ) LANGUAGE 'plpgsql'
AS $func$

DECLARE sql_string VARCHAR :=
    format('
        SELECT ''%s'' table_name,     -- just for validating
            MAX(feature) max_feature,
            MIN(feature) min_feature,
            AVG(feature) avg_feature,
            STDDEV(feature) std_feature
        FROM "%s";',
        table_name, table_name );

BEGIN
  EXECUTE sql_string INTO result_;
END
$func$;

E então chame-o com todos os nomes de tabelas no banco de dados, dentro de uma consulta, assim:

SELECT tbs."table_name", get_features_for( tbs."table_name" )
FROM information_schema."tables" tbs
WHERE tbs.table_schema = 'public' AND tbs.table_type = 'BASE TABLE'
  AND tbs."table_name" LIKE 'analy%'
ORDER BY tbs."table_name";

Funciona, mas os resultados são semelhantes aos seguintes:

"table_name", "get_features_for"

"analy_001", "(analy_001,-3,2,0,2.5)"

"analy_002", "(analy_002,-3,2,-1,2)"

"analy_003", "(analy_003,-3,2,0,2)"

Você pode ver que todas as colunas de resultado foram agrupadas em uma única coluna de string e deixe-me NÃO usá -las convenientemente.

Eu quero que eles sejam semelhantes a:

table_name, max_feature, min_feature, avg_feature, std_feature

"analy_001", "analy_001", -3, 2, 0, 2,5

"analy_002", "analy_002", -3, 2, -1, 2

"analy_003", "analy_003", -3, 2, 0, 2

Como devo conseguir isso?

Aliás, estou usando o pgAdmin.

Obrigado!!!

Eu tenho uma tabela enorme como a seguir:

CREATE TABLE public.huge_table (
    sampl_day date NOT NULL,
    tick_time timestamp(6) with time zone NOT NULL,
    crit_feat integer NOT NULL,
    ---
    --- other fields
    ---
    CONSTRAINT pkey_huge_table PRIMARY KEY (tick_time)
);
CREATE INDEX idx_huge_table_day_time
ON public.huge_table USING brin (sampl_day, tick_time);

Esta tabela armazena muitas amostras (linhas) de uma série temporal, uma linha para uma amostra. Na maioria das vezes, o campo "crit_feat" tem um valor muito baixo, por exemplo 1 ou 2. Em algum momento anormal, ele iria muito alto, por exemplo 100.

É muito fácil descobrir essas linhas especiais em uma única consulta. Vamos chamá-los de "pontos de tempo especiais".

E agora preciso visualizar as linhas que aconteceram em 5s antes/depois de cada um dos pontos de tempo especiais acima em um conjunto de resultados de consulta, para descobrir o que aconteceu antes/depois de um evento especial.

Se eu precisar apenas descobrir um único ponto de tempo especial e linhas em 5s antes / depois dele, é relativamente mais fácil.

eu posso fazer assim:

WITH spec_time as (
    SELECT tick_time tp
    FROM huge_table
    ORDER BY crit_feat DESC LIMIT 1)

SELECT *
FROM huge_table
WHERE tick_time BETWEEN (SELECT tp FROM spec_time) - INTERVAL '5s'
                    AND (SELECT tp FROM spec_time) + INTERVAL '5s'
ORDER BY tick_time;

Mas preciso fazer isso com todos os pontos especiais e UNIONtodos eles!

O número de todos os pontos especiais é de cerca de 20k no mínimo, e os eventos que devem ser selecionados NÃO são APENAS cujo campo "crit_feat" tem o maior valor, MAS TAMBÉM incluem aqueles com um valor maior que um limite especificado, por exemplo: o valor mediano do campo "crit_feat" em todo o conjunto de dados.

Acho que posso obtê-lo com tabela temporária e cursor em um procedimento/função de armazenamento, mas estou esperando um método mais simples!

Desculpe pelo meu inglês feio, espero ter expressado corretamente as coisas que quero dizer.

Obrigado!

Eu tenho uma tabela enorme no Postgresql-11 da seguinte forma:

CREATE TABLE my_huge_table(
    tick_time timestamp(6) with time zone NOT NULL,
    brok_time timestamp(6) with time zone,
    trade_day date NOT NULL,

    --other fields ...
    ...

    CONSTRAINT my_huge_table_pkey PRIMARY KEY (tick_time)
);

CREATE INDEX idx_my_huge_table_td_time ON my_huge_table USING brin
    ( trade_day, abs(tick_time - brok_time) );

Aí eu faço uma query e quero que ela aproveite o index idx_my_huge_table_td_time, assim:

SELECT * FROM my_huge_table
WHERE trade_day BETWEEN TO_DATE('20220104', 'YYYYMMDD') AND TO_DATE('20220104', 'YYYYMMDD') 
  AND ABS(tick_time - brok_time) < INTERVAL '10 s';

Mas o PostgreSQL se recusou a executá-lo e disse:

ERRO: a função abs(intervalo) não existe

LINHA 3: AND ABS(tick_time - brok_time) < INTERVAL '10 s'

           ^ 

DICA: Nenhuma função corresponde ao nome e aos tipos de argumento fornecidos. Pode ser necessário adicionar conversões de tipo explícito.

Estado SQL: 42883 Caractere: 525

Parece que o func NÃOabs() pode aceitar um valor de intervalo como argumento.

Então, mudei minha consulta:

SELECT * FROM my_huge_table
WHERE trade_day BETWEEN TO_DATE('20220104', 'YYYYMMDD') AND TO_DATE('20220104', 'YYYYMMDD') 
  AND GREATEST(tick_time - brok_time, brok_time - tick_time) < INTERVAL '10 s';

Desta vez pode ser executado, mas não aproveitou o índice.

Minhas perguntas:

1.Como devo compor a expressão do índice? Na verdade, quero registrar uma distância (valor de intervalo absoluto) entre dois campos de carimbo de data/hora;

2.Como devo codificar a consulta que pode usar o índice acima?

3.Na verdade , NÃOGREATEST(tick_time - brok_time, brok_time - tick_time) é uma boa ideia, pois invocou a computação duas vezes. Não é?

4.Após criado o índice, observo que a DDL SQL real do índice reportado pelo PostgreSQL é:

CREATE INDEX idx_my_huge_table_td_time ON public.my_huge_table USING brin
    (trade_day, abs(date_part('epoch'::text, tick_time - brok_time)));

O valor da expressão foi convertido em um texttipo? Aparentemente NÃO é minha expectativa!

Nos últimos dias, ajustei meu PG11 para grandes transações de escrita e obtive um resultado incrível.

A única preocupação em minha mente é se há mais risco de perda de dados quando eu desliguei synchronous_commite usei um wal_buffersvalor grande.

Estou usando o Postgresql-11, com um HDD tradicional como dispositivo de armazenamento físico.

Preciso otimizar o desempenho para escrever.

Eu mudei wal_buffersde -1 para 512 MB, isso significa que wal_buffersnão uso mais arquivos shared_buffers. Estou certo?

Eu quero saber se pode ou não melhorar o desempenho da escrita para ampliar shared_buffers. Na minha opinião, é usado apenas para consultar?

O valor atual shared_buffersé de 2 GB, minha RAM é de 8 GB. Eu quero mudar para 4GB. obrigado!

Ao depurar programas, preciso descartar frequentemente um banco de dados do Postgresql e recriá-lo novamente.

Por conveniência, quero executar isso em uma única linha de comando, como a seguir:

psql -c "DROP DATABASE my_db; CREATE DATABASE my_db;"

Mas recebo um erro:

ERRO: DROP DATABASE não pode ser executado dentro de um bloco de transação

Existe uma maneira que eu posso fazer isso? Obrigado!

Postgresql-11

select tick_time, nano_secs,
    concat( to_char(tick_time, 'MMDD HH24:MI:SS.US'),
            to_char(nano_secs, '000') )
from ticks
order by tick_time, nano_secs limit 100;

Eu quero concatenar 2 strings como acima, mas sempre adiciona um caractere de espaço entre as duas strings.

Como evitar que isso aconteça?

Obrigado!

Vou usar um índice BRIN para substituir o índice B-tree em uma coluna de data/hora no Postgresql-11. Eu nunca usei antes.

Já que um índice BRIN pode ser mais eficaz se os dados forem armazenados fisicamente na ordem em que a coluna está sendo indexada.

Estou em dúvida se devo ou não excluir todos os dados, e depois reinseri-los no pedido. Meus dados estão sendo usados ​​para análise estática, não mudariam.

Eu tenho um grande arquivo de despejo que é gerado por pg_dumpque vem com o cliente PostgreSQL v12 e está em formato personalizado em vez de texto sql simples. A versão do servidor também é PostgreSQL v12.

Agora preciso recarregar o arquivo em um servidor PostgreSQL v11 e recebo uma mensagem de erro:

unrecognized configuration parameter "default_table_access_method"

Eu acho que esse parâmetro é introduzido pelo PG12, então o PG11 não pode reconhecê-lo, mesmo que eu use a versão 12 do pg_restore.

Como o arquivo é enorme e em formato binário, NÃO consigo editá-lo para remover a linha manualmente.

Eu testei isso pg_restore test_file -f plain.sqlcom um arquivo de despejo de teste e parece funcionar, e posso remover a linha do arquivo SQL simples. Mas o arquivo de despejo real tem cerca de 30 GB, o arquivo SQL simples expandido do binário pode ser maior (o banco de dados de trabalho ocupa 300 GB de espaço em disco).

Existe uma maneira de recarregá-lo de forma limpa no PG11, apenas sem a linha "SET default_table_access_method = heap;"?

Na verdade, todos os itens a seguir podem ser ignorados, porque são todos valores padrão:

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';

Eu tenho um grande banco de dados rodando com PostgreSQL v11, e o espaço em disco é muito limitado, então eu tenho que VACUUM (FULL)acessar o banco de dados com frequência (por exemplo, todos os dias).

Mas a aspiração é uma operação muito demorada. Além disso, muitas vezes encontro mensagens de erro de vácuo, como

ERROR:  canceling autovacuum task
CONTEXT:  automatic vacuum of table ***

Felizmente, o serviço pode ser encerrado em um determinado momento do dia, para que eu possa despejar o banco de dados após desligá-lo, restaurá-lo imediatamente e reiniciar o serviço novamente.

Além disso, descobri que "despejar/restaurar" é mais rápido que "aspirar".

Minha pergunta: as coisas que são feitas por "despejo/restauração" incluem as coisas que são feitas por "aspiração completa"?

Se assim for, posso fazê-lo em vez de aspirar.