水曜以外もどうでしょう

北海道発。食べ歩きの記録グルメ情報や北海道の見どころなどをメモ替わりに書いています。

| PAGE-SELECT | NEXT

≫ EDIT

MySQLのエラー「Row size too large」

データベースのupdate時に以下のエラーが発生。

#1118 - Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

ひとつのレコード(1行)の上限って8126バイトなんですね。
ROW_FORMATという言葉も、後から出るストレージエンジンという言葉も知りませんでした。

ネット検索すると、
ストレージエンジンが「InnoDB」の場合で、ROW_FORMATが「Compact」のとき、8126が上限らしいですね。
「8Kの壁」というらしいです。

で解消方法を調べると・・。

参考ページ)
http://sawara.me/mysql/2219/

エラーメッセージにもある通り、まずは、
ROW_FORMAT=DYNAMIC または ROW_FORMAT=COMPRESSED に変えたいと試みる。

以下のコマンドで、テーブルの状態を確認
SHOW TABLE STATUS LIKE 't1'

Engine: InnoDB
Row_format: Compact だと言っている。

ところが、
ALTER TABLE `t1` ROW_FORMAT=DYNAMIC;
として、もう一度 SHOW TABLE STATUS LIKE 't1' で確認しても、Row_formatが変わらないのだ。
まさに、参考ページの通り。

で、
SHOW GLOBAL VARIABLES LIKE '%innodb_file_%';
を実行して、ファイルフォーマットが、「Antelope」になっているのも参考ページと同じ。
ファイルフォーマットが「Antelope」の場合には、ROW_FORMATの変更が効かないんですかね、わかりませんが。
ファイルフォーマットを「Barracuda」にしなさいとのこと。
自前サーバーだと、/etc/my.cnfをいじるところ。しかし、今回はレンタルサーバーなのでそれができない。

じゃ、
ストレージエンジンを変えるしかないのですかね。
ストレージエンジンとはなにか知りませんでした。

参考ページ) ストレージエンジン MyISAMとInnoDBの違い
http://onlineconsultant.jp/pukiwiki/?MyISAM%E3%81%A8InnoDB%E3%81%AE%E9%81%95%E3%81%84

今回のアプリケーションでは、トランザクションが使えなくても良いでしょう・・
ということで、MyISAMへの変更をすることにした。

ALTER TABLE `t1` engine=MyISAM;

これで、1行のバイト数制限が緩んだ。
64KBなのかな? ちょっと詳しくはわかりません。
とりあえず、8KBでエラーになっていたデータは通りました。

結論から書けば良いのですがね、苦労の過程を書いてみました。

スポンサーサイト
応援宜しくお願いします。ポチ↓↓
fC2ブログランキング にほんブログ村 グルメブログ 北海道食べ歩きへ 人気ブログランキングへ


| データベース | 01:39 | comments:0| trackbacks:0| TOP↑

≫ EDIT

Postgresqlでto_dateが使えなくなった?

Postgresql 7.3.x → 8.4.x
へバージョンアップ。

今まで動いていたものが動かなくなっていることに気づく。
調べてみると、to_dateでエラーが出ていますね。

縮小記述はこちら。

select insert_time,to_date(insert_time,'YYYY-MM-DD') from table_name;

ERROR: function to_date(timestamp without time zone, unknown) does not exist at character 20


to_date関数がなくなったわけではなくて、型判定が厳しくなった?
ようで。

::textを付けると良いようだ

select insert_time,to_date(insert_time::text,'YYYY-MM-DD') from table_name;

情報元
http://www.mkyong.com/database/to_date-function-between-postgresql-82-and-83/


無事動きました。


応援宜しくお願いします。ポチ↓↓
fC2ブログランキング にほんブログ村 グルメブログ 北海道食べ歩きへ 人気ブログランキングへ


| データベース | 16:41 | comments:1| trackbacks:0| TOP↑

≫ EDIT

postgresqlのlike検索でインデックスを使わせたい

likeでデータを探すのが遅いので高速化したい。

(1)そもそもが遅い
という問題と
(2)インデックスが使われていない

という問題がある。
(1)については、senna+ludiaを使って高速化する方法など試した。
これは、また別の機会に書くことにする。

(2)インデックスが使われない問題について書く。

そもそも、likeで
target like '%abc%' などと中間一致検索をしても、indexが使われないようだ。
これはネット上の皆さんも苦労されているのがうかがえた。

当方の環境
CentOS release 5
postgresql 8.1


今直面しているクリティカルな問題は、likeを使った前方一致検索のSQLだったので、
target like 'abc%'
前方一致だけはやっぱりindexを使いたいのだ。


# SET ENABLE_SEQSCAN TO OFF;
を使って、全走査をOFFにすると良いという情報を発見し、意気揚々だったが、当方の環境では期待通りに動かなかった。

インデックスが使われないことは、explain analyzeで確認
# explain analyze select count(*) from t where target like 'abc%';


次に発見したのは下記の情報。

「ロケール(国際化と地域化) - Let's Postgres」
http://lets.postgresql.jp/documents/technical/text-processing/2

ロケールの問題を論じてくださっているページだったが、
「LIKE でインデックスを使わせるには」という、まさに必要な情報を掲載してくださっていた。

以下引用文

普通にインデックスを作成してしまうと、LIKE 前方一致検索でインデックスが使われません。以下では、"SET enable_seqscan = off" 指定しているにも関わらず、他の選択肢が無いので Seq Scan を使ってしまっています。

<中略>

回避方法は、演算子クラス text_pattern_ops を指定することです



ということで、以下の様にtext_pattern_opsを使ってインデックスを作成すると、なんとインデックスが使われる様になった。

create index index_t_target on t (target text_pattern_ops);


なんともありがたい情報でした。

同じくexplain analyzeでindexが使われたことを確認し、かつcost(実行時間)が1/3くらいになった。
もっと速くなっても良さそうだったけど、まずは効果があり感激。

データベースは奥が深い。


応援宜しくお願いします。ポチ↓↓
fC2ブログランキング にほんブログ村 グルメブログ 北海道食べ歩きへ 人気ブログランキングへ


| データベース | 02:12 | comments:0| trackbacks:0| TOP↑

≫ EDIT

postgresqlで”select count(*) from table_name”が遅い対応

テーブルの行数を数えたい場合、以下のように数えているのだが、どうも最近遅いことに気づく。
いや、前から薄々感じていたのだが、ちゃんと調べることができず。

select count(*) from table_name;

たかだか1,000行ぐらいのテーブルだ。

それでも時間がかかっていて、ぜひ速くしたい。


web上の情報を探してみると、where条件を付けてあげると速くなるようで。
でも、無条件に全部数えたいから、whereは付けにくいのだが。。

該当のテーブルはIDっぽい物を振っていて、sequenceで管理しているので、

select count(*) from table_name where id>0;

と、idは正の整数なんで、全部検索されるハズの一見意味のない条件を付けてみる。


これが効果があるようで、速度が劇的に改善。

\timing

での計測で、200倍近く高速になった。
うれしい。


どうしてこんな結果になるのか、indexやらなにやら、データベースについて勉強のしがいがある課題だけど。
今は深追いせず、結果だけを受け入れよう。

いつか調べたいなー。




応援宜しくお願いします。ポチ↓↓
fC2ブログランキング にほんブログ村 グルメブログ 北海道食べ歩きへ 人気ブログランキングへ


| データベース | 20:11 | comments:0| trackbacks:0| TOP↑

≫ EDIT

postgresqlでログファイルの出力をカスタマイズ

postgresでうまく行かないことがあり、ログをチェックしてみることにした。
サーバを変えてから大きなトラブルもなく、ログを注視する機会がなかったので、ログの調整から。

postgres 8.1.4
CentOS 5

postgresqlのホームへ移動。
/var/lib/pgsql/data

設定ファイルは
postgresql.conf

ログの位置は
pg_log/postgres-XXX.log


ログを出力する位置、ファイル名のルールはpostgresql.confで決めているようだ。

早速ログを見てみるとエラーが連続している。
------
ERROR:  syntax error at or near "and" at character 100
------

このエラー自体は、今回のトラブルとは無関係だが、解決するに越したことはない。
でも、このエラーだけでは、timestampも出てないし、SQL文も出てないし、なんの事やら。

それで、ログの出方を調整して解析することにした。


参考資料から、postgresql.confをいじってみる。

参考にしたページ
http://lets.postgresql.jp/documents/technical/log_setting


調整してみたのは以下

★SQL文を出すエラーレベルを指定する。
log_min_error_statement = panic # 実質出さない。
 ↓
log_min_error_statement = error # エラー時は出す。


★実行に時間がかかる場合の、SQL文の出力。msでレベル指定。
log_min_duration_statement = -1   # -1は無効、0は全て
 ↓
log_min_duration_statement = 500   # ms で


★エラー出力時のprefixフォーマット
log_line_prefix = '' 

log_line_prefix = '[%t][%p][%u][%d] ' 
時刻、プロセスID、dbユーザ名、db名を出す


★どのSQL文をログに記録するかを制御
log_statement = 'none'         # none, mod, ddl, all
 ↓
log_statement = 'mod'           # none, mod, ddl, all

ddlはCREATE, ALTER, DROP
modはddlに加えて、insert, update, delete,truncate等

log_statementは、問題が解決したら、noneに戻したほうがいいかな。


でリスタート
/etc/rc.d/init.d/postgresql restart


これで、エラーメッセージが装飾され、かつその時のSQL文が出てくるので解析しやすい。

[2011-05-12 13:13:59 JST][プロセスID][ユーザ名][db名] ERROR:  syntax error at or near "and" at character 100
[2011-05-12 13:13:59 JST][プロセスID][ユーザ名][db名] STATEMENT:  select db_field from db_name
where id= and mode=1


応援宜しくお願いします。ポチ↓↓
fC2ブログランキング にほんブログ村 グルメブログ 北海道食べ歩きへ 人気ブログランキングへ


| データベース | 13:29 | comments:0| trackbacks:0| TOP↑

| PAGE-SELECT | NEXT