O que definir innodb_buffer_pool e por quê ..?

A maior tabela que você possui representa 16,47% (28/170) do total de dados. Mesmo que a tabela tenha sido altamente escrita e lida, nem todos os 28G da tabela são carregados no buffer pool em um determinado momento. O que você precisa calcular é quanto do InnoDB Buffer Pool é carregado a qualquer momento no servidor de banco de dados atual .

Aqui está uma maneira mais granular de determinar innodb_buffer_pool_size para um novo servidor de banco de dados, dado o conjunto de dados atualmente carregado no pool de buffers InnoDB do servidor de banco de dados atual.

Execute o seguinte em sua instância MySQL atual (servidor do qual você está migrando)

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_data';  -- IBPDataPages 
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total'; -- IBPTotalPages
SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; -- IPS

Execute a fórmula IBPPctFull = IBPDataPages * 100.0 / IBPTotalPages.

SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPTotalPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'); -- SELECT @IBPTotalPages;
SET @IBPPctFull = CAST(@IBPDataPages * 100.0 / @IBPTotalPages AS DECIMAL(5,2));
SELECT @IBPPctFull;

Se IBPPctFull for 95% ou mais, você deve definir innodb_buffer_pool_size para 75% da RAM do servidor de banco de dados.

Se IBPPctFull for menor que 95%, execute esta fórmula: IBPSize = IPS X IBPDataPages / (1024*1024*1024) X 1.05.

SET @IBPSize = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_page_size'); -- SELECT @IBPSize;
SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPSize = concat(ROUND(@IBPSize * @IBPDataPages / (1024*1024*1024) * 1.05, 2), ' GB' );
SELECT @IBPSize;

O número de IBPSize (em GB) é o número que melhor se ajusta ao seu conjunto de dados de trabalho real.

Agora, se o IBPSize ainda for muito grande para o maior Amazon EC2 RAM Config, use 75% da RAM para o Amazon EC2 DB Server.

Estou fornecendo esta resposta como informação complementar à resposta de Rolando abaixo.

Antes do servidor estar em produção

Calcule innodb_buffer_pool_size com base nas maiores tabelas que são usadas com mais frequência pelo MySQL. Para identificar as maiores tabelas com base em seu tamanho no banco de dados, você pode usar este script:

select table_schema, table_name, round(data_length/1024/1024,2) as size_mb 
from information_schema.tables where table_schema like 'my_database' 
order by size_mb desc;


+--------------+-------------------------+---------+
| table_schema | table_name              | size_mb |
+--------------+-------------------------+---------+
| heavybidder     | user                 |   522.55|
| heavybidder     | bid                  |   121.52|
| heavybidder     | item_for_sale        |    10.52|
| heavybidder     | account_user         |    5.02 |
| heavybidder     | txn_log              |    4.02 |
| heavybidder     | category             |    0.02 |
+--------------+-------------------------+---------+

Agora que sabemos quais tabelas são as maiores em nosso banco de dados, precisamos determinar quais são as mais usadas. Para fazer isso, eu usaria um programa de criação de perfil como o Jet Profiler (JP) para ver quais tabelas estão sendo mais acessadas. O JP mostrará quais tabelas estão sendo acessadas com mais frequência. Aqui está uma captura de tela dessa seção em JP

Então, com isso em mente, agora sei que as tabelas de usuários e lances ocupam cerca de 640 MB de espaço em disco, são usadas com muita frequência de acordo com o JP e o que significa que o MySQL armazenará seus índices e dados no buffer pool como Rolando menciona abaixo em seus comentários.

Para garantir que o MySQL tivesse memória suficiente para armazenar dados para minhas tabelas maiores e mais usadas, eu definiria innodb_buffer_pool_size em 640 MB.

Existem algumas considerações adicionais, mas elas não se aplicam ao innodb_buffer_pool_size.

Este é um sistema de 32 bits ou 64 bits? Em um sistema de 32 bits, você está limitado a 4 GB, a menos que ative o PAE. No Windows, isso significa executar as edições Windows Enterprise ou Datacenter.

De quanta memória os outros processos em execução no seu sistema precisam? Em um servidor MySQL dedicado, deixarei entre 5% e 10% para o SO. No Windows, você pode usar o Process Explorer para analisar o uso da memória. No Linux, você tem sysstat, free, htop, top e vmstat.

O banco de dados é composto apenas de tabelas Innodb ou uma mistura de Innodb e MyISAM? Se for uma mistura dos dois, vou separar memória para o key_cache, variáveis ​​de junção, cache de consulta, etc. Você pode calcular mais tarde sua taxa de acertos do MyISAM quando o servidor estiver em produção.

Depois que o servidor estiver em produção

Qual é a taxa de acerto atual do Innodb?

1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests).

Qual é a taxa de acerto do cache de chave

1 - (Key_reads/Key_read_requests)

Normalmente, tento obter a proporção o mais próximo possível de 100%.

Quão bem suas tabelas se encaixam no buffer pool

Você também pode ver como os dados da sua tabela se encaixam no seu buffer_pool consultando este link, que fornece uma maneira de mostrar "quantas páginas estão no buffer pool para determinada tabela (cnt), quantas delas estão sujas (sujas) , e qual é a porcentagem de ajuste de índice na memória (fit_pct)." Aplica-se apenas ao servidor Percona

http://www.mysqlperformanceblog.com/2010/12/09/how-well-does-your-table-fits-in-innodb-buffer-pool/