PostgreSQLのパフォーマンス改善用のメモ
個人的に色々と参考させていただいているものをまとめました。
HugePages
オンプレのLinuxは有効にすることを検討する。
- Improving PostgreSQL performance without making changes to PostgreSQL
- Cloud SQL for PostgreSQL が新たに Linux Huge Pages をサポート | Google Cloud 公式ブログ
- 18.4. カーネルリソースの管理
- 18.4.5. LinuxのHugePages
- 19.4. 資源の消費
- huge_pages
インデックス
カバリングインデックスの作成も検討する。
ただ、インデックスオンリースキャンを狙いにいっても可視性の情報はみないといけないので更新が多いテーブルは注意する。
- 11.9. インデックスオンリースキャンとカバリングインデックス
- パフォーマンスを考慮したIndex定義設計 | TECHSCORE BLOG
- データベースのインデックスの種類と内部の仕組み｜SQL Serverで数億規模のデータ量を扱ってみて | 釣りキチ翔平の備忘録
FILLFACTOR
更新が多いテーブルはFILLFACTORを設定することを検討する。
ロック管理
場合によるが、deadlock_timeoutの変更を検討する。
デッドロック検出処理が同時セッション数の分行われサーバ負荷が高くなってどうにもならなくなる可能性を考慮する。
ユーザ定義関数が返す行数の推定値
デフォルト推定値（ROWS result_rows）は1000行になっているので変更することを検討する。
- CREATE FUNCTION
- 37.11. 関数最適化に関する情報
- 12からドキュメントがある
パーティショニング
結構データ量ないと意味ない感じと思われる（11系で約100万行だと変わらなかった）
意味ある状況に遭遇したい。
- パーティショニングの概要｜PostgreSQLインサイド : 富士通
- 利用するなら12系以上は望ましい
- MySQL のパーティショニングで速くなる？ならない？問題、あらためて実験してみた - Qiita
全体的に参考にしているもの
- 2018年度WG3活動報告書 性能トラブル調査編
- https://dbstudychugoku.github.io/pdf/20140713_postgre_tuning.pdf
- https://www.postgresql.jp/sites/default/files/2017-01/T5_jissen_PostgreSQL_unyou.pdf
- https://pgecons-sec-tech.github.io/tech-report/presentation/PGECons_20181018_parallel.pdf
- https://www.pgecons.org/wp-content/uploads/PGECons/2013/WG2/11_PerformanceTuning.pdf
- https://www.pgecons.org/wp-content/uploads/2021/09/50a72f0dfa24a14d9aa77020f550f2a0.pdf
- https://pgecons-sec-tech.github.io/tech-report/pdf/wg2_PostgresSelfStudyBook.pdf
- パフォーマンスチューニング9つの技 ～はじめに～｜PostgreSQLインサイド : 富士通
バージョンアップによる改善メモ
リリースノートを見て気になったものを抜粋。
あと、11からバージョンアップする場合はJITの挙動に注意する。
11 → 12
- E.5. リリース12
- https://www.sraoss.co.jp/tech-blog/wp-content/uploads/2019/10/pg12_report_1004.pdf
- 4.5. インデックスの機能追加・性能改善
- 4.6. パーティショニングの改善
- 12から性能が明らかに向上
- 4.7. WITH 句の最適化
- 場合によってはインデクススキャンになり得る
- PostgreSQL 12 に関する技術情報
12 → 13
- E.2. リリース13
- https://www.sraoss.co.jp/tech-blog/wp-content/uploads/2020/12/pg13_report_1203.pdf
- 4.1. B-Tree インデックスの性能向上
- 4.2.1. インクリメンタルソート
- PostgreSQL 13 に関する技術情報
13 → 14
- E.6. リリース 14
- https://www.sraoss.co.jp/tech-blog/wp-content/uploads/2021/08/pg14_report_20210818.pdf
- 4.1.1. Btree インデックスの肥大化抑止
- 4.1.5. ロジカルレプリケーションの改善
- 4.1.6. パラレルクエリ対応の拡張
- PostgreSQL 14 に関する技術情報
Chrome拡張機能をCreate React Appを使って作る
Chrome拡張機能を作ってみようと思いました。
とりあえずTypeScriptの方がいいかと思ったり、Reactでも使ってみるかと思いました。
検索するとスケルトン的なのを公開してくれている方もいたのですが、若干バージョン古くなってしました。
少し考えた結果、Create React App使えば良くないと思ったのでメモを残します。
Node.jsのインストール
いろいろ参考になる記事あると思うので、参考にしてインストールします。
$ node -v v16.15.1
Create React App
表題の通りcreate-react-app使います。
$ npx create-react-app my-app --template typescript
manifest.jsonを修正
Chrome拡張用に修正というか置き換えます。
$ cat > public/manifest.json <<EOF { "name": "__MSG_ext_name__", "description": "__MSG_ext_desc__", "version": "1.0", "manifest_version": 3, "default_locale": "ja", "icons": { "16": "logo192.png", "48": "logo192.png", "128": "logo192.png" }, "action": { "default_popup": "index.html" } } EOF
細かい説明はドキュメントに任せますが、とりあえずindex.htmlをpopupとして使用する形にしています。popup.htmlの方がいいかもしれません。
権限とかは作成する拡張によるので、実装時に調整します。
多言語化はしないと思いますが、たまたま目についてしまったので対応しときます。
$ mkdir -p public/_locales/{en,ja} $ cat > public/_locales/en/messages.json <<EOF { "ext_name": { "message": "Extension Name" }, "ext_desc": { "message": "Extension Name" } } EOF $ cat > public/_locales/ja/messages.json <<EOF { "ext_name": { "message": "拡張機能の名前" }, "ext_desc": { "message": "拡張機能の名前" } } EOF
Chrome拡張向けに微調整
他にも微調整必要ではというツッコミはなしとして
Chrome拡張には不要なファイルを削除する
$ rm public/{favicon.ico,logo512.png,robots.txt}
@types/chromeをインストール
$ npm install @types/chrome --save-dev
ビルド
$ npm run build
Chrome拡張機能の「パッケージ化されていない拡張機能を読み込む」で、buildディレクトリを指定します。
これで動くっぽいことを確認できます。
https://developer.chrome.com/docs/extensions/mv3/getstarted/#unpacked
実装
日付型が条件にあるSQLをライブラリを介して発行した際に困ったことになった
とある言語のライブラリをロクに検証せず使っていたら困ったことになって、そこ気にしないといけないのだっけと思った次第です。無知なだけです。
PostgreSQLを使っています。DATE 型のカラムに対してライブラリを介して日付型のパラメータを渡した際の挙動どうなるかを確認して行きたいと思います。
準備
postgres=# SELECT version(); -[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------ version | PostgreSQL 14.2 (Debian 14.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit CREATE TABLE sample ( id serial, name text, start_date date NOT NULL, end_date date NOT NULL, start_datetime timestamp without time zone NOT NULL, end_datetime timestamp without time zone NOT NULL, PRIMARY KEY (id) ); -- データ登録 INSERT INTO sample (name, start_date, end_date, start_datetime, end_datetime) VALUES ('Alice', CURRENT_DATE, CURRENT_DATE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
※ CURRENT_DATEとCURRENT_TIMESTAMPを利用してデータを登録していますが、途中外出して日付変わってしまったので入れなおしてます。
PostgreSQLで確認
いったん素のSQLで念のため確認します。
CURRENT_DATEで確認（DATE = DATE）
-- CURRENT_DATEとCURRENT_TIMESTAMPを型を変換するパターンで確認 postgres=# SELECT * FROM sample WHERE CURRENT_DATE BETWEEN start_date AND end_date; id | name | start_date | end_date | start_datetime | end_datetime ----+-------+------------+------------+----------------------------+---------------------------- 1 | Alice | 2022-05-28 | 2022-05-28 | 2022-05-28 10:54:26.129743 | 2022-05-28 10:54:26.129743 (1 row)
これはDATEとDATEで時分秒は関係ないので1件取得できました。期待通り。
CURRENT_DATE::TIMESTAMPで確認（TIMESTAMP = DATE）
postgres=# SELECT * FROM sample WHERE CURRENT_DATE::TIMESTAMP BETWEEN start_date AND end_date; id | name | start_date | end_date | start_datetime | end_datetime ----+-------+------------+------------+----------------------------+---------------------------- 1 | Alice | 2022-05-28 | 2022-05-28 | 2022-05-28 10:54:26.129743 | 2022-05-28 10:54:26.129743 (1 row)
これはTIMESTAMPとDATEですが、'2022-05-28 00:00:00'になると思っているので1件取得できました。期待通り。
postgres=# EXPLAIN SELECT * FROM sample WHERE CURRENT_DATE::TIMESTAMP BETWEEN start_date AND end_date; -[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Seq Scan on sample (cost=0.00..33.00 rows=102 width=60) -[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------------------------- QUERY PLAN | Filter: (((CURRENT_DATE)::timestamp without time zone >= start_date) AND ((CURRENT_DATE)::timestamp without time zone <= end_date)) postgres=# SELECT (CURRENT_DATE)::timestamp; -[ RECORD 1 ]+-------------------- current_date | 2022-05-28 00:00:00
CURRENT_TIMESTAMPで確認（TIMESTAMP = DATE）
postgres=# SELECT * FROM sample WHERE CURRENT_TIMESTAMP BETWEEN start_date AND end_date; id | name | start_date | end_date | start_datetime | end_datetime ----+------+------------+----------+----------------+-------------- (0 rows)
これはTIMESTAMPとDATEで、'2022-05-28 10:59:26'などになるので取得できません。期待通り。
CURRENT_TIMESTAMP::DATEで確認（DATE = DATE）
postgres=# SELECT * FROM sample WHERE CURRENT_TIMESTAMP::DATE BETWEEN start_date AND end_date; id | name | start_date | end_date | start_datetime | end_datetime ----+-------+------------+------------+----------------------------+---------------------------- 1 | Alice | 2022-05-28 | 2022-05-28 | 2022-05-28 10:54:26.129743 | 2022-05-28 10:54:26.129743 (1 row)
これはDATEとDATEで、'2022-05-28 10:59:26'::DATEなどが'2022-05-28'::DATEで取得できました。期待通り。
postgres=# SELECT (CURRENT_TIMESTAMP)::date; -[ RECORD 1 ]-----+----------- current_timestamp | 2022-05-28
日時部分を文字列でDATEに型変換で確認（DATE = DATE）
postgres=# SELECT * FROM sample WHERE '2022-05-28 10:59:26'::DATE BETWEEN start_date AND end_date; id | name | start_date | end_date | start_datetime | end_datetime ----+-------+------------+------------+----------------------------+---------------------------- 1 | Alice | 2022-05-28 | 2022-05-28 | 2022-05-28 10:54:26.129743 | 2022-05-28 10:54:26.129743 (1 row)
これはDATEとDATEで時分秒は関係ないので1件取得できました。期待通り。
日時部分を文字列でTIMESTAMPに型変換で確認（TIMESTAMP = DATE）
postgres=# SELECT * FROM sample WHERE '2022-05-28 10:59:26'::TIMESTAMP BETWEEN start_date AND end_date;~ id | name | start_date | end_date | start_datetime | end_datetime ----+------+------------+----------+----------------+-------------- (0 rows)
これはTIMESTAMPとDATEで、'2022-05-28 10:59:26'などになるので取得できません。期待通り。
日時部分を文字列で明示的な型変換なしで確認（? = DATE）
postgres=# SELECT * FROM sample WHERE '2022-05-28 10:59:26' BETWEEN start_date AND end_date; id | name | start_date | end_date | start_datetime | end_datetime ----+-------+------------+------------+----------------------------+---------------------------- 1 | Alice | 2022-05-28 | 2022-05-28 | 2022-05-28 10:54:26.129743 | 2022-05-28 10:54:26.129743 (1 row)
これは'2022-05-28 10:59:26'が暗黙の型変換でDATEになり取れているということですかね。
postgres=# EXPLAIN SELECT * FROM sample WHERE '2022-05-28 10:59:26' BETWEEN start_date AND end_date; -[ RECORD 1 ]----------------------------------------------------------------------------------- QUERY PLAN | Seq Scan on sample (cost=0.00..23.80 rows=102 width=60) -[ RECORD 2 ]----------------------------------------------------------------------------------- QUERY PLAN | Filter: (('2022-05-28'::date >= start_date) AND ('2022-05-28'::date <= end_date))
EXPLAINで見ると'2022-05-28'::dateとキャストされているようです。
ライブラリを経由してみる
C#のものは今回の話の流れとして必要でしたが、他は本当は色々試したかったけど、という感じです。
C#
EF Core(.NET6)
$ dotnet --version 6.0.300 # 新しいプロジェクトを作成する $ dotnet new console -o EFGetStarted
リバース エンジニアリング - EF Core | Microsoft Docs
$ dotnet tool install --global dotnet-ef $ dotnet add package Microsoft.EntityFrameworkCore.Design # モデルを作成する $ dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL $ dotnet ef dbcontext scaffold "Host=localhost;Port=15432;Database=postgres;Username=postgres;Password=password" Npgsql.EntityFrameworkCore.PostgreSQL
namespace EFGetStarted { public partial class Sample { public int Id { get; set; } public string? Name { get; set; } public DateOnly StartDate { get; set; } public DateOnly EndDate { get; set; } public DateTime StartDatetime { get; set; } public DateTime EndDatetime { get; set; } } }
ちなみにDateOnlyは.Net6からです。
※ usingは省略しています。
namespace EFGetStarted; internal class Program { private static void Main() { using (var db = new postgresContext()) { var now = DateOnly.FromDateTime(DateTime.Now); Console.WriteLine(now); var sample = db.Samples .Where(s => s.StartDate >= now && s.EndDate <= now) .First(); Console.WriteLine($"{sample.Id} {sample.Name} {sample.StartDate} {sample.EndDate} {sample.StartDatetime} {sample.EndDatetime}"); } } }
DateOnly.FromDateTime(DateTime.Now)を使って範囲検索してみます。
$ dotnet run 2022/05/28 1 Alice 2022/05/28 2022/05/28 2022/05/28 10:54:26 2022/05/28 10:54:26
DateOnly型を明示的に使う側で渡しているのもあり、期待した形で取得できました。
EF Core(.NET Core 3.1)
使用する .NET のバージョンを選択する - .NET | Microsoft Docs
$ dotnet new globaljson --sdk-version 3.1.419 $ dotnet --version 3.1.419 # 新しいプロジェクトを作成する $ dotnet new console -o ../EFGetStarted3.1
$ dotnet new tool-manifest $ dotnet tool install dotnet-ef --version 3.1.25 $ dotnet add package Microsoft.EntityFrameworkCore.Design --version 3.1.25 # モデルを作成する $ dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 3.1.18 $ dotnet ef dbcontext scaffold "Host=localhost;Port=15432;Database=postgres;Username=postgres;Password=password" Npgsql.EntityFrameworkCore.PostgreSQL
namespace EFGetStarted3._1 { public partial class Sample { public int Id { get; set; } public string Name { get; set; } public DateTime StartDate { get; set; } public DateTime EndDate { get; set; } public DateTime StartDatetime { get; set; } public DateTime EndDatetime { get; set; } } }
DateOnlyは.Net6からなのでDateTimeになりました。
namespace EFGetStarted3._1 { class Program { static void Main(string[] args) { Console.WriteLine("Now"); var now = DateTime.Now; Test(now); Console.WriteLine("Today"); var today = DateTime.Today; Test(today); } static void Test(DateTime date) { Console.WriteLine(date); using (var db = new postgresContext()) { var sample = db.Sample .Where(s => s.StartDate >= date && s.EndDate <= date) .First(); Console.WriteLine($"{sample.Id} {sample.Name} {sample.StartDate} {sample.EndDate} {sample.StartDatetime} {sample.EndDatetime}"); } } } }
DateTime.NowとDateTime.Todayを使って範囲検索してみます。
$ dotnet run Now 2022/05/28 17:05:40 1 Alice 2022/05/28 0:00:00 2022/05/28 0:00:00 2022/05/28 10:54:26 2022/05/28 10:54:26 Today 2022/05/28 0:00:00 1 Alice 2022/05/28 0:00:00 2022/05/28 0:00:00 2022/05/28 10:54:26 2022/05/28 10:54:26
両方とも検索できました。
2022-05-29 15:06:47.599 JST [107] LOG: execute <unnamed>: SELECT s.id, s.end_date, s.end_datetime, s.name, s.start_date, s.start_datetime FROM sample AS s WHERE (s.start_date >= $1) AND (s.end_date <= $1) LIMIT 1 2022-05-29 15:06:47.599 JST [107] DETAIL: parameters: $1 = '2022-05-29' 2022-05-29 15:06:47.599 JST [107] LOG: duration: 0.014 ms plan: Query Text: SELECT s.id, s.end_date, s.end_datetime, s.name, s.start_date, s.start_datetime FROM sample AS s WHERE (s.start_date >= $1) AND (s.end_date <= $1) LIMIT 1 Limit (cost=0.00..0.23 rows=1 width=60) -> Seq Scan on sample s (cost=0.00..23.80 rows=102 width=60) Filter: ((start_date >= '2022-05-29'::date) AND (end_date <= '2022-05-29'::date))
PostgresのログをみるとパラメータがNowの時でも'2022-05-29'となってました。
Dapper
$ dotnet --version 6.0.300 # 新しいプロジェクトを作成する $ dotnet new console -o ../DapperGetStarted
$ dotnet add package Dapper --version 2.0.123 $ dotnet add package Npgsql --version 6.0.4
エンティティはEF Coreと同じものを使用しています。
namespace DapperGetStarted { class Program { static async Task Main(string[] args) { Console.WriteLine("Now"); var now = DateTime.Now; await TestAsync(now); Console.WriteLine("Today"); var today = DateTime.Today; await TestAsync(today); } static async Task TestAsync(DateTime date) { Console.WriteLine(date); using (var connection = new NpgsqlConnection("Host=localhost;Port=15432;Database=postgres;Username=postgres;Password=password")) { var sample = (await connection.QueryAsync<Sample>( "SELECT * FROM sample WHERE start_date >= @start_date AND end_date <= @end_date", new { start_date = date, end_date = date })).FirstOrDefault(); if (sample != null) { Console.WriteLine($"{sample.Id} {sample.Name} {sample.StartDate} {sample.EndDate} {sample.StartDatetime} {sample.EndDatetime}"); } } } } }
同じくDateTime.NowとDateTime.Todayを使って範囲検索してみます。
$ dotnet run Now 2022/05/28 23:01:55 Today 2022/05/28 0:00:00 1 Alice 0001/01/01 0:00:00 0001/01/01 0:00:00 0001/01/01 0:00:00 0001/01/01 0:00:00
DateTime.Nowは取得できません。型情報はDateTime以外わからないので、こうことになってしまう感じですかね。
# DateTime.Now 2022-05-29 15:12:20.015 JST [114] LOG: execute <unnamed>: SELECT * FROM sample WHERE start_date >= $1 AND end_date <= $2 2022-05-29 15:12:20.015 JST [114] DETAIL: parameters: $1 = '2022-05-29 15:12:19.474152', $2 = '2022-05-29 15:12:19.474152' 2022-05-29 15:12:20.019 JST [114] LOG: duration: 3.971 ms plan: Query Text: SELECT * FROM sample WHERE start_date >= $1 AND end_date <= $2 Seq Scan on sample (cost=0.00..23.80 rows=102 width=60) Filter: ((start_date >= '2022-05-29 15:12:19.474152'::timestamp without time zone) AND (end_date <= '2022-05-29 15:12:19.474152'::timestamp without time zone)) # DateTime.Today 2022-05-29 16:30:03.040 JST [210] LOG: execute <unnamed>: SELECT * FROM sample WHERE start_date >= $1 AND end_date <= $2 2022-05-29 16:30:03.040 JST [210] DETAIL: parameters: $1 = '2022-05-29 00:00:00', $2 = '2022-05-29 00:00:00' 2022-05-29 16:30:03.040 JST [210] LOG: duration: 0.009 ms plan: Query Text: SELECT * FROM sample WHERE start_date >= $1 AND end_date <= $2 Seq Scan on sample (cost=0.00..23.80 rows=102 width=60) Filter: ((start_date >= '2022-05-29 00:00:00'::timestamp without time zone) AND (end_date <= '2022-05-29 00:00:00'::timestamp without time zone))
パラメータは「timestamp」でキャストされている。
また、DateOnlyは「Unhandled exception. System.NotSupportedException: The
member start_date of type System.DateOnly cannot be used as a parameter value」でまだ使えないみたいです。
Rails(Ruby)
ActiveRecord
$ rails db:schema:dump
$ rails generate model Sample --skip-migration
テーブル名sampleにしてしまっていたって気づきました。
$ rails c irb(main):001:0> now = Time.now => 2022-05-29 15:17:06.6508589 +0900 irb(main):002:0> Sample.where("start_date >= ?", now).where("end_date <= ?", now) Sample Load (2.9ms) SELECT "sample".* FROM "sample" WHERE (start_date >= '2022-05-29 06:17:06.650858') AND (end_date <= '2022-05-29 06:17:06.650858') => [#<Sample:0x00007fea7fcf6608 id: 1, name: "Alice", start_date: Sun, 29 May 2022, end_date: Sun, 29 May 2022, start_datetime: Sun, 29 May 2022 14:04:26.409945000 UTC +00:00, end_datetime: Sun, 29 May 2022 14:04:26.409945000 UTC +00:00>]
WHERE (start_date >= '2022-05-29 06:17:06.650858')ってなってるけど、取得できている。
2022-05-29 15:17:13.364 JST [120] LOG: execute <unnamed>: SELECT "sample".* FROM "sample" WHERE (start_date >= '2022-05-29 06:17:06.650858') AND (end_date <= '2022-05-29 06:17:06.650858') 2022-05-29 15:17:13.364 JST [120] LOG: duration: 0.010 ms plan: Query Text: SELECT "sample".* FROM "sample" WHERE (start_date >= '2022-05-29 06:17:06.650858') AND (end_date <= '2022-05-29 06:17:06.650858') Seq Scan on sample (cost=0.00..23.80 rows=102 width=60) Filter: ((start_date >= '2022-05-29'::date) AND (end_date <= '2022-05-29'::date))
Dapperとは異なりパラメータ直接埋まってます。
TypeScript
Prisma
Add Prisma to an existing project | Prisma Docs
# Set up Prisma $ npm install prisma --save-dev $ npx prisma init # 事前に.envのDATABASE_URLを修正 # Introspect your database with Prisma $ npx prisma db pull # Install and generate Prisma Client $ npm install @prisma/client $ npx prisma generate # Querying the database $ npx ts-node index.ts
model sample { id Int @id @default(autoincrement()) name String? start_date DateTime @db.Date end_date DateTime @db.Date start_datetime DateTime @db.Timestamp(6) end_datetime DateTime @db.Timestamp(6) }
スキーマの型がDateTime @db.DateとDateTime @db.Timestamp(6)で認識されています。この時点で良さそうです。
import { PrismaClient } from '@prisma/client' const prisma = new PrismaClient({ // https://www.prisma.io/docs/concepts/components/prisma-client/working-with-prismaclient/logging log: ['query', 'info', 'warn', 'error'], }); async function test(date: Date): Promise<void> { console.log({ date }) const samples = await prisma.sample.findMany({ where: { AND: [{ start_date: { lte: date } }, { end_date: { gte: date } }] }, }) console.log({ samples }) } async function main() { const now = new Date() await test(now) } main() .catch((e) => { throw e }) .finally(async () => { await prisma.$disconnect() })
new Date()を使って範囲検索してみます。
$ npx ts-node index.ts { date: 2022-05-29T06:25:09.893Z } { samples: [ { id: 1, name: 'Alice', start_date: 2022-05-29T00:00:00.000Z, end_date: 2022-05-29T00:00:00.000Z, start_datetime: 2022-05-29T14:04:26.409Z, end_datetime: 2022-05-29T14:04:26.409Z } ] }
取得できました。
2022-05-29 15:25:10.321 JST [128] LOG: execute s0: SELECT "public"."sample"."id", "public"."sample"."name", "public"."sample"."start_date", "public"."sample"."end_date", "public"."sample"."start_datetime", "public"."sample"."end_datetime" FROM "public"."sample" WHERE ("public"."sample"."start_date" <= $1 AND "public"."sample"."end_date" >= $2) OFFSET $3 2022-05-29 15:25:10.321 JST [128] DETAIL: parameters: $1 = '2022-05-29', $2 = '2022-05-29', $3 = '0' 2022-05-29 15:25:10.321 JST [128] LOG: duration: 0.010 ms plan: Query Text: SELECT "public"."sample"."id", "public"."sample"."name", "public"."sample"."start_date", "public"."sample"."end_date", "public"."sample"."start_datetime", "public"."sample"."end_datetime" FROM "public"."sample" WHERE ("public"."sample"."start_date" <= $1 AND "public"."sample"."end_date" >= $2) OFFSET $3 Seq Scan on sample (cost=0.00..23.80 rows=102 width=60) Filter: ((start_date <= '2022-05-29'::date) AND (end_date >= '2022-05-29'::date))
渡っているパラメータが'2022-05-29'となってます。
まとめ
結果はDapperだけ気を付ける必要がありました。Dapperが悪いわけではありません。 何種類かのライブラリをチュートリアル的に触る感じになってしまいましたが、結論的にはちゃんと単体テストしようねということですね。
PostgreSQLのVACUUMを整理する
以下の動画をみてVACUUMのこと何も知らないなと思ったので個人的に整理しました。
上の記事を参考にハンズオン形式で整理したいと思います。
- テストデータの準備
- 1行INSERT
- 1000万件INSERT
- 1000万件DELETE
- 再度1000万件INSERT
- autovacuum_vacuum_scale_factorに関して
- VACUMM FULL
- 終わりに
version
PostgreSQL 14.2 (Debian 14.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
postgresql.conf
log_autovacuum_min_duration = 0 autovacuum_vacuum_scale_factor = 0.2
テストデータの準備
テスト用のテーブルを作成
CREATE TABLE sample ( id serial NOT NULL, name text, create_date timestamp without time zone NOT NULL );
テーブルの実ファイルの確認
$ psql -U postgres -d sample sample=# SELECT datid, datname FROM pg_stat_database WHERE datname = 'sample'; -[ RECORD 1 ]--- datid | 16384 datname | sample sample=# SELECT relfilenode,relname FROM pg_class WHERE relname = 'sample'; -[ RECORD 1 ]------- relfilenode | 16386 relname | sample sample=# exit $ ls -lh $PGDATA/base/16384/16386* -rw------- 1 postgres postgres 0 May 1 12:55 /var/lib/postgresql/data/base/16384/16386
ファイルサイズは0です。
1行INSERT
INSERT INTO sample(name, create_date ) VALUES ( 'abc', -- 2022/4/1～2023/3/31でランダムを期待している ('2022-04-01 00:00:00'::timestamp + trunc(random() * 365) * '1 day'::interval + trunc(random() * 24) * '1 hour'::interval) );
テーブルの実ファイルの確認
$ ls -lh $PGDATA/base/16384/16386* -rw------- 1 postgres postgres 8.0K May 1 13:01 /var/lib/postgresql/data/base/16384/16386
ファイルサイズが8KBとなりました。SELECTしてみます。
SELECT
sample=# EXPLAIN ANALYZE SELECT * FROM sample WHERE id = 1; QUERY PLAN -------------------------------------------------------------------------------------------------- Seq Scan on sample (cost=0.00..24.12 rows=6 width=44) (actual time=0.020..0.021 rows=1 loops=1) Filter: (id = 1) Planning Time: 0.188 ms Execution Time: 0.042 ms (4 rows)
1000万件INSERT
INSERT INTO sample(name, create_date ) SELECT md5(clock_timestamp()::TEXT), -- 2022/4/1～2023/3/31でランダムを期待している ('2022-04-01 00:00:00'::timestamp + trunc(random() * 365) * '1 day'::interval + trunc(random() * 24) * '1 hour'::interval) FROM generate_series(1,10000000);
テーブルの実ファイルの確認
$ ls -lh $PGDATA/base/16384/16386* -rw------- 1 postgres postgres 731M May 1 13:10 /var/lib/postgresql/data/base/16384/16386 -rw------- 1 postgres postgres 200K May 1 13:08 /var/lib/postgresql/data/base/16384/16386_fsm -rw------- 1 postgres postgres 24K May 1 13:10 /var/lib/postgresql/data/base/16384/16386_vm
700MBを超えました。空き領域マップ(fsm)、可視性マップ(vm)のファイルも作成されました。
SELECT
sample=# EXPLAIN ANALYZE SELECT * FROM sample WHERE id = 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..146541.38 rows=1 width=45) (actual time=3.516..296.051 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on sample (cost=0.00..145541.28 rows=1 width=45) (actual time=167.849..260.686 rows=0 loops=3) Filter: (id = 1) Rows Removed by Filter: 3333333 Planning Time: 0.366 ms JIT: Functions: 6 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 1.252 ms, Inlining 0.000 ms, Optimization 0.758 ms, Emission 8.043 ms, Total 10.054 ms Execution Time: 296.749 ms (12 rows)
1000万件DELETE
idが1のもの1件だけを残して削除します。
sample=# DELETE FROM sample WHERE id != 1; DELETE 10000000 sample=# SELECT COUNT(*) FROM sample; -[ RECORD 1 ]- count | 1 $ ls -lh $PGDATA/base/16384/16386* -rw------- 1 postgres postgres 8.0K May 1 13:56 /var/lib/postgresql/data/base/16384/16386 -rw------- 1 postgres postgres 24K May 1 13:56 /var/lib/postgresql/data/base/16384/16386_fsm -rw------- 1 postgres postgres 8.0K May 1 13:56 /var/lib/postgresql/data/base/16384/16386_vm
データ削除で、ファイルサイズは変わらないはずでしたが変わりました。参考記事の下の方に書いてる「VACUUMの例外ケース」です。
2022-05-01 13:56:57.315 JST [221] LOG: automatic vacuum of table "sample.public.sample": index scans: 0 pages: 93457 removed, 1 remain, 0 skipped due to pins, 0 skipped frozen tuples: 4774018 removed, 1 remain, 0 are dead but not yet removable, oldest xmin: 741 index scan not needed: 93458 pages from table (100.00% of total) had 10000000 dead item identifiers removed avg read rate: 30.876 MB/s, avg write rate: 15.100 MB/s buffer usage: 135949 hits, 144514 misses, 70674 dirtied WAL usage: 231536 records, 44621 full page images, 52825589 bytes system usage: CPU: user: 1.53 s, system: 1.32 s, elapsed: 36.56 s
あらためて1000万件INSERT、1件残しのDELETE
sample=# INSERT INTO sample(name, create_date) SELECT md5(clock_timestamp()::TEXT), ('2022-04-01 00:00:00'::timestamp + trunc(random() * 365) * '1 day'::interval + trunc(random() * 24) * '1 hour'::interval) FROM generate_series(1,10000000); INSERT 0 10000000 $ ls -lh $PGDATA/base/16384/16386* -rw------- 1 postgres postgres 731M May 1 17:10 /var/lib/postgresql/data/base/16384/16386 -rw------- 1 postgres postgres 200K May 1 17:10 /var/lib/postgresql/data/base/16384/16386_fsm -rw------- 1 postgres postgres 24K May 1 17:10 /var/lib/postgresql/data/base/16384/16386_vm sample=# DELETE FROM sample WHERE id != 30000001; DELETE 9999999 sample=# SELECT COUNT(*) FROM sample; -[ RECORD 1 ]- count | 1 $ ls -lh $PGDATA/base/16384/16386* -rw------- 1 postgres postgres 731M May 1 17:33 /var/lib/postgresql/data/base/16384/16386 -rw------- 1 postgres postgres 200K May 1 17:32 /var/lib/postgresql/data/base/16384/16386_fsm -rw------- 1 postgres postgres 24K May 1 17:33 /var/lib/postgresql/data/base/16384/16386_vm
データ削除しましたがファイルサイズは変わりませんでした。期待した状況です。
自動VACUUMの確認
YouTubeの話になりますが、約1000万件削除したので「autovacuum_vacuum_scale_factor = 0.2」等により閾値を超えて自動VACUUMが動作していました。
ログで気にする項目
- index scans: 1以外はワークメモリ不足してるので確認する
- このテーブルはインデックスがないので「index scans: 0」ということかと思います
- tuples: 削除できない不要行増えてないか
- tuples: 9999999 removed なので問題なし
2022-05-01 17:33:10.474 JST [334] LOG: automatic vacuum of table "sample.public.sample": index scans: 0 pages: 0 removed, 93458 remain, 1 skipped due to pins, 0 skipped frozen tuples: 2858184 removed, 108 remain, 0 are dead but not yet removable, oldest xmin: 750 index scan not needed: 93457 pages from table (100.00% of total) had 9999892 dead item identifiers removed avg read rate: 15.174 MB/s, avg write rate: 16.528 MB/s buffer usage: 113394 hits, 73608 misses, 80177 dirtied WAL usage: 213627 records, 26716 full page images, 43675683 bytes system usage: CPU: user: 1.57 s, system: 1.00 s, elapsed: 37.89 s 2022-05-01 17:33:11.128 JST [334] LOG: automatic analyze of table "sample.public.sample" avg read rate: 298.308 MB/s, avg write rate: 0.060 MB/s buffer usage: 5312 hits, 24972 misses, 5 dirtied system usage: CPU: user: 0.01 s, system: 0.05 s, elapsed: 0.65 s 2022-05-01 17:33:32.600 JST [340] LOG: automatic vacuum of table "sample.public.sample": index scans: 0 pages: 0 removed, 93458 remain, 0 skipped due to pins, 93456 skipped frozen tuples: 0 removed, 4 remain, 0 are dead but not yet removable, oldest xmin: 751 index scan not needed: 1 pages from table (0.00% of total) had 107 dead item identifiers removed avg read rate: 2.230 MB/s, avg write rate: 2.230 MB/s buffer usage: 89 hits, 1 misses, 1 dirtied WAL usage: 3 records, 0 full page images, 509 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
pg_stat{all,sys,user}tables 統計情報ビューを確認
以下が気にするポイントだとYouTubeで話していました。
- n_dead_tup(不要行数)が定常的に増えていないか
- 定期的に自動VACUUMMは行われているか
sample=# SELECT relname, n_tup_ins, n_tup_del, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count FROM pg_catalog.pg_stat_user_tables WHERE relname = 'sample'; -[ RECORD 1 ]----+------------------------------ relname | sample n_tup_ins | 30000001 n_tup_del | 39235147 n_live_tup | 4 n_dead_tup | 0 last_autovacuum | 2022-05-01 17:33:32.599949+09 autovacuum_count | 7
あと、pg_stat_progress_vacuum 統計情報ビューでVACUUMの処理フェーズを確認できるとのことでした。
ヒープ=テーブルのデータ部分とのこと
vacuuming indexesでmaintenance_work_memが不足しているとバキューム実行ごとに複数回発生する可能性があるというのが「index scans: 1」を確認することにつながるみたいです。
YouTubeの話しでは、不要行を1行回収するのに必要なメモリは6バイト、デフォルトの64MBでも約1000万行回収できるとのことでした。
定期的に自動VACUUMMは行われているかに関しては、競合するロックが要求されると自動VACUUMは自動的にキャンセルされるのでログに同じテーブルのキャンセルが続いていないか確認するとYouTubeで話してました。
ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table "xxxxxxxx"
再度1000万件INSERT
参考記事とは異なり自動VACUUMで不要領域が回収されているので、ここではファイルサイズは変わらないはずです。
sample=# INSERT INTO sample(name, create_date) SELECT md5(clock_timestamp()::TEXT), ('2022-04-01 00:00:00'::timestamp + trunc(random() * 365) * '1 day'::interval + trunc(random() * 24) * '1 hour'::interval) FROM generate_series(1,10000000); INSERT 0 10000000 sample=# SELECT COUNT(*) FROM sample; count ---------- 10000001 (1 row) $ ls -lh $PGDATA/base/16384/16386* -rw------- 1 postgres postgres 731M May 1 17:54 /var/lib/postgresql/data/base/16384/16386 -rw------- 1 postgres postgres 200K May 1 17:53 /var/lib/postgresql/data/base/16384/16386_fsm -rw------- 1 postgres postgres 24K May 1 17:53 /var/lib/postgresql/data/base/16384/16386_vm sample=# SELECT relname, n_tup_ins, n_tup_del, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count FROM pg_catalog.pg_stat_user_tables WHERE relname = 'sample'; -[ RECORD 1 ]----+------------------------------ relname | sample n_tup_ins | 40000001 n_tup_del | 39235147 n_live_tup | 10000006 n_dead_tup | 0 last_autovacuum | 2022-05-01 17:53:55.123102+09 autovacuum_count | 8 sample=# EXPLAIN ANALYZE SELECT * FROM sample WHERE id = 39234530; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..146541.46 rows=1 width=45) (actual time=7.130..882.356 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on sample (cost=0.00..145541.36 rows=1 width=45) (actual time=494.259..783.931 rows=0 loops=3) Filter: (id = 39234530) Rows Removed by Filter: 3333333 Planning Time: 0.195 ms JIT: Functions: 6 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 2.756 ms, Inlining 0.000 ms, Optimization 1.743 ms, Emission 20.110 ms, Total 24.609 ms Execution Time: 883.301 ms (12 rows)
ファイルサイズに変わりはありませんでした。pg_stat_user_tables の値は期待したものと微妙に一致はしないものなのか。（統計情報更新されてないからか）
「VACUUM FULL」をやる前にいくつか試します。
autovacuum_vacuum_scale_factorに関して
現状はpostgresql.confで「autovacuum_vacuum_scale_factor = 0.2」と設定しています。
テーブルごとにautovacuum_vacuum_scale_factorを調整
「バキューム閾値 = バキューム基礎閾値 + バキューム規模係数 * タプル数」
- バキューム基礎閾値(autovacuum_vacuum_threshold=50)
- バキューム規模係数(autovacuum_vacuum_scale_factor)
- タプル数(pg_class.reltuples)
sample=# SELECT reltuples FROM pg_catalog.pg_class WHERE relname = 'sample'; reltuples --------------- 1.0000006e+07
デフォルトのバキューム規模係数は「autovacuum_vacuum_scale_factor = 0.2」なので、少なめに設定して動作を確認します。
ALTER TABLE sample SET (autovacuum_vacuum_scale_factor = 0.001, autovacuum_analyze_scale_factor = 0.001)
10,050(バキューム閾値) = 50(バキューム基礎閾値) + 0.001(バキューム規模係数) * 1000万(タプル数)
sample=# SELECT COUNT(*) FROM sample; count ---------- 10000001 (1 row) sample=# DELETE FROM sample WHERE id IN (SELECT id FROM sample LIMIT 10000); DELETE 10000
1万削除で自動VACUUMが実行されました。
autovacuum_vacuum_scale_factor に関しては定期的にデータ量を確認してその時々の適切な値を指定するのが正しい
運用ということですね。
VACUMM FULL
VACUMM FULLを行って、ファイルサイズが小さくなることを確認します。
※ 手順を色々と失敗したので仕切り直しています。
sample=# SELECT COUNT(*) FROM sample; -[ RECORD 1 ]- count | 912000 sample=# SELECT relname, n_tup_ins, n_tup_del, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count FROM pg_catalog.pg_stat_user_tables WHERE relname = 'sample'; -[ RECORD 1 ]----+----------------------------- relname | sample n_tup_ins | 50000001 n_tup_del | 66575055 n_live_tup | 891153 n_dead_tup | 0 last_autovacuum | 2022-05-01 21:16:31.92196+09 autovacuum_count | 25 sample=# SELECT datid, datname FROM pg_stat_database WHERE datname = 'sample'; -[ RECORD 1 ]--- datid | 16384 datname | sample sample=# SELECT relfilenode,relname FROM pg_class WHERE relname = 'sample'; -[ RECORD 1 ]------- relfilenode | 16416 relname | sample $ ls -lh $PGDATA/base/16384/16416* -rw------- 1 postgres postgres 797M May 1 21:17 /var/lib/postgresql/data/base/16384/16416 -rw------- 1 postgres postgres 224K May 1 21:15 /var/lib/postgresql/data/base/16384/16416_fsm -rw------- 1 postgres postgres 32K May 1 21:15 /var/lib/postgresql/data/base/16384/16416_vm # ここでsampleのバックアップを取得 sample=# VACUUM FULL sample; VACUUM sample=# SELECT relfilenode,relname FROM pg_class WHERE relname = 'sample'; -[ RECORD 1 ]------- relfilenode | 16436 relname | sample $ ls -lh $PGDATA/base/16384/16436* -rw------- 1 postgres postgres 67M May 1 21:24 /var/lib/postgresql/data/base/16384/16436 sample=# SELECT relname, n_tup_ins, n_tup_del, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count FROM pg_catalog.pg_stat_user_tables WHERE relname = 'sample'; -[ RECORD 1 ]----+----------------------------- relname | sample n_tup_ins | 50000001 n_tup_del | 66575055 n_live_tup | 891153 n_dead_tup | 0 last_autovacuum | 2022-05-01 21:16:31.92196+09 autovacuum_count | 25 sample=# EXPLAIN ANALYZE SELECT * FROM sample WHERE id = 49088002; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..14274.10 rows=1 width=45) (actual time=0.185..46.649 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on sample (cost=0.00..13274.00 rows=1 width=45) (actual time=11.008..25.856 rows=0 loops=3) Filter: (id = 49088002) Rows Removed by Filter: 304000 Planning Time: 0.044 ms Execution Time: 46.666 ms (8 rows)
VACUUM FULLによりファイルサイズが小さくなることを確認できました。
実は気になっていたので、論理バックアップ・リストアした場合にリストア先の状況がどうなっているのかを念のため確認します。
sample2データベースを作成してリストアします。論理バックアップはVACUUM FULL前に取得していました。
$ psql -U postgres -d sample2 sample2=# SELECT datid, datname FROM pg_stat_database WHERE datname = 'sample2'; -[ RECORD 1 ]---- datid | 16441 datname | sample2 sample2=# SELECT relfilenode,relname FROM pg_class WHERE relname = 'sample'; -[ RECORD 1 ]------- relfilenode | 16442 relname | sample $ ls -lh $PGDATA/base/16441/16442* -rw------- 1 postgres postgres 67M May 1 21:30 /var/lib/postgresql/data/base/16441/16442 -rw------- 1 postgres postgres 40K May 1 21:29 /var/lib/postgresql/data/base/16441/16442_fsm -rw------- 1 postgres postgres 8.0K May 1 21:30 /var/lib/postgresql/data/base/16441/16442_vm sample2=# SELECT relname, n_tup_ins, n_tup_del, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count FROM pg_catalog.pg_stat_user_tables WHERE relname = 'sample'; -[ RECORD 1 ]----+------------------------------ relname | sample n_tup_ins | 912000 n_tup_del | 0 n_live_tup | 912000 n_dead_tup | 0 last_autovacuum | 2022-05-01 21:30:39.399737+09 autovacuum_count | 1
不要領域は含まれないファイルサイズでした。また、n_tup_insやlast_autovacuum、autovacuum_countの値も初期化されていました。
終わりに
長くなりましたが、ドキュメント読むよりは体感的に動作が理解できました。
VACUUMは不要領域を再利用可能な状態に、VACUUM FULLはファイルサイズも小さくなります。ただ気軽にVACUMM FULLはできないという話。
GitHub - reorg/pg_repack: Reorganize tables in PostgreSQL databases with minimal locks
AWSコンテナ設計・構築［本格］入門のハンズオンやり終えた
AWSコンテナ設計・構築［本格］入門をようやく一通り終わったのでメモを残します。
最近、技術書を電子書籍(Kindle)で購入することも多くなってきたんですが、固定レイアウトはなかなか難しいですね。
固定レイアウトなら書籍で購入したほうが良いのかなと思いました。電子書籍良いんですが。
やってみた感想
普段の業務ではAWSは使ってないのですが、「AWS 認定ソリューションアーキテクト – アソシエイト」は一応取得しています。AWSのハンズオンをいくつかやっていて、なんとなくはEC2使うなら一通り構築できると思うことにしている感じの人です。
最初に必要なAWSの知識の章があり、とても勉強になりました。その後にハンズオンの流れになるので何やってるかわからんとならなかったです。
（ページを行ったり来たりしたいときは、書籍がいいのかなって感じました）
運用設計に限らないですが、色々と選択肢を示してくれるのが勉強になります。
AWSが公開している各種ドキュメントなどが紹介されていたので、読んで理解したいと思います。
www.slideshare.net
- 5-2章 CodeBuildで起きる「Too Maney Requests.」対策
- Amazon ECR「プルスルーキャッシュ」機能でもどうにかできるのかな
注意点というかあらためて感じられたこと
- ハンズオンで作成しているECSのタスクやRDSは課金されることが明白なので、1日でやりきれない場合は停止する
- 4章終わるのに力量によると思いますが３時間はかかったみたいです
- ELBは1つだと無料枠内ですが、2つ以上ある場合は無料枠超えてしまうので放置しないほうが良い
- もともと1つ放置ELBがあったので1000円くらいになって気づきました
- VPCエンドポイントもあるだけで課金されるので注意が必要でした。時間課金されるのでタイミングによって削除したほうが良い
- 200円くらいで気づきました
- あるあるかもですがRDSを停止していて次の週末に続きできないことがあり、気づいたら動き出して課金されるやつを自分がやるとは思いませんでした。気を付ける
- 500円くらい持ってかれました。
書籍の中でも、しっかり説明されています。要件によってはNATゲートウェイが良いかもまで説明されています。
まとめ
ECSに特化してまとまった内容を学べるのは非常に良いと思いました。AWS Well-Architected フレームワーク沿ったものかを自分の手を動かすことで、資格勉強でなんとなく知った内容を深堀できたりしたのかなと思いました。
書籍内のハンズオン実施有無で無となっていた項目を含めて、あらためて時間のあるときにやろうと思います。
OpenRestyを使わずにnginxでCache Key Preprocessingしたい
OpenRestyで簡単にできるCache Key Preprocessing を素のnginxで使いたいだけです。
OpenResty使えよって気持ちはわかるのですが、追加のモジュールを入れるのは気が重いので使わない方法を調べてみました。
まずはOpenRestyを試してみる
準備
公式のイメージを利用します。
初期のdefault.confはコンテナから取得しました。
$ docker cp <コンテナID>:/etc/nginx/conf.d/default.conf conf.d/default.conf
以下のdocker-composeファイルでconfをマウントします。
version: "3" services: openresty: image: openresty/openresty:1.19.9.1-6-alpine container_name: openresty-test ports: - "8081:80" volumes: - type: bind source: "./conf.d" target: "/etc/nginx/conf.d"
confの修正
Cache Key Preprocessingに記載されている以下の部分をdefault.confに追記します。
location = /t { rewrite_by_lua ' local args = ngx.req.get_uri_args() args.SID = nil args.UID = nil ngx.req.set_uri_args(args) '; echo $args; }
試す
$ docker-compose up -d $ curl 'localhost:8081/t?RT=62&SID=BC3781C3-2E02-4A11-89CF-34E5CFE8B0EF&UID=44332&L=EN&M=1&H=1&UNC=0&SRC=LK' UNC=0&H=1&SRC=LK&L=EN&M=1&RT=62
ドキュメントにも書いてあるように、順番変わりましたがSIDとUIDがなくなりました。
OpenRestyを使えてしまえば、すごく簡単です。すごいです。
echoもだいぶ強力です。
nginxでやりたい
準備
公式のイメージを利用します。
初期のdefault.confはコンテナから取得しました。
あらためてlua-nginx-moduleもecho-nginx-moduleも使わない話でした。
以下のdocker-composeファイルでconfをマウントします。
version: "3" services: nginx: image: nginx:1.20.2-alpine container_name: nginx-test ports: - "8080:80" volumes: - type: bind source: "./conf.d" target: "/etc/nginx/conf.d"
confの修正(#1)
とりあえずレスポンスを返すことで、echoで確認できてたことを確認できるようにします。
default.confに追記します。
location /t { set $_args $args; default_type application/json; return 200 '{"args": "$args", "_args": "$_args"}'; }
試す(#1)
$ docker-compose up -d $ curl 'localhost:8080/t?RT=62&SID=BC3781C3-2E02-4A11-89CF-34E5CFE8B0EF&UID=44332&L=EN&M=1&H=1&UNC=0&SRC=LK' {"args": "RT=62&SID=BC3781C3-2E02-4A11-89CF-34E5CFE8B0EF&UID=44332&L=EN&M=1&H=1&UNC=0&SRC=LK", "_args": "RT=62&SID=BC3781C3-2E02-4A11-89CF-34E5CFE8B0EF&UID=44332&L=EN&M=1&H=1&UNC=0&SRC=LK"}
confの修正(#2)
location /t { set $_args $args; # SIDを削除 if ($_args ~ (.*)SID=[^&]*(.*)) { set $_args $1$2; } # UIDを削除 if ($_args ~ (.*)UID=[^&]*(.*)) { set $_args $1$2; } default_type application/json; return 200 '{"args": "$args", "_args": "$_args"}'; }
試す(#2)
$ docker-compose up -d $ curl 'localhost:8080/t?RT=62&SID=BC3781C3-2E02-4A11-89CF-34E5CFE8B0EF&UID=44332&L=EN&M=1&H=1&UNC=0&SRC=LK' {"args": "RT=62&SID=BC3781C3-2E02-4A11-89CF-34E5CFE8B0EF&UID=44332&L=EN&M=1&H=1&UNC=0&SRC=LK", "_args": "RT=62&&&L=EN&M=1&H=1&UNC=0&SRC=LK"}
アンパサンド、正規表現力が足りてないとかあると思いますができました。これで良いことにしたいと思います。
proxy_cacheで使ってみる
confの修正
以下を適当に追記しました。
proxy_cache_path /var/cache/nginx/test levels=1:2 keys_zone=my-key:16m max_size=100m inactive=1d; ... proxy_cache my-key; proxy_cache_valid 200 302 1d; proxy_cache_key "$host$uri$is_args$_args"; add_header X-Nginx-Cache $upstream_cache_status;
試す
# RT=62 => X-Nginx-Cache: MISS $ curl -I 'localhost:8080/tpcache?RT=62&SID=BC3781C3-2E02-4A11-89CF-34E5CFE8B0EF&UID=44332&L=EN&M=1&H=1&UNC=0&SRC=LK' HTTP/1.1 200 OK Server: nginx Date: Mon, 21 Mar 2022 04:25:37 GMT Content-Type: text/html Content-Length: 612 Connection: keep-alive Last-Modified: Tue, 16 Nov 2021 15:04:23 GMT ETag: "6193c877-264" X-Nginx-Cache: MISS Accept-Ranges: bytes # RT=62（最初と同じもの）=> X-Nginx-Cache: HIT $ curl -I 'localhost:8080/tpcache?RT=62&SID=BC3781C3-2E02-4A11-89CF-34E5CFE8B0EF&UID=44332&L=EN&M=1&H=1&UNC=0&SRC=LK' HTTP/1.1 200 OK Server: nginx Date: Mon, 21 Mar 2022 04:25:53 GMT Content-Type: text/html Content-Length: 612 Connection: keep-alive Last-Modified: Tue, 16 Nov 2021 15:04:23 GMT ETag: "6193c877-264" X-Nginx-Cache: HIT Accept-Ranges: bytes # RT=63 => X-Nginx-Cache: MISS $ curl -I 'localhost:8080/tpcache?RT=63&SID=BC3781C3-2E02-4A11-89CF-34E5CFE8B0EF&UID=44332&L=EN&M=1&H=1&UNC=0&SRC=LK' HTTP/1.1 200 OK Server: nginx Date: Mon, 21 Mar 2022 04:26:22 GMT Content-Type: text/html Content-Length: 612 Connection: keep-alive Last-Modified: Tue, 16 Nov 2021 15:04:23 GMT ETag: "6193c877-264" X-Nginx-Cache: MISS Accept-Ranges: bytes # RT=62, SID変更(BC37..→AD37..) => X-Nginx-Cache: HIT $ curl -I 'localhost:8080/tpcache?RT=62&SID=AD3781C3-2E02-4A11-89CF-34E5CFE8B0EF&UID=44332&L=EN&M=1&H=1&UNC=0&SRC=LK' HTTP/1.1 200 OK Server: nginx Date: Mon, 21 Mar 2022 04:26:50 GMT Content-Type: text/html Content-Length: 612 Connection: keep-alive Last-Modified: Tue, 16 Nov 2021 15:04:23 GMT ETag: "6193c877-264" X-Nginx-Cache: HIT Accept-Ranges: bytes # RT=62, UID変更(44332→55443) => X-Nginx-Cache: HIT $ curl -I 'localhost:8080/tpcache?RT=62&SID=BC3781C3-2E02-4A11-89CF-34E5CFE8B0EF&UID=55443&L=EN&M=1&H=1&UNC=0&SRC=LK' HTTP/1.1 200 OK Server: nginx Date: Mon, 21 Mar 2022 04:27:19 GMT Content-Type: text/html Content-Length: 612 Connection: keep-alive Last-Modified: Tue, 16 Nov 2021 15:04:23 GMT ETag: "6193c877-264" X-Nginx-Cache: HIT Accept-Ranges: bytes
期待された形でX-Nginx-Cacheが返ってきました。良さそうです。
念のためキャッシュされたファイルも確認します。
$ docker exec -it nginx-test sh $ cd /var/cache/nginx/test $ ls 5 8 $ cat 8/62/2764b90faf9a7484b00c51d615edb628 ... KEY: localhost/tpcache?RT=62&&&L=EN&M=1&H=1&UNC=0&SRC=LK HTTP/1.1 200 OK Server: nginx Date: Mon, 21 Mar 2022 04:25:37 GMT ...
アンパサンド、とりあえずOKということで終わります。
SQLをいい感じに補完してほしい
SQLを書くときにテーブル名やカラム名を全部入力したり、タイポが怖くてコピー&ペーストしたりと今更ですが効率悪いことしてるなと思ったのでツールを見直そうと思いました。
実際はそこまで酷くはないのですが。
やりたいこと
- テーブル名やカラム名などの補完をしてほしい
- SQLキーワードは大文字になってほしい
- 定義情報を簡単にみたい
- できれば複数のデータベース(PostgreSQL, MySQL)に対応したい
試したツール
Googleで「sql 補完」を検索して5ページぐらいをざっくり見ました。 検索結果を少し考慮しつつ私の気持ちで候補を決めます。
- pgAdmin 4
- 個人的にPostgreSQLは割と使うことがあるので専用ツールですが許します。
- DBeaver
- 検索結果にあり、少し使ったことがある状況。
- A5:SQL MK-2
- 検索結果にあり、Windows環境が主なので候補に入ります。
- DataGrip
- 検索結果にあり、有償ツールの凄さか気になるので試します。
- sqls
- 検索結果にあり、Go Conferenceで見て気になってました（だいぶ経ってるが）
評価の方法は、以下で書いたSQLの書き心地で決めます。
基本的にSELECT系のみというか色々試してたら時間がないのでSELECT系のみです。
pgAdmin 4
試したバージョン：pgAdmin 4 v6.7
普段はPostgreSQLを使うことが多いので、GUIのクライアントにpgAdmin 4が入っていたりするので使ってます。
ただ、SQLを実行させるために使うだけでここでたくさん書くことはなかったです。
今回試したバージョンはデスクトップアプリになってました（言い方あってるのか）。ブラウザで起動するものを使っていたので少しびっくりしました。バージョンを定期的に上げよう。勝手に上げられないことも多いですが。
使ってみて
「Query Tool」を使います
- デフォルトで「Ctrl + Space」で補完が行われます
- selで「SELECT」に補完できます
- froで「FROM」に補完でき、FROMの後はテーブルやスキーマやVIEWが補完候補となります
- FROMでテーブルなどが決まればカラム名は補完できる
- 補完されない
- latで「LATERAL」は補完できない
- ASで別名つけたもののカラム名は補完できない
- おまけでUNIONの後は、selで「SELECT」は補完できない
- 定義情報は補完時やホバーなどでは見られない
デスクトップアプリになって補完されるまでが早くなった気がしました。
関係ないけど、ERD Toolが増えてDBを右クリックのGenerate ERDでER図がとりあえず作成された。
DBeaver
試したバージョン：Community Edition 22.0
以下の設定でSQLキーワードは大文字、テーブル名などは小文字で補完できる形になった。
- Preferences -> Editors -> SQL Editor
- Code Completion
- Insert caseはDefaultのままにする
- Insert space after table/column namesをオフに変更する
- Code Editor
- Auto formatのConvert keyword caseをオフにしないと小文字になる
- Formatting
- Keyword caseをUpperに変更する
- Code Completion
フォーマットの調整に時間がかかってしまいました。
使ってみて（pgAdmin 4との差分）
- FROMでテーブル名.カラム名も問題ない感じ
- ASで別名つけたもののカラム名は補完できる（サブクエリはできないみたい）
- UNIONの後のselで「SELECT」は補完できる
- 定義情報はカラムだと型やNot Nullかなどは補完時に確認できる
感覚的にはpgAdminより良いと思われる。
A5:SQL MK-2
試したバージョン： 2.17.1
DBeaverと同様に設定の微調整が必要でした。
- オプション -> SQL整形
- キーワードの大文字／小文字変換を大文字に変更する
使ってみて（DBeaverとの差分）
- 補完時に定義情報はカラムだと型はみることできましたが、Not Nullかやデフォルト値はみれなかった
- 逆にコメントを見られる
DataGrip
使ってみて
個人でしかもホビー利用だとお高いですよね。ということでフォーマット系はひとまず気にせず。
- latで「LATERAL」は補完できる
- 補完時に定義情報はカラムだと型はみることできましたが、Not Nullかやデフォルト値はみれなかった
- 補完時ではないがカラム名をホバーすると定義情報（alterなんちゃらやコメント）を確認できる
- ASで別名つけたもののカラム名は補完できる（サブクエリも）
他のツールで届かなかった痒いところに届きました。補完もIDE使ってる感覚です。
sqls
VSCodeで使います。
GitHub - lighttiger2505/vscode-sqls: This extension adds language support for the SQL to VS Code.
Install sqls
$ go install github.com/lighttiger2505/sqls@v0.2.20
gccが必要でした（私の環境はWindowsです）
Download | tdm-gcc
$ mkdir -p ~/.config/sqls/ $ cat <<EOF > ~/.config/sqls/config.yml # Set to true to use lowercase keywords instead of uppercase. lowercaseKeywords: false connections: - alias: test driver: postgresql proto: tcp user: postgres passwd: password host: 127.0.0.1 port: 15432 dbName: postgres EOF
とりあえず上の感じでconfig配置すれば動きました。
あと、説明に書いてるようにsqlの拡張子のファイルに書く感じです。
（Open any *.sql file in VS Code. The extension is now activated.）
lowercaseKeywordsでtrue/falseいじってみましたが、selectなどのSQLキーワードが大文字にならず。
使ってみて（pgAdmin 4、DBeaverとの差分）
- DBeaverと同じくFROMでテーブル名.カラム名も問題ない感じ
- DBeaverと同じくASで別名つけたもののカラム名は補完できる（サブクエリはできないみたい）
- DBeaverと同じく定義情報はカラムだと型は補完時に確認できる
- sqlsだとホバー時にも定義情報みれる
- DBeaverと同じくUNIONの後のselで「SELECT」は補完できる
設定等を理解できていないのですが、以下を解決できればVSCodeで使えるので一番良いかなと思っています。
- SQLキーワードを大文字にしたい
- config.ymlの切り替え
これから使おうと思ったツール
適当な項目で適当に順位をつけてみました。
（スコアって書いてるのは順位を合計しただけなので、小さいほうが良い）
| 補完 | 定義情報確認 | 複数DB対応 | コスト | スコア | |
|---|---|---|---|---|---|
| pgAdmin 4 | 5 | 5 | 5 | 1 | 16 |
| Dbeaver | 2 | 2 | 1 | 1 | 6 |
| A5:SQL MK-2 | 2 | 4 | 1 | 1 | 8 |
| DataGrip | 1 | 1 | 1 | 5 | 8 |
| sqls | 2 | 2 | 1 | 1 | 6 |
個人的な感想としては、(DataGrip) > sqls > DBeaver > A5:SQL MK-2 > pgAdmin 4 といった感じです。
DataGripがさすがに有償ですし、雰囲気違う感がありました。でも、そんな君SQL書かないよねと。
普段割と使っているエディタ（VSCode）上で実行できるといいと思ったので、もう少しsqlsの設定等を調べつつ使ってみたいなと思いました。
