Insert buffer performance and srv_master_thread

[Mark Callaghan]

I was examining the performance of an 'idle' system that had a lot of IO activity and high fsync rate (30/sec) on a 1 disk server running MySql.

I learned a bit about the work done by srv_master_thread and the insert buffer (it is very clever and interesting stuff, if you are into database servers).

Most of the fsyncs were done when dirty buffer pool pages were flushed to disk by the background loop (search for the background_loop label in srv0srv.c) in srv_master_thread.

I also noticed that the background loop is biased towards keeping all buffer pool pages clean. It flushes up to 100 pages per iteration in the call to buf_flush_batch, while using ~20 IOs per iteration in the call to ibuf_contract_for_n_pages for insert buffer merges.

I increased the argument in the ibuf_contract_for_n_pages from 20 to 100, and the time to merge all entries from the insert buffer on an otherwise idle system decreased by a factor of 2, the number of fsync calls dropped by a factor of 4 and the number of writes done decreased by 1/3. The time to do the insert (200,000 single-row insert statements, each in a separate transaction, with 10 indexes on the table) did not change.

Does anyone know the justification for the current values? I am quite fond of the better performance with the new value for ibuf_contract_for_n_pages, and am trying to figure out what problems this might cause. It will leave the buffer pool with more dirty pages if the idle loop is interrupted by system activity. It will also make the idle loop less responsive to a new query because more IOs have been issued to the insert buffer thread per iteration.