How to update 400k rows on a production MySQL db and don't kill it

Answer 1

Hi,

What I recommend.

1. Add an isProcessed column to your table.
2. Make your script work on a chunk of, say, 1k rows for the first run (of course select only rows that are not processed).
3. Benchmark it.
4. Adjust the chunk size if needed.
5. Build another script that calls this one at intervals.

Don't forget to add some sleep time in both your scripts!

This will work if your change does not need to be continuous (and I don't think it has to be). If you have to do it all at once you should put your database offline during the time the script runs.

Answer 2

i will Add:

Why you create in each loop a new connexion and close it heh !!!

And maybe you can use db.autocommit(False) specially for the UPDATE and do a db.commit() for each 100 rows or something ;

and like Alin Purcaru you should do some benchmark as well.

Hope this can help :)

Answer 3

    db_data = db.query('''
        SELECT id AS news_id, image AS src_filename
        FROM emd_news
        ORDER BY id ASC
        LIMIT %s, %s''', offset, LIMIT_ROW_COUNT)
     # Why is there any code here at all?  If there's no data, why proceed?
     if not db_data: break