この広告は30日以上更新がないブログに表示されております。
新規記事の投稿を行うことで、非表示にすることが可能です。

一つしかない資源（リソース）へ同時に更新、書き込みが発生する事態は極力避けたいです。FastCGI(nginxやapache）で動作させるPHPでは、同時間に複数のアクセスがあったりするので、一つのリソースに対する排他制御が必要になることがあります。

C#のような言語では、lockステートメントやMonitor.TryEnterなどを利用することで実現できます。PHPではこのようなステートメントや高級な仕組みが用意されていません。

PHP でもコンパイル指定次第でセマフォ（sem_get 、sem_acquire）やMutexが使えます。レンタルサーバー上にあるPHPでは利用できないことが多いですね
セマフォはサーバー上資源を利用する仕組みだと思っています。そのためデットロック等の異常事態が発生した際、復旧にはサーバー管理者権限が必要になったりするので仕方ないと思っています。

セマフォやMutexを利用しない排他制御の手段は、ファイルロック(flock)です。

PHPのnumber_format関数は数字を千の位毎にグループ化してフォーマットする関数です。
数値にカンマをつけたい、そんな場合に利用する関数です。

ただ、小数を含むフォーマットの場合には注意が必要です。number_format関数内で丸め（四捨五入）が働きます。

echo 12339960/1000;

結果は12339.96です。
「12,339.9」と表示させたいのですが、number_formatではうまくいきません。

echo number_format(12339960/1000,1);

結果は12,340.0です。




number_formatの挙動は以下の通り・・・・

sqlite3データベースのファイルサイズは114,570,240バイト、テーブル数は1件だけです。ファイルサイズはそこそこあるんですが、１テーブルのみのデータベースで960件ぐらいの登録数です。この程度ならケア不要かと思っていたんですが、間違いでした。

SQLite3 1000件未満のテーブルでselectに1.78秒かかる

1. sqlite3のバージョンは3.7.17。
   
2. スキーマはこんな感じです

   
   CREATE TABLE tbl( pk INTEGER NOT NULL PRIMARY KEY, 
   sts, model,year, post_id, time, lapse, 
   mokuji, html, error_reason, fatal_reason );

   
   
3. 問題になったのは「select * from tbl order by time, year desc」の発行です。
   約1.78秒かかりました。サーバーの負荷が高い場合は30秒近くかかることがありました。
   
4. どうすべきか？処理の見直し、EXPLAIN QUERY PLANを使って調査、原因追求して対処するしかないですね。
   処理自体見直しましたが、これといっておかしな部分はありませんでした。
   

1000件未満のテーブルでselectに1.78秒かかる原因はUSE TEMP

SQLite3は「EXPLAIN QUERY PLANコマンド」でSQLの問題点（というかどんな感じに処理しているのか）を見つけることができます。（詳しくはこちら＝＞https://www.sqlite.org/eqp.html）

調査中データベースに変更を加える事になるので、対象のデータベースファイルをコピーしたものを利用しています。

1. PKが効いているパターンはこんな感じにシンプルな結果になります。

   explain query plan select * from tbl;
   0|0|0|SCAN TABLE tbl (~1000000 rows)

   
   
   
2. 遅いSQLはB-TREEのテンポラリを使ったソートを使っていることがわかります。

   explain query plan select * from tbl order by time, year desc;
   0|0|0|SCAN TABLE tbl (~1000000 rows)
   0|0|0|USE TEMP B-TREE FOR ORDER BY

   このUSE TEMPを消すことができれば速く改善できます。
   
   
3. インデックスはtime,yearは不正解でした。

   sqlite> create index idx_1 on tbl( time, year );
   sqlite> explain query plan select * from tbl order by time, year desc;
   0|0|0|SCAN TABLE tbl (~1000000 rows)
   0|0|0|USE TEMP B-TREE FOR ORDER BY
   sqlite> drop index idx_1 ;

   結果は変わりません。
   
   
4. order句に指定しているすべてを対象にします。time,year descが正解です。

   sqlite> create index idx_1 on tbl( time, year desc);
   sqlite> explain query plan select * from tbl order by time, year desc;
   0|0|0|SCAN TABLE tbl USING INDEX idx_1 (~1000000 rows)

   USE TEMPが消えました。
   

まとめ：indexを適切に設定することで改善できます。EXPLAIN QUERYは万能ではないです

phpで「$開始=array_sum( explode(' ', microtime() ));処理;echo array_sum( explode(' ', microtime() )) - $開始;」とmicrotime関数を利用すると精度の高い実行時間を計測することが可能です。

↓で1.78秒かかっていました。EXPLAIN QUERY PLAINで見つけたindexで0.088秒に改善することができました。

            $results = $db->query("select * from tbl order by time asc,year desc"); 

今回の知見：データベースファイルサイズが大きいと、USE TEMPを利用するパターンは速度的な影響をモロに受けることがわかりました。



EXPLAIN QUERY PLANはクエリがデータベースインデックスを効果的に利用しているのか？、インデックスを設定することで速くできるのか？という視点で調査する場合に使えるコマンドです。

PHPプログラムをcronでスケジュール実行しています。PHP7からPHP8にアップグレードすることで速度アップするメリットがあるようなので気軽にPHP8に変更しました。ひっそりとPHPプログラムが異常終了していました。



変数の初期化忘れは致命的・・・

Rakuten Web APIで商品アイテム検索するPHPプログラムを利用しています。PHPのfile_get_contents関数でWeb Requestしています。
リクエスト部分するソースは↓こんな感じです。

if( $sts == 'fatal' ){
エラー処理
}
とか、よくあるソースコードかと思います。

$stsは正常系（数値）と異常系（fatal）を混在させていました。

これ、実は、以下ソースコードだとエラー処理が成立します。
$sts = 0;
if( $sts == 'fatal' ){
エラー処理 　＜＝ここに来ます。
}

まじですか？文字列と数値ですよ？＝＝が成立するなんて思いもつきませんが？本当です。実環境で相当悩みました。ソースコードレビューではわからない不具合ですね・・

FreeBSD 9.1のPHP 7.4.19 で確認しました。

$sts = 1;echo ($sts == 'fatal'); <= 不成立です。
echo (0 == 'fatal'); <= 不成立ですが・・・
$sts = 0;echo ($sts == 'fatal');
1 <= 成立です。
これってPHPの仕様なんでしょうかね？

久しぶりにドキドキました。謎の動作でどハマりしましたよ・・
ソースは以下のように修正したところ、仕様通りの動作となりました。
if( is_numeric($sts) == false && $sts == 'fatal' ){
エラー処理
}

PHP5とPHP7の違いは、ワードプレスを使っている限りほとんど意識することはありません。レンタルサーバーのコントロールパネルからPHPのバージョンを5.6.x系から7.0、7.1、7.2系に切り替えるだけでそのまま動きます。互換性の問題は発生しないのが素晴らしいです。よく考えられたアップデートだと思っていたんですが、5.6.x系・7.0系＝＞7.1系のアップデートは、とても気づきにくい罠がありました。知っていましたか？負の offset をサポートするようになりました。って一文でfile_get_contentsが別物の動きをしていました。

PHP5.6では、思い通り、設計通りの動作をしていたプログラムがありました。
このプログラムは、macOSで動かしていて、先日macOSをSierraからHigh Sierraにアップグレードしました。
macOS Sierraは、PHP5.6系です。macOS High Sierraは、PHP7.1系がすぐに使える状態で入っています。
この違いは、プログラムの結果が求めている動作をしていないことで初めて知りました。


PHPのマイグレーションをページに新機能、新しい関数、下位互換性のない変更点、推奨されなくなる機能、変更された関数、その他の変更など、PHPのバージョンをあげる前に注意するべきポイントがまとまっているので参考にしています。
メニューから各バージョンのマイグレーションを選択することができます。
参考：PHP7.0.xからPHP7.1.xへの移行

今回、発生した問題は、この移行ページに掲載されていました。
PHP 7.0.x から PHP 7.1.x への移行＞変更された関数 ＞ファイルシステム

file_get_contents() now accepts a negative seek offset if the stream is seekable.

さらりと１行でまとまっています。

file_get_contents関数はファイルを読み込んだり、URLからWEBページを取得できるよく使われる関数の一つです。
PHP5のfile_get_contents関数の説明はこんな感じでした。

それが、PHP7.1の変更後、このように変わっていました。


違いわかるでしょうか？そうです！$offsetのデフォルト値が-1から0に変わっています。マニュアル上はこのように変わっていますが、PHP5.6はいままで通り-1を渡しても0と同じ意味です。

一方、$offsetに-1を渡すようなプログラムをPHP7.1以降で動かすと、 ストリームの末尾からのオフセットと解釈されます。

＝＞PHP５で動く、PHP7.1で動きがおかしい原因はこれでした。

関数のデフォルト値って不変、ずっと変わらないものって思い込んでいました。現実は、バージョンアップで変わることがあるってことです。ファイル名以外、引数を指定することがないって方も多いかと思います。

作り込んだライブラリで、拡張性を持たせるためにPHPのデフォルト引数も全て受け付けるようなラッパー関数、ヘルパー関数はPHPのデフォルト値を自作関数、メソッドの引数にコーディングすることが多いです。

PHP5で動いていたものがPHP7で動かなくなったら、チェックすべきポイント

PHPのマイグレーションには、たった１行の変更点の記載でしたが、インパクトが大きいってことがわかったかと思います。
自作関数、自作メソッドでPHPのデフォルトを書いているところは、マニュアルを参考にし、再確認するのが良いかと思います。

この際、マイグレーションページを見ながら、記載のある関数を使っているかgrepやIDEの検索機能をフルに使ってチェックするのが効率的です。


＝＞マイグレーションページをインデックスにし、関数のマニュアルとソースを比較！

まとめ

繰り返しになりますが、PHP5とPHP7の違いは、ワードプレスを使っている限りほとんど意識することはありません。今回のfile_get_contentsに関しても完全互換性があり、ファイル名のみの指定で使っていると同一です。でもデフォルト引数の違いがありましたね。拡張性を考えたメソッドやヘルパ関数は標準と同じような引数を許可するような設計はこういった変更のインパクトが大きいって思いましたよ。

0と-1で別物の動きになってしまいますから・・・

PHPで文字列(テキスト)をストリームとして読み込みたい、fgetsのような感じでループさせながら１行ずつ処理したいっていう際に使えるやり方です。
実際のやり方は以下PHPソースコードの通りです。$txtはStringストリームとして扱いたいテキストを指定します。file_get_contentsで読み込んだテキストでもOKです。ここではヒアドキュメント形式で設定しています。テキストファイルから読み込む場合は、改行コードの違いに注意してくださいね。

<?php
$txt = <<< EOM
１行目
２行目テキスト
３行目文字列
４行目文字列
EOM;

foreach(  explode( PHP_EOL, $txt) as $row=>$line ){
// $rowは0から始まる番号です。
// $lineは１行ずつ文字列が入ってきます。
}

読み込んだテキストを改行で区切り、配列に格納しています。ここでは変数に入れることなく、直にforeachで回しています。
これでテキストをStringストリームのように１行ずつ処理することが可能になります。$rowは配列の添え字なので0から始まることに注意してください。行番号ではないですよ。

PHP_EOLは、行末文字です。WindowsならWIndowsの改行コード、Unix系ならUnix系の改行コードを意味しています。

SQLite3でたまに発生するdatabase is lockedって悩ましいですよね。勝手にロック解除してくれるとありがたいんですがlock状態を継続してしまうので時間経過で問題も大きくなりがちです。ここではdatabase is lockedになったデータベースファイルをunlockの状態に戻す方法、真の原因、lockされているデータベースファイルを特定する方法などがわかります。

SQLite3データベースをunlockする方法

ここではロックされているデータベースをdb.sqlite3としています。アンロックする方法自体はとても簡単でした。リモートログインしてshell操作する必要があります。FTPでも同様のことができるかもしれませんが、ここでは試していないのです。

移動して、コピーすることで、unlockできました。

1. mv db.sqlite3 bk.db.sqlite3
ロックされているSQLite3のデータベースファイルを別名にします。(mv)
2. cp -p bk.db.sqlite3 db.sqlite3
元と同じファイル日付で、元と同じファイル名としてコピーします。(cp -p)

たったこれだけの操作で悩み悩んだdatabase is lockedが回避できました。

ファイルを別名し、コピーすることでロックが解除できます。
sqlite3データベースにロック情報が記録されているのであれば、この操作では中身を一切書き換えていないので、アンロックされることはあり得ません。
このことからロックの原因は、PHPのSQLite3 拡張モジュールで使われているflock系のシステムコールに起因すると想像しています。

database is lockedが発生した真の理由

SQLite3データベースをunlockする方法にflock系のシステムコールがロックの原因と書きました。これは誰かがflockしているからdatabase is lockedが発生してしまうことになります。では、誰が一体flockをやっているのか？遅延書き込みなどの要因？
疑問でしたが、ようやくわかりました。まず以下のような設計でクーロン実行させています。

1. １）プログラムはPHPで作っています。クーロン実行させています。
   
2. ２）エックスサーバーのクーロンは、１回で動作できる時間が30秒までの制限があります。
   制限に達するとこのようなPHP Fatal error: Maximum execution time of 30 seconds exceededエラーメッセージとともに強制終了されます。
   
3. ３）PHPプログラムは、SQLite3データベースに同時にアクセスしない設計になっています。そのタイミングでCRUDを行うのは１つのプログラムだけです

このような状況下で、database is lockedが発生しました。SQLite3データベースをunlockする方法で解決しますがロックさせてしまう根本的な対処にはなっていないですよね。

プログラムの設計上、シングルで動作しているのでロックされること自体ありえないって思っていました。原因は、プログラムの重複実行でした（笑）

予想外です。まさかの自分自身のプログラムがdatabase is lockedの要因を作っているなんて・・・


ただ、これ自分でプログラムを複数実行しているわけではないです。スケジュール実行で自動実行されています。今回対象のプログラムは30秒以上動作することがあるのでMaximum execution timeになり、プログラムが強制終了していたはずなのですが。。。どうやら生き残る生命力豊かなたくましい奴がたまに誕生するようです。
１）Maximum execution timeになる処理はデータベースの更新を含んでいるのでタイミングが悪いとトランザクションでロックした状態になりえます。
２）そのため次のスケジュール実行で動いたクーロンプログラムがdatabase is lockedで失敗する
この流れでdatabase is lockedが発生していたと確信しています。


根本的な原因はクーロンが生き残ることがあるということでしたね。あとはこれに対応できるようにプログラムを変えるだけです。例えば29秒経過したら自発的に正常終了させる、クーロン実行されたプログラムのPIDを記録して、killするようにする、ロックの回避方法はわかっているのでロックが発生したらロックの回避方法を試すなどのことをやればdatabase is lockedを撲滅できそうです！

ロックされている？データベースロックの確認方法

database is lockedエラーが発生した、１つのSQLite3データベースだけ扱っていればロックされたデータベースの特定は簡単ですよね。複数のSQLite3データベースがあるとどれ？これ？どうやって調べるの？となります。どうしたら単純に調べられるのか、コマンドを色々試して見つけたのがこの方法です。
この方法は、リモートログインして確認します。
シェルからsqlite3コマンドでSQLite3データベースファイルに対してVACUUMコマンドを実行する方法です。

$ sqlite3 データベース "vacuum;"

lockedの状態のデータベースファイルは、このコマンドを拒否します。
SQL error: database is locked
というメッセージが出力されます。

問題なければ、VACUUMにより空き領域が解放されます。データベースサイズが膨れていくので定期的にVACUUMコマンドを実行するのが良いとされていますよ。一石二鳥の確認方法ですね！

１つ注意点があって、他のプログラムやPHPからアクセス中にはやらない！ってことです。やっても良いですが失敗する可能性があるのと、参照している側もそのタイミングにアクセスすることでCRUDが失敗する恐れもあります。そのため、アクセスが少ない時にやったほうが良さそうです。

ちなみにCRUDは、Create/Read(select)/Update/Deleteのデータベース操作を示しす用語です。

まとめ

いかがでしたでしょうか？ここまでSQLite3データベースをunlockする方法、database is lockedが発生した真の理由、ロックされている？データベースロックの確認方法をご紹介してきました。

database is lockedは回避できましたでしょうか。

今回ご紹介したこの方法で私は回避できました。たまに間違えるのですが、mvは重要です。消すのは怖いからと代わりにcpでやると解決できません。lockの状態を継続しますよ。必ず元あったデータベースファイルの存在を消す！ってことが重要です


参考までに、この現象は、xserverで遭遇しました。
SQLite3のバージョンは、SQLite version 3.3.6です。
PHPのバージョンは、7.0.16です。

PHP＋PDOでsqlite3のデータベースを使うことがありました。

データ件数は、１日１０００件ぐらい増える感じで、１万件、５万件、10万件と増えていきます。

当然追加(INSERT)する際は、PK(実際はインデックスなし）の存在を確認してInsert Or Updateな処理が必要になります。

件数が多くなるとトランザクションは必須です。

テストデータは、５万件超えのもので確認しています。
何も考えずに、select pk from tbl where pk=?的なSQLで検索して、存在しなければInsert、存在すればUpdateという処理にすると

（以下、msec=ミリ秒です。）

select発行コストは、１件あたり0.001sec(1msec)とごくわずかな時間でした。が、
UPDATE発行コストは、１件あたり0.06sec(60msec)もかかります。
１０件で、600msec、１００件で6秒、１０００件で１分・・・・うーん数は力です。

UPDATE発行コストを下げるには・・・

件数が少ない場合、無視できるコストですが、件数が１０万件、将来的に１００万件を超えるようなテーブルでは、無視できません＞＜

パッと思いつく方法は、以下２つでした
1. UPDATE対象専用のテーブルに、INSERT、まとめてUPDATEする方法
2. UPDATE対象専用のテーブルに、INSERT後、更新対象をTBLからDELETE、その後UPDATE対象＝＞TBLヘ

「1. UPDATE対象専用のテーブルに、INSERT、まとめてUPDATEする方法」
＝＞INSERTコストは全く気になりません。
　　update tbl set x = (select uptbl.x from uptbl where tbl.pk = uptbl.pk ) where exists (select uptbl.pk from uptbl where tbl.pk = uptbl.pk)
　　みたいなSQLでUPDATE対象専用のテーブル＝＞TBLへまとめて更新する方法は、
　　１件ずつUPDATEする方法とほぼ同等のコストでした。＞＜

「2. UPDATE対象専用のテーブルに、INSERT後、更新対象をTBLからDELETE、その後UPDATE対象＝＞TBLヘ」
＝＞ delete tbl from pk = ?というSQLで１件ずつ削除すると１件削除の平均コストは124msecでした
　　delete 超遅い＞＜


たった２つしか試していませんがw・・・
m(_ _)m UPDATEコストを低減させる方法は、見つかりませんでした。

ここまでを一言でまとめると・・
sqlite3ではUPDATE/DELETEはコストが高い！です。

できるだけ、UPDATE/DELETEを発行しないような仕組みに作り変えます。

sqlite3は、INSERT/ALTERを多用しようぜ！

ここまで確認している中で、INSERTコストは、全く気になりません。
トランザクションのメリットですね。

ということで、INSERT主体の処理に変更します。
ちなみに、私は、この方法で、５分かかっていた処理が 3秒になりましたw

元
beginTransaction
LOOP START
SELECT PK
INSERT OR UPDATE
LOOP END
commit

と、
１テーブルに対して、SELECTして、値を得た結果でINSERTかUPDATEで挿入、更新を行う処理だったものを、

beginTransaction
SELECTで全件検索
　更新対象となるキー　値を書き換えて UPTBLへINSERT（更新）
更新対象外　　　　　そのままUPTBLへINSERT（更新対象外）
SELECT END
commit

更新対象で処理していないものを全部UPTBLへINSERT (新規）
という流れに変更しました。

UPTBLは、CREATE TABLEでTransaction開始前に作成しています。

この変更点をサクっというと、
５万件のテーブルは触れずに、新たに５万件のテーブルを更新しながら作っちゃう作戦です。
繰り返しになりますが、UPDATEやDELETEを発行しない方法です。

でも、
５万件のデータでも、２、３秒で終わります。


この後、元々のTBLをDROP TABLE、
更新したUPTBLをTBLに変更します。
＝＞ALTER TABLE UPTBL RENAME TO TBLでUPTBLをTBLに変更しちゃいます。

私が検証した結果では、速度面でUPDATE/DELETEは使ってはいけない、代わりに新たにテーブル作っちゃえという結果になりました。


最後に・・・
検証は行っていますが、あくまで個人の環境です。
お手元にあるDBの特性に合わせてチューニングすることをお勧めします。

・・・・

ファイル系のデータベース、sqlite以外にもSQL Server CompactやAccess(ACE)などもあります。

sqliteは、SQL Server Compact(sdf)やOLEDBに比べて、とても簡単な印象を受けました。
PHPなどでは何もインストールしなくても使えるし、fts3,4の仮想テーブルを使うと型宣言も必要ないし、・・

万件データを扱うようになると、やはりSQLコストが問題になってきます。
likeや=での検索は、致命的に遅いです＞＜

なので、全文検索のmatchという選択にせざる終えませんが、
謎の挙動が多いので、https://sqlite.org/fts3.html を徹底的に読んだ方が良さそうです・・・



sqlite3のデータベースは、ディスクIO速度が性能に直結します。
可能であれば、SSDの領域にdbファイルを置いて処理すると格段にパフォーマンスが上げられます。
さらに余裕があれば、メモリ領域に置くことで高速化可能です。

SQL Server/Oracle/MySQL/PostgreSQLなども同様ですが、結局のところ、IOコストですね・・

変更来歴
2015/1/17 言葉足らずな文章だった箇所を修正しています。
2017/9/22 SQLite3のコツ10選を追加しました。

続きを見る方はこちらをクリックしてください