Tuning MySQL/PHP/NGINX/PHP-FPM
Here is my.cnf from mysql running percona server 5.5.25
[mysql]
# CLIENT #
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
# GENERAL #
user = mysql
default_storage_engine = InnoDB
socket = /var/run/mysqld/mysqld.sock
port = 3306
#bind-address = 192.168.130.231
pid_file = /var/run/mysqld/mysqld.pid
thread_concurrency = 4
# MyISAM #
key_buffer_size = 64M
myisam_recover = FORCE,BACKUP
# SAFETY #
max_allowed_packet = 8M
max_connect_errors = 1000000
skip_name_resolve
#sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
#sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE
sysdate_is_now = 1
innodb = FORCE
innodb_strict_mode = 1
# DATA STORAGE #
datadir = /var/lib/mysql
# BINARY LOGGING #
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 14
sync_binlog = 1
# CACHES AND LIMITS #
tmp_table_size = 16M
max_heap_table_size = 16M
query_cache_type = 0
query_cache_size = 32M
query_cache_limit = 1M
max_connections = 200
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 4096
table_open_cache = 4096
# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# LOGGING #
log_error = /var/log/mysql/mysql-error.log
#log_queries_not_using_indexes = 1
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /var/log/mysql/mysql-slow.log
[safe_mysqld]
err-log = /var/log/mysql/mysqld.log
5 Replies
mysqltuner.pl
@danlee:
I can't switch to Postgresql
and this:
@danlee:
IPB
I'm not sure which is more unfortunate! I haven't had a good experience with Invision since the mid-2000s. And I'm a dyed-in-the-wool PostgreSQL fanatic
That said, the reason I prefer PostgreSQL is features, not speed (though the speed is very comparable) and I don't think that being stuck on MySQL should present a major problem for you. In fact, it's easier to parallelize MySQL than it is Postgres, so if your loads get that massive you're in a good place.
Something like a forum is a good fit for MySQL, too, because few of MySQL's limitations (combining full-text search and foreign keys, transactions and full-text, etc) matter in this setting.
Edit: Whoops, didn't notice the post date. Sorry for the necromancy, if that's frowned upon here. Conversely if y'all are okay with that I'll express no remorse for twisting the wicked energies to my unholy will.