iiBench configuration

December 5, 2008

Author

kuszmaul

MySQL

Share this Post:

A tip of the hat to Mark Callaghan, who suggested I post our my.cnf settings for iiBench.

Instead of fiddling around with the configuration file, we adjusted everything on the command line. Here’s the relevant script from iiBench/scripts/start_mysql.sh:

<br>#<br># Script to start the mysqld process.<br># - Documents mysqld parameters used for iiBench testing.<br>#<br>DATADIR="/usr/local/mysql/data"<br>MYSQL_PATH="/usr/local/mysql/bin"<br><br># These parameters were selected for a test machine with:<br># - 16GB of memory<br># - 2 socket, quad core = 8 cores total.<br><br>PARAM=" <br>--no-defaults <br>--user=mysql  <br>--datadir=$DATADIR <br>--max_connections=3000 <br>--max_connect_errors=10 <br>--table_cache=2048 <br>--max_allowed_packet=1M <br>--binlog_cache_size=1M <br>--max_heap_table_size=64M <br>--sort_buffer_size=64M <br>--read_buffer_size=64M <br>--join_buffer_size=64M <br>--thread_cache=16 <br>--thread_concurrency=16 <br>--thread_stack=196K <br>--query_cache_size=0 <br>--ft_min_word_len=4 <br>--default_table_type=MYISAM <br>--transaction_isolation=REPEATABLE-READ <br>--tmp_table_size=64M <br>--skip-locking <br>--innodb_file_per_table <br>--innodb_data_file_path=ibdata1:256M:autoextend <br>--innodb_buffer_pool_size=3072M <br>--innodb_additional_mem_pool_size=2M <br>--innodb_log_file_size=768M <br>--innodb_log_buffer_size=4M <br>--innodb_flush_log_at_trx_commit=2 <br>--innodb_thread_concurrency=8 <br>--innodb_flush_method=O_DIRECT <br>--innodb_lock_wait_timeout=50 <br>--key_buffer_size=8G <br>--myisam_sort_buffer_size=2G <br>--tmpdir=/tmp/mysql_tmp <br>--myisam_use_mmap=1"<br><br>$MYSQL_PATH/mysqld_safe $PARAM &amp;<br>

1

# # Script to start the mysqld process. # - Documents mysqld parameters used for iiBench testing. # DATADIR="/usr/local/mysql/data" MYSQL_PATH="/usr/local/mysql/bin" # These parameters were selected for a test machine with: # - 16GB of memory # - 2 socket, quad core = 8 cores total. PARAM=" --no-defaults --user=mysql --datadir=$DATADIR --max_connections=3000 --max_connect_errors=10 --table_cache=2048 --max_allowed_packet=1M --binlog_cache_size=1M --max_heap_table_size=64M --sort_buffer_size=64M --read_buffer_size=64M --join_buffer_size=64M --thread_cache=16 --thread_concurrency=16 --thread_stack=196K --query_cache_size=0 --ft_min_word_len=4 --default_table_type=MYISAM --transaction_isolation=REPEATABLE-READ --tmp_table_size=64M --skip-locking --innodb_file_per_table --innodb_data_file_path=ibdata1:256M:autoextend --innodb_buffer_pool_size=3072M --innodb_additional_mem_pool_size=2M --innodb_log_file_size=768M --innodb_log_buffer_size=4M --innodb_flush_log_at_trx_commit=2 --innodb_thread_concurrency=8 --innodb_flush_method=O_DIRECT --innodb_lock_wait_timeout=50 --key_buffer_size=8G --myisam_sort_buffer_size=2G --tmpdir=/tmp/mysql_tmp --myisam_use_mmap=1" $MYSQL_PATH/mysqld_safe $PARAM &