I have encountered some performance issues while migrating data from SSD pure Linux environment (no virtualization) into hybrid paravirtualized multi-tier stuff.
\nI used docker containers and bCache hybrid storage. Frontend consists of NVMe (fusionIO) 400G RAID1 and backend is EMC VNX5200 SAS RAID6.
\nIn my case the process of mass inserts was not stable – it took from 0.5s per bunch to 10s or even more.<\/p>\n
To be sure that the performance of storage is accurate I created the container which used RAM disk – it was my comparison matrix.
\nFor the period of migration I changed the most obvious parameters in mysql server:<\/p>\n
innodb_flush_method=O_DIRECT
\ninnodb_change_buffering=inserts
\ninnodb_doublewrite=OFF
\ninnodb_flush_log_at_trx_commit=0
\nbinlog-do-db=mysql # do not log any other db.
\ninnodb_adaptive_hash_index=ON<\/p>\n
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:<\/p>\n
\r\ndocker run -c<\/span>=2048 -cpuset-cpus<\/span>=\"0,1,2,3\"\r\n<\/pre>\nThanks 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. <\/p>\n
The performance increased from ~80k of rows inserts per second to avr 250k (sometime it was even 400k). <\/p>\n
\r\ncat \/sys\/devices\/system\/cpu\/cpu*\/cpufreq\/scaling_governor\r\n<\/pre>\nNow I use the a\/m script (centOS) to manage the CPU governor<\/p>\n
\r\n#!\/bin\/bash\r\n\r\nRED='\\033[0;31m'\r\nNC='\\e[0m'\r\n\r\nfunction set_governor {\r\n\r\nlocal governor\r\ngovernor=$1\r\n\r\nfor CPUFREQ in \/sys\/devices\/system\/cpu\/cpu*\/cpufreq\/scaling_governor\r\ndo\r\n [ -f $CPUFREQ ] || continue\r\n echo -n \"$governor\" > $CPUFREQ\r\ndone\r\n\r\n}\r\n\r\ncase \"$1\" in\r\n (performance | powersave)\r\n set_governor \"$1\"\r\n ;;\r\n *)\r\n printf \"${RED}Choose mode from whithin:${NC}\\n\"\r\n cat \/sys\/devices\/system\/cpu\/cpu0\/cpufreq\/scaling_available_governors\r\n exit 3\r\n ;;\r\n\r\nesac\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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 – […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/1154"}],"collection":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1154"}],"version-history":[{"count":17,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/1154\/revisions"}],"predecessor-version":[{"id":1171,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/1154\/revisions\/1171"}],"wp:attachment":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1154"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1154"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1154"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}