Need some assistance tuning some configs. Any help is appreciated. I have two VPS's, a 768 and a 512 both running arch linux. The 768 runs nginx/php-fpm/php/postfix. The 512 just runs mysql.

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

Is there a problem?

_________________

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

If php-fpm is using too much system resources, you could try spawn-fcgi-php instead. I would also suggest using strace -p <pid> to track what mysql, php, nginx, etc are doing. It could give some clues on any hangups. Alternatively (I don't know if this is viable for you) you could switch to PostgreSQL instead of Mysql. It uses less system resources in many instances.

_________________
If you haven't heard this:

echo '[q]sa[ln0=aln256%Pln256/snlbx]sb3135071790101768542287578439snlbxq'|dc

You aren't using your computer enough.

The issue is more with MYSql, I can't switch to Postgresql as it is not compatible with IPB, or more to say, IPB does not work on Postgresql. I am trying to use INNODB, so I don't run into the problem with locking tables, but having trouble with the memory limitations on the 512. Right now with the current config, over 750MB Ram is required if pegged.

Try running mysqltuner.pl, and paste its recommendations here.

_________________

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

Between this:



and this:



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.