「MySQL」タグアーカイブ

mysqlがEXPLAINですら重い時に

2016年1月21日mysqlMySQLpr

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でAUTO_INCREMENTの初期値をはずしたい

2014年6月30日mysqlMySQLpr

何もオプションを付けずに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 — データベースバックアッププログラム

【cakePHP】トグルスイッチをAJAXで反映させる

2013年1月3日cakePHPcakePHP, jqueryMobile, MySQLpr

さして目新しいことでもないですが簡単な小ネタでも。

トグルスイッチを切り替えるたびにDBを書き換えることは簡単にできます。
cakePHP2.0+jQueyMobile1.2でのトグルスイッチのDBへの反映のやり方です。いいやり方か分からないですが何かの助けになればと。

cakePHPのアクションは

public function toggleValid($id){
	if (!is_numeric($id)) return false;
	$this-&gt;{モデル名}-&gt;query(&quot;UPDATE {$this-&gt;table} SET valid = (CASE valid WHEN 1 THEN 0 ELSE 1 END) WHERE id = ?&quot;, array($id));
}

public function toggleValid($id){

if (!is_numeric($id)) return false;

$this->{モデル名}->query("UPDATE {$this->table} SET valid = (CASE valid WHEN 1 THEN 0 ELSE 1 END) WHERE id = ?", array($id));

}

こんな感じです。これをコントローラに記述して /{コントローラ名}/toggleValid/{ID} にアクセスするたびにvalidカラムが0と1に交互に変わります。CASE文で判定すればいろいろできて便利なのでcakePHPでSQL組み立てるやり方が分からないときはとりあえずqueryファンクション使っちゃいます。

そしてこの関数はコントローラーでなくモデルに書くべきものなので実際にはモデルに書いてます。またjQueryMobileと組み合わせているので下のようになります。

[共通読み込みJavaScriptファイル]

$(&#039;#_favorite_people&#039;).live(&#039;pageinit&#039;, function(){
    $(&#039;#_favorite_people .ui-slider-switch&#039;).live(&#039;change&#039;, function(){
        $.post(&#039;/favorite_people/update_valid/&#039;+$(this).data(&#039;id&#039;));
    });
});

$('#_favorite_people').live('pageinit', function(){

$('#_favorite_people .ui-slider-switch').live('change', function(){

$.post('/favorite_people/update_valid/'+$(this).data('id'));

});

このファイルはjQueryMobile特有の処理をしています。最初のイベントバインドはDOMキャッシュを有効にしたままJavascriptを実行させるため、data-role=”page”の属性を持つコンテナにidを割り振り、そのページコンテナの初期イベントを監視しています。

[ビューファイル(index.ctp)]

	&lt;?php
	echo $this-&gt;Form-&gt;input(&#039;FavoritePerson.valid&#039;, array(
		&#039;type&#039;=&gt;&#039;select&#039;
		,&#039;data-role&#039;=&gt;&#039;slider&#039;
		,&#039;label&#039;=&gt;false
		,&#039;options&#039;=&gt;array(&#039;0&#039;=&gt;&#039;OFF&#039;, &#039;1&#039;=&gt;&#039;ON&#039;)
		,&#039;value&#039;=&gt;$valid
		,&#039;data-id&#039;=&gt;$id
	));

<?php

echo $this->Form->input('FavoritePerson.valid', array(

'type'=>'select'

,'data-role'=>'slider'

,'label'=>false

,'options'=>array('0'=>'OFF', '1'=>'ON')

,'value'=>$valid

,'data-id'=>$id

));

$validと$idはコントローラからセットしておいてください。data-idはjQueryMobileではおなじみのHTML5の独自データ属性です。これでデータIDをjQueryへ渡すのが簡単です。

[AJAX呼び出し先コントローラー]

	public function update_valid($id){
		$this-&gt;autoRender = false;
		if ($this-&gt;request-&gt;is(&#039;post&#039;)){
			$this-&gt;FavoritePerson-&gt;toggleValid($id);
		}
	}

public function update_valid($id){

$this->autoRender = false;

if ($this->request->is('post')){

$this->FavoritePerson->toggleValid($id);

}

[モデル]

	public function toggleValid($id){
		if (!is_numeric($id)) return false;
		$this-&gt;query(&quot;UPDATE {$this-&gt;table} SET valid = (CASE valid WHEN 1 THEN 0 ELSE 1 END) WHERE id = ?&quot;, array($id));
	}

public function toggleValid($id){

if (!is_numeric($id)) return false;

$this->query("UPDATE {$this->table} SET valid = (CASE valid WHEN 1 THEN 0 ELSE 1 END) WHERE id = ?", array($id));

}

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

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

「MySQL」タグアーカイブ

mysqlがEXPLAINですら重い時に

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

参考

【cakePHP】トグルスイッチをAJAXで反映させる