Mysql InnoDB desempenho do tamanho da tabela

Question

Eu tenho uma tabela que está se aproximando de 2 milhões de registros. Esta tabela armazena um histórico de transações. Isto está em um site de alto tráfego, mas também a tabela não é acessada regularmente. Atualmente, não temos consultas lentas devido a essa tabela, mas estamos pensando em quando eu precisaria migrar os dados dessa tabela para um método de arquivamento de dados.

O servidor é uma caixa média amazon ec2 high cpu.

High-CPU Medium Instance

1.7 GB of memory
5 EC2 Compute Units (2 virtual cores with 2.5 EC2 Compute Units each)
350 GB of instance storage
32-bit platform
I/O Performance: Moderate
API name: c1.medium


         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1677878
 Avg_row_length: 71
    Data_length: 120209408
Max_data_length: 0
   Index_length: 246497280
      Data_free: 0
 Auto_increment: 1914179
    Create_time: 2011-08-07 20:15:29
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: InnoDB free: 7168 kB

Poucas perguntas:

• Nesse ponto, posso começar a ver o desempenho degradar devido à quantidade de registros nesta tabela?

• Quais configurações devo verificar no servidor mysql, via SHOW INNODB STATUS e na própria tabela (ou seja, formato de linha) para garantir que eu sou maximizar o desempenho conforme necessário?

• Quais métricas devo coletar para calcular o desempenho tempo?

Answer 1

Tantas perguntas !! Para seu primeiro ponto, você provavelmente começará a notar problemas de desempenho se começar a fazer varreduras de tabela completas ou junções que criam tabelas temporárias. Você pode acompanhar isso observando a saída EXPLAIN para suas consultas. Aqui estão algumas informações sobre EXPLAIN:

link

Para as configurações, idealmente, é ótimo se você puder colocar todo o seu banco de dados na memória. Aqui estão as variáveis que provavelmente são mais úteis para ajustar:

innodb_buffer_pool_size=8192M
innodb_additional_mem_pool_size=512M
innodb_log_buffer_size=8M
innodb_flush_method = O_DIRECT
key_buffer_size=4096M
read_buffer_size=1M
read_rnd_buffer_size=10M
sort_buffer_size=1M
join_buffer_size=1M
tmp_table_size=32M

Você vai querer tornar a maioria deles tão grande quanto razoável (você vai querer pesquisar alguns deles, pois alguns são por conexão e alguns são globais). Você mencionou SHOW INNODB STATUS, que é um ótimo lugar para começar, tente ver esses campos:

--------
FILE I/O
--------
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
28889 OS file reads, 52982178 OS file writes, 35063424 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 2.67 writes/s, 2.67 fsyncs/s

Estes devem mostrar se você está preso por IO em algum lugar. Então verifique:

----------------------
BUFFER POOL AND MEMORY
----------------------

Free buffers       342668

para garantir que você tenha buffers gratuitos.

Em relação à sua última pergunta sobre o que monitorar, mais é melhor. Eu uso o OpenNMS para monitorar o desempenho do nosso servidor MySQL. Aqui estão as coisas que atualmente tendemos / alertamos sobre mudanças drásticas:

EventCount
Uptime
Bytes_received
Bytes_sent
Com_delete
Com_delete_multi
Com_insert
Com_insert_select
Com_select
Com_stmt_execute
Com_update
Com_update_multi
Created_tmp_disk_tables
Created_tmp_tables
key_buffer_size
key_cache_block_size
Key_blocks_unused
Key_read_requests
Key_reads
Key_write_requests
Key_writes
Open_files
Open_tables
table_open_cache
Questions
Slow_queries
Connections
Threads_created
Threads_cached
Threads_connected
Threads_running