Peut SQLite poignée de 90 millions de disques?
Ou devrais-je utiliser un autre marteau pour résoudre ce problème.
J'ai une très simple d'utilisation pour le stockage des données, de manière efficace d'une matrice creuse, que j'ai tenté de stocker dans une base de données SQLite. J'ai créé un tableau:
create TABLE data ( id1 INTEGER KEY, timet INTEGER KEY, value REAL )
dans lequel j'ai insérer un grand nombre de données, (800 éléments, toutes les 10 minutes, 45 fois par jour), la plupart des jours de l'année. Le n-uplet de (id1,préventive) sera toujours unique.
L'préventive valeur est en secondes depuis l'epoch, et sera toujours en augmentation. Le id1 est, à toutes fins pratiques, un entier aléatoire. Il n'y a probablement que 20000 id unique bien.
Je ne puis accéder à toutes les valeurs où id1==someid ou accès à tous les éléments où préventive==de temps en temps. Sur mes tests à l'aide de la dernière SQLite via l'interface C sur Linux, recherche pour l'un de ces (ou toute variante de cette recherche) prend environ 30 secondes, ce qui n'est pas assez rapide pour mon cas d'utilisation.
J'ai essayé de définir un index de la base de données, mais cela a ralenti le curseur d'insertion complètement impraticable vitesses (j'ai peut-être fait de cette manière incorrecte si...)
Le tableau ci-dessus conduit à de très lent d'accès pour toutes les données. Ma question est:
- Est SQLite complètement le mauvais outil pour cela?
- Puis-je définir des indices pour accélérer les choses de manière significative?
- Dois-je utiliser quelque chose comme HDF5 au lieu de SQL pour cela?
Veuillez excuser mon très compréhension de base de SQL!
Grâce
- Je inclure un exemple de code qui montre comment l'insertion de la vitesse ralentit énormément lors de l'utilisation d'indices. Avec la 'création d'un" indice de déclarations en place, le code prend en 19 minutes. Sans cela, il s'exécute en 18 secondes.
#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" );
}
- Combien d'historique de données avez-vous besoin d'accéder à un moment donné? Vous pourriez archive des données plus anciennes dans une autre table de la persistance et de gagner du temps sur l'interrogation de "pertinent" de données.
- I <idéalement> besoin d'accéder à toutes les données historiques. Si besoin, je peux diviser ce dans un dataset par année, mais j'espère SQLite pourrait m'épargner d'avoir à gérer de tels détails.
- Pourriez-vous s'il vous plaît poster le code complet où vous utiliser sqlite3* fonctions (en omettant les autres pièces)? Si le processus de "rampa jusqu'à l'arrêt complet", quelque chose est certainement pas droit.
- Avez-vous besoin de la même granularité des données historiques? Si non, RRDB peut être intéressant.
- Pourquoi avez-vous besoin pour la recherche de id1==someid? Est-ce ainsi que vous pouvez SÉLECTIONNER "random" de données à un moment plus tard? À partir de votre scénario, il semble que vous n'avez pas besoin someid être unique, record #3 à 2010-01-01, et record #79832759385 à 2010-06-06, pourraient avoir le même id1 champ. De même, vous avez des collisions sur HHMMSS pour plusieurs enregistrements insérés par seconde. Ainsi, dans le cas 1, vous sélectionnez id==someId et obtenir zéro, un, ou plusieurs éparses dossiers; et dans le cas 2, vous obtenez zéro, un, ou plusieurs adjacentes enregistrements. Si ce sont vos besoins, vous pouvez bénéficier de différents runtime méthodes de requête.
- Je pense que vous comprenez mes besoins correctement. Essentiellement, je suis le stockage d'une matrice creuse où la ligne d'axe est indexé par id1 et la colonne indexée par le temps. Pour mes requêtes, je veux sélectionner toute une ligne ou une colonne. Toutes les suggestions de requête méthodes adaptées à cette situation?
Vous devez vous connecter pour publier un commentaire.
Êtes-vous l'insertion de tous les 800 éléments à la fois? Si vous êtes à la, faire de l'insère au sein d'une transaction permettra d'accélérer le processus de façon spectaculaire.
Voir http://www.sqlite.org/faq.html#q19
SQLite peut gérer de très grandes bases de données. Voir http://www.sqlite.org/limits.html
J'ai regardé ton code, et je pense que vous pourriez être en faire trop avec le
prepare
etfinalize
consolidés. Je ne suis en aucun cas SQLite expert, mais il doit y avoir une surcharge importante dans la préparation d'une déclaration à chaque passage dans la boucle.Citant le SQLite site web:
http://www.sqlite.org/cintro.html
Dans votre cas, plutôt que de préparer une nouvelle déclaration, à chaque fois, vous pouvez essayer de la liaison de nouvelles valeurs à votre déclaration.
Cela dit, je pense que l'index pourrait être le véritable coupable, puisque le temps ne cesse d'augmenter à mesure que vous ajoutez plus de données. Je suis assez curieux à propos de cette où j'ai l'intention de faire quelques tests sur le week-end.
Pour répondre à ma propre question juste comme un endroit pour mettre quelques détails:
Il s'avère (comme l'a très justement suggéré ci-dessus) que la création de l'index est l'étape lente, et chaque fois que je fais une autre opération de plaquettes, l'index est mis à jour, ce qui prend du temps. Ma solution est de:
(A) créer la table de données
(B) insérer toutes mes données historiques (plusieurs années)
(C) créer l'index
Maintenant toutes les recherches etc sont vraiment rapide et sqlite fait un excellent travail. Ultérieure des mises à jour quotidiennes maintenant, prenez quelques secondes pour insérer seulement 800 enregistrements, mais c'est pas un problème, car il ne circule toutes les 10 minutes.
Merci à Robert Harvey et maxwellb pour l'aide/suggestions/réponses ci-dessus.
Puisque nous savons que la capture de vos données est très rapide quand il n'y a pas d'index sur la table, ce qui pourrait effectivement le travail est ceci:
Capturer l'800 les valeurs dans une table temporaire avec aucun indice.
Copier les enregistrements de la table principale (contenant des index) à l'aide de la forme de l'INSÉRER DANS qui prend une instruction SELECT.
Supprimer les enregistrements de la table temporaire.
Cette technique est basée sur la théorie que l'INSERTION DANS qui prend une instruction SELECT est plus rapide que l'exécution individuelle des INSERTs.
L'étape 2 peut être exécuté en arrière-plan à l'aide de la Asynchrone Module, si elle s'avère être encore un peu lent. Cette méthode tire parti des morceaux de temps d'arrêt entre les captures.
Envisager d'utiliser un tableau pour les nouveaux inserts de la journée, sans index. Puis, à la fin de chaque jour, exécuter un script qui:
Si vous pouvez faire des recherches sur l'historique des données en temps O(log n), et les recherches sur les données d'aujourd'hui en O(n), ce qui devrait fournir un bon compromis.
Je ne peux pas dire à partir de votre cahier des charges, mais si le champ ID est toujours en augmentation, et le champ temps comprend AAAAMMJJ pour l'unicité et est également toujours en augmentation, et que vous êtes en train de faire soit l'ID de recherches ou de recherches en temps, alors le plus simple non-solution de base de données serait de tout simplement ajouter tous les enregistrements d'un champ fixe fichier texte ou binaire (car ils sont générés dans des "triés" de commande) et utiliser le code pour faire une recherche binaire pour les documents souhaités (par exemple, trouver le premier enregistrement avec l'ID de temps ou d'intérêt, puis de manière séquentielle étape à travers la gamme désirée).
Lors de la construction de grandes bases de données SQLite, insérez toujours autant de données que vous pouvez avant la création de l'index. Qui va l'exécuter plusieurs fois plus rapide que si vous créez des indices avant d'insérer les données.
Le nombre maximal théorique de lignes dans une table est 2^64 (18446744073709551616 soit environ 1,8 e+19). Cette limite est inaccessible depuis la taille maximale de la base de 140 téraoctets sera atteint en premier. 140 téraoctets de la base de données peut être porteur de plus d'environ 1e+13 lignes, et seulement si il n'y a pas d'indices, et si chaque ligne contient très peu de données.