1

I am using Google Cloud SQL. I am using a g1-small. I have a table that looks like this:

+-----------+-------------------+-------------+
| Column    | Type              | Modifiers   |
|-----------+-------------------+-------------|
| id        | character varying |  not null   |
| reportId  | uuid              |  not null   |
| value     | jsonb             |             |
| isTouched | boolean           |  not null   |
+-----------+-------------------+-------------+
Indexes:
    "report_variable_pkey" PRIMARY KEY, btree (id, "reportId")
    "report_variable_id_idx" btree (id)
    "report_variable_isTouched_idx" btree ("isTouched")
    "report_variable_reportId_idx" btree ("reportId")
    "report_variable_value_idx" gin ((value::text) gin_trgm_ops)
Foreign-key constraints:

I have a query that looks like this:

INSERT INTO
  report_variable ("id",
    "reportId",
    "value",
    "isTouched")
SELECT
  *
FROM
  UNNEST($1::"varchar"[], $2::"uuid"[], $3::"jsonb"[], $4::"bool"[])
ON
  CONFLICT ("id",
    "reportId") DO
UPDATE
SET
  "value" = EXCLUDED."value",
  "isTouched" = EXCLUDED."isTouched" RETURNING *

My table has ~1.6m rows in it. This query runs thousands of times per day. Occasionally (as in, every 100 executions?), it will take a VERY long time. Here is a graph showing latency:

latency

Here is the data that comes from the Google Cloud SQL Query Insights page, for an average query: enter image description here

Here is the data for one of the strange queries: enter image description here

Some things I noticed:

  • Why is I/O Read Time so much higher?
  • Why is Shared Dirtied Blocks, Shared Hit Blocks, Shared Read Blocks and Shared Written Blocks? Why are they so much higher?

How can I find out more information? Let me know if you need any more info. Thank you in advance.

6
  • I think most people will be far more accustomed to looking at raw text formatted EXPLAIN output rather than google's presentation of it. Any chance you can retrieve the raw text?
    – jjanes
    Commented Nov 25, 2021 at 17:25
  • Does your GIN index have "fastupdate" turned on? If so, one user process may occasionally get tasked with consolidating all the index changes that have accumulated recently. This improves the overall average throughput, but it sucks for the latency of that chosen user process.
    – jjanes
    Commented Nov 25, 2021 at 17:34
  • @jjanes I can yeah. Commented Nov 25, 2021 at 20:11
  • @jjanes how would I go about seeing If I have fastupdate for a specific index? \d+ doesn't show anything interesting Commented Nov 25, 2021 at 20:23
  • On is the default, and if \d doesn't show it as being off (which I now notice it did not in the output you already showed) then it must be on implicitly. You can turn it off with alter index report_variable_value_idx set (fastupdate = off)
    – jjanes
    Commented Nov 26, 2021 at 0:08

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.