Shahid Thaika's questions

Eu tenho uma consulta semelhante à seguinte:

FROM example_table
WHERE 
    `date` BETWEEN '2023-11-26' AND '2023-11-28'
    AND location_id IN (3, 4, 6, 7, 8, 10, 11, 12, 14, 18, 19, 22, 23, 24, 28, 29, 30, 31, 32, 36, 39, 40, 41, 43, 45, 46, 48, 49, 50, 51, 52, 54, 55, 56, 57, 59, 60, 61, 62, 68, 69, 75, 121)
    AND ( `type` IS NULL OR ( `type` IN ('type1', 'type2', 'type3') ) )
GROUP BY location_id;

Meu entendimento é que, ao criar um índice multicoluna, a coluna com maior cardinalidade/seletividade vai primeiro. Tentei testar o desempenho com duas chaves de índice:

1. (data, location_id, tipo, valor)
2. (location_id, data, tipo, valor)

Na minha tabela real, tenho 11.833 valores exclusivos na coluna de data e apenas 99 em location_id. Atualmente, existem mais de 63 milhões de linhas.

No entanto, o MySQL 8 prefere usar aquele que começa com location_id. Mesmo quando tento FORCE INDEXe EXPLAIN ANALYZE, ele mostra um custo/tempo maior daquele que começa com date.

O que poderia estar acontecendo?

EDITAR:

EXPLICAR ANÁLISE:

1. data primeiro índice

    -> Group aggregate: sum(ledger_entries.amount_cents)  (cost=1897 rows=6236) (actual time=0.167..4.67 rows=43 loops=1)
        -> Filter: ((ledger_entries.`date` = DATE'2023-11-28') and (ledger_entries.location_id in (3,4,6,7,8,10,11,12,14,18,19,22,23,24,28,29,30,31,32,36,39,40,41,43,45,46,48,49,50,51,52,54,55,56,57,59,60,61,62,68,69,75,121)) and ((ledger_entries.`type` is null) or (ledger_entries.`type` in ('Procedure','Adjustment','AncillarySale'))))  (cost=1273 rows=6236) (actual time=0.0221..4.09 rows=6192 loops=1)
            -> Covering index range scan on ledger_entries using index_le_date_location_type_amount_cents over (date = '2023-11-28' AND location_id = 3 AND type = NULL) OR (date = '2023-11-28' AND location_id = 3 AND type = 'Adjustment') OR (170 more)  (cost=1273 rows=6236) (actual time=0.02..2.83 rows=6192 loops=1)

2. primeiro índice de localização

    -> Group aggregate: sum(ledger_entries.amount_cents)  (cost=1888 rows=6236) (actual time=0.171..4.74 rows=43 loops=1)
        -> Filter: ((ledger_entries.`date` = DATE'2023-11-28') and (ledger_entries.location_id in (3,4,6,7,8,10,11,12,14,18,19,22,23,24,28,29,30,31,32,36,39,40,41,43,45,46,48,49,50,51,52,54,55,56,57,59,60,61,62,68,69,75,121)) and ((ledger_entries.`type` is null) or (ledger_entries.`type` in ('Procedure','Adjustment','AncillarySale'))))  (cost=1265 rows=6236) (actual time=0.0244..4.15 rows=6192 loops=1)
            -> Covering index range scan on ledger_entries using ledger_entries_location_date_type_amount_cents over (location_id = 3 AND date = '2023-11-28' AND type = NULL) OR (location_id = 3 AND date = '2023-11-28' AND type = 'Adjustment') OR (170 more)  (cost=1265 rows=6236) (actual time=0.022..2.91 rows=6192 loops=1)

Ferramentas como MySQLTuner adicionam memória alocada globalmente e a adicionam ao produto de conexões máximas e requisitos de memória por conexão. Formulei a seguinte consulta para estimar melhor quanta memória máxima seria necessária, se 100% das max_connections fossem usadas.

Alguém pode verificar se parece correto e se algum ajuste pode ser feito?

Percebo que exclui muitos outros requisitos de RAM do MySQL e do servidor, mas a ideia é estimar melhor.

SELECT
    sys.FORMAT_BYTES(
        @@key_buffer_size + @@query_cache_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size +
        (
            @@max_connections
             * (
                ((select_scans / queries) * @@read_buffer_size)
                + ((sort_operations / queries) * (@@read_rnd_buffer_size + @@sort_buffer_size))
                + ((join_operations / queries) * @@join_buffer_size)
                + @@binlog_cache_size + @@thread_stack
                + ((temp_tables / queries) * LEAST(@@tmp_table_size, @@max_heap_table_size))
                #need a better way to determine average packet size
                + (@@max_allowed_packet * 0.5) + @@net_buffer_length
            )
        )
    ) AS MAX_MEMORY
FROM (
    SELECT
        SUM(COUNT_STAR) AS queries,
        SUM(SUM_SELECT_SCAN) AS select_scans,
        SUM(
            GREATEST(
                (CASE WHEN UPPER(DIGEST_TEXT) LIKE '%ORDER BY%' THEN COUNT_STAR ELSE 0 END),
                (CASE WHEN SUM_SORT_MERGE_PASSES > 0 OR SUM_SORT_RANGE > 0 OR SUM_SORT_ROWS > 0 OR SUM_SORT_SCAN > 0 THEN COUNT_STAR ELSE 0 END)
            )
        ) AS sort_operations,
        SUM(CASE WHEN UPPER(DIGEST_TEXT) LIKE '%JOIN%' THEN COUNT_STAR ELSE 0 END) AS join_operations,
        SUM(SUM_CREATED_TMP_TABLES) AS temp_tables
    FROM performance_schema.events_statements_summary_by_digest
) t;

A documentação em https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/performance-schema-wait-summary-tables.html explica quais são as colunas, mas não menciona a unidade.

Presumi que fossem microssegundos, quando notei pela primeira vez que os números estavam em milhões, mas depois também vejo números em bilhões ( 408,113,517,000) na MIN_WAIT_TIMEcoluna da minha performance_schema.events_statements_summary_by_digesttabela.

A documentação diz especificamente que essas colunas não são agregadas e, enquanto observo as linhas com um ou dois COUNT_STARvalores, posso confirmar isso.

Como leio e possivelmente tento otimizar, MIN_TIMER_WAIT, AVG_TIMER_WAITe MAX_TIMER_WAITcolunas?

Estou tentando migrar de uma instância do MySQL AWS RDS com um SSD enorme e muito espaço em excesso para um pequeno, e a migração de dados é o único método. Existem quatro tabelas no intervalo de 330 GB a 450 GB e a execução do mysqldump, em um único thread, enquanto o encaminhamento direto para a instância RDS de destino é estimado em cerca de 24 horas por pv (copiando a 5 mbps).

Eu escrevi um script bash que chama vários mysqldump usando ' & ​​' no final e um --whereparâmetro calculado, para simular multithreading. Isso funciona e atualmente leva menos de uma hora com 28 threads.

No entanto, estou preocupado com qualquer perda potencial de desempenho durante a consulta no futuro, pois não estarei inserindo na sequência das colunas auto_increment id.

Alguém pode confirmar se esse seria o caso ou se estou sendo paranóico sem motivos.

Qual solução você usou para uma única tabela que está na casa dos 100 GB? Por um motivo específico, quero evitar o uso do AWS DMS e definitivamente não quero usar ferramentas que não são mantidas há algum tempo.

Minhas habilidades de pesquisa no Google não foram suficientes para encontrar uma solução para isso. Se possível, como posso promover uma partição em uma tabela para uma tabela por conta própria?

Vamos supor que eu tenho part0, part1, e part2. Existe algum ALTER TABLEque eu possa dar que converte part0para uma nova tabela?

Meu processo de pensamento foi que, como os dados já existem em um arquivo separado no disco, o processo de criação da tabela pode ser mais rápido do que criar uma nova tabela usando uma SELECTconsulta.

Eu tenho um banco de dados MySQL com 15 tabelas. Três deles são usados ​​para autenticação de sites e outros três são tabelas que são frequentemente lidas e gravadas. Em seguida, outras tabelas são definidas e eu nunca precisaria alterá-las, a menos que fizesse algumas alterações significativas no aplicativo da web.

Existe algo que eu possa fazer para tornar meu banco de dados mais eficiente e para desempenho, já que não preciso gravar nessas tabelas em tempo de execução?

No Google, consegui ver algumas configurações do MyISAM, mas preferiria ficar com o InnoDB. Não foi possível encontrar outras informações além de certas pessoas que têm todo o banco de dados como somente leitura. No entanto, o meu tem algumas tabelas que eu precisaria ler/escrever.

EDITAR:

Quatro tabelas têm 4 MB ou menos e uma tem 7,55 MB. Também não precisaria ajustar todos os meus procedimentos para verificar se a tabela na memória existe ou não? Porque pode desaparecer quando a instância for reiniciada ou algo assim.