All perguntas(dba)

Estou inserindo dados da tmp_detailstabela na detailstabela usando uma consulta INSERT em blocos. Também estou modificando os dados enquanto os insiro. A consulta está demorando muito para ser executada. Meu IOPS de leitura está atingindo 2500s, e meu IOPS de gravação está perto de 100. Minha utilização de E/S é 100%, e minha utilização de CPU é menor que 10%. Minha utilização de RAM é menor que 40%. Estou usando ctidpara inserções de blocos de 2.000.000 de linhas. O que posso fazer para melhorar o desempenho das minhas consultas e reduzir a utilização de E/S? Quero aumentar a utilização da CPU para mais de 80%.

Especificações do servidor:

• Versão do PostgreSQL: 15.6
• RAM: 32 GB
• Núcleos: 16
• Espaço em disco: SSD 500 GB
• SO: Linux Ubuntu 22.04

Configuração do PostgreSQL:

max_connections = 200
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
huge_pages = try
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 16
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4

Detalhes da tabela:

Há índices nas colunas uniquekey, key_priority_radcs, key_priority_rtdps, is_processed, e key_priority_ratrs. Tive que usar a cláusula DISTINCT ON porque estava obtendo linhas duplicadas devido ao JOIN. Tentei inserir em blocos de 1.000.000 de linhas usando ctid, mas ainda está demorando muito para executar, provavelmente porque ele tem que escanear toda a tabela C e D para cada iteração. Então, inseri os dados como um todo, 60 milhões de linhas e então executei um commit no final. Meu objetivo é executar essas consultas de inserção semelhantes para a tabela C e D em paralelo a partir do servidor de aplicativo de backend, mas será inútil se minha Utilização de E/S for 100%.

Inserir consulta:

EXPLAIN
INSERT
    INTO
    cbsupi.source_cbsupi_details (codglacct,
    refusrno,
    key_priority_radcs,
    recon_created_date,
    dattxnposting,
    status,
    uniquekey,
    coddrcr,
    cbsacqiss,
    codacctno,
    amttxnlcy,
    acnotrim,
    priority_no,
    rrn,
    recon_updated_date,
    recon_date_1_to_2,
    recon_date_1_to_3,
    reconciliation_date_time ) (
    SELECT
        DISTINCT ON
        (A.uniquekey) A.codglacct,
        A.refusrno,
        A.key_priority_radcs,
        A.recon_created_date,
        A.dattxnposting,
        A.status,
        A.uniquekey,
        A.coddrcr,
        A.cbsacqiss,
        A.codacctno,
        A.amttxnlcy,
        A.acnotrim,
        A.priority_no,
        A.rrn,
        '2025-01-07 19:50:41' AS recon_updated_date,
        CASE
            WHEN C.key_priority_rtdps IS NOT NULL THEN '2025-01-07 19:50:41'
            ELSE NULL
        END::TIMESTAMP AS recon_date_1_to_2,
        CASE
            WHEN D.key_priority_ratrs IS NOT NULL THEN '2025-01-07 19:50:41'
            ELSE NULL
        END::TIMESTAMP AS recon_date_1_to_3,
        CASE
            WHEN (C.key_priority_rtdps IS NOT NULL
                AND D.key_priority_ratrs IS NOT NULL) THEN '2025-01-07 19:50:41'
            ELSE NULL
        END::TIMESTAMP AS reconciliation_date_time
    FROM
        cbsupi.source_cbsupi_tmp_details A
    LEFT JOIN switchupi.source_switchupi_tmp_details C ON
        (A.key_priority_radcs = C.key_priority_rtdps)
    LEFT JOIN npciupi.source_npciupi_tmp_details D ON
        (A.key_priority_radcs = D.key_priority_ratrs)
    WHERE
        A.is_processed IS NULL ) ON
    CONFLICT (uniquekey) DO
UPDATE
SET
    recon_updated_date = EXCLUDED.recon_updated_date,
    recon_date_1_to_3 = EXCLUDED.recon_date_1_to_3,
    key_priority_radcs = EXCLUDED.key_priority_radcs,
    status = EXCLUDED.status,
    reconciliation_date_time = EXCLUDED.reconciliation_date_time,
    codacctno = EXCLUDED.codacctno,
    amttxnlcy = EXCLUDED.amttxnlcy,
    recon_date_1_to_2 = EXCLUDED.recon_date_1_to_2,
    rrn = EXCLUDED.rrn,
    codglacct = EXCLUDED.codglacct,
    refusrno = EXCLUDED.refusrno,
    dattxnposting = EXCLUDED.dattxnposting,
    coddrcr = EXCLUDED.coddrcr,
    cbsacqiss = EXCLUDED.cbsacqiss,
    acnotrim = EXCLUDED.acnotrim,
    priority_no = EXCLUDED.priority_no;

Explique os resultados

"QUERY PLAN"
Insert on source_cbsupi_details  (cost=72270111.44..73213761.44 rows=0 width=0)
  Conflict Resolution: UPDATE
  Conflict Arbiter Indexes: source_cbsupi_details_pkey
"  ->  Subquery Scan on ""*SELECT*""  (cost=72270111.44..73213761.44 rows=62910000 width=811)"
        ->  Unique  (cost=72270111.44..72584661.44 rows=62910000 width=823)
              ->  Sort  (cost=72270111.44..72427386.44 rows=62910000 width=823)
                    Sort Key: a.uniquekey
                    ->  Hash Left Join  (cost=10739152.00..50771187.50 rows=62910000 width=823)
                          Hash Cond: (a.key_priority_radcs = d.key_priority_ratrs)
                          ->  Hash Left Join  (cost=5337191.00..25537830.00 rows=62910000 width=800)
                                Hash Cond: (a.key_priority_radcs = c.key_priority_rtdps)
                                ->  Seq Scan on source_cbsupi_tmp_details a  (cost=0.00..2092124.00 rows=62910000 width=767)
                                      Filter: (is_processed IS NULL)
                                ->  Hash  (cost=4118441.00..4118441.00 rows=60000000 width=33)
                                      ->  Seq Scan on source_switchupi_tmp_details c  (cost=0.00..4118441.00 rows=60000000 width=33)
                          ->  Hash  (cost=4124101.00..4124101.00 rows=62910000 width=33)
                                ->  Seq Scan on source_npciupi_tmp_details d  (cost=0.00..4124101.00 rows=62910000 width=33)
JIT:
  Functions: 24
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"

Questões:

1. Como posso melhorar o desempenho da consulta e reduzir a utilização de E/S?
2. Existe uma maneira de executar essas consultas de inserção semelhantes em paralelo a partir do aplicativo sem atingir o limite de utilização de E/S?
3. Inserir dados em blocos me beneficiará ou é melhor inserir todos os dados de uma vez? Porque, pelo que observei, inserir dados em blocos está levando mais tempo do que inserir todos os dados de uma vez.

EDITAR:

Estou anexando o plano de consulta para a instrução select. Aparentemente, a consulta Insert está levando mais de uma hora, enquanto a instrução Select está levando apenas 265 segundos. Acho que meu problema está no commit único no final, possivelmente devido à geração excessiva de log. Funcionaria se eu mantivesse o auto-commit ativado? Existe uma maneira de inserir em pedaços sem fazer um loop pela tabela inteira?

Tenho um databricks SQL warehouse. Quando tento iniciá-lo, recebo o seguinte erro:

Os clusters estão falhando ao iniciar. O lançamento do cluster será tentado novamente.

Detalhes da falha mais recente: Erro: Não é possível buscar segredos referenciados na configuração do Spark. Verifique se os segredos existem e se o proprietário do cluster tem permissões de leitura. Tipo: CLIENT_ERROR Código: INVALID_ARGUMENT

Não tenho certeza do que está errado. Alguém pode explicar?

Usamos updlock em algumas instruções select para garantir que apenas uma conexão possa ler os dados. Isso não parece funcionar se o SQL Server puder buscar todos os dados de um índice, quando a outra consulta que também emite um updlock pode ser satisfeita com outro índice (não)clusterizado.

Qual seria a abordagem recomendada para ter bloqueios que realmente bloqueiem um registro de tabela, mesmo que outras consultas possam ser satisfeitas com dados de um índice?

Exemplo, conn2 deve ser bloqueado até que conn1 seja confirmado/revertido:

create database TestDB
go
use TestDB
go

create table LockTest(
    LockTestID int not null identity(1,1) primary key,
    Number int
)

create index IX_LockTest on LockTest(Number)

insert into LockTest(Number) values(1)


--Conn1
begin tran
select LockTestID from LockTest with(updlock) where LockTestID=1


--Conn2 -- The expectation here is, that this query waits untill Conn1 has committed
begin tran
select LockTestID from LockTest with(updlock) where Number=1

Mesmos resultados com holdlock ou lock

Tenho um procedimento armazenado Oracle que recebe vários parâmetros e faz muita junção externa esquerda, assim:

create or replace PROCEDURE "SOME_PROCEDURE" (
    cRECORDSET OUT SYS_REFCURSOR,
    iPHY_YEAR IN SOME_PROCEDURE_TYPE.PHYSICAL_YEAR % TYPE,
    iPHYSICAL_DOC_TYPE IN SOME_PROCEDURE_TYPE.PHYSICAL_DOC_TYPE % TYPE,
    iPHYSICAL_DOC_TYPE_NONSAP IN SOME_PROCEDURE_TYPE.PHYSICAL_DOC_TYPE_NONSAP % TYPE,
    sREFERENCE_NO IN SOME_PROCEDURE_TYPE.REFERENCE_NO % TYPE,
    sPHYSICAL_NO IN SOME_PROCEDURE_TYPE.PHYSICAL_NO % TYPE,
    iLOCATION_ID IN SOME_PROCEDURE_TYPE.LOCATION_ID % TYPE,
    iCONTAINER_ID IN SOME_PROCEDURE_TYPE.CONTAINER_ID % TYPE,
    sCONTAINER_NUMBER IN SOME_PROCEDURE_TYPE.CONTAINER_NUMBER % TYPE,
    dREGISTERED_DATE_FROM IN SOME_PROCEDURE_TYPE.REGISTERED_DATE_FROM % TYPE,
    dREGISTERED_DATE_TO IN SOME_PROCEDURE_TYPE.REGISTERED_DATE_TO % TYPE,
    iPHYSICAL_STATUS_ID IN SOME_PROCEDURE_TYPE.PHYSICAL_STATUS_ID % TYPE,
    sDRAFT_FLAG IN SOME_PROCEDURE_TYPE.DRAFT_FLAG % TYPE,
    sCANCEL_FLAG IN SOME_PROCEDURE_TYPE.CANCEL_FLAG % TYPE,
    sPHYSICAL_REMARK IN SOME_PROCEDURE_TYPE.PHYSICAL_REMARK % TYPE,
    sPHYSICAL_DOC_TITLE IN SOME_PROCEDURE_TYPE.PHYSICAL_DOC_TITLE % TYPE,
    sFI_DOC_NO_FROM IN SOME_PROCEDURE_TYPE.FI_DOC_NO_FROM % TYPE,
    sFI_DOC_NO_TO IN SOME_PROCEDURE_TYPE.FI_DOC_NO_TO % TYPE,
    iCOMPANY_ID IN SOME_PROCEDURE_TYPE.COMPANY_ID % TYPE,
    iFI_DOC_YEAR IN SOME_PROCEDURE_TYPE.FI_DOC_YEAR % TYPE,
    sFI_DOC_TYPE IN SOME_PROCEDURE_TYPE.FI_DOC_TYPE % TYPE,
    sIS_ASSET IN SOME_PROCEDURE_TYPE.IS_ASSET % TYPE,
    sWORKFLOW_STATUS_CODE IN SOME_PROCEDURE_TYPE.WORKFLOW_STATUS_CODE % TYPE,
    sPO_NO IN SOME_PROCEDURE_TYPE.PO_NO % TYPE,
    sVENDOR_NAME IN SOME_PROCEDURE_TYPE.VENDOR_NAME % TYPE,
    sCUSTOMER_NAME IN SOME_PROCEDURE_TYPE.CUSTOMER_NAME % TYPE,
    dPOSTING_DATE_FROM IN SOME_PROCEDURE_TYPE.POSTING_DATE_FROM % TYPE,
    dPOSTING_DATE_TO IN SOME_PROCEDURE_TYPE.POSTING_DATE_TO % TYPE,
    sCLEARING_DOC_NO IN SOME_PROCEDURE_TYPE.CLEARING_DOC_NO % TYPE,
    iCLEARING_DOC_YEAR IN SOME_PROCEDURE_TYPE.CLEARING_DOC_YEAR % TYPE,
    dCLEARING_DOC_DATE IN SOME_PROCEDURE_TYPE.CLEARING_DOC_DATE % TYPE,
    sPAYMENT_BLOCK IN SOME_PROCEDURE_TYPE.PAYMENT_BLOCK % TYPE,
    iAMOUNT_INC_VAT IN SOME_PROCEDURE_TYPE.AMOUNT_INC_VAT % TYPE,
    sFI_DOC_REFNO IN SOME_PROCEDURE_TYPE.FI_DOC_REFNO % TYPE,
    sSEND_TO IN SOME_PROCEDURE_TYPE.SEND_TO % TYPE,
    sSAP_REVERSE_FLAG IN SOME_PROCEDURE_TYPE.SAP_REVERSE_FLAG % TYPE,
    sCLEARING_FLAG IN SOME_PROCEDURE_TYPE.CLEARING_FLAG % TYPE,
    iPAGE_SIZE IN SOME_PROCEDURE_TYPE.PAGE_SIZE % TYPE,
    iPAGE_INDEX IN SOME_PROCEDURE_TYPE.PAGE_INDEX % TYPE,
    iUSERID IN USR_DETAIL.USER_ID % TYPE
) IS
LAST_INDEX NUMBER;
USR_PERM_TYPE GET_USR_PERM_TYPE;
FIRST_INDEX NUMBER;
BEGIN
    LAST_INDEX := iPAGE_SIZE * iPAGE_INDEX;
    FIRST_INDEX := LAST_INDEX - iPAGE_SIZE + 1;
    SELECT FN_GET_USR_PERM(iUSERID) INTO USR_PERM_TYPE FROM DUAL;
    OPEN cRECORDSET FOR 
    
    WITH CTE_PHD_DETAIL AS (
    SELECT
        *
    FROM
        PHD_DETAIL
    WHERE
        USR_PERM_TYPE.VIS_MODE = 'ALL'
        OR (
            USR_PERM_TYPE.VIS_MODE = 'UNT'
            AND (UNIT_ID = USR_PERM_TYPE.USUB_ID)
        )
        OR (
            USR_PERM_TYPE.VIS_MODE = 'SUB'
            AND SUB_UNIT_ID = USR_PERM_TYPE.USUB_ID
        )
)
    
    SELECT
    PHD_ITEMS.RECORD_NUMBER,
    PHD.PHYSICAL_ID,
    PHD.PHYSICAL_DOC_TYPE,
    PHD.PHYSICAL_NO,
    PHD.REGISTERED_DATE,
    PHD.CANCEL_FLAG,
    PHD.DRAFT_FLAG,
    CASE
            WHEN PHD.REQUESTED_NO IS NOT NULL THEN
            PHD.REQUESTED_NO ELSE PHD.TEXT_FILE_NAME
        END REFERENCE_NO,
    PHDS.PHYSICAL_STATUS_ID,
    PHDS.PHYSICAL_STATUS_NAME,
    CNT.CONTAINER_ID,
CASE
    WHEN PHD.PHYSICAL_STATUS_ID = 2 THEN
    NULL ELSE CNT.CONTAINER_NUMBER
    END CONTAINER_NUMBER,
    CNT.CONTAINER_TYPE,
    CNTU.IS_DOC_HOUSE CONTAINER_IS_DOC_HOUSE,
    CNTU.UNIT_ABBR CONTAINER_UNIT_ABBR,
    LOC.LOCATION_ID,
CASE
    WHEN PHD.PHYSICAL_STATUS_ID = 2
    OR PHD.PHYSICAL_STATUS_ID = 12
    OR PHD.PHYSICAL_STATUS_ID = 14 THEN
    (
    SELECT
        SEND_TO
    FROM
        ( SELECT SEND_TO, PHYSICAL_ID FROM PHD_STATUS_LOGS T1 WHERE T1.PHYSICAL_STATUS_ID = 2 ORDER BY CREATE_DATE DESC )
    WHERE
        ROWNUM = 1
        AND PHYSICAL_ID = PHD_ITEMS.PHYSICAL_ID
    ) --DECODE( PHDLG.SENT_TO,NULL , '',PHDLG.SENT_TO )
    WHEN CNT.CONTAINER_TYPE = 1 THEN
CASE
        WHEN CNT.CONTAINER_STATUS_ID = 2 THEN
    CASE
            WHEN CNT.CURRENT_UNIT_ID IS NOT NULL THEN
            CNTU.UNIT_FULL_NAME ELSE CCOM.COMPANY_CODE || ' - ' || CCOM.COMPANY_NAME
        END ELSE LOC.LOCATION_NAME
    END ELSE LOC.LOCATION_NAME -- LOC.LOCATION_NAME
    END LOCATION_NAME,
    FIDS.WORKFLOW_STATUS_NAME,
    FID.FI_DOC_ID,
    FID.FI_DOC_NO,
    FID.FI_DOC_YEAR,
CASE
    WHEN COM_FID.COMPANY_ID IS NOT NULL THEN
    COM_FID.COMPANY_CODE || ' - ' || COM_FID.COMPANY_NAME ELSE
CASE
        WHEN COM_PHD.COMPANY_ID IS NOT NULL THEN
        COM_PHD.COMPANY_CODE || ' - ' || COM_PHD.COMPANY_NAME ELSE ''
    END
    END AS COMPANY_FULL_NAME,
    FID.FI_DOC_REFNO,
CASE
        WHEN FID.FI_DOC_ID IS NULL THEN
        PTEMP.AMOUNT_INC_VAT ELSE FID.AMOUNT_INC_VAT
    END AMOUNT_INC_VAT,
CASE
    WHEN FID.FI_DOC_ID IS NULL THEN
    PTEMP.AMOUNT_CURRENCY ELSE FID.AMOUNT_CURRENCY
    END AMOUNT_CURRENCY,
    FID.IS_ASSET,
CASE
    WHEN PHD_ITEMS.FI_DOC_ID IS NULL THEN
CASE
        WHEN PTEMP.CUSTOMER_CODE IS NOT NULL THEN
        PTCUS.CUSTOMER_FULL_NAME ELSE PTVEN.VENDOR_FULL_NAME
END ELSE
CASE
        WHEN FID.FIRST_ITEMS = 'CUSTOMER' THEN
        FID.CUSTOMER_NAME ELSE FID.VENDOR_NAME
    END
    END AS CUSTOMER_VENDOR_NAME,
    PHD.PHYSICAL_REMARK
FROM
    (
    SELECT
        ROW_NUMBER ( ) over ( ORDER BY PHD.PHYSICAL_NO DESC, FID_ID.FI_DOC_NO, PHDTEMP.PHD_TEMP_DATA_ID DESC ) RECORD_NUMBER,
        PHD.PHYSICAL_ID,
        PHD.PHYSICAL_NO,
        FID_ID.FI_DOC_ID,
        FID_ID.FI_DOC_NO,
        PHDTEMP.PHD_TEMP_DATA_ID
    FROM
        (
        SELECT DISTINCT
            PHD.PHYSICAL_ID,
            PHD.PHYSICAL_NO,
            FID_ID.FI_DOC_ID,
            PHDTEMP.PHD_TEMP_DATA_ID
        FROM
            CTE_PHD_DETAIL PHD
            LEFT JOIN (
            SELECT
                t1.FI_DOC_ID,
                t1.PHYSICAL_ID,
                t2.fi_Doc_no,
                t2.fi_doc_year
            FROM
                PHD_FID_RELATE_DOC t1
                INNER JOIN FID_SAP_DETAIL t2 ON t1.FI_DOC_ID = t2.FI_DOC_ID UNION ALL
            SELECT
                t1.FI_DOC_ID,
                t1.PHYSICAL_ID,
                t2.fi_Doc_no,
                t2.fi_doc_year
            FROM
                FID_SAP_PHYSICAL_HISTORY t1
                INNER JOIN FID_SAP_DETAIL t2 ON t1.FI_DOC_ID = t2.FI_DOC_ID
            ) FID_ID ON FID_ID.PHYSICAL_ID = PHD.PHYSICAL_ID
            LEFT JOIN PHD_TEMP_DATA PHDTEMP ON PHD.PHYSICAL_ID = PHDTEMP.PHYSICAL_ID
            AND FID_ID.FI_DOC_ID
            IS NULL LEFT JOIN PHD_STATUS_LOGS PHDSL ON PHD.PHYSICAL_ID = PHDSL.PHYSICAL_ID
            LEFT JOIN PHD_TEMP_DOCTITLE PHDT ON PHD.PHYSICAL_ID = PHDT.PHYSICAL_ID
            LEFT JOIN CNT_DETAIL CNT ON PHD.CURRENT_CONTAINER_ID = CNT.CONTAINER_ID
            LEFT JOIN VEW_UNT_DETAIL CNTU ON CNT.CURRENT_UNIT_ID = CNTU.UNIT_ID
            LEFT JOIN LOC_DETAIL LOC ON CNT.LOCATION_ID = LOC.LOCATION_ID
            LEFT JOIN COM_DETAIL COM_PHD ON PHD.COMPANY_ID = COM_PHD.COMPANY_ID
            LEFT JOIN FID_SAP_DETAIL FID ON FID_ID.FI_DOC_ID = FID.FI_DOC_ID
            LEFT JOIN COM_DETAIL COM ON FID.COMPANY_CODE = COM.COMPANY_CODE
            LEFT JOIN FID_SAP_CUSTOMER FCUS ON FID.FI_DOC_ID = FCUS.FI_DOC_ID
            LEFT JOIN FID_SAP_VENDOR FVEN ON FID.FI_DOC_ID = FVEN.FI_DOC_ID
            LEFT JOIN FID_SAP_CLEARING_DOC CLR ON FID.FI_DOC_ID = CLR.FI_DOC_ID
            LEFT JOIN FID_SAP_PO PO ON FID.FI_DOC_ID = PO.FI_DOC_ID
            LEFT JOIN FID_SAP_STATUS FIDS ON FID.WORKFLOW_STATUS_CODE = FIDS.WORKFLOW_STATUS_CODE
            LEFT JOIN MAS_VENDOR PTVEN ON PHDTEMP.VENDOR_CODE = PTVEN.VENDOR_CODE
            AND COM_PHD.SERVER_ID = COM_PHD.SERVER_ID
            LEFT JOIN MAS_CUSTOMER PTCUS ON PHDTEMP.CUSTOMER_CODE = PTCUS.CUSTOMER_CODE
            AND PTCUS.SERVER_ID = COM_PHD.SERVER_ID
        WHERE
            PHD.PHYSICAL_NO IS NOT NULL
            AND (
                (
                    (
                        ( PHD.PHYSICAL_DOC_TYPE = 1 AND iPHYSICAL_DOC_TYPE = 1 )
                        OR ( ( PHD.PHYSICAL_DOC_TYPE = 2 OR PHD.PHYSICAL_DOC_TYPE = 4 ) AND iPHYSICAL_DOC_TYPE = 2 )
                    )
                    AND iPHYSICAL_DOC_TYPE_NONSAP IS NULL
                )
                OR ( PHD.PHYSICAL_DOC_TYPE = iPHYSICAL_DOC_TYPE_NONSAP AND iPHYSICAL_DOC_TYPE_NONSAP IS NOT NULL )
            )
            AND (
                sREFERENCE_NO IS NULL
                OR (
                    UPPER( PHD.REQUESTED_NO ) LIKE '%' || UPPER( sREFERENCE_NO ) || '%'
                    OR UPPER( PHD.TEXT_FILE_NAME ) LIKE '%' || UPPER( sREFERENCE_NO ) || '%'
                )
            )
            AND ( sPHYSICAL_NO IS NULL OR ( PHD.PHYSICAL_NO = sPHYSICAL_NO ) )
            AND ( iLOCATION_ID IS NULL OR ( LOC.LOCATION_ID = iLOCATION_ID ) )
            AND ( iPHYSICAL_STATUS_ID IS NULL OR ( PHD.PHYSICAL_STATUS_ID = iPHYSICAL_STATUS_ID ) )
            AND ( iCONTAINER_ID IS NULL OR ( CNT.CONTAINER_ID = iCONTAINER_ID AND CNT.CONTAINER_TYPE = 2 ) )
            AND (
                sCONTAINER_NUMBER IS NULL
                OR ( UPPER( CNT.CONTAINER_NUMBER ) LIKE '%' || UPPER( sCONTAINER_NUMBER ) || '%' AND CNT.CONTAINER_TYPE = 1 )
            )
            AND ( TRUNC( dREGISTERED_DATE_FROM ) IS NULL OR ( TRUNC( PHD.REGISTERED_DATE ) >= TRUNC( dREGISTERED_DATE_FROM ) ) )
            AND ( TRUNC( dREGISTERED_DATE_TO ) IS NULL OR ( TRUNC( PHD.REGISTERED_DATE ) <= TRUNC( dREGISTERED_DATE_TO ) ) )
            AND ( ( PHD.DRAFT_FLAG IS NULL AND sDRAFT_FLAG IS NULL ) OR ( PHD.DRAFT_FLAG = sDRAFT_FLAG ) ) --AND (PHD.CANCEL_FLAG              IS NULL
--OR (PHD.CANCEL_FLAG                = sCANCEL_FLAG))
            AND ( sPHYSICAL_REMARK IS NULL OR ( UPPER( PHD.PHYSICAL_REMARK ) LIKE '%' || UPPER( sPHYSICAL_REMARK ) || '%' ) )
            AND ( sPHYSICAL_DOC_TITLE IS NULL OR ( UPPER( PHDT.PHYSICAL_DOC_TITLE ) LIKE '%' || UPPER( sPHYSICAL_DOC_TITLE ) || '%' ) )
            AND ( iCOMPANY_ID IS NULL OR ( COM.COMPANY_ID = iCOMPANY_ID OR COM_PHD.COMPANY_ID = iCOMPANY_ID ) )
            AND ( iPHY_YEAR IS NULL OR ( PHD.PHYSICAL_YEAR = iPHY_YEAR ) )
            AND ( iFI_DOC_YEAR IS NULL OR ( FID_ID.FI_DOC_YEAR = iFI_DOC_YEAR OR FID_ID.FI_DOC_ID IS NULL ) ) /*    AND (
                sSEND_TO IS NULL
                OR ( PHDSL.SEND_TO LIKE '%' || sSEND_TO || '%' OR LOWER( PHDSL.SEND_TO ) LIKE '%' || sSEND_TO || '%' )
            )*/
            AND (
                sSEND_TO IS NULL
                OR (
                    iPHYSICAL_STATUS_ID = 2
                    AND EXISTS (
                    SELECT
                        PDSENT.SEND_TO
                    FROM
                        ( SELECT SEND_TO, PHYSICAL_ID FROM PHD_STATUS_LOGS T1 WHERE T1.PHYSICAL_STATUS_ID = 2 ORDER BY CREATE_DATE DESC ) PDSENT
                    WHERE
                        ROWNUM = 1
                        AND PDSENT.PHYSICAL_ID = PHD.PHYSICAL_ID
                        AND LOWER( PDSENT.SEND_TO ) LIKE '%' || LOWER( sSEND_TO ) || '%'
                    )
                )
            )
            AND ( sFI_DOC_NO_FROM IS NULL OR ( FID.FI_DOC_NO >= sFI_DOC_NO_FROM ) )
            AND ( sFI_DOC_NO_TO IS NULL OR ( FID.FI_DOC_NO <= sFI_DOC_NO_TO ) )
            AND ( iCOMPANY_ID IS NULL OR ( COM.COMPANY_ID = iCOMPANY_ID OR COM_PHD.COMPANY_ID = iCOMPANY_ID ) )
            AND ( iFI_DOC_YEAR IS NULL OR ( PHD.PHYSICAL_YEAR = iFI_DOC_YEAR OR FID.FI_DOC_YEAR = iFI_DOC_YEAR ) ) --AND ( sFI_DOC_TYPE              IS NULL
--OR ( UPPER(T1.FI_DOC_TYPE)       = UPPER(sFI_DOC_TYPE) ) )
            AND ( sIS_ASSET IS NULL OR ( sIS_ASSET IS NOT NULL AND FID.IS_ASSET IS NOT NULL ) )
            AND ( sWORKFLOW_STATUS_CODE IS NULL OR ( FID.WORKFLOW_STATUS_CODE = sWORKFLOW_STATUS_CODE ) )
            AND ( sPO_NO IS NULL OR ( UPPER( PO.PO_NO ) = UPPER( sPO_NO ) ) )
            AND (
                TRUNC( dPOSTING_DATE_FROM ) IS NULL
                OR ( TRUNC( FID.POSTING_DATE ) >= TRUNC( dPOSTING_DATE_FROM ) OR TRUNC( PHDTEMP.CREATE_DATE ) <= TRUNC( dPOSTING_DATE_TO ) )
            )
            AND (
                TRUNC( dPOSTING_DATE_TO ) IS NULL
                OR ( TRUNC( FID.POSTING_DATE ) <= TRUNC( dPOSTING_DATE_TO ) OR TRUNC( PHDTEMP.CREATE_DATE ) >= TRUNC( dPOSTING_DATE_FROM ) )
            )
            AND ( sCLEARING_DOC_NO IS NULL OR ( UPPER( CLR.CLEARING_DOC_NO ) LIKE '%' || UPPER( sCLEARING_DOC_NO ) || '%' ) )
            AND ( iCLEARING_DOC_YEAR IS NULL OR ( CLR.CLEARING_DOC_YEAR = iCLEARING_DOC_YEAR ) )
            AND ( dCLEARING_DOC_DATE IS NULL OR ( CLR.CLEARING_DATE = dCLEARING_DOC_DATE ) ) --AND ( sPAYMENT_BLOCK      IS NULL
-- OR ( UPPER(FID.PAYMENT_BLOCK) LIKE '%'
--   || UPPER(sPAYMENT_BLOCK)
--  || '%' ) )
            AND ( sFI_DOC_REFNO IS NULL OR ( UPPER( FID.FI_DOC_REFNO ) LIKE '%' || UPPER( sFI_DOC_REFNO ) || '%' ) )
            AND ( ( sSAP_REVERSE_FLAG IS NULL AND ( FID.SAP_REVERSE_FLAG IS NULL OR FID.FI_DOC_ID IS NULL ) ) OR ( sSAP_REVERSE_FLAG IS NOT NULL ) )
            AND ( iAMOUNT_INC_VAT IS NULL OR ( FID.AMOUNT_INC_VAT = iAMOUNT_INC_VAT OR PHDTEMP.AMOUNT_INC_VAT = iAMOUNT_INC_VAT ) )
            AND (
                sCUSTOMER_NAME IS NULL
                OR UPPER( PTCUS.CUSTOMER_FULL_NAME ) LIKE '%' || UPPER( sCUSTOMER_NAME ) || '%'
                OR UPPER( FCUS.CUSTOMER_CODE || ' ' || FCUS.CUSTOMER_NAME ) LIKE '%' || UPPER( sCUSTOMER_NAME ) || '%'
            )
            AND (
                sVENDOR_NAME IS NULL
                OR UPPER( PTVEN.VENDOR_FULL_NAME ) LIKE '%' || UPPER( sVENDOR_NAME ) || '%'
                OR UPPER( FVEN.VENDOR_CODE || ' ' || FVEN.VENDOR_NAME ) LIKE '%' || UPPER( sVENDOR_NAME ) || '%'
            ) --AND FID_ID.FI_DOC_ID IS NULL AND PHDTEMP.PHD_TEMP_DATA_ID IS NOT NULL
        ) PHD
        LEFT JOIN FID_SAP_DETAIL FID_ID ON FID_ID.FI_DOC_ID = PHD.FI_DOC_ID
        LEFT JOIN PHD_TEMP_DATA PHDTEMP ON PHD.PHD_TEMP_DATA_ID = PHDTEMP.PHD_TEMP_DATA_ID
        AND PHD.FI_DOC_ID IS NULL
    ) PHD_ITEMS
    INNER JOIN CTE_PHD_DETAIL PHD ON PHD_ITEMS.PHYSICAL_ID = PHD.PHYSICAL_ID
    LEFT JOIN PHD_STATUS PHDS ON PHD.PHYSICAL_STATUS_ID = PHDS.PHYSICAL_STATUS_ID
    LEFT JOIN CNT_DETAIL CNT ON PHD.CURRENT_CONTAINER_ID = CNT.CONTAINER_ID
    LEFT JOIN VEW_UNT_DETAIL CNTU ON CNT.CURRENT_UNIT_ID = CNTU.UNIT_ID
    LEFT JOIN LOC_DETAIL LOC ON CNT.LOCATION_ID = LOC.LOCATION_ID
    LEFT JOIN COM_DETAIL CCOM ON CNT.CURRENT_COMPANY_ID = CCOM.COMPANY_ID
    LEFT JOIN COM_DETAIL COM_PHD ON PHD.COMPANY_ID = COM_PHD.COMPANY_ID
    LEFT JOIN FID_SAP_DETAIL FID ON PHD_ITEMS.FI_DOC_ID = FID.FI_DOC_ID
    LEFT JOIN FID_SAP_STATUS FIDS ON FID.WORKFLOW_STATUS_CODE = FIDS.WORKFLOW_STATUS_CODE
    LEFT JOIN COM_DETAIL COM_FID ON COM_FID.COMPANY_CODE = FID.COMPANY_CODE
    LEFT JOIN PHD_TEMP_DATA PTEMP ON PHD_ITEMS.PHD_TEMP_DATA_ID = PTEMP.PHD_TEMP_DATA_ID
    LEFT JOIN MAS_VENDOR PTVEN ON PTEMP.VENDOR_CODE = PTVEN.VENDOR_CODE
    AND PHD_ITEMS.FI_DOC_ID IS NULL
    AND PTVEN.SERVER_ID = COM_PHD.SERVER_ID
    LEFT JOIN MAS_CUSTOMER PTCUS ON PTEMP.CUSTOMER_CODE = PTCUS.CUSTOMER_CODE
    AND PHD_ITEMS.FI_DOC_ID IS NULL
    AND PTCUS.SERVER_ID = COM_PHD.SERVER_ID
WHERE
    PHD_ITEMS.RECORD_NUMBER BETWEEN FIRST_INDEX
    AND LAST_INDEX
ORDER BY
    RECORD_NUMBER;
END;

Esta consulta leva cerca de 1:30 minutos antes de emitir o resultado da consulta de ~4000 registros. É difícil extrair o plano de execução para esta consulta longa. Que sugestão ou estratégia ou devo fazer algo para obter o plano?

Na minha opinião, atualmente tenho 3 opções:

Consulta dinâmica

A primeira opção é escalar o procedimento para ser uma declaração ORM com alguma declaração if para compor a declaração adequada com junção interna em vez de junção externa esquerda (se o parâmetro for nulo, sem junção). Acho que essa opção é inteligente quando penso sobre isso à primeira vista. No entanto, pode levar muito tempo para eu realizar a tarefa.

Visão materializada

Uma das opções que penso é puxar as cláusulas left join para serem reescritas como Materialized View. Infelizmente, esse procedimento de armazenamento está envolvido na aplicação, então a Materialized View será atualizada frequentemente quando combinada com ON COMMITou ON STATEMENT. Essa opção é considerada perigosa para deadlock ou desempenho ruim?

Remova alguns critérios de pesquisa (parâmetros de procedimento armazenado)

Suponha que eu possa remover alguns critérios, isso significa que posso remover algumas cláusulas left outer join. No entanto, essa opção não é possível depois que eu discuto com o colega de equipe.

Como devo fazer para lidar com o problema de desempenho ou quais outras estratégias ou opções eu tenho? Quaisquer conselhos são bem-vindos.

Recentemente desabilitamos uma conta SQL em um dos nossos servidores de produção a pedido do proprietário do aplicativo, conta aparentemente não mais usada. Desde a desabilitação, vimos falhas de login no log de erros SQL. Veja a imagem abaixo.

A equipe do aplicativo não consegue localizar o processo usando-o e me enviou de volta. Não consigo ver nada em sys.sysprocesses sys.dm_exec_sessions sys.dm_exec_connections

sp_whosisactive também não captura nenhum código em execução. Sinto que é quase um processo de heartbeat do servidor de aplicativo que verifica a cada dois minutos para garantir que o servidor esteja ativo.

Há outras maneiras de capturar a atividade, o profiler não é uma opção rápida e suja, pois este servidor é muito temperamental. Estamos investigando os logs do lado do aplicativo também.

Editado:

Preciso remover restrições de chave estrangeira de algumas tabelas do PostgreSQL. No entanto, essas tabelas são muito usadas e contêm um volume significativo de dados . Algumas são particionadas , enquanto outras não .

Quando tento alterar a tabela para remover a chave estrangeira, isso causa um bloqueio , que interrompe as operações em andamento ou é bloqueado devido a alguma operação de gravação, a menos que programemos um tempo de inatividade.

Existe uma maneira de remover restrições de chave estrangeira de uma tabela no PostgreSQL sem causar bloqueios ou impactar operações em andamento?

Quaisquer sugestões ou melhores práticas serão apreciadas.

PS: Acredito que sem bloqueios isso poderia causar problemas com consistência de dados, integridade e controle de simultaneidade.

Valor de string incorreto: \'\\xCD\\xCF\\xD7\\xA7\\xC9\\xFA...\\

Tudo no meu servidor deve estar utf8mb4, utf8mb4_uca1400_ai_cientão vamos analisar tudo.

Meu my.iniarquivo no Windows 10 para MariaDB 11.4.2:

[mysql]
default-character-set = utf8mb4

[mysqld]
datadir=C:/WAMP/MariaDB/data
port=3306
innodb_buffer_pool_size=50M
character-set-server=utf8mb4
character-set-client-handshake = FALSE
collation-server = utf8mb4_uca1400_ai_ci
general-log
general-log-file=C:/WAMP/MariaDB/logs/queries.log

[client]
default-character-set = utf8mb4
port=3306
plugin-dir=C:\WAMP\MariaDB/lib/plugin

A consulta local que uso para verificar conjuntos de caracteres e comparações:

SELECT LOWER(VARIABLE_NAME) AS `variable_name`, 
       GLOBAL_VALUE AS `global_value`, 
       DEFAULT_VALUE AS `default_value` 
FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES 
WHERE VARIABLE_NAME LIKE '%CHARACTER_SET_%' 
   OR VARIABLE_NAME LIKE '%COLLATION_%' 
ORDER BY `VARIABLE_NAME` ASC;

Toda vez que faço uma migração de servidor, executo o seguinte:

SET CHARACTER SET 'utf8';
SET collation_connection = 'utf8mb4_uca1400_ai_ci';
SET GLOBAL collation_connection = 'utf8mb4_uca1400_ai_ci';
SET GLOBAL collation_database = 'utf8mb4_uca1400_ai_ci';
SET GLOBAL collation_server = 'utf8mb4_uca1400_ai_ci';

Os SHOW CREATE DATABASEretornos em utf8mb4e utf8mb4_uca1400_ai_ci.

A coluna associada ao erro usa utf8mb4_uca1400_ai_ci.

Isto é para uma mensagem de e-mail com o assunto "[标题]Satisfaça todas as suas fantasias sobre a turnê da mão de Xianxia." 🙄︀ Acho que algo se perdeu na tradução. Não tenho tudo no máximo em conjuntos de caracteres e agrupamentos, embora eu ache que já cobri a maioria das coisas. Então...

O que ainda não defini corretamente para conjuntos de caracteres e/ou ordenação e como posso garantir que tudo o que é possível esteja definido como utf8mb4e utf8mb4_uca1400_ai_ci?

Se você é bem lido sobre particionamento, então você estará completamente ciente de que particionamento não é um recurso de desempenho para índices rowstore. No entanto, sempre que vejo argumentos feitos para isso, como feito de forma mais convincente aqui por Gail Shaw , os argumentos dependem da comparação de um índice não particionado com um índice particionado onde ambos os índices têm a chave de particionamento como sua chave principal. Argumentos como o de Gail funcionam com base no fato de que há uma diferença mínima de desempenho entre a eliminação de partições e a busca por um índice não particionado com sua suposta chave de particionamento como a primeira chave.

Esses argumentos são convincentes, mas e os casos em que a chave de particionamento não é a primeira chave do seu índice particionado? Nesses casos, obtemos os benefícios da eliminação de partições e nossa chave principal agora é usada para pesquisar um índice consideravelmente menor do que teríamos se deixássemos o mesmo índice não particionado. Isso parece que deve dar uma melhoria de desempenho em relação a deixar o mesmo índice não particionado.

Em resumo, minha pergunta é esta: Há benefícios de desempenho em particionar um índice que tem várias chaves se a coluna de particionamento não for a primeira chave do índice? Tudo o que deve ser necessário para responder a essa pergunta é um exemplo acompanhado de alguma teoria explicando por que os resultados são como são.

Eu costumava atualizar uma tabela muito grande usando UPDATEconsultas, mas elas estavam demorando muito para executar. Para melhorar o desempenho, mudei para usar a CREATE TABLEabordagem e adicionar índices para atualizar a tabela. Essa abordagem aumentou significativamente minha velocidade de execução de consultas, mas quero entender sua escalabilidade e limitações.

Especificações do servidor:

• Versão do PostgreSQL: 15.6
• RAM: 32 GB
• Núcleos: 16
• Espaço em disco: SSD 250 GB (50% grátis)
• SO: Linux Ubuntu 22.04

Configuração do PostgreSQL:

max_connections = 200
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
huge_pages = try
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 16
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4

Detalhes da tabela:

Consulta:

BEGIN;
ALTER TABLE source_switchdata_tmp_details RENAME TO source_switchdata_tmp_details_og;
CREATE TABLE source_switchdata_tmp_details AS
SELECT DISTINCT ON (A.uniqueid) A.transactiondate,
        A.cycles,
        A.transactionamount,
        A.bcid,
        A.bcname,
        A.username,
        A.terminalid,
        A.uidauthcode,
        A.itc,
        A.transactiondetails,
        A.deststan,
        A.sourcestan,
        A.hostresponsecode,
        A.institutionid,
        A.acquirer,
        A.bcrefid,
        A.cardno,
        A.rrn,
        A.transactiontype,
        A.filename,
        A.cardnotrim,
        A.uniqueid,
        A.transactiondatetime,
        A.transactionstatus,
        A.overall_probable_status,
        A.recon_created_date,
        A.priority_no,
        A.recon_key_priority_1_1_to_2,
        A.recon_key_priority_1_1_to_3,
        A.recon_key_priority_2_1_to_2,
        A.recon_key_priority_2_1_to_3,
        A.process_status,
        A.reconciliation_date_time,
        CURRENT_TIMESTAMP AS recon_updated_date,
        CASE
                WHEN C.recon_key_priority_1_2_to_1 IS NOT NULL THEN 'Reconciled'
                ELSE 'Not Reconciled'
        END AS recon_status_1_to_2,
        CASE
                WHEN D.recon_key_priority_1_3_to_1 IS NOT NULL THEN 'Reconciled'
                WHEN D.recon_key_priority_2_3_to_1 IS NOT NULL THEN 'Reconciled'
                ELSE 'Not Reconciled'
        END AS recon_status_1_to_3,
        CASE
                WHEN (C.recon_key_priority_1_2_to_1 IS NOT NULL AND D.recon_key_priority_1_3_to_1 IS NOT NULL) THEN 'Reconciled'
                WHEN (D.recon_key_priority_2_3_to_1 IS NOT NULL) THEN 'Reconciled'
                ELSE 'Not Reconciled'
        END AS overall_recon_status
FROM source_switchdata_tmp_details_og A
        LEFT JOIN source_aepscbsdata_tmp_details C ON (A.recon_key_priority_1_1_to_2 = C.recon_key_priority_1_2_to_1)
        LEFT JOIN source_npcidata_tmp_details D 
        ON (A.recon_key_priority_1_1_to_3 = D.recon_key_priority_1_3_to_1) 
        OR (A.recon_key_priority_2_1_to_3 = D.recon_key_priority_2_3_to_1);
DROP TABLE source_switchdata_tmp_details_og;
COMMIT;

Restrições e índices exclusivos:

A.uniqueid = Primary key and Index
A.recon_key_priority_1_1_to_3 = Index
A.recon_key_priority_1_1_to_2 = Index
D.recon_key_priority_1_3_to_1 = Index
A.recon_key_priority_2_1_to_3 = Index
D.recon_key_priority_2_3_to_1 = Index

Questões:

1. Atualmente, estou executando a consulta acima para 180 milhões de linhas (60M + 60M + 60M). No futuro, posso precisar executar essa consulta para 1 bilhão de linhas. Essa abordagem será escalável para 1 bilhão de linhas? Podemos aumentar as especificações do servidor se necessário, mas essa abordagem será viável? Essencialmente, se eu fosse recriar a tabela para 300 milhões de linhas ou mesmo 1 bilhão de linhas, isso seria prático?
2. Minha equipe sugere atualizar os dados em blocos de 1 milhão de linhas. Essa abordagem é melhor do que a atual?
3. A consulta atualmente leva cerca de 20 minutos, o que é aceitável. Se o tamanho dos dados aumentar, de quais gargalos, como gargalos de E/S, devo estar ciente para garantir que o tempo da consulta seja dimensionado proporcionalmente sem ficar preso?
4. Quais são as limitações da abordagem atual? E o que posso fazer para evitar tais limitações?

Quaisquer insights ou otimizações seriam muito apreciados. Obrigado!

Estou trabalhando para um consultório médico particular, e estamos planejando instalar um sistema de agendamento de consultas por meio de nossa página da web. O sistema de consultas é um aplicativo PHP que armazena seus registros em um banco de dados SQL que pode estar no próprio servidor da web ou em um servidor remoto.

Devido ao caso de uso, o banco de dados conterá informações pessoais muito confidenciais* que devem ser mantidas em segredo do público.

Reforçar o acesso ao banco de dados é um ponto, mas acho que uma importante brecha de entrada nos dados seria o próprio aplicativo PHP.

Existe uma maneira recomendada de isolar os dados sensíveis do acesso público? Eu poderia imaginar um sistema como o seguinte:

público -> acesso via aplicativo PHP em servidor acessível publicamente -> banco de dados SQL com entradas confidenciais mascaradas <- sincronização via túnel ssh reforçado -> banco de dados SQL privado com entradas completas <- acesso via aplicativo PHP em servidor não público -> funcionários

Dessa forma, mesmo que o aplicativo PHP expusesse dados ao público, os dados sensíveis seriam protegidos. Além disso, obter acesso ao próprio banco de dados SQL disponível publicamente seria menos crítico.

O mapeamento entre os dois bancos de dados seria por meio de um código hash exclusivo para cada uma das entradas. Efetivamente, isso é apenas uma pseudonimização e não uma anonimização. Mas ainda assim, é uma camada adicional de segurança.

Só não sei se há uma maneira pronta para tal implementação. Basicamente, qualquer alteração no banco de dados SQL com entradas sensíveis mascaradas deve disparar uma sincronização com um mascaramento subsequente (já que entradas de clientes feitas de público precisam ser mascaradas).

Existe uma implementação melhor? Eu ficaria muito feliz com qualquer sugestão.

* Os pacientes inseririam dados sensíveis no banco de dados público, e precisamos desses dados para fazer nosso trabalho (precisamos de nomes, contato e algumas informações médicas). Mas assim que esses dados forem inseridos, eles devem ser transferidos para o banco de dados privado e ficar indisponíveis no banco de dados público.

Mas ainda assim, não quero fuzz com o aplicativo PHP que é pré-fabricado. Mascarar os dados no banco de dados público (por exemplo, com *s) faria o aplicativo PHP funcionar normalmente