The Deal on Database Refreshes
Many people visit FreeSQL.org and create databases every day. After just a few month of operation, I can easily have in excess of 20,000 active databases. MySQL seperates databases by keeping each in it's own subdirectory. This means that the main database directory must be searched whenever a new database connection is made. With thousands of databases this can dramatically slow down performance when opening new database connections.
Not all of the databases I host are in active use. Unfortunately, just the existance of an empty database contributes to the problem. So the question is, how do I weed out the unused databases? Throwing more hardware at the problem just won't work, people create databases too fast!
At first, I tried to parse my logs and see who's database has been active. If they made a connection at any time I would mark it as "in use". Then after a month or so, if they were unused I would move them offline. That became too much work on my part, and it was also error prone.
Then I tried just removing empty databases. If after a month a database remained empty, I assumed that the user had lost interest and I would delete it. Invariably, I would get emails from people saying that their database was gone and that even though they'd been away for a while they still wanted a database. Again, too much work on my part
I really needed a way for users themselves to flag their databases as "in use" and to not cause myself too much work. Here is what happens: Once I decide that I've hit the limit of active databases I schedule a "refresh". All MySQL databases are copied offline and become unavailable. User panic ensues because this breaks many of their critical web applications, homework assignments and blogs. They rush to the "restore" page and submit their database name. From there magical elves (also known as a cron job) move the database from the offline directory back into production. This happens fairly quickly and usually without error (although there is at least one pesky bug that I'm currently chasing).
Basically, refreshes and occasional service interruptions are a fact of life. If your app cannot stand an hour or even a day of downtime, you really need to look into commercial hosting Note: this only applies to MySQL not PostgreSQL because I host far more MySQL databases than PostgreSQL.