If you are using InnoDB tables, the size of your ibdata files will grow over time. So, if you issue DELETE
statement, your database size will reduce, but the ibdata file will remain the same (not reduce).
If you are not using innodb_file_per_table
option, the only way to reclaim the space is by dumping the database and restoring from the dumpfile.
However, if you are using innodb_file_per_table
, you can issue an
ALTER TABLE foo ENGINE=InnoDB;
on tables that grow too large to reclaim the disk space.
Your innodb_buffer_pool_size is enormous. You have it set at 20971520000
. That's 19.5135 GB. If you only have 5GB of InnoDB data and indexes, then you should only have about 8GB. Even this may be too high.
Here is what you should do. First run this query
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;
This will give you the RIBPS, Recommended InnoDB Buffer Pool Size, based on all InnoDB Data and Indexes, with an additional 60%.
For Example
mysql> SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
-> (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
-> FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
| 8 |
+-------+
1 row in set (4.31 sec)
mysql>
With this output, you would set the following in /etc/my.cnf
[mysqld]
innodb_buffer_pool_size=8G
Next, service mysql restart
After the restart, run MySQL for a week or two. Then, run this query:
SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;
This will give you how many actual GB of memory is in use by InnoDB
Data in the InnoDB Buffer Pool at this moment.
I have written about this before : What to set innodb_buffer_pool and why..?
You could just run this DataGB
query right now rather than reconfiguring, restarting and waiting a week.
This value DataGB
more closely resembles how big the InnoDB Buffer Pool should be + (percentage specified in innodb_change_buffer_max_size). I am sure this will be far less than the 20000M you have reserved right now. The savings in RAM can be used for tuning other things like
CAVEAT #1
This is very important to note: At times, InnoDB may require an additional 10% over the value for the innodb_buffer_pool_size. Here is what the MySQL Documentation says on this:
The larger you set this value, the less disk I/O is needed to access
data in tables. On a dedicated database server, you may set this to up
to 80% of the machine physical memory size. Be prepared to scale back
this value if these other issues occur:
Competition for physical memory might cause paging in the operating
system.
InnoDB reserves additional memory for buffers and control structures,
so that the total allocated space is approximately 10% greater than
the specified size.
The address space must be contiguous, which can be an issue on Windows
systems with DLLs that load at specific addresses.
The time to initialize the buffer pool is roughly proportional to its
size. On large installations, this initialization time may be
significant. For example, on a modern Linux x86_64 server,
initialization of a 10GB buffer pool takes approximately 6 seconds.
See Section 8.9.1, “The InnoDB Buffer Pool”.
CAVEAT #2
I See the following values in your my.cnf
| innodb_io_capacity | 200 |
| innodb_read_io_threads | 4 |
| innodb_thread_concurrency | 4 |
| innodb_write_io_threads | 4 |
These number will impede InnoDB from accessing multiple cores
Please set the following:
[mysqld]
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
I have written about this before in the DBA StackExchange
I just answered a question like this in Server Fault using a more concise formula:
SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
FROM
(
SELECT SUM(data_length+index_length)*1.1*growth RIBPS
FROM information_schema.tables AAA,
(SELECT 1.25 growth) BBB
WHERE ENGINE='InnoDB'
) AA
) A;
Best Answer
innodb_buffer_pool_size
No; wrong approach.
Simply set
innodb_buffer_pool_size
to about 70% of available RAM.If you make it too much larger than that, you risk swapping, which hurts performance terribly.
If you make it too much smaller than that, your queries run slower.
If you have a lot more data than that, you will simply hope that the "working set" is smaller, and the performance will still be good.
If you have a lot less data than that, you are wasting some RAM that you won't be using anyway.
Fragmentation
Bogus.
There are at least 3 ways in which InnoDB fragments. The available metrics measure only one of them.
Fragmentation has very little to do with performance. So, as long as you are not threatened with running out of disk space, don't defragment. Furthermore, promptly after defragmenting, the freed up space will be quickly gobbled up. The encouraging thing is that the used space will stop growing as fast after it gets to some steady state.
Catch 22: If you are about to run out of disk space, you probably can't run
OPTIMIZE
because it needs space to run!OPTIMIZE TABLE
Ignore their advice; never use it for InnoDB tables.
It copies the table over, thereby removing any "free" space because the copying compacts things better. But, I repeat, this only squeezes out some of the "free" space.
Well, OK, there are rare cases where
OPTIMIZE
is useful. However, MysqlTuner "cry's wolf" a lot on this item.innodb_buffer_pool_instances
Simple; don't worry.
Just set it to an integer that is innodb_buffer_pool_size/1G, but no less than 1 and no bigger than 16. No risk. But, also, virtually no gain. In a very busy system, it may speed things up by only a few percent. If you have a very busy system, you need to be looking at other ways to avoid falling off the cliff.
The application probably does not mention it because either (1) they did notice it when it was added (MySQL 5.5), or (2) they realized it did not matter much.