[Media Viewer] Performance on clicks is slow

mahalakshme commented 1 month ago

https://avni.freshdesk.com/a/tickets/4775

Issue:

On clicking MediaViewer and on clicking a image - take around 1 min to load. Some users feel this used to take only around 5 seconds before

AC:

The below actions(basically any action in Media Viewer) should not take more than 5 secs to load

On click of 'Media Viewer' box in home page
On click of an image in Media Viewer
On selection of any filters and on clicking 'Apply filter' on Media Viewer
Validate the above performance improvement using gatling

Technical suggestions:

Clicking on an image/media we need not again call /et/media/search? since we would already have the pre-signed image URLs to fetch or if it has got expired can fetch the pre-signed URL for that image alone.
On moving across each image on the carousel additional fetching of consecutive images can be done, instead of fetching everything in one go
When rendering the full page, pre-signing images link for thumbnails should suffice, instead of pre-signing original image link as well

himeshr commented 1 month ago

We have satisfied the AC requirements except for writing gatling tests.. Not sure how we would do that as well.

AchalaBelokar commented 1 month ago

[ ] the page number count is not showing in the lane of next and previous button

1t5j0y commented 1 month ago

@AchalaBelokar reported issue is same as prod behaviour and not part of the AC of this card.

mahalakshme commented 2 weeks ago

@1t5j0y still it seems to be slow as before, also page 1 call is going for page 0 itself. And clicking on an image also renders it very slowly, not sure if there was some issue in deployment.

1t5j0y commented 2 weeks ago

Bug was introduced into ETL where new indexes are created for the same columns on media table on each run. 56 orgs currently have 100+ indexes on the corresponding 'media' tables.

Query to check impact:

select tm.schema_name, tm.name, count(*)from index_metadata im join table_metadata tm on im.table_metadata_id = tm.id
where im.column_id is null
group by tm.schema_name, tm.name;

Steps for tech QA

select an affected org for which you are testing
delete from index_metadata where table_metadata_id = (select id from table_metadata where schema_name = '<affected_org_schema_name>' and name = 'media');
drop all indexes on media table for org you are testing against (can do this easily in intellij ui by navigating to schema -> 'media' table -> indexes -> right click -> drop)
run etl couple of times for the affected org to check that indexes are recreated and not repeated

These steps will be repeated on prod for all affected orgs after QA pass and deployment of fix.

mahalakshme commented 2 weeks ago

@1t5j0y forgot to add the screen recording(took yesterday) for the issue I noticed. Adding for reference to help the QA as well: https://drive.google.com/file/d/1w5q-OCYu8z-BVPIAhnLN-v6EL5NNXfOS/view?usp=drive_link

himeshr commented 2 weeks ago

Testing results

Validated that the fix works as expected, without creating duplicates.

SQL to bulk drop all the indexes from media table and index_metadata(media) are as follows, the next etl run should only create 1 set of required indexes


select 'drop index "' || tm.schema_name|| '"."' || im.name || '";' from public.index_metadata im
join table_metadata tm on im.table_metadata_id = tm.id
where tm.name = 'media';

delete from index_metadata where table_metadata_id = (select id from table_metadata where name = 'media');

1t5j0y commented 1 week ago

/etl/media/search was slow despite changes to query with improved plans and several query styles did not fix it. Simple count query on goonj.address was taking a long time which indicated that the query planner had bad statistics. Ran (on prerelease and prod):

reindex (verbose) table goonj.address;
reindex (verbose) table goonj.media;
VACUUM (FULL, ANALYSE) goonj.address;

and query performance is now much better. Created avniproject/avni-etl#114 to run this in a scheduled manner.

Additionally, optimised resource fetching from media client.

AchalaBelokar commented 1 week ago

[ ] I Login with maha@gvamp I created a activity with name Gaj which is not reflected on media viewer.. Not showing the images also ..but data entry app it is showing. ... platform : Prerelease

himeshr commented 1 week ago

@AchalaBelokar

[ ] I Login with maha@gvamp I created a activity with name Gaj which is not reflected on media viewer.. Not showing the images also ..but data entry app it is showing. ... platform : Prerelease

Please note that i have configured prerelease ETL to use a different DB than prerelease avni server, to test the postgres upgradation.. Therefore you'll not see recent entities data there as of now..

To unblock your testing, we can revert the above change.

mahalakshme commented 1 week ago

@himeshr yeah releasing 10.1.1 cards are higher priority, so we can do the needful asap to unblock the testing since it will help in testing fixes for 3 tickets.

1t5j0y commented 1 week ago

@himeshr has already reverted the change. ETL for gvamp_uat is failing on prerelease.

Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [create table "gvamp_uat".village_village_form ("id" integer, "uuid" text, "is_voided" boolean, "created_by_id" integer, "last_modified_by_id" integer, "created_date_time" timestamp with time zone, "last_modified_date_time" timestamp with time zone, "organisation_id" integer, "name" text, "individual_id" integer, "address_id" integer, "earliest_visit_date_time" timestamp with time zone, "max_visit_date_time" timestamp with time zone, "encounter_date_time" timestamp with time zone, "encounter_location" point, "cancel_date_time" timestamp with time zone, "cancel_location" point, "legacy_id" text, "latest_approval_status" text, "Account  name" text, "Total Households" numeric, "Types of communities living" text, "Main Occupation of the Village" text, "Number of Male residents" numeric, "TotalVillagePopulation" numeric, "Number of Female Residents" numeric, "Description of the communities" text, "Center / Field office" text, "Account  name" text );
.
.
Caused by: org.postgresql.util.PSQLException: ERROR: column "Account  name" specified more than once

Have asked @AchalaBelokar to test with another org.

dinesh2096 commented 5 days ago

@1t5j0y we have created the work order and the uuid is '42680a41-c290-481c-9f58-a9946c74c9b6' we can able to see the data in DB but not in ETL table so the image we have added is not reflecting in the media viewer env : per-release org : rwb24uat user : dinesh@rwb24uat

1t5j0y commented 5 days ago

Images are visible in media viewer.

dinesh2096 commented 4 days ago

QA Reference :

Click here to watch the video

avniproject / avni-media