Table 6 MySQL Test Configuration (continued)

mysqld Option

Value

Description

join_buffer_size

1M

Buffer size for joins that do not use indices and require a

 

 

full table scan.

thread_cache_size

16

Number of server threads to cache for reuse.

thread_stack

192K

Per-thread stack size. The default is 192K.

query_cache_size

0

Amount of memory allocated for caching query results. The

 

 

value “0” disables query cache.

ft_min_word_len

4

Minimum length of the word to be included in a full-text

 

 

index.

tmp_table_size

64M

Maximum size of in-memory temp tables. Increase for

 

 

GROUP BY.

innodb_data_file_path

ibdata1:100M\

Path to innodb data files and sizes. Increase size from 10MB

 

:autoextend

to 100MB.

innodb_buffer_pool_size

1900M

Number of bytes of the memory buffer to cache data and

 

 

indices.

innodb_additional_mem_pool_size

20M

Number of bytes of memory pool used to store the data

 

 

dictionary information and data structures. The default is

 

 

1M.

innodb_log_file_size

900M

Size of each log file in log group. The default is 5MB.

innodb_flush_log_at_trx_commit

2

Determines how the log buffer is written to the disk. The

 

 

value “2” means data is flushed on every commit, but no

 

 

disk flush occurs. The default is 1.

innodb_locks_unsafe_for_binlog

1

Controls next-key locking in search and index scans. The

 

 

default is 0 (disabled).

innodb_thread_concurrency

1000

Number of threads currently inside the innodb engine. The

 

 

default varies with the MySQL version.

innodb_concurrency_tickets

500

Number of 'free tickets' to allow re-entry into the innodb

 

 

engine.

Innodb_log_buffer_size

8M

The size of buffer InnoDB used in the memory to write the

 

 

log files. Ranges from 1MB to 8MB. Large log buffer saves

 

 

disk I/O operations.

innodb_table_locks

0

Specifies whether InnoDB locks a table internally. The

 

 

default is 1 (enabled).

innodb_support_xa

0

Specifies whether InnoDB supports XA transactions. The

 

 

default is 1 (supported).

innodb_doublewrite

skip-innodb-doublewrite

InnoDB stores all data twice by default. Disable it by using

 

 

the --skip-innodb-doublewrite parameter when starting the

 

 

MySQL server.

innodb_checksums

skip-innodb-checksums

Checksum validation is used on all data pages by default.

 

 

Disable it by using the --skip-innodb-checksums parameter

 

 

when starting the MySQL server.

 

 

 

Benchmark Results

For each read-only or read/write test, SysBench was run with a varying number of thread connections to determine the maximum transactions per second (tps). The tests were conducted on both the internal storage with the battery backed write cache option (BBWC) and the Fibre Channel attached Storage Area Network.

Benchmark Results 15