Why would a restored DB be smaller than the one backed up?
Don't have anything like expertise in mysql so I guess this is a newb question.
I've got a drupal site and the db is about 70mb. If I dump the DB, make a new empty one and then restore the dump file to it, it's about 40mb.
Seems to work fine but what would make it 30mb smaller?
Just moved to a new host and I don't remember seeing this phenomenon before. Could it be a mysql config. thing?
Greg K posted this at 03:38 — 19th July 2009.
He has: 2,145 posts
Joined: Nov 2003
My guess would be that that extra 30mb was data that was "deleted" but still in the files. When you backed up the database, it only gives you what is "active", not the deleted information.
IMO nothing to worry about, think of it like a spiral notebook. You have 70 pages in it. However 30 of them are old things you "deleted" and don't need. They are there for new data to be written to. This is more efficient than undoing the spiral, pulling out the old page and rebinding it.
When you backed it up, it only took the 40 pages of information to your copy machine to make your dump file. When you made a new notebook from the copy, there was only 40 pages.
There is a command to manually optimize the database files, but most systems are set to do it at set intervals for you.
-Greg
aharown07 posted this at 20:47 — 19th July 2009.
They have: 43 posts
Joined: Oct 2008
Thanks, Greg.
Sounds quite possible. I do have a module for optimizing the db and ran it on the original db, but it doesn't appear to have shrunk any. It's also supposed to write info to a log and there we nothing there so I'm thinking it didn't really fly.
Is there a mysql query I can key in? (I do have phpmyadmin)
Seems like I read somewhere also that there's a way to compare two mysql databases and view the difference? Maybe that would confirm your idea.
aharown07 posted this at 21:02 — 19th July 2009.
They have: 43 posts
Joined: Oct 2008
Just comared the two db's more closely in phpmyadmin. There's an "overhead" column that is significantly larger in the original... in addition to the sizes of the tables being smaller in total.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.