Início / dba / Perguntas / 12675
Accepted
Katafalkas
Asked: 2012-02-12 03:34:25 +0800 CST

MySQL lê/grava por tabela

  • 772

Estou otimizando nosso banco de dados. Essencialmente, estou tentando encontrar as tabelas mais escritas e mais lidas em nosso banco de dados. Depois disso, continuarei vinculando essas tabelas a unidades separadas.

Existe uma maneira de seguir cada atividade de tabelas? Como a seguir IOPS, gravações, leituras por tabela?

mysql optimization

1 respostas

  1. Best Answer

    Método 1

    Se você estiver usando Percona Server ou MariaDB (>= 5.2), você pode simplesmente definir a variável userstat/userstat_running para habilitar um monte de novas tabelas INFORMATION_SCHEMA incluindo uma chamada TABLE_STATISTICS que fornece exatamente esta informação.

    Por exemplo:

    mysql> SELECT TABLE_NAME, ROWS_READ, ROWS_CHANGED, ROWS_CHANGED_X_INDEXES FROM TABLE_STATISTICS ORDER BY ROWS_CHANGED DESC LIMIT 5;
+-------------------+------------+--------------+------------------------+
| TABLE_NAME        | ROWS_READ  | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
+-------------------+------------+--------------+------------------------+
| user              |   21122527 |      5989231 |               23956924 |
| audit             |       1208 |      5020929 |               20083716 |
| sometemp          |   13995426 |      3182150 |                9546450 |
| creditcards       |    3566482 |      2998976 |               11995904 |
| order             | 2147483647 |      2662606 |               53252120 |
+-------------------+------------+--------------+------------------------+

    ROWS_CHANGED corresponderia ao mais gravado nas tabelas e ROWS_READ seria o mais lido. Você também deve consultar INDEX_STATISTICS para encontrar seus índices mais e menos usados.

    Consulte também a documentação de estatísticas do usuário do MariaDB .

    Método 2

    Se você não estiver usando o Percona Server, poderá usar pt-query-digest para capturar uma amostra de suas consultas e, em seguida, filtrar apenas INSERT/UPDATE/DELETEs. Isso ficaria mais ou menos assim:

    mysql> SELECT @@GLOBAL.slow_query_log_file;
+------------------------------------------+
| @@GLOBAL.slow_query_log_file             |
+------------------------------------------+
| /var/logs/mysql/slowquery.log            |
+------------------------------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL slow_query_log_file='/tmp/allqueries.log';
mysql> SELECT @@GLOBAL.long_query_time;
+--------------------------+
| @@GLOBAL.long_query_time |
+--------------------------+
|                 0.250000 |
+--------------------------+
1 row in set (0.00 sec)
mysql> SET GLOBAL long_query_time = 0;
mysql> FLUSH LOGS;
mysql> SLEEP 600; SET GLOBAL long_query_time = 0.25; SET GLOBAL slow_query_log_file='/var/logs/mysql/slowquery.log'; FLUSH LOGS;

    Agora você tem um arquivo /tmp/allqueries.logque contém todas as consultas executadas em seu servidor por aproximadamente 10 minutos.

    Em seguida, analise-o com pt-query-digest para obter as tabelas gravadas com mais frequência:

    pt-query-digest /tmp/allqueries.log --group-by=distill --filter '$event->{arg} =~ m/^(update|delete|insert)/i' --limit 5 > /tmp/writes.txt

    Se você examinar /tmp/writes.txt, verá uma seção perto do topo que se parece com esta:

    # Profile
# Rank Query ID Response time Calls R/Call Apdx V/M   Item
# ==== ======== ============= ===== ====== ==== ===== ====================
#    1 0x        0.0558 26.8%   282 0.0002 1.00  0.00 INSERT UPDATE user
#    2 0x        0.0448 21.5%   246 0.0002 1.00  0.00 UPDATE audit
#    3 0x        0.0228 10.9%    11 0.0021 1.00  0.00 UPDATE sometemp
#    4 0x        0.0108  5.2%    16 0.0007 1.00  0.00 UPDATE creditcards
#    5 0x        0.0103  4.9%    43 0.0002 1.00  0.00 UPDATE order

    Basicamente, essas são as tabelas mais gravadas durante a amostra que você escolheu. Para obter o máximo de leitura das tabelas (aproximadamente), você pode alterar o --filterparâmetro para --filter '$event->{arg} =~ m/^select/i'e verá uma saída semelhante.

    Se você estiver interessado apenas em gravações, poderá passar um log binário pt-query-digeste obter resultados semelhantes:

    mysqlbinlog mysql-bin.000511 | pt-query-digest --type=binlog --group-by=distill > /tmp/writes.txt

    Você também pode obter os mesmos dados com tcpdump ept-query-digest --type=tcpdump

    Então, dito isso, supondo que você esteja usando tabelas InnoDB, duvido muito que você veja muitos benefícios de desempenho ao fazer isso. Devido à maneira como os dados são armazenados em buffer no log do InnoDB e, em seguida, gravados no disco, eu não esperaria muito ou nenhum ganho de desempenho ao mover tabelas individuais dessa maneira. Você pode ver algum benefício em mover os próprios arquivos de log do InnoDB para um disco separado e mais rápido para separar as leituras/gravações do log das leituras/gravações do tablespace, mas mesmo isso é questionável. Investir em matrizes RAID rápidas e de alta qualidade com um cache alimentado por bateria (ou melhor ainda, SSD) será um melhor uso de seus recursos.

    • 10

relate perguntas