SQL Database issues and questions

[The_Hawk]

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.

[snakes1100]

The default Vb tables in your DB already have indexes, your host is most likely blowing smoke where the sun dont shine because they dont know why its happening, so they blame something.

There can be numerous things causing this, simple changes to my.cnf for mysql would be a start, if the post table is being placed in tmp while it dumps, i would start by changing these two values & test the backup again.

tmp_table_size = 128M / 256M or 512M etc
max_heap_table_size = 128M

[The_Hawk]

Thanks for that, I have been thinking the same thing about the host, but then not being an expert I haven't been comfortable enough to call them on it.


FYI the my.cnf file currently looks like this:

[mysqldump]
max_allowed_packet=16M
quick
[mysqlhotcopy]
interactive-timeout
[mysqld]
open_files_limit=2810
read_buffer_size=2M
max_user_connections=512
myisam_sort_buffer_size=64M
sort_buffer_size=2M
query_cache_size=64M
wait_timeout=60
skip-external-locking
thread_cache_size=8
connect_timeout=10
max_allowed_packet=32M
server-id=1
key_buffer=384M
max_connections=500
thread_concurrency=8
read_rnd_buffer_size=8M
table_cache=512
max_heap_table_size=64M
tmp_table_size=64M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer=256M
read_buffer=2M
sort_buffer_size=256M
write_buffer=2M

I am running vBulletin 4 and Wordpress so nothing exactly out of the box.


I have this plug in:

http://www.vbulletin.org/forum/showthread.php?t=231481

Which schedules a backup of the SQL database at ~midnight, then another cron job which I scripted to dump the wordpress database then rsync both databases and the public_html folder to a remote server. I have run this manually to confirm it's working as well as leaving it to run overnight with the same results.

As I said, what's doing my head in is the fact I can manually or via cron jobs schedule this and it works, yet the cPanel done fails. :S

It's a virtual dedicated machine so it is shared to some extent, maybe all backups are running across the system at the same time and causing massive IO issues... but then you would think the host would notice that?

Historically I have always wanted to keep my server in Australia being a forum predominantly used by Australians, but I'm seriously considering moving it to a US (overseas at least) based server instead. The only problem then is trying to pick a provider!

[snakes1100]

As cpanel can be set to backup the entire account, files & db, the db dumps will be done via the --all-databases option, but thats still one at a time, that is going to still be a lot of IO on the server while that happens, whether its killing SQL, thats another issue.

I havent looked at the cpanel scripts in a log time, so im not sure how much if any that they have changed & what they are doing.

Without debugging it, while it runs to find out what the failure point is, its really hard to speculate on it.