Large MySQL Databases
Posted May 6th, 2010 @ 07:18pm by Erik J. Barzeski
I'll search the web later for answers, but I thought I'd put this up now and see if anyone had any more directed things so I could narrow my focus a bit.
I have a MySQL database that's now 1 GB in size. It has 235 tables, the largest four of which are 263.0 MiB, 343.5 MiB, 239.6 MiB, and then just 55.9 MiB.
The database powers a vBulletin forum.
Generally things are fine, but I'm always looking to optimize. I had a fairly optimized setup for vBulletin 3.0, but with 4.0 a lot changed, I think, and I'm looking for some specific MySQL (MyISAM table types) things which can help.
Any ideas? I'll answer any questions as best as I can too.
Donate Life
Posted 28 May 2010 at 4:19am #
Well, I say this to people a lot, whats the problem your trying to solve here?
Do you have lots of slow/stuck queries ? If so, post the actual queries (along with show create table for any tables involved and explain , and show table status for those tables too).
Are you using too much CPU / Disk I/O or memory ? Those are going to all be tradeoffs for each other though, if your memory constrained you might have to sacrifice disk I/O to keep your memory profile down, if your I/O constrained you can help that out by adding memory (either physically adding more and thus increasing your cached content, or just adjusting your MySQL settings to make better use of your memory or some combination there of).
That said, i've not worked with vB a TON, but my understanding is that its a relatively mature script and you really shouldn't have issues with things like amateurish schema mistakes.
If you really want more help feel free to hit me up (my Jabber handle is my email but without the -nslog suffix on the local part and/or my full name is my AIM handle).
Posted 28 May 2010 at 1:22pm #
Try running the mysqltuner script at http://mysqltuner.pl/mysqltuner.pl