Minimizing / Reducing MySQL database size

[pols1337]

Hi, can you provide any options or strategies on how to minimize the database size used by Coppermine Photo Gallery?  (eg. deleting all comments? deleting all votes? deleting all views?)

Here's my problem: my webhosting plan allows a certain amount of disk space for MySQL Databases.  While I am currently at only 80% of max capacity, I would like to be proactive in conserving my disk space used by Coppermine so that my galleries can continue to grow in the future.  Thus, any helpful suggestions would be greatly appreciated.   

Joel

[Αndré]

Most databases blow up because of the detailed hit and vote statistics. I suggest to have a look which tables takes the most space, as nobody knows your gallery setup and database content.

[pols1337]

Thanks for the input.  Luckily, I do not have the detailed hit and vote statistics (Config > Logging and Statistics) turned on. 

Please see attached for screenshot of phpMyAdmin.  Most of it is for pictures, then a big drop-off to albums and fav-pics.  Do you have any suggestion on how to reduce the space used for pictures?

[pols1337]

For some reason, the picture didn't attach.  Trying again.

[phill104]

Not a huge amount you can do. Is there no chance of moving to a more flexible host? If they have restrictions on DB size you might also find that as your gallery becomes more popular that you run into other more pressing restrictions they may impose.

[pols1337]

Yeah, so my initial request for reducing my website's database was based upon nearing capacity of my current plan's MySQL size.  It prompted me to start searching for other hosting solutions and I think I've close to finalizing a good deal with one.  This will preempt all of my problems.

BUT then I thought it would still be helpful for myself to know how to reduce / optimize my Coppermine database for future knowledge.  It certainly doesn't hurt to learn more about administering my galleries, right?  The fact that I know how to even go in to myPHPAdmin, and to sort and search through the raw tables, is pretty self-empowering.

So back to my original topic: any suggestions or tips on how to minimize the database size used by Coppermine Photo Gallery?

[phill104]

Well the records you show for your galley are about as minimal as you can go. You don't seem to be storing a lot of stuff that could increase your DB size such as all the image data, detailed stats etc. You seem to have over 60k images in your db and it is the data for those that takes up most of your space in there and all the information recorded in that table is needed. So that only leaves a couple of options. You can use myisampack to compress your db. While this does do a great job of reducing space it does come at a cost. On busy systems it slows down access and increases cpu/memory usage. It may only be by a tiny bit but with lots of users it can add up. It is a good option thoug. The other option you know it to change to a host with a more flexible approach. These days only a few hosts limit the db disk space in this way as more and more applications are relying on the db especially with the popularity of CMS systems like Joomla and Wordpress along with all the galleries and forums out there.

[pols1337]

So I was thinking, if I let Coppermine skip over the intermediate pictures and use the full-size on the displayimage.php page, would that shrink the picture table by 1/3?  (Because ... Coppermine has pointers to the thumbnail, intermediate, and full size.  So if I take out the intermediate, that basically cuts away one third of the needed pointers, ya?) 

That would save on storage space too, since I could delete all those intermediate pics, but it would increase the bandwidth.  Is my thinking correct?

[pols1337]

Mm so I was reading some more about misampack.  (By the way, thanks for pointing out this nifty tool).  Once the tables become compressed, they become read-only.  So I probably wouldn't be able to compress the tables for pictures, votes, and comments anyways since those constantly change.

Here's the article I read:
http://www.techrepublic.com/article/save-disk-space-by-compressing-mysql-tables/5852557

[phill104]

Only partly. You would save on normal disk space for sure removing intermediate images but the amount per image saved on removing that element from the db would none. Examine the table and you will see exactly what data is stored.

http://documentation.coppermine-gallery.net/en/dev_database.htm#db_pictures

And yes packing the tables does make them read only so only a few of your tables could be compressed. As I said, a decent hosting package is your only real option.

[Αndré]

Coppermine has pointers to the thumbnail, intermediate, and full size.

As Phill already suggested, please have a short look at the pictures table. Coppermine only stores the file path and file name for the full-sized picture in the database. We use prefixes for the other related pictures.