Comments on my.cnf for high insert volume db

[Amir Elaguizy]

So we're looking to start testing our configuration for a new project that is essentially an ultra high insert volume database. We had an extremely thin budget for the project, so we had to go with MySQL. We're dealing with a lot of data (somewhere in the range of 750Gb size on disk increase per year, with the potential of doubling after a year).

We've never really had to tune a MySQL server like this before, where performance was such a large concern. Before we'd just adjust a few simple things and leave it at that.

We're satisfied that we can hit the insert volume we need, and are now working on tuning things to get every last bit of performance before we start a series of tests we expect to take 3 weeks that will test every aspect of the system under every scenario we could concoct.

The hardware is:

2x
Dell PowerEdge 1950
2x Quad Core 2.66 Ghz Xeon
16Gb RAM
4x 10k RPM 73GB drives (raid-10)


2x
Dell PowerVault MD1000
15 750Gb SATA drives in a raid-10


We have each 1950 connected to a PowerVault, in a master-slave replication scheme.

Currently the PowerVaults have 2x2TB partitions for data and 1x2TB partition for binary logs. This is configured in a single raid 10 virtual disk.

The work load is VERY low connections (4-16 realistically) but extremely high insert volume. All inserts are happening locally over a Gigabit link. All of our tables are InnoDB and transactions are a requirement. We have no referential integrity because FKs were too slow/big on disk. Also some of our tables are (necessarily) extremely wide, one table in particular contains 300 columns (this is a denormalized table that contains data that absolutely must be instantaneously selected and is directly index by a single unique integer key).

If there is ANY sort of information that anyone would like to know to help them comment intelligently, please ask!

I am appending our my.cnf to the end of this post and would really appreciate any comments, as we've never had to tune a MySQL config that had any sort of real volume. FYI - We have already been told that it is stupid to do this on MySQL, so if that is your comment - it is not necessary, we know Smile

Specific questions:
Thread Concurrency, too low?
We have not specified the innodb_commit_concurrency or the innodb_thread_concurrency, recommendations?
Starting with gigantic innodb data files, or autoextending?
Binary log file size, too small, too big?
Innodb log file size, log buffer size comments?

[mysqld]
max_connections = 40
skip-locking
key_buffer = 256M
max_allowed_packet = 32M
table_cache = 512
sort_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
thread_cache_size = 8
query_cache_size= 32M
thread_concurrency = 16

tmp_table_size=1000MB
max_heap_table_size=1000MB

log-bin=/pv/p4/mysql/logs/mysql-bin.log

expire-logs-days = 20
max_binlog_size = 504857600

innodb_data_home_dir = /
innodb_data_file_path = /pv/p1/mysql/data/ibdata1:2000000M;/pv/p2/mysql/data/ibdata1:2000000M
innodb_log_group_home_dir = /pv/p4/mysql/innodb_logs
innodb_log_arch_dir = /pv/p4/mysql/innodb_logs

innodb_buffer_pool_size = 14000M
innodb_additional_mem_pool_size = 20M

innodb_log_file_size = 500M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 20