I have some issues on my server and I need to improve it.
First, every 2 hours, I insert / update / delete about 1 million results (wich is all the table) in a MyISAM table and the table must be accessible during this time from my website.
About 1 time by month, my site doesn't display and the message "Unable to connect to the database" is shown. When I restart the server, I can see this table must be repaired.
Is there a way to prevent my table from being "broken" or to repair it automaticcaly? Should I change the engine to innoDB? Why?
Secondly, During this operation my site is really slow and it's not only the page wich show this table results.
I have full access to my server, but I don't know what I am looking for... The operation is a cron job with a nice of 19.
I want my website to be at the same speed whatever I do in the background. The operation I do takes about an hour to execute and it doesn't matters if it takes more than that.
Running the job at a low priority doesn't make a lot of sense.
This is a lot of data. Your really pushing the envelope of what the system is capable of. As to how to fix the problem....how much effort would it really be to try using innodb? IMHO less than posting here, waiting for answers and trying them out.
There are lots of other things you could try - like running the cron job against a shadow copy of the table and swapping it in place of the table accessed by the front end (but on its own this won't solve the corruption issue).
Certainly I've run into problems carrying out lots of deletes against MyISAM tables.
I suspect that there may be many ways to fix the problem - but you've not given much indication of why you need such a high data turnover; since I doubt that your users are looking at the detail of all the records you are changing, I suspect that the biggest gain would be in consolidating the data before it hits the database.
Even when you disable access to the fast turnover data via cron jobs and front end URLs? That's a completely different problem.