October 2018
M T W T F S S
« Sep    
1234567
891011121314
15161718192021
22232425262728
293031  

Categories

WordPress Quotes

The future belongs to those who believe in the beauty of their dreams.
Eleanor Roosevelt

Recent Comments

October 2018
M T W T F S S
« Sep    
1234567
891011121314
15161718192021
22232425262728
293031  

Short Cuts

2012 SERVER (64)
2016 windows (9)
AIX (13)
Amazon (34)
Ansibile (18)
Apache (133)
Asterisk (2)
cassandra (2)
Centos (209)
Centos RHEL 7 (258)
chef (3)
cloud (2)
cluster (3)
Coherence (1)
DB2 (5)
DISK (25)
DNS (9)
Docker (28)
Eassy (11)
ELKS (1)
EXCHANGE (3)
Fedora (6)
ftp (5)
GIT (3)
GOD (2)
Grub (1)
Hacking (10)
Hadoop (6)
horoscope (23)
Hyper-V (10)
IIS (15)
IPTABLES (15)
JAVA (7)
JBOSS (32)
jenkins (1)
Kubernetes (2)
Ldap (5)
Linux (189)
Linux Commands (167)
Load balancer (5)
mariadb (14)
Mongodb (4)
MQ Server (22)
MYSQL (84)
Nagios (5)
NaturalOil (13)
Nginx (30)
Ngix (1)
openldap (1)
Openstack (6)
Oracle (34)
Perl (3)
Postfix (19)
Postgresql (1)
PowerShell (2)
Python (3)
qmail (36)
Redis (12)
RHCE (28)
SCALEIO (1)
Security on Centos (29)
SFTP (1)
Shell (64)
Solaris (58)
Sql Server 2012 (4)
squid (3)
SSH (10)
SSL (14)
Storage (1)
swap (3)
TIPS on Linux (28)
tomcat (59)
Uncategorized (29)
Veritas (2)
vfabric (1)
VMware (28)
Weblogic (38)
Websphere (71)
Windows (19)
Windows Software (2)
wordpress (1)
ZIMBRA (17)

WP Cumulus Flash tag cloud by Roy Tanck requires Flash Player 9 or better.

Who's Online

27 visitors online now
5 guests, 22 bots, 0 members

Hit Counter provided by dental implants orange county

MySQL: Calculate the free space in IBD files

If you use MySQL with InnoDB, chances are you’ve seen growing IBD data files. Those are the files that actually hold your data within MySQL. By default, they only grow — they don’t shrink. So how do you know if you still have free space left in your IBD files?

There’s a query you can use to determine that:

SELECT round((data_length+index_length)/1024/1024,2)
FROM information_schema.tables
WHERE
  table_schema='zabbix'
  AND table_name='history_text';

The above will check a database called zabbix for a table called history_text. The result will be the size that MySQL has “in use” in that file. If that returns 5.000 as a value, you have 5GB of data in there.

In my example, it showed the data size to be 16GB. But the actual IBD file was over 50GB large.

$ ls -alh history_text.ibd
-rw-r----- 1 mysql mysql 52G Sep 10 15:26 history_text.ibd

In this example I had 36GB of wasted space on the disk (52GB according to the OS, 16GB in use by MySQL). If you run MySQL with innodb_file_per_table=ON, you can individually shrink the IBD files. One way, is to run an OPTIMIZE query on that table.

Note: this can be a blocking operation, depending on your MySQL version. WRITE and READ I/O can be blocked to the table for the duration of the OPTIMIZE query.

MariaDB [zabbix]> OPTIMIZE TABLE history_text;
Stage: 1 of 1 'altering table'   93.7% of stage done
Stage: 1 of 1 'altering table'    100% of stage done

+---------------------+----------+----------+-------------------------------------------------------------------+
| Table               | Op       | Msg_type | Msg_text                                                          |
+---------------------+----------+----------+-------------------------------------------------------------------+
| zabbix.history_text | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| zabbix.history_text | optimize | status   | OK                                                                |
+---------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (55 min 37.37 sec)

The result is quite a big file size savings:

$ ls -alh history_text.ibd
-rw-rw---- 1 mysql mysql 11G Sep 10 16:27 history_text.ibd

The file that was previously 52GB in size, is now just 11GB.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

  

  

  

Blue Captcha Image
Refresh

*

Protected by WP Anti Spam