Performance problems accessing detections about aatams HOT 10 CLOSED

Still seems to be slow on prod...

from aatams.

I have reached the time-box threshold for this bug, henceforth I'm going to document what I know, what I've tried etc. If I think of anything else, I'll add it to the list.

Notes

• it is the select count(*) with filter applied (i.e. where clause) which is the slow part, although on my development machine, it is not so slow (see [my stackoverflow question]
• there were some differences w.r.t. to indexes between production and my dev DB, but these differences have now all been eliminated, with little effect. See this SO question for a description of how to list indexes.
  (http://stackoverflow.com/questions/19149585/postgresql-query-plan-discrepancy))
• the detections materialized view is now truncated before being re-generated. Apart from being much faster to execute, the data in the resulting table should be less bloated
• with the current design it may be impossible to get the select count to execute in a reasonable amount of time. i.e. clustering the table around the tag ID instance would make filtering by tag ID fast at the expense of filtering by project, for example

from aatams.

Comparing the same query on imos-2 against my machine, my machine rans the query about 10x faster. Examining the situation with iostat showed that my machine provided approx 2000 IOPS whereas imos-2 provided between 100-200.

We believe that this might be the cause for the x10 slowness witnessed in production.

from aatams.

Another comparison with imos-2-test (with new storage attached) - the query run time compares with my dev machine (~30s).

The query being:

psql aatams3 -c "select count(*) from detection_extract_view_mv where project = 'AATAMS Rowley shoals';"

from aatams.

imos-2-test iostat:

from aatams.

Jon's machine iostat:

from aatams.

After moving to gold storage on NSP:

postgres@imos-2:~$ time psql aatams3 -c "select count(*) from detection_extract_view_mv where project = 'AATAMS Rowley shoals';"
 count  
--------
 268652
(1 row)


real    0m2.595s
user    0m0.084s
sys 0m0.072s

from aatams.

After moving to gold storage on NSP:

postgres@imos-2:~$ time psql aatams3 -c "select count(*) from detection_extract_view_mv where project = 'AATAMS Scott reef';"
 count  
--------
 122096
(1 row)


real    0m5.020s
user    0m0.048s
sys 0m0.092s

from aatams.

@pblain @jkburges According to Tim "it works like a treat". So I'm happy to close it.

Pete, would you like to have a completed VIT item for that? To be honest the work was done as part of the previous iteration...

from aatams.

I'll add the completed VIT so it's in the stats.

from aatams.