JIStone's questions

Estou recebendo um erro 'servidor msyql desapareceu' às vezes ao fazer uma inserção binária grande, então queria verificar as configurações no mysql, coisas como wait_timeoute max_allowed_packet. No entanto

show create table mysql.user

retorna

[Err] 1142 - SELECT command denied to user '557574_prod'@'96.25.95.162' for table 'user'

Prefiro não entrar em contato com o suporte técnico sempre que quiser saber algumas das configurações relevantes, então gostaria de saber se pode haver uma solução alternativa.

Não tenho controle sobre coisas como tmp_table_sizee max_heap_table_size, portanto, à medida que nossas tabelas crescem, o tempo gasto por consultas que exigem tabelas temporárias aumenta geometricamente.

Eu estou querendo saber se existe uma maneira de impedir que o MySQL use tabelas temporárias para essas consultas? Qual seria a melhor abordagem nesta situação:

Aqui está um exemplo do maior infrator:

SELECT `skills`.`id`
FROM (`jobs_skills`)
JOIN `jobs` ON (`jobs`.`id` = `jobs_skills`.`job_id`)
JOIN `skills` ON (`skills`.`id` = `jobs_skills`.`skill_id`)
WHERE `jobs`.`job_visibility_id` = 1
AND `jobs`.`active` = 1
AND `skills`.`valid` = 1
AND `jobs_skills`.`skill_id` IN (96,101,103,108,121,2610,99,119,2607,102,104,112,113,122,1032,1488,2608,109,126,1438,2310,2318,2622,118,1046,1387,2609,100,116,123,2611,2612,2616,2618,114,127,1562,1587,1608,2276,2615,125,1070,1071,1161,1658,2613,2614,2617,105,110,111,120,1394,1435)
GROUP BY `jobs_skills`.`job_id`

para o qual copying to temp tablelevou 107 segundos, 99% do tempo total da consulta.

apesar dos medos da síndrome tl;dr, estou oferecendo. . .

MAIS DETALHES

Aqui está a EXPLAINdeclaração para a consulta:

+----+-------------+-------------+--------+----------------------+--------------+---------+----------------------------------+--------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys        | key          | key_len | ref                              | rows   | Extra                                        |
+----+-------------+-------------+--------+----------------------+--------------+---------+----------------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | jobs        | ref    | PRIMARY,active_index | active_index | 1       | const                            | 468958 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | jobs_skills | ref    | PRIMARY              | PRIMARY      | 4       | 557574_prod.jobs.id              |      1 | Using where; Using index                     |
|  1 | SIMPLE      | skills      | eq_ref | PRIMARY              | PRIMARY      | 4       | 557574_prod.jobs_skills.skill_id |      1 | Using where                                  |
+----+-------------+-------------+--------+----------------------+--------------+---------+----------------------------------+--------+----------------------------------------------+

e aqui estão as CREATE TABLEdeclarações para as tabelas relevantes:

| jobs  | CREATE TABLE `jobs` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` int(10) unsigned NOT NULL,
  `title` varchar(40) NOT NULL,
  `description` text NOT NULL,
  `address_id` int(10) unsigned NOT NULL,
  `proximity` smallint(3) unsigned NOT NULL default '15',
  `job_payrate_id` tinyint(1) unsigned NOT NULL default '1',
  `payrate` int(10) unsigned NOT NULL,
  `start_date` int(10) unsigned NOT NULL,
  `job_start_id` tinyint(1) unsigned NOT NULL default '1',
  `duration` tinyint(1) unsigned NOT NULL COMMENT 'Full-time, Part-time, Flexible',
  `posting_date` int(10) unsigned NOT NULL,
  `revision_date` int(10) unsigned NOT NULL,
  `expiration` int(10) unsigned NOT NULL,
  `active` tinyint(1) unsigned NOT NULL default '1',
  `team_size` tinyint(2) unsigned NOT NULL default '1',
  `job_type_id` tinyint(1) unsigned NOT NULL default '1',
  `job_shift_id` tinyint(1) unsigned NOT NULL default '1',
  `job_visibility_id` tinyint(1) unsigned NOT NULL default '1',
  `position_count` smallint(5) unsigned NOT NULL default '1',
  `impressions` int(10) unsigned NOT NULL default '0',
  `clicks` int(10) unsigned NOT NULL default '0',
  `employer_email` varchar(100) NOT NULL default '',
  `job_source_id` smallint(6) unsigned NOT NULL default '0',
  `job_password` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `active_index` (`active`),
  KEY `user_id_index` (`user_id`),
  KEY `address_id_index` (`address_id`),
  KEY `posting_date_index` USING BTREE (`posting_date`)
) ENGINE=InnoDB AUTO_INCREMENT=875013 DEFAULT CHARSET=utf8

-

| jobs_skills | CREATE TABLE `jobs_skills` (
  `job_id` int(10) unsigned NOT NULL,
  `skill_id` int(10) unsigned NOT NULL,
  `required` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY  (`job_id`,`skill_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

-

| skills | CREATE TABLE `skills` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `parent_id` int(10) unsigned NOT NULL,
  `name` varchar(35) NOT NULL default '',
  `description` varchar(250) NOT NULL,
  `valid` tinyint(1) unsigned NOT NULL default '0',
  `is_category` tinyint(1) unsigned NOT NULL default '0',
  `last_edited` int(10) unsigned NOT NULL default '0',
  `impressions` int(10) unsigned NOT NULL default '0',
  `clicks` int(10) unsigned NOT NULL default '0',
  `jobs` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `parent` (`parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2657 DEFAULT CHARSET=utf8 |

Como eu disse, esta não é a única consulta com este problema, então qualquer conselho geral seria muito útil, embora eu não recuse nenhum conselho específico para esta consulta.

Estamos recebendo um erro intermitente em nosso site:

Aviso: mysql_connect() [function.mysql-connect]: O usuário '557574_prod' excedeu o recurso 'max_user_connections' (valor atual: 10)

O que impede que o site seja acessado e efetivamente está fora do ar.

(obviamente, aumentar o 'max_user_connections' resolveria isso, mas isso não é uma opção em nosso esquema de hospedagem)

Posso ter 'consertado', mas não tenho 100% de certeza de que entendi o problema e gostaria de ser claro. Achei que o problema devia ser causado por muitos trabalhos cron usando as conexões (temos alguns em execução a cada minuto) e reduzi o número de trabalhos cron e isso parecia diminuir a quantidade de erros. Eu criei uma conta de usuário separada para os trabalhos cron, portanto, se esse era o problema, ele deve ser resolvido agora.

No entanto, nosso sistema de registro não registra o erro quando ele ocorre, por isso ainda estou preocupado.

Então eu estou perguntando: o que constitui um 'user_connection' no MySQL?

• existe uma conexão separada para cada chamada de banco de dados?
• caso contrário, quando as chamadas de banco de dados exigiriam uma conexão separada?
• por que esse erro ocorreria apenas no site de produção ao vivo? talvez haja uma conexão separada para cada ip ou simialr exclusivo?

Eu sinto que estou pensando demais nisso. . .

OK, eu estava adicionando um índice a uma tabela e verifiquei o progresso usando show processliste sob stateele output Repair by sorting.

Dos documentos do MySQL:

Reparar por classificação

O código de reparo está usando uma classificação para criar índices.

Então, qual é o 'código de reparo'? Algo quebrou?

Eu tenho uma tabela com um UNIQUEíndice de várias colunas _job_id__e __keyword_id__.

Eu também precisaria adicionar outro índice __job_id__se tivesse uma consulta frequente que executasse uma GROUP BYnessa coluna?

(com 100 milhões de linhas pode demorar um pouco. É por isso que estou perguntando em vez de apenas fazer)

A eliminação de um índice duplicado no MySQL estava demorando bastante, então, enquanto esperava, pesquisei sobre isso e encontrei esta postagem de 2006, falando sobre como o MySQL lida ADDe DROPindexa.

Se uma tabela T é uma tabela MySQL com quatro índices (ndx1,ndx2,ndx3,ndx4) e você deseja 'alter table T drop index ndx3;' aqui está exatamente o que acontece sob o capô:

1) O MySQL copia T.MYD para uma tabela temporária, ou seja, S.MYD e um S.MYI de zero byte. 2) MySQL faz 'alter table S add index ndx1 (...); 3) MySQL faz 'alter table S add index ndx2 (...); 4) MySQL faz 'alter table S add index ndx4 (...); 5) MySQL exclui T.MYD e exclui T.MYI 6) MySQL renomeia S.MYD para T.MYD e renomeia S.MYI para T.MYI

Isso ainda é verdade? O conselho dele ainda é válido?

Dada a mesma tabela MyISAM T com quatro índices (ndx1,ndx2,ndx3,ndx4) e você deseja 'alter table T drop index ndx3;' tente isso em vez disso:

1) criar tabela T1 como T; Isso cria uma tabela vazia T1 com índices ndx1,ndx2,ndx3 e ndx4. 2) alterar tabela T1 drop index ndx3; Isso descarta o índice ndx3 no T1 vazio, que deve ser instantâneo. 3) insira em T1 selecione * de T; Isso preencherá a tabela T e carregará todos os três (3) índices para T1 em uma passagem. 4) tabela drop table T; 5) alterar o nome da tabela T1 para T;

Como vocês lidam com a adição e remoção de índices de tabelas grandes?

Às vezes, durante um SNAFU, tenho que correr kill query xxxxxxxvinte ou trinta vezes. Algum tipo de kill allcomando que estou perdendo?

Por conta de como eu não gosto de digitar.

O site em que estou trabalhando tem muita atividade de back-end/cron, então há entre 5 e 15 consultas sendo executadas a cada segundo do dia. Isso pode retardar o carregamento da página em um segundo ou mais.

Eu estava pensando que a melhor coisa a fazer seria criar dois bancos de dados e sincronizá-los diariamente. No entanto:

• eu não sei como fazer isso
• Estou preocupado que a sincronização seja lenta e bloqueie o acesso ao banco de dados. Eu realmente não gosto da ideia de deixar o site offline, mesmo que por 10 minutos às 3 da manhã.

Então eu me pergunto:

• Bancos de dados separados são a melhor solução para esse problema ou outra coisa seria melhor?
• Como eu sincronizaria o banco de dados sem interferir muito na 'experiência do usuário'?

Muito obrigado!

BTW rodando PHP/MySQL em servidores 'poderia'.

Eu só posso imaginar fazer isso com duas consultas de banco de dados. O primeiro encontra o número de linhas no banco de dados e o segundo seleciona com um ORDER BYna coluna em que estou interessado e LIMIT X, 1onde X é metade do número de linhas.

Existe uma maneira simples de fazer isso com apenas uma consulta?

No momento estou usando médias em meus cálculos, mas acho que a média seria melhor; não há limite superior para os valores e eles são limitados a partir de baixo por 0.

EDIT: sim, eu quis dizer 'mediana', mas estava tendo algum erro cerebral e procurei 'média'. Agora encontrei a resposta no stackoverflow