spanner_info_reader, spanner_sys_readerを設定する手順

[sinmetal]

spanner_info_reader, spanner_sys_readerだけ使えるようにIAMを設定する

DBにRoleを生成するためにDDLを実行する

observerというのはsinmetalが適当に決めた名前

https://cloud.google.com/spanner/docs/fgac-system-roles

CREATE ROLE observer;
GRANT ROLE spanner_info_reader TO ROLE observer;
GRANT ROLE spanner_sys_reader TO ROLE observer;

作ったRoleを使えるように設定

gcloud spanner databases add-iam-policy-binding sinmetal \
--project=gcpug-public-spanner \
--instance=merpay-sponsored-instance \
--role=roles/spanner.fineGrainedAccessUser \
--member=user:metal.tie@gmail.com \
--condition=None

gcloud spanner databases add-iam-policy-binding sinmetal \
--project=gcpug-public-spanner \
--instance=merpay-sponsored-instance \
--role=roles/spanner.databaseRoleUser \
--member=user:metal.tie@gmail.com \
--condition='expression=(resource.type == "spanner.googleapis.com/DatabaseRole" && resource.name.endsWith("/observer")),title=observer,description=spanner performance monitor'

Custom Roleの作成と適用

sessionが作れないとQueryが実行できないので、以下を持つCustom Roleが必要

• spanner.sessions.create
• spanner.sessions.delete

spanner_info_readerあれば、DDLは見えるしなってことで、ついでに以下も入れたりした

• spanner.databases.getDdl

Queryを投げる

--database-role=ROLE が必要 https://cloud.google.com/spanner/docs/access-with-fgac

gcloud spanner databases execute-sql sinmetal --billing-project sinmetal-slide --project gcpug-public-spanner --instance=merpay-sponsored-instance --database-role=observer --sql='SELECT * FROM spanner_sys.query_stats_top_hour ORDER BY interval_end DESC LIMIT 3;'

[sinmetal]

TSVで保存する

SELECT TEXT_FINGERPRINT,
       ANY_VALUE(text) AS text,
       ANY_VALUE(request_tag) AS request_tag,
       SUM(execution_count) AS execution_count,
       SUM(execution_count * avg_cpu_seconds) AS total_cpu_seconds,
       SUM(execution_count * avg_bytes) AS total_bytes,
       SUM(execution_count * avg_latency_seconds) AS total_latency_seconds,
       SUM(execution_count * avg_rows) AS total_rows,
       SUM(execution_count * avg_rows_scanned) AS total_rows_scanned,

       SUM(execution_count * avg_cpu_seconds) / SUM(execution_count) AS avg_cpu_seconds,
       SUM(execution_count * avg_bytes) / SUM(execution_count) AS avg_bytes,
       SUM(execution_count * avg_latency_seconds) / SUM(execution_count) AS avg_latency_seconds,
       SUM(execution_count * avg_rows) / SUM(execution_count) AS avg_rows,
       SUM(execution_count * avg_rows_scanned) / SUM(execution_count) AS avg_rows_scanned,
FROM spanner_sys.query_stats_top_hour
WHERE INTERVAL_END > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY TEXT_FINGERPRINT
ORDER BY total_cpu_seconds DESC
LIMIT 200;

cat query_stats_top_hour.sql | spanner-cli -p gcpug-public-spanner -i merpay-sponsored-instance -d sinmetal  > spanner_query_stats_result_$(date +%Y%m%d-%H%M).tsv

[sinmetal]

Queryの改行をスペースに置き換えてみる版 TSVで出力された後の処理がめんどうなので

SELECT TEXT_FINGERPRINT,
       REPLACE(ANY_VALUE(text),"\n"," ") AS text,
       ANY_VALUE(request_tag) AS request_tag,
       SUM(execution_count) AS execution_count,
       SUM(execution_count * avg_cpu_seconds) AS total_cpu_seconds,
       SUM(execution_count * avg_bytes) AS total_bytes,
       SUM(execution_count * avg_latency_seconds) AS total_latency_seconds,
       SUM(execution_count * avg_rows) AS total_rows,
       SUM(execution_count * avg_rows_scanned) AS total_rows_scanned,

       SUM(execution_count * avg_cpu_seconds) / SUM(execution_count) AS avg_cpu_seconds,
       SUM(execution_count * avg_bytes) / SUM(execution_count) AS avg_bytes,
       SUM(execution_count * avg_latency_seconds) / SUM(execution_count) AS avg_latency_seconds,
       SUM(execution_count * avg_rows) / SUM(execution_count) AS avg_rows,
       SUM(execution_count * avg_rows_scanned) / SUM(execution_count) AS avg_rows_scanned,
FROM spanner_sys.query_stats_top_hour
WHERE INTERVAL_END > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY TEXT_FINGERPRINT
ORDER BY total_cpu_seconds DESC
LIMIT 200;