My forum has grown over time and I'm having to learn more and more to keep it all together, something I have largely kept on top of.
My forum stats: Threads 65,627 Posts 833,378 Members 84,508 Active Members 64,504
The database is about 2GB before compression.
The whole thing is running on a single VDS with CentOS and CPanel.
But to the problem. Lately when the overnight backups have been running it seems to cause the SQL server to stop responding during the database backup, this was then leading to the "post" table being marked crashed and me having to manually repair it before things would start working again. The crashing part I think is sorted, but the SQL failing isn't.
Strangely I can manually backup (or via a separate cron job) the SQL database without issues… it's just during the CPanel backup things fall apart.
But to my question, the hosting company has suggested that I need to:
"Properly index the tables of the database to prevent this from happening again. Please contact your developer and properly optimize the database and repair your tables."
So I'm aware of the admincp option to repair and optimise (or what I gather is largely the same through myphpadmin, but I can't seem to find anything on indexing a vBulletin database.
From what I read indexing is good if you have lots of reads, bad if you have lots of writes…
So the two questions are:
Can you index a vBulletin database; and
should you?
Any information would be much appreciated.
My forum stats: Threads 65,627 Posts 833,378 Members 84,508 Active Members 64,504
The database is about 2GB before compression.
The whole thing is running on a single VDS with CentOS and CPanel.
But to the problem. Lately when the overnight backups have been running it seems to cause the SQL server to stop responding during the database backup, this was then leading to the "post" table being marked crashed and me having to manually repair it before things would start working again. The crashing part I think is sorted, but the SQL failing isn't.
Strangely I can manually backup (or via a separate cron job) the SQL database without issues… it's just during the CPanel backup things fall apart.
But to my question, the hosting company has suggested that I need to:
"Properly index the tables of the database to prevent this from happening again. Please contact your developer and properly optimize the database and repair your tables."
So I'm aware of the admincp option to repair and optimise (or what I gather is largely the same through myphpadmin, but I can't seem to find anything on indexing a vBulletin database.
From what I read indexing is good if you have lots of reads, bad if you have lots of writes…
So the two questions are:
Can you index a vBulletin database; and
should you?
Any information would be much appreciated.
Comment