Derek Downey's questions

Fundo

Estou trabalhando na criação de um novo processo de desenvolvimento para uma pequena equipe web de cerca de 4 programadores e 4 designers, com o óbvio potencial de crescimento da equipe no futuro. Nosso produto é um aplicativo central que alimenta sites de clientes que também projetamos e hospedamos.

Anteriormente, todos trabalhávamos via FTP em um servidor de desenvolvimento, com um único banco de dados de desenvolvimento. "Funcionou" ^* por um tempo, mas estamos caminhando para uma nova direção, então é hora de amadurecer nosso processo.

Usamos o Percona Server 5.5, mas deve ser independente do banco de dados, com a ideia de manter os custos baixos.

Objetivos :

Estou pensando em criar um processo de integração contínua (CI) para desenvolvimento de banco de dados com o seguinte em mente:

1. Os desenvolvedores têm uma cópia local dos dados para executar o código de desenvolvimento
2. Capaz de reverter a estrutura do banco de dados para um conjunto de alterações anterior
3. Capaz de separar novas alterações de esquema de recurso versus alterações de correção de design de esquema
4. Capaz de modificar a estrutura do banco de dados localmente para teste

Conceito inicial

Esbocei um processo abaixo usando SVN e LiquiBase, embora remova completamente #4.

• crie uma ramificação de 'desenvolvimento' a partir do tronco
• servidor db 'desenvolvimento' central é executado fora do ramo 'desenvolvimento'
• desenvolvedores locais são configurados como escravos para o ramo de desenvolvimento (fornece #1acima)
• Os conjuntos de alterações do liquibase são confirmados regularmente no ramo de desenvolvimento, que executa um gancho pós-confirmação para atualizar o banco de dados de desenvolvimento central (que será transferido para as máquinas locais executando como escravos para este servidor de desenvolvimento) (o liquibase fornece #2acima)
• quando os recursos ou correções de esquema estiverem prontos para ir para o controle de qualidade, o DBA (eu) mesclará as alterações apropriadas da ramificação de desenvolvimento no tronco. Este ato criará um script sql para aplicar a um servidor de banco de dados temporário.
• O servidor Staging deve refletir o TRUNK, que deve ter a mesma estrutura da Produção, mais as alterações que estão no QA
• depois de executar o script sql no servidor de teste, faça algum controle de qualidade nas alterações.
• Se tudo parecer bom, MARQUE a estrutura. Isso irá gerar o script .sql para ser executado em produção manualmente pelo DBA (para horários fora de pico, se necessário)

Esse processo requer que todos os desenvolvedores executem a mesma ramificação de 'desenvolvimento', o que significa que há apenas uma versão do esquema do banco de dados a qualquer momento (não tenho certeza se quero isso).

Isso também significa que quaisquer alterações no esquema não podem ser testadas localmente e podem afetar outros desenvolvedores se não forem feitas corretamente. Em nosso ambiente, os desenvolvedores podem adicionar novas tabelas, mas raramente modificam a estrutura existente. Como DBA, as correções de design são feitas por mim. Mas a incapacidade de testar as correções localmente é meu maior problema no processo.

Como o processo acima pode ser ajustado para permitir o desenvolvimento local, mantendo uma cópia relativamente atualizada dos dados (conforme fornecido pela replicação em meu processo proposto)? Não exijo que os dados estejam atualizados até a última semana.

Ao converter um banco de dados para UTF-8, notei um comportamento estranho em relação aos caracteres de controle 0x80-0x9F. Por exemplo, 0x92 (apóstrofe à direita) não seria convertido em UTF-8 e truncaria o restante do conteúdo de uma coluna, usando este método:

CREATE TABLE `bar` (
 `content` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1

INSERT INTO bar VALUES (0x8081828384858687898A8B8C8D8E8F909192939495969798999A9B9C9D9E9F);
Query OK, 1 row affected (0.06 sec)

SELECT content FROM bar;
+---------------------------------------------------------------------------------+
| content                                                                         |
+---------------------------------------------------------------------------------+
| €‚ƒ„…†‡‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ                                                 |
+---------------------------------------------------------------------------------+
1 row in set (0.06 sec)

ALTER TABLE bar CHANGE content content TEXT CHARACTER SET UTF8;
Query OK, 1 row affected, 1 warning (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 1

SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                             |
+---------+------+-------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\x80\x81\x82\x83\x84\x85...' for column 'content' at row 1 |
+---------+------+-------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

SELECT * FROM bar;
+---------+
| content |
+---------+
|         |
+---------+
1 row in set (0.06 sec)

Embora normalmente 0x80-0x9F não seja permitido no Latin1, o MySQL parece lidar com isso de maneira diferente:

O latin1 do MySQL é o mesmo que o conjunto de caracteres cp1252 do Windows. Isso significa que é o mesmo que o oficial ISO 8859-1 ou IANA (Internet Assigned Numbers Authority) latin1, exceto que IANA latin1 trata os pontos de código entre 0x80 e 0x9f como “indefinidos”, enquanto cp1252 e, portanto, o latin1 do MySQL, atribui caracteres para essas posições. [origem]

Mas o MySQL não consegue converter o intervalo de valores acima de seu conjunto de caracteres latin1 para seu conjunto de caracteres UTF-8.

Esses caracteres estão entrando em meu banco de dados copiando/colando de um documento do Word (cp1252) e, embora eu possa ter encontrado uma maneira de fazer com que o aplicativo force os valores UTF-8 corretos para novas entradas, preciso garantir que o antigo obtenha convertido corretamente.

Existe uma maneira dentro do MySQL que estou perdendo para convertê-los para o equivalente UTF-8 sem passar por cada linha de cada coluna de texto e substituí-los por uma versão compatível com ASCII?

Estou pensando em configurar o log de todos os meus servidores em um banco de dados postgresql centralizado. Faz sentido poder descartar logs por data ou por host, então gostaria de configurá-lo com particionamento , mas uma partição de várias camadas:host-service inherits service, host-service-yyyymm inherits host-service.

Elaborei um esquema de exemplo para um serviço específico (erros de PHP neste caso) e estou procurando alguns especialistas em PostgreSQL para criticá-lo por gargalos óbvios de desempenho.

// SET UP MASTER PHP LOG TABLE //
CREATE TABLE php (
 log_id     int not null,
 host       char(5),    
 logdate     date not null,
 message        text
);

// SET UP HOST-SPECIFIC 'PARTITIONS' //
CREATE TABLE host1_php (
 CHECK ( host = 'host1' )
) INHERITS (php);

CREATE TABLE host2_php (
 CHECK ( host = 'host2' )
) INHERITS (php);

// SET UP HOST-SPECIFIC TIME 'PARTITIONS' //
CREATE TABLE host1_php_2011m12 (
 CHECK ( logdate >= DATE '2011-12-01' AND logdate < DATE '2012-01-01' )
) INHERITS (host1_php);

CREATE TABLE host1_php_2012m01 (
 CHECK ( logdate >= DATE '2012-01-01' AND logdate < DATE '2012-02-01' )
) INHERITS (host1_php);

CREATE TABLE host2_php_2011m12 (
 CHECK ( logdate >= DATE '2011-12-01' AND logdate < DATE '2012-01-01' )
) INHERITS (host2_php);

CREATE TABLE host2_php_2012m01 (
 CHECK ( logdate >= DATE '2012-01-01' AND logdate < DATE '2012-02-01' )
) INHERITS (host2_php);

CREATE INDEX host1_php_2011m12_logdate ON host1_php_2011m12 (logdate);
CREATE INDEX host1_php_2012m01_logdate ON host1_php_2012m01 (logdate);
CREATE INDEX host2_php_2011m12_logdate ON host2_php_2011m12 (logdate);
CREATE INDEX host2_php_2012m01_logdate ON host2_php_2012m01 (logdate);

Também adicionarei serviços como acesso/erros do apache.

Acho que tenho que acionar para inserir no host_service (para utilizar a restrição de verificação do host) e, em seguida, cada host_service deve acionar para inserir na tabela host_service_yyyymm.

O que posso esperar em termos de desempenho de tal esquema de acionamento/partição?

Algumas informações extras discutidas no chat:

• PostgreSQL versão 9.1.2
• As consultas de dados não serão muito frequentes e principalmente no mês atual de dados para vários hosts.
• O PHP tem apenas cerca de 1 inserção por minuto, mas o apache total provavelmente ficará em torno de 300-500 por segundo em todos os hosts.

Vindo de um histórico de MySQL, onde o desempenho do procedimento armazenado (artigo mais antigo) e a usabilidade são questionáveis, estou avaliando o PostgreSQL para um novo produto para minha empresa.

Uma das coisas que eu gostaria de fazer é mover um pouco da lógica da aplicação para stored procedures, então estou aqui pedindo DOs e DON'Ts (melhores práticas) no uso de funções no PostgreSQL (9.0), especificamente em relação às armadilhas de desempenho.

O MySQL não pode usar um índice composto em uma pesquisa na qual a WHEREcondição não inclui as colunas que formam um prefixo mais à esquerda:

O MySQL não pode usar o índice para realizar pesquisas se as colunas não formarem um prefixo mais à esquerda do índice.

Uma citação desta resposta no PostgreSQL chamou minha atenção:

Isso é um pouco diferente no Oracle 11, que às vezes também pode usar colunas que não estão no início da definição do índice.

Em que circunstâncias o Oracle (pelo menos no 11g) pode fazer uma pesquisa sem as colunas de prefixo mais à esquerda existentes na consulta?

Recentemente, encontrei uma mudança que o Percona Server implementou sobre o MySQL InnoDB normal. O novo recurso éinnodb_fast_checksum

O XtraDB pode usar um algoritmo de CPU mais eficiente, baseado em palavras de 4 bytes, o que pode ser benéfico para algumas cargas de trabalho (por exemplo, cargas de trabalho pesadas de gravação em servidores que podem executar muitas E/S).

Parece bom, mas gostaria de saber se há alguma carga de trabalho em que essa alteração teria um desempenho pior do que o método antigo.

A documentação continua dizendo:

O algoritmo original é verificado após o novo, portanto, você pode ter páginas de dados com somas de verificação antigas e páginas de dados com somas de verificação novas. No entanto, neste caso, você pode experimentar leituras lentas de páginas com somas de verificação antigas.

Portanto, supondo que você habilite innodb_fast_checksume reconstrua todas as suas tabelas InnoDB conforme sugerido para remover as somas de verificação antigas, há alguma situação em que a verificação extra nas somas de verificação antigas causaria degradação do desempenho?

Resumindo, há alguma razão para não correr innodb_fast_checksum?

Provavelmente estou sendo tacanho, mas se eu criar uma restrição de chave estrangeira e uma linha for atualizada ou excluída, perco essa conexão se a coluna da tabela filha for definida como NULL.

Qual é o propósito de manter intencionalmente essas linhas órfãs?

Até recentemente, eu via o cache de consulta como uma ferramenta muito importante para melhorar o desempenho da consulta. Hoje, eu estava ouvindo um podcast que discutia o ajuste do cache de consulta para 0 e o uso de uma solução de cache de memória melhor (como memcache.d).

Mas eles também mencionaram que há alguns casos em que query_cache é útil. Portanto, uma recomendação geral seria habilitá-lo sob demanda (usando SELECT SQL_CACHE, com uma configuração query_cache_type = 2).

Minha pergunta é, supondo que você tenha uma solução de cache como memcache.d, que tipo de circunstâncias tornaria query_cache mais ideal?

Editar: link adicionado

Recentemente, tive um problema em que notei que o MySQL ignorava espaços em branco à direita ao emitir um GROUP BY. Aqui está a tabela:

CREATE TABLE `myTable` (
 `id` int(10) unsigned NOT NULL,
 `foo` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

E aqui está um exemplo de consulta que executei (não é um uso de produção, mas mostra o que estou tentando corrigir):

SELECT foo, CHAR_LENGTH(foo) FROM myTable
WHERE foo='bar'
GROUP BY foo

Que retornaria um conjunto de resultados:

| foo  | CHAR_LENGTH(foo)  |
+------+-------------------+
| Bar  |                 4 |

Tudo muito bem, exceto 'Bar' é comprimento três, não quatro. Então fui examinar as entradas individuais (sem grupo por):

SELECT foo, CHAR_LENGTH(foo) FROM myTable
WHERE foo='bar'

| foo  | CHAR_LENGTH(foo)  |
+------+-------------------+
| Bar  |                 3 |
| Bar  |                 3 |
| Bar  |                 3 |
| Bar  |                 4 |
| Bar  |                 3 |

Agora, há cinco entradas para Bar, quatro das quais relatam corretamente um comprimento 3. Mas há uma que relata um comprimento de quatro.

Acontece que aquele com comprimento quatro tem um espaço no final: "Barra". Eu esperava GROUP BYtratar isso como um valor completamente diferente e não agrupá-lo com as outras "Barras".

Existe uma maneira de dizer ao MySQL para considerar valores diferentes se eles tiverem espaços em branco à direita (ou à esquerda)?

(executando mysql 5.1.55 64 bits no mac osx)

Deixe-me começar admitindo que sou muito ignorante sobre o funcionamento interno dos discos rígidos. Então, quando li o manual da variável innodb_flush_method , fiquei confuso. Posso obter uma explicação em termos leigos sobre a diferença entre O_DSYNC e O_DIRECT e como saber se é um problema de desempenho em um servidor de banco de dados.

Algumas estatísticas na minha configuração: Mac OSX 10.6 (kernel de 32 bits, já que a arquitetura está desatualizada) executando MySQL 5.1.49-64 bits (esperando que me permita usar a memória). 8 GB de RAM, ~ 6 GB de dados/índices innodb.

Portanto, tenho esta tabela de auditoria (rastreia ações em qualquer tabela do meu banco de dados):

CREATE TABLE `track_table` (
  `id` int(16) unsigned NOT NULL,
  `userID` smallint(16) unsigned NOT NULL,
  `tableName` varchar(255) NOT NULL DEFAULT '',
  `tupleID` int(16) unsigned NOT NULL,
  `date_insert` datetime NOT NULL,
  `action` char(12) NOT NULL DEFAULT '',
  `className` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userID` (`userID`),
  KEY `tableID` (`tableName`,`tupleID`,`date_insert`),
  KEY `actionDate` (`action`,`date_insert`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

e preciso começar a arquivar itens desatualizados. A tabela cresceu para cerca de 50 milhões de linhas, portanto, a maneira mais rápida de excluir as linhas era excluí-la uma tabela por vez (com base em tableName).

Isso funciona muito bem, mas em algumas tabelas com muita gravação, não será concluído. Minha consulta exclui todos os itens que possuem uma deleteação associada em uma combinação tupleID/tableName:

DELETE FROM track_table WHERE tableName='someTable' AND tupleID IN (
  SELECT DISTINCT tupleID FROM track_table
  WHERE tableName='someTable' AND action='DELETE' AND date_insert < DATE_SUB(CURDATE(), INTERVAL 30 day)
)

Deixei isso rodar no meu servidor por 3 dias e nunca foi concluído para a maior tabela. A saída de explicação (se eu alternar a exclusão para selecionar:

| id | select_type        | table       | type | possible_keys      | key     | key_len | ref        | rows    | Extra                        |
|  1 | PRIMARY            | track_table | ref  | tableID            | tableID | 257     | const      | 3941832 | Using where                  |
|  2 | DEPENDENT SUBQUERY | track_table | ref  | tableID,actionDate | tableID | 261     | const,func |       1 | Using where; Using temporary |

Portanto, 4 milhões de linhas não devem levar 3 dias para serem excluídas, eu acho. Eu tenho meu innodb_buffer_pool_size definido como 3 GB e o servidor não está definido para usar one_file_per_table. De que outras maneiras posso melhorar o desempenho de exclusão do InnoDB? (Executando MySQL 5.1.43 no Mac OSX)

Então, eu sou bastante novo para ajustar o InnoDB. Estou mudando lentamente as tabelas (quando necessário) de MyIsam para InnoDB. Eu tenho cerca de 100 MB no innodb, então aumentei a innodb_buffer_pool_sizevariável para 128 MB:

mysql> show variables like 'innodb_buffer%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

Quando fui alterar o innodb_log_file_sizevalor (exemplo my.cnf na página de configuração do mysql do innodb comentários para alterar o tamanho do arquivo de log para 25% do tamanho do buffer. Então agora meu my.cnf está assim:

# innodb
innodb_buffer_pool_size = 128M
innodb_log_file_size = 32M

Quando reinicio o servidor, recebo este erro:

110216 9:48:41 InnoDB: Inicializando buffer pool, tamanho = 128.0M
110216 9:48:41 InnoDB: Inicialização completa do buffer pool
InnoDB: Erro: arquivo de log ./ib_logfile0 é de tamanho diferente 0 5242880 bytes
InnoDB: do que o especificado em o arquivo .cnf 0 33554432 bytes!
110216 9:48:41 [ERRO] A função de inicialização do plug-in 'InnoDB' retornou um erro.
110216 9:48:41 [ERRO] Falha no registro do plug-in 'InnoDB' como STORAGE ENGINE.

Então, minha pergunta: é seguro excluir os log_files antigos ou existe outro método para alterar a innodb_log_file_sizevariável?

Eu tenho meu escravo configurado para não replicar o banco de dados 'mysql' conforme descrito nesteSHOW SLAVE STATUS\G;

           Slave_IO_State: Waiting for master to send event
              Master_Host: 127.0.0.1
              Master_User: replication
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: master-bin.000001
      Read_Master_Log_Pos: 1660
           Relay_Log_File: mysql-relay-bin.000004
            Relay_Log_Pos: 478
    Relay_Master_Log_File: master-bin.000001
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB: 
      **Replicate_Ignore_DB: mysql**
       Replicate_Do_Table: 
   Replicate_Ignore_Table: 
  Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
               Last_Errno: 0
               Last_Error: 
             Skip_Counter: 0
      Exec_Master_Log_Pos: 1660
          Relay_Log_Space: 633
          Until_Condition: None
           Until_Log_File: 
            Until_Log_Pos: 0

Agora, se eu for para o servidor MASTERGRANT e emitir um e FLUSH PRIVILEGES:

GRANT SELECT ON *.* TO `foo`@`localhost` IDENTIFIED BY 'bar';
FLUSH PRIVILEGES;

Eu então volto para o servidor SLAVE e emito:

SHOW GRANTS FOR `foo`@`localhost`;

e receba a resposta:

+-------------------------------------------------------------------------------------------------------------+
| Grants for foo@localhost                                                                                    |
+-------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'foo'@'localhost' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Como posso impedir que o escravo replique as alterações no banco de dados mysql? Achei que 'replicate_ignore_db' seria suficiente.

Estou pensando em configurar uma replicação mestre-escravo para meu banco de dados. O servidor escravo será usado para redundância e possivelmente um servidor de relatórios. No entanto, um dos maiores problemas que estou enfrentando é que já estamos esgotando a energia em nosso datacenter. Portanto, adicionar outro servidor físico não é uma opção.

Nosso servidor de banco de dados existente é bastante subutilizado no que diz respeito à CPU (as médias de carga nunca ficam acima de 1 em um quad-core). Portanto, a ideia principal é lançar algumas novas unidades e dobrar a memória (de 8 GB para 16) e executar uma segunda instância mysql na mesma máquina física. Cada instância teria discos separados para o banco de dados.

Há algo de errado com essa ideia?

Editar (mais informações): (felizmente) nunca tive nada ruim o suficiente para derrubar o servidor, mas estou tentando planejar com antecedência. É claro que temos backups noturnos dos quais poderíamos nos recuperar. Mas imaginei que ter os dados redundantes em discos separados forneceria uma solução mais rápida se as unidades do servidor mestre falhassem (obviamente não se a máquina inteira falhasse).

Quanto ao aspecto de relatório, todas as tabelas das quais relataríamos são MyIsam. Portanto, fazer leituras caras nas mesmas tabelas que estão sendo gravadas pode sobrecarregar o servidor. Minha suposição era que ter um servidor escravo para relatar não afetaria o servidor principal, desde que jogássemos RAM suficiente nele (já que a carga da CPU ainda não foi um problema).

Após a versão 5.0.3 (que permitiu que VARCHAR tivesse 65.535 bytes e parou de truncar espaços à direita), há alguma diferença importante entre esses dois tipos de dados?

Eu estava lendo a lista de diferenças e as únicas duas dignas de nota são:

Para índices em colunas BLOB e TEXT, você deve especificar um comprimento de prefixo de índice. Para CHAR e VARCHAR, um comprimento de prefixo é opcional. Consulte a Seção 7.5.1, “Índices de coluna”.

e

As colunas BLOB e TEXT não podem ter valores DEFAULT.

Então, por causa dessas duas limitações no tipo de dados TEXT, por que você o usaria sobre varchar(65535) ? Existem ramificações de desempenho de um sobre o outro?

Eu tenho um único banco de dados de cerca de 4,5 GB em execução em um servidor com 8 GB de RAM. A grande maioria das tabelas são MyIsam (cerca de 4,3 GB), mas em breve estarei convertendo algumas delas para InnoDB. (Será um processo lento, concentrando-se inicialmente nas tabelas de gravação mais intensiva).

Há algo de errado em executar um servidor dedicado onde existem os dois tipos de mecanismos de armazenamento?

De acordo com as restrições sobre rotinas armazenadas e gatilhos , sql dinâmico não pode ser usado (restrição suspensa para procedimentos armazenados na versão 5.0.13 e posterior). Por que essa limitação está em vigor? E por que levantá-lo para procedimentos, mas não para funções ou gatilhos?

Eu tenho um servidor mysql 5.1 com um banco de dados de aproximadamente 450 tabelas, ocupando 4 GB. A grande maioria dessas tabelas (todas menos 2) são MyIsam. Isso tem funcionado bem na maior parte (não precisa de transações), mas o aplicativo está ganhando tráfego e algumas tabelas foram afetadas devido ao bloqueio de tabelas nas atualizações. Essa é a razão pela qual 2 das tabelas são InnoDB agora.

A conversão nas tabelas menores (100k linhas) não demora muito, causando um tempo de inatividade mínimo. No entanto, algumas das minhas tabelas de rastreamento estão se aproximando de 50 milhões de linhas. Existe uma maneira de acelerar um ALTER TABLE...ENGINE InnoDBem grandes tabelas? E se não, existem outros métodos para converter minimizando o tempo de inatividade nessas tabelas pesadas de gravação?