All perguntas(dba)

Eu uso o script a seguir para fazer um .bak de um banco de dados de produção e salvá-lo em um servidor de teste onde ele pode ser restaurado se necessário. O script não declara para compactar o arquivo. Executando o SQL Server 2019 Enterprise.

BACKUP DATABASE [PINK] 
    TO DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH NOFORMAT, INIT,  NAME = N'PINK', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO

DECLARE @backupSetId AS int

SELECT @backupSetId = position 
FROM msdb..backupset 
WHERE database_name = N'PINK' 
  AND backup_set_id = (SELECT MAX(backup_set_id) 
                       FROM msdb..backupset 
                       WHERE database_name = N'PINK')

IF @backupSetId IS NULL
BEGIN
    RAISERROR(N'Verify failed. Backup information for database ''PINK'' not found.', 16, 1) 
END

RESTORE VERIFYONLY 
    FROM DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

Ele .bakvai para o servidor de teste e, dependendo de onde você olha no Explorer, ele tem 162 GB ou 154 GB:

Para economizar espaço, tentei compactar .bakadicionando COMPRESSIONao script:

BACKUP DATABASE [PINK] 
    TO DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH NOFORMAT, INIT, NAME = N'PINK', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM
GO

DECLARE @backupSetId AS int

SELECT @backupSetId = position 
FROM msdb..backupset 
WHERE database_name = N'PINK' 
  AND backup_set_id = (SELECT MAX(backup_set_id) 
                       FROM msdb..backupset 
                       WHERE database_name = N'PINK')

IF @backupSetId IS NULL
BEGIN
    RAISERROR(N'Verify failed. Backup information for database ''PINK'' not found.', 16, 1) 
END

RESTORE VERIFYONLY 
    FROM DISK = N'\\TestServer\Transfer\PINK.bak' 
    WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

Após executar com o COMMPRESSIONcomando incluído, ele caiu no mesmo tamanho no servidor de teste. Eu verifiquei os arquivos .ndfe .mdfe eles totalizam 770 GB ou 732 GB, dependendo de onde você olha. Além disso, ele inclui cerca de 23 GB de informações de índice.

Isso .bakjá está compactado, mesmo que eu não tenha dito isso?

Estou corrigindo alguns dados incorretos e estou procurando uma maneira mais segura de atualizar uma determinada coluna quando o valor não existe mais na tabela relacionada.

Dê uma olhada no exemplo a seguir ( violino aqui ):

-- SET sql_mode = '';

create table if not exists table1
(
    id                           int auto_increment primary key,
    table2_id int
);

create table if not exists table2
(
    id                           int auto_increment primary key
);


INSERT INTO table1 VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
INSERT INTO table2 VALUES(1), (2);

SELECT * FROM table1;
SELECT * FROM table2;

SELECT * FROM table1 LEFT JOIN table2 ON table1.table2_id = table2.id;

a última consulta acima retornará apenas 2 linhas porque table2não tem os IDs: 3, 4, 5. Então preciso escrever uma ATUALIZAÇÃO para a tabela1 para definir os IDs: 3, 4, 5 para NULL.

Posso ter algumas ideias?

Olá, tenho o SQL 2016 com AlwaysOn Availability. Eu deletei uma tabela no nó "ativo". Posso recuperar a tabela do outro nó ou a remoção é "sincronizada" automaticamente e, portanto, a tabela é removida no outro nó também?

Estou sofrendo com lentidão no mysqldump/mariadb-dump.

Descobri que desabilitar autocommit, unique_checks and foreign_key_checksaumenta o desempenho da importação.

Eu faço isso adicionando dump.sqlantes

SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;

e anexando

COMMIT;SET unique_checks=1;SET foreign_key_checks=1;

até o fim disso.

Acrescentar é trivial, mas ao acrescentar preciso fazer um acordo entre tempo ou espaço consumido.

Prependendo com

sed -i '0,/^/s//SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;/' dump.sql

pode levar muito tempo para despejos grandes, mas cria um único arquivo.

cat <(echo "SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;") dump.sql <(echo "COMMIT;SET unique_checks=1;SET foreign_key_checks=1;") > new-dump.sql"

É rápido, mas cria outro arquivo de despejo e, portanto, usa muito espaço (mesmo que seja apenas temporário)

É possível instruir mysqldump/maridb-dumppara acrescentar e acrescentar um texto personalizado ao dump quando ele estiver sendo criado?

Estou executando o PostgreSQL 13.15 em um contêiner docker local, com versões pg_dump/pg_restore correspondentes. Ao despejar e restaurar o banco de dados, uma das funções personalizadas que estava presente anteriormente parece estar faltando quando se trata de criar a tabela. A tabela de interesse é ignorada durante seu COPYcomando. Após a restauração do banco de dados, a mesma função com a mesma entrada pode ser executada com sucesso sem problemas.

Reduzi o pg_dump de texto simples para o exemplo reproduzível mínimo. Aqui está o erro que ocorre durante a restauração:

CREATE TABLE
ALTER TABLE
COMMENT
WARNING:  Value: Equipment
WARNING:  TYPE: text
ERROR:  function try_cast_jsonb(text) does not exist
LINE 1: SELECT try_cast_jsonb(raw_text)
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT try_cast_jsonb(raw_text)
CONTEXT:  PL/pgSQL function public.content_aware_tsvector(regconfig,text[],character) line 18 at assignment
COPY searchable_items, line 1: "84      name    {}      {}      {Equipment}     A       app_user"

Aqui está a execução da função após a conclusão da restauração no shell psql:

mydb=# SELECT try_cast_jsonb('Equipment');
 try_cast_jsonb 
----------------

A função retorna nulo se a string não puder ser convertida em jsonb.

O dump do banco de dados foi executado com o seguinte comando:

pg_dump --host localhost --port 1487 --dbname mydb --clean --if-exists > backuptest.sql

A restauração foi realizada da seguinte forma em um banco de dados limpo:

pg_restore --host localhost --port 1487 --dbname mydb --clean --if-exists --no-owner backuptest.sql

Isso ocorre tanto no formato simples quanto no formato C.

Aqui está o pg_dump em texto simples:

--
-- PostgreSQL database dump
--

-- Dumped from database version 13.15 (Debian 13.15-1.pgdg120+1)
-- Dumped by pg_dump version 13.15 (Debian 13.15-1.pgdg120+1)

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;

-- DROP ALL CONSTRAINTS, INDEXES, FUNCTIONS, EXTENSIONS if existing
DROP FUNCTION IF EXISTS public.try_cast_jsonb(_text text);

DROP FUNCTION IF EXISTS public.content_aware_tsvector(_lang text, _raw_text text, _tsweight character);
DROP FUNCTION IF EXISTS public.content_aware_tsvector(_lang regconfig, _raw_text_arr text[], _tsweight character);
DROP EXTENSION IF EXISTS pg_trgm;
DROP EXTENSION IF EXISTS btree_gist;
--
-- Name: btree_gist; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS btree_gist WITH SCHEMA public;


--
-- Name: EXTENSION btree_gist; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION btree_gist IS 'support for indexing common datatypes in GiST';


--
-- Name: pg_trgm; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;


--
-- Name: EXTENSION pg_trgm; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';


--
-- Name: try_cast_jsonb(text); Type: FUNCTION; Schema: public; Owner: app_user
--

CREATE FUNCTION public.try_cast_jsonb(_text text) RETURNS jsonb
    LANGUAGE plpgsql STABLE
    AS $$
        DECLARE
            trimmed_text text;
            casted_json jsonb;
        BEGIN
            -- Remove leading whitespace
            trimmed_text := ltrim(_text);

            -- Check if the string doesn't start with '{' or '['
            IF NOT (left(trimmed_text, 1) = '{' OR left(trimmed_text, 1) = '[') THEN
                RETURN NULL;
            END IF;

            BEGIN
                SELECT trimmed_text::jsonb INTO casted_json;
            EXCEPTION WHEN OTHERS THEN
                RETURN NULL;
        END;

        RETURN casted_json;
        END;
    $$;


ALTER FUNCTION public.try_cast_jsonb(_text text) OWNER TO app_user;


--
-- Name: content_aware_tsvector(regconfig, text[], character); Type: FUNCTION; Schema: public; Owner: app_user
--

CREATE FUNCTION public.content_aware_tsvector(_lang regconfig, _raw_text_arr text[], _tsweight character DEFAULT NULL::bpchar) RETURNS tsvector
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
        DECLARE
            vec tsvector;
            i integer;
            raw_text text;
            json_text jsonb;
            debug text;
        BEGIN
            vec := '';

            FOR i IN array_lower(_raw_text_arr, 1) .. array_upper(_raw_text_arr, 1) LOOP

                raw_text := COALESCE(_raw_text_arr[i], '');

                RAISE WARNING 'Value: %', raw_text;
                SELECT pg_typeof(raw_text) INTO debug; 
                RAISE WARNING 'TYPE: %', debug;
                json_text := try_cast_jsonb(raw_text);

                IF json_text IS NOT NULL THEN
                    -- JSON aware tsvector only indexes contents and not structure.
                    vec := vec || jsonb_to_tsvector(_lang, json_text, '["string", "numeric", "boolean"]'::jsonb);
                ELSEIF raw_text ~ '^[a-fA-F0-9]{8}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{12}$' THEN
                    -- Makes UUIDs a single element in the index for only exact matches.
                    vec := vec || array_to_tsvector(ARRAY[raw_text]);
                ELSE
                    vec := vec || to_tsvector(_lang, raw_text);
                END IF;

            END LOOP;

            IF _tsweight IS NOT NULL THEN
                RETURN setweight(vec, _tsweight::"char");
            END IF;

            RETURN vec;
        END;
    $_$;


ALTER FUNCTION public.content_aware_tsvector(_lang regconfig, _raw_text_arr text[], _tsweight character) OWNER TO app_user;

--
-- Name: content_aware_tsvector(text, text, character); Type: FUNCTION; Schema: public; Owner: app_user
--

CREATE FUNCTION public.content_aware_tsvector(_lang text, _raw_text text, _tsweight character DEFAULT NULL::bpchar) RETURNS tsvector
    LANGUAGE plpgsql IMMUTABLE
    AS $$
            DECLARE
                raw_json json;
                vec tsvector;
            BEGIN
                BEGIN
                    raw_json = _raw_text::json;
                EXCEPTION
                    WHEN others THEN
                        raw_json = NULL;
                END;

                vec = (
                    CASE
                        WHEN raw_json IS NOT NULL THEN json_to_tsvector(_lang::regconfig, raw_json, '["string", "numeric", "boolean"]')
                        ELSE to_tsvector(_lang::regconfig, _raw_text)
                    END
                );

                IF _tsweight IS NOT NULL THEN
                    RETURN setweight(vec, _tsweight::"char");
                ELSE
                    RETURN vec;
                END IF;

            END;
        $$;


ALTER FUNCTION public.content_aware_tsvector(_lang text, _raw_text text, _tsweight character) OWNER TO app_user;



SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: searchable_items; Type: TABLE; Schema: public; Owner: app_user
--

CREATE TABLE public.searchable_items (
    item_id integer NOT NULL,
    item_type text NOT NULL,
    pk_meta jsonb DEFAULT '{}'::jsonb NOT NULL,
    meta jsonb DEFAULT '{}'::jsonb NOT NULL,
    raw_text text[] NOT NULL,
    tsweight character(1) DEFAULT NULL::bpchar,
    tenant character varying DEFAULT CURRENT_USER NOT NULL,
    var_id integer GENERATED ALWAYS AS ((((meta || pk_meta) ->> 'resource_var_id'::text))::integer) STORED,
    val_id integer GENERATED ALWAYS AS ((((meta || pk_meta) ->> 'resource_val_id'::text))::integer) STORED,
    search_tsv_english tsvector GENERATED ALWAYS AS (public.content_aware_tsvector('english'::regconfig, raw_text, tsweight)) STORED
);


ALTER TABLE public.searchable_items OWNER TO app_user;

--
-- Name: TABLE searchable_items; Type: COMMENT; Schema: public; Owner: app_user
--

COMMENT ON TABLE public.searchable_items IS 'Table for optimizing text based search';

--
-- Data for Name: searchable_items; Type: TABLE DATA; Schema: public; Owner: app_user
--

COPY public.searchable_items (item_id, item_type, pk_meta, meta, raw_text, tsweight, tenant) FROM stdin;
84  name    {}  {}  {Equipment} A   app_user
84  description {}  {}  {"core resource class for the scheduling of equipment"} C   app_user


--
-- Name: searchable_items searchable_items_pkey; Type: CONSTRAINT; Schema: public; Owner: app_user
--

ALTER TABLE ONLY public.searchable_items
    ADD CONSTRAINT searchable_items_pkey PRIMARY KEY (item_id, item_type, pk_meta);


--
-- Name: trgm_search_idx; Type: INDEX; Schema: public; Owner: app_user
--

CREATE INDEX trgm_search_idx ON public.searchable_items USING gin (((array_to_tsvector(array_remove(raw_text, NULL::text)))::text) public.gin_trgm_ops);



--
-- PostgreSQL database dump complete
--

O que estou esquecendo? O que pode fazer com que um pg_dump falhe quando restaurado nas mesmas versões de ferramentas PostgreSQL e psql?

A tabela que não está sendo restaurada é a seguinte (também incorporada no dump acima):

CREATE TABLE public.searchable_items (
    item_id integer NOT NULL,
    item_type text NOT NULL,
    pk_meta jsonb DEFAULT '{}'::jsonb NOT NULL,
    meta jsonb DEFAULT '{}'::jsonb NOT NULL,
    raw_text text[] NOT NULL,
    tsweight character(1) DEFAULT NULL::bpchar,
    tenant character varying DEFAULT CURRENT_USER NOT NULL,
    var_id integer GENERATED ALWAYS AS ((((meta || pk_meta) ->> 'resource_var_id'::text))::integer) STORED,
    val_id integer GENERATED ALWAYS AS ((((meta || pk_meta) ->> 'resource_val_id'::text))::integer) STORED,
    search_tsv_english tsvector GENERATED ALWAYS AS (public.content_aware_tsvector('english'::regconfig, raw_text, tsweight)) STORED
);

A search_tsv_englishcoluna calculada que é derivada das colunas raw_texte tsweightcausa a falha. Esta função personalizada deve ser definida antes que o COPYcomando para restaurar a tabela seja executado, mas o erro relata que a função não está presente ou que há uma incompatibilidade de tipo de argumento.

Executar o COPYcomando manualmente após a restauração funciona sem problemas, como se a função não tivesse sido persistida antes do COPYcomando.

Tenho uma consulta SQL que executa lentamente no PostgreSQL 13.15 com 128 GB de memória. A consulta executa principalmente um Bitmap Heap Scan, e notei que muitas leituras estão vindo do heap em vez do cache. Aqui está um link para a consulta e o plano de execução.

Um índice que aparece frequentemente no plano se parece com este:

create index ix_contacts__addresses_value__normalized
    on contacts__addresses using gin (company_id, field_name, field_id, value__normalized);

Minhas configurações de memória:

•   shared_buffers = 32GB
•   work_mem = 64MB
•   effective_cache_size = 96GB
•   maintenance_work_mem = 1320MB

Questões:

1. Por que a consulta está lendo tanto do heap?
2. Como posso configurar o PostgreSQL para usar melhor a memória (shared_buffers, work_mem e outras configurações) para evitar leituras de heap desnecessárias?
3. Devo considerar modificar os índices para reduzir o tempo de execução?

Eu apreciaria muito qualquer conselho sobre como otimizar o cache e o desempenho geral.

Um antigo artigo da Microsoft diz para considerar o uso de ROWcompactação por padrão se você tiver muito espaço na CPU (ênfase minha).

Se a compactação de linha resultar em economia de espaço e o sistema puder acomodar um aumento de 10% no uso da CPU, todos os dados deverão ser compactados por linha

Hoje, temos ainda mais espaço de CPU do que tínhamos quando o artigo foi escrito. Homens sábios disseram que deveríamos considerar usar PAGEcompressão em todos os lugares, a menos que tenhamos uma razão convincente para não fazê-lo.

Tudo isso é um bom conselho e muitas vezes vejo sp_estimate_data_compression_savingsque concordam. No entanto, o que deve ser feito com tabelas minúsculas que são acessadas com frequência? Por exemplo, tenho algumas tabelas de dimensão extremamente pequenas. 100 linhas no máximo e pouquíssimas colunas. Como são tão pequenas, o benefício de economia de espaço de qualquer compactação é mínimo. Qual é considerada a melhor prática para aplicar ROWou PAGEcompactar tabelas tão minúsculas em caixas que têm uma quantidade enorme de espaço livre na CPU?

Para os propósitos desta questão, ignore columnstore. Estamos falando apenas de índices rowstore da velha escola em tabelas baseadas em disco.

Estou tentando copiar o registro mais recente para cada valor de número de certificado. Temos vários documentos para cada certificado e estamos tentando obter o registro mais recente para cada certificado e copiá-lo para uma nova coleção.

aqui estão os dados:

{ "_id": ..., "certnumber":"123456", "createtime": "2024-02-03T16:42:20.324Z", "company": "A" ,.....}
{ "_id": ..., "certnumber":"123456", "createtime": "2024-02-03T16:32:20.324Z", "company": "A" ,.....}
{ "_id": ..., "certnumber":"123456", "createtime": "2024-02-02T14:28:20.324Z", "company": "A" ,.....}
{ "_id": ..., "certnumber":"123456", "createtime": "2024-02-02T13:22:20.324Z", "company": "A" ,.....}
{ "_id": ..., "certnumber":"654321", "createtime": "2024-02-05T17:42:20.324Z", "company": "B" ,.....}
{ "_id": ..., "certnumber":"654321", "createtime": "2024-02-05T16:42:20.324Z", "company": "B" ,.....}
{ "_id": ..., "certnumber":"654321", "createtime": "2024-02-03T15:32:20.324Z", "company": "B" ,.....}
{ "_id": ..., "certnumber":"654321", "createtime": "2024-02-03T14:22:20.324Z", "company": "B" ,.....}

Tentei reunir os últimos registros usando o comando abaixo e funciona, mas ele retorna apenas 2 campos. Como faço para extrair o documento inteiro em vez de apenas 2 campos?

db.collectionname.aggregate(
   [
     { $sort: { certnumber: 1, createtime: 1} },
     {
       $group:
         {
           _id: {certnumber : "$certnumber" },
           updatedDate: { $last: "$createtime" }
         }
     },
   ],
)

Preciso saber se o padrão a seguir requer que eu o use HOLDLOCKem um ambiente altamente concorrente. Observe particularmente que sourcenão é outra tabela. São basicamente parâmetros que foram passados ​​para a consulta.

MERGE INTO table1 WITH (HOLDLOCK) AS target
USING (
    SELECT 
        @ID AS ID,
        ...
) AS source
ON (target.ID = source.ID AND ...)
WHEN NOT MATCHED THEN
    INSERT (ID, ...)
    VALUES (source.ID, ...)

Eu só o coloquei por precaução, já que tenho lido sobre as armadilhas do MERGE. No meu próprio teste de desenvolvimento (ainda muito altamente concorrente), não parece fazer diferença. Nunca tive problemas de simultaneidade, mesmo sem o HOLDLOCK.

Não posso usar INSERT INTO table1 ... WHERE ... DOES NOT EXISTS (... SELECT table1 ...)o padrão, porque isso gera problemas de concorrência, mesmo com o HOLDLOCKin place. Então, essa MERGEfoi minha solução para isso, só não tenho certeza se preciso disso HOLDLOCK.

por exemplo CREATE INDEX [IX_table_bool1_bool2_decimal1_25233] ON [table] ([bool1, bool2,decimal1)incluir ??