Recalculate and replace large table without interrupting table reads

I have a suggestion that could simply your code and your SQL

You are doing round trips at this point by checking each individual someID, coming back to the client, seeing if the check failed. If the check failed, it is OK to perform the INSERT. That's another round trip. You will require some kind of locking for reads.

If you have to INSERT 10000 rows:

• In the best case, you run 10000 SELECTs are find that someID exists
• In the worst case, you run 10000 SELECTs, each someID does not exist, and do 10000 INSERTs.

Why not just perform all this with a single SQL statement ?

function doStuff(int someID) {
    getvalue = `SELECT value FROM statetable`
    INSERT IGNORE INTO targettable VALUES (someID,getvalue);
}

This works only if someID is unique in targettable

In your code, try submitting a list of someID values, iterate that list, and submit INSERT IGNORE commands for all someID values. That you can do in a START TRANSACTION/COMMIT block.

function doStuff(int someIDList) {
    int idcount = 0;
    getvalue = `SELECT value FROM statetable`
    for someID in someIDList
    do
        if idcount = 0 then
            START TRANSACTION
        endif;
        idcount++;
        INSERT IGNORE INTO targettable VALUES (someID,getvalue);
    end while;
    if idcount > 0 then
        COMMIT
    endif;
}

Many experts share many ways on how to overcome this problem. These are my suggestions to play a safe game.

Try to set the below command in a seperate session.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
use db;
SELECT COLA, COLB into outfile '/tmp/data.csv' from TABLE_NAME;
COMMIT;
exit;

Doing by this way the SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent.When you say not-consistent it means recently changing records i.e.. DML transactions that are currently in process will not be read. I assume which is in your case it is acceptable. This is also called a “dirty read.” Otherwise, this isolation level works like READ COMMITTED.

If I were to be you, the below order is what I follow.

• Check for non-peak hours for DB server also for the table that you mind to do select. By this the outfile reads/write, IOs shouldn't cost much to the server.

• Manually run this in the server back ground. And keept monitoring the server health.

$ cat bg_loaddata.sh

mysql -uroot -p*** << EOF
use db;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT COLA, COLB into outfile '/tmp/data.csv' from TABLE_NAME;
COMMIT;
exit;
EOF


$ nohup ./bg_loaddata.sh > bg_loaddata.log &

• If you see any inserts or updates that are taking longer time to complete in show processlist; You may wait for sometime, but if it is causing severe turbulence then goahead and kill the connection id in processlist for your select ...into outfile ... [I'm 99% sure this will not happen, but always to be prepared for any back fires]

By this way you can be sure for N number of records to be retrieved it taks N number of seconds, and no issues can be faced by doing this way. And whenever you need this to be run next time put the script in cron and have a sound sleep. :) [Ofcourse you have monitoring metrics to wake you up]-> Just to be aware before someone surprises you with the NEWS.

All the best !!