Forum Moderators: open
The database is currently over 1 GB in size.
Lately, I've had MULTIPLE issues with the database which has been causing every SQL driven site on the server to go down.
One time, it was that a few tables had crashed. I had a command set up in cron to run nightly...I think it was mysqlcheck with auto repair option turned on. I checked the logs and it said that the tables had crashed and autorepair failed.
Yesterday, I had another similar issue. I had to go through and repair and optimize each and every table manually to get my sites back and running.
How do I maintain a large MySQL db like this to prevent problems from occurring? Or, if another table crash does occur, how do I get it to auto fix itself?
If you can provide examples, that would be great.
What storage engine are you using? I hope InnoDB.
While MyISAM can be faster for read-mostly applications, it is simply not suitable for a large and/or buggy site.
You need a journaling storage engine that can recover from most corruption on it's own. Along with making sure that your scripts use transactions and use them properly.
If I'm just doing a simple select or some other simple query, should I use transactions for those?
If you never write to the database, you don't need transactions.
If you have any scripts that write to the database, you should be using transactions for each and every query.
Any place where you are using multiple queries that are related, you will need to wrap the whole thing in a transaction. (Say, a query to look up an order, and then a separate query to look up order details.)
It's usually not sufficient to only use transactions for writes. What would happen if the database changed in the middle of a series of related reads? Could be serious chaos.
In some/most language interfaces, there is an "auto-commit" feature you can turn on. (It's not a native MySQL feature.) This will automatically create a transaction for each simple query.
(I use this in Python MySQLdb. I took a quick look and see it's supported in PEAR for PHP.)
If your script for some reason (logic, caught exception or error) doesn't complete the transaction, it has to roll it back. If the script dies, normally any open transactions will be rolled-back as well.
If you are getting corruption of a MySQL database as a regular occurrence, there is something seriously wrong.
