Quel SGBD pour de bonnes performances en Insert ?

[arthix]

Salut,

depuis un certain temps je suis à la recherche d'une solution tip-top pour loger des infos dans des bases de données.
Le contexte ??? Loger dans une base des informations (IDENTIFIANT string 16 digits/DateHeure/Data string 20 digits), en gros pas grand chose. IDENTIFIANT doit être unique (donc clé primaire de ma base), et zou c'est parti comme en '14.
Le soucis ??? disons que ma base doit pouvoir supporter 10 millions d'enregistrements sans broncher lors de l'ajout d'un nouvel enregistrement (pas plus d'une seconde), et surtout supporter des appels concurrents (nous fixerons 50), et que le temps de réponse de la base ne doit pas dépasser la seconde dans ce cas assez critique (50 appels concurrent d'INSERT de data avec clé primaire sur une table de 10 millions d'enregistrements existant)
Le "HARDCORE worst case" ??? Ca deviens chaud les marrons, je ne dois pas dépasser la seconde toujours sur une requete d'INSERT, toujours avec une contrainte de clé PRIMAIRE sur un champ, MAIS avec 100 millions de records et 30 appels concurrents...moi ca me parait terrible

Alors on m'a demandé de regarde SQLITE, j'ai testé, et je pense ne pas être convaincu. Précisons que je ne suis pas du tout expert en base de données. Mon soucis ici ne me demande pas à mon sens d'être un expert, puisqu'il n'est pas question d'optimisation de nombreuses data avec plein de tables, liens etc... c'est juste une pauvre table de rien du tout, mais enorme.

Donc je parlais de SQLITE, que j'ai testée d'abord dans mon environnement de travail, avec une bibliothèque existante. J'ai vu que cet existant ne permettait pas les appels concurrents. Je me suis dit, tiens je vais me faire un petit truc en C, avec appel à sqlite3.dll et un remplissage de table brutal. Impossible de faire des appels en concurrence, je me fais jeter (error code 5 : The database file is locked) Il faut croire que je m'y prend mal, puisqu'à lire des infos du site http://www.sqlite.org/lockingv3.html ca devrait tourner.

Mais ma question avant de m'embourber plus est la suivante : avec un seul appel qui boucle sur 1000 enregistrements pas exemple :
- open base
- boucle requete INSERT
- close base
j'ai des performances qui me paraissent complètement nulles (10 INSERT à la secondes), le tout avec une base vide.

Donc j'appelle à l'aide les grands chefs de la base de données sur plusieurs points :
- est-ce que je v dans le mur avec SQLITE ?
- quel autre SGBD utiliser si j'y vais, à fond à fond ? (dans le mur) et avez vous des exemples ? (est-ce qu'une solution ACCESS ou ORACLE est mieux, prkoi?)
- si vous pensez que ca peut le faire, ben ou est-ce que je me plante ?

Bref, merci pour vos lumières, et @+ !!!!!

[Marc Lussac]

Il y à pas que SQLITE, il y à aussi Firebird SQL, MySQL, PostgreSQL,...

Voir le comparatif SGBD

[arthix]

salut,

j'avais deja trouvé cette page, mais le soucis c'est que ca reste vague pour le commun des mortels non initiés aux base de données.

Prenons le descriptif de MySQL par exemple :
"Manque de robustesse avec de fortes volumétries" : est-ce que 10 ou 100 millions de records c bcp dans une table?

Je pourrais choisir Oracle, mais on me dit "Sur de petits volumes de traitements (2 Go par exemple) et peu d'utilisateurs (une trentaine) vous pourriez trouver des benchmark ou MySql offre des performances quasi comparables à Oracle... ". Note tout de même : l'environnement final du système contient de l'Oracle, mais pour ce qui est de l'environnement de test, nada. Si on considère 100 millions de records, avec disons 50 octets de data ca me donne moins de 5Go la base (cas ultra critique je me répète)

Merci

[Marc Lussac]

Justement, dans ton cas n'importe quel SGBD convient

Par exemple je serais toi je prendrais un SGBD gratuit, et j'investirais dans la RAM, si ta base fait 4 Go, si ton serveur fait aussi 4Go de RAM tous les reads se feront dans le cache RAM, donc instantanément

[arthix]

OKAY,

et donc dans mon cas de SQLITE, je ne sais pas si t'y a déjà touché, mais est-ce que ces performances (10 INSERT à la seconde) ca te parait correct ? parceque je boucle comme un sal**** dans mon truc, pour moi ca devrait aller bcp plus vite non ?

merci !!

[Marc Lussac]

Si j'ai bien compris tu as besoin d'un outil performant pour les "bulk insert" ?

Dans ce cas il faut envisager de prendre un SGBD qui à un module de "bulk Insert"' et ne pas utiliser les insert simple en SQL qui se font plus lentement.

Je ne connais pas SQL Lite donc je ne peu pas de répondre sur ce sujet personnellement.

[arthix]

après une breve lecture sur la fonction de "bulk insert", je crois comprendre qu'elle est utilisée pour importée de facon brutale un grand volume de données en provenance d'un fichier

or mes données sont "live" : je ne peux pas les stocker dans un fichier, je dois les inserer dans la base au fur et à mesure afin d'avoir une réponsé sur l'unicité. Car pour vous préciser la finalité de ce système, si je ne vous l'ai pas précisée, c'est bien de s'assurer de l'unicité de l'info seulement.

Merci encore

[Marc Lussac]

Si ce qu'il va vérifier est en RAM, et que de plus les reads se font en ram, le disque sera dispo pour les insert.

Donc la solution c'est toujours la RAM pour faire des insert massifs, mais aussi un disque rapide (en écriture). Par exemple du SCSI haut de gamme ca fait une bonne différence.

[Spoutnik]

Hello,

Il y a qq années, j'ai fait un projet qui demandait à peu près ce que tu décrit (90M lignes, 15Go/tables, accès fortement concurentiel et de nombreuse lectures écritures).
Au final, MySQL est passé à la poubelle au bénéfice de postgres. Trop lent lors des phases d'écriture en particulier. Et comme les opérations à faire étaient assez longues, mysql "chutait" (les perfs) dès que la concurence se mettait en place.
Mais c'était il y a qq années, donc, MySQL a probablement fait des progrès.
Pas de gros pb avec postgres.
Pour SQLite, je ne sais pas ce qu il vaut dans ce type de configuration. Mais étant donné que c'est un système "light" qui est basé sur un fichier, je ne suis pas à priori convaincu que ca tourne rapidement dans des conditions de concurence et de fort volume.


Good luck!

[arthix]

Salut Spoutnik,

après réflexion sur le système, j'ai une surcouche logicielle qui peut gérer la concurrence. J'entend par là que si je m'assure que les appels à la base ne dépassent pas la seconde (même si elle est pleine à péter), il est acceptable de ne pas avoir de concurrence (les appels sont effectués les uns après les autres).

Mais bref, ca n'est pas mon soucis actuellement : après des tests que l'on pourrait qualifier de pourris dans mon environnement (10 INSERT à la seconde ...!!!) j'ai décidé de revenir à la source, de prendre un projet Visual tout vide, d'ouvrir une base vide qui contient 3 champs dont 1 en clé primaire et de l'attaquer avec la dll sqlite3.dll :
- sqlite3_open
- boucle for sur sqlite3_exec avec un INSERT de base (int incrémenté par visual, heure courante, et 'toto')
- sqlite3_close
Je me base sur les heures que je peux lire dans ma base après excucution pour affirmer que je met toujours environ 1 seconde pour insérer 10 records.

Là je pleure, je me dis c koi ce bordel de base gratuite qui marche pas (ca dure pas longtemps, ma mauvaise foi ne dure jamais) et je cherche.

Je tombe sur http://sqlite.phxsoftware.com/forums/37/ShowPost.aspx , les résultats sont clairs, et je télécharge la source du test http://sqlite.phxsoftware.com/forums...ttachment.aspx
Là je vois qu'ils n'attaquent plus la dll de sqlite, mais directement la source

Et c'est là que je requiert l'attention et l'aide des expérimentés en la matière :

1/

Code :

1
2
3
err = sqlite3_exec(pcnn, "pragma synchronous=normal", NULL, NULL, NULL);
    err = sqlite3_exec(pcnn, "pragma cache_size=2000", NULL, NULL, NULL);
    err = sqlite3_exec(pcnn, "pragma page_size=1024", NULL, NULL, NULL);

ca sert à quoi, puisque moi je ne le fais pas ??

2/ est-ce que le fait d'appeler la dll peut me péter les performances à ce point?

pour info ci dessous le code que g utilisé et qui me donne ces résultats de

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
 
IF(mh_SqlLiteDll != NULL) FreeLibrary(mh_SqlLiteDll);
 mh_SqlLiteDll = LoadLibrary("sqlite3.dll");
 IF(mh_SqlLiteDll == NULL) RETURN DB_DLL_LOAD_ERROR; 
 mp_sqlite3_open = (t_sqlite3_open) GetProcAddress(mh_SqlLiteDll, "sqlite3_open");
 mp_sqlite3_exec = (t_sqlite3_exec) GetProcAddress(mh_SqlLiteDll, "sqlite3_exec");
 mp_sqlite3_close = (t_sqlite3_close) GetProcAddress(mh_SqlLiteDll, "sqlite3_close");
 printf("Offset de départ remplissage de base :");
 scanf( "%I64d", &sf_sn);
 printf("Nombre d'enregistrements à insérer :");
 scanf( "%I64d", &nr_records);
 printf("WORKING...");
 FOR (sn=sf_sn;sn<sf_sn+nr_records;sn++)
 {
  result_open = mp_sqlite3_open(ms_DBFilePath, &mp_sqlite3_Ctx);
  IF(result_open == SQLITE_OK)
  {
   time(&ltime);
   strftime( datebuf, 128, "%Y%m%d %H%M%S", localtime( &ltime ) );
   _itoa(sn, ic_sn, 10);
   _snprintf(query, MAX_QUERY, "INSERT INTO ic VALUES('%s', 'PPERSONALIZED', '%s')",ic_sn, datebuf);
 
   result_exec = mp_sqlite3_exec(mp_sqlite3_Ctx, query, NULL, NULL, &err_msg);
   IF(result_exec != SQLITE_OK)
   {
    printf("Erreur lors de l'insertion de l'enregistrement %I64d. (erreur %d)\n", sn, result_exec);
    printf("WORKING...");
    nb_errors++;
   } 
  }
  else
  {
   printf("Erreur lors de la tentative d'ouverture de la base (erreur %d)", result_open);
   printf("WORKING...");
   nb_errors++;
  }
  mp_sqlite3_close(mp_sqlite3_Ctx);
 }
 printf("Fin d'operation de remplissage. %I64d erreurs relevées.", nb_errors);
 scanf( "%I64d", &nr_records);

Merci d'avance pour vos lumières
A+

[Spoutnik]

juste par curiosité, ca donne quoi ton bench quand tu garde ta connexion ouverte?
cad au lieu de

Code :

1
2
3
4
5
6
FOR (sn=sf_sn;sn<sf_sn+nr_records;sn++)
 {
  result_open = mp_sqlite3_open(ms_DBFilePath, &mp_sqlite3_Ctx);
  result_exec = mp_sqlite3_exec(mp_sqlite3_Ctx, query, NULL, NULL, &err_msg);
  mp_sqlite3_close(mp_sqlite3_Ctx);
 }

tu mets :

Code :

1
2
3
4
5
6
 
result_open = mp_sqlite3_open(ms_DBFilePath, &mp_sqlite3_Ctx);
FOR (sn=sf_sn;sn<sf_sn+nr_records;sn++) {
  result_exec = mp_sqlite3_exec(mp_sqlite3_Ctx, query, NULL, NULL, &err_msg);
 }
 mp_sqlite3_close(mp_sqlite3_Ctx);

10 insert /s, ca me parait bcp, peut etre est ce le temps d'ouverture de la connexion?

[arthix]

Salut !!!

j'ai effectué le test comme tu le proposait aussi, même perf (c ce que j'avais fait dans un premier temps, mais ca donne les même perfs)

Quelle est l'intérêt de faire :

Code :

1
2
3
4
5
6
7
8
 
psql = "insert into foo values(?)";
err = sqlite3_prepare(pcnn, psql, (int)strlen(psql), &pstmt, &psql);
err = sqlite3_exec(pcnn, "begin", NULL, NULL, NULL);
.....
err = sqlite3_step(pstmt);
err = sqlite3_reset(pstmt);
sqlite3_exec(pcnn, "commit", NULL, NULL, NULL);

si je comprend, il s'agit en gros d'envoyer un paquet de requetes à la base, et les executer au final, toutes en même temps?

Dans mon cas, ca reste impossible, je n'ai qu'une requête à effectuer par appel à mon système. De plus, si je ne m'abuse, en cas de coupure électrique par exemple, les requetes buffurisées sont perdues exact?

Pour en revenir aux perfs, je vois qu'en jouant sur les paramètres

Code :

1
2
3
4
 
err = sqlite3_exec(pcnn, "pragma synchronous=normal", NULL, NULL, NULL);
err = sqlite3_exec(pcnn, "pragma cache_size=2000", NULL, NULL, NULL);
err = sqlite3_exec(pcnn, "pragma page_size=1024", NULL, NULL, NULL);

je peux sensiblement améliorer mes perfs, mes c'est pas top
Je vois également que sur http://www.sqlite.org/pragma.html y'a tout un tas de paramètres sur lesquels je peux jouer.

Est-ce que l'un d'entre eux serait responsable de ces performances pourries?
Est-ce que vous pourriez me donner une idées des paramètres à choisir, en rappelant que ma base finale n'aura que 2 champs texte, le premier de 8 caractères de long en clé primaire, le second texte egalement de 14 caractères, et que le but c'est de stocker à toute vitesse les infos :-)

merci encore & @+

[arthix]

Ho miracle, une fois l'option suivante executée sur la base :

Code :

1
2
 
sqlite3_exec(pcnn, "pragma synchronous=off", NULL, NULL, NULL);

mes perfs deviennent honorables avec 1000 records par seconde environ ..... OUF !!!!!!

mais ca ne m'explique pas tout : pourquoi une désactivation de cette option me multiplie les performances par 100 ? c'est obscur

maintenant j'ai absolument besoin d'un bon gros expert en base pour me conseiller ->>> je prévois de faire tourner mon soft de la façon suivante :

1/ ouverture de la base
2/ insertion des enregistrements
3/ fermeture de la base

mes questions concernent la performance et la sécurité :

Q1/ faut-il que la base soit stockée sur un disque local, ou y a til une incidence à la stocker sur le réseau ?

Q2/ concernant les paramètres suivants, j'ai vraiment besoin de conseils sur leur choix/dimensionnement, le coup du pragma synchronous=off, à mon sens les paramètres suivant ont une importance :

Code :

1
2
3
4
5
6
PRAGMA default_cache_size = Number-of-pages;
PRAGMA cache_size = Number-of-pages;
PRAGMA page_size = bytes;
PRAGMA synchronous = FULL/NORMAL/OFF;
PRAGMA temp_store = DEFAULT/FILE/MEMORY;

Q3/ concernant la façon dont je souhaite bosser (open base / insertion de nombreux enregistrements / close base), je me pose le risque éventuel du bug en cours de process, mon système plante comme un nul :-) confirmez-vous que vu que j'appelle une instance de cette DLL sqlite3.dll, il va quand même finaliser tout ce qu'il a en mémoire etc... et me fermer proprement mon fichier mabase.db3 ???

Q4/ concernant les appels concurrents, est-il envisageable d'avoir plusieurs de mes systèmes qui appellent la même base? (oui j'espère ...) mais la question véritable : y a-t-il des points dont il faut s'assurer quand on bosse sur un tel système ???

Merci pour vos réponses et d'avance bon WE !!!

[arthix]

Salut,

je reviens à nouveau "à la charge" concernant ces histoires de configuration de bases VS les performances et la sécurité, et notamment sur

Code :

pragma synchronous=off

Les différences de performances entre l'option mise à OFF et NORMAL sont telles qu'elles poussent à mettre à OFF, mais je me pose tout plein de questions.

Si qlqu'un à l'expérience dans le manipage de ces options, merci d'avance !!!

@+!!


Pour répondre à ta question Spoutnik, l'open / close ne consomme pas tant de temps que ça : 4 ms l'opération sur une base généreusement chargées (100million de records) contre 3ms sur la même base pour l'exécution de l'insert seul (P4 2.8GHz / 512MB RAM)

Envoyé par Spoutnik

juste par curiosité, ca donne quoi ton bench quand tu garde ta connexion ouverte?
cad au lieu de

Code :

tu mets :

Code :

10 insert /s, ca me parait bcp, peut etre est ce le temps d'ouverture de la connexion?