Use seu índice, mesmo com um varchar | | serviço doméstico

Original post: http://anothermysqldba.blogspot.com/2014/01/use-your-index-even-with-varchar-char.html

Eu observei recentemente um post sobre o forums.mysql.com site: Como a jejuar em 3 milhões registro de pesquisa? 
O exemplo dado utilizado um LIKE '% eed' 

Isso não será aproveitado de um índice e vai fazer uma varredura completa da tabela. 
Abaixo está um exemplo usando o banco de dados do mundo, então não 3 milhões de discos, mas apenas tentando mostrar como ele funciona. 

> explain select * from City where Name LIKE '%dam' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4188
Extra: Using where
1 row in set (0.01 sec)

[world]> select count(*) FROM City;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+

> select * from City where Name LIKE '%dam';
+------+------------------------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population
+------+------------------------+-------------+----------------+------------+
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 1146 | Ramagundam | IND | Andhra Pradesh | 214384 |
| 1318 | Haldwani-cum-Kathgodam | IND | Uttaranchal | 104195 |
| 2867 | Tando Adam | PAK | Sind | 103400 |
| 3122 | Potsdam | DEU | Brandenburg | 128983 |
+------+------------------------+-------------+----------------+------------+

Para mostrar o ponto mais 

> explain select * from City where Name LIKE '%dam%' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4188
Extra: Using where
1 row in set (0.00 sec)

> select * from City where Name LIKE '%dam%';
+------+------------------------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+------------------------+-------------+----------------+------------+<
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 380 | Pindamonhangaba | BRA | São Paulo | 121904 |<
| 625 | Damanhur | EGY | al-Buhayra | 212203 |
| 1146 | Ramagundam | IND | Andhra Pradesh | 214384 |
| 1318 | Haldwani-cum-Kathgodam | IND | Uttaranchal | 104195 |
| 1347 | Damoh | IND | Madhya Pradesh | 95661 |
| 2867 | Tando Adam | PAK | Sind | 103400 |
| 2912 | Adamstown | PCN | – | 42 |
| 3122 | Potsdam | DEU | Brandenburg | 128983 |
| 3177 | al-Dammam | SAU | al-Sharqiya | 482300 |
| 3250 | Damascus | SYR | Damascus | 1347000 |
+------+------------------------+-------------+----------------+------------+<
12 rows in set (0.00 sec) 

A saída de explicar acima mostra que há índices estão sendo usados. 

CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB
<

Assim, por sorrisos vamos colocar uma chave no campo varchar. Repare que eu não colocar uma chave em toda a faixa de apenas os primeiros caracteres. Este é, naturalmente, dependente de seus dados. 

> ALTER TABLE City ADD KEY name_key(`Name`(5));
Query OK, 0 rows affected (0.54 sec)

CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
KEY `name_key` (`Name`(5)),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB

SO vai este mesmo assunto? 

> explain select * from City where Name LIKE '%dam' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4188
Extra: Using where
1 row in set (0.00 sec) 

Não, não vai importar, por causa do LIKE '% barragem "vai forçar uma varredura completa de qualquer maneira. 

> EXPLAIN select * from City where Name LIKE '%dam' AND CountryCode = 'IND' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: ref
possible_keys: CountryCode
key: CountryCode
key_len: 3
ref: const
rows: 341
Extra: Using index condition; Using where
1 row in set (0.00 sec) 

Notar a diferença na saída explicado acima. Esta consulta está usando um índice. Ele não está usando o nome do índice, mas ele está usando um índice. Então, como você pode tirar vantagem do índice varchar? 

> EXPLAIN select * from City where Name LIKE 'Ra%' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: City
type: range
possible_keys: name_key
key: name_key
key_len: 5
ref: NULL
rows: 35
Extra: Using where
1 row in set (0.00 sec) 

A consulta acima irá utilizar o índice name_key. 

O ponto é que você tem que ter cuidado como você escreve sua consulta SQL e garantir que você executar explica para encontrar o melhor índice de escolha para sua consulta.

MySQL Replication pode pegar

Original post: http://anothermysqldba.blogspot.com/2014/01/can-mysql-replication-catch-up.html

Assim, a replicação foi recentemente melhorado no MySQL 5.6. No entanto, as pessoas ainda estão usando 5.1 e 5.5 para algumas dessas melhorias vão ter que esperar para atingir o mundo real.

Recentemente, ajudou a mover nessa direção com uma solução de replicação localizado geo-. Uma parte do país tinha um servidor MySQL 5.1 ea outra parte do país, teve um novo servidor MySQL 5.6 instalado.

Depois de lidar com as questões de conseguir o apoio de dados inicial do primário para o servidor secundário (levou várias horas para dizer o mínimo), eu tinha que decidir poderia replicação apanhar e manter-se. O servidor primário teve algumas grandes consultas e otimização é sempre um bom lugar para começar. Eu tinha que se o servidor secundário puxar e aplicar o mais rápido que pude em primeiro lugar.

Então, aqui estão algumas coisas para verificar e manter em mente quando se trata de replicação. Eu adicionei alguns links abaixo que ajudam a apoiar os meus pensamentos como eu trabalhei nisto.

A replicação pode ser muito I / O pesado. Dependendo da sua aplicação. Um blog não tem que várias escritas de modo a replicação I / O é leve, mas um servidor primário fortemente escrito e atualizado vai levar a um servidor de replicação escrevendo um monte de relay_logs e binary_logs se eles estão habilitados. Logs binários podem ser habilitados no secundário para permitir que você executar backups ou você pode querer esse servidor seja um primário para os outros.

Eu dividir os logs em uma partição de dados diferente do diretório de dados.
Isso é definido no arquivo my.cnf - relay-log

O buffer pool do InnoDB já foi definido para um valor mais de 10GB. Isso foi muito para este servidor.
O servidor foi mais de 90.000 segundos atrás ainda.

Então eu comecei a fazer alguns ajustes para o servidor e acabou com essas configurações no final. Concedido a cada servidor é diferente.

mysql> select @ @ sync_relay_log_info \ G
*************************** 1. linha ***************************
@ @ Sync_relay_log_info: 0
1 row in set (0.08 sec)

mysql> select @ @ innodb_flush_log_at_trx_commit \ G
*************************** 1. linha ***************************
@ @ Innodb_flush_log_at_trx_commit: 2
1 row in set (0.00 sec)

mysql> select @ @ log_slave_updates \ G
*************************** 1. linha ***************************
@ @ Log_slave_updates: 0

mysql> select @ @ sync_binlog \ G
*************************** 1. linha ***************************
@ @ Sync_binlog: 0
1 row in set (0.00 sec)

mysql> select @ @ max_relay_log_size \ G
*************************** 1. linha ***************************
@ @ Max_relay_log_size: 268435456

Virei o log binário fora como eu monitorados diferentes configurações e opções para ajudar a replicação catch up. Demorou um pouco. Algumas das configurações que você vê acima pode ou não ter sido aplicada como eu trabalhei dentro deste prazo. No entanto, se pegar para 0 segundos atrás. Agora você pode notar que muitas dessas configurações acima referem e em torno do log binário. Então eu corri um pequeno teste. Então, eu reiniciado e permitiu que os registros de lixo. Eu verifiquei com o servidor mais tarde e descobriu que mais de 10.000 segundos atrás. Então eu, mais uma vez reiniciado e desativados os logs de lixo. Ele pego (0 segundos atrás) com o servidor principal em menos de 15 minutos. Eu costumava Aurimas ' ferramenta como eu assisti-lo apanhar também. Se você não usá-lo antes, é uma ferramenta muito agradável e acessível.

O que tudo isto significa é o servidor primário deve ser compatível com ACID. Com esta configuração você também está dependendo do sistema operacional para o cache e limpar. Este é o servidor vai ser utilizado como um servidor de ler principalmente para alimentar informação aos outros. Isso também significa que, sim, a replicação localizado geo-pode manter-se atualizado com um servidor primário.

E se você precisa parar o escravo, isso ainda será pegar rapidamente?

Como e por que você parar o escravo é a minha primeira resposta. Você deve adquirir o hábito de usar STOP SLAVE SQL_THREAD , em vez de STOP SLAVE ; Isso permite que os relay logs para continuar a recolher dados e simplesmente não aplicá-lo ao seu servidor primário. Então, se você pode tirar vantagem de que ele vai ajudar a reduzir o tempo que leva para que você possa preencher os relay logs mais tarde.

Algumas leituras adicionais para você:

• http://www.mysqlperformanceblog.com/2011/07/29/reasons-for-mysql-replication-lag/
• http://www.mysqlperformanceblog.com/2007/05/24/predicting-how-long-data-load-would-take/
• http://www.mysqlperformanceblog.com/2012/08/29/heres-a-quick-way-to-foresee-if-replication-slave-is-ever-going-to-catch-up-and-when/

O trabalho duro que passa despercebido ....

Originally posted: http://anothermysqldba.blogspot.com/2014/01/hard-work-that-goes-unnoticed.html

Eu levei um momento hoje e atualizado uma das minhas distribuições Linux. Nesta distribuição Acontece que eu tenho Percona 5.6 instalado como o banco de dados MySQL. Eu já mencionei antes, como você pode configurar a sua escolha de MySQL através de um repositório Yum .

Meu ponto aqui é que, como é que vamos sempre agradecer a essas pessoas por todo o trabalho que eles fazem?

Muitos desses repositórios são executados por empresas e essas pessoas são pagas pelo que fazem. No entanto, por meio de observação e geral / perguntas do inquérito do Linux (incluindo Debian / Ubuntu) da comunidade, a maioria das pessoas não vai atualizar até que esteja disponível na sua distribuição. Acontece que eu sou aquele que quer ficar no topo das correções de segurança e bugs, então eu tenho o yum repositório de atualização da fonte o mais rápido possível.

Meu ponto é, um monte de trabalho entra em embalagens desses arquivos para distribuição e para a maior parte parece um trabalho muito ingrato. Lembro-me de os mais velhos (não de idade, mas mais antigos) dias de tar e gzip, quando você teve que cavar e encontrar as dependências sozinho. -. / Configure .. Não. precisa de algo mais vai baixar e instalar que tente novamente .....

Eu acabei de atualizar 25 pacotes diferentes em alguns momentos, o que teria levado algum tempo antes. Enquanto Yum e Apt Get estão longe de novo, e eu soar como um velho aqui, eu pensei que poderia ser bom para dizer obrigado, a todos as pessoas que trabalham nos bastidores para fazer todas as nossas experiências de Linux, e muito menos o MySQL relacionado instala, mais fácil e suave.

Eu gostaria de salientar que a Oracle tem 5,6 pacotes já está disponível.

• http://public-yum.oracle.com/repo/OracleLinux/OL6/MySQL56/

Lembro-me de que o meu post anterior mencionado como ele não tinha sido.

A DBA MySQL olha para PostgreSQL part3 PostgreSQL Para MySQL

Original post: http://anothermysqldba.blogspot.com/2014/01/a-mysql-dba-looks-at-postgresql-part3.html

Então, eu recentemente postou: Um DBA MySQL olha para o PostgreSQL e parte 2: MySQL para PostgreSQL .

Este post vai explorar a migração do PostgreSQL para MySQL . Mais uma vez, o objetivo de longo prazo com essas mensagens devem ser mostrar capaz como os dados funciona dentro das diferentes bases de dados, bem como a forma de resolver problemas semelhantes em cada banco de dados quando deve surgir exemplos.

MySQL empurra o MySQL Workbench a ferramenta de migração de dados. Eu tenho que admitir que eu sou curioso porque o MySQL Utilities não fornece uma opção de linha de comando. O post anterior ( parte 2 ) mostrou como é fácil a migração via linha de comando foi para MySQL para PostgreSQL . Tenha em mente que ao trazer os dados para MySQL ao motor de dados tem de ser considerado.

Para colocá-lo simplesmente, se você estiver indo para trazer dados de volta para MySQL doPostgreSQL uma opção rápida é provável que o MySQL Workbench . Mas isso não é uma solução global, uma vez que muitas vezes preferem ficar dentro de nossas janelas de terminal. Assim, você estará fazendo o seguinte:

• Despejar o esquema do PostgreSQL em um arquivo
• Rever e editar o arquivo para MySQL.
• Por esquema e tabelas desejado exportar como um arquivo CSV.
• Importação de volta para MySQL

De qualquer forma, primeiro temos ainda os dados em PostgreSQL a partir do banco de dados Mundial exemplo.


world=> \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------+-------+----------
public | city | table | testuser
public | country | table | testuser
public | countrylanguage | table | testuser
(3 rows)

world=> select count(ID) from City;
count
-------
4079
(1 row)

world=> 

Despejar o esquema:


$ pg_dump -s world > world_schema.pgsql 

Usando pg_dump tomar o - somente dados e - Insere simplesmente construir arquivo SQL padrão de dados.


pg_dump --data-only --inserts world > world_data.pgsql 

Você verá mais tarde que fazer um depósito de acordo com a tabela seria melhor, mas isso funciona.

Criando um banco de dados em MySQL para colocar os dados de volta, bem como testar seu novo esquema.


mysql> CREATE DATABASE world_back;
Query OK, 1 row affected (0.01 sec) 

Edite seu arquivo de esquema: vi world_schema.pgsql
Você tem o novo banco de dados MySQL criado para que você possa testá-los como você vai.



CREATE TABLE city (
id integer DEFAULT nextval('city_id_seq'::regclass) NOT NULL,
name character(35) DEFAULT ''::bpchar NOT NULL,
countrycode character(3) DEFAULT ''::bpchar NOT NULL,
district character(20) DEFAULT ''::bpchar NOT NULL,
population integer DEFAULT 0 NOT NULL
);

CREATE TABLE country (
code character(3) DEFAULT ''::bpchar NOT NULL,
name character(52) DEFAULT ''::bpchar NOT NULL,
continent character varying DEFAULT 'Asia'::character varying NOT NULL,
region character(26) DEFAULT ''::bpchar NOT NULL,
surfacearea double precision DEFAULT 0::double precision NOT NULL,
indepyear smallint,
population integer DEFAULT 0 NOT NULL,
lifeexpectancy double precision,
gnp double precision,
gnpold double precision,
localname character(45) DEFAULT ''::bpchar NOT NULL,
governmentform character(45) DEFAULT ''::bpchar NOT NULL,
headofstate character(60) DEFAULT NULL::bpchar,
capital integer,
code2 character(2) DEFAULT ''::bpchar NOT NULL,
CONSTRAINT country_continent_check CHECK (((continent)::text = ANY ((ARRAY['Asia'::character varying, 'Europe'::character varying, 'North America'::character varying, 'Africa'::character varying, 'Oceania'::character varying, 'Antarctica'::character varying, 'South America'::character varying])::text[])))
);
ALTER TABLE ONLY city
ADD CONSTRAINT city_pkey PRIMARY KEY (id);

CREATE INDEX city_countrycode_idx ON city USING btree (countrycode); 

Você terá que analisar o arquivo para todas as chaves relacionadas para que você possa criar declarações válidas.
Você precisa entender MySQL, assim você pode criar instruções CREATE TABLE válidas.


CREATE TABLE city (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(35) NOT NULL DEFAULT '',
`countrycode` char(3) NOT NULL DEFAULT '',
`district` char(20) NOT NULL DEFAULT '',
`population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
);

CREATE TABLE `country` (
`code` char(3) NOT NULL DEFAULT '',
`name` char(52) NOT NULL DEFAULT '',
`continent` char(5) NOT NULL DEFAULT '',
`region` char(26) NOT NULL DEFAULT '',
`surfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
`indepyear` smallint(6) DEFAULT NULL,
`population` int(11) NOT NULL DEFAULT '0',
`lifeexpectancy` float(3,1) DEFAULT NULL,
`gnp` float(10,2) DEFAULT NULL,
`gnpold` float(10,2) DEFAULT NULL,
`localname` char(45) NOT NULL DEFAULT '',
`governmentform` char(45) NOT NULL DEFAULT '',
`headofstate` char(60) DEFAULT NULL,
`capital` int(11) DEFAULT NULL,
`code2` char(2) NOT NULL DEFAULT '',
PRIMARY KEY (`code`)
); 

É claro que é até você. mas uma vez que você trabalha fora da chave primária por tabela, eu iria criar instruções alter para atualizar os novos esquemas para que você possa garantir que você pegar tudo. Enquanto eles podem ser adicionados diretamente para a primeira declaração Criar em sua maior parte como você processar o arquivo de despejo Postgresql fazer altera pode mantê-lo sob controle.

Alguns exemplos das declarações Alter necessários:


ALTER TABLE city ENGINE=InnoDB;
ALTER TABLE country ENGINE=InnoDB;
ALTER TABLE countrylanguage ENGINE=InnoDB;

ALTER TABLE country DROP continent;
ALTER TABLE country ADD continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia' AFTER name;

ALTER TABLE city ADD KEY `countrycode` (`countrycode`),
ALTER TABLE city ADD CONSTRAINT `city_ibfk_1` FOREIGN KEY (`countrycode`) REFERENCES `country` (`code`) 

Uma vez que todo o esquema é atualizado e válido. você pode colocar os dados salvos de volta.


vi world_data.pgsql to remove the SET statements at the top.
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog; 

Copie os arquivos para fora por tabela, neste caso, por causa das restrições. Editar em conformidade para cada arquivo só tem os dados por tabela. Eu deveria ter despejado assim ou simplesmente despejar novamente por tabela.


$ cp world_data.pgsql world_data_city.pgsql
$ cp world_data.pgsql world_data_countrylanguage.pgsql
$ cp world_data.pgsql world_data_country.pgsql

$ mysql -u root -p world_back < world_data_country.pgsql
Enter password:
$ mysql -u root -p world_back < world_data_countrylanguage.pgsql
Enter password:
$ mysql -u root -p world_back < world_data_city.pgsql 

Então, basta colocá-lo não é tão fácil, eu diria automatizada, a migrar para o MySQL via linha de comando por causa das mudanças de esquema que vai exigir sua atenção, mas isso pode ser feito. 


mysql> select count(id) from city;
+-----------+
| count(id) |
+-----------+
| 4079 |
+-----------+
1 row in set (0.14 sec)

MySQL Workbench banco de dados de migração, claro, pode fazer o mesmo processo e você pode aprender mais sobre essa ferramenta aqui -http://www.mysql.com/products/workbench/migrate/