Tenho vários bancos de dados todos em INNODB, 50GB são BLOBcolunas, apenas 700MB não são BLOBdados. Ferramentas como mysqltuner me pedem para definir innodb_buffer_pool_size = 51GB.
O MySQL armazena o BLOB na RAM? Se sim, como posso configurar o MYSQL para que ele não use innodb_buffer_pool_size para armazenar BLOB?
A parte 1 da resposta à sua pergunta é não, você não pode dizer seletivamente ao MySQL o que não armazenar no buffer pool do InnoDB. É parte integrante de como o InnoDB manipula os dados da tabela.
A parte 2 é que você realmente não quer ou precisa, porque é inteligente o suficiente para descobrir o que deve permanecer lá se o pool não for grande o suficiente para reter todo o conjunto de dados.
A propósito, devo ter mencionado anteriormente ... não confie em scripts de ajuste.
Na melhor das hipóteses, eles podem fornecer "coisas interessantes" para investigar e ler, mas é absolutamente impossível "ajustar" adequadamente um servidor MySQL sem entender a natureza dos dados que estão naquele servidor ... e você está apenas provavelmente ajustará seu desempenho para baixo ou para cima.
O InnoDB não carrega nada no buffer pool que não seja de alguma forma útil. Deixe um servidor MySQL recém-reiniciado sozinho, sem executar nenhuma consulta, e o buffer pool ficará lá, alocado, mas quase vazio.
SELECTde uma tabela, e algumas páginas do espaço de tabela dessa tabela no disco serão carregadas no pool, mas geralmente não todas. Quais páginas dependem de quais linhas você recupera, quais índices são necessários para encontrá-las, se está fazendo leitura antecipada, etc.UPDATEuma tabela e as páginas com as linhas que você atualizar serão carregadas no pool, modificadas no pool e depois gravadas de volta no disco em segundo plano.INSERTeDELETEtrazer as páginas para o pool e, por fim, liberá-las de volta para o disco também.Mas o InnoDB acompanha o quão "útil" é o que está atualmente no pool de buffer, quão recentemente foi necessário, se alguma alteração já foi gravada no disco ou não e removerá as páginas para abrir espaço para outras páginas quando o pool for cheio. Muitos dos meus servidores de produção têm um pool de buffer InnoDB menor que o conjunto de dados do servidor e essencialmente 100% cheio 100% do tempo, mas não há impacto no desempenho quando uma grande quantidade de dados não foi acessada por alguns Tempo.
Se uma página no pool não foi alterada desde que foi carregada, ou se foi alterada, mas desde então as alterações foram gravadas de volta no disco e não foram necessárias por um tempo, esse pedaço do pool é prontamente reutilizável para outra coisa na próxima vez que o InnoDB quiser carregar algo de volta no pool a partir do disco ... então está "em uso", mas ainda "disponível".
A menos que você acesse com frequência todos os 50 GB desses blobs, a recomendação de tamanho do script de ajuste é exagerada. É claro que, em um mundo ideal, seu pool seria maior do que todas as suas tabelas e índices e você teria um banco de dados totalmente em memória que só precisava acessar o disco para liberar as alterações... mundo de dados reais, as chances são muito boas de que uma grande parte de seus dados não seja acessada com frequência e não há como um script simples antecipar essas condições.
O buffer pool do InnoDB armazena cópias de qualquer coisa que esteja armazenada nas páginas do banco de dados no disco. Isso inclui:
Não há como excluir um desses tipos de página. Todos eles ocupam o buffer pool de tempos em tempos, com base na demanda.
Então, estritamente falando, a única resposta para sua pergunta de como excluir os dados do InnoDB Blob do buffer pool é usar um mecanismo de armazenamento diferente.
Você também pode excluir as colunas Blob/Text/Varchar de suas consultas. Apenas não faça referência a essas colunas em sua consulta (evite usar
SELECT *). O InnoDB é inteligente o suficiente para pular o carregamento de páginas extras de dados Blob/Text/Varchar que ele não precisa.Mas você deve aceitar a recomendação de ferramentas como mysqltuner com cautela. Você não precisa necessariamente de tanto buffer pool quanto da quantidade total de dados. Na verdade, é mais comum que você tenha uma regra 80/20: 80% de suas consultas são atendidas por 20% de seus dados.
Sua milhagem pode variar -- os tipos de consultas que você executa determinam qual é o melhor compromisso no tamanho do buffer pool. Você pode fornecer uma alocação de melhor estimativa e, em seguida, monitorar a proporção de leituras de página lógica versus E/S de página física:
Ou a taxa de acesso à página:
Se você não gostar dessas proporções, aloque mais RAM para o buffer pool.