MySQL

MySQL database files (data and log files) can be placed directly on a PersistentFS file system. The following MySQL configuration settings are recommended:

datadir = a directory on the mountpoint such as /mnt/pfs/mysql
innodb_autoextend_increment = 256K
innodb_doublewrite = 0
innodb_flush_log_at_trx_commit = 0


Attached below is a compete /etc/my.cnf roughly optimized for an EC2 small instance.

MySQL uses the fsync() system call to ensure transactions have been saved to disk. As required by MySQL and other databases, the fsync system call in PersistentFS will not return until all data has been saved to S3, ensuring the integrity of transactions. See http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html

Setting innodb_flush_log_at_trx_commit = 0 causes transactions to be saved to S3 once per second, and as a result, the last second of transactions can be lost in the event of a crash. Setting innodb_flush_log_at_trx_commit = 1 (its default value) will cause all transactions to be saved to S3 immediately, but may result in lower write performances. See http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#option_mys...

Note it is safe to set innodb_doublewrite = 0 because it is not possible for PersistentFS to write a partial database page. (The default PersistentFS file block size is 256 KB, and the InnoDB page size is hardcoded at 16 KB.) See http://www.mysqlperformanceblog.com/2006/08/04/innodb-double-write/

--------------

/etc/my.cnf:

[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
datadir=/pfs/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
skip-networking
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
skip-locking
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
net_buffer_length = 8K
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
query_cache_size= 24M
#log-slow-queries = /var/log/mysqlslow.log
#long_query_time = 5

##Inno DB
#innodb_file_per_table
innodb_autoextend_increment = 256K
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 24M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_doublewrite = 0
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 2000

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid