syneticon-dj's questions

Em uma configuração do MS SQL Server 2014 AlwaysOn AG, gostaria de agendar trabalhos de backup para um determinado grupo de disponibilidade. O objetivo final é fazer com que os backups regulares sejam executados no secundário sincronizado e, mais importante, não vinculados à disponibilidade de um nó secundário específico.

A abordagem que vi até agora foi usar o agendador do SQL Server, configurar tarefas idênticas em todas as instâncias em execução e introduzir lógica condicional nas etapas do agendador para determinar se a função é primária ou secundária. Isso não funcionará para o meu caso de uso por vários motivos:

1. Desejo executar a ação agendada em um secundário apenas uma vez, mas tenho vários secundários
2. Eu quero garantir que ele seja executado, independentemente de haver algum secundário restante - se nenhum secundário for deixado, ele deve ser executado no primário

A tarefa de backup consiste em executar a BACKUP LOG [...] WITH COMPRESSION, NOINIT, NOFORMATcada 15 minutos.

No momento, estou pensando em criar uma tarefa agendada em cluster vinculada à função de cluster de failover do respectivo AG, mas gostaria de saber se há uma maneira mais fácil e simplificada de implementar isso.

Problema

Uma instância do MySQL 5.6.20 executando (principalmente apenas) um banco de dados com tabelas InnoDB está exibindo travamentos ocasionais para todas as operações de atualização com duração de 1 a 4 minutos, com todas as consultas INSERT, UPDATE e DELETE permanecendo no estado "Fim da consulta". Isso obviamente é muito lamentável. O log de consultas lentas do MySQL está registrando até mesmo as consultas mais triviais com tempos de consulta insanos, centenas delas com o mesmo carimbo de data/hora correspondente ao ponto no tempo em que a parada foi resolvida:

# Query_time: 101.743589  Lock_time: 0.000437 Rows_sent: 0  Rows_examined: 0
SET timestamp=1409573952;
INSERT INTO sessions (redirect_login2, data, hostname, fk_users_primary, fk_users, id_sessions, timestamp) VALUES (NULL, NULL, '192.168.10.151', NULL, 'anonymous', '64ef367018099de4d4183ffa3bc0848a', '1409573850');

E as estatísticas do dispositivo estão mostrando um aumento, embora não haja uma carga de I/O excessiva neste intervalo de tempo (neste caso, as atualizações estavam travando 14:17:30 - 14:19:12 de acordo com os timestamps da declaração acima):

# sar -d
[...]
02:15:01 PM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
02:16:01 PM    dev8-0     41.53    207.43   1227.51     34.55      0.34      8.28      3.89     16.15
02:17:01 PM    dev8-0     59.41    137.71   2240.32     40.02      0.39      6.53      4.04     24.00
02:18:01 PM    dev8-0    122.08   2816.99   1633.44     36.45      3.84     31.46      1.21      2.88
02:19:01 PM    dev8-0    253.29   5559.84   3888.03     37.30      6.61     26.08      1.85      6.73
02:20:01 PM    dev8-0    101.74   1391.92   2786.41     41.07      1.69     16.57      3.55     36.17
[...]
# sar
[...]
02:15:01 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
02:16:01 PM     all     15.99      0.00     12.49      2.08      0.00     69.44
02:17:01 PM     all     13.67      0.00      9.45      3.15      0.00     73.73
02:18:01 PM     all     10.64      0.00      6.26     11.65      0.00     71.45
02:19:01 PM     all      3.83      0.00      2.42     24.84      0.00     68.91
02:20:01 PM     all     20.95      0.00     15.14      6.83      0.00     57.07

Na maioria das vezes, noto no log lento do mysql que a interrupção da consulta mais antiga é um INSERT em uma tabela grande (~ 10 M linhas) com uma chave primária VARCHAR e um índice de pesquisa de texto completo:

CREATE TABLE `files` (
  `id_files` varchar(32) NOT NULL DEFAULT '',
  `filename` varchar(100) NOT NULL DEFAULT '',
  `content` text,
  PRIMARY KEY (`id_files`),
  KEY `filename` (`filename`),
  FULLTEXT KEY `content` (`content`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Uma investigação mais aprofundada (ou seja, SHOW ENGINE INNODB STATUS) mostrou que, de fato, sempre é uma atualização em uma tabela usando índices de texto completo que está causando a paralisação. A respectiva seção TRANSACTIONS de "SHOW ENGINE INNODB STATUS" tem entradas como estas duas para as transações em execução mais antigas:

---TRANSACTION 162269409, ACTIVE 122 sec doing SYNC index
6 lock struct(s), heap size 1184, 0 row lock(s), undo log entries 19942
TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_1" trx id 162269409 lock mode IX
TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_2" trx id 162269409 lock mode IX
TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_3" trx id 162269409 lock mode IX
TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_4" trx id 162269409 lock mode IX
TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_5" trx id 162269409 lock mode IX
TABLE LOCK table "vw"."FTS_000000000000224a_00000000000036b9_INDEX_6" trx id 162269409 lock mode IX
---TRANSACTION 162269408, ACTIVE (PREPARED) 122 sec committing
mysql tables in use 1, locked 1
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 165998, OS thread handle 0x7fe0e239c700, query id 91208956 192.168.10.153 root query end
INSERT INTO files (id_files, filename, content) VALUES ('f19e63340fad44841580c0371bc51434', '1237716_File_70380a686effd6b66592bb5eeb3d9b06.doc', '[...]
TABLE LOCK table `vw`.`files` trx id 162269408 lock mode IX

Portanto, há alguma ação pesada de índice de texto completo acontecendo lá ( doing SYNC index) parando TODAS AS atualizações SUBSEQUENTES em QUALQUER tabela.

A partir dos logs, parece que o undo log entriesnúmero doing SYNC indexestá avançando a ~ 150/s até atingir 20.000, ponto em que a operação é concluída.

O tamanho FTS desta tabela específica é bastante impressionante:

# du -c FTS_000000000000224a_00000000000036b9_*
614404  FTS_000000000000224a_00000000000036b9_INDEX_1.ibd
2478084 FTS_000000000000224a_00000000000036b9_INDEX_2.ibd
1576964 FTS_000000000000224a_00000000000036b9_INDEX_3.ibd
1630212 FTS_000000000000224a_00000000000036b9_INDEX_4.ibd
1978372 FTS_000000000000224a_00000000000036b9_INDEX_5.ibd
1159172 FTS_000000000000224a_00000000000036b9_INDEX_6.ibd
9437208 total

embora o problema também seja acionado por tabelas com tamanho de dados FTS significativamente menor como este:

# du -c FTS_0000000000002467_0000000000003a21_INDEX*
49156   FTS_0000000000002467_0000000000003a21_INDEX_1.ibd
225284  FTS_0000000000002467_0000000000003a21_INDEX_2.ibd
147460  FTS_0000000000002467_0000000000003a21_INDEX_3.ibd
135172  FTS_0000000000002467_0000000000003a21_INDEX_4.ibd
155652  FTS_0000000000002467_0000000000003a21_INDEX_5.ibd
106500  FTS_0000000000002467_0000000000003a21_INDEX_6.ibd
819224  total

O tempo de estol nesses casos também é aproximadamente o mesmo. Eu abri um bug em bugs.mysql.com para que os desenvolvedores pudessem investigar isso.

A natureza das paradas primeiro me fez suspeitar que a atividade de liberação de log seja a culpada e este artigo da Percona sobre problemas de desempenho de liberação de log com o MySQL 5.5 está descrevendo sintomas muito semelhantes, mas outras ocorrências mostraram que as operações INSERT na única tabela MyISAM neste banco de dados também são afetados pela parada, então isso não parece ser um problema apenas do InnoDB.

No entanto, decidi rastrear os valores de Log sequence numbere Pages flushed up todas saídas da seção "LOG"SHOW ENGINE INNODB STATUS a cada 10 segundos. De fato, parece que a atividade de descarga está em andamento durante a parada, pois a dispersão entre os dois valores está diminuindo:

Mon Sep 1 14:17:08 CEST 2014 LSN: 263992263703, Pages flushed: 263973405075, Difference: 18416 K
Mon Sep 1 14:17:19 CEST 2014 LSN: 263992826715, Pages flushed: 263973811282, Difference: 18569 K
Mon Sep 1 14:17:29 CEST 2014 LSN: 263993160647, Pages flushed: 263974544320, Difference: 18180 K
Mon Sep 1 14:17:39 CEST 2014 LSN: 263993539171, Pages flushed: 263974784191, Difference: 18315 K
Mon Sep 1 14:17:49 CEST 2014 LSN: 263993785507, Pages flushed: 263975990474, Difference: 17377 K
Mon Sep 1 14:17:59 CEST 2014 LSN: 263994298172, Pages flushed: 263976855227, Difference: 17034 K
Mon Sep 1 14:18:09 CEST 2014 LSN: 263994670794, Pages flushed: 263978062309, Difference: 16219 K
Mon Sep 1 14:18:19 CEST 2014 LSN: 263995014722, Pages flushed: 263983319652, Difference: 11420 K
Mon Sep 1 14:18:30 CEST 2014 LSN: 263995404674, Pages flushed: 263986138726, Difference: 9048 K
Mon Sep 1 14:18:40 CEST 2014 LSN: 263995718244, Pages flushed: 263988558036, Difference: 6992 K
Mon Sep 1 14:18:50 CEST 2014 LSN: 263996129424, Pages flushed: 263988808179, Difference: 7149 K
Mon Sep 1 14:19:00 CEST 2014 LSN: 263996517064, Pages flushed: 263992009344, Difference: 4402 K
Mon Sep 1 14:19:11 CEST 2014 LSN: 263996979188, Pages flushed: 263993364509, Difference: 3529 K
Mon Sep 1 14:19:21 CEST 2014 LSN: 263998880477, Pages flushed: 263993558842, Difference: 5196 K
Mon Sep 1 14:19:31 CEST 2014 LSN: 264001013381, Pages flushed: 263993568285, Difference: 7270 K
Mon Sep 1 14:19:41 CEST 2014 LSN: 264001933489, Pages flushed: 263993578961, Difference: 8158 K
Mon Sep 1 14:19:51 CEST 2014 LSN: 264004225438, Pages flushed: 263993585459, Difference: 10390 K

E às 14:19:11 o spread atingiu seu mínimo, então a atividade de descarga parece ter cessado aqui, apenas coincidindo com o fim da tenda. Mas esses pontos me fizeram descartar a descarga de log do InnoDB como a causa:

• para que a operação de limpeza bloqueie todas as atualizações no banco de dados, ela precisa ser "síncrona", o que significa que 7/8 do espaço de log deve ser ocupado
• seria precedido por uma fase de descarga "assíncrona" começando no innodb_max_dirty_pages_pctnível de preenchimento - o que não estou vendo
• os LSNs continuam aumentando mesmo durante a parada, então a atividade de log não está parando completamente
• Os INSERTs da tabela MyISAM também são afetados
• o thread page_cleaner para liberação adaptativa parece fazer seu trabalho e liberar os logs sem interromper as consultas DML:

^{(os números são ([Log Sequence Number] - [Pages flushed up to]) / 1024de SHOW ENGINE INNODB STATUS)}

O problema parece um pouco aliviado ao definir innodb_adaptive_flushing_lwm=1, forçando o limpador de página a fazer mais trabalho do que antes.

O error.lognão tem entradas coincidentes com as baias. SHOW INNODB STATUStrechos após aproximadamente 24 horas de operação são assim:

SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 789330
OS WAIT ARRAY INFO: signal count 1424848
Mutex spin waits 269678, rounds 3114657, OS waits 65965
RW-shared spins 941620, rounds 20437223, OS waits 442474
RW-excl spins 451007, rounds 13254440, OS waits 215151
Spin rounds per wait: 11.55 mutex, 21.70 RW-shared, 29.39 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-09-03 10:33:55 7fe0e2e44700
[...]
--------
FILE I/O
--------
[...]
932635 OS file reads, 2117126 OS file writes, 1193633 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 17.00 writes/s, 1.20 fsyncs/s
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 54745, id 140604272338688, state: sleeping
Number of rows inserted 528904, updated 1596758, deleted 99860, read 3325217158
5.40 inserts/s, 10.40 updates/s, 0.00 deletes/s, 122969.21 reads/s

Então, sim, o banco de dados tem impasses, mas eles são muito raros (o "mais recente" foi tratado cerca de 11 horas antes da leitura das estatísticas).

Tentei rastrear os valores da seção "SEMAPHORES" durante um período de tempo, especialmente em uma situação de operação normal e durante uma parada (escrevi um pequeno script verificando a lista de processos do servidor MySQL e executando alguns comandos de diagnóstico em uma saída de log no caso de uma parada óbvia). Como os números foram obtidos em diferentes intervalos de tempo, normalizei os resultados para eventos/segundo:

                          normal   stall
                          1h avg  1m avg
OS WAIT ARRAY INFO: 
    reservation count      5,74    1,00
    signal count          24,43    3,17
Mutex spin waits           1,32    5,67
    rounds                 8,33   25,85
    OS waits               0,16    0,43
RW-shared spins            9,52    0,76
    rounds               140,73    13,39
    OS waits               2,60    0,27
RW-excl spins              6,36    1,08
    rounds               178,42   16,51
    OS waits               2,38    0,20

Não tenho certeza do que estou vendo aqui. A maioria dos números caiu em uma ordem de magnitude - provavelmente devido à interrupção das operações de atualização, "esperas de rotação mutex" e "rodadas de rotação mutex", no entanto, aumentaram pelo fator de 4.

Investigando isso ainda mais, a lista de mutexes ( SHOW ENGINE INNODB MUTEX) tem ~480 entradas de mutex listadas tanto na operação normal quanto durante uma parada. Eu habilitei innodb_status_output_lockspara ver se ele vai me dar mais detalhes.

Variáveis ​​de configuração

(Eu mexi com a maioria deles sem sucesso definitivo):

mysql> show global variables where variable_name like 'innodb_adaptive_flush%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_adaptive_flushing     | ON    |
| innodb_adaptive_flushing_lwm | 1     |
+------------------------------+-------+
mysql> show global variables where variable_name like 'innodb_max_dirty_pages_pct%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_max_dirty_pages_pct     | 50    |
| innodb_max_dirty_pages_pct_lwm | 10    |
+--------------------------------+-------+
mysql> show global variables where variable_name like 'innodb_log_%';
+-----------------------------+-----------+
| Variable_name               | Value     |
+-----------------------------+-----------+
| innodb_log_buffer_size      | 8388608   |
| innodb_log_compressed_pages | ON        |
| innodb_log_file_size        | 268435456 |
| innodb_log_files_in_group   | 2         |
| innodb_log_group_home_dir   | ./        |
+-----------------------------+-----------+
mysql> show global variables where variable_name like 'innodb_double%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_doublewrite | ON    |
+--------------------+-------+
mysql> show global variables where variable_name like 'innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 29360128000    |
+-------------------------------------+----------------+
mysql> show global variables where variable_name like 'innodb_io_capacity%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 200   |
| innodb_io_capacity_max | 2000  |
+------------------------+-------+
mysql> show global variables where variable_name like 'innodb_lru_scan_depth%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_lru_scan_depth | 1024  |
+-----------------------+-------+

Coisas já tentadas

• desativando o cache de consulta porSET GLOBAL query_cache_size=0
• aumentando innodb_log_buffer_sizepara 128 milhões
• brincando com innodb_adaptive_flushing, innodb_max_dirty_pages_pcte os respectivos _lwmvalores (eles foram definidos como padrão antes das minhas alterações)
• crescente innodb_io_capacity(2000) e innodb_io_capacity_max(4000)
• contextoinnodb_flush_log_at_trx_commit = 2
• executando com innodb_flush_method = O_DIRECT (sim, usamos uma SAN com um cache de gravação persistente)
• definindo o /sys/block/sda/queue/scheduler para noopoudeadline

Pessoal,

Estou executando uma cópia de nosso banco de dados de produção como um back-end para uma instância do SQL Server Reporting Services. A cópia está sendo atualizada dos logs de transação que são obtidos com frequência (a cada hora) para fins de backup usando RESTORE DATABASE WITH NORECOVERY/ WITH STANDBY. Obviamente, não posso usar os recursos integrados de envio de log de transação, pois o banco de dados já está sendo BACKUP LOGgerido e tentar usar o envio de log quebraria as cadeias de log de ambos os mecanismos.

Mas preciso de alguma forma de monitorar se minha cópia está atualizada, de preferência sem alterar o esquema ou os dados do banco de dados original. Minha primeira abordagem consistiria em recuperar o último LSN do backup de log mais recente RESTORE HEADERONLY FROM DISKe compará-lo com o LSN da minha instância de cópia. Mas estou tendo dificuldades para encontrar um LSN que seja capaz de corresponder ao resultado RESTORE HEADERONLY- o que vejo em sys.database_files parece ser uma coisa totalmente diferente.

Onde eu encontraria LSNs compatíveis?

Ao olhar para uma consulta particularmente irritante sobre tabelas MyISAM que leva muito tempo para executar em várias ocasiões, notei que o MySQL parece expor um padrão de I/O bastante estranho: ao executar uma única consulta e ter que fazer um quantidade de E/S (por exemplo, para uma varredura de tabela ou quando os caches estão vazios, echo 3 > /proc/sys/vm/drop_cachesentão os índices precisam ser carregados do disco primeiro), o tamanho da fila para o dispositivo de bloco subjacente está próximo do valor 1, com desempenho péssimo de apenas 4-5 MB/s:

root@mysql-test:~# iostat -xdm 5 /dev/sda
Linux 3.2.0-40-generic (mysql-test)  04/30/2014      _x86_64_        (4 CPU)

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.14    24.82   18.26   88.79     0.75     4.61   102.56     2.83   26.39   19.29   27.85   2.46  26.31

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00    69.29  151.52   72.73     5.31     0.59    53.95     1.21    5.39    7.84    0.29   4.39  98.51

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00   153.06  144.29  174.69     4.96     1.36    40.54     1.39    4.36    8.91    0.60   3.15 100.49

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00   105.75  150.92  109.03     4.53     0.85    42.41     1.29    4.96    8.15    0.54   3.90 101.36

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00    48.89  156.36   51.72     5.28     0.76    59.38     1.28    6.16    8.02    0.55   4.77  99.23

Embora os 150 IOPS sejam simplesmente o que um único disco na configuração fornecida é capaz de fornecer em termos de I/O aleatório, o resultado ainda me surpreende, pois eu esperaria que o MySQL fosse capaz de executar I/O assíncrono para leituras e buscar um grande quantidade de blocos simultaneamente em vez de lê-los e avaliá-los um por um, negligenciando efetivamente os ganhos de paralelização disponíveis nas configurações de RAID. Qual decisão de projeto ou opção de configuração é responsável por isso? Este é um problema específico da plataforma?

Embora eu tenha testado isso com tabelas MyISAM grandes, vejo efeitos semelhantes com as mesmas tabelas convertidas para InnoDB (embora não seja tão ruim, a consulta de amostra ainda leva de 20 a 30 segundos, com a maior parte do tempo gasta na leitura do disco com um comprimento de fila de 1) depois de reiniciar o daemon mysql e, portanto, os buffer pools estão vazios. Também verifiquei que o mesmo problema persiste no 5.6 GA e no atual marco 14 do 5.7 - enquanto estiver usando um único thread de consulta, o MySQL parece incapaz de paralelizar as operações de E/S necessárias para o processamento da consulta.

A pedido, alguns detalhes adicionais sobre o cenário. O comportamento pode ser observado com vários tipos de consulta. Eu escolhi arbitrariamente um para testes adicionais, que é mais ou menos assim:

SELECT herp.id, herp.firstname, herp.lastname, derp.label, herp.email, 
(SELECT CONCAT(label, " (", zip_code, " ", city,")" ) FROM subsidiaries WHERE subsidiaries.id=herp.subsidiary_id ) AS subsidiary, 
(SELECT COUNT(fk_herp) from herp_missing_data WHERE fk_herp=herp.id) AS missing_data
FROM herp LEFT JOIN derp ON derp.id=herp.fk_derp
WHERE (herp.fk_pools='123456')  AND herp.city LIKE '%Some City%' AND herp.active='yes' 
ORDER BY herp.id desc LIMIT 0,10;

Sei que há espaço para otimização, mas decidi deixar por isso mesmo por vários motivos e me concentrar em encontrar uma explicação geral para o padrão de E/S inesperado que estou vendo.

As tabelas usadas têm um monte de dados nelas:

mysql> select table_name, engine, table_rows, data_length, index_length from information_schema.tables WHERE tables.TABLE_SCHEMA = 'mydb' and tables.table_name in ( 'herp', 'derp', 'missing_data', 'subsidiaries');
+-------------------------+--------+------------+-------------+--------------+
| table_name              | engine | table_rows | data_length | index_length |
+-------------------------+--------+------------+-------------+--------------+
| derp                    | MyISAM |      14085 |     1118676 |       165888 |
| herp                    | MyISAM |     821747 |   828106512 |    568057856 |
| missing_data            | MyISAM |    1220186 |    15862418 |     29238272 |
| subsidiaries            | MyISAM |       1499 |     6490308 |       103424 |
+-------------------------+--------+------------+-------------+--------------+
4 rows in set (0.00 sec)

Agora, quando estou executando a consulta acima nessas tabelas, estou obtendo tempos de execução de mais de 1 minuto, enquanto o sistema está aparentemente continuamente ocupado lendo dados do disco com um único thread.

O perfil para uma execução de consulta de amostra (que levou 1 minuto e 9,17 segundos neste exemplo) tem a seguinte aparência:

mysql> show profile for query 1;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000118 |
| Waiting for query cache lock   |  0.000035 |
| init                           |  0.000033 |
| checking query cache for query |  0.000399 |
| checking permissions           |  0.000077 |
| checking permissions           |  0.000030 |
| checking permissions           |  0.000031 |
| checking permissions           |  0.000035 |
| Opening tables                 |  0.000158 |
| init                           |  0.000294 |
| System lock                    |  0.000056 |
| Waiting for query cache lock   |  0.000032 |
| System lock                    |  0.000116 |
| optimizing                     |  0.000063 |
| statistics                     |  0.001964 |
| preparing                      |  0.000104 |
| Sorting result                 |  0.000033 |
| executing                      |  0.000030 |
| Sending data                   |  2.031349 |
| optimizing                     |  0.000054 |
| statistics                     |  0.000039 |
| preparing                      |  0.000024 |
| executing                      |  0.000013 |
| Sending data                   |  0.000044 |
| optimizing                     |  0.000017 |
| statistics                     |  0.000021 |
| preparing                      |  0.000019 |
| executing                      |  0.000013 |
| Sending data                   | 21.477528 |
| executing                      |  0.000070 |
| Sending data                   |  0.000075 |
| executing                      |  0.000027 |
| Sending data                   | 45.692623 |
| end                            |  0.000076 |
| query end                      |  0.000036 |
| closing tables                 |  0.000109 |
| freeing items                  |  0.000067 |
| Waiting for query cache lock   |  0.000038 |
| freeing items                  |  0.000080 |
| Waiting for query cache lock   |  0.000044 |
| freeing items                  |  0.000037 |
| storing result in query cache  |  0.000033 |
| logging slow query             |  0.000103 |
| cleaning up                    |  0.000073 |
+--------------------------------+-----------+
44 rows in set, 1 warning (0.00 sec)

Uma instalação do MySQL 5.6.10 em um Ubuntu 12.04 virtualizado está exibindo uma enorme sobrecarga de memória. O processo mysqld reivindica toda a memória disponível dentro de algumas horas de uptime e força o host a trocar:

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
16229 mysql     20   0 26.8g  21g 8736 S   42 93.4  37:23.22 mysqld

Ele cresceu até 50 GB uma vez e, portanto, superou significativamente o próprio conjunto de dados:

Current InnoDB index space = 5.25 G
Current InnoDB data space = 23.07 G

Normalmente, consigo liberar ~ 3 GB emitindo FLUSH TABLES, embora seja consideravelmente mais rápido apenas kill -9o processo mysql, reiniciá-lo e executar a recuperação do InnoDB. As tabelas usadas são quase exclusivamente InnoDB, o innodb_buffer_pool_size foi definido para 5 GB (depois de configurá-lo para 16 GB rapidamente esgotou a memória física disponível e trocou mais de 18 GB dela).

Enquanto o sistema estava trocando, pude observar números bastante altos para contadores de "troca" (vmstat está mostrando ~ 1k páginas/segundo durante rajadas) e quase nada foi trocado de volta (algumas dezenas de páginas por minuto). Suspeitei primeiro de vazamento de memória, mas não encontrei nada que apoiasse essa hipótese até agora.

SHOW INNODB STATUS indica que o buffer pool está apenas parcialmente preenchido:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 5365825536; in additional pool allocated 0
Dictionary memory allocated 2558496
Buffer pool size   320000
Free buffers       173229
Database pages     142239
Old database pages 52663
Modified db pages  344
Pending reads 1
Pending writes: LRU 0, flush list 1 single page 0
Pages made young 34, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 141851, created 387, written 41126
81.16 reads/s, 0.00 creates/s, 0.39 writes/s
Buffer pool hit rate 998 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 142239, unzip_LRU len: 0
I/O sum[0]:cur[464], unzip sum[0]:cur[0]

O servidor tem um total de 80-90 conexões, a maioria das quais relatadas como estando no estado "Sleep" por SHOW PROCESSLIST.

As opções sensíveis à memória definidas são

max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
max_connections         = 1000

innodb_file_format      = Barracuda
innodb_buffer_pool_size = 5000M
innodb_log_file_size    = 256M
innodb_flush_method     = O_DIRECT

query_cache_limit       = 1M
query_cache_size        = 256M

join_buffer_size        = 256k
tmp_table_size          = 2M
max_heap_table_size     = 64M

O script tuning-primer.sh calcula valores sensatos para uso de memória:

MEMORY USAGE
Max Memory Ever Allocated : 5.27 G
Configured Max Per-thread Buffers : 1.92 G
Configured Max Global Buffers : 5.15 G
Configured Max Memory Limit : 7.07 G
Physical Memory : 22.98 G
Max memory limit seem to be within acceptable norms

Binlog está ativado e o host tem um escravo de replicação anexado a ele (embora os resultados não tenham sido tão diferentes no momento, esse não foi o caso). Innodb_file_per_table é ativado por padrão em 5.6 e os bancos de dados estão hospedando um total de aproximadamente 1.300 tabelas.

Que meios tenho para identificar as possíveis causas do crescimento aparentemente ilimitado?

Depois de ler "Como o MySQL usa a memória" , suspeitei que as tabelas temporárias poderiam ser as culpadas. Se eles não estiverem sendo liberados corretamente por qualquer motivo, eles podem se acumular rapidamente. O aplicativo que consulta o banco de dados emite muitas consultas aninhadas e complicadas, portanto, as tabelas temporárias seriam muito usadas de acordo com os documentos referenciados. Eu tentei verificar se matar / redefinir conexões existentes (ociosas) reduziria significativamente o uso de memória quando o mysqld atingiu ~ 20 GB - não, então isso não está relacionado aos estados de conexão ou a memória está vazando de uma maneira que ser afetado pelo fechamento da conexão.

Como eu verificaria se as tabelas temporárias na memória estão ocupando uma quantidade significativa de memória? As variáveis ​​STATUS e o INFORMATION_SCHEMA parecem não ter essa informação.

O uso de memória do MySQL parece difícil de depurar - os contadores disponíveis parecem não contabilizar a maior parte do uso que estou vendo. Eu posso estar perdendo alguma coisa, no entanto.

Eu também tenho um escravo de replicação baseado em MyISAM anexado ao mestre InnoDB recebendo cargas semelhantes (somente leitura) - ele não mostra nenhum sinal de uso excessivo de memória (mysqld RSS é continuamente < 1 GB), então o problema parece ser específico para a configuração do InnoDB.

Estou tentando usar log_queries_not_using_indexes = 1para encontrar consultas que não estão sendo executadas de maneira ideal em um servidor MySQL. No entanto, acho o arquivo de log resultante de valor bastante limitado. Aparentemente, as consultas são registradas sempre que o otimizador realmente decidiu não usar um índice como critério em uma cláusula WHERE. E não se eles realmente não tiverem índices correspondentes às colunas filtradas.

Então dada uma tabela com a seguinte estrutura

CREATE TABLE `test` (
    `id_test`   int(11) NOT NULL AUTO_INCREMENT,
    `some_text`  varchar(255) DEFAULT NULL,
    `some_more_text` text,
 PRIMARY KEY (`id_test`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 

uma consulta SELECT id_test from test where id_test != 69seria registrada no log lento por não usar índices (o otimizador decidiu que uma varredura de tabela é mais eficiente, pois não se pode ganhar muito usando um índice), mas SELECT id_test from test where id_test = 69não.

Eu esperaria o comportamento da última consulta no primeiro caso, pois o índice está presente. Como está, torna a solução de problemas de índices ausentes bastante cansativa. Idéias sobre como abordar isso são muito apreciadas.

De acordo com a documentação de replicação do MySQL , é possível configurar a replicação de tabelas de origem InnoDB para tabelas de destino MyISAM. Infelizmente, a documentação tem pouco a dizer sobre desvantagens, implicações de consistência de dados (além do CASCADE corner case) e configurações recomendadas para habilitar tal configuração de replicação. Então, apenas algumas perguntas me vêm à mente em relação a essa configuração:

1. a replicação baseada em instrução é preferida à variante baseada em linha ou vice-versa?
2. o lado do InnoDB ou do MyISAM precisa de algumas configurações específicas para que a consistência dos dados não seja interrompida por si só como resultado da atividade de replicação "normal"?
3. as execuções DDL no mestre são tratadas no escravo de maneira sã?
4. como eu impediria que uma ALTER TABLE blah ENGINE=InnoDBinstrução executada no mestre se propagasse para o escravo?
5. Existe uma maneira recomendada de configurar essa configuração "dividida"? Eu poderia pensar em um direto ALTER TABLE blah ENGINE=MyISAMno escravo, isso é viável?
6. quaisquer outras advertências que se deva saber?

Preciso de ajuda para entender o que está acontecendo com meu log de "consulta lenta" no MySQL. Eu arrumei

 set global long_query_time=3

e, no entanto, é isso que vejo no log de consulta lento:

# Time: 120723  9:04:38
# User@Host: root[root] @  [111.111.111.205]
# Query_time: 0.017508  Lock_time: 0.000111 Rows_sent: 2884  Rows_examined: 9132
SET timestamp=1343027078;
SELECT [...]
# User@Host: root[root] @  [111.111.111.202]
# Query_time: 0.030282  Lock_time: 0.000102 Rows_sent: 0  Rows_examined: 12323
SET timestamp=1343027078;
SELECT [...]
# Time: 120723  9:04:41
# User@Host: root[root] @  [111.111.111.202]
# Query_time: 0.004416  Lock_time: 0.000104 Rows_sent: 18  Rows_examined: 6266
SET timestamp=1343027081;
SELECT [...]
# User@Host: root[root] @ hostname [111.111.111.199]
# Query_time: 0.029399  Lock_time: 0.000116 Rows_sent: 0  Rows_examined: 12323
SET timestamp=1343027088;
SELECT [...]
# Time: 120723  9:04:53
# User@Host: root[root] @  [111.111.111.201]
# Query_time: 0.004470  Lock_time: 0.000080 Rows_sent: 0  Rows_examined: 6248
SET timestamp=1343027093;
SELECT [...]
# Time: 120723  9:04:55
# User@Host: root[root] @  [111.111.111.198]
# Query_time: 0.042627  Lock_time: 0.000087 Rows_sent: 0  Rows_examined: 0
use [...];
SET timestamp=1343027095;
UPDATE [...]
# User@Host: root[root] @  [111.111.111.198]
# Query_time: 0.029115  Lock_time: 0.000124 Rows_sent: 0  Rows_examined: 0

onde o tempo em Query_time é significativamente menor do que meus 3 segundos especificados anteriormente. Por que isso? Onde está o tempo perdido?

MySQL 5.1.41, Ubuntu 10.04

Estou tentando importar um script SQL longo (3,5 milhões de linhas, 1,5 GB de tamanho) contendo principalmente instruções INSERT INTO curtas com dados usando sqlcmd -E -d <database> -S 127.0.0.1 -i <scriptfile>e está quase parando (aproximadamente 150 linhas por segundo) sem inferir qualquer perceptível carga no host do servidor SQL (todos os núcleos da CPU estão quase ociosos, a taxa de transferência do disco é de cerca de 200 KB/s).

Como o arquivo de origem está localizado em um compartilhamento NFS, primeiro suspeitei que esse fosse o culpado, mas um script idêntico disponível localmente é executado na mesma velocidade.

O banco de dados está basicamente vazio, as tabelas são criadas pelo mesmo script, sem gatilhos ou outras fantasias - apenas os dados brutos, principalmente numéricos ou varchar.

O que o SQLCMD está esperando? Existe alguma maneira de agilizar?

Editar:

estamos limitados em nossos meios para alterar os dados nos arquivos de script SQL. Os dados são fornecidos por um terceiro para um procedimento de importação. Acredito que foi exportado originalmente usando o recurso "Tabela de script como..." do Management Studio 2005.

Editar o arquivo é tedioso devido ao tamanho - qualquer operação leva uma eternidade com editores de texto comuns, embora o prefixo "SET NOCOUNT ON" tenha sido feito copiando dois arquivos juntos - e trouxe uma aceleração de aproximadamente 50%.

Devido à codificação de texto (Unicode-LE), é impossível editar com o conjunto comum de textutils GNU (que de outra forma funcionaria bem com arquivos grandes) sem converter - e estou relutante em converter devido a problemas de fidelidade de dados que provavelmente venha com ele.

Portanto, estou um pouco preso em como aplicar as sugestões sobre como inserir os blocos BEGIN TRAN/COMMIT TRAN ou converter as inserções individuais em conjuntos maiores.