Hey forum - I'm not a database admin, and I've been looking at other sample mysql configs and optimization stuff. Someone on the Linode forum told me to change over to InnoDB and also consider running the database on a separate VPS. Before I follow the seemingly good advice, wanted to get feedback from here:
OS: Debian Lenny
VPS: a 1GB RAM Linode VPS
Drupal: 6.x
MySQL: 5.xSite purpose: this site is not serving anonymous users at all. Everyone hitting the site is logged in - it is a social media site with images and extensive searching based on integer and text fields in multiple CCK types.
*Note: 1GB of RAM will increase. We just launched the site, but as members increase I'll have to up all of that.
Instructions I was given:
Update 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 = INNODBIt 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.
My MY.CNF file currently:
[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/
Comments
Well you have skip_innodb in
Well you have skip_innodb in that config so I wouldn't run those alter table statements yet. Remove that line and add some lines like this.
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
(20-30% of RAM) obviously you'll have to figure out that number for yourself.
Those lines you already have recommended are fine although if you already have any innodb tables then you might need to not add innodb_file_per_table = 1
You should look at memcached and memcache.inc memcache-session.inc as well for caching to reduce throughput on the db.
Full Fat Things ( http://fullfatthings.com ), my Drupal consultancy that makes sites fast.
Thank you so much man. I have
Thank you so much man.
I have not done any innodb stuff yet - this is a clear Mercury installation on a Linode VPS.
Thanks for the suggested lines and for telling me to remove the skip innodb line (I'm not a DB admin, but more of a firewall/server guy).
My installation does have memcache running, but I don't know what to change if anything.
Is innodb in your opinion the correct move here? Someone on the Linode forum indicated it would be a better performance thing for me and avoid issues down the road.
Again, thank you.
!!!DO IT NOW!!!!
Yes I would recommend moving to InnoDB. Post back your success/failure!
Full Fat Things ( http://fullfatthings.com ), my Drupal consultancy that makes sites fast.
Stewsnooze: Everything went
Stewsnooze:
Everything went great. Thanks a lot for the information. I added the lines you mentioned including the following:
default-storage-engine = innodb
innodb_file_per_table = 1
Restarted MySQL, did SHOW TABLES after changing to the database, and then did the ALTER table stuff for all tables which was a PAIN. Should have used a script, but I'm not good with that.
Read something afterwards that any search_ tables should be left MyISAM so I moved them back.
Server seems to be fine. Now for the fun of configuring.
Hey, and if you have any tips on Apache tuning, I'd love to see the link or hear them. Thanks again man.