LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 10-02-2009, 07:46 AM   #1
the182guy
Member
 
Registered: Jan 2009
Posts: 40

Rep: Reputation: 15
Deleted 25 million records from database, had no effect on disk space


Hi all,

I have a dedicated CentOS server running MySQL. The disk space was filling up due to a large database. I checked the disk space using this command:

df -h

I also ran a command to find all files on the server that are greater than 20mb in size, in order to find the source of the disk usage. This confirmed it was a table in the database using all the disk space, the command I used is:

find / -type f -size +20000k -exec ls -lh {} \; | awk '{ print $NF ": " $5 }'

I then proceeded to delete 25 million records from this table in a MySQL database stored on the server, I was expecting a lot of disk space to be free after doing that, but when I run the command again it shows exactly the same disk usage as before.

I am guessing I need to restart either MySQL, Apache or the entire server for the disk space changes to be shown using "df -h". Is this true?

If so, should I expect it to take some time to restart MySQL because of cleaning up the database files?

I can't restart anything yet due to causing downtime for the website that is hosted on the server, but I will later when the traffic is low.

Thanks in advance
 
Old 10-02-2009, 07:54 AM   #2
Lordandmaker
Member
 
Registered: Sep 2005
Location: London, UK
Distribution: Debian
Posts: 258

Rep: Reputation: 38
The rows are deleted, but the space is reserved for subsequent INSERT statements. To make the file smaller, look at MySQL's OPTIMISE-TABLE function.

The table is locked while it's being optimised, so you'll likely end up with some downtime.
 
Old 10-02-2009, 08:03 AM   #3
Robert Carnegie
LQ Newbie
 
Registered: Aug 2009
Posts: 19

Rep: Reputation: 0
My expertise is with Microsoft SQL Server, not MySQL, but professional database products generally don't give back disk space that they aren't using any more, even after rebooting, unless you specifically tell them to. The reasoning is that you'll probably want to store more data later, and releasing and reclaiming disk space will lead to fragmentation of the database, as it uses whatever disk space was available at the time it was needed. So instead, the space is held onto.

I found one apparent description of how to make MySQL release disk space but it sounds rather radical. Look at
http://forums.mysql.com/read.php?35,...886#msg-121886
and see whether it makes sense to you. If not, I suppose you should keep looking for more. But I do expect it doesn't just happen, you need to force it.

In Microsoft SQL Server, each "database" (or "catalog") has its own data files (with many tables in one file), so you can design your application to release disk space just by putting data in a separately created database and then dropping the database after use. The free space on the disk probably still contains the data, but if that's a problem (for confidentiality) then it's a separate one. MS also has separate commands to reorganise and release disk space in each database's files.
 
Old 10-02-2009, 08:23 AM   #4
the182guy
Member
 
Registered: Jan 2009
Posts: 40

Original Poster
Rep: Reputation: 15
Thanks for the responses....


What about if I drop the table in question? Then I could just recreate it. Would that free up the space?
 
Old 10-02-2009, 10:54 AM   #5
fpmurphy
Member
 
Registered: Jan 2009
Location: /dev/ph
Distribution: Fedora, Ubuntu, Redhat, Centos
Posts: 299

Rep: Reputation: 62
That is often the fastest way.
 
Old 10-03-2009, 10:35 AM   #6
the182guy
Member
 
Registered: Jan 2009
Posts: 40

Original Poster
Rep: Reputation: 15
Thanks all, I dropped the table which freed up the space
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
How to read next five records from the database table? maradnus Programming 7 12-08-2008 08:57 AM
How to compare records in two tables in seperate My Sql database using shell script sumitarun Programming 5 04-14-2005 09:45 AM
3Gb of disk space lost! Disk space problem or mother board conflicts with HDD Mistreated Linux - Hardware 4 12-06-2004 03:58 PM
what files can be safely deleted to save disk space? rohan208 Linux - General 9 05-27-2004 08:06 PM
Import records to MySQL Database from a shell script chrisk5527 Linux - General 12 03-24-2004 09:49 PM


All times are GMT -5. The time now is 01:00 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration