MySQL Gravação Lenta

Question

As inserções na tabela a seguir levam até 70 segundos para serem concluídas:

CREATE TABLE IF NOT EXISTS 'productsCategories' (
  'categoriesId' int(11) NOT NULL,
  'productsId' int(11) NOT NULL,
  PRIMARY KEY ('categoriesId','productsId'),
  KEY 'categoriesId' ('categoriesId')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Existem cerca de 100.000 linhas na tabela, e está levando 7 MB no disco.

Existem algumas configurações no MySQL que podem melhorar o desempenho de gravação?

Meu arquivo my.cnf é o seguinte:

log-slow-queries="/var/log/mysql/slow-query.log"
long_query_time=1 
log-queries-not-using-indexes

innodb_buffer_pool_size=4G
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT

query_cache_size = 6G
key_buffer_size = 284M
query_cache_limit = 1024M
thread_cache_size = 128
table_cache = 12800

sort_buffer_size=2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M

read_buffer_size=128K

open_files_limit               = 1000
table_definition_cache         = 1024
table_open_cache               = 6000

max_heap_table_size=512M
tmp_table_size=4096M

max_connections=1000

thread_concurrency = 24

Aqui está a configuração do hardware:

• Dell R710
• RAID10
• 48G de RAM

Dado esse hardware, não esperaria que o problema fosse um gargalo de hardware.

    

Answer 1

OBSERVAÇÃO # 1

A primeira coisa que me chama a atenção é a estrutura da tabela

CREATE TABLE IF NOT EXISTS 'productsCategories' (
  'categoriesId' int(11) NOT NULL,
  'productsId' int(11) NOT NULL,
  PRIMARY KEY ('categoriesId','productsId'),
  KEY 'categoriesId' ('categoriesId')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Observe que o categoriesId index e a PRIMARY KEY começam com a mesma coluna. É um índice redundante. Como esta tabela é InnoDB, o categoriesId index é redundante por outro motivo: Todos os índices secundários contêm chaves em gen_clust_index (também conhecido como Clustered Index; Consulte para que é usado o gen_clust_index no mysql? )

Se você remover o índice categoriesId com

ALTER TABLE productsCategories DROP INDEX categoriesId;

isso melhorará os INSERTs dramaticamente porque não é necessário fazer manutenção extra no índice Secundário e em Cluster.

OBSERVAÇÃO # 2

Se você estiver fazendo operações de inserção em massa, precisará de um grande buffer de inserção em massa .

Por favor, veja minhas postagens anteriores sobre isso:

• link
• link
• link

OBSERVAÇÃO # 3

O tamanho do seu arquivo de log é muito pequeno !!! Deve ser 25% do InnoDB Buffer Pool, que no seu caso deve ser 1G. Veja meu artigo sobre como redimensionar os arquivos de log do InnoDB .

OBSERVAÇÃO # 4

Por favor, não defina innodb_thread_concurrency !!! Aprendi em primeira mão no Percona Live NYC a deixar essa configuração em paz . Ele é desabilitado por padrão no MySQL 5.5, MySQL 5.1 InnoDB Plugin e Percona Server 5.1 +.

OBSERVAÇÃO # 5

Você precisa usar innodb_file_per_table. Se isso está desabilitado, eu faço a manutenção de arquivos no ibdata1 um pesadelo. Por favor, leia meu post sobre como limpar o InnoDB para implementar isso .

OBSERVAÇÃO # 6

Se você estiver usando o MySQL 5.5 ou o Percona Server, você deve definir certas opções para fazer com que o InnoDB use múltiplas CPUs / múltiplos núcleos. Por favor, veja meu post sobre essas configurações .

OBSERVAÇÃO # 7

Você tem innodb_log_buffer_size=4M . O padrão é 8M. Isso causará o dobro de descarga nos logs de redo. Isso também neutralizará sua configuração de innodb_flush_log_at_trx_commit=2 . Por favor, defina-o para 32M. Além disso, consulte a Documentação do MySQL em innodb_log_buffer_size .

À luz dessas observações, adicione ou substitua as seguintes configurações:

[mysqld]
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000
innodb_file_per_table
innodb_log_file_size=1G
innodb_log_buffer_size=1G
bulk_insert_buffer_size = 256M

    

Answer 2

Você deve verificar innodb_log_file_size , a configuração padrão é 5M, o que é bastante baixo para configurações intensivas de gravação. Considere configurá-lo para 100M. Você terá que excluir os arquivos ib_logfile* antigos para iniciar o banco de dados com novas configurações. Por favor, não apague os arquivos de log enquanto o servidor DB estiver rodando, você terá que pará-lo primeiro. Provavelmente você deve fazer o backup dos arquivos de log antigos primeiro, e não apenas excluí-los.