Configurando a compactação do MySQL INNODB KEY_BLOCK_SIZE

Question

Recentemente, comecei a usar o formato de tabela Barracuda InnoDB / MySQL, que permite a compactação.

Eu compactei uma das minhas tabelas executando:

alter table pricing row_format=compressed, key_block_size=8;

Depois que eu corri isso eu vi as estatísticas de compressão (eu tinha limpado-os logo antes do ALTER TABLE):

mysql> select * from INFORMATION_SCHEMA.INNODB_CMP;
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| page_size | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
|      1024 |            0 |               0 |             0 |              0 |               0 |
|      2048 |            0 |               0 |             0 |              0 |               0 |
|      4096 |            0 |               0 |             0 |              0 |               0 |
|      8192 |      7029231 |         6352315 |          1437 |         339708 |              41 |
|     16384 |            0 |               0 |             0 |              0 |               0 |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
5 rows in set (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.INNODB_CMPMEM;
+-----------+------------+------------+----------------+-----------------+
| page_size | pages_used | pages_free | relocation_ops | relocation_time |
+-----------+------------+------------+----------------+-----------------+
|       128 |      11214 |          0 |        8434571 |               2 |
|       256 |          0 |         37 |              0 |               0 |
|       512 |          0 |         34 |              0 |               0 |
|      1024 |          0 |          2 |              0 |               0 |
|      2048 |          0 |        141 |              0 |               0 |
|      4096 |          0 |        298 |          96657 |               0 |
|      8192 |      15133 |          0 |        4121178 |               5 |
|     16384 |          0 |          0 |              0 |               0 |
+-----------+------------+------------+----------------+-----------------+
8 rows in set (0.00 sec)

Se eu dividir compress_ops_ok por compress_ops, isso é 6352315/7029231 = 90,4%. Meu entendimento é que basicamente 90,4% das páginas são comprimidas de 16 KB para 8 KB, e o restante não conseguiu compactar em 2x.

Eu li que essas páginas que falham na compactação prejudicaram o desempenho, mas os mais de 90% que foram compactados com sucesso devem melhorar bastante o desempenho (diminuindo as operações de E / S). Existe uma regra geral de qual porcentagem de páginas deve ser compactada para que isso seja considerado OK? Minha outra opção provavelmente seria apenas desativar a compactação.

Meu objetivo líquido é reduzir o número de operações de E / S e não desejo ativar a compactação se isso for contraproducente.

    

Answer 1

Mesmo depois de executar a compactação, você ainda pode não obter o desempenho que está procurando. Por quê?

O InnoDB tem o Buffer Pool para carregar páginas de dados e páginas de índice lidas para atender consultas. Ao ler uma tabela e seus índices pela primeira vez, a página compactada deve ser descompactada. Na verdade, você pode ter o dobro de dados no buffer pool como resultado disso.

Observe como esse é o caso da Documentação do MySQL

Compression and the InnoDB Buffer Pool

In a compressed InnoDB table, every compressed page (whether 1K, 2K, 4K or 8K) corresponds to an uncompressed page of 16K bytes. To access the data in a page, InnoDB reads the compressed page from disk if it is not already in the buffer pool, then uncompresses the page to its original 16K byte form. This section describes how InnoDB manages the buffer pool with respect to pages of compressed tables.

To minimize I/O and to reduce the need to uncompress a page, at times the buffer pool contains both the compressed and uncompressed form of a database page. To make room for other required database pages, InnoDB may “evict” from the buffer pool an uncompressed page, while leaving the compressed page in memory. Or, if a page has not been accessed in a while, the compressed form of the page may be written to disk, to free space for other data. Thus, at any given time, the buffer pool may contain both the compressed and uncompressed forms of the page, or only the compressed form of the page, or neither.

InnoDB keeps track of which pages to keep in memory and which to evict using a least-recently-used (LRU) list, so that “hot” or frequently accessed data tends to stay in memory. When compressed tables are accessed, InnoDB uses an adaptive LRU algorithm to achieve an appropriate balance of compressed and uncompressed pages in memory. This adaptive algorithm is sensitive to whether the system is running in an I/O-bound or CPU-bound manner. The goal is to avoid spending too much processing time uncompressing pages when the CPU is busy, and to avoid doing excess I/O when the CPU has spare cycles that can be used for uncompressing compressed pages (that may already be in memory). When the system is I/O-bound, the algorithm prefers to evict the uncompressed copy of a page rather than both copies, to make more room for other disk pages to become memory resident. When the system is CPU-bound, InnoDB prefers to evict both the compressed and uncompressed page, so that more memory can be used for “hot” pages and reducing the need to uncompress data in memory only in compressed form.

Se esta duplicação de conteúdo de dados está acontecendo no Buffer Pool, você precisa aumentar innodb_buffer_pool_size por um pequeno fator linear da nova taxa de compactação. Aqui está como:

CENÁRIO

• Você tem um servidor de banco de dados com um pool de buffers 8G
• Você executou a compactação com key_block_size=8
  • 8 é 50.00% de 16
  • 50.00% de 8G é 4G
  • aumentar innodb_buffer_pool_size para 12G ( 8G + 4G )
• Você executou a compactação com key_block_size=4
  • 4 é 25.00% de 16
  • 25.00% de 8G é 2G
  • aumentar innodb_buffer_pool_size para 10G ( 8G + 2G )
• Você executou a compactação com key_block_size=2
  • 2 é 12.50% de 16
  • 12.50% de 8G é 1G
  • aumentar innodb_buffer_pool_size para 9G ( 8G + 1G )
• Você executou a compactação com key_block_size=1
  • 1 é 06.25% de 16
  • 06.25% de 8G é 0.5G ( 512M )
  • aumentar innodb_buffer_pool_size para 8704M ( 8G ( 8192M ) + 512M )

MORAL DA HISTÓRIA : O InnoDB Buffer Pool precisa de mais espaço para respirar ao manipular dados compactados e páginas de índice.

    

Answer 2

Esses dados foram coletados de um ALTER TABLE, que é uma instrução que você não usa com frequência e que reescreve a tabela inteira. O que importa é sua carga de trabalho diária, todos os INSERTOS e ATUALIZAÇÕES que seu aplicativo faz no ambiente de produção. De acordo com o manual do MySQL:

"Você pode desativar a compactação para tabelas que fazem com que o número de" falhas de compactação "em seu aplicativo seja superior a 1% ou 2% do total. Essa proporção de falhas pode ser aceitável durante uma operação temporária, como uma carga de dados). "