0

I'm using Python 3.7 with SQLAlchemy on Ubuntu 18.04. I have two MySQL Databases - a local instance hosted on bare metal and an Amazon RDS instance. Both are accessible from MySQL Workbench on my machine with no noticeable speed delays (I have 15 Mbps download, 0.9 Mbps download speeds).

As for my schema, it's extremely barebones - most tables have around twenty-thirty columns, but there are no foreign keys.

I'm writing a script that is attempting to load approximately 300 entries from a file. I can verify this (I am using logging to write my INSERT statements to a file). When I am performing this locally, it takes only a moment to complete. When I am writing to AWS, however, the session.commit() statement takes up to 10 minutes to complete. I have isolated the time delay to this line.

RDS Configuration:

  • max_allowed_packet = 1073741824 (1G)
  • key_buffer_size = 16777216 (16M)
  • innodb_buffer_pool_size = 402653184 (384M)

What are some potential issues with my configuration that might be causing such a significant slowdown?

Improve this question
3
  • there timings are very weird. Does monitoring show anything? Is a streaming replica present?
    – user953
    Jun 9, 2021 at 8:41
  • 1
    don't use python if you only want to make a bulk insert check the manual for bulk insert for hint and use LOAD INFile
    – nbk
    Jun 9, 2021 at 11:25
  • 1
    SQLAlchemy does not perform the operations directly. Did you issue flush before committing? If not, it is the first thing commit will do, so it may be the case you are perfoming the inserts all at once in those 10 minutes.
    – Jean Jung
    Jun 13, 2021 at 0:51

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.