toic.org - Entries for the category Serviceshttps://toic.org/blog/categories/services/It's all about those pesky daemons...en-usZinniaFri, 08 Jan 2016 14:19:45 +0000Reclaiming InnoDB ibdata unused space. https://toic.org/blog/2012/reducing-innodb-ibdata-unused-space/<p><img align='left' src="/media/filer_public/9f/70/9f70cbfc-1a4f-4885-97e1-66196ea4e805/crystal_128_kcmpartitions.png" alt="ssh" width="120" height="120" style="margin-right:20px; margin-bottom:20px;" /></p> <p>As you well know InnoDB will store its data either in one big system tablespace called ibdata or you can store it in multiple tablespaces (<a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table">innodb_file_per_table</a>). In either case InnoDB by default will not reclaim unused space gained when deleting data. Using innodb_file_per_table will separate each InnoDB table in its own .ibd file and it can be easily reclaimed by doing optimize table, using system tablespace will get you stuck since there is no real supported method for reducing you ibdata file and reclaiming unused disk space.</p> <p>One could wonder why would anyone want to have its ibdata in system tablespace? Well there are some &quot;<a class="reference external" href="http://bugs.mysql.com/bug.php?id=51325">bugs</a>&quot; (in versions prior to 5.5) and performance issues that cause mysql lock-ups when using innodb_file_per_table and doing truncate table or drop table or even drop database queries with large buffer pools. In a nutshell MySQL will do a whole buffer pool lock and scan on each of the above mentioned commands effectively stoping the entire MySQL server until it scans over the entire buffer pool for references to those dropped/truncated tables.</p> <p>When given choice and when I do know the pattern of data input to my tablespace i will chose system tablespace.</p> <p>Recently I was a victim of my own system and uncontrolled input of data to my InnoDB table space which leaved me with almost 36Gb of garbage data over one night. Naturally i truncated the data, but it left me with 37Gb ibdata file.</p> <p>So here are the steps I've taken to reduce this system tablespace.</p> <p><strong>First of all backup!</strong></p> <p>I highly recommend <a class="reference external" href="http://www.percona.com/software/percona-xtrabackup">Xtrabackup</a> from Percona for this task, yes it will require additional 37Gb of space for backup but this is by far best point in time backup for MySQL.</p> <pre class="literal-block"> innobackupex $backupdir </pre> <p>And replace the backupdir with your preferred location</p> <p>Let's create a working environment, say you have a lots of space on /home</p> <pre class="literal-block"> mkdir /home/inno-reduce cd /home/inno-reduce </pre> <p>Ok, next stop dumping all the databases that have innodb tables inside, for this I've created a small bash script a while ago..</p> <pre class="code bash literal-block"> <span class="comment">#!/bin/bash </span><span class="name variable">CWD</span><span class="operator">=</span><span class="literal string backtick">`</span><span class="name builtin">pwd</span><span class="literal string backtick">`</span> mysql -e <span class="literal string double">&quot;SELECT distinct(table_schema) FROM information_schema.TABLES where ENGINE = 'InnoDB'&quot;</span> &gt; <span class="name variable">$CWD</span>/innodb-databases.list sed <span class="literal string double">&quot;/table_schema/d&quot;</span> <span class="name variable">$CWD</span>/innodb-databases.list &gt; <span class="name variable">$CWD</span>/tmp mv <span class="name variable">$CWD</span>/tmp <span class="name variable">$CWD</span>/innodb-databases.list <span class="keyword">for</span> database in <span class="literal string backtick">`</span>cat <span class="name variable">$CWD</span>/innodb-databases.list<span class="literal string backtick">`</span><span class="punctuation">;</span> <span class="keyword">do</span> <span class="name builtin">echo</span> <span class="literal string double">&quot;dumping data from </span><span class="name variable">$database</span><span class="literal string double"> to </span><span class="name variable">$CWD</span><span class="literal string double">/</span><span class="name variable">$database</span><span class="literal string double">.sql&quot;</span> /usr/bin/mysqldump --triggers --routines <span class="name variable">$database</span> &gt; <span class="name variable">$CWD</span>/<span class="name variable">$database</span>.sql<span class="punctuation">;</span> <span class="keyword">done</span> </pre> <p>Now would be a good time to cut off any application using MySQL (for example turn off Apache, or drop the packets in firewall). Just copy and paste this code in <strong>step1-dump.sh</strong> file, make it executable and run it. It will produce a .sql dump files in your current directory (hopefully we are still on /home/inno-reduce)</p> <p>Step2 script will iterate trough that dumped data files and drop all the databases that have innodb tables inside.</p> <pre class="code bash literal-block"> <span class="comment">#!/bin/bash </span><span class="name variable">CWD</span><span class="operator">=</span><span class="literal string backtick">`</span><span class="name builtin">pwd</span><span class="literal string backtick">`</span> <span class="keyword">for</span> database in <span class="literal string backtick">`</span>ls <span class="name variable">$CWD</span>/*.sql<span class="punctuation">|</span>awk -F<span class="literal string single">'/'</span> <span class="literal string single">'{print $NF}'</span><span class="punctuation">|</span>cut -d. -f1<span class="literal string backtick">`</span><span class="punctuation">;</span> <span class="keyword">do</span> <span class="name builtin">echo</span> <span class="literal string double">&quot;droping database </span><span class="name variable">$database</span><span class="literal string double">&quot;</span> mysqladmin drop <span class="name variable">$database</span> <span class="keyword">done</span> </pre> <p>Again just copy and paste this code in <strong>step2-drop.sh</strong>, make it executable and run it. At this point There shouldn't be any InnoDB tables in MySQL server.</p> <p>Now, it is a good time to shutdown your MySQL service and rm ibdata file. On typical CentOS system this would be done with</p> <pre class="literal-block"> service mysqld stop rm -f /var/lib/mysql/ibdata1 </pre> <p>If you are not running CentOS and default MySQL install or if you have ibdata file on some other path… please adjust the above. Starting MySQL service at this point would &quot;regenerate&quot; ibdata file with its smallest size increment as defined in my.cnf lets do so:</p> <pre class="literal-block"> service mysqld start </pre> <p>And now for the third script</p> <pre class="code bash literal-block"> <span class="comment">#!/bin/bash </span><span class="name variable">CWD</span><span class="operator">=</span><span class="literal string backtick">`</span><span class="name builtin">pwd</span><span class="literal string backtick">`</span> <span class="keyword">for</span> database in <span class="literal string backtick">`</span>ls <span class="name variable">$CWD</span>/*.sql<span class="punctuation">|</span>awk -F<span class="literal string single">'/'</span> <span class="literal string single">'{print $NF}'</span><span class="punctuation">|</span>cut -d. -f1<span class="literal string backtick">`</span><span class="punctuation">;</span> <span class="keyword">do</span> mysqladmin create <span class="name variable">$database</span> mysql <span class="name variable">$database</span> &lt; <span class="name variable">$CWD</span>/<span class="name variable">$database</span>.sql <span class="keyword">done</span> </pre> <p>As before copy and paste this script into <strong>step3-import.sh</strong> (remember to place this file at the same directory as other two, and dumps), make it executable and run it.</p> <p>Voila! your data is back in, ibdata reduced and MySQL up&amp;running.</p> <p>Remember to restore any access to your MySQL service (starting Apache, clearing firewall drops, etc..)</p> branko@toic.org (branko)Tue, 04 Dec 2012 23:05:56 +0000https://toic.org/blog/2012/reducing-innodb-ibdata-unused-space/Services