Thanks for reply . I am using postures 9.6 DB and CPU / Memory block is at both DB and application side.
Thanks for suggestion and using EXPLAIN i got very important information like even though we have fetched 60 records some 40000 rows were affected and it seems like some improper joins are happening because of which if same query we perform using PostgreSQL ( pg-admin) db tool also we got memory exceptions. In SQL alchemy logs we can see improper joins are happening like marked in red below . It seems some circular reference is happening in parent child relationships. We have example scenario like Table A - Recording - Parent >> define tags relationships here Table B - Video --> Child of Table A >> define tags and recordings relationships here Table C - Tags ---> Child of both Table A and Table B . --> define both video and recordings here Recordings can have many videos and tags and videos can also have many tags . Here if we fetch videos , it seems it join tags and then in tags it join recordings and then again tags . Please note here we just define db relationships and don't explicitly write SQL queries and this is handled by flask sqlalchemy. So if we define properly relationships then i think this issue will be resolved as well. ******************************************************************************************************************************** SELECT required columns FROM (SELECT videoclip columns FROM video_clip LIMIT 100) AS anon_1 LEFT OUTER JOIN recording_session AS recording_session_1 ON recording_session_1.recording_id = anon_1.video_clip_recording_id LEFT OUTER JOIN maindriver_info AS maindriver_info_1 ON maindriver_info_1.main_driver_id = recording_session_1.main_driver_id LEFT OUTER JOIN auxdriver_info AS auxdriver_info_1 ON auxdriver_info_1.aux_driver_id = recording_session_1.aux_driver_id LEFT OUTER JOIN car_info AS car_info_1 ON car_info_1.automobile_id = recording_session_1.automobile_id LEFT OUTER JOIN camera_info AS camera_info_1 ON camera_info_1.camera_id = recording_session_1.camera_id LEFT OUTER JOIN tag AS tag_1 ON recording_session_1.recording_id = tag_1.recordingsession_content_id LEFT OUTER JOIN driving_speed AS driving_speed_1 ON recording_session_1.recording_id = driving_speed_1.recording_id LEFT OUTER JOIN contextlabel AS contextlabel_1 ON recording_session_1.recording_id = contextlabel_1.recording_id LEFT OUTER JOIN tag AS tag_2 ON anon_1.video_clip_video_clip_id = tag_2.videoclip_content_id *LEFT OUTER JOIN recording_session AS recording_session_2 ON recording_session_2.recording_id = tag_2.recordingsession_content_id* *LEFT OUTER JOIN maindriver_info AS maindriver_info_2 ON maindriver_info_2.main_driver_id = recording_session_2.main_driver_id* *LEFT OUTER JOIN auxdriver_info AS auxdriver_info_2 ON auxdriver_info_2.aux_driver_id = recording_session_2.aux_driver_id* *LEFT OUTER JOIN car_info AS car_info_2 ON car_info_2.automobile_id = recording_session_2.automobile_id * *LEFT OUTER JOIN camera_info AS camera_info_2 ON camera_info_2.camera_id = recording_session_2.camera_id * *LEFT OUTER JOIN driving_speed AS driving_speed_2 ON recording_session_2.recording_id = driving_speed_2.recording_id * *LEFT OUTER JOIN contextlabel AS contextlabel_2 ON recording_session_2.recording_id = contextlabel_2.recording_id * *LEFT OUTER JOIN contextlabel AS contextlabel_3 ON anon_1.video_clip_video_clip_id = contextlabel_3.videoclip_content_id* Regards Nitin On Sunday, 27 October 2019 03:13:03 UTC+5:30, Mike Bayer wrote: > > Hi there - > > > the first thing you should be looking at is the SQL output, the code here > is not very important. have you turned on SQL logging on both the Python > side, as well as enabled server-side metrics, such as if this is MySQL you > want to use slow query log ? have you done EXPLAIN on the slow queries in > question ? is the CPU blockage in the Python application side or in the > database? what kind of database? is the slowness during the waiting > for queries to complete or in the fetching of objects? these are all > questions you can get answers using to in order to start understanding the > problem. > > Take a look at > https://docs.sqlalchemy.org/en/13/faq/performance.html#query-profiling > for some guidance on getting started on this. > > > > > > On Sat, Oct 26, 2019, at 1:50 PM, Nitin Jain wrote: > > Hi all , > > We are observing very high memory and cpu consumption almost 100% CPU and > memory for one of the API call witch select query . > > > In our model we have many tables like > > > TABLE A - Parent ( recording session) > > > Table B - Child of A ( table a id as FK) (video ) > > > Table C - Child of B ( table C id as FK) ---> Very high Memory / CPU ( > images) > > > In addition to above table we have many other related tables (parent - > child ) also . > > > Table A is like recording session which contains many videos ( Table B) > which contains many images ( Table C) . > > > So for 30 min recording session we have 30 clips of videos and then 30 * > 30 = 900 images i.e image metadata and not actual image. > > > We have created relationships between tables using db.relationship so that > we can use filter operations as well. > > > Once we trigger the query to fetch say 900 images metadata and not actual > images then query is almost blocked with 100 % CPU and Memory. > > . > > Please let me know how to debug this issue . We suspect issue in db > relationships / some infinite loop in query . > > > *class *Images(db.Model): > __tablename__ = *'images'* > > > videoclips = db.relationship(*'VideoClip'*, backref=*'images'*, > lazy=*'joined'*) > > > > *class *VideoClip(db.Model): > __tablename__ = *'video_clip'* > > > images = db.relationship(*'*Images', backref=*'*video_clip', lazy=*'joined'*) > > > Similarly there are many one to many relationships are defined. Is it related > to lazy=joined which i > > have used for filter operations. > > > Please let me know if anyone has faced this kind of issues. > > > Regards > > Nitin > > > > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected] <javascript:>. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/0ba4d972-6a65-49a6-993f-1db65b7b8a33%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/0ba4d972-6a65-49a6-993f-1db65b7b8a33%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/09d2354f-9e5d-4b06-9d4f-2ab394dcbdef%40googlegroups.com.
