MySQL Database's

[stuart]

OK, I have asked the team, and we are all at a complete blank why this would happen

This site run's on a MySQL database, which, up until about a week ago was around 455mb in size. I did a back up on Saturday, and this was almost half that size

Now, the site runs perfect, we appear to have not lost anything, so how the hell can the database drop in size

Any experts out there?

Stuart

[Captain Answer]

I don't think you could afford my consultancy fee

[stuart]

Thanks for the sarcastic answer.... that's that out of the way, Now can the sensible members offer advise please?

[Captain Answer]

Thanks for the sarcastic answer....

Anytime big boy, anytime

Has the size of the database grown steadily say as the forum has aged? Has there been any change to the schema? In theory it should only reduce in size if its physical size changes (are ALL old threads still showing in the DB?) Has there been any change to the architecture of the storage media?

[Sam]

I don't think you could afford my consultancy fee

One thing Stu's post doesn't mention is that he has recently got rid of one of the site's colour schemes.

My layman's view was that each scheme had it's own copy of the data - but I find that hard to believe.

[hagis]

OK, I have asked the team, and we are all at a complete blank why this would happen

This site run's on a MySQL database, which, up until about a week ago was around 455mb in size. I did a back up on Saturday, and this was almost half that size

Now, the site runs perfect, we appear to have not lost anything, so how the hell can the database drop in size

Any experts out there?

Stuart

Disclaimer: most of my database expertise is with SQL Server nowadays and it's been a few years since I've used MySQL and it's probably changed since then.

If MySQL works like Access with regards to storing data in files then any data removed from the database is marked as deleted rather than being physically deleted. It will be only when you run a compact or restore when the database is off-line that MySQL removes the deleted data, freeing up the space.

[hagis]

I've just remembered I have a MySQL book and I've looked into this a bit more:

http://dev.mysql.com/doc/refman/4.1/...intenance.html

The isamchk command will allow the database to compact the ISM & ISD files that contain the keys and the data from the tables. Is you run this as part of a regular maintenance schedule you'll probably find the database runs a bit quicker.

[stuart]

Good point Sam I have removed one of the three schemes we had, and if you divided the original figure by 3 then multiplied by 2 it does work out around the new figure.. so maybe it does store copies for each scheme, although vB say not.

On Saturday I did optimize the database prior to doing the back up, so maybe, as Hagis has suggested maybe it removed the redundant files, but we then had the system crash... maybe related, or not.

It appears that we have lost no posts, members details, and the site runs OK.. Very strange

[ini]

This would not follow normal form, surely it would not duplicate data?

I have not done any MySQL projects for around 7yrs though, so my knowledge is a bit ropey now.

The optimization must have been the cause.

[stuart]

It has confused the hell out of me I optimize the database every time I do a backup, and never had this before.... I find it hard to see how each scheme would have a database.. waste of resourses.. but all I have done is remove the one colour scheme...

Very strange