Confused by an error and slow statements missing from pg_stat_activity

General
1

I setup a cluster on Managed Yugabyte and started to run a simple benchmark. This was done because I am curious – I have no plans to spend a few weeks getting a good result and then publishing it.

But while running it I had three problems:

  1. The PK is by hash by default. Blame me for not reading the docs. I do range scans on it and switched it to range when I realized this.
  2. Some long running statements never showed up in pg_stat_activity. In this case “long” means 4+ seconds. The slow statements were deletes that have a range condition in the WHERE clause, and the range predicate could use the PK if it were a range (alas it was hash at the time), so the slowness comes from doing a Seq Scan.
  3. I got an error while creating an index, and after that odd things continued to happen. In this case I had repeated the benchmark a few times and each time I drop the table named pi1, then create a table named pi1, then load some data. Cluster health on the web page shows a 1 next to a green heart and then 0 next to the yellow triangle and red stop sign.

The error on create index:

psycopg2.errors.InternalError_: Aborted: ERROR: Not found: [Not found (yb/master/catalog_manager.cc:10567): LookupByIdRpc(tablet: 314078da33234e09aea68c393931657c, num_attempts: 1) failed: Tablet deleted: Table deleted at 2023-07-24 01:21:02 UTC (split child tablet IDs ), Not found (yb/master/catalog_manager.cc:10567): LookupByIdRpc(tablet: ad8da011bc5143d897d4a173ada14ecc, num_attempts: 1) failed: Tablet deleted: Table deleted at 2023-07-24 01:21:02 UTC (split child tablet IDs ), Not found (yb/master/catalog_manager.cc:10567): LookupByIdRpc(tablet: f88c26fac11345b6ba4f49012792f1e8, num_attempts: 1) failed: Tablet deleted: Table deleted at 2023-07-24 01:21:02 UTC (split child tablet IDs )]

Then I killed that run, restarted it and while doing some inserts I get another error

SQL error: ERROR: Not found: [Not found (yb/master/catalog_manager.cc:10567): LookupByIdRpc(tablet: 85096634e5c349b6a6cb988ed8e794a5, num_attempts: 1) failed: Tablet deleted: Table deleted at 2023-07-24 01:29:47 UTC (split child tablet IDs )]

2

While repeating the test I also don’t see much in pg_stat_activity for insert statements using …

SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;
3

Hi Mark,

  1. Yes hash sharding on the first column is the default. The Performance Advisor should give a recommendation. I’ll check if it gives one for this case
  2. The views pg_stat_activity and pg_stat_statements are per-node and when you connect to YB Managed, it goes to any node. This may explain why you don’t see a query run from another connection. Those views are aggregated in the UI (Performance / Live Queries is from all pg_stat_activity and Performance / Slow Queries is from all pg_stat_statements )
  3. The index creation is online (with backfill) and if it fails may leave the index invalid. This is visible with \d <tablename>.
    To understand better, how is this drop/create/load different than:
drop table if exists demo\;
create table demo (id bigint generated always as identity primary key, ts timestamptz)\;
insert into demo(ts) values(now());

Is it run in a single transaction?

4

| FranckPachot
July 24 |

  • | - |

Hi Mark,

  1. Yes hash sharding on the first column is the default. The Performance Advisor should give a recommendation. I’ll check if it gives one for this case
  2. The views pg_stat_activity and pg_stat_statements are per-node and when you connect to YB Managed, it goes to any node. This may explain why you don’t see a query run from another connection. Those views are aggregated in the UI (Performance / Live Queries is from all pg_stat_activity and Performance / Slow Queries is from all pg_stat_statements )

How do I view all statements running on the cluster given that pg_stat_activity is per-node not per-cluster?

From the perf section of the UI the Operations/sec graph has lines for inserts and deletes, but in the Average Latency graph I only see lines for inserts – despite deletes having a similar latency.

  1. The index creation is online (with backfill) and if it fails may leave the index invalid. This is visible with \d <tablename>.
    To understand better, how is this drop/create/load different than:
drop table if exists demo\;
create table demo (id bigint generated always as identity primary key, ts timestamptz)\;
insert into demo(ts) values(now());

Is it run in a single transaction?

Sequence is:

  • drop table in separate transaction
  • create table in separate transaction
  • insert many rows
  • create 3 secondary indexes via one statement

My issue wasn’t so much the failure as it was the inability to recover after the failure. As in I restarted the benchmark: drop table, create table, … and there were still errors. Regardless, I don’t expect an internal error that made the cluster unusable for some time although that appears to have resolved itself this morning.

Given that PG already supports “create index concurrently” it is confusing that YSQL uses concurrent by default and then adds “create index nonconcurrently”.
https://docs.yugabyte.com/preview/api/ysql/the-sql-language/statements/ddl_create_index/

5

Currently there’s no global view from the SQL API. The managed service has it in the UI and API. Or you can connect to each node and query pg_stat_activity (I know this is not a solution though the single endpoint but with VPC peering you can connect to each node). We will add a global view probably, but this needs to be scalable (reading from all nodes is not)

I’ll ask the managed cloud team to look at it

I’ll ask the managed cloud team to look at it

Right, but the semantic is different:

  • PostgreSQL ‘nonconcurrently’ locks tables for the whole duration of the DDL. YugabyteDB doesn’t lock and noncncurrently is to be used when you know there are not concurrent updates. It is there to be used on empty tables before any load
  • PostgreSQL ‘concurently’ still locks the tables at some point which is not acceptable for online index creation in a scalable database. YugabyteDB does optimistic locking: does not lock concurrent DML but they may get a serializable error to retry if the new index invalidates their cursor.
6

@MarkCallaghan note that the free tier is really small. Nice to be used to test application but not performance. What you encounter may be a consequence of it. For benchmarks, it is recommended to run on a larger cluster. On your own infrastructure (YugabyteDB is open source, you have all features) or in the paid tiers (sales can give free credits for proof of concepts)

1 Like
7

Frank,

Let’s get tickets going on both the internal error and the slow queries problem. Is he seeing both issues on a free tier?

Juan

8

I used the paid tier to create a 3 node cluster with node level HA. I deleted the cluster yesterday.

Cluster name was me1, it ran v2.14.10-2-b1, was created on 7/23/2023 at 15:40, the ID was 6265946d-b1ea-4870-8fd7-ef914fa4e407