Saminnet-Search Article Wiki Forum Blog SNS Cloud
MySQL Too Many Connection

  • Data-Articles
    • Slower CKD stage (3) Wed12,16:41pm

      Having your kidneys work 窶 even a little 窶 can help you feel better and live longer. If you can slow your CKD, you can delay the need for treatment of kidney failure. The types of changes you might make to help your heart or the rest of your body will help your kidneys, too. Here are some things you can do 窶 or avoid 窶 to protect your kidneys: r Blood Sugar In The Target Range.…

      Read More...

MySQL Too Many Connection

縺昴b縺昴bToo many connections縺ィ縺ッシ

  • 縲悟商縺謗・邯壹r蛻譁ュ縺励↑縺髯舌j縲√%繧御サ・荳頑眠隕上ョ謗・邯壹′縺ァ縺阪∪縺帙s縲阪→MySQL縺瑚ィ縺」縺ヲ縺繧
  • 縺薙%縺ァ縺ョ縲梧磁邯壹阪ョ螳夂セゥ縺ッ縲瑚ェ崎ィシ髢句ァ句セ鯉ス柢uit縲阪→諤昴o繧後k
    • Too many connections縺瑚ェ崎ィシ蜃ヲ逅縺ョ蠕後↓蜃コ蜉帙&繧後◆縺溘a
    • quit縺ッ謗ィ貂ャ

髢「騾」繝代Λ繝。繝シ繧ソ

  • 謗・邯壹°繧牙譁ュ縺セ縺ァ縲∝ィ縺ヲ縺ョ蜃ヲ逅縺梧ュ」蟶ク縺ォ騾イ繧蜑肴署縺ェ繧峨∽サ・荳九′髢「菫ゅ@縺昴≧
    • 縺ゥ繧後□縺代ョ鬆サ蠎ヲ縺ァ譁ー隕乗磁邯壹′陦後o繧後k縺
    • 縺ゥ繧後□縺代ョ譎る俣謗・邯壹&繧後※縺繧九°
      • 謗・邯壼セ後↓陦後o繧後k蜃ヲ逅縺ッ縺ゥ繧後□縺代ョ險育ョ鈴上°
      • 蜃ヲ逅繧定。後≧險育ョ玲ゥ溘ッ縺ゥ繧後□縺代ョ諤ァ閭ス繧呈戟縺」縺ヲ縺繧九°
  • 蜃ヲ逅縺梧ュ」蟶ク縺ォ邨ゆコ縺励↑縺蝣エ蜷医ッ莉・荳九ョ2繝代ち繝シ繝ウ縺ョ蜃ヲ逅縺瑚オキ縺薙j縺昴≧
    • 逡ー蟶ク繧呈、懃衍縺励※蠑キ蛻カ逧縺ォ蛻譁ュ縺吶k
    • 縺壹▲縺ィ謗・邯壹′谿九j邯壹¢繧
  • MySQL縺ォ縺ッ荳願ィ倥r閠諷ョ縺励◆謖ッ繧玖槭>繧呈悄蠕縺励◆縺上↑繧
  • 縺昴ョ縺溘a縺ョMySQL縺ョ險ュ螳壼、縺ッ莉・荳九ョ2縺、
    • max_connections
    • wait_timeout

縺ァ縲∫オ仙ア縺ゥ縺縺吶l縺ー縺縺縺ョ繧

莉・荳九r逍代>縺セ縺励g縺縲

  • 縺吶∋縺ヲ縺ョ蜃ヲ逅縺ッ豁」蟶ク縺ォ陦後o繧後※縺繧九′縲∬イ闕キ繧謗・邯夐サ蠎ヲ縺ョ荳頑縺ォ繧医▲縺ヲ蜷梧凾謗・邯壽焚縺悟「励∴縺ヲ縺励∪縺」縺ヲ縺繧具シ
  • 迚ケ螳壹ョ繧ッ繧ィ繝ェ縺碁撼蟶ク縺ォ驕縺九▲縺溘j縺励※縲√⊇縺九ョ蜃ヲ逅繧帝仆螳ウ縺励※縺繧具シ

縺セ縺壹ッ迴セ迥カ謚頑升縺九i縲

  • 閻舌▲縺ヲ縺繧区磁邯壹′辟。縺縺具シ
mysql> show processlist;
+------+--------+-------------+-------+-------------+--------+-------+------------------+
| Id   | User   | Host        | db    | Command     | Time   | State | Info             |
+------+--------+-------------+-------+-------------+--------+-------+------------------+
|   66 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |    186 |       | NULL             |
|   67 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |    186 |       | NULL             |
| 2450 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |    428 |       | NULL             |
| 2451 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |    428 |       | NULL             |
| 4725 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |     46 |       | NULL             |
| 6194 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |      7 |       | NULL             |
| 6213 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |      2 |       | NULL             |
| 6215 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |      7 |       | NULL             |
| 6216 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |      7 |       | NULL             |
| 6217 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |    405 |       | NULL             |
| 6218 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |      7 |       | NULL             |
| 6219 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |      7 |       | NULL             |
| 6221 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |      7 |       | NULL             |
| 6222 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |      7 |       | NULL             |
| 6225 | xxxxxx | xxxxx:xxxxx | NULL  | Query       |      0 | NULL  | show processlist |
| 6226 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |      7 |       | NULL             |
| 6237 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |      7 |       | NULL             |
| 6238 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep       |      7 |       | NULL             |
+------+--------+-------------+-------+-------------+--------+-------+------------------+
18 rows in set (0.00 sec)

mysql>

Time縺檎焚蟶ク縺ォ髟キ縺繧ゅョ縺ィ縺九≠繧後ー縲√◎縺ョId繧呈ョコ縺吶→貊樒蕗縺瑚ァ」豸医☆繧九°繧ゅ@繧後∪縺帙s縲 閾ェ蛻縺碁ュ驕縺励◆迥カ豕√〒縺ッ縲,ommand縺ォ縲郡ending Data縲阪→縺縺Id縺悟ュ伜惠縺励 縺昴l莉・螟悶ッ縲鍬ock縲阪→縺縺迥カ諷九ョ繧ゅョ縺悟、壹¥蟄伜惠縺励※縺繧狗憾豕√〒縲 Sending Data縺ョId繧談ill縺励◆繧牙セゥ豢サ縺励◆縺薙→縺後≠繧翫∪縺励◆縲

mysql> kill 6031;
Query OK, 0 rows affected (0.00 sec)

mysql>

縺薙s縺ェ諢溘§縲

荳願ィ倥ッ蜃ヲ逅縺梧ュ」蟶ク邨ゆコ縺励※縺縺ェ縺繧ア繝シ繧ケ縺ァ縺励◆縺後∝ィ驛ィ豁」蟶ク縺縺ィ縲∵怙螟ァ蜷梧凾謗・邯壽焚縺ョ險ュ螳壹′ 縺昴b縺昴b雜ウ繧翫※縺縺ェ縺九▲縺溘j縲繧ソ繧、繝繧「繧ヲ繝譎る俣縺碁聞縺吶℃繧九ョ縺悟次蝗縺九b縺励l縺セ縺帙s縲

莉・荳九〒迴セ迥カ縺ョ險ュ螳壹r謚頑升縺励∪縺吶

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>
mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

蜷梧凾謗・邯壽焚縺ッ險育ョ鈴上→諤ァ閭ス繧定ヲ九↑縺後i豎コ繧√k縺薙→縺ォ縺ェ繧九ョ縺九↑縲 wait_timeout縺ッ遘貞腰菴阪〒縲√ョ繝輔か繝ォ繝医□縺ィ28800(8譎る俣)縺ィ縺ョ縺薙→縲つ繧ソ繧、繝繧「繧ヲ繝縺後い繧、繝峨Ν繧ソ繧、繝繧「繧ヲ繝縺ェ縺ョ縺区磁邯夐幕蟋九°繧画磁邯夂オゆコ縺セ縺ァ縺ョ譎る俣縺ェ縺ョ縺九′荳肴弱 縺縺代←8譎る俣縺ッ髟キ縺吶℃繧九√→蛻、譁ュ縺ァ縺阪k讖滉シ壹ッ螟壹◎縺縲ゆサ願ェ蛻縺碁擇蛟偵r隕九※縺繧九す繧ケ繝繝縺ァ繧ゅ◎縺縲

險ュ螳壼、画峩シ医ョ蜑阪ョ豕ィ諢丈コ矩シ

險育ョ玲ゥ溘ョ險ュ螳壹r螟画峩縺吶k縺ィ縺阪ッ莉・荳九↓豕ィ諢上☆繧句ソ隕√′縺ゅk縲

  • 迴セ迥カ縺ョ蜍穂ス懊r螟画峩縺吶k
    • 莉翫☆縺舌☆縺ケ縺ヲ縺ォ螟画峩縺悟渚譏縺輔l繧九ョ縺具シ滂シ井セ九∴縺ー縲梧里縺ォ謗・邯壹&繧後※縺繧区磁邯壹ョwait_timeout繧ょ、画峩縺輔l繧九ョ縺具シ溘阪→縺縺逍大撫シ
    • 縺吶∋縺ヲ縺ァ縺ッ縺ェ縺上√%繧後°繧臥匱逕溘☆繧九う繝吶Φ繝医↓蟇セ縺励※縺具シ滂シ井セ九∴縺ー縲後%繧後°繧画磁邯壹&繧後k謗・邯壹ョwait_timeout縺縺代′螟画峩縺輔l繧九ョ縺具シ溘阪→縺縺逍大撫シ
  • 谺。蝗櫁オキ蜍墓凾縺ョ蜍穂ス懊r螟画峩縺吶k
    • 蜀崎オキ蜍募セ後√←縺ョ繧医≧縺ェ蜍穂ス懊→縺ェ繧九°シ

蜑崎縺ッ蜍穂ス應クュ縺ョ繝励Ο繧サ繧ケ縺ォ蟇セ縺励※縺ェ繧薙i縺句多莉、縺吶k縺薙→縺ァ蜍穂ス懊r螟画峩縺輔○繧九√→縺縺險ュ險医ョ繧キ繧ケ繝繝縺悟、壹>縲 蠕瑚縺ッ襍キ蜍墓凾縺ォ隱ュ縺ソ霎シ縺セ繧後k險ュ螳壹ヵ繧。繧、繝ォ繧貞、画峩縺吶k縲√→縺縺險ュ險医ョ繧キ繧ケ繝繝縺悟、壹>縲

縺ァ縲√ロ繝繝井ク翫ョ縺繧阪s縺ェ諠蝣ア繧定ヲ九※縺繧九→縲√←縺。繧峨°縺励°邏ケ莉九@縺ヲ縺縺ェ縺萓九′螟壹>縲 迴セ迥カ縺ョ蜍穂ス懷、画峩縺縺代d縺」縺ヲ縲∝崎オキ蜍輔@縺滄圀縺ョ蜍輔″繧定諷ョ縺励※縺縺ェ縺繧ゅョ縺ィ縺九 繧ゅ@縺上ッ險ュ螳壹ヵ繧。繧、繝ォ繧貞、画峩縺励※霆ス縲縺励¥OS縺ィ縺九励Ο繧サ繧ケ繧貞崎オキ蜍輔@縺ヲ縺縺溘j縺ィ縺九 讒狗ッ我クュ縺ィ縺九〒豁「繧√k縺薙→縺瑚ィア縺輔l繧九す繧ケ繝繝縺ェ繧峨>縺繧薙〒縺吶′縲∵ュ「繧√i繧後↑縺蝣エ蜷医ッ縺ゥ縺。繧峨b荳∝ッァ縺ォ繧繧峨↑縺代l縺ー縺縺代↑縺縺ァ縺吶ュ縲

險ュ螳壼、画峩

邨占ォ悶ッ莉・荳九

迴セ迥カ縺ョ蜍穂ス懊r螟画峩縺吶k

蠖鍋┯縺ァ縺吶′縲∬ィュ螳壼、縺ッ繧キ繧ケ繝繝縺ョ迚ケ諤ァ繧定縺医※驕ゥ蛻縺ォ豎コ繧√※縺上□縺輔>縺ュ縲

  • 縺セ縺壹ッwait_timeout縺九i
mysql> set global wait_timeout = 3600;
Query OK, 0 rows affected (0.00 sec)

mysql>
  • 谺。縺ォmax_connections
mysql> set global max_connections = 200;
Query OK, 0 rows affected (0.00 sec)

mysql>

螟画峩縺励◆繧峨■繧繧薙→螟画峩縺輔l縺ヲ縺繧九°遒コ隱阪@縺セ縺励g縺縲

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 200   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>
mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 3600  |
+---------------+-------+
1 row in set (0.00 sec)

mysql>
谺。蝗櫁オキ蜍墓凾縺ョ蜍穂ス懊r螟画峩縺吶k

閾ェ蛻縺ョ迺ー蠅縺ョ蝣エ蜷医ッ/etc/my.cnf繧貞、画峩縺吶k縺薙→縺ォ縺ェ繧九∩縺溘>縺縺」縺溘

[mysqld]
(snip)
wait_timeout=3600
max_connections=200

豁「繧√※繧ゅ>縺繧キ繧ケ繝繝縺ェ繧鋭ervice mysqld restart縺ィ縺九〒mysql繧貞崎オキ蜍輔@縺ヲ豁」蟶ク縺ォ蜿肴丐縺輔l繧九°縺ゥ縺縺狗「コ隱阪☆繧九→縺縺縺ィ諤昴>縺セ縺吶 シ繧上°繧翫d縺吶>縺ッCentOS菴ソ縺」縺ヲ縺縺セ縺吶 縺。縺ェ縺ソ縺ォservice mysqld reload縺ァ繧ゅ>縺代k縺九b縺励l縺セ縺帙s縺後√◎繧後〒險ュ螳壼、縺悟ィ驛ィ蜍穂ス懊↓蜿肴丐縺吶k縺九←縺縺九ッ隰弱〒縺吶 豁「繧√※縺ッ縺縺代↑縺繧キ繧ケ繝繝縺ェ繧峨″縺」縺ィ蜷後§繝舌シ繧ク繝ァ繝ウ縺ィ縺九ョ髢狗匱繝サ讀懆ィシ迺ー蠅縺悟ュ伜惠縺吶k縺ッ縺壹ゅ◎縺薙〒繝繧ケ繝医@縺セ縺励g縺縲 豁「繧√※縺ッ縺縺代↑縺繧キ繧ケ繝繝縺ァ髢狗匱繝サ讀懆ィシ迺ー蠅縺檎┌縺縺ェ繧峨√◎繧後ッ繧キ繧ケ繝繝縺ョ驕狗畑菫晏ョ井ス灘宛縺梧ョ句ソオ縺吶℃繧九 莉ョ諠ウ繧オ繝シ繝舌→縺狗ー。蜊倥↓隱ソ驕斐〒縺阪k譏ィ莉翫∵ァ閭ス繧定誠縺ィ縺帙ー縺昴ョ繧医≧縺ェ迺ー蠅縺ッ邁。蜊倥↓謇九↓蜈・繧九 縺昴l縺檎┌縺縺ィ縺縺縺薙→縺ッ窶ヲ縲よ耳縺励※遏・繧九∋縺励

Add comment


Articles by Date

TweetTweet Share on LinkedInShare on LinkedIn Share on Google+Google+ Submit to RedditReddit Publish on WordPress WordPress Send emailSend email