p-null
commented
3 years ago Add another query when accessing Hasura UI that times out here:
SELECT
COALESCE(
json_agg(
Row_to_json(functions)
),
'[]' :: JSON
) from (
SELECT * FROM (
SELECT p.proname::text AS function_name,
pn.nspname::text AS function_schema,
pd.description,
CASE
WHEN p.provariadic = 0::oid THEN false
ELSE true
END AS has_variadic,
CASE
WHEN p.provolatile::text = 'i'::character(1)::text THEN 'IMMUTABLE'::text
WHEN p.provolatile::text = 's'::character(1)::text THEN 'STABLE'::text
WHEN p.provolatile::text = 'v'::character(1)::text THEN 'VOLATILE'::text
ELSE NULL::text
END AS function_type,
pg_get_functiondef(p.oid) AS function_definition,
rtn.nspname::text AS return_type_schema,
rt.typname::text AS return_type_name,
rt.typtype::text AS return_type_type,
p.proretset AS returns_set,
( SELECT COALESCE(json_agg(json_build_object('schema', q.schema, 'name', q.name, 'type', q.type)), '[]'::json) AS "coalesce"
FROM ( SELECT pt.typname AS name,
pns.nspname AS schema,
pt.typtype AS type,
pat.ordinality
FROM unnest(COALESCE(p.proallargtypes, p.proargtypes::oid[])) WITH ORDINALITY pat(oid, ordinality)
LEFT JOIN pg_type pt ON pt.oid = pat.oid
LEFT JOIN pg_namespace pns ON pt.typnamespace = pns.oid
ORDER BY pat.ordinality) q) AS input_arg_types,
to_json(COALESCE(p.proargnames, ARRAY[]::text[])) AS input_arg_names,
p.pronargdefaults AS default_args,
p.oid::integer AS function_oid
FROM pg_proc p
JOIN pg_namespace pn ON pn.oid = p.pronamespace
JOIN pg_type rt ON rt.oid = p.prorettype
JOIN pg_namespace rtn ON rtn.oid = rt.typnamespace
LEFT JOIN pg_description pd ON p.oid = pd.objoid
WHERE
pn.nspname::text !~~ 'pg_%'::text
AND(pn.nspname::text <> ALL (ARRAY ['information_schema'::text]))
AND NOT(EXISTS (
SELECT
1 FROM pg_aggregate
WHERE
pg_aggregate.aggfnoid::oid = p.oid))) as info
WHERE function_schema=''
AND has_variadic = FALSE
AND returns_set = TRUE
AND return_type_type = 'c'
ORDER BY function_name ASC
) as functions;
I have isolated the slowness that it was not caused by network issue or Hasura app itself.
munjalpatel
ddorian
m-iancu
FranckPachot
maaft
mislam-yb
morigs
ammirator-administrator
Jira Link: DB-2522
Commonly PG-compatible tools and ORMs (e.g. Hasura examples in comments below) query the database system catalog to build their internal metadata and/or build their UI pages. For these tools to work seamlessly in YSQL it is important the performance of these queries are comparable with vanilla Postgres.
Why are some system queries slow
Often, these queries end up being quite complex either directly (large complex generated by the tool/ORM) or indirectly (e.g. simple-looking queries to
information_schemaviews many of which are very complex).For example the definition for the commonly-used
information_schema.columnsview is ~120 lines of SQL (See information_schema.sql#L653:L773).Therefore, the current default
EXPLAIN ANALYZEplan for the "simple" queryhas around ~60 lines and involves 9 joins (see information_schema.column EXPLAIN output). Moreover, such views sometimes come up as one of multiple join tables/views inside an already large query generated by an ORM/tool.
Additionally, in YSQL, the catalog data may not be local, so internal data lookups required for query execution will often require network roundtrips rather that just local disk access. In particular in multi-region clusters the latency cost of these roundrips can be particularly large (e.g. in the tens of milliseconds range per roundtrip). Therefore, the latency impact of picking a suboptimal plan in YSQL for these complex queries can be significantly larger than in vanilla postgres.
Current mitigation and workarounds
Longer-term plan is improve and tune our cost-based optimizer to ensure these queries are executed with optimal plans (see section below). Until then, since these queries are internally generated by ORMs rewriting them to be more efficient or add hints is typically not possible.
pg_catalogtables rather then the genericinformation_schemaviews (as the former option is typically much faster).pg_hint_tableto add hints without changing the query syntax:set enable_nestloop=falsecan help).Current Roadmap
yb_enable_base_scans_cost_modeltotrue. May require running ANALYZE manually to get table statistics (depending on the DB version)Original user report
Hi, open this pr to report and track the slowness/timeout issue when testing hasura + yb.
I am running yb(2.5.3.1) on a 4 node k8s cluster (20 cores and 60 gb) with the following (related) values:
The connected database only has 1 table with 3 records, having columns of id(int), name(text).
When accessing hasura console (the UI), some queries will be executed to display the table/schema info of the database. However I consistently get failing/timeout errors at the frontend.
By analyzing networking requests sent from the frontend, i was able to locate the queries that caused the timeout. The following is one of the queries that hasura console will execute. Here is one of them:
First exec to pod:
Then
The lantency of running normal graphql queries on application tables is fine.
The timeout/slowness occurs eveytime accessing the Data tab on the hasura UI, which pretty much makes the Data tab unusable.
Link to a comment here for more ideas: https://github.com/hasura/graphql-engine/issues/3549#issuecomment-566514142