Trouble with ttserve psql?

[matschaffer]

I tried to load https://grafana.safecast.cc/dashboard/db/scnano-radiation today and noticed the ttserve psql panels aren't loading.

This appears to have also tripped the 5xx alarm for the reporting app.

Haven't dug in to see what's going on but figured I'd open this for visibility/collaboration.

/cc @robouden @rayozzie

[rayozzie]

I am away and can’t do much research, but the dB seems to be performing ok according to aws. From a UI perspective queries seem ok if a bit pokey. Not sure why there was that strange behavior in the write iops graph for a few hours.

[image1.png]

On Aug 5, 2018, at 10:42 PM, Mat Schaffer notifications@github.com<mailto:notifications@github.com> wrote:

I tried to load https://grafana.safecast.cc/dashboard/db/scnano-radiation today and noticed the ttserve psql panels aren't loading.

This appears to have also tripped the 5xx alarm for the reporting app.

Haven't dug in to see what's going on but figured I'd open this for visibility/collaboration.

/cc @roboudenhttps://github.com/robouden @rayozziehttps://github.com/rayozzie

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/Safecast/safecastapi/issues/485, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AAuhPT7sMyKqea1kSlGco80dBssiq3vmks5uN60fgaJpZM4VvqyU.

[matschaffer]

Thanks @rayozzie - wondering maybe the queries don't work well for the table size at this stage.

Here's an example from grafana:

SELECT
 to_timestamp(value->>'when_captured','YYYY-MM-DD"T"HH24:MI:SS"Z"') AS time,
 (value->>'lnd_7318u')::real as lnd_7318u
FROM
 data
WHERE
 ((value->>'device')::bigint = $device::bigint) AND (value->>'lnd_7318u' != '') AND $__timeFilter(modified)
ORDER BY
 time

Not sure if I have creds to try it directly but maybe I can just grab them from a ttserve instance and run an explain.

We also have ingest's elasticsearch so not sure we need to necessarily add indexing to make it faster for dashboards as long as it's working for ttserve's purposes.

[rayozzie]

I am not a sql person, so forgive the naive q: when you have a query like this and you want to make it faster, is there some simple/trivial way to create a materialized view or persistent indexes of some kind to suffer the penalty on insertion - which is best for our relatively low insertion rate?

On Aug 6, 2018, at 9:40 AM, Mat Schaffer notifications@github.com<mailto:notifications@github.com> wrote:

Thanks @rayozziehttps://github.com/rayozzie - wondering maybe the queries don't work well for the table size at this stage.

Here's an example from grafana:

SELECT to_timestamp(value->>'when_captured','YYYY-MM-DD"T"HH24:MI:SS"Z"') AS time, (value->>'lnd_7318u')::real as lnd_7318u FROM data WHERE ((value->>'device')::bigint = $device::bigint) AND (value->>'lnd_7318u' != '') AND $__timeFilter(modified) ORDER BY time

Not sure if I have creds to try it directly but maybe I can just grab them from a ttserve instance and run an explain.

We also have ingest's elasticsearch so not sure we need to necessarily add indexing to make it faster for dashboards as long as it's working for ttserve's purposes.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/Safecast/safecastapi/issues/485#issuecomment-410711537, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AAuhPTc--pV2xs8QbUwfOvgA1fjl8UD8ks5uOEdegaJpZM4VvqyU.

[rayozzie]

For example, by just grepping the web, I found this. Would this just magically increase query performance without even changing the queries? Do I have this even remotely right?

CREATE INDEX idxgin ON data USING gin (value);

From: Ray Ozzie ray@ozzie.net Date: Monday, August 6, 2018 at 9:44 AM To: Safecast/safecastapi reply@reply.github.com Cc: Safecast/safecastapi safecastapi@noreply.github.com, Mention mention@noreply.github.com Subject: Re: [Safecast/safecastapi] Trouble with ttserve psql? (#485)

I am not a sql person, so forgive the naive q: when you have a query like this and you want to make it faster, is there some simple/trivial way to create a materialized view or persistent indexes of some kind to suffer the penalty on insertion - which is best for our relatively low insertion rate?

On Aug 6, 2018, at 9:40 AM, Mat Schaffer notifications@github.com<mailto:notifications@github.com> wrote:

Thanks @rayozziehttps://github.com/rayozzie - wondering maybe the queries don't work well for the table size at this stage.

Here's an example from grafana:

SELECT

to_timestamp(value->>'when_captured','YYYY-MM-DD"T"HH24:MI:SS"Z"') AS time,

(value->>'lnd_7318u')::real as lnd_7318u

FROM

data

WHERE

((value->>'device')::bigint = $device::bigint) AND (value->>'lnd_7318u' != '') AND $__timeFilter(modified)

ORDER BY

time

Not sure if I have creds to try it directly but maybe I can just grab them from a ttserve instance and run an explain.

We also have ingest's elasticsearch so not sure we need to necessarily add indexing to make it faster for dashboards as long as it's working for ttserve's purposes.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/Safecast/safecastapi/issues/485#issuecomment-410711537, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AAuhPTc--pV2xs8QbUwfOvgA1fjl8UD8ks5uOEdegaJpZM4VvqyU.

[matschaffer]

I haven't tried gin indexes myself. More familiar with specific column indexes (e.g., https://github.com/Safecast/safecastapi/blob/master/db/structure.sql#L986) - I haven't tried adding those on jsonb column keys though. But perhaps @Frangible has.

[rayozzie]

http://bitnine.net/blog-postgresql/postgresql-internals-jsonb-type-and-its-indexes/

From: Mat Schaffer notifications@github.com Reply-To: Safecast/safecastapi reply@reply.github.com Date: Monday, August 6, 2018 at 10:17 AM To: Safecast/safecastapi safecastapi@noreply.github.com Cc: Ray Ozzie ray@ozzie.net, Mention mention@noreply.github.com Subject: Re: [Safecast/safecastapi] Trouble with ttserve psql? (#485)

I haven't tried gin indexes myself. More familiar with specific column indexes (e.g., https://github.com/Safecast/safecastapi/blob/master/db/structure.sql#L986) - I haven't tried adding those on jsonb column keys though. But perhaps @Frangiblehttps://github.com/Frangible has.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/Safecast/safecastapi/issues/485#issuecomment-410723364, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AAuhPcBEWjZoOL6kk1hM858Sq7b9QGd5ks5uOE_ggaJpZM4VvqyU.

[matschaffer]

So could be just adding this would speed things up a bit:

CREATE INDEX index_data_on_device ON data (((value ->> 'device')::bigint));

Always good to check analyze before/after but guessing at least having the device number indexed would help cut down the result set quite a bit for the second lnd_7318u filter.