ランチャは、1つのワーカを各データベースに対しautovacuum_naptime秒ごとに開始するよう試みることにより、時間に対して作業を分散化します。

同時に最大autovacuum_max_workers個のプロセスが実行可能です。 この方法は、多版同時性制御(MVCC。第13章を参照してください)の恩恵を受けるために必要なものです。 データ 主キー:数値、値:文字列、レコード長:約 200 byte DBOnline ©2006-2019 Buzzword Inc.. All Rights Reserved. テーブルの容量を最小化しますが、長い時間がかかる可能性があります。 PostgreSQL のVACUUMコマンドは以下の理由により定期的にそれぞれのテーブルを処理しなければなりません。. また操作が終わるまで、テーブルの新しいコピー用に余計なディスク領域を必要とします。, 定常的なバキューム作業の通例の目安はVACUUM FULLの必要性を避けるに充分な頻度で標準VACUUMを行うことです。 This means the database file might be larger than strictly necessary. 以下で定義される閾値が、前回のANALYZEの後に挿入、更新、削除されたタプル数と比較されます。, 一時テーブルには自動バキュームでアクセスすることはできません。 このURL列の更新頻度も高いものかもしれませんが、その値の統計的な分布の変更は相対的に見ておそらく低いものです。, 特定のテーブルに対してANALYZEを実行することができます。 特に、テーブルに対応するpg_class行のrelfrozenxid列には、テーブルに対する最後のテーブル全体のVACUUMで使用された凍結切捨てXIDが含まれます。 しかし、トランザクションIDのサイズには制限(32ビット)があり、長時間(40億トランザクション)稼働しているクラスタはトランザクションの周回を経験します。 しかし、バキューム処理中はALTER TABLE ADD COLUMNなどのコマンドを使用してテーブル定義を変更することはできません。) ちなみに、 AirOne もsqliteが原因で表示速度が徐々に遅くなっていきます。遅いと感じたらスタートメニューの「表示用データの最適化」を実行してみてください。「表示用データの最適化」は、内部的にはsqliteのvacuum(ついでにanalyze)を実行しています。 ※2 組み込みモード (ローカルファイル永続化) のみ計測、インメモリモードやサーバーモードは未計測 テーブルのrelfrozenxid値がvacuum_freeze_table_ageトランザクション年齢より大きい場合、テーブル全体は古いタプルを凍結するため走査され、relfrozenxidを繰り上げます。 2つの目的があります。 問題が生じる可能性があります。, pragma freelist_count(freeになっているページ数)やpragma page_count(データベースのページ数)などの数値全体から判断します。 全ての旧XIDがFrozenXIDにより置き換えられたかどうかの確証を得るためには、全てのテーブルの走査が必要です。 XIDのカウンタが一周して0に戻り、そして、突然に、過去になされたトランザクションが将来のものと見えるように、つまり、その出力が不可視になります。 (負荷が高いと一部の情報が失われる可能性があることから、これはほぼ正確な数でしかありません。) 取り得る妥協案の1つは、いつになく激しい更新作業にのみ反応するよう、デーモンのパラメータを設定することです。 自動バキュームデーモンを使用することにより、この問題は緩和されます。 バキューム作業の基本. さもなければ、直前のVACUUMの後に不要となったタプル数が"バキューム閾値"を超えると、テーブルはバキュームされます。 可視性マップがヒープより非常に小さいため、ヒープが非常に大きい場合であっても簡単にキャッシュすることができます。, PostgreSQLのMVCCトランザクションのセマンティックは、トランザクションID(XID)番号の比較が可能であることに依存しています。 こういった列は、おそらく、例えば、あるWebサイト上のアクセスされたページのURLを保持する列よりも頻繁に統計情報を更新する必要があるでしょう。 不利な点は厳格なMVCC動作が違反となることです。, PostgreSQL問い合わせプランナは、優れた問い合わせ計画を作成するのに、テーブルの内容に関する統計情報に依存しています。 完全に作業量を予測することができない限り、デーモンを完全に無効化するのは勧められません。 単純な鉄則は、テーブル内の列の最小値、最大値にどのくらいの変化があったかを考えることです。 それぞれのワーカプロセスはデータベース内の各テーブルを検査し、必要に応じてVACUUMまたはANALYZEコマンドを発行します。, 短期間にいくつかの大規模なテーブルがすべてバキューム対象として適切な状態になったとすると、すべての自動バキュームワーカはこうしたテーブルに対するバキューム処理に長い期間占領される可能性があります。 (B) 「プログラム」に対する IBM または IBM 製品を提供する第三者 (以下「第三者」といいます。) から提供される最新の適用可能な更新、パッチ、修正が適用された所定の稼働環境で 「プログラム」を実行してベンチマーク・テストを行い 毎回vacuumを利用すると容量が少なくなり、却ってb-tree木のフラグメンテンーション化が進む恐れがあります。, データベースのリビルド前に既存のデータベースを一時ファイルにコピーするので、既存のDB以上のディスク空き容量が必要

デーモンは、挿入または更新された行数の関数としてANALYZEを厳密に計画します。 これは、全ての通常のXIDでは、20億の"より古い"XIDと20億の"より新しい"XIDが存在することを意味します。

SQLiteはファイルベース・追記型のデータベースなので、途中のデータを削除するとその部分が使用されずに残ります。

1つ目はバキューム自身が、整理するものがありませんので、こうしたページを次回飛ばすことができます。, 2つ目は、PostgreSQLが、背後にあるテーブルを参照することなく、インデックスのみを使用して一部の問い合わせに応えることができるようになります。 また、テーブルの特定の列のみに対してさえも実行することができます。 現在のトランザクションのXIDよりも新しい挿入時のXIDを持ったバージョンの行は、"未来のもの"であり、現在のトランザクションから可視であってはなりません。 SQLiteにはauto_vacuumという機能があるらしいので、 auto_vacuumを試してみようかと. 対照的にVACUUM FULLは、不要な領域のない全く新しいバージョンのテーブルファイルを書き出すことで、積極的にテーブルを圧縮します。 くっそ遅い!! insert終わるまで1時間以上かかるんですわ。 で、ちょっとググってみたところ、sqliteのinsertメソッド、内部でトランザクション管理してるみたいです。 なので、以下のようにやっちゃうと20万回トランザクション… WHERE句でよく使用され、データ分布の規則性がほとんどない列は、他の列よりもより細かいデータの度数分布が必要になるでしょう。

Unless SQLite is running in "auto_vacuum=FULL" mode, when a large amount of data is deleted from the database file it leaves behind empty space, or "free" database pages. PHP+PDOでsqlite3のデータベースを使うことがありました。 データ件数は、1日1000件ぐらい増える感じで、1万件、5万件、10万件と増えていきます。 当然追加(INSERT)する際は、PK(実際はインデックスなし)の存在を確認してInsert Or Updateな処理が必要になります。 件数が多くなるとトランザク … 以前から紹介しているsqliteは便利なデータベースです。このsqliteにもpostgreSQL同様にvacuumというコマンドが存在します。クライアントサーバを気にしない、ファイルベースのデータベースで、なぜこれが取りざたされないかが不思議です。 これを防ぐためには、すべてのデータベースにあるすべてのテーブルを少なくとも20億トランザクションごとにバキュームする必要があります。, 定期的なバキューム処理によりこの問題が解決する理由は、PostgreSQLが特別なXID、FrozenXIDを確保することです。 必要なディスク容量が無制限に増加しないように、これらが占める領域は、新しい行で再利用できるように回収されなければなりません。 有効にすると、自動バキュームは大量のタプルの挿入、更新、削除があったテーブルを検査します。 このため、ワーカの実稼働数に関らず、システムに与える総影響は変わりありません。. (VACUUMを起動してから始まったトランザクションの数分大きくなります)。 PostgreSQLデータベースはバキューム処理として知られている定期的な保守を必要とします。 テーブル全体は、relfrozenxidがvacuum_freeze_table_ageトランザクション年齢より大きい時、VACUUMのFREEZEオプションが使用された時、もしくは使用されない行バージョンを削除するため全てのページをバキュームしなければならなくなった時、走査されます。 従って、程よい頻度の標準VACUUMを実行するほうが、不定期のVACUUM FULLを実行するより大量の更新テーブルを保守するにはより良い取り組みとなります。, 例えば負荷が少ない夜間に全ての作業を行うように、一部の管理者は自身で計画したバキューム作業の方を選びます。 SQLiteはファイルベース・追記型のデータベースなので、途中のデータを削除するとその部分が使用されずに残ります。 また、SQLiteはデータが追加されるごとにファイルサイズを伸ばしていくので、b-tree木が断片化されていきます。 例えば、行の更新時刻を保持するtimestamp列の場合、最大値は行が追加、更新されるにつれて、単純に増加します。 このXIDは通常のXIDの比較規則には従わず、常に全ての通常のXIDよりも古いものとみなされます。 より詳細な情報は格納パラメータを参照してください。 テーブル行がすぐに変更される場合(新しいXIDを獲得することになります)、テーブル行のXIDをFrozenXIDに変更することは時間の無駄です。

しかし、(挿入のみで更新や削除が行われないテーブルを含む)静的なテーブルでは、領域確保のためのバキューム処理を行う必要がなくなりますので、非常に長期間静的なテーブルでは、強制的な自動バキューム間の間隔を最大まで延ばすことができます。 ALTER TABLE SET STATISTICSを参照するか、default_statistics_target設定パラメータでデータベース全体のデフォルトを変更してください。, またデフォルトで、関数の選択性に関して利用可能な制限付きの情報があります。 VACUUM FULLはそれが作用する全てのテーブルに対し排他ロックを必要とするので、それらテーブルのその他の用途と並行して行うことはできません。

これを確実に防止するために、自動バキュームがautovacuum_freeze_max_age設定パラメータで指定された時代より古いXIDを持つ可能性がある任意のテーブルに対して呼び出されます。 また、単一データベースに対するワーカ数には制限はありませんが、ワーカはすでに他のワーカによって実行された作業を繰り返さないように試みます。 あるバージョンの行は他のトランザクションから参照される可能性がある場合は削除されてはなりません。 これにより、抑制可能な範囲を維持しつつ、負荷が標準的な場合に計画化されたVACUUMがまとめて作業を行うことを想定することができます。, 自動バキュームを使用しない場合の典型的な方式は、データベース全体のVACUUMを1日1回使用頻度が低い時間帯にスケジュールすることです。 そのため、この設定は、行の変更が起こらなくなるまで凍結されない程度に大きくすべきです。 この特殊なXIDに割り当てられた後は、周回問題に関係なく、全ての通常のトランザクションから"過去のもの"として認識され、また、そのバージョンの行はどれだけ古いものであろうと、削除されるまで有効状態となります。 $ sqlite3 データベース "vacuum;" lockedの状態のデータベースファイルは、このコマンドを拒否します。 SQL error: database is locked というメッセージが出力されます。 問題なければ、VACUUMにより空き領域が解放されます。データベースサイズが膨れていくので定期的にVACUUMコマンドを実行するの … 多くのインストレーションでは、項23.1.6で説明されている自動バキュームデーモンでのバキューム処理を行わせることで充分です。 更新、あるいは削除された行によって占められたディスク領域の復旧または再利用。 ワーカの実行数はmax_connections制限にもsuperuser_reserved_connections制限にも計上されないことに注意してください。, テーブルのrelfrozenxid値がautovacuum_freeze_max_ageトランザクション年齢よりも古い場合、そのテーブルは常にバキュームされます

autovacuum_freeze_min_age、autovacuum_freeze_max_ageおよびautovacuum_freeze_table_ageは、それぞれvacuum_freeze_min_age、autovacuum_freeze_max_ageおよびvacuum_freeze_table_ageに対する値を設定するために使用されます。, 複数のワーカプロセスの実行中、コスト制限は実行中のワーカ全体に"振り分け"られます。 一方で、この値を小さくすることで、テーブルを次にバキュームする必要が起こるまで継続できるトランザクション数を増加させることができます。, VACUUMは通常もはや使用されない行バージョンを持っていないページを読み飛ばしますが、それらのページは旧XID値の行バージョンを未だ所有している可能性があります。 autovacuum_freeze_max_ageの範囲まですべてのトランザクションのコミット状況を格納しなければならないためです。 これは、そのデータベース内のテーブル当たりのrelfrozenxid値の最小値です。 下記のようなシステムでパフォーマンスが良さげな SQLite を使用予定ですが、もっと速いものが無いか確認のため他のデータベースのパフォーマンスを計測してみました。SQL 利用前提ですが、NoSQL が圧倒的な性能を出す場合は検討する必要があるので KVS も確認しました。, ※1 JDBC 操作に関して INSERT は 1 万件ごとにコミット、SELECT は主キー指定で 1 件ずつ全件取得 (C) 「プログラム」の資料ならびに IBM がサポートする「プログラム」用の Web サイトで提供されているすべてのパフォーマンス・チューニングおよび最良の方法に従うこと, 今回の計測対象のメインとなる JDBC のソースはこちらになります。INSERT のあとの SELECT なのでキャッシュ云々の話もありますが、それも含めて各データソースに対して同じ操作をしています。SQLite のみ Class.forName しているのはドライバがサービスプロバイダーフレームワークに対応していないためです。(bitbucket リポジトリの最新ソースでは 2012/09 対応), *3:2012/10/14 12:15 @showyou さんの指摘によりビット数追記, *5:2012/10/14 12:15 @showyou さんの指摘によりビット数追記, *6:2012/10/14 12:15 @showyou さんの指摘により型番追記, *7:2012/10/14 11:57 @shiumachi さんの指摘により修正, cypher256さんは、はてなブログを使っています。あなたもはてなブログをはじめてみませんか?, Powered by Hatena Blog そうでなければ最後のバキュームの後に変更されたページのみ走査されます。, 解析でも似たような条件が使用されます。 それぞれの状況に合った最善の結果を得るため、そこで説明する自動バキューム用パラメータの調整が必要かもしれません。 この2つの理由により、この設定を減らすことは完全に静的なテーブルでない限りお勧めしません。, データベース内のもっとも古いXIDの年代を追跡するために、VACUUMはシステムテーブルpg_classとpg_databaseにXID統計情報を保持します。 自動バキュームランチャという永続的デーモンプロセスが存在し、自動バキュームワーカプロセスがすべてのデータベースを処理します。 スレッド数 1, データベースの処理数に対する処理時間は、上記結果の MySQL に見られるような線形的増加 (リニア、直線的) に増加するパターンと、Cassandra のように指数関数的 (雪ダルマ式) に増大するパターンがあります。また、線形的増加から指数関数的増加に移行する臨界点や動作不能になる臨界点がある場合が多いです。1 万件での処理時間はこうだから 1 億件の予想処理時間はその 1 万倍というような情報をたまに見かけますが、件数が増大するほど線形的増加ではないほうが多いと思います。, SQLite が INSERT、SELECT とも予想以上に他を圧倒し高速でした。計測結果に示すとおり 1 億件程度のデータ (データファイルサイズは 1.8 GB でした) なら大丈夫そうです。H2 のサイトに H2 が最速とするパフォーマンス比較 (H2) がありますが SQLite はトランザクションがテストされていないという理由で比較されていません。ちなみに実際には SQLite はトランザクションをサポートしていて、テストに関しても SQLite 本体コード 6万7000行 に対しテストコードは 4567万8000行 (publickey) だそうです。古い情報ですが、SQLiteを使うべき10の理由と5つのデメリット (CAP-LAB テクニカル) が色々参考になります。, SSD で試してみたところ、1 億件 INSERT 647秒、1 億回 SELECT 383秒 でした。さすがに速い。SSD 環境は CPU i5 2.4GH、メモリ 8GB、Windows 7 64bit*5、SSD (TOSHIBA THNSNC128GMMJ)*6、これもノートパソコンです。, EHCache は 1 万件取得で最速ですが、件数が増えると組み込み RDB の SQLite や H2 より遅いのは良い?にしても、クライアント・サーバー型 KVS の Mongodb や Redis より総合的に少し遅いのは予想外でした。overflowToDisk の設定にもよると思いますが、知らずに H2 や Mongodb のキャッシュとして EHCache を使ってまいそうです。すべてディスクに永続化するように設定していたのですが、100万件テストでは登録したデータを取得しようとしたときに欠落している場合があるため計測しませんでした。ところでロゴを見て気づいたのですが EHCache が回文になっているのを今初めて知りました。, 最速の SQLite と比較すると登録は 6 倍かかり、取得は 20 倍かかりますが、クライアント・サーバー型としては最も登録性能に優れています。ただし、分散環境ではデータがときどき消える、マルチコアでスケールしない (InfoQ)、などに注意する必要があります。もちろん、これらの問題は将来解消されるかもしれません。, 分散 KVS をスタンドアロンの組み込み RDB と比較するのもあれですが、テストした環境での 100万件 SELECT は SQLite の 200 倍以上遅いです。分散によるネットワーク負荷係数を 2 とした場合、サーバーを 400 台程度用意すれば SQLite と同等の性能を出せるかもしれません。一時期 Twitter や Facebook が Cassandra をメインに使用することを検討していましたが断念しました。ちなみに Cassandra を元々開発したのは Facebook です。, NoSQL が終焉したと言われて久しいですが、Google のインフラ基盤や Facebook の HBase (Hadoop) で HBase (Hadoop データベース)*7 がバリバリ使われていて、Mixi では永続化機能付き memcached とも言える TokyoTyrant が使用され、DeNA では MySQL に HandlerSocket を組み込み NoSQL を実現しています。必要な場面があるので今後も NoSQL や KVS が無くなることはなく、逆に RDB も衰退することはありません。Twitter は今でも MySQL + memcached ですし、GAE では MySQL も用意されています。また、NoSQL に対して SQL が遅いというアンチテーゼとして、Postgres の設計者による分散インメモリ RDB の VoltDB が登場しています。, 業務システムから見ると SQL はそのままでスケールアウトしたいという要望に単純な NoSQL や KVS は合致しません。元々 RDB がスケールアウトによりリニアにスケールしない問題や耐障害性を解決するために Google の BigTable などにインスパイアされ登場したのが分散 KVS ですが、現在は RDB に NoSQL や KVS の要素が取り込まれています。例えば、MySQL 5.6 では memcached や HandlerSoclet などの概念が取り込まれ SQL なしで高速アクセス可能になっています。また MySQL Cluster や Oracle Exadata は分散 KVS と同じようにリニアにスケールアウト可能になっており、Postgres も 32 コア CPU 対応などスケールアップ可能になっています。, 計測結果はあくまでも私が必要な環境と条件の計測結果であり、id:matsumoto_r さんがおっしゃられているとおりデータベースそのものの優劣を示しているものではありません。並行アクセスしたらとか、設定変えたらとか、たくさんご意見いただきましたが、もちろん、負荷条件を変えたりそれぞれ環境に合わせてチューニングすれば結果は変わると思います。今回の想定システムでは並行アクセスはほぼ発生せず意味がないのでマルチスレッドでの計測はせず、シングルスレッドでのデータ永続化の基本性能を確認するために実施しました。並行処理が得意(前提)とされるデータベースをシングル構成で計測に追加したのは差を確認するためです。並行性能の情報はたくさん公開されていると思いますが、ご自分の環境に合う方法で計測していただければと思います。, なお、ソフトウェアやドライバはすべて最新安定版、設定はデフォルトです。ただし、全データのディスク同期が前提の計測であるため、EHCache はインメモリではなくディスク永続化モードです。ちなみに SQLite はもう少し触ってみたところ Beta 版にすると 20% 高速化、非同期モードにするとさらに 10% 高速化しました。, 私は臆病者なので良い結果を出さないと Oracle に怒られる気がするからです。OTN ライセンスに下記の条項が含まれています。, 蛇足ですが、DB2 は設定を完璧にして最新パッチをあてて最高性能を出さないと怒られるかもしれません。, (A) ベンチマーク・テストで使用した方法 (例えば、ハードウェアおよびソフトウェアのセットアップ、導入手順および構成ファイル) を公開し、

ストレージパラメータで設定が変更されるとその値が使用されます。 言い換えると、通常のXID空間は終わることなく循環されているということです。 VACUUM のバリエーションはいろいろあるものの、結論としては $ sqlite filename.db vacuum とやるのが一番無難。 これまでは個人用途のしょうもないプログラムのためにしか使って来なかったため、スルーしていた SQLite の VACUUM 機能をメモ。 記載するまでもありませんが、autovacuum_freeze_max_ageを増やすことでもvacuum_freeze_min_ageを減らすことでも、これを行うことができます。, vacuum_freeze_table_ageに対する有効な最大値は0.95 * autovacuum_freeze_max_ageです。

可視性マップがディスクアクセスを防ぐことができるデータ群が大規模である場合、特に顕著です。 一方より低い値はより頻繁なテーブル全体の走査を引き起こします。, autovacuum_freeze_max_age(およびそれに付随するvacuum_freeze_table_age)を増やす唯一の欠点は、データベースクラスタのサブディレクトリpg_clogがより大きな容量となることです。 (非常に高い頻度でデータの更新を行うインストレーションの中では、分間隔位という頻度で高負荷なテーブルのVACUUMを行うこともあります。) このバキューム閾値は以下のように定義されます。, ここで、バキューム基礎閾値はautovacuum_vacuum_threshold、バキューム規模係数はautovacuum_vacuum_scale_factor、タプル数はpg_class.reltuplesです。 SQLite のデータベースでファイルの追加や削除を繰り返していくと、格納されているデータの量に比例しないようなファイルのサイズになることがあります。ここでは VACUUM 文を使って使用していない領域を解放する方法について解説します。 0に設定することでVACUUMは可視性マップを実質無視し、常に全てのページを走査するようになります。, テーブルをバキュームすることなく処理できる最大の時間は、20億トランザクションからVACUUMが前回テーブル全体を走査した時点のvacuum_freeze_min_ageの値を差し引いたものです。 データベース管理者によっては、cronもしくはタスクスケジューラスクリプトに従って典型的に実行される、手作業管理のVACUUMコマンドによりデーモンの活動を補足したり、置き換えたりすることを意図するかもしれません。 そして実際VACUUM FULLを行いません。

これによりVACUUMが大して役に立たなくなるかもしれません。 (デフォルトは2億トランザクションです。換算するとpg_clogはおよそ50メガバイトの容量となります。), vacuum_freeze_min_age を減らすことにも1つ欠点があります。 しかし、実際には、操作が高速であるため、単にデータベース全体を解析することが最善です。 この停止モードはシングルユーザモードのバックエンドでは強制されません。