Could I get feedback on this MySQL configuration file?

@theatereleven:

Would some kind soul here take a look at the my.cnf file below?

I'm on a 1GB RAM Linode, and not having problems yet, but our site just launched. I'm using Drupal, SOLR, Apache, MySQL (not innob or whatever).

Also….1GB of RAM will be the minimum we run on. Might end up having more depending on the size of the community. This site is NOT dealing with anonymous users. All users are signed in, etc.

Thanks!!!

No specific recommendations here, but I'd consider going with two nodes: one for the database, one for everything else. That will let you adjust either up/down independently, and if Apache+mod_php goes insane and eats everything, it won't take out MySQL.

Also, use InnoDB unless you absolutely must use MyISAM. It is the default in newer MySQL (and Drupal?) for a very good reason: it tends to not shred your data quite as much when something goes wrong. There's no real performance benefit to MyISAM these days, and about the only feature MyISAM has that InnoDB doesn't is full-text search, and you've got that covered with Solr.

Finally, for actual relevant performance suggestions :-), check out http://mysqltuner.pl/. Run the script after you've been running for a few days and it will suggest things to change. Pick a suggestion, research it to figure out what the setting does and whether it seems appropriate, then apply it and repeat the process a few days later. It's an iterative process which depends a lot on your dataset and how you query it.

Hey thanks for the feedback!!!

The other database method - I'm totally for that, but it sounds like if there were a SOLR problem, that newer database method might have issues?

Our site does a lot of searches on db content.

I did run that mysql tuner thing…I seem to be okay by it's recommendations.

Oh….and moving the db to another Linode. Like the sound of that….but not experienced in moving a db. Guess I could just clone the Linode and then remove Drupal from the new one and tell the old drupal to point to the new db server.

Full-text search indexing is an unusual enough feature that you'd have to go out of your way to be relying on it. Anything Drupal-based should fall back to other methods without issue, if it even takes advantage of it in the first place.

Moving a database isn't too bad. The approach you mentioned will do it. Just remember to enable private IP addresses on both Linodes, and shut down the "donor" Linode before starting the clone (otherwise it won't get a clean copy, and that would be… unfortunate).

Sweet….then I think I'll do that route.

Do you have any article you can recommend for converting my Drupal site to using the Innob database structure?

Not too much to it :-) In my.cnf,

default-storage-engine = innodb
innodb_file_per_table = 1

(The second setting tells it to use a different file on the filesystem for each table, instead of lumping them all together into one big file. This will make some things a little easier down the road.)

Then, run this query for each table:

ALTER TABLE foo ENGINE = INNODB

It will churn for a few moments and, bam, it's done. Surprisingly painless. Someone put some thought into this, that's for sure.

Also, once you're InnoDB'd, remember to add –single-transaction to your mysqldump statements and omit any --lock-tables, etc. No need to lock tables to get a consistent read. Angels sing.

Awesome - appreciate this! I'll get back and post after.

The conversion went great. Added the lines you mentioned including:

innodbbufferpool_size=250M (20-30% of RAM)

innodbflushlogattrx_commit=2

innodbflushmethod=O_DIRECT

innodbthreadconcurrency=8

Then restarted MySQL and did the alter table on all of my tables. DANG. That took awhile.

Also…I converted all search_ tables back to MyISAM after reading somewhere that they shouldn't be InnoDB. Bad or good decision?

Hey, and if you have an Apache tuning tips, I'd love to hear them. Thank you!

I don't generally use Apache, but it's usually not the source of most performance issues. So, don't worry too much about it, unless you're using mod_php, in which case you're stuck using mpm-prefork and need to ensure your MaxClients is set reasonably low. But that's old hat.

I'm not sure why the search_ tables wouldn't work just as well with InnoDB at this point; I'm not an all-star Drupal admin, but haven't noticed any search-related (or database-related, for that matter) problems with a handful of Drupal 6 and 7 installations in an all-InnoDB environment. There's a lot of weird advice out there. (And some number of people would consider my advice weird, too!)