Início / dba / Perguntas / 95740
Accepted
Mark Hansen
Asked: 2015-03-20 14:36:32 +0800 CST

ALTER coluna de chave primária de INT para BIGINT em produção (MySQL 5.6.19a)

  • 772

Algumas tabelas INNODB em nosso banco de dados de produção estão prestes a atingir o limite INT AUTO_INCREMENT de 2147483647 e precisamos alterá-las para BIGINT, caso contrário as gravações começarão a falhar.

As tabelas estão em um banco de dados MySQL 5.6.19a de produção em execução no Amazon RDS.

Como podemos fazer um ALTER assim sem atrapalhar as leituras e inserções de produção que estão acontecendo o tempo todo?

ALTER TABLE MYTABLECHANGE id idBIGINT NOT NULL AUTO_INCREMENT;

Aqui está o DDL para a tabela:

CREATE TABLE `MYTABLE` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `siteId` int(11) NOT NULL,
  `filter` varchar(10) NOT NULL DEFAULT 'ALL',
  `date` varchar(10) NOT NULL,
  `cards` varchar(250) NOT NULL,
  `apples` varchar(45) NOT NULL,
  `carrots` varchar(45) NOT NULL,
  `corn` varchar(45) NOT NULL,
  `peas` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`siteId`,`filter`,`date`,`cards`),
  KEY `date_k` (`date`),
  KEY `cards_k` (`cards`),
  KEY `apples_k` (`apples`),
  KEY `siteId_k` (`siteId`)
) ENGINE=InnoDB AUTO_INCREMENT=1748961482 DEFAULT CHARSET=utf8
mysql innodb

3 respostas

  1. Best Answer

    Se você tiver espaço suficiente, poderá criar uma cópia da tabela real e fazer o trabalho nela:

    CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

    Em seguida, você pode alterar a coluna conforme desejado:

    ALTER TABLE tbl_name MODIFY COLUMN col_name BIGINT AUTO_INCREMENT;

    Depois que o processo estiver concluído, você poderá renomear as tabelas:

    RENAME TABLE tbl_name TO new_tbl_name, tbl_name2 TO new_tbl_name2;

    Em seguida, solte a tabela original e você deverá ter o resultado esperado.

    • 26

  2. O kit de ferramentas percona é o caminho a percorrer, pelo menos se você não tiver muito tempo. A conversão levou em nossa mesa (500Gb, configuração master-slave) quando testamos um pouco mais de 24h, em produção levou (com hardware melhor) quase 1 mês (sidenote engraçado que tínhamos cerca de 30 dias antes de ficarmos sem ids, portanto já começamos a planejar o plano B e C, trabalhando com backups offline, removendo slaves,... ). O atraso foi principalmente devido à espera da replicação acontecendo para os escravos (permitimos um atraso máximo de 50 segundos). Certifique-se também de limitar o número de threads simultâneos. Temos mais de 2 milhões de inserções/dia e muitos milhões de leituras.

    Também esteja ciente de que, uma vez iniciada a cobertura, você não pode pará-la (ou pelo menos não encontramos nenhuma maneira de reiniciá-la) :-(

    • 5

  3. Nós iremos....

    KEY TOP_QUERIES_LAST_30DAYS_fk (siteId) é redundante com a CHAVE PRIMÁRIA, então você também pode SOLTAR.

    INT UNSIGNED levaria você a 4 bilhões, será suficiente?

    Considere mudar filterpara um ENUM.

    Você tem 1,75 bilhão de linhas? Ou você "queimou" muitos ids? Se sim, talvez possamos consertar isso? Por exemplo REPLACEe certos sabores de INSERTids de lance de vontade. INSERT...ON DUPLICATE KEYgeralmente pode substituir REPLACE. Um processo de 2 etapas pode evitar INSERT IGNOREa queima de ids.

    Voltando à pergunta...

    Veja se pt-online-schema-change fará o truque: http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html

    • 1

relate perguntas