I'm not even sure the problem is with the batch insert function itself, creating half a million dicts in Python is going to cause you a bit of a bad time. That said, I've run into the same problem with a little toy project, which works around this with a 'bulk save' interface. With a minimal change you get to create the object/mapping to insert at the time you need it, push it into the bulk saver, and it will flush every time it hits a threshold. The actual threshold to use will depend a bit on your database and driver, and I imagine connection latency plays an important part as well.
Anyway, use what you can: https://github.com/edelooff/smallville/blob/master/scripts/seed.py#L27 On Thu, Nov 14, 2019 at 8:42 PM James Fennell <[email protected]> wrote: > Because the memory spike was so bad (the application usually runs at 250mb > RAM, and it went up to a GB during this process), I was able to find the > problem by running htop and using print statements to discover where in the > execution the Python code was when the RAM spike happened. > > I unfortunately don't have any advice on actual good tools for tracking > RAM usage in Python programs but would to leave to hear if others do. > > > > On Thu, Nov 14, 2019 at 12:41 PM Soumaya Mauthoor < > [email protected]> wrote: > >> What did you use to profile memory usage? I've recently been >> investigating memory usage when loading data using memory_profiler and >> would be interested to find out about the best approach >> >> On Thu, 14 Nov 2019, 17:16 James Fennell, <[email protected]> >> wrote: >> >>> Hi all, >>> >>> Just sharing some perf insights into the bulk operation function >>> bulk_insert_mappings. >>> >>> I was recently debugging a SQL Alchemy powered web app that was crashing >>> due to out of memory issues on a small Kubernetes node. It turned out to be >>> "caused" by an over optimistic invocation of bulk_insert_mappings. >>> Basically I'm reading a CSV file with ~500,000 entries into a list of >>> dictionaries, and then passing it into the bulk_insert_mappings function at >>> once. It turned out the SQL Alchemy work was using 750mb of RAM, which was >>> enough to OOM the small node the web app was running on. >>> >>> A simple workaround is to split the list of 500,000 entries into chunks >>> of 1000 entries each, and then call bulk_insert_mappings on each chunk. >>> When I do this, the extra memory usage is not even noticeable. But also, it >>> seems that this chunked approach is actually faster! I might benchmark that >>> to quantify that. >>> >>> Thought it was interesting. I wonder would it be worth adding to the >>> docs on bulk_insert_mappings? Given that function is motivated by >>> performance, it seems it might be relevant. >>> >>> James >>> >>> >>> >>> >>> -- >>> 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/CALDF6i1sx_QmW1v1jvopT-iWZqmSGmJ7JpJi10egNnbE0D7FMQ%40mail.gmail.com >>> <https://groups.google.com/d/msgid/sqlalchemy/CALDF6i1sx_QmW1v1jvopT-iWZqmSGmJ7JpJi10egNnbE0D7FMQ%40mail.gmail.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/CAN14jWTdFQ7tSyhr71H_wCX_JXT58S40Q4MN7qte6pE2N-%2BOLw%40mail.gmail.com >> <https://groups.google.com/d/msgid/sqlalchemy/CAN14jWTdFQ7tSyhr71H_wCX_JXT58S40Q4MN7qte6pE2N-%2BOLw%40mail.gmail.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/CALDF6i3KeKteJ8aV5XHM-g%2BMsAZdRoAnGqLfCxVETfj8vsDTSg%40mail.gmail.com > <https://groups.google.com/d/msgid/sqlalchemy/CALDF6i3KeKteJ8aV5XHM-g%2BMsAZdRoAnGqLfCxVETfj8vsDTSg%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > -- Elmer -- 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/CAA7D1eF5QdRqV_7fGzdf3XHaOkRV2Ky8sUvFgPWG-dj9BeLO_Q%40mail.gmail.com.
