Como gerenciar uma tabela MySQL com mais de 2 milhões de registros

Question

Como gerenciar uma tabela MySQL com mais de 2 milhões de registros

#1 resposta do (4 votos)
#2 resposta do (2 votos)
#3 resposta do (0 votos)

1

Eu tenho uma tabela InnoDB rodando no MySQL 5.0.45 no CentOS. Para piorar, tudo está sendo executado em uma máquina virtual. Existem várias tabelas que têm a probabilidade de exceder 2 milhões de registros com muita facilidade. Tenho notado que começar em torno do 900K a 1M marca de registro O desempenho do banco de dados começa a diminuir.

Eu tenho todo o poder necessário para fazer qualquer e todas as mudanças para o futuro para manter esta coisa em pé, tanto quanto possível, dadas as circunstâncias. Eu deveria estar usando o MyIsam? Existem apenas alguns índices e estou mais preocupado em obter um bom desempenho de gravação. O programa que grava dados nas tabelas os agrupa em grupos de cerca de 250 solicitações por tabela e as executa em uma base por tabela para ajudar as coisas.

Eu incluí a instrução create table para uma das tabelas maiores, e sim, é uma tabela muito ampla - eu entendo. Eu tentei fazer as colunas o mais estreitas que eu posso, enquanto ainda ser capaz de acomodar de forma confiável os dados que chegam.

EDITAR:

O programa emprega transações para reverter as mudanças se algo atingir o ventilador durante a execução, mas é basicamente apenas bombeando dados para o estilo firehose do banco de dados. Uma corrida de 8 horas pode facilmente colocar 400 mil linhas em cada tabela como esta. Esta tabela é uma das 25 que são de tamanho similar e todas têm os mesmos índices. Eles estão todos unidos para consulta por LINE e RUN_ID. O desempenho de leitura, não estou particularmente preocupado. Estou tentando fazer a gravação o mais rápido possível.

CREATE TABLE IF NOT EXISTS 'TMD_INDATA_INVOICE' (
  'ID' int(11) NOT NULL auto_increment,
  'LINE' int(11) NOT NULL,
  'RUN_ID' int(11) NOT NULL,
  'INDATA_INVOICE_ALLOCATION_GROUP_NAME' varchar(128) default NULL,
  'INDATA_INVOICE_ALLOCATION_GROUP_OWNER' varchar(128) default NULL,
  'INDATA_INVOICE_ALLOCATION_NAME' varchar(128) default NULL,
  'INDATA_INVOICE_IS_AUDITED' varchar(5) default NULL,
  'INDATA_INVOICE_BASIS_PERCENT' varchar(32) default NULL,
  'INDATA_INVOICE_COUNTRY_OF_ORIGIN' varchar(64) default NULL,
  'INDATA_INVOICE_CUSTOMER_GROUP_NAME' varchar(128) default NULL,
  'INDATA_INVOICE_CUSTOMER_GROUP_OWNER' varchar(128) default NULL,
  'INDATA_INVOICE_CUSTOMER_NAME' varchar(128) default NULL,
  'INDATA_INVOICE_CUSTOMER_TAX_CATEGORY' varchar(128) default NULL,
  'INDATA_INVOICE_DELIVERY_TERMS' varchar(128) default NULL,
  'INDATA_INVOICE_DEPARTMENT_OF_CONSIGN' varchar(128) default NULL,
  'INDATA_INVOICE_DOCUMENT_TYPE' varchar(128) default NULL,
  'INDATA_INVOICE_END_USE' varchar(128) default NULL,
  'INDATA_INVOICE_END_USER_NAME' varchar(128) default NULL,
  'INDATA_INVOICE_FILTER_GROUP_NAME' varchar(128) default NULL,
  'INDATA_INVOICE_FILTER_GROUP_OWNER' varchar(128) default NULL,
  'INDATA_INVOICE_FISCAL_DATE' varchar(32) default NULL,
  'INDATA_INVOICE_INPUT_RECOVERY_TYPE' varchar(50) default NULL,
  'INDATA_INVOICE_INVOICE_NUMBER' varchar(128) default NULL,
  'INDATA_INVOICE_IS_AUDITING_MESSAGES' varchar(5) default NULL,
  'INDATA_INVOICE_IS_AUDIT_UPDATE' varchar(5) default NULL,
  'INDATA_INVOICE_IS_BUSINESS_SUPPLY' varchar(5) default NULL,
  'INDATA_INVOICE_IS_CREDIT' varchar(5) default NULL,
  'INDATA_INVOICE_IS_EXEMPT' varchar(5) default NULL,
  'INDATA_INVOICE_IS_NO_TAX' varchar(5) default NULL,
  'INDATA_INVOICE_IS_REPORTED' varchar(5) default NULL,
  'INDATA_INVOICE_IS_REVERSED' varchar(5) default NULL,
  'INDATA_INVOICE_IS_ROUNDING' varchar(5) default NULL,
  'INDATA_INVOICE_IS_SIMPLIFICATION' varchar(5) default NULL,
  'INDATA_INVOICE_MODE_OF_TRANSPORT' varchar(128) default NULL,
  'INDATA_INVOICE_MOVEMENT_DATE' varchar(32) default NULL,
  'INDATA_INVOICE_MOVEMENT_TYPE' varchar(128) default NULL,
  'INDATA_INVOICE_NATURE_OF_TRANSACTION_CODE' varchar(128) default NULL,
  'INDATA_INVOICE_OVERRIDE_AMOUNT' varchar(128) default NULL,
  'INDATA_INVOICE_OVERRIDE_RATE' varchar(32) default NULL,
  'INDATA_INVOICE_PORT_OF_ENTRY' varchar(128) default NULL,
  'INDATA_INVOICE_PORT_OF_LOADING' varchar(128) default NULL,
  'INDATA_INVOICE_PRODUCT_MAPPING_GROUP_NAME' varchar(128) default NULL,
  'INDATA_INVOICE_PRODUCT_MAPPING_GROUP_OWNER' varchar(128) default NULL,
  'INDATA_INVOICE_REGIME' varchar(128) default NULL,
  'INDATA_INVOICE_SUPPLY_EXEMPT_PERCENT' varchar(32) default NULL,
  'INDATA_INVOICE_SUPPLY_TYPE' varchar(128) default NULL,
  'INDATA_INVOICE_TITLE_TRANSFER_LOCATION' varchar(128) default NULL,
  'INDATA_INVOICE_VENDOR_NAME' varchar(128) default NULL,
  'INDATA_INVOICE_VENDOR_NUMBER' varchar(128) default NULL,
  'INDATA_INVOICE_VENDOR_TAX' varchar(128) default NULL,
  'INDATA_INVOICE_VERSION' varchar(5) default NULL,
  'INDATA_INVOICE_CALCULATION_DIRECTION' varchar(5) default NULL,
  'INDATA_INVOICE_CALLING_SYSTEM_NUMBER' varchar(128) default NULL,
  'INDATA_INVOICE_COMPANY_NAME' varchar(128) default NULL,
  'INDATA_INVOICE_COMPANY_ROLE' varchar(20) default NULL,
  'INDATA_INVOICE_CUSTOMER_NUMBER' varchar(128) default NULL,
  'INDATA_INVOICE_CURRENCY_CODE' varchar(32) default NULL,
  'INDATA_INVOICE_EXTERNAL_COMPANY_ID' varchar(128) default NULL,
  'INDATA_INVOICE_HOST_SYSTEM' varchar(128) default NULL,
  'INDATA_INVOICE_INVOICE_DATE' varchar(32) default NULL,
  'INDATA_INVOICE_POINT_OF_TITLE_TRANSFER' varchar(32) default NULL,
  'INDATA_INVOICE_REGISTRATIONS_BUYER_ROLE' varchar(32) default NULL,
  'INDATA_INVOICE_REGISTRATIONS_MIDDLEMAN_ROLE' varchar(32) default NULL,
  'INDATA_INVOICE_REGISTRATIONS_SELLER_ROLE' varchar(32) default NULL,
  'INDATA_INVOICE_VAT_GROUP_REGISTRATION' varchar(32) default NULL,
  'INDATA_INVOICE_TRANSACTION_TYPE' varchar(5) default NULL,
  'INDATA_INVOICE_UNIQUE_INVOICE_NUMBER' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE1' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE2' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE3' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE4' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE5' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE6' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE7' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE8' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE9' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE10' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE11' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE12' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE13' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE14' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE15' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE16' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE17' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE18' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE19' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE20' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE21' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE22' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE23' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE24' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE25' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE26' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE27' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE28' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE29' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE30' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE31' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE32' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE33' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE34' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE35' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE36' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE37' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE38' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE39' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE40' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE41' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE42' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE43' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE44' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE45' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE46' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE47' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE48' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE49' varchar(128) default NULL,
  'INDATA_INVOICE_USER_ELEMENT_ATTRIBUTE50' varchar(128) default NULL,
  'INDATA_INVOICE_ORIGINAL_DOCUMENT_ID' varchar(128) default NULL,
  'INDATA_INVOICE_ORIGINAL_DOCUMENT_ITEM' varchar(128) default NULL,
  'INDATA_INVOICE_ORIGINAL_DOCUMENT_TYPE' varchar(128) default NULL,
  'INDATA_INVOICE_ORIGINAL_INVOICE_DATE' varchar(32) default NULL,
  'INDATA_INVOICE_ORIGINAL_INVOICE_NUMBER' varchar(128) default NULL,
  'INDATA_INVOICE_ORIGINAL_MOVEMENT_DATE' varchar(32) default NULL,
  PRIMARY KEY  ('ID'),
  KEY 'RUN_ID' USING BTREE ('RUN_ID'),
  KEY 'LINE' ('LINE')
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4011 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table 'TMD_INDATA_INVOICE'
--
ALTER TABLE 'TMD_INDATA_INVOICE'
  ADD CONSTRAINT 'TMD_INDATA_INVOICE_ibfk_1' FOREIGN KEY ('RUN_ID') REFERENCES 'RunHistory' ('id') ON DELETE CASCADE;

performance mysql database-administration innodb performance-tuning

por manyxcxi 14.09.2010 / 23:27

3 respostas

2

certifique-se de que pelo menos seus índices se encaixam na memória. defina innodb_buffer_pool_size grande o suficiente. se você precisar de transações ou tiver muito acesso de gravação simultâneo - escolha o innodb.

se for lido principalmente e com poucas atualizações - myisam pereps + alocação de memória sintonizada para ele.

tente mysqltuner.pl para aconselhamento genérico e mergulhe no mysqlperformanceblog para detalhes mais detalhados.

por 14.09.2010 / 23:42

0

2mln de linhas não é demais. Talvez seja muito para o seu tamanho de VM?

Você não deve se preocupar com o número de linhas e com o tamanho do seu conjunto de dados. À medida que o tamanho do conjunto de dados aumenta, ele não cabe no buffer pool e começará a ler dados do disco.

Aqui está minha resposta sobre melhorando o desempenho de inserção / atualização do MySQL / InnoDB

por 23.09.2010 / 14:21

Tags performance mysql database-administration innodb performance-tuning

Pedido http estranho em access_log svn servers auto sincronizar

score 4 · Accepted Answer

Pelo que eu vejo, a tabela parece autocontida (ou seja, você não precisa fazer nenhum LOJ para extrair dados normalizados) para que o MyISAM possa certamente ter um efeito positivo na velocidade de acesso.

Em segundo lugar, e mais importante, você tem os índices corretos para suas consultas? 2 milhões de linhas são poucas, mas não são muitas. Você precisa examinar cuidadosamente todas as suas SELECT consultas e verificar se possui um índice apropriado para cada uma delas. Isso consumirá um pouco de espaço em disco, mas a desvantagem é tempos de consulta incrivelmente rápidos.

Em terceiro lugar, e isso é apenas uma preferência pessoal e não muito a ver com o seu problema específico, eu não acho, mas NDATA_INVOICE_USER_ELEMENT_ATTRIBUTE1 to NDATA_INVOICE_USER_ELEMENT_ATTRIBUTE50 - isso poderia ser projetado em um lote mais inteligente, movendo-os para uma tabela chamada DATA_INVOICE_USER_ELEMENT_ATTRIBUTES com um PK de INVID,ATTRIBUTEID e armazenando-os verticalmente, e imediatamente você economizou 6.25kb de espaço por linha.