Forums PostgreSQL.fr

sco

Membre

[ECPG] Performances du SELECT/FETCH

Bonjour,

Je viens m'enquérir ici du retour d'expérience sur les performances du SELECT/FETCH sur des gros set de résultats en SQL embarqué en C.

J'ai des interrogations sérieuses sur l'écart flagrant de performances entre une requête exécutée avec psql, et cette même requete exécutée dans mon code en C.
Je constate un facteur 10 (psql étant, vous vous en douterez le plus rapide), ce qui me semble simplement énorme !

La requete SELECT est une jointure entre deux tables (25000 lignes et 50000 lignes respectivement chacune), renvoyant environ 2000 lignes. psql retourne les résultat en 80ms, mon application en 800ms.

Sur le coup, j'ai fait un tour sur le channel IRC pg et on m'a suggéré de faire un strace. Il s'est averé que vraisemblablement le FETCH ligne par ligne me faisait perdre énormément de temps. Au vu de la documentation 9.1devel, j'ai tenté de passer un tableau alloué statiquement pour par exemple 1000 lignes, et donc au final mon code C itère deux fois sur le FETCH, je fais deux FETCH au lieu de 2000. Le gain obtenu est de l'ordre de 50%, puisque je récupère l'ensemble des 2000 lignes maintenant en 400 ms environ. Fetcher davantage que 1000 lignes à chaque fois n'a plus d'impact sur les performances.
C'est mieux, mais encore loin de mon objectif qui est de m'approcher de psql.

Avez-vous déjà constaté un tel différentiel ? Comment vous en êtes-vous sorti ?

Merci d'avance pour vos retours !

Marc Cousin

Membre

Re : [ECPG] Performances du SELECT/FETCH

Un fetch 1000 par 1000 ne devrait pas être plus lent que ce que fait psql. Il doit y avoir autre chose.

- Avez vous vérifié après avoir modifié le code C pour faire ces fetchs de tableau qu'il n'y a effectivement plus autant de dialogues client/serveur ?
- Êtes vous sûr d'avoir le même plan d'exécution pour la requête que celui de psql (vous faites une requête préparée ?)

---

Marc.

sco

Membre

Re : [ECPG] Performances du SELECT/FETCH

- Avez vous vérifié après avoir modifié le code C pour faire ces fetchs de tableau qu'il n'y a effectivement plus autant de dialogues client/serveur ?

Oh que oui, c'est flagrant. C'est infiniment moins verbeux en fetchant 1000 par 1000.

- Êtes vous sûr d'avoir le même plan d'exécution pour la requête que celui de psql (vous faites une requête préparée ?)

Non, il ne s'agit pas d'une requête préparée, je la passe avec des variables hote directement comme ceci :

EXEC SQL
SELECT <les colonnes qui vont bien>
INTO                   <mes variables :pg_ qui vont bien>
FROM    ( SELECT <ce qu'il faut> FROM <la table>
                                WHERE  <les clauses qui vont bien>
                                ORDER BY        <comme il faut>
                                OFFSET         <le bon>
                        ) AS alias_de_tout_ca
INNER JOIN <l'autre table> ON 
                        <la bonne jointure>
ORDER BY        <comme il faut>
OFFSET <ce qu'il faut>
LIMIT <la bonne>

Je n'ai rien d'autre qui pourrait consommer dans ce bout de code (pas de log, pas d'écriture fichier, pas d'affichage, pas de calcul), il s'agit d'une récupération de base. Je suis à sec !

Marc Cousin

Membre

Re : [ECPG] Performances du SELECT/FETCH

Testez toujours en demandant à psql de faire aussi des fetch 1000 par 1000:
\set FETCH_COUNT 1000
Et tentez la requête. Il fera exactement comme vous: ouverture d'un curseur et récupération des résultats 1000 par 1000.

---

Marc.

sco

Membre

Re : [ECPG] Performances du SELECT/FETCH

Bonjour Marc,

Testez toujours en demandant à psql de faire aussi des fetch 1000 par 1000:
\set FETCH_COUNT 1000
Et tentez la requête. Il fera exactement comme vous: ouverture d'un curseur et récupération des résultats 1000 par 1000.

Avec psql, un fetch ligne par ligne (avec \set FETCH_COUNT 1) en moyenne coute 68ms pour fetcher l'ensemble des 1740 lignes, avec un fetch 1000 par 1000, la moyenne redescend a 60ms.

Avec mon code (tenez-vous bien), avec un fetch ligne par ligne (donc sans utilisation de tableaux alloués statiquement), je m'en sors avec 1400ms en moyenne. Et grosso modo 600ms avec des tableaux alloués statiquement et sans FETCH mais des SELECT avec un offset +1000 pour chaque SELECT.

Très étrange aussi : si je me contente d'un COUNT sur la requete plutot que de fetcher le résultat, j'en suis deja a 78ms en moyenne... Le meme count sur psql tourne autour de 50ms. Le cout du COUNT ne me choque pas (j'en ai absolument besoin, et d'une manière précise), mais c'est le fait qu'il soit presque 50% plus élevé que par psql alors que le volume de données transférées est j'imagine très faible.

La durée indiquée par psql (eg. "LOG:  duration: 50.639 ms") indique-t'il le temps complet de récupération de l'intégralité des lignes résultat, ou le temps jusqu'à la completion du premier FETCH partiel ( de FETCH_COUNT lignes donc).

psql utilise la lib libpq non ? Ca vaudrait le coup que je compare avec ou pas ?

Merci encore !

sco

Membre

Re : [ECPG] Performances du SELECT/FETCH

Bon, je n'ai pas pu m'empêcher de faire quelques petits essais avec libpq...
Le verdict est sans appel : j'ai des logs qui sortent sur la sortie standard, ils montrent une moyenne approchant plutôt les 50ms. J'ai du mal à rester assis !

A-t'on suffisamment de preuves pour commencer a envisager un probleme au niveau du SQL embarqué ?

Marc Cousin

Membre

Re : [ECPG] Performances du SELECT/FETCH

Une chose à laquelle je viens de penser :

Seriez vous en 8.4 ?

Si c'est le cas, pouvez vous tester en passant le paramètre cursor_tuple_fraction à 1 ?

---

Marc.

sco

Membre

Re : [ECPG] Performances du SELECT/FETCH

Oui effectivement, je suis en 8.4.

Apres passage de cursor_tuple_fraction à 1, le tout premier SELECT a pris environ 1400ms (peut etre parce que j'ai pris la base un peu à froid), tous les essais suivants seulement environ 40ms.
J'ai essayé ensuite d'unset cursor_tuple_fraction, les essais suivants ne sont pas spécialement plus longs que 40ms.
J'ai repassé cursor_tuple_fraction à 1, les temps de requetage oscillent tous autour de 40 ms.

D'autres idées ?

Marc Cousin

Membre

Re : [ECPG] Performances du SELECT/FETCH

Je ne comprends plus rien aux chiffres que vous donnez. Pouvez vous reprendre les chiffres en étant le plus clair possible, afin que je comprenne bien ? Parce que là, je ne comprends plus à quoi sont associés tous vos tests.

---

Marc.

sco

Membre

Re : [ECPG] Performances du SELECT/FETCH

Bonjour Marc,

Alors je reprend, j'avoue que c'était confus. Voici les manipulations faites sur la base de test, à froid, en arrivant le matin :

Step #1 :
\set cursor_tuple_fraction 1
SELECT <....> retourne en 1400 ms
SELECT <....> retourne en 44 ms
SELECT <....> retourne en 34 ms
SELECT <....> retourne en 40 ms

Step #2
\unset cursor_tuple_fraction
SELECT <....> retourne en 42 ms
SELECT <....> retourne en 37 ms
SELECT <....> retourne en 41 ms

Step #3 :
\set cursor_tuple_fraction 1
SELECT <....> retourne en 41 ms
SELECT <....> retourne en 44 ms
SELECT <....> retourne en 34 ms

Voilà, je pense que c'est deja plus clair en effet !

Marc Cousin

Membre

Re : [ECPG] Performances du SELECT/FETCH

Donc le cursor_tuple_fraction n'a pas d'effet. On voit bien que le premier temps est dû à l'absence de cache.

Par contre, le temps sur l'application ecpg est toujours aussi mauvais, je présume ?

---

Marc.

sco

Membre

Re : [ECPG] Performances du SELECT/FETCH

Bonjour Marc,

Il me manque une info pour pouvoir répondre à cette question : j'imagine que l'appel a \set dans psql a une portée qui se limite uniquement au process psql en question, et n'est pas un paramètre global de postgresql. La preuve, je relance psql, et la variable n'est pas settée.

Si je comprends bien votre question, je devrais passer l'équivalent d'une telle commande en SQL embarqué dans mon code. Comment faire ? Je sèche !

Merci encore !

Marc Cousin

Membre

Re : [ECPG] Performances du SELECT/FETCH

\set FETCH_COUNT demande à psql d'utiliser un curseur pour exécuter la requête, au lieu du mode d'exécution simple. C'est pour cela que ce n'est pas un paramètre 'serveur', mais client.

Si vous faites des fetch dans votre code ecpg, je présume que lui aussi génère un curseur. Vous pouvez facilement le voir en demandant à postgresql de tracer tous les ordres SQL (log_statement= 'all')

---

Marc.

sco

Membre

Re : [ECPG] Performances du SELECT/FETCH

Pour faire des fetchs dans mon code, j'ouvre systématiquement un curseur. Je 'fetch' ce curseur jusqu'a ce qu'il ne retourne plus de résultats.

Donc à ce point mes interrogations demeurent. D'autres idées ?

Marc Cousin

Membre

Re : [ECPG] Performances du SELECT/FETCH

Le code ECPG ne fait rien des données ?

Il n'y aurait pas moyen de poster ce code ? Je pense que ça irait plus vite.

---

Marc.

sco

Membre

Re : [ECPG] Performances du SELECT/FETCH

Non, j'avais un doute sur le traitement des données après réception, donc j'ai chronométrer, puis fini par supprimer les traitements autres que la pure récupération. Il n'y a guere plus que du code touchant de près à Postgres.
Le code résultant ressemble à celui indiqué ci-dessous.

/*
*
*/
int DBGet_AllItem ( long id_customer , long item_type , long item_id , long endDate , long startDate ) {

EXEC SQL BEGIN DECLARE SECTION;
long            pg_id_item_grp;
long            pg_id_item;
long            pg_id_customer;
long            pg_item_type;
long            pg_startDate;
long            pg_endDate;
EXEC SQL END DECLARE SECTION;

int             sqlcode                 = 0;

        /* A few inits */
        pg_id_customer = id_customer;
        pg_endDate = endDate;
        pg_startDate = startDate;
        pg_item_type = item_type;
        pg_id_item = item_id;
        
        EXEC SQL DECLARE cur_allitem CURSOR FOR
                                SELECT                  item_groups.id_item_grp,
                                                        item_groups.id_customer_item_grp,
                                                        id_item,
                                                        type_item
                                FROM    ( SELECT id_item_grp, id_customer_item_grp
                                                        FROM item_grp
                                                        WHERE   date_item_grp >= :pg_startDate
                                                        AND date_item_grp <= :pg_endDate
                                                        AND ( id_customer_item_grp = :pg_id_customer )
                                                        AND ( id_item_grp = :pg_id_item_grp OR :pg_id_item_grp = -1 )
                                                        ORDER BY        date_item_grp DESC , id_item_grp DESC
                                        ) AS item_groups
                                INNER JOIN item ON
                                                        ( id_item_grp = id_item_grp_item
                                                        AND ( type_item = :pg_item_type OR :pg_item_type = -1 ) )
                                ORDER BY        id_item_grp DESC , id_item DESC , type_item ASC;

        EXEC SQL        OPEN cur_allitem;
        if ( sqlca.sqlcode != ECPG_NO_ERROR ) {
                return ( sqlca.sqlcode );
        }

        while ( sqlca.sqlcode == ECPG_NO_ERROR ) {
                EXEC SQL        FETCH   cur_allitem
                                        INTO        :pg_id_item_grp, :pg_id_customer , :pg_id_item , :pg_type_item;
                if ( sqlca.sqlcode == ECPG_NO_ERROR ) {
                    // Aucun traitement ici en vue de ces tests de perf
                }
                else if ( sqlca.sqlcode == ECPG_NOT_FOUND ) {
                        break;
                }
                else {
                        sqlcode = sqlca.sqlcode;
                        EXEC SQL CLOSE cur_allitem;
                        if ( sqlca.sqlcode != ECPG_NO_ERROR ) {
                              return ( sqlca.sqlcode );
                        }
                        return ( sqlcode );
                }
        }

        EXEC SQL CLOSE cur_allitem;
        if ( sqlca.sqlcode != ECPG_NO_ERROR ) {
                return ( sqlca.sqlcode );
        }

        return ( ECPG_NO_ERROR );
}

J'ai quelque peu simplifié le code pour le rendre plus lisible.

Marc Cousin

Membre

Re : [ECPG] Performances du SELECT/FETCH

Un truc m'échappe: où est le fetch 100 par 100 ou 1000 par 1000 dans ce code ? Je ne vois qu'un fetch ligne à ligne.

---

Marc.

sco

Membre

Re : [ECPG] Performances du SELECT/FETCH

Le code présenté précédemment est un fetch avec curseur explicite, ce qui suit est une tentative de FETCH 1000 par 1000 avec un curseur implicite, avec passage de tableaux :

/*
*
*/
int DBGet_AllItem ( long id_customer , long item_type , long item_id , long endDate , long startDate ) {

#define MAX_PGROWS_SIZE                 1000

EXEC SQL BEGIN DECLARE SECTION;
long            pg_id_item_grp [ MAX_PGROWS_SIZE ];
long            pg_id_item [ MAX_PGROWS_SIZE ];
long            pg_id_customer [ MAX_PGROWS_SIZE ];
long            pg_item_type [ MAX_PGROWS_SIZE ];
long            pg_startDate;
long            pg_endDate;
EXEC SQL END DECLARE SECTION;

int             sqlcode                 = 0;
int             idx                        = 0;

        /* A few inits */
        pg_id_customer = id_customer;
        pg_endDate = endDate;
        pg_startDate = startDate;
        pg_item_type = item_type;
        pg_id_item = item_id;

        pg_max_rows = MAX_PGROWS_SIZE;
        pg_offset_rows = 0;

        do {
                EXEC SQL        SELECT                  item_groups.id_item_grp,
                                                        item_groups.id_customer_item_grp,
                                                        id_item,
                                                        type_item
                                INTO    :pg_id_item_grp, :pg_id_customer , :pg_id_item , :pg_type_item;
                                FROM    ( SELECT id_item_grp, id_customer_item_grp
                                                        FROM item_grp
                                                        WHERE   date_item_grp >= :pg_startDate
                                                        AND date_item_grp <= :pg_endDate
                                                        AND ( id_customer_item_grp = :pg_id_customer )
                                                        AND ( id_item_grp = :pg_id_item_grp OR :pg_id_item_grp = -1 )
                                                        ORDER BY        date_item_grp DESC , id_item_grp DESC
                                        ) AS item_groups
                                INNER JOIN item ON
                                                        ( id_item_grp = id_item_grp_item
                                                        AND ( type_item = :pg_item_type OR :pg_item_type = -1 ) )
                                ORDER BY        id_item_grp DESC , id_item DESC , type_item ASC
                                OFFSET :pg_offset_rows
                                LIMIT :pg_max_rows;

                if ( sqlca.sqlcode == ECPG_NO_ERROR ) {
                        for ( idx=0 ; idx<sqlca.sqlerrd[2] ; idx++ ) {
                                /* Ici on ne fait rien du résultat récu, afin de tester les perfs */
                        }
                        pg_offset_rows += idx;
                }
        } while ( sqlca.sqlcode == ECPG_NO_ERROR );

        return ( ECPG_NO_ERROR );
}

Dernière modification par sco (07/12/2010 16:16:12)

Marc Cousin

Membre

Re : [ECPG] Performances du SELECT/FETCH

J'ai tenté de le reproduire :

Voici le code (sale) que j'ai écrit pour tester :

marc@marco-dalibo:~/temp/test_ecpg$ cat test_ecpg.pgc 
/*
*
*/
int DBGet_AllItem (  ) {

#define MAX_PGROWS_SIZE                 1000
EXEC SQL CONNECT TO tcp:postgresql://localhost:5845/marc AS con1 USER marc;

EXEC SQL BEGIN DECLARE SECTION;
long            pg_id_item_grp [ MAX_PGROWS_SIZE ];
EXEC SQL END DECLARE SECTION;

int             sqlcode                 = 0;
int             idx                        = 0;


        do {
                EXEC SQL SELECT a INTO :pg_id_item_grp FROM test;

                if ( sqlca.sqlcode == ECPG_NO_ERROR ) {
                        for ( idx=0 ; idx<sqlca.sqlerrd[2] ; idx++ ) {
                                /* Ici on ne fait rien du résultat récu, afin de tester les perfs */
                        }
                }
        } while ( sqlca.sqlcode == ECPG_NO_ERROR );

        return ( ECPG_NO_ERROR );
}

int main(int arcg,char** argv)
{
        DBGet_AllItem ();
}

J'en ai profité pour regarder ce qui se passe au niveau procole: ecpg, dans ce cas, envoie une 'simple query', pas un curseur. Il récupère tout le résultat, et le fournit dans un tableau.

Si on veut vraiment faire du '1000 à 1000' comme psql, il faut faire un code de ce genre :

/*
*
*/
int DBGet_AllItem (  ) {

EXEC SQL CONNECT TO tcp:postgresql://localhost:5845/marc AS con1 USER marc;

EXEC SQL BEGIN DECLARE SECTION;
int            pg_id_item_grp [ 1000 ];
EXEC SQL END DECLARE SECTION;

int             sqlcode                 = 0;
int             idx                        = 0;

        EXEC SQL DECLARE cursor_test CURSOR FOR
                   SELECT a FROM test;
        EXEC SQL OPEN cursor_test;

        do {
                EXEC SQL FETCH FORWARD 1000 cursor_test INTO :pg_id_item_grp;

                if ( sqlca.sqlcode == ECPG_NO_ERROR ) {
                        printf("fournee suivante!\n");
                        for ( idx=0 ; idx<sqlca.sqlerrd[2] ; idx++ ) {
                                printf("%d\n",pg_id_item_grp[idx]);
                        }
                }
        } while ( sqlca.sqlcode == ECPG_NO_ERROR );

        return ( ECPG_NO_ERROR );
}

int main(int arcg,char** argv)
{
        DBGet_AllItem ();
}

Ce qui m'ennuie dans tout ça, c'est que j'ai des temps d'exécution similaires entre psql et le programme ecpg.

Pouvez vous m'expliquer ce que sont l'offset et le limit ? Je pense que ça vient d'eux: vous n'auriez pas une boucle while non visible ici, que vous utiliseriez pour exécuter cette requête plusieurs fois avec des offset incrémentés ?

---

Marc.

sco

Membre

Re : [ECPG] Performances du SELECT/FETCH

Oh mais je commence à mettre les choses ensembles... J'ignorais l'existence de la syntaxe FETCH FORWARD, et donc j'utilisais un offset (celui sur lequel porte votre question!) pour récupérer "les 1000 suivantes", puis "encore les 1000 suivantes".
Si je comprends bien, le fait de passer une tableau de 1000 ne suffit pas à indiquer à ECPG qu'il faut fetcher par plus que 1 à chaque FETCH ?

L'utilisation du FORWARD dans le FETCH et l'élimination de l'offset devenu superflu divisent par 6 le temps d'exécution. C'est déjà plus acceptable. A l'occasion je refais un benchmark avec psql pour savoir plus précisemment où on en est.

Marc, encore mille mercis pour votre aide et votre patience !

SC

Marc Cousin

Membre

Re : [ECPG] Performances du SELECT/FETCH

Point à point :
LIMIT/OFFSET obligeait à exécuter la requête à chaque fois. Donc recalcul des enregistrements à chaque fois.

Le tableau de 1000 permet simplement de faire l'affectation de 1000 enregistrements en une passe. Je pense que ce n'est plus rapide que pour la recopie elle même des enregistrements en mémoire. Le fetch 1000 amène bien les 1000 enregistrements d'un coup au niveau du protocole (cela permet donc de se débarrasser de la latence réseau à peu près totalement), mais je ne pense pas que le tableau de 1000 change grand chose. Vous pouvez comparer, le résultat m'intéresse

Si vous récupérez tous les enregistrements, essayez de positionner le cursor_tuple_fraction à 1, cela aidera PostgreSQL à choisir le plan le plus performant.

---

Marc.