My.cnfサンプル
提供：MySQL Practice Wiki
このコーナーでは、典型的なmy.cnfのサンプルを掲載します。MySQLに同梱されているサンプルは少し古いので、最近のシステムに合わせた設定を掲載します。参考にして使ってみてください。
目次 |
OLTPでメモリ8GB/InnoDBがメインの場合
[mysqld] # basic settings port = 3306 socket = /tmp/mysql.sock user = mysql character-set-server = utf8 basedir = /usr datadir = /var/lib/mysql tmpdir = /var/tmp/mysql core-file # buffers key_buffer = 128M max_allowed_packet = 16M table_cache = 1024 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 64M max_connections = 512 thread_cache_size = 128 query_cache_size = 0 # binlog and replication log-bin = mysql-bin server-id = 1 max_binlog_size = 256M # if the system is the master, comment out the following line. # sync_binlog=1 # InnoDB configurations innodb_buffer_pool_size=6G innodb_additional_mem_pool_size=20M innodb_flush_method=O_DIRECT innodb_log_file_size=256M innodb_log_files_in_group=2 innodb_log_buffer_size=64M innodb_flush_log_at_trx_commit=1
OLTPでメモリ2GB/InnoDBがメインの場合
[mysqld] # basic settings port = 3306 socket = /tmp/mysql.sock user = mysql character-set-server = utf8 basedir = /usr datadir = /var/lib/mysql tmpdir = /var/tmp/mysql core-file # buffers key_buffer = 64M max_allowed_packet = 10M table_cache = 512 sort_buffer_size = 1M read_buffer_size = 256K read_rnd_buffer_size = 1M myisam_sort_buffer_size = 64M max_connections = 300 thread_cache_size = 100 query_cache_size = 0 # binlog and replication log-bin = mysql-bin server-id = 1 max_binlog_size = 256M # if the system is the master, comment out the following line. # sync_binlog=1 # InnoDB configurations innodb_buffer_pool_size=1400M innodb_additional_mem_pool_size=20M innodb_flush_method=O_DIRECT innodb_log_file_size=256M innodb_log_files_in_group=2 innodb_log_buffer_size=64M innodb_flush_log_at_trx_commit=1
Webのバックエンドでメモリ2GB/MyISAMのみの場合
[mysqld] # basic settings port = 3306 socket = /tmp/mysql.sock user = mysql character-set-server = utf8 basedir = /usr datadir = /var/lib/mysql tmpdir = /var/tmp/mysql core-file # buffers key_buffer = 512M max_allowed_packet = 10M table_cache = 4096 sort_buffer_size = 1M read_buffer_size = 256K read_rnd_buffer_size = 1M myisam_sort_buffer_size = 64M max_connections = 512 thread_cache_size = 128 query_cache_size = 64 query_cache_type = ON # binlog and replication log-bin = mysql-bin server-id = 1 max_binlog_size = 256M # if the system is the master, comment out the following line. # sync_binlog=1 skip-innodb
Linuxの場合は、sysctl.confにおいて以下の設定をすること。
fs.file-max=8000
