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!!!



[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]

user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking

bind-address = 127.0.0.1

key_buffer = 16M
max_allowed_packet = 4M
thread_stack = 128K
thread_cache_size = 64

myisam-recover = BACKUP


query_cache_limit = 32M
query_cache_size = 32M

expire_logs_days = 10
max_binlog_size = 100M

skip_innodb
log-slow-queries = slow-queries.log
tmp_table_size = 64M
max_heap_table_size = 64M
table_cache = 1024
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_connect_errors = 999999
query_cache_type = 1
myisam_sort_buffer_size = 64M
join_buffer_size = 512K
read_buffer_size = 2M
sort_buffer_size = 3M
max_connections = 120
max_user_connections = 800

[mysqldump]
quick
quote-names
max_allowed_packet = 8M

[isamchk]
key_buffer = 16M

!includedir /etc/mysql/conf.d/

_________________
kyler d. boudreau
theatereleven.com

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.

_________________

/* TODO: need to add signature to posts */

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.

_________________
kyler d. boudreau

theatereleven.com

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).

_________________

/* TODO: need to add signature to posts */

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?

_________________
kyler d. boudreau

theatereleven.com

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.

_________________

/* TODO: need to add signature to posts */

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

_________________
kyler d. boudreau

theatereleven.com

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

innodb_buffer_pool_size=250M (20-30% of RAM)
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=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!

_________________
kyler d. boudreau

theatereleven.com

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!)

_________________

/* TODO: need to add signature to posts */