MySQL: InnoDB Corrompido

 MySQL  10 Responses »
jan 052012
 

Recentemente tivemos um problema com o desligamento inadequado de um servidor, e então o MySQL não iniciou mais corretamente.
Analisando o mysql.log, verificamos mensagens como essa abaixo, a cada tentativa de início.

InnoDB: stored checksum 3103110567, prior-to-4.0.14-form stored checksum 438929502
InnoDB: Page lsn 66 2543532480, low 4 bytes of lsn at page end 2544351964
InnoDB: Page number (if stored to page already) 131080,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 0 381
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 131080.

Utilizar o programa mysqlcheck não era uma opção, pois ele precisa que o MySQL esteja iniciado e operacional.
Então utilizamos o parâmetro innodb_force_recovery, que possui os níveis de 0 a 6, sendo que 0 é o padrão e significa que não haverá uma recuperação forçada, e 6 pode ser considerado o mais agressivo, pois com este valor nenhuma recuperação é executada.
A intenção deste parâmetro é que caso não haja um backup recente disponível (quase nunca há, não é mesmo?), o MySQL possa ser iniciado e ter seus dados exportados. É uma opção para ser utilizada em último caso, contando com a sorte para que os dados possam ser exportados.
Fui então incrementando o valor do parâmetro progressivamente e tentando iniciar o MySQL, mas só consegui com o 6.

[root@zabbix ~]# grep force /etc/my.cnf
[root@zabbix ~]# service mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:                                            [FAILED]
[root@zabbix ~]# echo "innodb_force_recovery = 1" >> /etc/my.cnf
[root@zabbix ~]# service mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:                                            [FAILED]
[root@zabbix ~]# echo "innodb_force_recovery = 2" >> /etc/my.cnf
[root@zabbix ~]# service mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:                                            [FAILED]
[root@zabbix ~]# echo "innodb_force_recovery = 3" >> /etc/my.cnf
[root@zabbix ~]# service mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:                                            [FAILED]
[root@zabbix ~]# echo "innodb_force_recovery = 4" >> /etc/my.cnf
[root@zabbix ~]# service mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:                                            [FAILED]
[root@zabbix ~]# echo "innodb_force_recovery = 5" >> /etc/my.cnf
[root@zabbix ~]# service mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:                                            [FAILED]
[root@zabbix ~]# echo "innodb_force_recovery = 6" >> /etc/my.cnf
[root@zabbix ~]# service mysqld start
Starting MySQL:                                            [  OK  ]
[root@zabbix ~]# mysqldump -u root -pMinhaSenha mysql > mysql.sql
[root@zabbix ~]# mysqldump -u root -pMinhaSenha zabbix > zabbix.sql

Então eu desistalei o MySQL, removi todos seus arquivos de dados e de configurações, e então reinstalei a mesma versão, para depois importar os dumps criados.
Felizmente não perdemos nenhum dado com esta operação, mas a melhor estratégia é a correta: tenha Backup, e utilize Bin Logs.

Documentação innodb_force_recovery: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
Documentação mysqlcheck: http://dev.mysql.com/doc/refman/5.5/en/mysqlcheck.html

Migração de MySQL para PostgreSQL

 MySQL, PostgreSQL  No Responses »
out 072011
 

A migração de MySQL para PostgreSQL é facilmente (e rapidamente) alcançada com o script Perl mysql2pgsql.

http://pgfoundry.org/projects/mysql2pgsql/

Ele faz a conversão de um dump de MySQL (gerado pelo utilitário do MySQL mysqldump) em um arquivo com comandos SQL traduzidos para o PostgreSQL.

No exemplo abaixo, gerei o dump com o nome de mysql.sql, e pedi para gerar o arquivo para o PostgreSQL com o nome pg.sql.

Geração do dump em MySQL:

C:\temp>mysqldump -u admin -pMinhaSenha -h 192.168.1.11 -d zabbix > zabbix.sql

Tradução do dump:

C:\temp>perl mysql2pgsql.perl mysql.sql pg.sql
table "acknowledges" will be dropped CASCADE
table "actions" will be dropped CASCADE
table "alerts" will be dropped CASCADE
table "applications" will be dropped CASCADE
table "auditlog" will be dropped CASCADE
table "conditions" will be dropped CASCADE
table "config" will be dropped CASCADE
table "dchecks" will be dropped CASCADE
table "dhosts" will be dropped CASCADE
table "drules" will be dropped CASCADE
table "dservices" will be dropped CASCADE
table "escalations" will be dropped CASCADE
table "events" will be dropped CASCADE
table "functions" will be dropped CASCADE
table "graphs" will be dropped CASCADE
table "graphs_items" will be dropped CASCADE
table "groups" will be dropped CASCADE
table "help_items" will be dropped CASCADE
table "history" will be dropped CASCADE
table "history_log" will be dropped CASCADE
table "history_str" will be dropped CASCADE
table "history_str_sync" will be dropped CASCADE
table "history_sync" will be dropped CASCADE
table "history_text" will be dropped CASCADE
table "history_uint" will be dropped CASCADE
table "history_uint_sync" will be dropped CASCADE
table "hosts" will be dropped CASCADE
table "hosts_groups" will be dropped CASCADE
table "hosts_profiles" will be dropped CASCADE
table "hosts_profiles_ext" will be dropped CASCADE
table "hosts_templates" will be dropped CASCADE
table "housekeeper" will be dropped CASCADE
table "httpstep" will be dropped CASCADE
table "httpstepitem" will be dropped CASCADE
table "httptest" will be dropped CASCADE
table "httptestitem" will be dropped CASCADE
table "ids" will be dropped CASCADE
table "images" will be dropped CASCADE
table "items" will be dropped CASCADE
table "items_applications" will be dropped CASCADE
table "mappings" will be dropped CASCADE
table "media" will be dropped CASCADE
table "media_type" will be dropped CASCADE
table "node_cksum" will be dropped CASCADE
table "nodes" will be dropped CASCADE
table "opconditions" will be dropped CASCADE
table "operations" will be dropped CASCADE
table "profiles" will be dropped CASCADE
table "proxy_dhistory" will be dropped CASCADE
table "proxy_history" will be dropped CASCADE
table "rights" will be dropped CASCADE
table "screens" will be dropped CASCADE
table "screens_items" will be dropped CASCADE
table "scripts" will be dropped CASCADE
table "service_alarms" will be dropped CASCADE
table "services" will be dropped CASCADE
table "services_links" will be dropped CASCADE
table "services_times" will be dropped CASCADE
table "sessions" will be dropped CASCADE
table "slides" will be dropped CASCADE
table "slideshows" will be dropped CASCADE
table "sysmaps" will be dropped CASCADE
table "sysmaps_elements" will be dropped CASCADE
table "sysmaps_link_triggers" will be dropped CASCADE
table "sysmaps_links" will be dropped CASCADE
table "trends" will be dropped CASCADE
table "trends_uint" will be dropped CASCADE
table "trigger_depends" will be dropped CASCADE
table "triggers" will be dropped CASCADE
table "users" will be dropped CASCADE
table "users_groups" will be dropped CASCADE
table "usrgrp" will be dropped CASCADE
table "valuemaps" will be dropped CASCADE

Importação para o PostgreSQL:

-bash-3.2$ psql < pg.sql
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "acknowledges_pkey" for table "acknowledges"
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "actions_pkey" for table "actions"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "alerts_pkey" for table "alerts"
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "applications_pkey" for table "applications"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "applications_hostid_key" for table "applications"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "auditlog_pkey" for table "auditlog"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "conditions_pkey" for table "conditions"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "config_pkey" for table "config"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "dchecks_pkey" for table "dchecks"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "dhosts_pkey" for table "dhosts"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "drules_pkey" for table "drules"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "dservices_pkey" for table "dservices"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "escalations_pkey" for table "escalations"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "events_pkey" for table "events"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "functions_pkey" for table "functions"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "graphs_pkey" for table "graphs"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "graphs_items_pkey" for table "graphs_items"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "groups_pkey" for table "groups"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "help_items_pkey" for table "help_items"
CREATE TABLE
DROP TABLE
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "history_log_pkey" for table "history_log"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "history_log_itemid_key" for table "history_log"
CREATE TABLE
CREATE INDEX
DROP TABLE
CREATE TABLE
CREATE INDEX
DROP TABLE
DROP SEQUENCE
CREATE SEQUENCE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "history_str_sync_pkey" for table "history_str_sync"
CREATE TABLE
CREATE INDEX
DROP TABLE
DROP SEQUENCE
CREATE SEQUENCE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "history_sync_pkey" for table "history_sync"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "history_text_pkey" for table "history_text"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "history_text_itemid_key" for table "history_text"
CREATE TABLE
CREATE INDEX
DROP TABLE
CREATE TABLE
CREATE INDEX
DROP TABLE
DROP SEQUENCE
CREATE SEQUENCE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "history_uint_sync_pkey" for table "history_uint_sync"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "hosts_pkey" for table "hosts"
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "hosts_groups_pkey" for table "hosts_groups"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "hosts_profiles_pkey" for table "hosts_profiles"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "hosts_profiles_ext_pkey" for table "hosts_profiles_ext"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "hosts_templates_pkey" for table "hosts_templates"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "hosts_templates_hostid_key" for table "hosts_templates"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "housekeeper_pkey" for table "housekeeper"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "httpstep_pkey" for table "httpstep"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "httpstepitem_pkey" for table "httpstepitem"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "httpstepitem_httpstepid_key" for table "httpstepitem"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "httptest_pkey" for table "httptest"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "httptestitem_pkey" for table "httptestitem"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "httptestitem_httptestid_key" for table "httptestitem"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "ids_pkey" for table "ids"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "images_pkey" for table "images"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "items_pkey" for table "items"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "items_hostid_key" for table "items"
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "items_applications_pkey" for table "items_applications"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mappings_pkey" for table "mappings"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "media_pkey" for table "media"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "media_type_pkey" for table "media_type"
CREATE TABLE
DROP TABLE
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "nodes_pkey" for table "nodes"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "opconditions_pkey" for table "opconditions"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "operations_pkey" for table "operations"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "profiles_pkey" for table "profiles"
CREATE TABLE
CREATE INDEX
DROP TABLE
DROP SEQUENCE
CREATE SEQUENCE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "proxy_dhistory_pkey" for table "proxy_dhistory"
CREATE TABLE
CREATE INDEX
DROP TABLE
DROP SEQUENCE
CREATE SEQUENCE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "proxy_history_pkey" for table "proxy_history"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "rights_pkey" for table "rights"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "screens_pkey" for table "screens"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "screens_items_pkey" for table "screens_items"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "scripts_pkey" for table "scripts"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "service_alarms_pkey" for table "service_alarms"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "services_pkey" for table "services"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "services_links_pkey" for table "services_links"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "services_links_serviceupid_key" for table "services_links"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "services_times_pkey" for table "services_times"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sessions_pkey" for table "sessions"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "slides_pkey" for table "slides"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "slideshows_pkey" for table "slideshows"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sysmaps_pkey" for table "sysmaps"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sysmaps_elements_pkey" for table "sysmaps_elements"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sysmaps_link_triggers_pkey" for table "sysmaps_link_triggers"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "sysmaps_link_triggers_linkid_key" for table "sysmaps_link_triggers"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sysmaps_links_pkey" for table "sysmaps_links"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "trends_pkey" for table "trends"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "trends_uint_pkey" for table "trends_uint"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "trigger_depends_pkey" for table "trigger_depends"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "triggers_pkey" for table "triggers"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_groups_pkey" for table "users_groups"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "usrgrp_pkey" for table "usrgrp"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "valuemaps_pkey" for table "valuemaps"
CREATE TABLE
CREATE INDEX
-bash-3.2$

Benchmark MySQL: Windows x Linux

 MySQL  1 Response »
jun 082011
 

Já saiu a edição 88 da Revista SQL Magazine, com meu artigo sobre um teste de desempenho do MySQL, feito entre Windows Server 2008 R2 e CentOS 5.5.

 

O que a Oracle fará com o MySQL? Esta é uma pergunta que veio a tona desde a compra da Sun pela Oracle (que por sua vez, comprara anteriormente a MySQL AB), pois parece inapropriado que a empresa mantenha dentro de casa um concorrente ao seu produto principal. Muitas teorias apareceram desde então, e entre elas, que o MySQL levaria uma morte lenta e dolorosa, intencionalmente esquecido pelas equipes internas de suporte e manutenção deste SGBD até que se tornasse obsoleto.

Certamente esta teoria não prevaleceu, visto o anúncio da versão 5.5 do MySQL, com grandes melhorias de desempenho, escalabilidade e replicação. Além disso, a Oracle tem promovido diversos encontros de usuários, anúncios, palestras via internet e presenciais, e até colocou o MySQL como matéria de capa da última edição da Oracle Magazine.

Eu creio que este direcionamento da Oracle, de investimentos pesados no MySQL, continuará, pois acho que para a Oracle Corporation, o MySQL não é concorrente do Oracle Database – ele é concorrente do SQL Server.

Bem, para que o MySQL seja um concorrente aceitável para o SQL Server, ele deve ter excelente desempenho no Windows Server, também por facilidade de implementação. Foi para este fim que fiz este teste, para comparar o desempenho do MySQL em computadores com o sistema operacional Linux e com o Windows Server. Como as conexões do MySQL são feitas através de threads, ao invés de processos (como é no Oracle), faz sentido que sua escalabilidade seja maior no Windows do que no Linux, já que aquele trabalha nativamente com threads, e este com processos. Em minha opinião, teorias só podem ser sustentadas por fatos e dados, então veja o resultado deste Benchmark.

MySQL Performance Tuning Parte V – Quando não é posível fazer o Tuning

 MySQL  No Responses »
mai 062011
 

Já está nas bancas a revista SQL Magazine 86, onde encerro a série sobre MySQL Performance Diagnostics & Tuning. Mas não encerro o assunto…

O último recurso de Tuning é não fazê-lo. Neste último artigo da série, vamos além do servidor. O que acontece quando o hardware já está no seu limite de upgrade, todos os parâmetros do MySQL estão ajustados, os comandos SQL são performáticos, mas mesmo assim isto não é suficiente para o sistema?

Revista SQL Magazine 86

MySQL Performance Tuning Parte IV – Encontrando os SQLs agressores

 MySQL  No Responses »
mar 312011
 

Está nas bancas a quarta parte da série que escrevi para a Revista SQL Magazine sobre Performance Diagnostics & Tuning no MySQL.

Neste quarto artigo da série, iremos passar da análise do servidor e seus parâmetros para abordar a monitoração e correção de comandos SQL danosos ao sistema. Este tipo de análise não pode ser ignorada em nenhum ambiente, uma vez que, mesmo sabendo que os valores padrão das variáveis de sistema do MySQL não são adequados, a maior causa de lentidão em um banco de dados são comandos SQL mal escritos, ou que rodam sobre um design ineficiente. Eu costumo até brincar que o servidor MySQL com melhor desempenho é aquele que não executa nenhum comando SQL!

Este artigo não irá tratar do Tuning do SQL em si, mas sim sobre como encontrar os SQLs mais danosos ao sistema, com as ferramentas nativas do MySQL.

http://www.devmedia.com.br/post-20124-Revista-SQL-Magazine-85.html

No MySQL, gravações NÃO bloqueiam leituras

 MySQL  No Responses »
mar 032011
 

Recentemente eu estive em um cliente que queria fazer uma migração de MySQL para SQL Server. Esta migração poderia ser interessante se o motivo principal fosse utilizar alguma funcionalidade que o segundo RDBMS tem e o primeiro não – por exemplo, o excelente Database Snapshot. Mas o motivo era errado: o cliente queria migrar porque o MySQL estava lento.

Outra vantagem do SQL Server sobre o MySQL é que as configurações padrão do RDBMs da Microsoft são razoavelemte boas para muitos ambientes, e as do MySQL, horríveis para qualquer carga um pouco maior. Com algum Tuning, o desempenho do MySQL voltou a ser satisfatório para o cliente.

Mas a migração poderia ter sido desastrosa, por uma questão de Design entre as duas ferramentas que o cliente não esperava: no MySQL, por padrão, gravações não bloqueiam leituras (na verdade, é no InnoDB). Como no Oracle, uma leitura em uma tabela que seja iniciada após uma transação de alterações nesta mesma tabela por outra sessão, irá ler a imagem de dados anterior a esta transação. Veja que isto não é Dirty Read, pois os dados ainda não permanentes: não foi feito COMMIT.

SESSÃO 1
Microsoft Windows [versão 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. Todos os direitos reservados.
C:\Users\Ricardo>mysql -u root -pNerv2010
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> USE test;
Database changed
mysql> CREATE TABLE TABELA_TESTE (COLUNA_TESTE INT(10));
Query OK, 0 rows affected (0.53 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO TABELA_TESTE VALUES (1);
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO TABELA_TESTE VALUES (2);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO TABELA_TESTE VALUES (3);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT COUNT(*) FROM TABELA_TESTE;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.04 sec)
mysql>
SESSÃO 2
Microsoft Windows [versão 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. Todos os direitos reservados.
C:\Users\Ricardo>mysql -u root -pNerv2010
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.8 MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> USE test;
Database changed
mysql> SELECT COUNT(*) FROM TABELA_TESTE;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
mysql>

MySQL Performance Tuning Parte III – Tabelas Temporárias

 MySQL  No Responses »
jan 272011
 

Está nas bancas a terceira parte da série que escrevi para a Revista SQL Magazine sobre Performance Diagnostics & Tuning no MySQL.

Nesta terceira parte, abordo uma ferramenta gráfica para auxiliar na análise de desempenho, e customizá-la para analisar o sistema de tabelas temporárias implícitas do MySQL.

Nada evidencia mais um problema do que um gráfico, baseado em números reais. Muitos DBAs que conheço, especialmente os com maior experiência, são aversos ao uso de ferramentas gráficas, pois acham que estas existem para serem utilizadas pelos administradores menos experientes. Eu acho que, além de dominar as ferramentas de linha de comando – nem sempre temos as opções gráficas, ou mesmo uma conexão rápida o suficiente a nossa disposição – os DBAs mais experientes devem também dominar as ferramentas gráficas, pois além de fornecerem uma informação com uma melhor apresentação, podem aumentar a produtividade do administrador. Além disso, um gráfico é uma ferramenta melhor que simples números para convencer um cliente sobre a causa de um problema.

As tabelas temporárias implícitas do MySQL são controladas por dois parâmetros: tmp_table_size e max_heap_table_size. Se uma tabela temporária implícita – elas são necessárias em operações com SORT BY, ORDER BY, GROUP BY, criação de índices, etc – exceder o valor de um destes parâmetros, a tabela será criada em disco, e não na memória. Este é um ponto delicado da configuração do MySQL, e para isto precisamos do gráfico que criamos: não queremos que tabelas temporárias sejam criadas em disco, mas também não queremos deixar um limite muito grande para que elas sejam criadas em memória, e correr o risco de ter uma indisponibilidade no banco de dados por falta de recursos.

MySQL Performance Tuning Parte II – Acelerando Leituras

 MySQL  No Responses »
jan 032011
 

Já está nas bancas a segunda parte da série que escrevi sobre MySQL Performance Diagnostics & Tuning para a Revista SQL Magazine.

No primeiro artigo desta série, vimos como diminuir o tempo de gravações no MySQL e começamos a verificar como poderíamos melhorar as leituras, aumentando o tamanho do cache do engine do InnoDB. Nesta segunda parte, iremos abordar principalmente os parâmetros que devem ser alterados para um melhor desempenho em leituras, além de explicar algumas complicações sobre alteração de parâmetros no MySQL.

Novamente, a idéia do artigo não é simplesmente dizer que parâmetros devem ser alterados, mas sim levar ao leitor, passo a passo, a uma evolução da configuração para obter-se um melhor desempenho.

http://www.devmedia.com.br/post-18556-MySQL-Performance-Diagnostics–Tuning-Parte-II.html

Teste de Carga no MySQL

 MySQL  No Responses »
dez 082010
 

Seja para testar a implantação que você fez no cliente, para comparar hardware ou sistemas operacionais diferentes, ou mesmo para validar uma alteração de parâmetro no MySQL, o melhor caminho é comparar um teste de carga, ou seja, um Benchmark.

O programa mysqlslap, que vem junto com o MySQL, faz um Benchmark simples, de SELECT, INSERT, UPDATE, ou misto (o padrão, que eu uso neste teste).

Este programa aceita vários parâmetros para executar testes diferentes, e o mais importante, mede o TEMPO das execuções, a unica forma de saber se um SQL é melhor ou pior.

No exemplo abaixo, eu utilizei as opções para que fossem feitas 100 conexões concorrentes, com comandos auto gerados (você pode também testar seus próprios comandos SQL), com 10 execuções, em uma tabela com coluna INT com 5 dígitos, e com coluna CHAR com 10 caracteres, e apenas com o Engine InnoDB.

Para ver as outras opções do mysqlslap, verifique sua man page, que é bem completa.

[root@MySQL01 ~]# mysqlslap --user=ricardo --password=Nerv2010 --auto-generate-sql --concurrency=100 --iterations=10 --number-char-cols=10 --number-int-cols=5 --engine=innodb
 Benchmark
 Running for engine innodb
 Average number of seconds to run all queries: 8.946 seconds
 Minimum number of seconds to run all queries: 7.683 seconds
 Maximum number of seconds to run all queries: 10.674 seconds
 Number of clients running queries: 100
 Average number of queries per client: 0
[root@MySQL01 ~]#

		

	


	

	



	

		
InnoDB Hot Backup

		

					 MySQL
						 No Responses »
				

		

		
dez 072010

	

	 	

		


A ferramenta gratuita XtraBackup torna possível o Backup do MySQL com o Banco no ar, para tabelas que utilizam o Engine InnoDB.


Para as tabelas MyISAM, o backup é possível colocando as tabelas em estado Read Only (o que causa certa indisponibilidade, dependendo da aplicação), e simplesmente copiando os arquivos do diretório de dados. Para tabelas em InnoDB é mais complicado, pois mesmo se o banco estiver em Read Only, as informações da tablespace de dados do InnoDB (geralmente localizada no arquivo ibdata1) podem não estar consistentes, pois algumas informações ainda podem estar nos Logs, como em outros RDBMS transacionais.


O XtraBackup tem um funcionamento curioso para quem conhece outras ferramentas de Backup de RDBMS: os arquivos são copiados do diretório de dados (pelo comando backup), e depois os Logs são aplicados (pelo comando prepare) no próprio backup, tornando-o consistente. Desta forma, o Restore é apenas uma cópia dos arquivos de volta para o diretório de dados. Em outros RDBMS, o mais comum é que a aplicação de Logs seja feita após a restauração de dados, e e não após o backup.


http://www.percona.com/software/percona-xtrabackup/


[root@MySQL01 ~]# ls -lh backup/
 total 0
 [root@MySQL01 ~]# xtrabackup --backup --datadir=/var/lib/mysql/ --target-dir=/root/backup/
 xtrabackup  Ver 1.4 Rev 193 for 5.1.47 pc-linux-gnu (i686)
 xtrabackup: uses posix_fadvise().
 xtrabackup: cd to /var/lib/mysql/
 xtrabackup: Target instance is assumed as followings.
 xtrabackup:   innodb_data_home_dir = ./
 xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
 xtrabackup:   innodb_log_group_home_dir = ./
 xtrabackup:   innodb_log_files_in_group = 2
 xtrabackup:   innodb_log_file_size = 5242880
 >> log scanned up to (4255945249)
 Copying ./ibdata1
 to /root/backup//ibdata1
 ...done
 Copying ./test/t3.ibd
 to /root/backup//test/t3.ibd
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 ...done
 Copying ./test/t1.ibd
 to /root/backup//test/t1.ibd
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 ...done
 Copying ./test/t2.ibd
 to /root/backup//test/t2.ibd
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 >> log scanned up to (4255945249)
 ...done
 xtrabackup: The latest check point (for incremental): '4255945249'
 >> log scanned up to (4255945249)
 xtrabackup: Stopping log copying thread.
 xtrabackup: Transaction log of lsn (4255945249) to (4255945249) was copied.
 [root@MySQL01 ~]# ls -lh backup/
 total 35M
 -rw-r--r-- 1 root root  34M Dec  2 11:33 ibdata1
 drwx------ 2 root root 4.0K Dec  2 11:36 test
 -rw-r--r-- 1 root root   61 Dec  2 11:38 xtrabackup_checkpoints
 -rw-r--r-- 1 root root 2.5K Dec  2 11:38 xtrabackup_logfile
 [root@MySQL01 ~]# xtrabackup --prepare --datadir=/var/lib/mysql/ --target-dir=/root/backup/
 xtrabackup  Ver 1.4 Rev 193 for 5.1.47 pc-linux-gnu (i686)
 xtrabackup: cd to /root/backup/
 xtrabackup: This target seems to be not prepared yet.
 xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(4255945249)
 xtrabackup: Temporary instance for recovery is set as followings.
 xtrabackup:   innodb_data_home_dir = ./
 xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
 xtrabackup:   innodb_log_group_home_dir = ./
 xtrabackup:   innodb_log_files_in_group = 1
 xtrabackup:   innodb_log_file_size = 2097152
 xtrabackup: Starting InnoDB instance for recovery.
 xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
 InnoDB: The InnoDB memory heap is disabled
 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
 InnoDB: Compressed tables use zlib 1.2.3
 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
 101202 11:38:50  InnoDB: highest supported file format is Barracuda.
 101202 11:38:52 Percona XtraDB (http://www.percona.com) 1.0.8-11.2 started; log sequence number 4255945249


[notice (again)]
 If you use binary log and don't use any hack of group commit,
 the binary log position seems to be:


xtrabackup: starting shutdown with innodb_fast_shutdown = 1
 101202 11:38:52  InnoDB: Starting shutdown...
 101202 11:38:57  InnoDB: Shutdown completed; log sequence number 4255945249
 [root@MySQL01 ~]#
[root@MySQL01 ~]# ls -lh backup/
total 37M
-rw-r--r-- 1 root root  34M Dec  2 11:38 ibdata1
drwx------ 2 root root 4.0K Dec  2 11:36 test
-rw-r--r-- 1 root root   61 Dec  2 11:38 xtrabackup_checkpoints
-rw-r--r-- 1 root root 2.0M Dec  2 11:38 xtrabackup_logfile
[root@MySQL01 ~]#

		

	


	

	

		 Older Entries