mysql | ぼくの答えはいつもNo…そしてYes！

mysqlがやたら重くなっていて、テーブル表示とかは問題ないのに普通のSELECTでも３０秒くらいかかるようになっていました。

ビューテーブルとか使いまくっていたので最初そのせいかと思って調査を始めたのですがEXPLAINですら相当重い・・・。

mysql> SHOW PROCESSLIST;

1	mysql> SHOW PROCESSLIST;

をすると、大量にSELECTやらUPDATEやらがLOCK状態になっていました。こういうこともあるんですね。１５年ほど使ってて初めて。

mysql> SELECT GROUP_CONCAT(id) FROM information_schema.PROCESSLIST WHERE TIME > 10;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(id)                                                                                                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 489757,489681,489654,489613,489552,489472,489395,489251,489169,489056,488965,488874,488804,488731,488641,488547,488456,488377,488322,488232,488138,488076,487985,487908,487842,487776,487718,487651 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.33 sec)
mysql> exit;
$ mysqladmin kill 489757,489681,489654,489613,489552,489472,489395,489251,489169,489056,488965,488874,488804,488731,488641,488547,488456,488377,488322,488232,488138,488076,487985,487908,487842,487776,487718,487651 -u root

mysql> SELECT GROUP_CONCAT(id) FROM information_schema.PROCESSLIST WHERE TIME > 10;

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| GROUP_CONCAT(id) |

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| 489757,489681,489654,489613,489552,489472,489395,489251,489169,489056,488965,488874,488804,488731,488641,488547,488456,488377,488322,488232,488138,488076,487985,487908,487842,487776,487718,487651 |

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.33 sec)

mysql> exit;

$ mysqladmin kill 489757,489681,489654,489613,489552,489472,489395,489251,489169,489056,488965,488874,488804,488731,488641,488547,488456,488377,488322,488232,488138,488076,487985,487908,487842,487776,487718,487651 -u root

でなんとかなりました。

以下のブログが大変参考になりました。

[blogcard url=”http://d.hatena.ne.jp/takami_hiroki/20101027/p1″]

[blogcard url=”http://tech.basicinc.jp/MySQL/2014/04/06/mysql_processlist_kill/”]

何もオプションを付けずにmysqldumpすると

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `users`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `users` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`username` varchar(50) DEFAULT NULL,

`password` varchar(50) DEFAULT NULL,

`created` datetime DEFAULT NULL,

`modified` datetime DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

となります。AUTO_INCREMENTの初期値が引き継がれてしまっていて邪魔ですね。

mysqldump --skip-opt -u hoge -p hoge_db

1	mysqldump --skip-opt -u hoge -p hoge_db

とすればすっきりしたdumpが吐出されます。

/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL,
`username` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
);
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `users` (

`id` int(10) unsigned NOT NULL,

`username` varchar(50) DEFAULT NULL,

`password` varchar(50) DEFAULT NULL,

`created` datetime DEFAULT NULL,

`modified` datetime DEFAULT NULL,

PRIMARY KEY (`id`)

);

/*!40101 SET character_set_client = @saved_cs_client */;

でもDBエンジンとか文字エンコーディングとかが抜けてると、使い物にならないですね。そこでこうするらしいです。

mysqldump --opt --single-transaction -u hoge -p -d hoge_db | sed 's/ AUTO_INCREMENT=[0-9]*\b//'

1	mysqldump --opt --single-transaction -u hoge -p -d hoge_db \| sed 's/ AUTO_INCREMENT=[0-9]*\b//'

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `users`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `users` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`username` varchar(50) DEFAULT NULL,

`password` varchar(50) DEFAULT NULL,

`created` datetime DEFAULT NULL,

`modified` datetime DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

sedで文字列変換なんて、なかなかの力技！

参考

stackoverflow – mysqldump – Export structure only without autoincrement

MySQL Documentation 7.11. mysqldump — データベースバックアッププログラム

ぼくの答えはいつもNo…そしてYes！

愛媛県南予在住現在フリーのウェブプログラマです。お仕事募集中です。

「mysql」カテゴリーアーカイブ

mysqlがEXPLAINですら重い時に

mysqldumpでAUTO_INCREMENTの初期値をはずしたい

参考