{"id":2176,"date":"2018-10-11T06:40:22","date_gmt":"2018-10-10T21:40:22","guid":{"rendered":"http:\/\/matoken.org\/blog\/?p=2176"},"modified":"2018-10-11T06:40:22","modified_gmt":"2018-10-10T21:40:22","slug":"mysqldump-limit-by-pv","status":"publish","type":"post","link":"https:\/\/matoken.org\/blog\/2018\/10\/11\/mysqldump-limit-by-pv\/","title":{"rendered":"mysqldump \u3092 pv \u3067\u5236\u9650"},"content":{"rendered":"<div id=\"__asciidoctor-preview-1__\" class=\"paragraph\">\n<p>mysqldump \u52d5\u3044\u3066\u308b\u6642\u9593\u306b\u540c\u30b5\u30fc\u30d0\u3067 GNU social \u3068\u304b Nextcloud \u3068\u304b\u306e mysql \u3092\u5229\u7528\u3057\u3066\u3044\u308b\u30a2\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u304c\u91cd\u304f\u3066\u4f7f\u3044\u7269\u306b\u306a\u3089\u306a\u3044\u3067\u3059\uff0enice + ionice \u306f\u6307\u5b9a\u3057\u3066\u3044\u307e\u3059\u304c\u52b9\u3044\u3066\u306a\u3044\u611f\u3058\uff0e<br \/>\ndump \u3057\u305f sql \u3092\u5727\u7e2e\u3057\u3066\u3044\u308b xz \u30b3\u30de\u30f3\u30c9\u304c cpu \u30921 core \u4f7f\u3044\u6f70\u3057\u3066\u3044\u308b\u3088\u3046\u3067\u3059\uff0e\u3053\u306e\u30de\u30b7\u30f3\u306f2 core \u3042\u308b\u306e\u3067\u3059\u304c\uff0c\u3082\u30461\u3064\u306e core \u3082\u305d\u306e\u4ed6\u306e\u51e6\u7406\u3067\u307b\u307c\u4f7f\u3044\u5207\u3063\u3066\u5f85\u3061\u304c\u51fa\u3066\u3044\u308b\u3088\u3046\u306a\u611f\u3058\uff0e<\/p>\n<\/div>\n<div id=\"__asciidoctor-preview-2__\" class=\"listingblock\">\n<div class=\"content\">\n<pre>$ vmstat 1 10\r\nprocs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----\r\n r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st\r\n 2  0 328212 264788 212260 5628192    1    2    35   160    2   15 35  9 44 12  0\r\n 1  1 328212 259136 212264 5628192    0    0     0   191 1497 3132 63  6 19 12  0\r\n 1  1 328212 252492 212264 5628192    0    0     0    88  959 1716 81  6  7  6  0\r\n 2  1 328212 248264 212264 5628196    0    0     0   114  973 1887 85  3  7  5  0\r\n 2  0 328212 242572 212264 5628196    0    0     0    85  972 1967 74  5 15  5  0\r\n 4  0 328212 241664 212100 5624380    0    0     0    97 1106 4226 91  7  2  2  0\r\n 2  1 328212 234268 212100 5624384    0    0     0   140 1013 1815 70  5 19  7  0\r\n 3  0 328212 235760 211944 5620076    0    0     0  2461 1289 4063 94  4  1  0  0\r\n 1  0 328212 229656 211944 5620080    0    0     0   110  953 1685 61  6 18 15  0\r\n 1  0 328212 224616 211952 5620072    0    0     0   151  983 1683 84  2  8  6  0<\/pre>\n<\/div>\n<\/div>\n<div id=\"__asciidoctor-preview-3__\" class=\"paragraph\">\n<p>pv -L \u3067\u5e2f\u57df\u7d5e\u3063\u3066\u307f\u307e\u3057\u305f\uff0e<br \/>\ncpu \u306e\u69d8\u5b50\u3092\u898b\u306a\u304c\u3089\u3060\u3093\u3060\u3093\u7d5e\u3063\u3066\u3044\u3063\u3066 128k \u8fc4\u7d5e\u3063\u3066\u3084\u3063\u3068 xz \u306e cpu 25\u301c40% \u4f4d\u306b\u306a\u308a\u307e\u3057\u305f\uff0e\u3053\u306e\u72b6\u614b\u3060\u3068\u666e\u901a\u306b\u4f7f\u3048\u308b\u611f\u3058\u3067\u3059\uff0e<br \/>\n\u3057\u3070\u3089\u304f\u3053\u308c\u3067\u8a66\u3057\u3066\u307f\u307e\u3059\uff0e<\/p>\n<\/div>\n<div id=\"__asciidoctor-preview-4__\" class=\"listingblock\">\n<div class=\"title\">pv \u3067\u5e2f\u57df\u5236\u9650<\/div>\n<div class=\"content\">\n<pre>$ time sh -c \"nice -n 19 ionice -c 3 mysqldump --defaults-file=\/backup\/micro\/.my-backup.cnf --single-transaction --quick\r\n--all-databases --events | pv -L 128k 2&gt;\/dev\/null | nice -n 19 ionice -c 3 xz -9 &gt; \/dev\/null\"\r\n\r\nreal 127m16.113s\r\nuser 34m30.508s\r\nsys 0m23.428s<\/pre>\n<\/div>\n<\/div>\n<div id=\"__asciidoctor-preview-5__\" class=\"listingblock\">\n<div class=\"title\">cron\u3067\u306e\u5b9f\u884c<\/div>\n<div class=\"content\">\n<pre>$ sudo -u backup crontab -l | grep mysqldump\r\n14 3 * * *     umask 0266 &amp;&amp; nice -n 19 ionice -c 3 \/usr\/bin\/mysqldump --defaults-file=\/mnt\/backup\/micro\/.my-backup.cnf --single-transaction --quick --all-databases --events | pv -L 128k 2&gt;\/dev\/null | nice -n 19 ionice -c 3 \/usr\/bin\/xz -9 &gt; \/mnt\/backup\/micro\/`date +\\%F_\\%T_$$`.sql.xz<\/pre>\n<\/div>\n<\/div>\n<div id=\"__asciidoctor-preview-6__\" class=\"listingblock\">\n<div class=\"title\">\u74b0\u5883<\/div>\n<div class=\"content\">\n<pre>$ dpkg-query -W mysql-client-5.7 pv xz-utils\r\nmysql-client-5.7        5.7.23-0ubuntu0.16.04.1\r\npv      1.6.0-1\r\nxz-utils        5.1.1alpha+20120614-2ubuntu2\r\n$ lsb_release -d\r\nDescription:    Ubuntu 16.04.5 LTS\r\n$ uname -m\r\nx86_64\r\n$ grep -m1 model\\ name \/proc\/cpuinfo\r\nmodel name      : AMD Athlon(tm) II Neo N36L Dual-Core Processor<\/pre>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>mysqldump \u52d5\u3044\u3066\u308b\u6642\u9593\u306b\u540c\u30b5\u30fc\u30d0\u3067 GNU social \u3068\u304b Nextcloud \u3068\u304b\u306e mysql \u3092\u5229\u7528\u3057\u3066\u3044\u308b\u30a2\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u304c\u91cd\u304f\u3066\u4f7f\u3044\u7269\u306b\u306a\u3089\u306a\u3044\u3067\u3059\uff0enice + ionice \u306f\u6307\u5b9a\u3057\u3066\u3044\u307e\u3059\u304c [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"webmentions_disabled_pings":false,"webmentions_disabled":false,"activitypub_content_warning":"","activitypub_content_visibility":"","activitypub_max_image_attachments":4,"activitypub_interaction_policy_quote":"anyone","activitypub_status":"","footnotes":""},"categories":[6,63,152],"tags":[57,423,380,424],"class_list":["post-2176","post","type-post","status-publish","format-standard","hentry","category-linux","category-ubuntu","category-xenial16-04","tag-mysql","tag-mysqldump","tag-pv","tag-xz"],"_links":{"self":[{"href":"https:\/\/matoken.org\/blog\/wp-json\/wp\/v2\/posts\/2176","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/matoken.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/matoken.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/matoken.org\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/matoken.org\/blog\/wp-json\/wp\/v2\/comments?post=2176"}],"version-history":[{"count":0,"href":"https:\/\/matoken.org\/blog\/wp-json\/wp\/v2\/posts\/2176\/revisions"}],"wp:attachment":[{"href":"https:\/\/matoken.org\/blog\/wp-json\/wp\/v2\/media?parent=2176"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/matoken.org\/blog\/wp-json\/wp\/v2\/categories?post=2176"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/matoken.org\/blog\/wp-json\/wp\/v2\/tags?post=2176"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}