Abaixo vou descrever uma forma de como poeríamos criar um script no Linux para pesquisar quais dados estão sendo menos utilizados no banco, desta forma, estes poeriam ser candidatos a popular uma tabela compactada melhorando a performance de consulta e economizando espaço em disco.
linux# grep "from " /var/log/mysql/mysqld.log | awk -Ffrom '{print $2}' | awk '{print $1}' | sort | uniq | cat > /var/lib/mysql/test/tablelist.txt
mysql> create table test.tableslog (tname varchar(250));
mysql> load data infile 'tablelist.txt' into table tableslog;
mysql> select t1.tname, t2.TABLE_NAME from test.tableslog as t1 LEFT join information_schema.TABLES as t2 on t1.tname = t2.TABLE_NAME group by t1.tname having t2.TABLE_NAME IS NULL
Espero que seja útil para vocês.
domingo, 29 de janeiro de 2012
Visual Explain para o MySQL
Existem muitas ferramentas para se utilizar juntamente com o MySQL
seja para a coleta de dados ou informações sobre o funcionamento do banco de dados e sistema operacional onde estes operam.
No MySQL temos um comando bastante útil chamado EXPLAIN, este comando
mostra quais os caminhos que uma query percorre antes que lhe seja retornado um resultset. O EXPLAIN é uma ótima forma de descobrirmos problemas com nossas queries como por exemplo consultas que estão executando uma varredura total de uma tabela ou de um arquivo de index entre outras utilidades que veremos mais adiante com mais detalhes.
O Visual Explain, aliás, antes que você se decepcione, este utilitário não tem nada de recursos gráficos, apenas mostra os resultados do comando EXPLAIN
de forma mais intuitiva na forma de árvores.
Segue link para download e mais informações do programa:
Download Visual Explain
seja para a coleta de dados ou informações sobre o funcionamento do banco de dados e sistema operacional onde estes operam.
No MySQL temos um comando bastante útil chamado EXPLAIN, este comando
mostra quais os caminhos que uma query percorre antes que lhe seja retornado um resultset. O EXPLAIN é uma ótima forma de descobrirmos problemas com nossas queries como por exemplo consultas que estão executando uma varredura total de uma tabela ou de um arquivo de index entre outras utilidades que veremos mais adiante com mais detalhes.
O Visual Explain, aliás, antes que você se decepcione, este utilitário não tem nada de recursos gráficos, apenas mostra os resultados do comando EXPLAIN
de forma mais intuitiva na forma de árvores.
Segue link para download e mais informações do programa:
Download Visual Explain
MySQL e o performance_schema
Nova versão do MySQL lançada e mais um recurso para facilitar a vida dos DBAs.
No MySQL já estamos acostumados a obter informações referentes a tabelas, bancos e características do servidor, usuários etc. Todas essas informações são conhecidas por metadatas e podem ser adquiridas executando queries nas tabelas de dois bancos principais; information_schema e mysql.
A partir da versão 5.5 mais precisamente 5.5.3-m3 temos outro banco, o performance_schema, aqui existem tabelas que podemos utilizar para obter informações de baixo nível referente a performance.
Foi adicionado também informações específicas ao funcionamento e performance do InnoDB.
Segue os links com mais informações no site oficial do mysql:
Estamos criando um artigo especialmente para explorar melhor este novo recurso do MySQL.
No MySQL já estamos acostumados a obter informações referentes a tabelas, bancos e características do servidor, usuários etc. Todas essas informações são conhecidas por metadatas e podem ser adquiridas executando queries nas tabelas de dois bancos principais; information_schema e mysql.
A partir da versão 5.5 mais precisamente 5.5.3-m3 temos outro banco, o performance_schema, aqui existem tabelas que podemos utilizar para obter informações de baixo nível referente a performance.
Foi adicionado também informações específicas ao funcionamento e performance do InnoDB.
Segue os links com mais informações no site oficial do mysql:
Estamos criando um artigo especialmente para explorar melhor este novo recurso do MySQL.
MySQL e o Query Cache
Uma forma bem simples e rápida de melhorar significantemente a performance de consultas no MySQL é saber utilizar corretamente o cache de resultados de suas consultas.
O query cache basicamente é um local na memória onde o MySQL armazena o texto de sua consulta "SELECT" juntamente com o resultado retornado por esta consulta, teoricamente, sempre que esta consulta for executada novamente não será necessário o mysqld fazer todo o trabalho para obter os dados resultantes sendo que estes já estarão disponíveis na memória.
Vamos aprender a utilizar o query cache, em primeiro lugar precisamos saber se está disponível, para isso vamos entrar o comando:
mysql> SELECT @@have_query_cache;
+--------------------+
| @@have_query_cache |
+--------------------+
| YES |
+--------------------+
1 row in set (0.00 sec)
Bem, isso já nos diz que o query cache está disponível.
Veremos agora como ele está definido:
mysql> show variables like '%query_cache%';
+------------------------------+--------+
| Variable_name | Value |
+------------------------------+--------+
| have_query_cache | YES |
| query_cache_limit | 524288 |
| query_cache_min_res_unit | 2048 |
| query_cache_size | 128000 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+--------+
6 rows in set (0.00 sec)
O query_cache_size, nos diz o tamanho em bytes reservado para o query cache, neste caso 128Mb
O query_cache_type irá informar o tipo de cacheamento que estamos utilizando, ele pode ser:
0 ou OFF; Não armazena nem busca resultados do query cache
1 ou ON; Armazena no cache o resultado de todas as consultas válidas para o cacheamento, exceto quando seguidas pela cláusula SQL_NO_CACHE
2 ou DEMAND; Armazena apenas os resultados de consultas válidas e seguidas por SQL_CACHE
O query cache em ação:
Em primeiro lugar se o query_cache_size for 0 então vamos definir seu valor e conseqüentemente será habilitado.
mysql> SET GLOBAL query_cache_size=128000;
O que acabamos de fazer ?
Definimos nosso query cache para ter 128 Mb, isso quer dizer que teremos todo este espaço disponível para armazenar o resultado de nossas consultas. Esse valor escolhi apenas para testes e não significa que seja o valor ideal.
Vamos utilizar uma consulta simples para testar o query cache, estou utilizando um banco de dados específico que em breve estarei disponibilizando aqui no blog, por enquanto veja o resultado:
1- Primeira vez que executo meu SELECT com o query cache habilitado
mysql> SELECT pedido.id, pedido.data, cliente.nome, cliente.sobrenome, cliente.email FROM pedido, cliente WHERE cliente.id=pedido.cliente_id;
............
100 rows in set (0.11 sec)
Suprimi as linhas para economizar o espaço e o SELECT está bem intuitivo e básico para que você entenda como estamos pesquisando.
O resultado foi retornado em 0.11 segundos
2- Vamos executar novamente o mesmo SELECT e observar quanto tempo irá demorar para sair o resultado.
100 rows in set (0.00 sec)
Ótimo. Agora a mesma consulta demorou 0.00 segundos mas o que aconteceu ?
Bem, isso é o nosso query cache trabalhando para armazenar o resultado de nossa consulta, como foi dito antes, na segunda vez que executei o mesmo SELECT o resultado foi retirado diretamente do query cache e não foi necessário nenhuma execução interna para obte-los.
Vamos analisar algumas variáveis referentes ao query cache e aprender mais sobre elas.
Digite o comando:
root@localhost livraria> SHOW STATUS LIKE 'Qc%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 112400 |
| Qcache_hits | 1 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 6 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 5 |
+-------------------------+--------+
8 rows in set (0.01 sec)
É importante observar que em Qcache_hits está mostrando quantas vezes utilizamos o conteúdo que está no query cache, e em Qcache_inserts temos a quantidades de resultados que foram inseridos em nosso cache, a cada nova query válida para para preencher o query cache este valor é incrementado.
Ao executar novamente a mesma consulta observamos que nosso Qcache_hits é incrementado.
Quando estamos utilizando o query cache temos que observar também o valor contido em Qcache_lowmem_prunes, esse valor quanto mais próximo de zero mais satisfatório para o bom uso do query cache. Essa variável é responsável por indicar quantas consultas foram "deletadas" do query cache devido a baixa memória definida.
O que não vai para o meu query cache?
1- Prepared Statements
2- Subqueries que se originam de uma OUTER query
3- Queries executadas dentro do corpo de uma função ou triggers
4- Todas as queries que utilize NO_CACHE como por exemplo, SELECT .... FROM .... NO_CACHE;
5- Resultados retornados por funções como SELECT NOW(), BENCHMARK etc.
Outra observação sobre o query cache é que a forma como o MySQL trara o query cache é totalmente case sensitive, ou seja, uma query SELECT * FROM LIVROS, é totalmente diferente de uma query Select * from LiVrOS e assim por diante.
O quanto devo reservar para meu query cache?
Isso irá depender de alguns fatores como: quanto de memória eu tenho no equipamento ? quantas consultas estou utilizando que serão executadas repetidamente ?
No próximo artigo veremos mais detalhes sobre o query cache e como definí-lo corretamente. Até lá.
O query cache basicamente é um local na memória onde o MySQL armazena o texto de sua consulta "SELECT" juntamente com o resultado retornado por esta consulta, teoricamente, sempre que esta consulta for executada novamente não será necessário o mysqld fazer todo o trabalho para obter os dados resultantes sendo que estes já estarão disponíveis na memória.
Vamos aprender a utilizar o query cache, em primeiro lugar precisamos saber se está disponível, para isso vamos entrar o comando:
mysql> SELECT @@have_query_cache;
+--------------------+
| @@have_query_cache |
+--------------------+
| YES |
+--------------------+
1 row in set (0.00 sec)
Bem, isso já nos diz que o query cache está disponível.
Veremos agora como ele está definido:
mysql> show variables like '%query_cache%';
+------------------------------+--------+
| Variable_name | Value |
+------------------------------+--------+
| have_query_cache | YES |
| query_cache_limit | 524288 |
| query_cache_min_res_unit | 2048 |
| query_cache_size | 128000 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+--------+
6 rows in set (0.00 sec)
O query_cache_size, nos diz o tamanho em bytes reservado para o query cache, neste caso 128Mb
O query_cache_type irá informar o tipo de cacheamento que estamos utilizando, ele pode ser:
0 ou OFF; Não armazena nem busca resultados do query cache
1 ou ON; Armazena no cache o resultado de todas as consultas válidas para o cacheamento, exceto quando seguidas pela cláusula SQL_NO_CACHE
2 ou DEMAND; Armazena apenas os resultados de consultas válidas e seguidas por SQL_CACHE
O query cache em ação:
Em primeiro lugar se o query_cache_size for 0 então vamos definir seu valor e conseqüentemente será habilitado.
mysql> SET GLOBAL query_cache_size=128000;
O que acabamos de fazer ?
Definimos nosso query cache para ter 128 Mb, isso quer dizer que teremos todo este espaço disponível para armazenar o resultado de nossas consultas. Esse valor escolhi apenas para testes e não significa que seja o valor ideal.
Vamos utilizar uma consulta simples para testar o query cache, estou utilizando um banco de dados específico que em breve estarei disponibilizando aqui no blog, por enquanto veja o resultado:
1- Primeira vez que executo meu SELECT com o query cache habilitado
mysql> SELECT pedido.id, pedido.data, cliente.nome, cliente.sobrenome, cliente.email FROM pedido, cliente WHERE cliente.id=pedido.cliente_id;
............
100 rows in set (0.11 sec)
Suprimi as linhas para economizar o espaço e o SELECT está bem intuitivo e básico para que você entenda como estamos pesquisando.
O resultado foi retornado em 0.11 segundos
2- Vamos executar novamente o mesmo SELECT e observar quanto tempo irá demorar para sair o resultado.
100 rows in set (0.00 sec)
Ótimo. Agora a mesma consulta demorou 0.00 segundos mas o que aconteceu ?
Bem, isso é o nosso query cache trabalhando para armazenar o resultado de nossa consulta, como foi dito antes, na segunda vez que executei o mesmo SELECT o resultado foi retirado diretamente do query cache e não foi necessário nenhuma execução interna para obte-los.
Vamos analisar algumas variáveis referentes ao query cache e aprender mais sobre elas.
Digite o comando:
root@localhost livraria> SHOW STATUS LIKE 'Qc%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 112400 |
| Qcache_hits | 1 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 6 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 5 |
+-------------------------+--------+
8 rows in set (0.01 sec)
É importante observar que em Qcache_hits está mostrando quantas vezes utilizamos o conteúdo que está no query cache, e em Qcache_inserts temos a quantidades de resultados que foram inseridos em nosso cache, a cada nova query válida para para preencher o query cache este valor é incrementado.
Ao executar novamente a mesma consulta observamos que nosso Qcache_hits é incrementado.
Quando estamos utilizando o query cache temos que observar também o valor contido em Qcache_lowmem_prunes, esse valor quanto mais próximo de zero mais satisfatório para o bom uso do query cache. Essa variável é responsável por indicar quantas consultas foram "deletadas" do query cache devido a baixa memória definida.
O que não vai para o meu query cache?
1- Prepared Statements
2- Subqueries que se originam de uma OUTER query
3- Queries executadas dentro do corpo de uma função ou triggers
4- Todas as queries que utilize NO_CACHE como por exemplo, SELECT .... FROM .... NO_CACHE;
5- Resultados retornados por funções como SELECT NOW(), BENCHMARK etc.
Outra observação sobre o query cache é que a forma como o MySQL trara o query cache é totalmente case sensitive, ou seja, uma query SELECT * FROM LIVROS, é totalmente diferente de uma query Select * from LiVrOS e assim por diante.
O quanto devo reservar para meu query cache?
Isso irá depender de alguns fatores como: quanto de memória eu tenho no equipamento ? quantas consultas estou utilizando que serão executadas repetidamente ?
No próximo artigo veremos mais detalhes sobre o query cache e como definí-lo corretamente. Até lá.
Particionamento no MySQL parte I
Bem, após o grande bla bla bla sobre o futuro do MySQL o nosso bom e parrudo gerenciador de banco de dados ainda sobrevive, O MySQL 5.5 trouxe novos recursos e correções de alguns bugs existentes nas versões anteriores, um dos novos recursos é forma como o MySQL trata o particionamento agora mais flexível e intuitivo como aprenderemos a seguir.
Em primeiro lugar para quem nunca utilizou o particionamento de tabelas no MySQL saiba que isso é possível, podemos dividir em várias outras "tabelas lógicas" uma única tabela, porém antes do lançamento da versão 5.5 estávamos limitados ao particionamento apenas por campos numéricos se fosse necessário o uso de campos date ou strings tinhamos que lançar mão de funções e converter cada um dos tipos tratados, isso agora mudou. Vamos a um exemplo:
CREATE DATABASE part_exemplo; USE part_exemplo;
CREATE TABLE despesas (
data DATE NOT NULL,
categoria VARCHAR(30),
valor DECIMAL(10,2)
);
Após incluirmos alguns registros, poderíamos criar o particionamento já na criação da tabela, mas vamos fazer isso com esta já pronta e com alguns dados inseridos.
INSERT INTO despesas VALUES( '2010-01-03', 'alimentação', 500.50);
INSERT INTO despesas VALUES( '2010-01-04', 'combustível', 30);
INSERT INTO despesas VALUES( '2010-01-05', 'hospedagem', 210.50);
INSERT INTO despesas VALUES( '2010-01-06', 'comunicacao', 500.50);
INSERT INTO despesas VALUES( '2010-01-07', 'compra', 600.00);
INSERT INTO despesas VALUES( '2010-01-08', 'vestimenta', 300.30);
INSERT INTO despesas VALUES( '2010-01-09', 'manutenção', 120.50);
INSERT INTO despesas VALUES( '2010-01-10', 'software', 700.90);
Agora vamos criar a partição baseada nas colunas e utilizando o tipo LIST
ALTER TABLE despesas
PARTITION BY LIST COLUMNS (categoria)
(
PARTITION p0 VALUES IN ('alimentacao', 'combustivel'),
PARTITION p1 VALUES IN ('hospedagem', 'comunicacao'),
PARTITION p2 VALUES IN ('compra', 'vestimenta'),
PARTITION p3 VALUES IN ('manutencao', 'software')
);
Vejamos o resultado do nosso particionamente.
mysql> explain partitions select * from despesas\G
Esta query irá retornar se nossas partições estão criadas devidamente. Se você está seguindo nosso exemplo aqui terá que obter o seguinte resultado:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: despesas
partitions: p0,p1,p2,p3
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8
Extra:
1 row in set (0.00 sec)
Ótimo! Não precisamos mais ter que usar complexas funções para converter dados do tipo não numéricos para utilizar o particionamento.
Nos próximos posts aprenderemos como utilizar colunas DATE nesta nova versão do MySQL 5.5 bem como vários novos recursos implementados. Até lá.
Em primeiro lugar para quem nunca utilizou o particionamento de tabelas no MySQL saiba que isso é possível, podemos dividir em várias outras "tabelas lógicas" uma única tabela, porém antes do lançamento da versão 5.5 estávamos limitados ao particionamento apenas por campos numéricos se fosse necessário o uso de campos date ou strings tinhamos que lançar mão de funções e converter cada um dos tipos tratados, isso agora mudou. Vamos a um exemplo:
CREATE DATABASE part_exemplo; USE part_exemplo;
CREATE TABLE despesas (
data DATE NOT NULL,
categoria VARCHAR(30),
valor DECIMAL(10,2)
);
Após incluirmos alguns registros, poderíamos criar o particionamento já na criação da tabela, mas vamos fazer isso com esta já pronta e com alguns dados inseridos.
INSERT INTO despesas VALUES( '2010-01-03', 'alimentação', 500.50);
INSERT INTO despesas VALUES( '2010-01-04', 'combustível', 30);
INSERT INTO despesas VALUES( '2010-01-05', 'hospedagem', 210.50);
INSERT INTO despesas VALUES( '2010-01-06', 'comunicacao', 500.50);
INSERT INTO despesas VALUES( '2010-01-07', 'compra', 600.00);
INSERT INTO despesas VALUES( '2010-01-08', 'vestimenta', 300.30);
INSERT INTO despesas VALUES( '2010-01-09', 'manutenção', 120.50);
INSERT INTO despesas VALUES( '2010-01-10', 'software', 700.90);
Agora vamos criar a partição baseada nas colunas e utilizando o tipo LIST
ALTER TABLE despesas
PARTITION BY LIST COLUMNS (categoria)
(
PARTITION p0 VALUES IN ('alimentacao', 'combustivel'),
PARTITION p1 VALUES IN ('hospedagem', 'comunicacao'),
PARTITION p2 VALUES IN ('compra', 'vestimenta'),
PARTITION p3 VALUES IN ('manutencao', 'software')
);
Vejamos o resultado do nosso particionamente.
mysql> explain partitions select * from despesas\G
Esta query irá retornar se nossas partições estão criadas devidamente. Se você está seguindo nosso exemplo aqui terá que obter o seguinte resultado:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: despesas
partitions: p0,p1,p2,p3
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8
Extra:
1 row in set (0.00 sec)
Ótimo! Não precisamos mais ter que usar complexas funções para converter dados do tipo não numéricos para utilizar o particionamento.
Nos próximos posts aprenderemos como utilizar colunas DATE nesta nova versão do MySQL 5.5 bem como vários novos recursos implementados. Até lá.
MySQL Slap: Batendo forte em nosso banco de dados
Continuando com nossa análise sobre teste de desempenho no MySQL vamos aprender hoje a utilizar uma ferramenta bastante poderosa, o mysqlslap, com ele poderemos executar várias tarefas, utilizar vários usuários ao mesmo tempo simulando um load casual de um servidor. Vamos lá.
O mysqlslap é distribuído juntamente com a instalação do mysql, geralmente se você não encontrá-lo no diretório padrão tente fazer o download de alguma distribuição com o título "noinstall".
Se sua instalação MySQL está completa e foi feita de forma correta com todos os PATHs e diretórios padrão basta digitar mysqlslap e pressionar ENTER e serão mostrados os resultados de execução.
shell> mysqlslap --user=bench_user --password --auto-generate-sql
Enter password:
Benchmark
Average number of seconds to run all queries: 0.035 seconds
Minimum number of seconds to run all queries: 0.035 seconds
Maximum number of seconds to run all queries: 0.035 seconds
Number of clients running queries: 1
Average number of queries per client: 0
Vamos a um exemplo mais complexo, para isto vou criar uma tabela utilizando a maioria dos tipos de dados existentes no MySQL. Segue os comandos abaixo:
/* Cria o banco de dados */
CREATE DATABASE bench;
/* Cria a tabela */
CREATE TABLE bench (
id int not null auto_increment primary key,
c1 int,
c2 CHAR(30),
c3 VARCHAR(30),
c4 FLOAT(10,2),
c5 DECIMAL(10,2),
c6 TEXT
) ENGINE=MyISAM;
Bem, essa será nossa tabela de testes, após a criação do banco de dados e da tabela descrita acima vamos criar um arquivo .SQL para incluir uma query com INSERTs nesta tabela.
INSERT INTO bench.bench VALUES(null,100000,'AA','AA',789.89,789.89,'TEXTO AQUI');
Insira os comandos acima em um arquivo e grave-o como bench.sql, lembre-se que apenas será executado apenas um comando por linha, na query acima você pode incluir o tamanho máximo de cada tipo criado, quis deixar assim para facilitar o exemplo.
Agora vamos utilizar o que criamos acima para executar com o mysqlslap.
mysqlslap --user=user_bench -p --concurrency=10 --iterations=10 --query=bench.sql
Vamos entender os parâmetros acima:
--user e -p -> usuário e senha que será utilizado para performar o teste, neste caso deve ter as permissões INSERT na tabela em questão.
--concurrency -> a quantidade de conexões simultâneas que serão utilizadas para executar a query expecificada, por exemplo, neste caso estaremos fazendo com que 10 conexões executem cada uma 10 vezes a mesma query.
--iterations -> a quantidade de queries que serão executadas para cada conexão especificada em concurrency
Após pressionar ENTER obteremos o resultado do nosso teste, claro que em seu servidor os valores poderão ser diferentes.
Benchmark
Average number of seconds to run all queries: 0.125 seconds
Minimum number of seconds to run all queries: 0.124 seconds
Maximum number of seconds to run all queries: 0.127 seconds
Number of clients running queries: 10
Average number of queries per client: 10
Abaixo vamos ver mais algumas opções interessantes para utilizar com o mysqlslap:
--auto-generate-sql -> gera automaticamente um query para execução
--auto-generate-sql-add-auto-increment -> gera automaticamente tabelas que contém campos do tipo AUTO_INCREMENT
--burnin -> executa os testes no modo burn-in, ou seja, executa todos os parâmetros especificados de forma ininterrupta, implementado no MySQL 6.0
--engine -> utiliza um determinado ENGINE para executar os testes
Bem, aqui você já tem uma idéia de como utilizar o mysqlslap para testar suas queries e o desempenho do seu servidor. Para saber mais parâmetros sobre o mysqlslap utilize mysqlslap --help
Até a próxima
O mysqlslap é distribuído juntamente com a instalação do mysql, geralmente se você não encontrá-lo no diretório padrão tente fazer o download de alguma distribuição com o título "noinstall".
Se sua instalação MySQL está completa e foi feita de forma correta com todos os PATHs e diretórios padrão basta digitar mysqlslap e pressionar ENTER e serão mostrados os resultados de execução.
shell> mysqlslap --user=bench_user --password --auto-generate-sql
Enter password:
Benchmark
Average number of seconds to run all queries: 0.035 seconds
Minimum number of seconds to run all queries: 0.035 seconds
Maximum number of seconds to run all queries: 0.035 seconds
Number of clients running queries: 1
Average number of queries per client: 0
Vamos a um exemplo mais complexo, para isto vou criar uma tabela utilizando a maioria dos tipos de dados existentes no MySQL. Segue os comandos abaixo:
/* Cria o banco de dados */
CREATE DATABASE bench;
/* Cria a tabela */
CREATE TABLE bench (
id int not null auto_increment primary key,
c1 int,
c2 CHAR(30),
c3 VARCHAR(30),
c4 FLOAT(10,2),
c5 DECIMAL(10,2),
c6 TEXT
) ENGINE=MyISAM;
Bem, essa será nossa tabela de testes, após a criação do banco de dados e da tabela descrita acima vamos criar um arquivo .SQL para incluir uma query com INSERTs nesta tabela.
INSERT INTO bench.bench VALUES(null,100000,'AA','AA',789.89,789.89,'TEXTO AQUI');
Insira os comandos acima em um arquivo e grave-o como bench.sql, lembre-se que apenas será executado apenas um comando por linha, na query acima você pode incluir o tamanho máximo de cada tipo criado, quis deixar assim para facilitar o exemplo.
Agora vamos utilizar o que criamos acima para executar com o mysqlslap.
mysqlslap --user=user_bench -p --concurrency=10 --iterations=10 --query=bench.sql
Vamos entender os parâmetros acima:
--user e -p -> usuário e senha que será utilizado para performar o teste, neste caso deve ter as permissões INSERT na tabela em questão.
--concurrency -> a quantidade de conexões simultâneas que serão utilizadas para executar a query expecificada, por exemplo, neste caso estaremos fazendo com que 10 conexões executem cada uma 10 vezes a mesma query.
--iterations -> a quantidade de queries que serão executadas para cada conexão especificada em concurrency
Após pressionar ENTER obteremos o resultado do nosso teste, claro que em seu servidor os valores poderão ser diferentes.
Benchmark
Average number of seconds to run all queries: 0.125 seconds
Minimum number of seconds to run all queries: 0.124 seconds
Maximum number of seconds to run all queries: 0.127 seconds
Number of clients running queries: 10
Average number of queries per client: 10
Abaixo vamos ver mais algumas opções interessantes para utilizar com o mysqlslap:
--auto-generate-sql -> gera automaticamente um query para execução
--auto-generate-sql-add-auto-increment -> gera automaticamente tabelas que contém campos do tipo AUTO_INCREMENT
--burnin -> executa os testes no modo burn-in, ou seja, executa todos os parâmetros especificados de forma ininterrupta, implementado no MySQL 6.0
--engine -> utiliza um determinado ENGINE para executar os testes
Bem, aqui você já tem uma idéia de como utilizar o mysqlslap para testar suas queries e o desempenho do seu servidor. Para saber mais parâmetros sobre o mysqlslap utilize mysqlslap --help
Até a próxima
MariaDB: Transacional ou não ?
Para criar uma tabela no MariaDB podemos fazer como segue:
CREATE TABLE t1 (c1 VARCHAR(20)) TRANSACTIONAL=1 ENGINE=Maria;
Espere! O que o parâmetro TRANSACTIONAL estám fazendo ai ?
Bem, na atual versão estável 5.1.44 até a data que escrevo este artigo o storage Maria ainda não é transacional, mas quando criar suas tabelas pode habilitar o parâmetro TRANSACTIONAL=1 pois em um futuro próximo como o próprio criador do software diz em seu site será implementado o suporte a transações no Maria sendo assim quando estiver tudo pronto e o Maria se tornar cem por cento transacional você não precisará alterar tabela por tabela para habilitar este recurso.
Para saber mais detalhes sobre o Maria acesse o site http://askmonty.org/wiki/Maria
Em breve estarei escrevendo um pouco mais sobre os principais storages utilizados no Maria e suas capacidades. Até lá.
CREATE TABLE t1 (c1 VARCHAR(20)) TRANSACTIONAL=1 ENGINE=Maria;
Espere! O que o parâmetro TRANSACTIONAL estám fazendo ai ?
Bem, na atual versão estável 5.1.44 até a data que escrevo este artigo o storage Maria ainda não é transacional, mas quando criar suas tabelas pode habilitar o parâmetro TRANSACTIONAL=1 pois em um futuro próximo como o próprio criador do software diz em seu site será implementado o suporte a transações no Maria sendo assim quando estiver tudo pronto e o Maria se tornar cem por cento transacional você não precisará alterar tabela por tabela para habilitar este recurso.
Para saber mais detalhes sobre o Maria acesse o site http://askmonty.org/wiki/Maria
Em breve estarei escrevendo um pouco mais sobre os principais storages utilizados no Maria e suas capacidades. Até lá.
Captura de queries lentas
No MariaDB as informações sobre as queries lentas estão mais completas e nos possibilitando mais opções para seu controle, a seguir veremos o que há de novo no MariaDB e suas diferenças no MySQL.
Em primeiro lugar vamos visualizar quatro variáveis que serão responsáveis por auxiliar no log das queries lentas, em seu cliente digite os seguintes comandos.
mysql> select @@log_slow_verbosity;
mysql> select @@log_slow_filter;
mysql> select @@log_slow_rate_limit;
Estas são as quatro variáveis que devemos definir de acordo com as explicações abaixo e de acordo com a necessidade de filtragem das queries lentas:
log_slow_verbosity; a verbosidade de informações que você deseja que seja logado quando ocorrer uma querie lenta.
Os valores para log_slow_verbosity podem ser: Query_plan, para o log sobre o planejamento de execução da query, isto inclui
* Full_scan,
* Full_join,
* Tmp_table,
* Tmp_table_on_disk,
* Filesort,
* Filesort_on_disk
* Merge_passes during sorting
Múltiplos valores podem ser usados e separados com (,) (vírgula).
O valor padrão é vazio para manter a compatibilidade com o MySQL 5.1
log_slow_filter; você pode definir quais queries irão para o log de queries lentas definindo os valores de acordo com a seguir:
* admin; irá logar comandos administrativos como CREATE, OPTIMIZE, DROP etc.
* filesort; se usar filesort
* filesort_on_disk; caso o comando use filesort que necessite de tabelas temporárias no disco
* full_join; loga comandos que não use index em tabelas pertencentes ao JOIN
* full_scan; quando for utilizado o escaneamento total das tabelas, full scan table
* query_cache; loga comandos que serão resolvidos pelo query cache
* query_cache_miss; loga comandos que não são resolvidos pelo query cache
* tmp_table;loga comandos que utilizam tabelas temporárias na memória
* tmp_table_on_disk; loga comandos que utilizam tabelas temporárias no disco
log_slow_rate_limit; limita a quantidade de queries que serão logadas, quando 1 apenas uma query será logada,
isso facilita no momento de debugging evitando muita informação quando não necessária.
Vamos então a um exemplo de como ficará nosso arquivo de log executando uma query que leve mais de 1 segundos (propositadamente) para liberar o resultado.
O primeiro passo é ativar nosso log de queries lentas com o seguinte comando:
SET GLOBAL log_slow_queries=1;
Em seguida definir nossa variável long_query_time como a seguir:
SET long_query_time=1;
Vamos definir as novas variáveis no MariaDB que acabamos de ver:
mysql> set log_slow_verbosity='query_plan';
Query OK, 0 rows affected (0.00 sec)
mysql> set log_slow_filter='full_scan';
Query OK, 0 rows affected (0.01 sec)
Em seguida utilizando o banco de dados world, o mais famoso para testes no MySQL iremos executar a seguinte query:
SELECT p.Code AS `Cód País`, p.Name AS `Nome País`,
(SELECT MAX(population) FROM City WHERE p.Code = countrycode) AS `Populacao`,
(SELECT name FROM City WHERE p.Code = countrycode ORDER BY Population DESC LIMIT 1) AS `Cidade Mais Populosa`,
l.Language AS `Língua Oficial`,
l.Percentage AS `%`
FROM Country AS p
JOIN CountryLanguage AS l ON p.Code = l.CountryCode AND l.isofficial = 'T'
WHERE p.Code IN(SELECT Code FROM Country WHERE (Continent='Asia' OR Continent='Europe')
AND GovernmentForm = 'Republic')
ORDER BY Continent;
Após o resultado podemos verificar o que foi gravado em nosso arquivo de log gravado no diretório escolhido para gravar os logs de queries lentas:
TCP Port: 3306, Named Pipe: MySQL
Time Id Command Argument
# Time: 100614 17:14:17
# User@Host: root[root] @ localhost [127.0.0.1]
# Thread_id: 1 Schema: world QC_hit: No
# Query_time: 1.872003 Lock_time: 0.000000 Rows_sent: 62 Rows_examined: 507204
# Full_scan: Yes Full_join: No Tmp_table: Yes Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0
use world;
SET timestamp=1276546457;
SELECT p.Code AS `C¢d Pa¡s`, p.Name AS `Nome Pa¡s`,
(SELECT MAX(population) FROM City WHERE p.Code = countrycode) AS `Populacao`,
(SELECT name FROM City WHERE p.Code = countrycode ORDER BY Population DESC LIMIT 1) AS `Cidade Mais Populosa`,
l.Language AS `L¡ngua Oficial`,
l.Percentage AS `%`
FROM Country AS p
JOIN CountryLanguage AS l ON p.Code = l.CountryCode AND l.isofficial = 'T'
WHERE p.Code IN(SELECT Code FROM Country WHERE (Continent='Asia' OR Continent='Europe')
AND GovernmentForm = 'Republic')
ORDER BY Continent;
Bem, esta é uma forma de sabermos qual query está lenta em nosso sistema com mais recursos agora no MariaDB. Atá a próxima.
Em primeiro lugar vamos visualizar quatro variáveis que serão responsáveis por auxiliar no log das queries lentas, em seu cliente digite os seguintes comandos.
mysql> select @@log_slow_verbosity;
mysql> select @@log_slow_filter;
mysql> select @@log_slow_rate_limit;
Estas são as quatro variáveis que devemos definir de acordo com as explicações abaixo e de acordo com a necessidade de filtragem das queries lentas:
log_slow_verbosity; a verbosidade de informações que você deseja que seja logado quando ocorrer uma querie lenta.
Os valores para log_slow_verbosity podem ser: Query_plan, para o log sobre o planejamento de execução da query, isto inclui
* Full_scan,
* Full_join,
* Tmp_table,
* Tmp_table_on_disk,
* Filesort,
* Filesort_on_disk
* Merge_passes during sorting
Múltiplos valores podem ser usados e separados com (,) (vírgula).
O valor padrão é vazio para manter a compatibilidade com o MySQL 5.1
log_slow_filter; você pode definir quais queries irão para o log de queries lentas definindo os valores de acordo com a seguir:
* admin; irá logar comandos administrativos como CREATE, OPTIMIZE, DROP etc.
* filesort; se usar filesort
* filesort_on_disk; caso o comando use filesort que necessite de tabelas temporárias no disco
* full_join; loga comandos que não use index em tabelas pertencentes ao JOIN
* full_scan; quando for utilizado o escaneamento total das tabelas, full scan table
* query_cache; loga comandos que serão resolvidos pelo query cache
* query_cache_miss; loga comandos que não são resolvidos pelo query cache
* tmp_table;loga comandos que utilizam tabelas temporárias na memória
* tmp_table_on_disk; loga comandos que utilizam tabelas temporárias no disco
log_slow_rate_limit; limita a quantidade de queries que serão logadas, quando 1 apenas uma query será logada,
isso facilita no momento de debugging evitando muita informação quando não necessária.
Vamos então a um exemplo de como ficará nosso arquivo de log executando uma query que leve mais de 1 segundos (propositadamente) para liberar o resultado.
O primeiro passo é ativar nosso log de queries lentas com o seguinte comando:
SET GLOBAL log_slow_queries=1;
Em seguida definir nossa variável long_query_time como a seguir:
SET long_query_time=1;
Vamos definir as novas variáveis no MariaDB que acabamos de ver:
mysql> set log_slow_verbosity='query_plan';
Query OK, 0 rows affected (0.00 sec)
mysql> set log_slow_filter='full_scan';
Query OK, 0 rows affected (0.01 sec)
Em seguida utilizando o banco de dados world, o mais famoso para testes no MySQL iremos executar a seguinte query:
SELECT p.Code AS `Cód País`, p.Name AS `Nome País`,
(SELECT MAX(population) FROM City WHERE p.Code = countrycode) AS `Populacao`,
(SELECT name FROM City WHERE p.Code = countrycode ORDER BY Population DESC LIMIT 1) AS `Cidade Mais Populosa`,
l.Language AS `Língua Oficial`,
l.Percentage AS `%`
FROM Country AS p
JOIN CountryLanguage AS l ON p.Code = l.CountryCode AND l.isofficial = 'T'
WHERE p.Code IN(SELECT Code FROM Country WHERE (Continent='Asia' OR Continent='Europe')
AND GovernmentForm = 'Republic')
ORDER BY Continent;
Após o resultado podemos verificar o que foi gravado em nosso arquivo de log gravado no diretório escolhido para gravar os logs de queries lentas:
TCP Port: 3306, Named Pipe: MySQL
Time Id Command Argument
# Time: 100614 17:14:17
# User@Host: root[root] @ localhost [127.0.0.1]
# Thread_id: 1 Schema: world QC_hit: No
# Query_time: 1.872003 Lock_time: 0.000000 Rows_sent: 62 Rows_examined: 507204
# Full_scan: Yes Full_join: No Tmp_table: Yes Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0
use world;
SET timestamp=1276546457;
SELECT p.Code AS `C¢d Pa¡s`, p.Name AS `Nome Pa¡s`,
(SELECT MAX(population) FROM City WHERE p.Code = countrycode) AS `Populacao`,
(SELECT name FROM City WHERE p.Code = countrycode ORDER BY Population DESC LIMIT 1) AS `Cidade Mais Populosa`,
l.Language AS `L¡ngua Oficial`,
l.Percentage AS `%`
FROM Country AS p
JOIN CountryLanguage AS l ON p.Code = l.CountryCode AND l.isofficial = 'T'
WHERE p.Code IN(SELECT Code FROM Country WHERE (Continent='Asia' OR Continent='Europe')
AND GovernmentForm = 'Republic')
ORDER BY Continent;
Bem, esta é uma forma de sabermos qual query está lenta em nosso sistema com mais recursos agora no MariaDB. Atá a próxima.
Armazenar endereços IP como tipos inteiros
Neste artigo irei explicar qual a principal vantagem de armazenarmos endereços IP como tipo inteiro.
Para que você possa entender melhor vamos criar uma tabela simples de forma que possamos armazenar algum endereço IP utilizando o tipo CHAR.
CREATE DATABASE exemplo;
USE exemplo;
CREATE TABLE log(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
usuario CHAR(30), visitas INT, ip CHAR(15)
Vamos inserir alguns registros nesta tabela.
INSERT INTO exemplo VALUES(null, 'douglas', 10, '192.168.10.10');
INSERT INTO log VALUES(null, 'luciana', 10, '200.213.162.161');
INSERT INTO log VALUES(null, 'alberto', 10, '213.187.0.1');
Bem, por enquanto temos tres registros em nossa tabela, começaremos nossa primeira análise.
Qual o tamanho ocupado pela coluna IP ?
Utilizando a função LENGTH do MySQL que nos retorna o tamanho utilizado por cada byte baseado no CHARSET utilizado temos:
SELECT LENGTH(IP) FROM log; ==> 15 bytes
Como resultado a este comando temos um custo total de 15 bytes, como nosso tipo para esta coluna é um CHAR que ocupa uma posição fixa então temos uma utilização de 15 bytes mesmo para um IP 1.1.1.1
Armazenando endereço IP com tipos String teremos um maior custo em disco e mais tempo será preciso para buscar um determinado registro, mas então como podemos economizar utilizando inteiros ?
Em primeiro lugar temos que fazer uma pequena alteração em nossa tabela onde o campo que armazenará o endereço IP seja do tipo UNSIGNED INT
Após esta alteração poderemos utilizar duas funções para armazenar o registro e outra quando precisamos recuper este.
Veja o exemplo:
INET_ATON('192.168.0.1'); ==> 3232235521
SELECT INET_NTOA(3232235521); ==> 192.168.0.1
Nossa tabela desta forma se tornará menos dispendiosa no que se refere ao armazenamento e muito mais rápida ao se procurar por um determinado registro.
Até a próxima.
Para que você possa entender melhor vamos criar uma tabela simples de forma que possamos armazenar algum endereço IP utilizando o tipo CHAR.
CREATE DATABASE exemplo;
USE exemplo;
CREATE TABLE log(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
usuario CHAR(30), visitas INT, ip CHAR(15)
Vamos inserir alguns registros nesta tabela.
INSERT INTO exemplo VALUES(null, 'douglas', 10, '192.168.10.10');
INSERT INTO log VALUES(null, 'luciana', 10, '200.213.162.161');
INSERT INTO log VALUES(null, 'alberto', 10, '213.187.0.1');
Bem, por enquanto temos tres registros em nossa tabela, começaremos nossa primeira análise.
Qual o tamanho ocupado pela coluna IP ?
Utilizando a função LENGTH do MySQL que nos retorna o tamanho utilizado por cada byte baseado no CHARSET utilizado temos:
SELECT LENGTH(IP) FROM log; ==> 15 bytes
Como resultado a este comando temos um custo total de 15 bytes, como nosso tipo para esta coluna é um CHAR que ocupa uma posição fixa então temos uma utilização de 15 bytes mesmo para um IP 1.1.1.1
Armazenando endereço IP com tipos String teremos um maior custo em disco e mais tempo será preciso para buscar um determinado registro, mas então como podemos economizar utilizando inteiros ?
Em primeiro lugar temos que fazer uma pequena alteração em nossa tabela onde o campo que armazenará o endereço IP seja do tipo UNSIGNED INT
Após esta alteração poderemos utilizar duas funções para armazenar o registro e outra quando precisamos recuper este.
Veja o exemplo:
INET_ATON('192.168.0.1'); ==> 3232235521
SELECT INET_NTOA(3232235521); ==> 192.168.0.1
Nossa tabela desta forma se tornará menos dispendiosa no que se refere ao armazenamento e muito mais rápida ao se procurar por um determinado registro.
Até a próxima.
Assinar:
Postagens (Atom)