[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


innoDB compression

Posted on 2009-12-28
Medium Priority
Last Modified: 2012-05-08

Does anybody know if there is a way to get some innoDB compression statistics? I have enabled it, but I'm not actually sure if it's working. My databases are taking more space than I imaged.
Question by:zattz
LVL 14

Accepted Solution

profya earned 2000 total points
ID: 26136449
"The current version of the InnoDB Plugin provides only a limited means to monitor the performance of compression at runtime. Overall application performance, CPU and i/o utilization and the size of disk files are the best indicators of how effective compression is for your application. "

" The InnoDB Plugin does include some Information Schema tables (see Example 6.1, Using the Compression Information Schema Tables) that reflect the internal use of memory and the rates of compression used overall. The INNODB_CMP tables report information about compression activity for each compressed page size (KEY_BLOCK_SIZE) in use. The information in these tables is system-wide, and includes summary data 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.

The key statistics to consider are the number of, and amount of time spent performing, compression and uncompression operations. Since InnoDB must split B-tree nodes when they are too full to contain the compressed data following a modification, you should also compare the number of successful compression operations with the number of such operations overall. Based on the information in the INNODB_CMP tables and overall application performance and hardware resource utilization, you may decide to make changes in your hardware configuration, adjust the size of the InnoDB buffer pool, choose a different page size, or select a different set of tables to compress.

If the amount of CPU time required for compressing and uncompressing is high, changing to faster CPUs, or those with more cores, can help improve performance with the same data, application workload and set of compressed tables. You may also benefit by increasing the size of the InnoDB buffer pool, so that more uncompressed pages can stay in memory, reducing the need to uncompress pages which exist in memory only in compressed form. "

3.4.2. Monitoring Compression at Runtime: http://www.innodb.com/doc/innodb_plugin-1.0/innodb-compression.html

Based on this information, no direct stats you can use to get how compressions performs, however, using the key performance indicators mentioned in the above lines along with querying the system tables specified, you can figure-out how your compressions really works.

I hope this helps.

Author Closing Comment

ID: 31670445
many thanks :)

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month19 days, 15 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question