tuning mysql for nonprofit site
ach64
nginix
apc
memcached
mysql
Don't know much about mysql, so relied on a "mysqltuner.sh" I found to come up with this my.cnf. After a few days uptime the site gets sluggish, and restarting mysql makes it fast again. Anything obvious I can change so I don't have to keep bouncing mysql?
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
port = 3306
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1024M
table_open_cache = 64
table_cache = 96
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
query_cache_size = 8M
query_cache_limit = 10M
thread_cache_size = 4M
log-slow-queries
bind-address=127.0.0.1
local-infile=0
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
default-storage-engine = innodb
innodb_buffer_pool_size = 100M
innodb_log_file_size = 50M
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_additional_mem_pool_size = 20M
innodb_thread_concurrency = 4
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
Also notice that mysql is by far the largest consumer of memory on my node. My guess is that at some point it starts utilizing swap thereby killing performance.
I have vm.swappiness = 10 in my sysctl.conf.
Right now site only has a couple of users, too. I know it would fall over under any kind of load, not that I anticipate that - just hosting a simple site for our cub scout pack. Some stats (note I was just migrated to new hypervisor a couple of hours ago):
The drupal site is