estamos usando mestre de tungstênio para replicação de escravos no MySQL. configuramos essa configuração há muito tempo. da semana passada em diante, o escravo está atrasado com o mestre em torno de 5 a 10 arquivos binários. nós verificamos a conectividade N/W está bem em b/w master e slave.
não estamos entendendo por que o atraso continua aumentando?
Como fazer com que o escravo esteja sincronizado com o mestre na replicação de tungstênio?
temos um banco de dados de 30 GB. Queremos fazer backup desse banco de dados.
aqui a questão é que queremos fazer o lote de backup de cada 10k registros.
Como podemos fazer backup em lote de registros usando mysqldump
Como aplicar patches no MySQL 5.1.67 ou qualquer versão.
porque precisamos corrigir tantos bugs em nossa versão sem nenhuma atualização.
por favor me sugira.
Temos 40 bancos de dados em nosso servidor.
Queremos fazer backup de 36 bancos de dados usando mysqldump. Como posso ignorar os 4 bancos de dados restantes no comando mysqldump? Existe alguma opção para o mysqldump ignorar bancos de dados para backup no MySQL?
Eu conheço o comando geral mysqldump, mas é muito longo. Eu quero ignorar apenas 4 bancos de dados e preciso fazer backup de dbs restantes.
consulta
select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from
promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci on pc.coupon_id = convert(pci.image_key,unsigned integer)
where pc.coupon_type_id=pct.coupon_type_id and pc.promotion_id=17;
Estrutura da tabela:
mysql> show create table promotion_coupon_types\G
*************************** 1. row ***************************
Table: promotion_coupon_types
Create Table: CREATE TABLE `promotion_coupon_types` (
`row_mod` datetime DEFAULT NULL,
`row_create` datetime DEFAULT NULL,
`coupon_type_id` int(11) NOT NULL,
`pretty_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`coupon_type_code` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`description` longtext COLLATE latin1_bin,
`type` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`validate_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`calculate_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`remove_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`coupon_constructor_callback` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`alter_subtotal` int(11) DEFAULT NULL,
`active` int(11) DEFAULT NULL,
UNIQUE KEY `idx_1505` (`coupon_type_id`),
UNIQUE KEY `idx_1711` (`coupon_type_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)
mysql> show create table promotion_coupons\G
*************************** 1. row ***************************
Table: promotion_coupons
Create Table: CREATE TABLE `promotion_coupons` (
`row_mod` datetime DEFAULT NULL,
`row_create` datetime DEFAULT NULL,
`coupon_id` int(11) DEFAULT NULL,
`promotion_id` int(11) DEFAULT NULL,
`coupon_type_id` int(11) NOT NULL,
`pretty_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`description` longtext COLLATE latin1_bin,
`accept_text` longtext COLLATE latin1_bin,
`active` int(11) DEFAULT NULL,
`start_date` datetime DEFAULT NULL,
`end_date` datetime DEFAULT NULL,
`value` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`min_value` decimal(6,2) DEFAULT NULL,
`needs_coupon` int(11) DEFAULT NULL,
`display` int(11) DEFAULT NULL,
`auto_apply` int(11) DEFAULT NULL,
`applicable_start_date` datetime DEFAULT NULL,
`applicable_end_date` datetime DEFAULT NULL,
`tax_after_discount` int(11) DEFAULT NULL,
`delivery_type` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`items_list` longtext COLLATE latin1_bin,
`display_options` varchar(50) COLLATE latin1_bin DEFAULT NULL,
UNIQUE KEY `idx_960` (`coupon_id`),
KEY `idx_1067` (`start_date`),
KEY `idx_1299` (`promotion_id`),
KEY `idx_820` (`end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)
mysql> show create table promotion_coupon_images\G
*************************** 1. row ***************************
Table: promotion_coupon_images
Create Table: CREATE TABLE `promotion_coupon_images` (
`row_mod` datetime DEFAULT NULL,
`row_create` datetime DEFAULT NULL,
`image_key` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`data` longtext COLLATE latin1_bin,
`content_type` varchar(40) COLLATE latin1_bin DEFAULT NULL,
`width` int(11) DEFAULT NULL,
`height` int(11) DEFAULT NULL,
`pretty_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
UNIQUE KEY `idx_939` (`image_key`),
KEY `idx_pci_cmp_n1` (`pretty_name`,`width`,`height`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)
plano de explicação de consulta normal
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | pc | ref | idx_1299 | idx_1299 | 5 | const | 356 | Using where |
| 1 | SIMPLE | pct | eq_ref | idx_1505 | idx_1505 | 4 | cms.pc.coupon_type_id | 1 | |
| 1 | SIMPLE | pci | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)
use o plano de explicação do índice:
mysql> explain select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci USE INDEX(idx_pci_cmp_n1) on pc.coupon_id = convert(pci.image_key,unsigned integer) where pc.coupon_type_id=pct.coupon_type_id and pc.promotion_id=17;
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | pc | ref | idx_1299 | idx_1299 | 5 | const | 356 | Using where |
| 1 | SIMPLE | pct | eq_ref | idx_1505 | idx_1505 | 4 | cms.pc.coupon_type_id | 1 | |
| 1 | SIMPLE | pci | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)
mysql> explain select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci USE INDEX(idx_939) on pc.coupon_id = convert(pci.image_key,unsigned integer) where pc.coupon_type_id=pct.coupon_type_id and pc.promotion_id=17;
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | pc | ref | idx_1299 | idx_1299 | 5 | const | 356 | Using where |
| 1 | SIMPLE | pct | eq_ref | idx_1505 | idx_1505 | 4 | cms.pc.coupon_type_id | 1 | |
| 1 | SIMPLE | pci | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)
plano de explicação do índice de força:
mysql> explain select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci force INDEX(idx_939) on pc.coupon_id = convert(pci.image_key,unsigned integer) where pc.coupon_type_id=pct.coupon_type_id and pc.promotion_id=17;
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | pc | ref | idx_1299 | idx_1299 | 5 | const | 356 | Using where |
| 1 | SIMPLE | pct | eq_ref | idx_1505 | idx_1505 | 4 | cms.pc.coupon_type_id | 1 | |
| 1 | SIMPLE | pci | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)
mysql> explain select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci force INDEX(idx_pci_cmp_n1) on pc.coupon_id = convert(pci.image_key,unsigned integer) where pc.coupon_type_id=pct.coupon_type_id and pc.promotion_id=17;
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | pc | ref | idx_1299 | idx_1299 | 5 | const | 356 | Using where |
| 1 | SIMPLE | pct | eq_ref | idx_1505 | idx_1505 | 4 | cms.pc.coupon_type_id | 1 | |
| 1 | SIMPLE | pci | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)
se eu removi converter inteiro também não há mudança no resultado
mysql> explain select pc.*,pct.pretty_name as coupon_type,pct.description as coupon_type_desc,pci.pretty_name as image_name,pci.width,pci.height from promotion_coupon_types pct,promotion_coupons pc left join promotion_coupon_images pci on pc.coupon_id = pci.image_key where pc.coupon_type_id=pct.coupon_type_id and pc.promotion_id=17; +----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | pc | ref | idx_1299 | idx_1299 | 5 | const | 356 | Using where |
| 1 | SIMPLE | pct | eq_ref | idx_1505 | idx_1505 | 4 | cms.pc.coupon_type_id | 1 | |
| 1 | SIMPLE | pci | ALL | idx_939 | NULL | NULL | NULL | 1 | |
+----+-------------+-------+--------+---------------+----------+---------+-----------------------+------+-------------+
3 rows in set (0.00 sec)
encontre a estrutura da tabela com a nova coluna image_key_int em promotion_coupon_images para lançar índices também adicionados.
mysql> show create table promotion_coupon_images\G
*************************** 1. row ***************************
Table: promotion_coupon_images
Create Table: CREATE TABLE `promotion_coupon_images` (
`row_mod` datetime DEFAULT NULL,
`row_create` datetime DEFAULT NULL,
`image_key` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`data` longtext COLLATE latin1_bin,
`content_type` varchar(40) COLLATE latin1_bin DEFAULT NULL,
`width` int(11) DEFAULT NULL,
`height` int(11) DEFAULT NULL,
`pretty_name` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`image_key_int` int(10) unsigned DEFAULT NULL,
UNIQUE KEY `idx_939` (`image_key`),
KEY `idx_pci_cmp_n1` (`pretty_name`,`width`,`height`) USING BTREE,
KEY `idx_img_key_n1` (`image_key_int`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)
A consulta abaixo está demorando muito - cerca de 540 segundos.
A tabela não tinha nenhum índice. Criei índices nas colunas da WHEREcláusula e outros campos mas a consulta não está utilizando esses índices.
Consulta:
select
alias_to,
fake_uri,
concat(ifnull(parameters,''),ifnull(parameters2,''),ifnull(parameters3,'')) as parameters,
precedence,
template_name
from site_map_template
order by precedence, fake_uri desc;
Explique a saída:
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | site_map_template | ALL | NULL | NULL | NULL | NULL | 7616 | Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
tabela site_map_template:
CREATE TABLE `site_map_template` (
`row_mod` datetime DEFAULT NULL,
`row_create` datetime DEFAULT NULL,
`template_name` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`alias_to` varchar(100) COLLATE latin1_bin DEFAULT NULL,
`package` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`fake_uri` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`precedence` int(11) DEFAULT NULL,
`parameters` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`parameters2` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`parameters3` varchar(255) COLLATE latin1_bin DEFAULT NULL,
KEY `idx_1372` (`template_name`),
KEY `idx_n1` (`precedence`),
KEY `idx_n2` (`fake_uri`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
Minha consulta não está usando índices.
estava demorando cerca de 5 segundos.
a saída é de apenas 500 linhas. é uma consulta simples
Encontre o plano de explicação e a estrutura da tabela aqui.
Explicar o plano
mysql> explain SELECT COUNT(*) AS `records_found`
FROM `builds` AS `build`
WHERE (`user_id` = '32762'
AND `country_id` = 'gb'
AND `share` = 0
AND `mode` = 'bouquet'
AND `build_data` != '');
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | build | ALL | NULL | NULL | NULL | NULL | 35209 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
Estrutura da tabela
mysql> show create table builds\G
*************************** 1. row ***************************
Table: builds
Create Table: CREATE TABLE `builds` (
`build_id` varchar(8) NOT NULL,
`user_id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`derivation` int(11) NOT NULL,
`share` tinyint(4) NOT NULL,
`country_id` varchar(2) NOT NULL,
`build_data` text NOT NULL,
`mode` varchar(250) DEFAULT NULL,
PRIMARY KEY (`build_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Minha consulta está levando cerca de 540 segundos para obter o resultado. Por favor, me ajude a reescrevê-lo.
Consulta:
SELECT pd.domain, fd.passkeyid
FROM portfolio.domains AS pd,
fabulousdomains.domains AS fd
WHERE pd.domain = fd.domain
AND pd.owner = fd.owner
AND pd.owner = '15940'
AND groupid = '28555'
AND fd.status = 'a'
AND listingqa NOT IN ( 'default', 'ok' ) ;
Explique o plano:
+----+-------------+-------+--------+---------------------------------------------------------+-------------------+---------+----------------------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------------------------------+-------------------+---------+----------------------------------------+--------+-------------+
| 1 | SIMPLE | pd | ref | PRIMARY,owner_idx,groupid_owner_idx,owner_roarcatid_idx | groupid_owner_idx | 8 | const,const | 126280 | Using where |
| 1 | SIMPLE | fd | eq_ref | PRIMARY | PRIMARY | 132 | portfolio.pd.domain,portfolio.pd.owner | 1 | Using where |
+----+-------------+-------+--------+---------------------------------------------------------+---
-----+---------+---------------------- ------------------+--------+-------------+ Estrutura da tabela:
mysql> show create table portfolio.domains\G
*************************** 1. row ***************************
Table: domains
Create Table: CREATE TABLE `domains` (
`domain` char(255) NOT NULL default '',
`owner` int(10) unsigned NOT NULL default '0',
`renewstatus` char(1) NOT NULL default 'u',
`insertdate` date NOT NULL default '0000-00-00',
`createdate` date NOT NULL default '0000-00-00',
`expirydate` date NOT NULL default '0000-00-00',
`refreshdate` date NOT NULL default '0000-00-00',
`lastwrite` date NOT NULL default '0000-00-00',
`lastupdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`registrar` int(11) NOT NULL default '0',
`groupid` int(10) unsigned NOT NULL default '0',
`roarcatid` int(10) unsigned NOT NULL default '0',
`illstatus` enum('notchecked','autoillegal','autook','ok','adult','general','unacceptable') NOT NULL default 'notchecked',
`illstatusdate` datetime NOT NULL default '0000-00-00 00:00:00',
`whitelist` set('unchecked','ok','unknown','trademark','typo','brand') NOT NULL default 'unchecked',
`darkbluesiteid` int(10) unsigned NOT NULL default '0',
`locked` enum('t','f') NOT NULL default 'f',
`googletagstatus` enum('notrequired','unchecked','ok','unacceptable') NOT NULL default 'notrequired',
PRIMARY KEY (`domain`,`owner`),
KEY `owner_idx` (`owner`),
KEY `refreshdate_idx` (`refreshdate`),
KEY `darkbluesiteid_idx` (`darkbluesiteid`),
KEY `insertdate_owner_idx` (`insertdate`,`owner`),
KEY `expiry_owner_registrar_idx` (`expirydate`,`owner`,`registrar`),
KEY `groupid_owner_idx` (`groupid`,`owner`),
KEY `renew_owner_expiry_idx` (`renewstatus`,`owner`,`expirydate`),
KEY `owner_roarcatid_idx` (`owner`,`roarcatid`),
KEY `roarcatid_domain_idx` (`roarcatid`,`domain`),
KEY `illstatus` (`illstatus`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table fabulousdomains.domains\G
*************************** 1. row ***************************
Table: domains
Create Table: CREATE TABLE `domains` (
`domain` char(128) NOT NULL default '',
`owner` int(10) unsigned NOT NULL default '0',
`fixprice` decimal(8,2) NOT NULL default '0.00',
`negprice` decimal(8,2) NOT NULL default '0.00',
`watprice` decimal(8,2) NOT NULL default '0.00',
`rejprice` decimal(8,2) NOT NULL default '0.00',
`price` decimal(8,2) NOT NULL default '0.00',
`pricetype` char(16) NOT NULL default '',
`minoffer` decimal(8,2) NOT NULL default '0.00',
`commission` decimal(8,2) NOT NULL default '0.00',
`commissionbonus` decimal(8,2) NOT NULL default '0.00',
`status` char(1) NOT NULL default '',
`statusdate` date NOT NULL default '0000-00-00',
`lastupdated` datetime NOT NULL default '0000-00-00 00:00:00',
`passkeyid` int(11) NOT NULL default '0',
`visibility` int(10) unsigned NOT NULL default '0',
`customsearch` int(10) unsigned NOT NULL default '0',
`yearlyearn` decimal(8,2) NOT NULL default '0.00',
`quality` float NOT NULL default '0',
`listingqa` enum('unchecked','default','ok','unacceptable','pendingreview') default NULL,
`instantsale` enum('t','f') NOT NULL default 'f',
`transferunlockdate` date NOT NULL default '0000-00-00',
PRIMARY KEY (`domain`,`owner`),
KEY `lastupdated_idx` (`lastupdated`),
KEY `quality_idx` (`quality`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Acabei de receber o erro abaixo em um dos escravos. Eu apenas pesquisei no Google e mudei a variável myisam_data_pointer_sizede 4 para 6.
De alguma forma, o meu ALTER TABLEfoi bem sucedido.
Isso causará algum problema de desempenho. devo revertê-lo?
130208 9:40:19 [ERRO] Escravo: Erro 'A tabela '#sql-19fc_3f8acc6' está cheia' na consulta. Banco de dados padrão: 'registro'. Consulta: 'alter table enom_log_2012_12_03 add responsedatatype char(1) not null after requestdata', Error_code: 1114 130208 9:40:19 [ERRO] Erro ao executar a consulta, thread SQL escravo abortada. Corrija o problema e reinicie o thread SQL escravo com "SLAVE START". Paramos no log 'pumpkin-bin.008662' posição 411952695
Verifique os Alertas no Errorlog para quaisquer erros e falhas. Verifique se o MySQL está ativo? se não, verifique se o processo foi desligamento normal.
mysql> SHOW TABLE STATUS FROM registry LIKE 'enom_log_2012_12_03'\G
*************************** 1. row ***************************
Name: enom_log_2012_12_03
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 1786679
Avg_row_length: 2403
Data_length: 4293600396
Max_data_length: 4294967295
Index_length: 60999680
Data_free: 0
Auto_increment: 1786680
Create_time: 2013-02-06 15:19:07
Update_time: 2013-02-06 15:22:16
Check_time: 2013-02-06 15:26:35
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> show global variables like '%tmp%';
+----------------+-----------------------+
| Variable_name | Value |
+----------------+-----------------------+
| bdb_tmpdir | /usr/local/mysql/tmp/ |
| max_tmp_tables | 32 |
| tmp_table_size | 8388608 |
| tmpdir | /usr/local/mysql/tmp |
+----------------+-----------------------+
mysql> show global variables like '%myisam%';
+---------------------------------+---------------+
| Variable_name | Value |
+---------------------------------+---------------+
| myisam_data_pointer_size | 4 |--before changing it was 4 .. i changed it to 6
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 4194304 |
| myisam_stats_method | nulls_unequal |
+---------------------------------+---------------
Esta é a minha consulta de longa duração:
select *
from t_keyword
where id in
(
select rel.element_id
from t_event event
join t_event_element_rel rel on
event.id = rel.event_id
where
event.domain_id=182
and event.event_type_cd = 26
and event.event_create_date = '2012-12-18'
)
Mesa:
mysql> show create table t_keyword\G
*************************** 1. row ***************************
Table: t_keyword
Create Table: CREATE TABLE `t_keyword` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`keyword_name` varchar(255) DEFAULT NULL,
`keyword_value` varchar(255) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`description` varchar(2000) DEFAULT NULL,
`own_domain_id` int(11) DEFAULT NULL,
`rank_check` int(11) DEFAULT NULL,
`rank1` int(11) DEFAULT NULL COMMENT 'yesterday rank value',
`rank2` int(11) DEFAULT NULL COMMENT 'the day before yesterday rank value',
`rank3` int(11) DEFAULT NULL COMMENT 'special date rank for overstock.com',
`yesterday_entrances` int(11) DEFAULT NULL COMMENT 'yesterday entrances',
`week_entrances` int(11) DEFAULT NULL COMMENT '7 days entrances',
`current_ctr` float(16,4) DEFAULT NULL COMMENT 'Current CTR',
`monthly_search_volume` int(11) DEFAULT NULL COMMENT 'Most Recent Month search volume',
`avg_monthly_search_volume` int(11) DEFAULT NULL COMMENT 'avg_monthly_search_volume',
`traffic_increase` int(11) DEFAULT NULL COMMENT 'Traffic Increase',
`rank_improvement` int(11) DEFAULT NULL COMMENT 'Rank Improvement',
`rank_update_date` date DEFAULT NULL COMMENT 'rank be updated for Special Date',
`top_rank_targeturl_id` int(11) DEFAULT NULL,
`frequency` int(10) DEFAULT '1' COMMENT '1: daily, 2: weekly, 3: monthly',
`score` float DEFAULT NULL,
`create_date` datetime DEFAULT NULL,
`bing_rank1` int(10) DEFAULT NULL,
`bing_rank2` int(10) DEFAULT NULL,
`yesterday_bing_entrances` int(11) DEFAULT NULL,
`bing_rank_improvement` int(11) DEFAULT NULL,
KEY `id` (`id`),
KEY `keyword_name` (`keyword_name`),
KEY `own_domain_id` (`own_domain_id`,`rank_check`),
KEY `rank_check` (`rank_check`)
) ENGINE=InnoDB AUTO_INCREMENT=641229869 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`rank_check`)
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2) ENGINE = InnoDB,
PARTITION pEOW VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.40 sec)
mysql> show create table t_event_element_rel\G
*************************** 1. row ***************************
Table: t_event_element_rel
Create Table: CREATE TABLE `t_event_element_rel` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`element_type_id` int(11) NOT NULL,
`event_id` int(11) NOT NULL,
`element_id` int(11) NOT NULL,
`element_desc` varchar(500) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_event` (`event_id`),
KEY `FK_eventType` (`element_type_id`),
KEY `element_type_id_element_id` (`element_type_id`,`element_id`)
) ENGINE=MyISAM AUTO_INCREMENT=41241643 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Consulta:
Select *
from `t_event`
where `create_user_id`=7
and (`event_create_date`)=('2012-12-18 00:00:00')
and `event_type_cd`=11
and `domain_id` =602
and `job_id` =1
limit 1
Estrutura da tabela:
mysql> show create table t_event\G
*************************** 1. row ***************************
Table: t_event
Create Table: CREATE TABLE `t_event` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`create_user_id` int(11) DEFAULT NULL,
`event_create_date` date DEFAULT NULL,
`event_type_cd` int(11) NOT NULL,
`event_desc` varchar(512) NOT NULL,
`IsGlobalEvent` int(2) DEFAULT NULL,
`event_start_date` datetime NOT NULL,
`event_end_date` datetime NOT NULL,
`job_id` int(11) DEFAULT NULL,
`domain_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `event_user_FK` (`create_user_id`),
KEY `domain_id_event_type_cd` (`domain_id`,`event_type_cd`)
) ENGINE=InnoDB AUTO_INCREMENT=8095673 DEFAULT CHARSET=utf8
1 row in set (0.03 sec)
Explique:
+----+-------------+---------+-------------+---------------------------------------+---------------------------------------+---------+------+-------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------------+---------------------------------------+---------------------------------------+---------+------+-------+---------------------------------------------------------------------+
| 1 | SIMPLE | t_event | index_merge | event_user_FK,domain_id_event_type_cd | domain_id_event_type_cd,event_user_FK | 8,5 | NULL | 14669 | Using intersect(domain_id_event_type_cd,event_user_FK); Using where |
+----+-------------+---------+-------------+---------------------------------------+---------------------------------------+---------+------+-------+---------------------------------------------------------------------+
Como posso otimizar esta consulta? Ele tem índices, mas está demorando muito para ser executado.
Temos replicação MySQL
Estamos obtendo discrepância de carga no Escravo (db02), mas a carga do servidor Mestre (db01) está baixa. MySQL é o único processo ativo no Slave.
O iowait da CPU é aumentado.
12:00:01 AM CPU %user %nice %system %iowait %steal %idle
12:10:01 AM all 0.07 0.00 0.02 0.01 0.00 99.90
12:20:01 AM all 0.06 0.00 0.01 0.01 0.00 99.92
12:30:01 AM all 0.05 0.01 0.01 0.01 0.00 99.92
12:40:01 AM all 0.06 0.00 0.01 0.01 0.00 99.92
12:50:01 AM all 0.06 0.00 0.01 0.01 0.00 99.92
01:00:01 AM all 0.05 0.00 0.01 0.01 0.00 99.93
01:10:01 AM all 0.07 0.00 0.02 0.01 0.00 99.90
01:20:01 AM all 0.06 0.00 0.01 0.01 0.00 99.92
01:30:01 AM all 0.05 0.01 0.01 0.01 0.00 99.92
01:40:01 AM all 0.06 0.00 0.01 0.02 0.00 99.91
01:50:01 AM all 0.05 0.00 0.01 0.01 0.00 99.93
02:00:01 AM all 0.05 0.00 0.01 0.01 0.00 99.93
02:10:01 AM all 0.06 0.00 0.02 0.01 0.00 99.91
02:20:01 AM all 0.05 0.00 0.01 0.01 0.00 99.93
02:30:01 AM all 0.05 0.01 0.01 0.01 0.00 99.93
02:40:01 AM all 0.06 0.00 0.01 0.01 0.00 99.92
02:50:01 AM all 0.05 0.00 0.01 0.01 0.00 99.93
03:00:01 AM all 0.05 0.00 0.01 0.01 0.00 99.94
03:10:01 AM all 0.07 0.00 0.02 0.40 0.00 99.52
03:20:01 AM all 0.05 0.00 0.01 0.59 0.00 99.35
03:30:01 AM all 0.05 0.01 0.01 0.50 0.00 99.44
03:40:01 AM all 0.05 0.00 0.01 0.51 0.00 99.43
03:50:01 AM all 0.05 0.00 0.01 0.71 0.00 99.22
04:00:01 AM all 0.05 0.00 0.01 0.47 0.00 99.47
04:10:01 AM all 0.06 0.00 0.01 0.63 0.00 99.30
04:20:01 AM all 0.05 0.00 0.02 0.48 0.00 99.45
04:30:01 AM all 0.05 0.01 0.01 0.48 0.00 99.45
04:40:01 AM all 0.05 0.00 0.01 0.54 0.00 99.39
04:50:01 AM all 0.05 0.00 0.01 0.53 0.00 99.40
05:00:01 AM all 0.05 0.00 0.01 0.55 0.00 99.39
05:10:01 AM all 0.06 0.00 0.02 0.81 0.00 99.11
05:20:01 AM all 0.05 0.00 0.01 0.67 0.00 99.27
05:30:01 AM all 0.05 0.01 0.01 0.57 0.00 99.36
05:40:01 AM all 0.06 0.00 0.02 0.58 0.00 99.35
05:50:01 AM all 0.06 0.00 0.02 0.67 0.00 99.25
06:00:01 AM all 0.05 0.00 0.01 0.62 0.00 99.31
06:10:01 AM all 0.06 0.00 0.02 0.74 0.00 99.18
Average: all 0.06 0.00 0.01 0.30 0.00 99.63
No Master(db01), a carga é baixa e constante. No Slave(db02), a carga está aumentando.
Perguntas
Por favor me ajude aqui.
## Only 1 query which returns 10 rows.
## It took about 66 secs
mysql> explain
SELECT al.log_id , al.user_id , al.page_name ,
al.section_name , al.submit_time , al.url ,
alfm1.value as 'source_env' ,
alfm2.value as 'website_id' ,
alfm3.value as 'index_id'
FROM admin_logs al
LEFT OUTER JOIN
(select * from admin_log_field_map where field_id = 3351
) alfm1 ON al.log_id = alfm1.log_id
LEFT OUTER JOIN
(select * from admin_log_field_map where field_id = 911
) alfm2 ON al.log_id = alfm2.log_id
, admin_log_field_map alfm3
WHERE 1=1
AND al.page_name='Index Management'
AND al.log_id=alfm3.log_id
AND alfm3.field_id=891
AND alfm3.value='jewelry'
ORDER BY al.log_id DESC;
+----+-------------+---------------------+--------+--------------------+------------+---------+---------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+--------+--------------------+------------+---------+---------------------+-------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 0 | const row not found |
| 1 | PRIMARY | al | ref | idx_622,idx_pgname | idx_pgname | 78 | const | 27720 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | alfm3 | ref | idx_1533,idx_0311 | idx_1533 | 10 | cms.al.log_id,const | 1 | Using where |
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 35358 | |
| 3 | DERIVED | admin_log_field_map | ref | idx_0311 | idx_0311 | 5 | | 33098 | Using where |
| 2 | DERIVED | admin_log_field_map | ref | idx_0311 | idx_0311 | 5 | | 1 | Using where |
+----+-------------+---------------------+--------+--------------------+------------+---------+---------------------+-------+----------------------------------------------+
6 rows in set (0.20 sec)
mysql>
mysql>
mysql> show create table admin_logs\G
*************************** 1. row ***************************
Table: admin_logs
Create Table: CREATE TABLE `admin_logs` (
`row_mod` datetime DEFAULT NULL,
`row_create` datetime DEFAULT NULL,
`log_id` int(11) DEFAULT NULL,
`user_id` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`page_name` varchar(75) COLLATE latin1_bin DEFAULT NULL,
`section_name` varchar(125) COLLATE latin1_bin DEFAULT NULL,
`submit_time` datetime DEFAULT NULL,
`url` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`source_environment` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`log` longtext COLLATE latin1_bin,
UNIQUE KEY `idx_622` (`log_id`),
KEY `idx_pgname` (`page_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)
mysql> \! hostname
fol-piwikdb01.dca.ftd.untd.com
mysql> show create table admin_log_field_map\G
*************************** 1. row ***************************
Table: admin_log_field_map
Create Table: CREATE TABLE `admin_log_field_map` (
`row_mod` datetime DEFAULT NULL,
`row_create` datetime DEFAULT NULL,
`log_id` int(11) DEFAULT NULL,
`field_id` int(11) DEFAULT NULL,
`value` varchar(255) COLLATE latin1_bin DEFAULT NULL,
UNIQUE KEY `idx_1533` (`log_id`,`field_id`),
KEY `idx_0311` (`field_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)
how to get better performance using above query?
Temos um problema pendente com VCC em chicago, há um problema de conexão com o banco de dados mysql Se um agente fizer uma atualização do monitor, ele será desconectado. Este é o erro que obtemos quando o servidor da web vcc é iniciado. [Erro: o servidor MySQL está solicitando o antigo e inseguro mecanismo de autenticação pré-4.1. Atualize a senha do usuário ou use a opção {insecureAuth: true}.] código: 'HANDSHAKE_INSECURE_AUTH', fatal: true } Eu fiz a etapa abaixo, mas isso o problema ainda existe. Por favor, verifique. senhas_antigas desativadas ALTER TABLE agent_login_sessionCHANGE operatorId operatorIdVARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL – concluído
· info: socket.io started
###### Session SB Server connected fail! ######
{ [Error: MySQL server is requesting the old and insecure pre-4.1 auth mechanism.Upgrade the user password or use the {insecureAuth: true} option.] code: 'HANDSHAKE_INSECURE_AUTH', fatal: true }
1. update mysql to use long password()
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=0
2. ALTER TABLE `agent_login_session` CHANGE `operatorId` `operatorId` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
Please help me here.