InnoDB Table Compression This is often especially the case when running in a multi-user environment with fast, multi-core CPUs. When a page of a compressed table is in memory, MySQL often uses additional memory, typically 16KB, in the buffer pool for an uncompressed copy of the page. The adaptive LRU algorithm attempts to balance the use of memory between compressed and uncompressed pages to take into account whether the workload is running in an I/O-bound or CPU-bound manner. Still, a configuration with more memory dedicated to the buffer pool tends to run better when using compressed tables than a configuration where memory is highly constrained. The optimal setting of the compressed page size depends on the type and distribution of data that the table and its indexes contain. The compressed page size should always be bigger than the maximum record size, or operations may fail as noted in Compression of B-Tree Pages. Setting the compressed page size too large wastes some space, but the pages do not have to be compressed as often. If the compressed page size is set too small, inserts or updates may require time- consuming recompression, and the B-tree nodes may have to be split more frequently, leading to bigger data files and less efficient indexing. Typically, you set the compressed page size to 8K or 4K bytes. Given that the maximum row size for an InnoDB table is around 8K,  KEY_BLOCK_SIZE=8  is usually a safe choice. Overall application performance, CPU and I/O utilization and the size of disk files are good indicators of how effective compression is for your application. This section builds on the performance tuning advice from Section 15.9.1.3, “Tuning Compression for InnoDB Tables” , and shows how to find problems that might not turn up during initial testing. To dig deeper into performance considerations for compressed tables, you can monitor compression performance at runtime using the Information Schema tables described in Example 15.1, “Using the Compression Information Schema Tables” . These tables reflect the internal use of memory and the rates of compression used overall. The  INNODB_CMP  table reports information about compression activity for each compressed page size ( KEY_BLOCK_SIZE ) in use. The information in these tables is system-wide: it summarizes the compression statistics across all compressed tables in your database. You can use this data to help decide whether or not to compress a table by examining these tables when no other compressed tables are being accessed. It involves relatively low overhead on the server, so you might query it periodically on a production server to check the overall efficiency of the compression feature. The  INNODB_CMP_PER_INDEX  table reports information about compression activity for individual tables and indexes. This information is more targeted and more useful for evaluating compression efficiency and diagnosing performance issues one table or index at a time. (Because that each  InnoDB  table is represented as a clustered index, MySQL does not make a big distinction between tables and indexes in this context.) The  INNODB_CMP_PER_INDEX  table does involve substantial overhead, so it is more suitable for development servers, where you can compare the effects of different workloads, data, and compression settings in isolation. To guard against imposing this monitoring overhead by accident, you must enable the  innodb_cmp_per_index_enabled  configuration option before you can query the INNODB_CMP_PER_INDEX  table. The key statistics to consider are the number of, and amount of time spent performing, compression and uncompression operations. Since MySQL splits B-tree nodes when they are too full to contain the compressed data following a modification, compare the number of “successful”  compression operations with the number of such operations overall. Based on the information in the  INNODB_CMP  and 3153 Choosing the Compressed Page Size 15.9.1.4 Monitoring InnoDB Table Compression at Runtime