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


port                           = 3306
socket                         = /var/run/mysqld/mysqld.sock


user                           = mysql
default_storage_engine         = InnoDB
socket                         = /var/run/mysqld/mysqld.sock
port                           = 3306
#bind-address                   =
pid_file                       = /var/run/mysqld/mysqld.pid
thread_concurrency             = 4

# MyISAM #
key_buffer_size                = 64M
myisam_recover                 = FORCE,BACKUP

max_allowed_packet             = 8M
max_connect_errors             = 1000000
sysdate_is_now                 = 1
innodb                         = FORCE
innodb_strict_mode             = 1

datadir                        = /var/lib/mysql

log_bin                        = /var/lib/mysql/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1

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

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

err-log                        = /var/log/mysql/mysqld.log

5 Replies

If php-fpm is using too much system resources, you could try spawn-fcgi-php instead. I would also suggest using strace -p 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.

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.

Between this:


I can't switch to Postgresql

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.


Please enter an answer

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct