We have an application that needs to be dumping rows into some tables at a much faster rate than it currently is. It is a dual processor xeon DB server with 48gb of ram and a 4 TB disk RAID 10 (for a total of 3.63TB) running freebsd with mysql 5.6.
We have tried what feels like everything... Using MYIsAM and Innodb, and we've tried many combinations of serving settings with many of the obvious variables.
The application is a multithreaded C client using a seperate connection per thread. The queries are about 65% selects and 35% inserts... all of the queries are relatively trivial (and they are only inserts and selects) and when ran individualy they all return in < 0.01 seconds.
My hunch is that concurrency is not being achieved for reason, despite adjusting every related server variable. This is my guess because we are getting the same level of data tranfer/inserts/total queries per second with 5 threads, as we are with 300 hundred threads. SHOW PROCESSLIST shows threads as sleeping between 0 < 30 seconds...the more threads we have running the more threads are sleeping for longer. We've audited these sleeping threads with truss (freebsd strace) and they are freezing on reads and polling quite a bit.
We've made what seems like very small gains in performance but even those have only been for those first few threads. We are currently using InnoDB, in which (to my understanding) all of are queries should be either concurrent or only row locking.
I've read some horror stories about freebsd with mysql nearly a decade ago but I assumed that was a thing of the past and decided to go with it anyways as I've preferred freeebsd for most other applications. In any event this is just ridiculous. I've searched here extensively and I've tried everything I've found.
The cpu is not working very hard at all and neither is the raid i/o. Why are we not getting concurrent thread performance?
here is the my.cnf as it stands right now (just one of many attempted versions) innodb_buffer_pool_size = 24576M innodb_flush_log_at_trx_commit = 0 thread_concurrency = 256 innodb_thread_concurrency = 256 innodb_locks_unsafe_for_binlog = 1
max_connections = 8192 key_buffer_size = 14495514624 read_buffer_size = 8M thread_cache = 128 innodb_read_io_threads = 64 innodb_write_io_threads = 64 innodb_io_capacity = 1000 table_open_cache = 4000 table_open_cache_instances = 16 ft_min_word_len = 1 concurrent_insert = 2 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES