buffer - Mysql threads stuck in 'query end', how to prevent furious flushing -

mysql became unresponsive many simple update , insert threads stuck in 'query end' state.

---transaction f528f961, active (prepared) 858 sec  mysql tables in use 1, locked 1  2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1  mysql thread id 82683520, os thread handle 0x7f73a6925700, query id 14714499253 192.168.1.22 wms query end update  `users`     set `id` = '6016', `es_id` = '4817', `department_id` = '4',         `schedule_id` = '1', `username` = 'john.doe',         `user_role` = 'guest,admin,picker',         `status` = '1', `team` = '2', `email` = null,         `wms_user` = '1', `logged_in_time` = '2016-02-01 07:06:45',         `last_activity` = '2016-02-01 13:07:49',         `session_id` = 'qbei0rrfiu05l9olcckh6sg976'      (id = 6016)  

cpu load went up, disk io went up, hit ratio went down. cpu load / disk io "use db" , "show master status" threads showed in slow log.

from figure 'furious flushing'. user ran large select statement through applcation. select inner joins 12 innodb tables have sum(data_length + index_length) = 11.2gand sorts results. thing not unusual query. runs smaller working set:

# query_time: 1.737293 lock_time: 0.000027 rows_sent: 7051 rows_examined: 1109050

this time user wanted data past 2 months, lead to:

# query_time: 370.063806 lock_time: 0.000039 rows_sent: 919 rows_examined: 27994638

from engine innodb status:

main thread process no. 24701, id 140134828910336, state: flushing buffer pool pages 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 894332.73 reads/s

server runs debian 6.0.4, mysql 5.5.31 community edition, 32 core cpu @ 2.60ghz / 64gb ram / ssd

my.cnf:

innodb_buffer_pool_size =  40g  innodb_log_file_size = 512m innodb_log_buffer_size = 16m innodb_flush_log_at_trx_commit = 1 innodb_thead_concurrency = 0 ## modified 32 after crash innodb_read_io_threads  = 4 innodb_write_io_threads = 4  innodb_old_blocks_pct = 37  innodb_flush_method=null ## change o_direct needs restart innodb_old_blocks_time = 0 ## modified 1000 after crash 

this isolated case, want know how can prevent in future. please offer input. thanks.

build summary tables users. will

• keep selects slowing down updates.
• make selects run 10x faster.
• decrease overall system usage.

more discussion.