Ajustando o MySQL de 1.3 G DB, 8GB Ram, High IO Wait

Navegue suas respostas
2

Estou tendo um banco de dados MySQL de 1,3 GB em 8 máquinas virtuais, 8 GB de memória e estou tendo problemas para otimizar a configuração. Há muitas linhas pequenas e muitas associações acontecendo.

Eu encontrei sobre essa ferramenta: link , mas as diferenças sugeridas estão apenas piorando o resultado. Primeiramente, eles sugerem aumentar o buffer pool do InnoDB para 6 GB. Atualmente estou tendo 1 GB de buffer pool, mas quando tento aumentá-lo, o desempenho é realmente pior. Por que isso está acontecendo?

Existe uma maneira de forçar o MySQL a armazenar em cache todo o banco de dados na memória, para que ele não fique preso a níveis tão altos de IO?

O resto das configurações não parece fazer essa diferença.

Estado atual do my.cnf: link

    
por Vojtěch 09.04.2013 / 17:31

1 resposta

4

Olhando para o meu my.cnf, você tem padrões para muitas coisas. O que você precisa desesperadamente é de uma limpeza completa da infra-estrutura do InnoDB. Clique aqui para ver a Representação Pictórica da Infra-estrutura InnoDB .

PASSO 01) Execute esta consulta 

SELECT CEILING(Total_InnoDB_Bytes/POWER(1024,2)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Isso lhe dirá o tamanho da configuração de innodb_buffer_pool_size (em MB).

Digamos que a resposta surgiu em 1024.

PASSO 02) Execute esta consulta 

SELECT CEILING(Total_MYI_Bytes/POWER(1024,2)) RKBS FROM
(SELECT SUM(index_length) Total_MYI_Bytes FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN
('information_schema','performance_schema','mysql')) A;

Isto irá dizer-lhe o quão grande é definir key_buffer_size (em MB).

Digamos que a resposta surgiu em 64.

PASSO 03: Execute esta consulta 

SET GLOBAL innodb_fast_shutdown = 0;

Isso executará uma confirmação transacional completa de qualquer Transação restante nos Logs de transações durante o encerramento. Os logs de transações são

  • /var/lib/mysql/ib_logfile0
  • /var/lib/mysql/ib_logfile1

PASSO 04: despeje todos os dados em um arquivo de texto 

cd /root
mkdir datadump
cd datadump
MYSQLDUMP_CONNECT="-uroot -ppassword"
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --flush-privileges --all-databases"
mysqldump ${MYSQLDUMP_CONNECT} ${MYSQLDUMP_OPTIONS} > MySQLData.sql

PASSO 05: Shutdown MySQL 

service mysql stop

PASSO 06: Adicione o seguinte a my.cnf

Se você estiver usando o MySQL 5.5 (ou MySQL 5.1 com InnoDB Plugin Enabled), adicione estes 

[mysqld]
key_buffer_size=64M
innodb_file_per_table
innodb_open_files=1000
innodb_buffer_pool_size=1024M
innodb_log_file_size=256M
innodb_fast_shutdown=0
innodb_read_io_threads=16
innodb_write_io_threads=16
join_buffer_size=4M
read_buffer_size=4M
read_rndbuffer_size=4M

Se você estiver usando o MySQL 5.0, adicione estes 

[mysqld]
key_buffer_size=64M
innodb_file_per_table
innodb_open_files=1000
innodb_buffer_pool_size=1024M
innodb_log_file_size=256M
innodb_fast_shutdown=0
innodb_thread_concurrency=0
join_buffer_size=4M
read_buffer_size=4M
read_rndbuffer_size=4M

Por favor, note que eu escolhi innodb_log_file_size para 25% de innodb_buffer_pool_size

PASSO 07: Deixe espaço para novos arquivos InnoDB 

cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile'.bak
mv ibdata1 ib_data1.bak

PASSO 08: Inicie o MySQL 

service mysql start

PASSO 09: Coloque os dados de volta 

mysql -uroot -p < /root/datadump/MySQLData.sql

PASSO 10: Se tudo estiver funcionando melhor, execute isso: 

rm -f ib_logfile0.bak
rm -f ib_logfile1.bak
rm -f ibdat1.bak

Tudo feito com o InnoDB Cleanup.

Sugeri melhorias do InnoDB como essa muitas vezes antes

Experimente!

    
por 09.04.2013 / 20:36

Tags

A verificação de conformidade com o PCI do ControlScan trava meu servidor AWS. Como parar? Como adicionar corretamente o registro SPF