タグ: MySQL を抽出しています。
Total: 54

うるう秒の調整でCPU負荷が上がる現象

2012/7/2 23:22
さくらインターネットのアナウンスで知ったのだけれど、うるう秒の調整でCPU負荷が上がる現象があるそうです。

CentOS6系、MySQL5.5を使っている場合に出てました。
ロードアベレージで、1~1.5くらいは上昇してる感じでした。

で、指示に従い、rootで、
date -s "`LANG=C date`"
で、下がっていきました。

MySQL5.5 InnoDBの圧縮

2011/9/30 01:34
InnoDB pluginで圧縮できるらしいので、トライ。
MySQL5.1だと、phpMyAdminの操作で、Compressedを選べたのだけれど、MySQL5.5だと選べない。なぜ?

とりあえず、コマンドラインで、
mysql> ALTER TABLE test ROW_FORMAT=Compressed;  

mysql> SHOW TABLE STATUS LIKE 'test' \G;
*************************** 1. row ***************************
          Name: test
        Engine: InnoDB
       Version: 10
    Row_format: Compact
          Rows: 0
Avg_row_length: 0
   Data_length: 16384
Max_data_length: 0
  Index_length: 0
     Data_free: 0
Auto_increment: 1
   Create_time: 2011-09-30 00:23:56
   Update_time: NULL
    Check_time: NULL
     Collation: utf8_general_ci
      Checksum: NULL
Create_options: row_format=COMPRESSED
       Comment:

上の、Row_formatがCompactのままで、どうも怪しい。
http://dev.mysql.com/doc/innodb/1.1/en/innodb-co...
で調べても、なんとなくエラーっぽい。

mysql> SET GLOBAL innodb_file_format=Barracuda;
でいけた。

MySQL5.5の場合も、my.cnfに
innodb_file_per_table
innodb_file_format=Barracuda
が必要。
事前検索では、5.5はデフォルトがBarracudaだからか、記述なしみたいな説明が多かったのだけれど。

で、下記を参考に、実際圧縮してみる。
http://d.hatena.ne.jp/sh2/20090628

ALTER TABLE test ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8;
だと、確かに半分、
ALTER TABLE test ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4;
だと、1/4くらいになった(ならないものもあった)。

それなりに時間はかかる。563M→245Mになるのに、4分くらいでした。

パフォーマンスも悪くない気がする。速くなった感じすらする。
転置インデックスのテーブルなんかは、とても肥大化するから、KEY_BLOCK_SIZE=4で圧縮していこうと思う。(ALTER TABLE時も、Lockされてる様子はないので、気にせずやってしまっていいのではなかろうか)
▼追記 2011/9/30 11:04
転置インデックスは、KEY_BLOCK_SIZE=4 だとあまり圧縮されなかった(8Gが7.5G程度)ので、KEY_BLOCK_SIZE=8 にする。
▼追記 2011/9/30 12:18
裏で走らせてたら、落ちましたがな。
やっぱり深夜にやろう。

FULLTEXTインデックス(MyISAM)から転置インデックス(InnoDB)へ

2010/11/28 23:04
全文検索を実装するときに、転置インデックス的なやり方(そんな名前がついてるとか知らなかったけれど)は検討したのだけれど、どんでもないレコード数になるから、扱いづらくて遅いんじゃないかと思ってました。

で、実験中。

table
CREATE TABLE IF NOT EXISTS `inno_node_index` (
 `ngram` varchar(3) NOT NULL,
 `nid` int(11) NOT NULL,
 PRIMARY KEY (`ngram`,`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
nidは、65万件くらい。
1nidに対して、ngram化した文字が1000あったとして、レコード数は6億レコードとかになる。

insertは、10万nidで15分くらいだったから、許容範囲だろうか。

検索クエリー
SELECT SQL_NO_CACHE nid FROM `inno_node_index`
WHERE ngram IN ('差出箱','出箱7','箱7号')
GROUP BY nid
HAVING COUNT(nid) = 3;

FULLTEXTの場合は、こんな感じだったもの。
SELECT SQL_NO_CACHE nid FROM `new_node_search`
WHERE match(ngram) against ('+差出箱 +出箱7 +箱7号'  in boolean mode);

さあ、どんな結果がでるのだろう。
ただいま、データinsert中。

MyISAMで更新の多いFULLTEXTインデックスは、すぐにオーバーヘッドが発生するし、修復もハイコストだし、なんとかしたいというのが動機。InnoDB化がきっかけ。
▼追記 2010/11/29 01:10
6億レコードもなかった。70,295,304レコード。(InnoDBはレコード数をカウントするのが遅いことを知る)

では、検索スピード比較(3文字ngram)
数日前のポストマップのインデックスからです。
「差出箱7号」
InnoDB転置: (2,622 合計, クエリの実行時間 1.0055 秒)
FULLTEXT: (2,624 合計, クエリの実行時間 0.0731 秒)

「差出箱5号」
InnoDB転置: (87 合計, クエリの実行時間 1.0579 秒)
FULLTEXT: (87 合計, クエリの実行時間 0.0633 秒)

「kuwa」
InnoDB転置: (1,369 合計, クエリの実行時間 0.0236 秒)
FULLTEXT: (1,369 合計, クエリの実行時間 0.1547 秒)

「たぬき」
InnoDB転置: (37 合計, クエリの実行時間 0.0007 秒)
FULLTEXT: (37 合計, クエリの実行時間 0.0222 秒)

「愛」
InnoDB転置: (43,252 合計, クエリの実行時間 0.0014 秒)
FULLTEXT: (43,257 合計, クエリの実行時間 0.0558 秒)

「東京都港区赤坂」
InnoDB転置: (4 合計, クエリの実行時間 0.0962 秒)
FULLTEXT: (4 合計, クエリの実行時間 0.0048 秒)

「万博」
InnoDB転置: (83 合計, クエリの実行時間 0.0029 秒)
FULLTEXT: (83 合計, クエリの実行時間 0.0514 秒)

「白木屋」
InnoDB転置: (29 合計, クエリの実行時間 0.0187 秒)
FULLTEXT: (29 合計, クエリの実行時間 0.0267 秒)

「傾いている」
InnoDB転置: (37 合計, クエリの実行時間 0.1045 秒)
FULLTEXT: (37 合計, クエリの実行時間 0.1354 秒)

「そんなことじゃないかと思っていました。」
InnoDB転置: (1 合計, クエリの実行時間 0.5424 秒)
FULLTEXT: (1 合計, クエリの実行時間 0.1139 秒)
スピードに関しては、十分という気がする。3文字までなら転置の方が速くて、それ以上はFULLTEXTという結果かな。

データ容量に関しては、
InnoDB転置: 3.8G
FULLTEXT: 1.6G
倍くらいなら、気にならない。
▼追記 2010/12/6 01:54
tableのindexだけれど、
PRIMARY KEY (`nid`,`ngram`)
こうしておかないと、nidで検索したときにインデックスが使用されない。順番大事。
http://dev.mysql.com/doc/refman/5.1/ja/multiple-...

検索を更新するときに、nidで検索して削除しなければならないから。
▼追記 2010/12/6 13:19
これはとんでもない勘違いだ。検索でindexが使用できなくなるじゃないか。
CREATE TABLE IF NOT EXISTS `inno_node_index` (
 `ngram` varchar(3) NOT NULL,
 `nid` int(11) NOT NULL,
 PRIMARY KEY (`ngram`,`nid`),
 KEY `nid` (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
nidに追加でindexをつけるしかなさそうだ。

PHPのDB接続の自作関数メモ

2010/11/28 20:03
// !define ---------------------------------------------->
define('DBSV','localhost'); //localhostとかipとか
define('DBUSER','root'); //rootとかユーザー名とか
define('DBPASS','xxxxxxxxxxxx');

define('DBNAME','dbmane');
define('PREFIX','dev_'); //tableにprefixをつけてるなら

// !db ---------------------------------------------->
$dbconn = false;

function db($sql,$ope='select',$dbname=DBNAME){
 global $dbconn;
 $sql = strtr($sql,array('@@'=>PREFIX,'@'=>'@'));
 if(empty($dbconn)) $dbconn = mysql_connect(DBSV,DBUSER,DBPASS);
 mysql_select_db($dbname,$dbconn);
 $res = mysql_query($sql,$dbconn) or die(mysql_error());
 switch($ope){
   case 'insert':
     $res = mysql_insert_id($dbconn);
     break;
   case 'update':
     $res = mysql_affected_rows($dbconn);
     break;
 }
 return $res;
}

function m($str){
 global $dbconn;
 if(empty($dbconn)) $dbconn = mysql_connect(DBSV,DBUSER,DBPASS);
 return mysql_real_escape_string(strtr($str,array('@'=>'@')));
}

// !debug ---------------------------------------------->
function d($v,$r = ''){
 $R = print_r($v,true);
 if(!empty($_SERVER['HTTP_USER_AGENT'])) $R = '<pre>'.$R.'</pre>';
 if($r) return $R;
 echo $R;
}
で、

$sql = "SELECT * FROM @@table WHERE field= ORDER BY field LIMIT 50;";
$res = db($sql);
while($row = mysql_fetch_assoc($res)){
 d($row);
}

$sql = "INSERT INTO @@table (field,field) VALUES (data,data);";
db($sql,'insert');
こんなかたちでつかう。

db関数の中で、いろいろやればいい。PEARとかPDOを使うとか、select系をレプリケーションしているサーバーに振るとか。

InnoDBメモ

2010/11/27 13:26
パスは、Macportsでインストールしたもの。

[バックアップ]
mysqldumpでも、バックアップを取るようにする。
生データのバックアップでは、
/opt/local/var/db/mysql5/ibdata1
/opt/local/var/db/mysql5/ib_logfile0
/opt/local/var/db/mysql5/ib_logfile1
このあたりは必須。
http://dev.mysql.com/doc/refman/5.1/ja/innodb-ba...

my.cnfで、
innodb_file_per_table
を記述している場合も。
http://dev.mysql.com/doc/refman/5.1/ja/multiple-...

後から、innodb_file_per_tableを記述した場合は、OPTIMIZE TABLEすると作成される。
mysql> USE dbname;
mysql> OPTIMIZE TABLE table;
mysqlcheckでやる場合、
/opt/local/bin/mysqlcheck5 --user root --password=xxxxxx  -ao dbname [table1 table2 ...]

[my.cnf]
/opt/local/shar/mysql5/mysql/
の中に、いくつかサンプルがあるので参考に。後は検索しつつ。
http://www.mysqlpracticewiki.com/index.php/My.cn...

後で変えるのがやっかいそうな、innodb_data_file_path は悩むところだけれど、innodb_file_per_tableにすれば、膨張しないだろうから、デフォルトの10Mでいってみよう。
innodb_file_per_table
innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_file_size のサイズを変えたときは、
ib_logfile0,ib_logfile1を移動するなりリネームするなり削除するなりして、MySQLを再起動。

[InnoDB Plugin]
http://nippondanji.blogspot.com/2010/03/innodb-p...
http://d.hatena.ne.jp/sh2/20100427

my.cnfに、下記を書く。(MySQL 5.1.38~, 正式版はMySQL 5.1.46~)
ignore-builtin-innodb  
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

動いているかの確認は、
mysql> select @@innodb_version;

[移行]
http://dev.mysql.com/doc/refman/5.1/ja/convertin...
mysql> ALTER TABLE table ENGINE=INNODB;
or 新しいテーブルを作って、
SET UNIQUE_CHECKS=0;
INSERT INTO new_table SELECT * FROM old_table;
SET UNIQUE_CHECKS=1;
するとか。

実際の移行イメージは、
  • 事前準備として、MySQL 5.1.46以上に上げて、InnoDBを有効にする。
で、サイト毎に様子を見ながら移行。影響の小さいものからやるのがよいだろう。
  • サイトをメンテナンス中にする。
  • MyISAMのテーブルをバックアップ。
  • ALTER TABLEする。
  • サイト再開。
というところだろうか。1サイト、1時間以内でいけそうな気はするが、レプリケーションが壊れそう。

▼追記 2010/11/27 22:49
ううむ、Tigerの32bitだと、
innodb_buffer_pool_size を大きくできない。2Gも2000MもInnoDBが立ち上がらなかった。1536Mにしたら立ち上がった。
http://dev.mysql.com/doc/refman/5.1/ja/innodb-co...

snow leopardにすれば、64bitで動く。
InnoDB化を済ませて、様子を見つつ、Masterをどうするか考えよう。
▼追記 2010/11/28 16:59
30maps系のInnoDB化は、ほぼ終了。今のところ、いい感じで動いているように見える。

FULLTEXTを使っている全文検索系のMyISAMテーブルをどうするかを研究しよう。FULLTEXT系のテーブルが壊れたときのREPAIRは(これがしばしばおこる)、とても時間がかかってやっかいだから、InnoDBでの転置インデックス化を取り組んでみる。
▼追記 2010/11/29 03:18
rottelも完了。

InnoDB化で、レプリケーションの遅延がほとんど発生しなくなった。効果絶大だった。InnoDBの癖を把握して、徐々にクエリーも手を加えていこう。とりあえずは、全文検索を実装する。
▼追記 2010/11/29 13:22
メモリとのバランスに不安が残る。
apacheとmysqlは、サーバーを分けた方がいいのかもしれないけど、まだしない。
とりあえず、メモリが貧弱なマシンの innodb_buffer_pool_size を下げよう。
▼追記 2010/12/1 02:34
テーブルが大きくなってきて、大量のデータ挿入時の遅延が大きくなってきた。
ちょっと調べると、レプリケーション フォーマットなんていうもの発見。
http://dev.mysql.com/doc/refman/5.1/ja/replicati...

STATEMENTになっていたから、ROWに変更してみたけれど、速度は似たようなものなので、mixedにしとく。

http://nippondanji.blogspot.com/2009/03/mysql10....
にあるように、「一度に大量の更新をしない」ことですね。

検索インデックス作成のような大量のデータの更新は、usleep(300000) ...0.3秒とかを挟みながら、ゆっくり動かす。1日で20万件くらいは更新できるんじゃなかろうか。
▼追記 2010/12/1 23:50
レプリケーションのマニュアルにあるように、RAND()とかをつかったクエリーはやめた方がいい。
http://dev.mysql.com/doc/refman/5.1/ja/replicati...

UPLOAD table SET rid=RAND();
こういうような。各サーバーで値が違って構わなかったので(どちらかと言えば、そっちの方がランダムでいい)、そういうクエリーが存在していたのだけれど、InnoDBにして、1台のレプリケーションがそれ原因でしばしば止まった。MyISAMでは、遅延こそすれ動いていたけれども。

データの同一性という点では、こういうのもNG.
UPDATE table SET ts=UNIX_TIMESTAMP()
レプリケーション フォーマットがROWなら、同一になるようだけれど。

あと、
innodb_buffer_pool_size
innodb_log_file_size
は、Masterとslaveで同一にした方がいいとのこと。ほんとかな?
http://nippondanji.blogspot.com/2009/03/mysql10....

徐々に買い増ししていくスタイルだと、最新のものはどんどんスペックが上がっていくので、古い低スペックなものに揃えるのは、ちょっと悩ましい。

古いものはApache用にして、新しいものをMySQL用にするとかいう流れでいくべきなのかしら。そのあたりはmacmini群を眺めながらゆっくり検討。
▼追記 2010/12/6 22:48
InnoDBにして、phpMyAdminがかなり重たい。
サイドバーのテーブルリストの表示に、かなり時間がかかっている感じ。
いずれ調査。

InnoDBにしていってみるか

2010/11/26 01:21
MyISAMをInnoDBに変換する方法
http://dev.mysql.com/doc/refman/5.1/ja/convertin...

ALTER TABLEか、new_tableをInnoDBでつくって、
SET UNIQUE_CHECKS=0;
INSERT INTO new_table SELECT * FROM old_table;
SET UNIQUE_CHECKS=1;
これでやってみるか。

ローカルでかなり検証してから移行しないとコワイ。
んー、これはハマりそうだ。じっくりやろう。

やっぱり時期尚早ということにしとこ。MyISAMで生ファイルを移動するだけで、DBが移動できるのは、今のところとてもいいし。
▼追記 2010/11/27 00:59
いや、やっぱり遅延を防ぐにはInnoDB化な気がしてきた。tableごとlockされないというのが、ポイントだきっと。

http://dev.mysql.com/doc/refman/5.1/ja/multiple-...
[mysqld]
innodb_file_per_table

複数のテーブルスペースを利用する事は、特定のテーブルを別々の物理ディスクに移動したり、単一テーブルのバックアップを残りの InnoDB テーブルの利用を邪魔する事なく、素早く復元したいユーザにとって、有益な物です。
とのことだから、まあまあ扱いやすくできるかもしれない。

InnoDBのレプリケーションについて研究してみる。

FULLTEXTインデックスをつけるテーブル(ROW_FORMAT=DYNAMIC)

2010/7/29 03:17
新たにfieldをつくって、FULLTEXTインデックスをつけたのだけれど、どうもインデックスが作成されているのか怪しい。更新するたびにオーバーヘッドが発生するし。

うまく動いているtableと比較すると、
ROW_FORMAT=DYNAMIC
というのがついてない。

ALTER TABLE `table名` row_format=DYNAMIC;
で付加してみたけれど、変わらなかったので、

CREATE TABLE IF NOT EXISTS `table名` (
 `id` int(11) NOT NULL,
 `ngram` longtext NOT NULL,
 PRIMARY KEY  (`id`),
 FULLTEXT KEY `ngram` (`ngram`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
という感じで、新規に作成して解決。

MySQLのvarchar型の長さって

2010/7/27 18:36
255byteだか255文字だと思っていたのだけれど、512とかでも指定できるようになってる。
MySQL 5.1.47にて。現在は文字数らしい。
http://dev.mysql.com/doc/refman/5.1/ja/char.html

もともとそうだったのかしら?
追求しないけれど。
前 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |

Rottel内コンテンツ

ユーザー一覧

Rottelとは?
利用規約
開発飲料
利用者の声
ヘルプ
close