Comments on my.cnf for high insert volume db?

MySQL & MariaDB Other MySQL® Questions
1

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

2

The thread_concurrency setting doesn’t really do anything except hinting to solaris how many threads that you are going to use. So you can basically forget about this setting with good consciens.

And as for the:
innodb_thread_concurrency
innodb_commit_concurrency
I would leave them as default to begin with and then run som test and check the SHOW ENGINE INNODB STATUS to see if you possibly have any threads waiting for semaphores before changing it.

InnoDB table spaces should be large to begin with.
Or change the innodb_autoextend_increment to be much bigger than the default 8MB so that you avoid that it has to auto increase the table space all the time.

The only other thing I can think of if you really want top performance is to disable the InnoDB double write buffer.
As an estimation it will give you about 10% increase in performance.
But then you will have to be willing to sacrifice robustness in case of a crash.
And normally people aren’t willing to do this for that little gain unless you are running some performance test in a lab environment.

Apart from that I think that your settings look good.

And for the record I don’t think that it’s stupid to do this on MySQL. :wink: