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?

New contributor
Ethan Hill is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
3
  • there timings are very weird. Does monitoring show anything? Is a streaming replica present? – ik_zelf Jun 9 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 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 yesterday

Your Answer

Ethan Hill is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.