[MySQL] Quante query inserire per volta e quando fare il commit?

[TommyB1992]

Attualmente ho un codice di questo tipo:

Codice: Seleziona tutto

for (...) {
    INSERT INTO tbl VALUES ('val1'), ('val2'), ('val3'), [...] ('valn');
    commit()
}

Ho diverse domande:
1) E' più conveniente fare il commit solo alla fine (fuori dal ciclo for)?
2) I valori da inserire sono circa 3M e faccio tranche da 100.000, sarebbe meglio 1 valore per query, 100, 10000000?

[korda]

Attualmente ho un codice di questo tipo:

Codice: Seleziona tutto

for (...) {
    INSERT INTO tbl VALUES ('val1'), ('val2'), ('val3'), [...] ('valn');
    commit()
}

Ho diverse domande:
1) E' più conveniente fare il commit solo alla fine (fuori dal ciclo for)?
2) I valori da inserire sono circa 3M e faccio tranche da 100.000, sarebbe meglio 1 valore per query, 100, 10000000?

Seguo con interesse... ho problemi di performance proprio su un costrutto analogo

Edit: in realtà io uso un modulo e la insert la faccio tramite il cmdlet della libreria, non ho idea se il commit sia già integrato nel cmdlet o se il comando si appoggi sull'eventuale configurazione di autocommit di MySQL. Fatto è che, ogni tanto, dopo qualche decina di insert il ciclo si incanta un attimo per poi proseguire. Nel mio caso devo popolare tabelle più piccole con appena qualche migliaio di record, quindi al momento ho ripiegato con uno sleep di 1 decimo di secondo dopo il comando di insert, affindandomi all'eventuale triggering del (auto)commit. Il delay dello sleep, per la mia quantità di dati attuale, è accettabile. Si tratta di un workaround temporaneo, ovviamente, ma non può essere la soluzione definitiva.

[TommyB1992]

Attualmente ho un codice di questo tipo:

Codice: Seleziona tutto

for (...) {
    INSERT INTO tbl VALUES ('val1'), ('val2'), ('val3'), [...] ('valn');
    commit()
}

Ho diverse domande:
1) E' più conveniente fare il commit solo alla fine (fuori dal ciclo for)?
2) I valori da inserire sono circa 3M e faccio tranche da 100.000, sarebbe meglio 1 valore per query, 100, 10000000?

Seguo con interesse... ho problemi di performance proprio su un costrutto analogo

Edit: in realtà io uso un modulo e la insert la faccio tramite il cmdlet della libreria, non ho idea se il commit sia già integrato nel cmdlet o se il comando si appoggi sull'eventuale configurazione di autocommit di MySQL. Fatto è che, ogni tanto, dopo qualche decina di insert il ciclo si incanta un attimo per poi proseguire. Nel mio caso devo popolare tabelle più piccole con appena qualche migliaio di record, quindi al momento ho ripiegato con uno sleep di 1 decimo di secondo dopo il comando di insert, affindandomi all'eventuale triggering del (auto)commit. Il delay dello sleep, per la mia quantità di dati attuale, è accettabile. Si tratta di un workaround temporaneo, ovviamente, ma non può essere la soluzione definitiva.

ho fatto diverse prove e ti direi che il commit lo fa in automatico dopo ogni insert.
Perchè togliendo il commit dal ciclo e mettendolo alla fine fa uno speed up non indifferente, ti conviene joinare tutti i valori da inserire in una volta sola

[korda]

ho fatto diverse prove e ti direi che il commit lo fa in automatico dopo ogni insert.
Perchè togliendo il commit dal ciclo e mettendolo alla fine fa uno speed up non indifferente, ti conviene joinare tutti i valori da inserire in una volta sola

In teoria sarebbe la cosa da fare, lo conferma il fatto che pure la query in output a mysqldump fa così.

In pratica però, così facendo, verrei a perdere una funzionalità estetica utile ad altri che usano il mio script. In quel ciclo ho integrato la visualizzazione di una barra di avanzamento. Che perderei se facessi una insert unica.

In pratica a me interesserebbe di più un avanzamento costante senza incantamenti, che non una riduzione del tempo totale. Questo, beninteso, sarebbe accettabile nel mio caso, in cui la quantità di dati da caricare è modesta.

[DoctorStrange]

Fossi in te, valuterei anche un'alternativa. Cambia la sintassi della tua query di inserimento. Invece di

Codice: Seleziona tutto

INSERT INTO Tabella VALUES (val)  (val)  (val)  (val) ...

Io proverei a riscriverla oon:

Codice: Seleziona tutto

INSERT INTO Tabella (val);INSERT INTO Tabella (val);INSERT INTO Tabella (val);INSERT INTO Tabella (val);

Il motivo è semplice. In caso di interruzione della sessione con il server DB, con la tua sintassi, perderesti l'intero numero di record scritti e, nel caso di tre milioni di record da scrivere, non è una cosa trascurabile. Con questa variante invece, anche se potrebbe impiegare di piu, in caso di interruzione della sessione, i record già scritti rimarranno persistenti, e dovrai scrivere solo l'eventuale delta mancante.

[korda]

Fossi in te, valuterei anche un'alternativa. Cambia la sintassi della tua query di inserimento. Invece di

Codice: Seleziona tutto

INSERT INTO Tabella VALUES (val)  (val)  (val)  (val) ...

Io proverei a riscriverla oin: [cpde]INSERT INTO Tabella (val);INSERT INTO Tabella (val);INSERT INTO Tabella (val);INSERT INTO Tabella (val);[/code]
Il motivo è semplice. In caso di interruzione della sessione con il server DB, con la tua sintassi, perderesti l'intero numero di record scritti e, nel caso di tre milioni di record da scrivere, non è una cosa trascurabile. Con questa variante invece, anche se potrebbe impiegare di piu, in caso di interruzione della sessione, i record già scritti rimarranno persistenti, e dovrai scrivere solo l'eventuale delta mancante.

Io davo per scontato che fosse questa la sintassi

Codice: Seleziona tutto

INSERT INTO Tabella (val);INSERT INTO Tabella (val);INSERT INTO Tabella (val);INSERT INTO Tabella (val);

e che

Codice: Seleziona tutto

INSERT INTO Tabella VALUES (val)  (val)  (val)  (val) ...

fosse solo metacodice

Nel mio costrutto do' difatti una INSERT a ciclo, in cui inserisco un solo record per volta (con tutti i campi ovviamente).

....però, dopo ogni tot, lo script fa una pausa di riflessione per poi ripartire


Edit: questo è il mio codice in PowerShell da Windows, il server MySQL sta su una macchina linux (nei test fatti la macchina Windows è una vm che sta sullo stesso host dove risiede il server MySQL)

Codice: Seleziona tutto

# import the SimplySQL module
$ErrorActionPreference= 'Stop'
try {
    Import-Module SimplySql
} catch {
    Install-Module SimplySql -Confirm:$False -Force
    Import-Module SimplySql
}
$ErrorActionPreference= 'Inquire'

# apro una connessione sul DB
Write-Host -NoNewline "Connecting... "
$ErrorActionPreference= 'Stop'
try {
    Open-MySqlConnection -Server '192.168.xxx.yyy' -Database 'Miodb' -Credential $MySQLlogin
    Write-Host -ForegroundColor Green 'DONE'
} catch {
    Write-Host -ForegroundColor Red 'FAILED'
    Write-Output "`nError: $($error[0].ToString())"
    Pause
    exit
}
$ErrorActionPreference= 'Inquire'

# raccolgo l'elenco delle tabelle presenti sul DB
$tablelist = Invoke-SqlQuery -Query 'SHOW TABLES'

# recupero la lista dei file e parso nonme tabella e data
foreach ($afile in (Get-ChildItem -Path ($sharepath + '\*.csv') -Name)) {
    $matches = @()
    $ErrorActionPreference= 'Stop'
    try {
        $afile -match "^([0-9]+)\-([a-zA-Z_\-\.\\\s0-9:]+)\.csv$" > $null
        $uptime = [Datetime]::ParseExact($matches[1], 'yyMMdd', $null) | Get-Date -format "yyyy-MM-dd"
        $tablename = $matches[2]
        if ($tablelist.Tables_in_AGMskyline -contains $tablename) {
            Write-Host -NoNewline -ForegroundColor Yellow ("Loading data for table [{0}] updated on {1}..." -f ($tablename, $uptime))
            
            $ErrorActionPreference= 'Stop'
            try {
                # svuoto la tabella dei vecchi record
                Invoke-SqlQuery -Query "TRUNCATE $tablename"
                Write-Host -NoNewline -ForegroundColor Yellow '.'
                
                # importo il csv di dati grezzi
                Invoke-SqlQuery -Query "LOCK TABLES $tablename WRITE"
                Write-Host -NoNewline -ForegroundColor Yellow '.'
                $rawdata = Get-Content -Path ($sharepath + '\' + $afile)
                $totrec = $rawdata.Count
                $parsebar = ProgressBar
                <# *** TESTING ***

                    Ho notato che il loop sulle query di INTRO, per ogni riga del csv, va un po' a rilento 
                    sulle prime 10-20 query. Poi gira fluido con le righe rimanenti.

                    Potrebbe essere un problema di "buffering" legato al commit. Qui c'è la pagina di 
                    riferimento di MySQL:

                    https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html

                    Testare con cautela (aka vedere come gestire eventuali eccezioni su singole query errate 
                    se il commit lo facessi alla fine del pacchetto intero  di intro)                   
                #>
                for ($i = 0; $i -lt $rawdata.Count; $i++) {
                    $rawvalue = $rawdata[$i] -replace "'", "\'" # escaping single quote, see https://stackoverflow.com/questions/4803354/how-do-i-insert-a-special-character-such-as-into-mysql
                    $rawvalue = $rawvalue -replace '"', "'"
                    $rawvalue = $rawvalue -replace ';', ", "
                    Invoke-SqlQuery -Query "INSERT INTO $tablename VALUES ($rawvalue)"
                    Start-Sleep -Milliseconds 100
                    $rawvalue = $null

                    # progress
                    $percent = (($i) / $totrec)*100
                    if ($percent -gt 100) {
                        $percent = 100
                    }
                    $formattato = '{0:0.0}' -f $percent
                    [int32]$progress = $percent   
                    $parsebar[2].Text = ("Add {0} out of {1} recs [{2}%] on {3}" -f (($i-1), $totrec, $formattato, $tablename))
                    if ($progress -ge 100) {
                        $parsebar[1].Value = 100
                    } else {
                        $parsebar[1].Value = $progress
                    }
                    [System.Windows.Forms.Application]::DoEvents()
                }
                $parsebar[0].Close()
                Write-Host -NoNewline -ForegroundColor Yellow '.'
                Invoke-SqlQuery -Query "UNLOCK TABLES"
                Write-Host -NoNewline -ForegroundColor Yellow '.'

                # aggiornamento update su UpdatedTables
                Invoke-SqlQuery -Query "UPDATE `UpdatedTables` SET `UPDATED` = @atime WHERE `UpdatedTables`.`ATABLE` = @atable" -Parameters @{atime = $uptime; atable = $tablename}
                Write-Host -NoNewline -ForegroundColor Yellow '.'

                Write-Host -ForegroundColor Green 'DONE'
            } catch {
                Write-Host -ForegroundColor Red 'FAILED'
                Write-Output "`nError: $($error[0].ToString())"
                if ($rawvalue -ne $null) {
                    # faccio un check dell'eventuale riga del csv che possa aver sollevato un'eccezione durante una INSERT
                    Write-Host -ForegroundColor Blue "`n>>>$rawvalue<<<"
                    Pause
                    Invoke-SqlQuery -Query "UNLOCK TABLES"
                } else {
                    Pause
                    exit
                }
            }
            $ErrorActionPreference= 'Inquire'
        } else {
            Write-Host -ForegroundColor Red "Table [$tablename] not found on DB"
            Pause
        }
    } catch {
        Write-Host -ForegroundColor Red "Bad filename <$afile>"
        # log x eccezioni non gestite, decommentare x debug
        # Write-Output "Error: $($error[0].ToString())"
        Pause
    }
    $ErrorActionPreference= 'Inquire'
}

[TommyB1992]

...

No non è meta-codice. Comunque io faccio una via di mezzo
insert into tbl (val1) (val2) (val3); insert into tbl (val4) (val5) (val6);

Con circa 10.000 inserimenti per query.

Nel tuo caso specifico potresti:
- calcolare la stima di tempo d'esecuzione e poi fare il loader (come in genere i sistemi fanno)
- inserire 10/20/30 records a botta come faccio io, così sarebbe più veloce e avresti anche il loader

Ma sono idee buttate li...

[korda]

...

No non è meta-codice. Comunque io faccio una via di mezzo
insert into tbl (val1) (val2) (val3); insert into tbl (val4) (val5) (val6);

Con circa 10.000 inserimenti per query.

Nel tuo caso specifico potresti:
- calcolare la stima di tempo d'esecuzione e poi fare il loader (come in genere i sistemi fanno)
- inserire 10/20/30 records a botta come faccio io, così sarebbe più veloce e avresti anche il loader

Ma sono idee buttate li...

Mmmh... mica male come idea: "spezzare" il ciclo in scaglioni più piccoli non è poi così complicato.

Ci proverò, grazie del consiglio

[korda]

...

No non è meta-codice. Comunque io faccio una via di mezzo
insert into tbl (val1) (val2) (val3); insert into tbl (val4) (val5) (val6);

Con circa 10.000 inserimenti per query.

Nel tuo caso specifico potresti:
- calcolare la stima di tempo d'esecuzione e poi fare il loader (come in genere i sistemi fanno)
- inserire 10/20/30 records a botta come faccio io, così sarebbe più veloce e avresti anche il loader

Ma sono idee buttate li...

Mmmh... mica male come idea: "spezzare" il ciclo in scaglioni più piccoli non è poi così complicato.

Ci proverò, grazie del consiglio

...niente, non ho risolto

Credo però che il problema non risieda in SQL ma nel mio SSD. Vedo che il led del disco blinka intensamente quando carico le insert: credo sia il solito problema di btrfs-cleaner che freeza pure la vm ogni tanto. Vabbeh...