Topic: Database size and Backup Issue
One of my database size has grown upto 240 MB. and now I am unable to take its backup. The script breaks on the way. What is the solution to it?
www.boxygen.pk
- apmuthu
- Moderator
- Offline
Re: Database size and Backup Issue
Close your previous fiscal years and purge them of their data.
- boxygen
- Senior Member
- Offline
Re: Database size and Backup Issue
For a permanent solution I have tried dropmysite.com and its very fast to backup and restore database in case of any failure.
www.boxygen.pk
- boxygen
- Senior Member
- Offline
Re: Database size and Backup Issue
@dz After you close fiscal year and then delete it than it will purge the data. But you won't be able to see previous year's transactions. But I think that is not ideal for any organization. (:
Am I correct @apmuthu
www.boxygen.pk
- apmuthu
- Moderator
- Offline
Re: Database size and Backup Issue
You must take a backup each year. Then close the year and take another backup and then purge it and start your new year. Keep atleast one previous fiscal years data for reference and awaiting closure while the new year is online. Dont forget to backup the entire web install folder as well since it will have the item images and attachments too (company/#/*).
There is a higher probability of large databases getting corrupt more frequently than smaller ones that are properly indexed.
Whenever you need to check on older years that have since been purged, just restore them into some other cloud instance and restore from the backups and view it!
Note that the fiscal years table must have the id in sequence without any gaps and must match the Start Date order as well, otherwise closure and purging will hang / get corrupted.
- boxygen
- Senior Member
- Offline
Re: Database size and Backup Issue
There is a higher probability of large databases getting corrupt more frequently than smaller ones that are properly indexed.
What size of database can generally be classified as large database in FA perspective.
www.boxygen.pk
- apmuthu
- Moderator
- Offline
Re: Database size and Backup Issue
Processor speed and hard disk size and speed will matter apart from sufficient RAM that must be appropriately allocated to PHP/Apache and MySQL. Use MySQL Tuner to tune your db settings.
Upto 10MB backup size has been no problems so far on modern machines.
- boxygen
- Senior Member
- Offline
Re: Database size and Backup Issue
I think thats too low. Because one of my clients one year data has gone up to 55 Mb without zip and 8 mb with zip. We shall do more research on it.
www.boxygen.pk
- Alaa
- Senior Member
- Offline
Re: Database size and Backup Issue
i had a website with database that got over 1GB of size and it worked perfectly till at oneday it started to get corruption and errors..
i believe that a database can go much more than 200 MB with no problems.
- apmuthu
- Moderator
- Offline
Re: Database size and Backup Issue
From time to time, stop FA and DB Access and optimise / re-index the databases after a backup. The indexes should be recreated and the data packed before restarting DB Access.
Migration of all tables to InnoDB in FA 2.4.x when there is no sign of any CASCADES is actually a retrograde step in so far as it increases the difficulty in table data recovery which would require expert assistance and commercial tools. Rarely changing lookup data should not need to reside in InnoDB tables ordinarily.
Only where transactional integrity with commit and rollback along with cascade deletion/updation of child tables are available in the codebase would it become necessary. Linkage with such enabled external applications is also another case when InnoDB conversion would become necessary.
Using the directive to keep files on a per table basis may improve the chances of recovery and restict damage to specific tables in InnoDB which would otherwise by default choose to store everything in one big file - lose it and you lose everything!