boost performance for bulk insert operation into large table.

I have encountered some performance issues while migrating data from SSD pure Linux environment (no virtualization) into hybrid paravirtualized multi-tier stuff.
I used docker containers and bCache hybrid storage. Frontend consists of NVMe (fusionIO) 400G RAID1 and backend is EMC VNX5200 SAS RAID6.
In my case the process of mass inserts was not stable – it took from 0.5s per bunch to 10s or even more.

To be sure that the performance of storage is accurate I created the container which used RAM disk – it was my comparison matrix.
For the period of migration I changed the most obvious parameters in mysql server:

innodb_flush_method=O_DIRECT
innodb_change_buffering=inserts
innodb_doublewrite=OFF
innodb_flush_log_at_trx_commit=0
binlog-do-db=mysql # do not log any other db.
innodb_adaptive_hash_index=ON

It allowed to improve the performance only a bit, the main problem remained. I boosted CPU weight for the container as well as limited the number of threads used by container:

docker run -c=2048  -cpuset-cpus="0,1,2,3"

Thanks to the a/m settings I realized that, because mysldump create chunks of inserts, practically each chunk was executed by other CPU thread. The clue was the CPU governor. I revealed that new server used “powersave” governor so I could observe some frequency fluctuation between the chosen (four) threads. How it worked? The first chunk was executed by processor 0, at the start of the execution it was “cold” so was boosted to maximum available frequency, then next chunk hited another “cold” processor and so on. The more partitions were in the table the more it was unstable. As the bCache uses CPU for its IO operation it was the main bottleneck. My matrix with RAM disk did not suffer so much when the governor of CPU was not set to performance.

The performance increased from ~80k of rows inserts per second to avr 250k (sometime it was even 400k).

cat /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor

Now I use the a/m script (centOS) to manage the CPU governor

#!/bin/bash

RED='\033[0;31m'
NC='\e[0m'

function set_governor {

local governor
governor=$1

for CPUFREQ in /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor
do
        [ -f $CPUFREQ ] || continue
        echo -n "$governor" > $CPUFREQ
done

}

case "$1" in
    (performance | powersave)
        set_governor "$1"
        ;;
    *)
        printf "${RED}Choose mode from whithin:${NC}\n"
        cat /sys/devices/system/cpu/cpu0/cpufreq/scaling_available_governors
        exit 3
        ;;

esac
  1. No comments yet.

  1. No trackbacks yet.