I want to start clustering my PHP app servers but don't want to go for a sticky-sessions load balancing setup at this point. However, I want sessions that are persisted to disk and implement proper locking. I have a server already dedicated to the database with comfortable amount of free ram so I'd like to consider using an additional instance of MySQL purely for session storage on the same machine. Currently my sessions folder on the file system is ~131Mb and has ~23k files. I'm planning on using InnoDb with SELECT … FOR UPDATE to implement the session locking.
Is running two instances of MySQL optimized for different purposes on the same server a bad idea? (If so, why?)
For the sessions-only instance what are some optimization tips? I want it to be persistent, but don't mind losing a little bit of data in the event of a disaster whereas with the main app db I don't want to risk any data loss.
If I can spare say 256Mb of ram for this MySQL instance (main instance has ~6Gb), how should I allocate it?
Disable query cache or no?
What settings can I use to reduce disk writes (since I don't care about durability so much with this instance)? E.g. innodb_locks_unsafe_for_binlog, innodb_flush_method, etc..
Best Answer
You could experiment with
innodb_flush_log_at_trx_commit
According to the MySQL Documentation on innodb_flush_log_at_trx_commit
Based on this, values other than 1 put InnoDB at risk of losing 1 second's worth of transactions, or a transaction commit's worth of data.
As far as innodb_flush_method, the default is best. I wrote an earlier post to describe the effects of tweeking it.
As for running multiple instances of MySQL, it's OK as along as you have enough memory for the OS and enough memory of DB Connections for the multiple instances.