Solved

MySQL ibdata1 file is pretty big: recommend stackexchange post?

Posted on 2014-11-15
8
397 Views
Last Modified: 2014-11-18
Hi,

I have an earlier related posting but different. The /var/lib/mysql/ibdata1 file is pretty big:
781056  /var/lib/mysql/ibdata1

I read this posting to on how to possibly resolve it:
http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql

Is this recommended? I'd like to avoid deleting any databases though.

Father God bless<><

-------
I ran this to see my large files.

du -a /var | sort -n -r | head -n 10

3048880 /var
1396040 /var/log
1381472 /var/lib
1140736 /var/log/btmp
928720  /var/lib/mysql
781056  /var/lib/mysql/ibdata1
210048  /var/log/chkservd.log
202580  /var/cpanel
118796  /var/cpanel/perl
118784  /var/cpanel/perl/easy
root@ip-184-168-116-73 [/var/lib/mysql]# cd /var/lib/mysql
root@ip-184-168-116-73 [/var/lib/mysql]# ls -al
total 901172
drwxr-x--x 15 mysql mysql      4096 Nov 15 17:43 ./
drwxr-xr-x 33 root  root       4096 Jul 30 15:43 ../
-rw-r--r--  1 mysql mysql      3142 Jul 30 14:39 RPM_UPGRADE_HISTORY
-rw-r--r--  1 mysql mysql       524 Jul 30 14:39 RPM_UPGRADE_MARKER-LAST
-rw-rw----  1 mysql mysql        56 Jul 30 05:29 auto.cnf
drwx------  2 mysql mysql      4096 Oct 27  2012 cphulkd/
drwx------  2 mysql mysql      4096 Nov 13 08:27 emailmar_iem/
drwx------  2 mysql mysql      4096 Aug 27  2011 emailmar_templates/
drwx------  2 mysql mysql      4096 Nov  1 05:32 eximstats/
drwx------  2 mysql mysql     12288 Mar  6  2014 horde/
-rw-rw----  1 mysql mysql  50331648 Nov 15 17:49 ib_logfile0
-rw-rw----  1 mysql mysql  50331648 Nov 13 12:21 ib_logfile1
-rw-rw----  1 mysql mysql 799014912 Nov 15 17:49 ibdata1
-rw-rw----  1 mysql mysql    499659 Jul 30 16:04 ip-184-168-116-73-slow.log
-rw-rw----  1 mysql mysql    895837 Nov 15 17:49 ip-184-168-116-73.ip.secureserver.net.err
-rw-rw----  1 mysql mysql         5 Nov 15 17:38 ip-184-168-116-73.ip.secureserver.net.pid
drwx------  2 mysql mysql      4096 Jul 13  2011 leechprotect/
drwx------  2 mysql mysql      4096 Apr  5  2012 logaholicDB_ip_184_168_116_73/
drwx------  2 mysql mysql      4096 Jul 31 01:44 modsec/
drwx--x--x  2 mysql mysql      4096 Jul 30 06:15 mysql/
-rw-rw----  1 mysql mysql  16436304 Nov 15 15:00 mysql-bin.000049
-rw-rw----  1 mysql mysql   4079207 Nov 15 17:38 mysql-bin.000050
-rw-rw----  1 mysql mysql    178659 Nov 15 17:49 mysql-bin.000051
-rw-rw----  1 mysql mysql        57 Nov 15 17:42 mysql-bin.index
srwxrwxrwx  1 mysql mysql         0 Nov 15 17:38 mysql.sock=
-rw-r--r--  1 mysql mysql         6 Jul 30 06:15 mysql_upgrade_info
drwx------  2 mysql mysql      4096 Jul 14 18:21 myultrat_questionnaire/
-rw-------  1 mysql root        953 Jul 30 15:42 nohup.out
drwx------  2 mysql mysql      4096 Jul 30 06:15 performance_schema/
drwx------  2 mysql mysql      4096 Aug  5 02:45 roundcube/
drwx------  2 mysql mysql      4096 Jul 31 02:37 whmxfer/
0
Comment
Question by:Victor Kimura
8 Comments
 
LVL 24

Accepted Solution

by:
mankowitz earned 125 total points
Comment Utility
This is a really big and really old problem. It has been around for years. The recommended solution is (unfortunately) still the best. Check this out: http://bugs.mysql.com/bug.php?id=1341
0
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 250 total points
Comment Utility
'Big' is relative.  Do you have 90% data in your 781MB or 10%?  If it is 90% you will waste a lot of time trying to shrink it.
0
 

Author Comment

by:Victor Kimura
Comment Utility
How do I find out how much space is in that 781MB file? Is there a MySQL command that I can use. All the databases are in that file though so I would assume (I don't know if it's safe to assume though) that the 781MB file is composed mostly of my entire databases aggregated together. But is there some command line that I enter to quickly find out?
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
I don't know yet.  There should be something but I haven't found it yet.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 250 total points
Comment Utility
On this page http://dev.mysql.com/doc/refman/5.6/en/innodb-file-space.html , it says you can get the amount of free space in a single table but not in the database or the server file 'ibdata'.
0
 

Author Comment

by:Victor Kimura
Comment Utility
Hmmm...I guess Danny Beckett's solution and Max Webster's reply is probably a good option. Max says the config option in the my.cnf file:

If you use the configuration option innodb_file_per_table, you create multiple tablespaces. That is, MySQL creates separate files for each table instead of one shared file. These separate files a stored in the directory of the database, and they are deleted when you delete this database. This should remove the need to shrink/purge ibdata files in your case

I guess this has to be done manually every so often then?
0
 
LVL 19

Assisted Solution

by:jools
jools earned 125 total points
Comment Utility
We used the following to get an idea of db size, I've no idea where it originally came from but we've been using it for a few months now;


SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"  FROM information_schema.TABLES GROUP BY table_schema;

Open in new window


Just for info and all that
0
 

Author Closing Comment

by:Victor Kimura
Comment Utility
Ok, thank you, fellas. You were helpful. =)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now