Comments (10)
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.
Related Issues (20)
- Detections from mobile receivers deployed on Gliders HOT 1
- Tag Expected Life Time (days) should be mandatory when registering a new tag HOT 1
- Exceptions in log when creating new tags HOT 1
- Generate error when uploading detection files as Receiver Events HOT 1
- Issue with sdkman installation blocking Travis build
- CAAB species names listing - updates HOT 2
- Prevent tag releases with no morphological measurements
- Taking people off projects deletes underlying metadata
- Flag people/organisation as not belonging to a project anymore HOT 1
- QC.R stops with 'Error in validityMethod(object) : invalid extent: ymin >= ymaxi' HOT 1
- All links on Getting Started page dysfunctional
- Dual range test tag
- Implement tests to validate station/receiver geographical coordinates HOT 1
- Modify table structure in receiver page to be identical to the one in the receiver recovery page
- Search functionality is not fully functional and is inconsistent between web app instances
- Users should be able to query the database to retrieve project-specific data and metadata
- Inconsistent SRIDs in database geom fields
- Receiver deployment metadata present in aatams and absent from AODN portal extract HOT 16
- tags not appearing in UI
- Possible to upload duplicates of tag deployments without recoveries HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from aatams.