iiBench configuration

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:

#
# 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 &
No Tags.

3 Responses to iiBench configuration

  1. I’m curious.

    If this is your my.cnf for your iibench Contest, where you are inserting into a single table, why do you have innodb_buffer_pool_size and key_buffer_size set, splitting up your memory resources. The resultant table in your benchmark is one storage engine or the other.

    Unless you are doing something like a MyISAM table (given it’s 8GB buffer), and then many InnoDB tables for additional seeding (you have plenty of innodb specific settings) I see no understand between your competition requirements and your settings.

    In addition, previous versions of MySQL crashed with a key_buffer_size > 4GB when accessing pointers above 4GB. I’m assuming this is no longer an issue in 5.1

  2. We used a single set of mysql parameters for both InnoDB and MyISAM to avoid stopping and restarting the mysql server. Although the total memory allocated to both engines together exceeds the size of physical memory, we ran only one test at a time. Individually, each engine’s allocation is less than the size of physical memory. Our thinking was that with only one engine running at a time, the working set would fit within physical memory. We ran InnoDB first, and then MyISAM, and each engine ran for at least two days, so the transition from working set to the other probably wasn’t significant. My own expertise is in the underlying algorithms, rather than the specifics of MySQL parameter settings. For the contest, I would expect and encourage people to modify those settings to optimize performance.

  3. Hm,

    If you’re focusing on Inserts why changing so many different parameters which does not anything to do with Inserts.

    Also you would normally want different configurations for MyISAM and Innodb because they would compete for the same memory.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>