Forums

Accessing MySQL settings

I'm running an always-on script to pull data from an outside source and save it to my database on pythonanywhere (so I can then display that data on my django web app). I ran into a problem where I couldn't make any other changes to the database while this script was running, like migrating or deleting rows.

Running show processlist; in a mysql console shows that the processes are Waiting for table metadata lock

I did some googling and there's some advice that setting the variable innodb_autoinc_lock_mode to 2 (instead of the default, 1) can help with this problem. https://dba.stackexchange.com/a/193060

Is it possible to change these MySQL settings on pythonanywhere? I don't think I have admin access to the database. Otherwise, has anyone else encountered this problem? Advice is appreciated.

Unfortunately that's not a configurable option for our MySQL databases -- the servers are shared between multiple users, so we only have one set that has to apply to everyone.

What code are you using to do the bulk insert? If it's using Python code, you can split it up into batches quite easily. In the Python itertools docs there is this function

def grouper(iterable, n, fillvalue=None):
    "Collect data into fixed-length chunks or blocks"
    # grouper('ABCDEFG', 3, 'x') --> ABC DEF Gxx"
    args = [iter(iterable)] * n
    return zip_longest(*args, fillvalue=fillvalue)

....which splits a list into a sequence of fixed-length lists:

>>> for chunk in grouper(range(20), 6):
...     print(chunk)
... 
(0, 1, 2, 3, 4, 5)
(6, 7, 8, 9, 10, 11)
(12, 13, 14, 15, 16, 17)
(18, 19, None, None, None, None)

...so you can use that to split up your data and then do a sequence of fixed-size inserts; the following code would do it in batches of 50,000

DATABASE_CHUNK_SIZE = 50000

for group in grouper(db_rows, DATABASE_CHUNK_SIZE):
    cursor.executemany(
        "INSERT INTO yourtable (col1, col2, col3) VALUES (%s, %s, %s);",
        [x for x in group if x is not None]
    )