On Wed, Oct 30, 2019, at 6:56 PM, Nitin Jain wrote: > 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.
even if those joins in red are removed, that's still vastly too many joins. SQLAlchemy never emits LEFT OUTER JOIN unless explicitly told to do so. The directives which may be emitting these LEFT OUTER JOINs include: with_polymorphic="*" on a joined table inheritance mapping lazy="joined" on relationship() joinedload() in Query.options with_polymorphic(BaseClass, "*") with Query. and of course query.outerjoin() and query.select_from(some join). These join appear to be emitted from relationships that likely have lazy="joined" at the mapping level; these should be removed. Instead, use joinedload() at Query time to optimize only those relationship paths which you actually need to load. Additionally, use joinedload() only for many-to-one relationships, and try to use it only for non-nullable foreign keys, along with the innerjoin=True flag so that an inner join is used, not an outer join. For one-to-many relationships, use the selectin() loader at query time which is much more efficient than joinedload(). > > > > ******************************************************************************************************************************** > > > 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 sqlal...@googlegroups.com. >>> 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 sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/09d2354f-9e5d-4b06-9d4f-2ab394dcbdef%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/09d2354f-9e5d-4b06-9d4f-2ab394dcbdef%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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/602db7cb-e61a-4a72-8089-9c952445a669%40www.fastmail.com.
