Início / dba / Perguntas / 318028
Accepted
Shahid Thaika
Asked: 2022-10-11 02:45:47 +0800 CST

Melhor estimativa do "pior uso de memória" para MySQL

  • 772

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;
mysql performance

2 respostas

  1. Este não é realmente um valor útil para calcular, mesmo que você possa obter uma estimativa mais precisa, porque o "uso máximo de memória" teórico nunca acontece.

    Em todas as produções do MySQL Server que analisei (visitei dezenas de empresas como consultor e suportei milhares de instâncias do MySQL Server como DBA), o cálculo do tipo MySQLTuner sempre fornece um uso teórico máximo de memória que é muitas vezes a quantidade de RAM física no servidor. Mas você nunca vê o uso de memória swap igual a 10x RAM física.

    A fórmula de estimativa que você mostra tem algumas imprecisões de qualquer maneira:

    • Alguns buffers não são alocados em seu tamanho total toda vez que são usados ​​(exemplo: tmp_table_size, etc.)

    • Alguns buffers podem ser alocados mais de uma vez em uma determinada consulta (exemplo: join_buffer_sizee tmp_table_size)

    • Algum uso de RAM não está disponível em uma variável de configuração (exemplo: o otimizador usa uma quantidade variável de RAM ao estimar o custo de consultas de intervalo de vários valores)

    De qualquer forma, para um servidor MySQL atingir o uso máximo de memória por esta fórmula, você teria que ter max_connectionsclientes, todas as consultas executando simultaneamente, e cada consulta teria que alocar todos os buffers em seu tamanho máximo permitido.

    Isso simplesmente nunca acontece. Seu servidor de banco de dados entraria em colapso muito antes de atingir esse nível de uso simultâneo.

    Eu me encolho quando vejo as pessoas confiarem no conselho do MySQLTuner. Frequentemente dá conselhos muito ruins e enganosos. Eu não acho que o autor tenha muito conhecimento sobre os componentes internos do MySQL.

    Então, o que devemos fazer para estimar o uso de memória com precisão?

    Observar.

    Obtenha algum serviço de coleta de métricas para representar graficamente o tamanho real da memória do mysqldprocesso ao longo do tempo, pois ele lida com tráfego realista de seu aplicativo em seu servidor. Isso será preciso, mostrará a informação verdadeira, mínima e máxima, e o padrão de aumento e diminuição.

    • 1
  2. Best Answer

    Essa é uma boa facada em uma fórmula melhor. Mas ainda acredito que não é possível obter um limite superior "correto".

    • max_connections, table_open_cache, max_allowed_packet, etc, raramente são atingidos. (Ou seja, sua fórmula ainda será uma superestimativa do máximo.)
    • Alguns dos buffers por consulta são realmente por consulta SELECT, e isso inclui subconsultas. Você tentou contabilizar tal.
    • Quando há vários JOINsem uma única consulta, pode haver várias tabelas temporárias. (Acho que você não permitiu isso.)
    • Alguns desses "count_stars" e "sums" são cumulativos e não são marcas d'água altas. (Eu não sei qual, do topo da minha cabeça.) Embora sua fórmula tente contabilizar várias seleções em uma única consulta, ela não evita os contadores "cumulativos".
    • A divisão dos contadores pelo número de consultas fornece uma média; você precisa de um máximo. (No meu sistema, recebo 0,08 "select_scans" e 0,03 "sorts".) Você precisa do "max" sobre as consultas. No meu caso, pode ser 1 ou 2 varreduras e classificações por consulta, mas geralmente 0. (Não, não sei como calcular isso.)
    • Existe um buffer de log binário e provavelmente algumas outras coisas.

    Algumas configurações:

    • Você não deve estar usando MyISAM, então key_buffer_sizedeve ser "pequeno" (exceto no MySQL 8.0, onde deve ser 0).
    • O cache de consulta não pode ser usado em clusters e foi removido de algumas versões; portanto query_cache_size, com poucas exceções, deve ser 0. (A variável até desapareceu em 8.0, invalidando assim a consulta.)

    Parece que alguns outros ajustáveis ​​estão faltando, mas levaria um tempo para vasculhar minhas anotações. Deseja continuar esta discussão?

    • 0

relate perguntas