Início / dba / Perguntas / 65504
Accepted
Ram
Asked: 2014-05-21 11:02:11 +0800 CST

Particionando uma grande tabela mysql

  • 772

Eu tenho uma tabela grande (40 GB) que gostaria de particionar. As linhas são essencialmente escritas uma vez lidas muitas. Atualmente estou usando mysql 5.5 innodb com file_per_table. É difícil otimizar essa tabela, pois a reconstrução de todo o arquivo leva muito tempo.

Estou procurando particionar a tabela de forma que haja apenas uma partição sendo gravada; Acho que isso significará que otimizar a tabela levará muito menos tempo, pois cada arquivo não atual será otimizado apenas uma vez e não precisará ser tocado novamente.

Eu sou novo no particionamento de tabelas no mysql e não tenho certeza de qual é o caminho certo para fazer isso. Eu sei que não há esquema de particionamento de 'tamanho de arquivo', então a próxima melhor coisa é SWAG no intervalo de linhas que resultará em tamanhos de arquivo que eu preferiria (3-4 GB parece bom - então cerca de 3-4 arquivos/ano em nosso taxa atual). Meu pensamento é ativar a partição, idmas isso não pode atender aos requisitos técnicos do particionamento de arquivos ("Todas as colunas usadas na expressão de particionamento para uma tabela particionada devem fazer parte de cada chave exclusiva que a tabela possa ter."). Então, qual é a maneira certa de fazer isso? A seguir está a definição da tabela reduzida às partes importantes:

+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| TransactionId   | int(11)      | NO   | MUL | NULL    |                |
| Parent          | int(11)      | NO   | MUL | 0       |                |
| Headers         | longtext     | YES  |     | NULL    |                |
| Creator         | int(11)      | NO   |     | 0       |                |
| Created         | datetime     | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

O tempo voa... Passamos a usar o repositório mysql.com e atualizamos para 5.6. Hora de um julgamento. Usando uma tabela menor, tentei usar a otimização do Online DDL. Não estou obtendo o resultado esperado:

mysql> optimize table Users;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| rt4.Users | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| rt4.Users | optimize | status   | OK                                                                |
+-----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.43 sec)

mysql> desc Users;
+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| id                    | int(11)      | NO   | PRI | NULL    | auto_increment |
| Name                  | varchar(200) | NO   | UNI | NULL    |                |
| Password              | varchar(256) | YES  |     | NULL    |                |
| AuthToken             | varchar(16)  | YES  |     | NULL    |                |
| Comments              | text         | YES  |     | NULL    |                |
| Signature             | text         | YES  |     | NULL    |                |
| EmailAddress          | varchar(120) | YES  | MUL | NULL    |                |
| FreeformContactInfo   | text         | YES  |     | NULL    |                |
| Organization          | varchar(200) | YES  |     | NULL    |                |
| RealName              | varchar(120) | YES  |     | NULL    |                |
| NickName              | varchar(16)  | YES  |     | NULL    |                |
| Lang                  | varchar(16)  | YES  |     | NULL    |                |
| EmailEncoding         | varchar(16)  | YES  |     | NULL    |                |
| WebEncoding           | varchar(16)  | YES  |     | NULL    |                |
| ExternalContactInfoId | varchar(100) | YES  |     | NULL    |                |
| ContactInfoSystem     | varchar(30)  | YES  |     | NULL    |                |
| ExternalAuthId        | varchar(100) | YES  |     | NULL    |                |
| AuthSystem            | varchar(30)  | YES  |     | NULL    |                |
| Gecos                 | varchar(16)  | YES  |     | NULL    |                |
| HomePhone             | varchar(30)  | YES  |     | NULL    |                |
| WorkPhone             | varchar(30)  | YES  |     | NULL    |                |
| MobilePhone           | varchar(30)  | YES  |     | NULL    |                |
| PagerPhone            | varchar(30)  | YES  |     | NULL    |                |
| Address1              | varchar(200) | YES  |     | NULL    |                |
| Address2              | varchar(200) | YES  |     | NULL    |                |
| City                  | varchar(100) | YES  |     | NULL    |                |
| State                 | varchar(100) | YES  |     | NULL    |                |
| Zip                   | varchar(16)  | YES  |     | NULL    |                |
| Country               | varchar(50)  | YES  |     | NULL    |                |
| Timezone              | varchar(50)  | YES  |     | NULL    |                |
| PGPKey                | text         | YES  |     | NULL    |                |
| Creator               | int(11)      | NO   |     | 0       |                |
| Created               | datetime     | YES  |     | NULL    |                |
| LastUpdatedBy         | int(11)      | NO   |     | 0       |                |
| LastUpdated           | datetime     | YES  |     | NULL    |                |
| SMIMECertificate      | text         | YES  |     | NULL    |                |
+-----------------------+--------------+------+-----+---------+----------------+
36 rows in set (0.00 sec)

mysql> select @@VERSION;
+------------+
| @@VERSION  |
+------------+
| 5.6.19-log |
+------------+
1 row in set (0.00 sec)

o que estou perdendo?

mysql partitioning

1 respostas

  1. Best Answer

    Primeiro, você deve considerar resolver o problema de outra maneira.

    • Atualize para o MySQL 5.6, onde OPTIMIZE TABLEfunciona sem bloqueio (para uma tabela InnoDB), pois é suportado pelo InnoDB Online DDL .

    • Se você não conseguir atualizar, tente usar o pt-online-schema-change do Percona Toolkit, que pode executar a reconstrução da tabela sem bloquear.

      $ pt-online-schema-change h=localhost,D=mydatabase,t=mytable --execute
    --alter="ENGINE=InnoDB"

    Se você está preso em usar partição, sim, você deve criar ida chave de partição na tabela que você mostra. Você pode converter a tabela em particionamento com ALTER TABLE. Se você precisar que a operação de conversão seja sem bloqueio, use pt-online-schema-change.

    Não há como particionar em partições de tamanho fixo. Você tem que particionar por valores. Mas é realmente tão importante atingir um tamanho específico por partição?

    Re seu comentário sobre o tamanho da partição:

    Ao usar o particionamento RANGE, o que faço é configurar um agendamento para ALTER TABLE e dividir a última partição de tempos em tempos. Se você tiver uma taxa regular de crescimento, isso é fácil, mas se você tiver padrões irregulares de crescimento, poderá configurar uma verificação periódica que examine o número de linhas por partição (use o INFORMATION_SCHEMA.PARTITIONS ) e envie um e-mail se está ficando cheio.

    Por exemplo, vamos configurar uma tabela particionada por intervalo em id.

    CREATE TABLE `mytable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `transactionid` int(11) NOT NULL,
  `parent` int(11) NOT NULL,
  `headers` longtext,
  `creator` int(11) NOT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `transactionid` (`transactionid`,`parent`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (3000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

    À medida MAX(id)que se aproxima de 3000, está chegando perto de encher p3e transbordar para p4. Então é hora de reorganizar. É bom fazer isso antes que qualquer dado transborde para p4, porque a reorganização afetará apenas a última partição vazia e, portanto, será muito rápida.

    ALTER TABLE mytable REORGANIZE PARTITION p4 INTO 
(PARTITION p4 VALUES LESS THAN (4000), PARTITION p5 VALUES LESS THAN MAXVALUE);

    Mesmo que você perca um dia e obtenha alguns dados no antigo p4, é provável que não sejam muitos dados. Mas se você negligenciar isso por um ou dois meses e p4encher com muitos dados, a REORGANIZAÇÃO demorará mais.

    • 8

relate perguntas