Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Our DBA is on holiday, and since I know 'a bit' of MySQL, they've asked me to fix this. The data slice is at 99%, and I need to know what to get rid of.

[root@servername]# find . -type f -size +1000000k -exec ls -lh {} \; | awk '{ print $9 ": " $5 }' ./mysql/stuffInDatabase.ibd: 6.8G

Thing is, I've never done this before, and so don't know where to start. Can someone point me in the right direction? Which database do I need to use, the production one or the information_schema? Which query do I use? Do I need to dump and then reimport?

Thanks in advance.

share|improve this question
4  
I suggest you scale up the disks or empty some space (trash, temp folders) before messing with the database. Especially if your DBA has not left a procedure to deal with such a situation. –  ypercube Sep 5 '13 at 14:36
    
Problem solved. I got them to provision more storage. Thanks all. :) –  user27880 Sep 10 '13 at 14:43

1 Answer 1

If you are not experienced at both mysql and linux sysadmin I would suggest backing off from this task: if this is a production system there could be much trouble if something goes wrong and data is lost! If you do proceed, take care to make sure you take, and test, fresh backups before doing anything.

I'm assuming the tables are of InnoDB type as that is the default these days. IIRC InnoDB files can not be shrink by the standard tools: when data is deleted the space is just left unallocated internally (not released by to the filesystem) to be used again as more data comes in. As your file is not ibdata I assume you have the "one file per innodb table` option turned on, in which case to free unallocated space within the file, dump that table to file, drop the table, recreate the table and then import the dump file back into it. If there is little free space in the file to start with (i.e. there has been very little delete activity recently) this won't help much though: the resulting file will be more-or-less the same size after you have finished. Also, as the database will be in an inconsistent state during the process, you should block access to it (aside from the admin accounts you need to perform the dump/drop/rebuild/restore of course) for the duration.

If your server uses LVM for space/partition management then you may have free space in the appropriate volume group and can just expend the volume to allow extra space. If you have room on other file-systems you could move the file to one of those and link to it from its currently location (IIRC mysql is happy to follow softlinks to find data-files) like so:

  • stop mysql
  • move the files to a volume with more space (though not /tmp or anything with tmp in the name - these may be wiped on start-up after reboot or power-fail)
  • create a sym-link with the same name in the old location pointing to the new on
  • restart mysql

(obviously all your databases on this machine will be inaccessible during this process)

To see what free space you have where on current filesystems run df -h. If you are using LVM run the commands lvs, vgs and pvs to see what space you have where outside that currently allocated to filesystems (if you are not using LVM these three commands will either return nothing or not be found). I may be straying a tad far off-topic here IMO (out of DBA-land and into ServerFault territory) so I'll not go into more detail.

I'll say again though: if you are not a confident DBA+sysadmin and this is a production system I would stay away. Failing that, at least try practice on another system first (do you have a development environment setup similarly to the production one?) so you can verify what you are about to do before doing it on real data.

If you do do anything make sure you fully document your actions (all commands entered and why, all output received) so your usual DBA/sysadmin can review things upon return and make any further adjustments that may be needed to keep things running well long-term.

share|improve this answer
    
Caveat: it's not safe to symlink "to files," only "to directories," with InnoDB. If you move something to a new storage location, MySQL will happily follow symlinks to directories (e.g., the entire datadir or a single large schema directory) but symlinking to a single .ibd file isn't recommended. –  Michael - sqlbot Sep 6 '13 at 12:16
    
I hear you. :-( Unfortunately, the company is going to lose money if I don't do this. So I'm pulling an all-nighter tonight. No, the one file per table is not switched on, but there are 'report.ibd' files in there. How can I find which tables are the largest, so that I can dump them, drop them and reimport them? –  user27880 Sep 6 '13 at 14:24
    
I'm not sure how to interrogate mySQL for what space each table has allocated and is using, sorry, (MSSQL has been my day-job for many years, I've had little exposure to mySQL recently) but by my understanding is if the file-per-table option is off then there should just the one ibdata file containing all the InnoDB tables, if this is the case then you need to drop all InnoDB tables, truncate the appropriate files, then recreate the tables and re-import the data. This may be a waste of time though if you have not deleted a fair amount of data recently, as there may be little space to save. –  David Spillett Sep 6 '13 at 14:55
    
You might be better off seeing if there is anything else on that volume that you can move to another filesystem (or remove entirely, if you find old "temporary" data cluttering up the place). –  David Spillett Sep 6 '13 at 14:56
    
Right, tomorrow, I go in early and I dump the entire database. I've done that many times, but don't know if the reports will be dumped, too. And I then drop the database and then recreate it before reimporting the dump. Have I missed anything? Are users with GRANT, SELECT etc. privileges destroyed if I drop the db? If so, how do I back them up? –  user27880 Sep 8 '13 at 9:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.