Può SQLite maniglia di 90 milioni di dischi?

O devo utilizzare un altro martello per risolvere questo problema.

Ho una molto semplice caso d’uso per l’archiviazione dei dati, in modo efficace una matrice sparsa, che ho cercato di memorizzare in un database SQLite. Ho creato una tabella:

create TABLE data ( id1 INTEGER KEY, timet INTEGER KEY, value REAL )

nel quale inserire un sacco di dati, (800 elementi ogni 10 minuti, 45 volte al giorno), la maggior parte dei giorni dell’anno. La tupla (id1,timet) sarà sempre unico.

Il timet valore di secondi dall’epoca, e sarà sempre in aumento. Il id1 è, ai fini pratici, un numero intero casuale. Probabilmente c’è solo 20000 id univoci però.

Vorrei quindi come per accedere a tutti i valori in cui id1==someid o di accedere a tutti gli elementi di cui timet==a volte. Sul mio test utilizzando la più recente SQLite tramite l’interfaccia C su Linux, ricerca per uno di questi (o qualsiasi variante di questa ricerca) impiega circa 30 secondi, che non è abbastanza veloce per il mio caso d’uso.

Ho cercato la definizione di un indice per il database, ma questo ha rallentato l’inserimento completamente inutilizzabile velocità (mi ha fatto questo in modo non corretto, però…)

La tabella di cui sopra porta di accesso molto lento per i dati. La mia domanda è:

  • È SQLite completamente lo strumento sbagliato per questo?
  • Posso definire gli indici per velocizzare le cose in modo significativo?
  • Dovrei usare qualcosa come HDF5 invece di SQL per questo?

Vi prego di scusare la mia conoscenza di base di SQL!

Grazie

Io sono un esempio di codice che mostra come la velocità di inserimento rallenta a passo d’uomo quando si utilizza indici. Con l’indice di creare dichiarazioni di luogo, il codice di 19 minuti per completare. Senza che, corre in 18 secondi.


#include <iostream>
#include <sqlite3.h>

void checkdbres( int res, int expected, const std::string msg ) 
{
  if (res != expected) { std::cerr << msg << std::endl; exit(1); } 
}

int main(int argc, char **argv)
{
  const size_t nRecords = 800*45*30;

  sqlite3      *dbhandle = NULL;
  sqlite3_stmt *pStmt = NULL;
  char statement[512];

  checkdbres( sqlite3_open("/tmp/junk.db", &dbhandle ), SQLITE_OK, "Failed to open db");

  checkdbres( sqlite3_prepare_v2( dbhandle, "create table if not exists data ( issueid INTEGER KEY, time INTEGER KEY, value REAL);", -1, & pStmt, NULL ), SQLITE_OK, "Failed to build create statement");
  checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute insert statement" );
  checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize insert");
  checkdbres( sqlite3_prepare_v2( dbhandle, "create index issueidindex on data (issueid );", -1, & pStmt, NULL ), SQLITE_OK, "Failed to build create statement");
  checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute insert statement" );
  checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize insert");
  checkdbres( sqlite3_prepare_v2( dbhandle, "create index timeindex on data (time);", -1, & pStmt, NULL ), SQLITE_OK, "Failed to build create statement");
  checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute insert statement" );
  checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize insert");

  for ( size_t idx=0; idx < nRecords; ++idx)
  {
    if (idx%800==0)
    {
      checkdbres( sqlite3_prepare_v2( dbhandle, "BEGIN TRANSACTION", -1, & pStmt, NULL ), SQLITE_OK, "Failed to begin transaction");
      checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute begin transaction" );
      checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize begin transaction");
      std::cout << "idx " << idx << " of " << nRecords << std::endl;
    }

    const size_t time = idx/800;
    const size_t issueid = idx % 800;
    const float value = static_cast<float>(rand()) / RAND_MAX;
    sprintf( statement, "insert into data values (%d,%d,%f);", issueid, (int)time, value );
    checkdbres( sqlite3_prepare_v2( dbhandle, statement, -1, &pStmt, NULL ), SQLITE_OK, "Failed to build statement");
    checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute insert statement" );
    checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize insert");

    if (idx%800==799)
    {
      checkdbres( sqlite3_prepare_v2( dbhandle, "END TRANSACTION", -1, & pStmt, NULL ), SQLITE_OK, "Failed to end transaction");
      checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute end transaction" );
      checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize end transaction");
    }
  }

  checkdbres( sqlite3_close( dbhandle ), SQLITE_OK, "Failed to close db" ); 
}

  • Come dati storici che avete bisogno di accedere in qualsiasi momento? Si potrebbe archiviare i vecchi dati in un’altra tabella per la persistenza e risparmiare sul tempo di esecuzione di query “rilevanti” di dati.
  • I <ideale> bisogno di accedere a tutti i dati storici. In caso di necessità, posso dividere in un set di dati per anno, ma ho sperato SQLite mi consente di risparmiare da dover gestire tali dati.
  • Potrebbe per favore postare l’intero codice in cui si utilizza sqlite3* funzioni (omettendo le altre parti)? Se il processo di “ricerca per indicizzazione di una battuta d’arresto”, qualcosa che non è sicuramente di destra.
  • Avete bisogno la stessa granularità dei dati storici? Se non, RRDB può essere interessante.
  • Perché avete bisogno di ricerca id1==someid? È in questo modo che è possibile SELEZIONARE “random” dati in un secondo momento? Dal tuo scenario, sembra che non richiedono someid per essere unico, in modo da record #3 a 2010-01-01, e record #79832759385 a 2010-06-06, potrebbero avere lo stesso id1 campo. Allo stesso modo, si dispone di collisioni HHMMSS per più record inserito al secondo. Così, nel caso 1, si seleziona id==someId avere zero, uno, o molti sparse record; e nel caso 2, si ottiene zero, uno, o molti adiacente record. Se queste sono le vostre esigenze, si può beneficiare di diverse runtime metodi di query.
  • Penso che tu capisca le mie esigenze in modo corretto. In pratica, sono la memorizzazione di una matrice sparsa di cui l’asse di riga è indicizzato da id1 e la colonna indicizzata da tempo. Per la mia query, selezionare un’intera riga o colonna. Suggerimenti per i metodi di query adatto per questo?



8 Replies
  1. 29

    Sei l’inserimento di tutte le 800 elementi in una sola volta? Se si sta facendo la inserisce all’interno di una transazione accelerare il processo in modo drammatico.

    Vedere http://www.sqlite.org/faq.html#q19

    SQLite in grado di gestire database di dimensioni molto grandi. Vedere http://www.sqlite.org/limits.html

    • Sto già facendo ogni set di 45*800 inserisce come una singola transazione per velocizzare gli inserti. Questo fa una grande differenza per la costruzione di un DB, mi dà su un inserto di 45*800 elementi in circa 300ms. Il mio problema è ancora con l’interrogazione di se.
    • Stai usando il bisogno di indice? L’indice di portare il tempo di lettura in basso a O(log n) O(n). Modo migliore per recuperare i dati dipendono dalla quantità di dati da recuperare in una sola volta. Sei il recupero di un insieme, o solo il singolo record singolarmente?
    • Ho provato l’indice facendo qualcosa come: INDICE di CREARE INDICE1 su dati ( id1 ); e INDICE di CREARE INDEX2 su dati ( timet ); Tuttavia, questo significa che, quando ho costruito il db in su, ogni successivo set di istruzioni insert preso più a lungo e più a lungo, e il processo, infine, sottoposto a ricerca per indicizzazione per un arresto completo. Sono queste istruzioni create index corretto/sensible?
    • Se è necessario eseguire una query su entrambi id e ora. Dovrò dare questo qualche pensiero.
    • Sarebbe pratico mettere tutti e 800 i valori di id in un singolo record, indicizzati da tempo? Come si sparse la data, vale a dire sei sempre la scrittura di tutti i 800 valori?
    • Le figure sono tutti più o meno rappresentativi di ciò che mi piacerebbe fare. Scrivo <circa> 800 elementi ogni 10 minuti, non è sempre lo stesso +- 800 elementi di entrambi. Forse il raggruppamento di ciascuno di questi in proprio record/table è la cosa giusta da fare, ma questo è dove la mia sql esperienza mi fallisce! 😉

  2. 9

    Ho guardato il tuo codice, e penso che si potrebbe essere esagerando con il prepare e finalize dichiarazioni. Io sono in alcun modo un SQLite expert, ma ci deve essere un overhead significativo nella preparazione di una dichiarazione in ogni momento attraverso il ciclo.

    Citando la SQLite sito web:

    Dopo una dichiarazione preparata è stata
    valutato da una o più chiamate a
    sqlite3_step(), può essere riportata in
    per essere di nuovo valutato da una chiamata
    per sqlite3_reset(). Utilizzando
    sqlite3_reset() su una esistente
    dichiarazione preparata piuttosto la creazione di un
    nuova dichiarazione preparata evita
    inutili le chiamate al
    sqlite3_prepare(). In molti SQL
    dichiarazioni, il tempo necessario per l’esecuzione
    sqlite3_prepare() è uguale o superiore a
    il tempo sqlite3_step().
    Evitando così chiamate per
    sqlite3_prepare() può risultare in un
    il significativo miglioramento delle prestazioni.

    http://www.sqlite.org/cintro.html

    Nel tuo caso, piuttosto che la preparazione di una nuova istruzione di ogni tempo, si potrebbe provare associazione nuovi valori esistenti e istruzione.

    Detto questo, penso che gli indici potrebbe essere il colpevole, dal momento che il tempo continua ad aumentare, come si aggiungono ulteriori dati. Io sono curioso di sapere abbastanza su questo, dove ho intenzione di fare qualche test durante il fine settimana.

  3. 7

    Rispondere alla mia domanda solo come un posto dove mettere alcuni dettagli:

    Si scopre (come correttamente suggerito in precedenza) che la creazione dell’indice è il passo lento, e ogni volta che faccio un’altra transazione di inserti, viene aggiornato l’indice che richiede un po ‘ di tempo. La mia soluzione è:
    (A) creare la tabella con i dati
    (B) inserire tutti i miei dati storici (diversi anni)
    (C) di creare gli indici

    Ora tutte le ricerche ecc sono veramente veloce e sqlite fa un grande lavoro. Successivi aggiornamenti giornalieri ora prendere un paio di secondi per inserire solo 800 record, ma questo non è un problema dal momento che solo con corse ogni 10 minuti o giù di lì.

    Grazie a Robert Harvey e maxwellb per l’aiuto/suggerimenti/le risposte di cui sopra.

  4. 5

    Poiché sappiamo che la cattura di dati è veloce, quando non c’è nessun indice della tabella, che potrebbe in realtà il lavoro è questo:

    1. Catturare l’800 valori in una tabella temporanea con nessun indice.

    2. Copiare i record della tabella master (contenente gli indici) utilizzando il modulo di INSERIRE prende un’istruzione SELECT.

    3. Eliminare i record dalla tabella temporanea.

    Questa tecnica è basata sulla teoria che l’INSERTO IN che richiede un’istruzione SELECT è più veloce di esecuzione di singoli Inserti.

    Fase 2 può essere eseguito in background, utilizzando il Asincrono Modulo, se ancora risulta essere un po ‘ lento. Questo sfrutta il bit di tempo di inattività tra cattura.

  5. 3

    Considerare l’utilizzo di una tabella per i nuovi inserimenti di un determinato giorno, senza un indice. Quindi, alla fine di ogni giorno, eseguire uno script che:

    1. Inserire i nuovi valori da new_table in master_table
    2. Chiaro il new_table per il prossimo giorno del trattamento

    Se è possibile fare ricerche su dati storici in O(log n), e le ricerche di oggi di dati in tempo O(n), questo dovrebbe fornire un buon compromesso.

    • Che è una buona idea, a patto c’è qualche widget disponibili.
    • ci dovrebbe essere un certo tempo di inattività. La domanda originale afferma che lui è l’inserimento di righe ogni 10 minuti, 45 volte al giorno. Significa che ci dovrebbe essere più di 16 ore di inattività per lo script da eseguire.
    • Ecco perché ho suggerito questo. Proprio a causa dell’10 minuti x 45.
    • Anche io uso il termine “script” liberamente. Questo può, naturalmente, essere un programma compilato.
  6. 2

    Non so dalle tue caratteristiche, ma se il campo ID è sempre in aumento, e il campo ora include AAAAMMGG per unicità ed è, inoltre, sempre in aumento, e si sta facendo ID ricerche o ricerche in tempo, quindi il più semplice non-soluzione di database sarebbe semplicemente aggiungere tutti i record di un campo fisso di testo o un file binario (dato che sono generati in “ordinati” e utilizzare il codice per fare una ricerca binaria per i record desiderati (ad esempio, trovare il primo record con l’ID o il tempo di interesse, poi in sequenza il passaggio attraverso il campo desiderato).

    • Il valore del tempo è un time_t, sempre in aumento, come posso aggiungere valori, con ogni pezzo di 800 valori al tempo stesso. Il id1 è un (quasi) casuale int. Stavo pensando qualcosa di simile utilizzando hdf5 per il binario di archiviazione di file, ma spera sqlite potrebbe aiutare. In particolare, che sqlite è transazionale scrive il file, dal momento che mi hanno più lettori di accedere al file, con occasionale scrive. Come ulteriore complessità, il file è mantenuto su di storage NFS (ma non ho il controllo sul kernel linux di accedere la conservazione e la corretta nfs chiusura è supportato). sqlite.org/faq.html#q5
  7. 1

    Quando la costruzione di grandi database SQLite, inserire sempre la maggior quantità di dati, come si può prima di creare gli indici. Che verrà eseguito molte volte più veloce di se si crea gli indici prima di inserire i dati.

  8. 0

    Il numero massimo teorico di righe in una tabella 2^64 (18446744073709551616 o circa 1,8 e+19). Questo limite non è raggiungibile in quanto la dimensione massima del database di 140 terabyte sarà raggiunto prima. 140 terabyte di database può contenere non più di circa 1e+13 righe, e solo se non esistono indici e se ogni riga contiene pochi dati.

Lascia un commento