[MySQL]MySQL 환경변수 파일

6187952_my.txt

${MYSQL_HOME}/bin/mysqld --verbose --help | grep -A 1 'Default options'

/usr/local/mysql/bin/mysql --verbose --help |grep -A 1 'Default options'

innodb_additional_mem_pool_size = 20M

innodb_buffer_pool_size = 1G

innodb_data_file_path = ibdata1:1024M;ibdata2:1024M;ibdata3:1024M:autoextend

#innodb_data_home_dir = ""

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 8M

innodb_log_file_size = 2000M

innodb_log_files_in_group = 2

#innodb_flush_method=O_DSYNC

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

일반적으로 위에 파라미터를 신경쓰시면 도움이 되실겁니다.

ib데이터는 미리 만들어 두고

innodb_log_file 은 트랜잭션 로그 파일이거든요

최대 4G 까지 만들수 있어요

innodb_log_file_size * innodb_log_files_in_group

MySQL Innodb Settings 

The most important ones are:

innodb_buffer_pool_size 70-80% of memory is a safe bet. I set it to 12G on 16GB box.

UPDATE: If you’re looking for more details, check out detailed guide on tuning innodb buffer pool

innodb_log_file_size – This depends on your recovery speed needs but 256M seems to be a good balance between reasonable recovery time and good performance

innodb_log_buffer_size=4M 4M is good for most cases unless you’re piping large blobs to Innodb in this case increase it a bit.

innodb_flush_log_at_trx_commit=2 If you’re not concern about ACID and can loose transactions for last second or two in case of full OS crash than set this value. It can dramatic effect especially on a lot of short write transactions.

innodb_thread_concurrency=8 Even with current Innodb Scalability Fixes having limited concurrency helps. The actual number may be higher or lower depending on your application and default which is 8 is decent start

innodb_flush_method=O_DIRECT Avoid double buffering and reduce swap pressure, in most cases this setting improves performance. Though be careful if you do not have battery backed up RAID cache as when write IO may suffer.

innodb_file_per_table – If you do not have too many tables use this option, so you will not have uncontrolled innodb main tablespace growth which you can’t reclaim. This option was added in MySQL 4.1 and now stable enough to use.

Also check if your application can run in READ-COMMITED isolation mode – if it does – set it to be default as transaction-isolation=READ-COMMITTED. This option has some performance benefits, especially in locking in 5.0 and even more to come with MySQL 5.1 and row level replication.

The slow query log

- Logs all queries that take longer than log_query_time

- Can also log all queries that don't use indexes with
  --log-queries-not-using-indexes

- To log slow administrative commands use
  --log-slow-admin-statements