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 sqlalchemy+unsubscr...@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/5c001ebb-8efb-4e6b-8ec8-85c39964387a%40www.fastmail.com.
