Setting_paramaters_for_Redmine_Performance_Tuning.txt

# 「Redmineチューニングの実際と限界」
# グラフやベンチマークはスライドシェアにあります https://goo.gl/VDSX9N
# 本稿で扱った性能ベンチ－マークは下記設定による。 
# Setting_paramaters_for_Redmine_Performance_Tuning
# Description, benchmark graph slides at Slideshare:  https://goo.gl/VDSX9N

# WARNING: 
#  No warranty, No support. 
#  You can use these settings at your own risk.
#  These settings may destroy your system, data, disks. Do not try, if you don't understand the risks.

# Table of Contents
1. Apache2.2, 2.4　HTTP/1.1 通信圧縮 (Transmit data compression by Apache http server)
2. Passenger 
3. MySQL 5.6, 5.7　Linux
4. MySQL 5.6, 5.7　Bitnami
5. Subversion　キャッシュ設定, 通信圧縮 (Server-side cache)
6. Redmine　Subversion同期 ProjectID指定スクリプト (sync script code redmine and subversion. Specific repository.)
7. Redmine　OOBGC for Unicorn
8. Redmine　OOBGC for Passenger
9. OS File System
10. 計測プログラム (Benchmark script for the slide on SlideShare: http://goo.gl/ammvTR  )


--------------------------------------------------------------------
1. Apache2.2, 2.4　HTTP/1.1 通信圧縮
--------------------------------------------------------------------
Apache2.2 /etc/httpd/conf.d/defelate.conf

<IfModule mod_deflate.c>
  DeflateCompressionLevel 1
 #  LogFormat '"%r" %{outstream}n/%{instream}n (%{ratio}n%%)' deflate
 #  CustomLog /var/log/httpd/deflate_log deflate

 <Location />
 # Netscape 4.x has some problems...
 BrowserMatch ^Mozilla/4 gzip-only-text/html

 # Netscape 4.06-4.08 have some more problems
 BrowserMatch ^Mozilla/4.0[678] no-gzip

 # MSIE masquerades as Netscape, but it is fine
 BrowserMatch bMSIE !no-gzip !gzip-only-text/html

 FilterDeclare Compression CONTENT_SET
 FilterProvider Compression DEFLATE resp=Content-Type $text/html
 FilterProvider Compression DEFLATE resp=Content-Type $text/xml
 FilterProvider Compression DEFLATE resp=Content-Type $text/css
 FilterProvider Compression DEFLATE resp=Content-Type $text/plain
 FilterProvider Compression DEFLATE resp=Content-Type $image/svg+xml
 FilterProvider Compression DEFLATE resp=Content-Type $application/xhtml+xml
 FilterProvider Compression DEFLATE resp=Content-Type $application/xml
 FilterProvider Compression DEFLATE resp=Content-Type $application/rdf+xml
 FilterProvider Compression DEFLATE resp=Content-Type $application/rss+xml
 FilterProvider Compression DEFLATE resp=Content-Type $application/atom+xml
 FilterProvider Compression DEFLATE resp=Content-Type $text/javascript
 FilterProvider Compression DEFLATE resp=Content-Type $application/javascript
 FilterProvider Compression DEFLATE resp=Content-Type $application/x-javascript
 FilterProvider Compression DEFLATE resp=Content-Type $application/x-font-ttf
 FilterProvider Compression DEFLATE resp=Content-Type $application/x-font-otf
 FilterProvider Compression DEFLATE resp=Content-Type $font/truetype
 FilterProvider Compression DEFLATE resp=Content-Type $font/opentype
 FilterChain Compression

 # Don't append Vary heder for specific files
 SetEnvIfNoCase Request_URI .(?:gif|jpe?g|png|t?gz|zip|bz2|sit|rar|lzh|exe)$ dont-vary

 # Make sure proxies don't deliver the wrong content
 Header append Vary User-Agent env=!dont-vary
 Header append Vary Accept-Encoding env=!dont-vary
 </Location>
</IfModule>


Apache2.4 /etc/httpd/conf.d/defelate.conf

<IfModule mod_deflate.c>
  SetOutputFilter DEFLATE
  DeflateCompressionLevel 1
  DeflateMemLevel 8
  #  LogFormat '"%r" %{outstream}n/%{instream}n (%{ratio}n%%)' deflate
  #  CustomLog /var/log/httpd/deflate_log deflate

  <Location />
    # MSIE masquerades as Netscape, but it is fine
    BrowserMatch \bMSIE\s(7|8) !no-gzip !gzip-only-text/html

    FilterDeclare Compression CONTENT_SET
    FilterProvider Compression DEFLATE "%{CONTENT_TYPE} = 'text/html'"
    FilterProvider Compression DEFLATE "%{CONTENT_TYPE} = 'text/plain'"
    FilterProvider Compression DEFLATE "%{CONTENT_TYPE} = 'text/css'"
    FilterProvider Compression DEFLATE "%{CONTENT_TYPE} = 'text/javascript'"
    FilterProvider Compression DEFLATE "%{CONTENT_TYPE} = 'text/xml'"
    FilterProvider Compression DEFLATE "%{CONTENT_TYPE} = 'application/xhtml'"
    FilterProvider Compression DEFLATE "%{CONTENT_TYPE} = 'application/xml'"
    FilterProvider Compression DEFLATE "%{CONTENT_TYPE} = 'application/xhtml+xml'"
    FilterProvider Compression DEFLATE "%{CONTENT_TYPE} = 'application/rss+xml'"
    FilterProvider Compression DEFLATE "%{CONTENT_TYPE} = 'application/atom+xml'"
    FilterProvider Compression DEFLATE "%{CONTENT_TYPE} = 'application/javascript'"
    FilterProvider Compression DEFLATE "%{CONTENT_TYPE} = 'image/svg-xml'"
    FilterChain Compression

    # Don't append Vary heder for specific files
    SetEnvIfNoCase Request_URI .(?:gif|jpe?g|png|t?gz|zip|bz2|sit|rar|lzh|exe)$ dont-vary

    # Make sure proxies don't deliver the wrong content
    Header append Vary Accept-Encoding env=!dont-vary
  </Location>
</IfModule>

--------------------------------------------------------------------
2. Passenger
--------------------------------------------------------------------
Apache2 /etc/httpd/conf.d/passenger.conf

# Rails environment settings
#
RailsEnv production

# Multiple Redmine Envirnoment with Sub-URI
RailsBaseURI /its26
RailsBaseURI /its30
RailsBaseURI /its31
RailsBaseURI /its32
RailsBaseURI /its33
RailsBaseURI /its34
RailsBaseURI /its40

# Remove http headers added by Passenger.
#
Header always unset "X-Powered-By"
Header always unset "X-Rack-Cache"
Header always unset "X-Content-Digest"
Header always unset "X-Runtime"

# Tuning Parameters for Passenger.
#
PassengerMaxPoolSize 20
PassengerMaxInstancesPerApp 4
PassengerPoolIdleTime 3600
PassengerHighPerformance on
PassengerStatThrottleRate 10
RailsSpawnMethod smart
RailsAppSpawnerIdleTime 86400
PassengerMaxPreloaderIdleTime 0

# For OOBGC < Experimental >
# PassengerSpawnMethod direct


--------------------------------------------------------------------
3. MySQL 5.6, 5.7　Linux
--------------------------------------------------------------------
* MySQL 5.6  my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# Pre requesties
# a linux box with 16GB or more memory for this setting.

[mysqld]
init-connect = SET NAMES utf8

# InnoDB Paramaters
# http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON

innodb_buffer_pool_size             = 8G
innodb_log_file_size                = 4G
innodb_log_buffer_size              = 8M
innodb_flush_log_at_trx_commit      = 1
innodb_log_files_in_group           = 2

innodb_thread_concurrency           = 16
innodb_concurrency_tickets          = 5000
innodb_old_blocks_time              = 1000
innodb_open_files                   = 300
innodb_stats_on_metadata            = 0
innodb_checksum_algorithm           = crc32
innodb_file_format                  = Barracuda
innodb_max_dirty_pages_pct          = 90
innodb_lock_wait_timeout            = 120
innodb_print_all_deadlocks          = ON
innodb_data_file_path               = ibdata1:10M:autoextend
innodb_autoextend_increment         = 64
innodb_flush_method                 = O_DIRECT
innodb_read_io_threads              = 8
innodb_write_io_threads             = 8
innodb_buffer_pool_instances        = 8
innodb_rollback_segments            = 32
innodb_use_sys_malloc               = 1

default_storage_engine = InnoDB
explicit_defaults_for_timestamp = 1

# For SSD
innodb_flush_neighbors              = 0

# MySQL Server System Variables
# http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
join_buffer_size                    = 128M
sort_buffer_size                    = 1M
query_cache_type                    = 1
query_cache_size                    = 128M
query_cache_limit                   = 6M
tmp_table_size                      = 512M
max_heap_table_size                 = 1G

read_rnd_buffer_size                = 16M
key_buffer_size                     = 32M
max_allowed_packet                  = 16M
read_buffer_size                    = 1M
bulk_insert_buffer_size             = 64M

max_connections                     = 100
character-set-server                = utf8
skip-external-locking
thread_cache_size                   = max_connections/3
table_open_cache                    = 4096
table_open_cache_instances          = 64
performance_schema                  = OFF

open_files_limit                    = 65500
max_prepared_stmt_count             = 65528


* MySQL 5.7  my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
init-connect                        = SET NAMES utf8
default_password_lifetime           = 0
server-id                           = 1

# InnoDB Paramaters
# http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON

innodb_buffer_pool_size             = 8G
innodb_log_file_size                = 4G
innodb_log_buffer_size              = 8M
innodb_flush_log_at_trx_commit      = 1
innodb_log_files_in_group           = 2
innodb_thread_concurrency           = 16
innodb_concurrency_tickets          = 5000
innodb_old_blocks_time              = 1000
innodb_open_files                   = 300
innodb_stats_on_metadata            = 0
innodb_checksum_algorithm           = crc32
innodb_max_dirty_pages_pct          = 90
innodb_lock_wait_timeout            = 120
innodb_print_all_deadlocks          = ON
innodb_data_file_path               = ibdata1:10M:autoextend
innodb_autoextend_increment         = 64
innodb_flush_method                 = O_DIRECT
innodb_read_io_threads              = 8
innodb_write_io_threads             = 8
innodb_buffer_pool_instances        = 8
innodb_rollback_segments            = 32

default_storage_engine = InnoDB
explicit_defaults_for_timestamp     = 1

# For SSD
innodb_flush_neighbors              = 0
#innodb_page_size                   = 4K
innodb_io_capacity                  = 2000
innodb_io_capacity_max              = 6000
innodb_lru_scan_depth               = 2000

# MySQL Server System Variables
# http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER
join_buffer_size                    = 128M
sort_buffer_size                    = 1M
query_cache_type                    = 1
query_cache_size                    = 128M
query_cache_limit                   = 6M
tmp_table_size                      = 512M
max_heap_table_size                 = 1G
read_rnd_buffer_size                = 16M
key_buffer_size                     = 32M
max_allowed_packet                  = 16M
read_buffer_size                    = 1M
bulk_insert_buffer_size             = 64M
max_connections                     = 100
character-set-server                = utf8
skip-external-locking
thread_cache_size                   = max_connections/3
table_open_cache                    = 4096
table_open_cache_instances          = 64
performance_schema                  = OFF
back_log                            = 50 + (max_connections / 5)
expire_logs_days                    = 7
open_files_limit                    = 65500
max_prepared_stmt_count             = 65528
innodb_print_all_deadlocks          = ON


--------------------------------------------------------------------
4. MySQL 5.6, 5.7  Bitnami
--------------------------------------------------------------------

MySQL 5.6, 5.7  my.cnf for Bitnami

# The MySQL server
[mysqld]
# set basedir to your installation path
basedir=C:/Bitnami/REDMIN~1.0-2/mysql
# set datadir to the location of your data directory
datadir=C:/Bitnami/REDMIN~1.0-2/mysql/data
port=3306
character-set-server=UTF8
collation-server=utf8_general_ci
max_allowed_packet=16M
bind-address=127.0.0.1
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB
#================================================================
# InnoDB Paramaters
# Pre requesties
# 4GB or more memory,
# http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html
innodb_buffer_pool_size             = 2G
innodb_log_file_size                = 512M
innodb_thread_concurrency           = 8
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON

# MySQL Server System Variables
# http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
join_buffer_size                    = 128M
sort_buffer_size                    = 1M
query_cache_type                    = 1
query_cache_size                    = 64M
query_cache_limit                   = 2M
tmp_table_size                      = 64M
max_heap_table_size                 = 64M
#================================================================

--------------------------------------------------------------------
5. Subversion　キャッシュ設定, 通信圧縮
--------------------------------------------------------------------
# Apache2.2, 2.4 /etc/httpd/conf.d/subversion.conf
# See, http://svnbook.red-bean.com/en/1.7/svn.ref.mod_dav_svn.conf.html

<IfModule dav_svn_module>
    SVNCompressionLevel 1
    SVNInMemoryCacheSize 128000
    SVNCacheFullTexts on
    SVNCacheTextDeltas on
</IfModule>

--------------------------------------------------------------------
6. Redmine　Subversion同期 ProjectID指定スクリプト
--------------------------------------------------------------------
Subversion Post-Commit-Hook  -  post-commit

#!/bin/sh
# Redmine のプロジェクト識別子（project identifier）とSubversionリポジトリ名称が完全一致している条件下でのスクリプトです。
# 上記２種の名称が一致しない場合、本スクリプト中にプロジェクト識別子を指定することも可能です。（数が多いと面倒なのでお勧めしません）

export LANG="ja_JP.UTF8"

REPOS="$1"
REV="$2"

# Send E-Mail
${REPOS}/hooks/commit-email.rb "$REPOS" "$REV"

# Sync SVN Repo with ITS
REPOSHOME=`/usr/bin/dirname $REPOS`
APIKEY=`/bin/cat $REPOSHOME/common/APIKEY`
PJNAME=`/bin/basename $REPOS`
URL="http://localhost/its/sys/fetch_changesets?id=$PJNAME&key=$APIKEY"
/usr/bin/wget -q --no-proxy $URL -O /dev/null

--------------------------------------------------------------------
7. Redmine　OOBGC for Unicorn
--------------------------------------------------------------------
Unicornへのgctools導入

（参考）
　  http://tmm1.net/ruby21-oobgc/
　  http://qiita.com/jemiam/items/5c6e4595f3565f3c5562

　  Cookpad さんでは2016年1月末からgctoolsの利用を止める模様
　　http://k0kubun.hatenablog.com/entry/2016/01/23/231213

　　一方で、GitHub StaffのCharlie Somerville (@chariesome)
　　は gctoolsのruby2.2+パッチを作ってプルリクを通し、
　　gctoolsは2016/2/11に0.2.4へアップデートされた。

　  Aman Gupta @tmm1 gctools for Ruby 2.1 (GitHub)
　  https://github.com/tmm1/gctools  # For Unicorn Ruby2.1, 2.2+

--------------------------------------------------------------------
8. Redmine　OOBGC for Passenger
--------------------------------------------------------------------
Passengerへの gctools導入

■About gctools
  Aman Gupta @tmm1 gctools for Ruby 2.1 (GitHub)
  http://tmm1.net/ruby21-oobgc/

  Passenger (Hongli Lai on)
  http://old.blog.phusion.nl/2014/01/31/phusion-passenger-now-supports-the-new-ruby-2-1-out-of-band-gc/

■ gctool gemのローカルビルド＆インストール (For Passenger)

  cd /usr/local/src
  git clone https://github.com/shirosaki/gctools  # For Ruby2.1
  # または
  git clone https://github.com/akahane92/gctools  # For Ruby2.1, 2.2, 2.3

  gem build gctools.gemspec
  gem install gctools-0.2.3.gem

■Passengerとgctools を Gemfile.localに追記

  gem 'passenger'
  gem "gctools", '0.2.3'

■config.ru パッチ

  diff --git config.ru config.ru
  index 2a89752..e3f49e6 100644
  --- config.ru
  +++ config.ru
  @@ -1,4 +1,28 @@
   # This file is used by Rack-based servers to start the application.

   require ::File.expand_path('../config/environment',  __FILE__)
  -run RedmineApp::Application
  +
  +# ==================================================
  +if defined?(PhusionPassenger)
  +  require "gctools/oobgc"
  +  GC::OOB.setup
  +  PhusionPassenger.require_passenger_lib 'rack/out_of_band_gc'
  +  use PhusionPassenger::Rack::OutOfBandGc, :strategy => :gctools_oobgc, frequency: 5
  +
  +  PhusionPassenger.on_event(:oob_work) do
  +    # Phusion Passenger has told us that we're ready to perform OOB work.
  +    t0 = Time.now
  +    GC.start
  +    Rails.logger.info "Out-Of-Bound GC finished in #{Time.now - t0} sec"
  +  end
  +end
  +# ==================================================
  +
  +#if ENV['RAILS_RELATIVE_URL_ROOT']
  +#  map ENV['RAILS_RELATIVE_URL_ROOT'] do
  +#    run RedmineApp::Application
  +#  end
  +#else
  +  run RedmineApp::Application
  +#end

■ /etc/httpd/conf.d/passenger.conf にOOBGC用の設定値を指定

# For OOBGC < Experimental >
PassengerSpawnMethod direct


--------------------------------------------------------------------
9. OS File System : CentOS7
--------------------------------------------------------------------
# ENABLING/DISABLING WRITE BARRIERS 
# See, English https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/7/html/Storage_Administration_Guide/writebarrieronoff.html
# See Japanese https://access.redhat.com/documentation/ja-JP/Red_Hat_Enterprise_Linux/7/html/Storage_Administration_Guide/writebarrieronoff.html
# /etc/fstab

# ENABLING/DISABLING ATIME
# See, English https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/7/html/Performance_Tuning_Guide/chap-Red_Hat_Enterprise_Linux-Performance_Tuning_Guide-Storage_and_File_Systems.html#varl-Red_Hat_Enterprise_Linux-Performance_Tuning_Guide-Considerations_at_mount_time-Access_Time
# See, Japanese https://access.redhat.com/documentation/ja-JP/Red_Hat_Enterprise_Linux/7/html/Global_File_System_2/s1-manage-atimeconf.html

# discard option
# See, English https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html-single/Storage_Administration_Guide/#ch-ssd
# See, Japanese https://access.redhat.com/documentation/ja-JP/Red_Hat_Enterprise_Linux/7/html/Storage_Administration_Guide/ch-ssd.html

# Sample setting:
 /dev/mapper/vg_sms01-lv_root                /     ext4    defaults,nobarrier,noatime,discard  1  1
 UUID=f94716b2-52d4-4e81-9a82-xxxxxxxxxxxx   /boot ext4    defaults,discard  1 2
 /dev/mapper/vg_sms01ex-lv_opt               /opt  xfs     defaults,nobarrier,noatime  1 2
 /dev/mapper/vg_sms01-lv_swap                swap  swap    defaults,nobarrier,discard  0 0


注意：ディスクにアクセスできなくなったり、システム・アプリケーションが正しく動作しなくなる危険性があります。
　　　バックアップを確保したうえで、理解できる方だけ自己責任でチャレンジしてください。
　　　危険性が高いわりに、性能向上は体感しにくいので、専門家以外には推奨しません。
WARNING: These settings may destroy your disk. Leave this, if you don't understand the risks.


--------------------------------------------------------------------
10.計測プログラム
--------------------------------------------------------------------
# bench.rb
# -*- coding: utf-8 -*-

rv = ARGV[0]
cashmode = ARGV[1]

url_list = [
        "/#{rv} ",
        "/#{rv}/projects ",
        "/#{rv}/projects/sscope ",
        "/#{rv}/issues?per_page=200 ",
        "/#{rv}/issues/1 ",
        "/#{rv}/issues/47548 ",
        "/#{rv}/issues/51782 ",
        "/#{rv}/projects/its-issues/wiki "
]

puts "--- Environment ---"
puts `httpd -version`
puts `ruby -v`
puts `gem list --local passenger`
puts `export RAILS_ENV=production; /var/lib/its/#{rv}/script/about`
puts `export RAILS_ENV=production; /var/lib/its/#{rv}/bin/about`
puts `export RAILS_ENV=production; cd /var/lib/its/#{rv}; bundle list`

ti = 5
result = []
url_list.each { |url|
  rate_avg = 0
  ms_avg = 0
  puts "--- #{url} ---"
  ti.times {
    res = `httperf --hog --server=localhost --port=80 --uri=#{url} --num-conns 2 --num-calls 10 2> /dev/null | grep 'Request rate:' | awk '{print$3,$5}' | sed -e 's/(//'`
    num = res.split(' ').map{|s| s.to_f}
    puts "    rate: #{num[0]} req/s  #{num[1]} ms/req"
    rate_avg += num[0]
    ms_avg   += num[1]
    sleep 0.2
  }
  rate_avg = (rate_avg/ti).round(1)
  ms_avg   = (ms_avg/ti).round(1)
  result << [rate_avg, ms_avg]
  puts "    AVG:  #{rate_avg} req/s  #{ms_avg} ms/req"
}

rate_avg_total = (result.transpose[0].inject(:+)).round(1)
ms_avg_total   = (result.transpose[1].inject(:+)).round(1)

puts "-------------------------"
result.size.times { |r|
  puts "#{r+1} \t #{url_list[r]} \t #{result[r][0]} \t #{result[r][1]}"
}
puts "#{cashmode} \t Total \t #{rate_avg_total} \t #{ms_avg_total}"

puts "-------------------------"
result.size.times { |r|
  puts " AVG Rate:  #{result[r][0]} req/s  (#{result[r][1]} ms/req)"
}