Lenteurs inexpliquées sur des insert.

Bonjour,

j'utilise un serveur postgresql 7.4 (2 serveurs en mode actif/secours) travaillant sur une partition
en raid réseau pour les datas et le WAL (mode fsync).

Descriptif des serveurs :
- bi-xéon 3,4Ghz
- 3Go DDRAM
- disque système : 18Go ultra320 SCSI 10000tr/mn
- disque data et WAL postgres : 146Go ultra320 SCSI 15000tr/mn (c'est sur ce disque qu'est montée la
partition drbd)
- interfaces réseau 1Gbps
- debian sarge
- kernel 2.6.8-3-686-smp
- postgresql 7.4 (7.4.7) - je n'ai pas la possibilité de passer en version 8.1 (du fait de
l'ancienneté des clients utilisés).
- drbd 0.7 (0.7.10)

Le raid réseau est assuré par le logiciel drbd (0.7) en mode synchrone avec un lien point à point 1
gigabits entre les serveurs. Les benchs disponibles sur le net et mes tests montrent une lenteur
apportée par la couche DRBD en écriture. Celle-ci n'est néanmoins pas significative et n'explique
pas le problème suivant.

J'ai environ 1000cnx/mn sur une base de 45 tables dont les plus grosses n'exédent pas 1,5 millions
de lignes.
Je n'ai pas activé les traces donc je ne sais pas quelles sont les tables les plus utilisées.
Je ne maitrise pas les clients, je ne peux donc pas minimiser le nombre d'ouverture et de fermeture
de session (si ce n'est par l'ajout d'un proxy).

Mon problème est le suivant :
certains insert peuvent prendre jusqu'à 90s et ceci :
- alors que la majorité des insert prennent un temps correct (moins d'1 seconde)
- aléatoirement dans le temps (aucune corrélation possible avec des traitements)
- aléatoirement sur les tables (pas une table en particuliers)
- sans lock utilisé sur les tables
- alors qu'un vacuum analyze est fait toutes les nuits
- alors qu'autovacuum fonctionne (j'ai bien validé que ce n'était pas autovacuum qui crée ces problèmes)
- alors qu'un reindex des tables système est fait toutes les semaines
- alors qu'un reindex des tables système et des tables de données est fait tous les mois

A la vue de ces informations, avez-vous une idée des principaux problèmes pouvant causé ces lenteurs
à l'insertion ?

Voici ci joint la conf de postgresql adaptée en fonction du site de Varlena :

tcpip_socket = true
max_connections = 1000
shared_buffers = 56250
sort_mem = 5120
vacuum_mem = 300000
max_fsm_pages = 200000
max_fsm_relations = 10000
wal_buffers = 256
checkpoint_segments = 16
checkpoint_timeout = 1800
commit_delay = 100000
effective_cache_size = 243750
default_statistics_target = 1000
syslog = 0
log_error_verbosity = default
log_min_error_statement = error
log_min_duration_statement = 1000
silent_mode = false
log_connections = true
log_pid = true
log_timestamp = true
stats_start_collector = true
stats_row_level = true
datestyle = 'SQL,European'
dynamic_library_path = '/usr/share/postgresql:/usr/lib/postgresql:/usr/lib/postgresql/lib'
deadlock_timeout = 2000

Le Tue, 06 Jun 2006 16:06:58 +0200
julien WICQUART <j(dot)wicquart(at)newtech(dot)fr> a écrit:

> Bonjour,
>
> j'utilise un serveur postgresql 7.4 (2 serveurs en mode
> actif/secours) travaillant sur une partition en raid réseau pour les
> datas et le WAL (mode fsync)
.
Je ne suis pas un EXPERT mais le seul souvenir de "lenteurs" qui arrivaient
à rendre un serveur presque inutilisable était du à des query pas "clearés".
La doc indique quelque part que l'utilisateur (ou le progammeur) doit
fermer ses queries, faute de quoi ils s'entassent et finissent par saturer
la mémoire, jusqu'à deconnexion de l'utilisateur.
A part ça, avec une carte SCSI-Raid Mylex DAC960 et deux disques en raid-1,
ça va bien plus vite qu'avec du SCSI ordinaire et je ne connais pas drbd ...
Seul autre problème, postgres fait volontier des recherches sequentielles,
ce qui peut être desactivé dans postgresql-conf "enable_seqscan=false"
mais ceci ne doit jouer que sur les recherches, pas les mises à jour, où
le problème pourrait provenir des "sync" que ferait faire drbd ... ?

>
> Descriptif des serveurs :
> - bi-xéon 3,4Ghz
> - 3Go DDRAM
> - disque système : 18Go ultra320 SCSI 10000tr/mn
> - disque data et WAL postgres : 146Go ultra320 SCSI 15000tr/mn
> (c'est sur ce disque qu'est montée la partition drbd)
> - interfaces réseau 1Gbps
> - debian sarge
> - kernel 2.6.8-3-686-smp
> - postgresql 7.4 (7.4.7) - je n'ai pas la possibilité de passer en
> version 8.1 (du fait de l'ancienneté des clients utilisés).
> - drbd 0.7 (0.7.10)
>
> Le raid réseau est assuré par le logiciel drbd (0.7) en mode
> synchrone avec un lien point à point 1 gigabits entre les serveurs.
> Les benchs disponibles sur le net et mes tests montrent une lenteur
> apportée par la couche DRBD en écriture. Celle-ci n'est néanmoins
> pas significative et n'explique pas le problème suivant.
>
>
> J'ai environ 1000cnx/mn sur une base de 45 tables dont les plus
> grosses n'exédent pas 1,5 millions de lignes.
> Je n'ai pas activé les traces donc je ne sais pas quelles sont les
> tables les plus utilisées. Je ne maitrise pas les clients, je ne
> peux donc pas minimiser le nombre d'ouverture et de fermeture de
> session (si ce n'est par l'ajout d'un proxy).
>
> Mon problème est le suivant :
> certains insert peuvent prendre jusqu'à 90s et ceci :
> - alors que la majorité des insert prennent un temps correct (moins
> d'1 seconde) - aléatoirement dans le temps (aucune corrélation
> possible avec des traitements) - aléatoirement sur les tables (pas
> une table en particuliers) - sans lock utilisé sur les tables
> - alors qu'un vacuum analyze est fait toutes les nuits
> - alors qu'autovacuum fonctionne (j'ai bien validé que ce n'était
> pas autovacuum qui crée ces problèmes) - alors qu'un reindex des
> tables système est fait toutes les semaines - alors qu'un reindex
> des tables système et des tables de données est fait tous les mois
>
>
> A la vue de ces informations, avez-vous une idée des principaux
> problèmes pouvant causé ces lenteurs à l'insertion ?
>
>
> Voici ci joint la conf de postgresql adaptée en fonction du site de
> Varlena :
>
> tcpip_socket = true
> max_connections = 1000
> shared_buffers = 56250
> sort_mem = 5120
> vacuum_mem = 300000
> max_fsm_pages = 200000
> max_fsm_relations = 10000
> wal_buffers = 256
> checkpoint_segments = 16
> checkpoint_timeout = 1800
> commit_delay = 100000
> effective_cache_size = 243750
> default_statistics_target = 1000
> syslog = 0
> log_error_verbosity = default
> log_min_error_statement = error
> log_min_duration_statement = 1000
> silent_mode = false
> log_connections = true
> log_pid = true
> log_timestamp = true
> stats_start_collector = true
> stats_row_level = true
> datestyle = 'SQL,European'
> dynamic_library_path =
> '/usr/share/postgresql:/usr/lib/postgresql:/usr/lib/postgresql/lib'
> deadlock_timeout = 2000
>
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: don't forget to
> increase your free space map settings
>

--
Alain Lucari (Eurlix)

julien WICQUART a écrit :
> Bonjour,
>
> j'utilise un serveur postgresql 7.4 (2 serveurs en mode actif/secours) travaillant sur une partition
> en raid réseau pour les datas et le WAL (mode fsync).
>
> Descriptif des serveurs :
> - bi-xéon 3,4Ghz
> - 3Go DDRAM
> - disque système : 18Go ultra320 SCSI 10000tr/mn
> - disque data et WAL postgres : 146Go ultra320 SCSI 15000tr/mn (c'est sur ce disque qu'est montée la
> partition drbd)
> - interfaces réseau 1Gbps
> - debian sarge
> - kernel 2.6.8-3-686-smp
> - postgresql 7.4 (7.4.7) - je n'ai pas la possibilité de passer en version 8.1 (du fait de
> l'ancienneté des clients utilisés).
> - drbd 0.7 (0.7.10)
>
> Le raid réseau est assuré par le logiciel drbd (0.7) en mode synchrone avec un lien point à point 1
> gigabits entre les serveurs. Les benchs disponibles sur le net et mes tests montrent une lenteur
> apportée par la couche DRBD en écriture. Celle-ci n'est néanmoins pas significative et n'explique
> pas le problème suivant.

Considérons cela comme axiomatique !

> J'ai environ 1000cnx/mn sur une base de 45 tables dont les plus grosses n'exédent pas 1,5 millions
> de lignes.
> Je n'ai pas activé les traces donc je ne sais pas quelles sont les tables les plus utilisées.
> Je ne maitrise pas les clients, je ne peux donc pas minimiser le nombre d'ouverture et de fermeture
> de session (si ce n'est par l'ajout d'un proxy).

La tennue des statistiques est essentielle pour les opérantions de
maintenance. Vos tables ont-elles des index en CLUSTER ? Si oui sachez
qu'avec votre version de PG, un VACUUM n'implique pas un CLUSTER. Qui
lui même doit être suivi de préférence d'un ANALYSE pour que
l'optimiseur fasse les meilleurs choix.

(...)

Stéphane.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bonjour,

merci pour cette réponse, excusez-moi de ne pas vous avoir répondu plus tôt.

Nous n'utilisons pas d'index en cluster.

- --
Julien WICQUART NEWTECH MULTIMEDIA
Responsable Exploitation 3 ch du Pigeonnier de la Cépière
Tel: +33 5 61 43 14 80 31081 TOULOUSE
Gsm: +33 6 88 17 16 30 http://www.newtech.fr/
Fax: +33 5 61 43 20 11
j(dot)wicquart(at)newtech(dot)fr

Stéphane wrote:
> julien WICQUART a écrit :
>> Bonjour,
>>
>> j'utilise un serveur postgresql 7.4 (2 serveurs en mode actif/secours) travaillant sur une partition
>> en raid réseau pour les datas et le WAL (mode fsync).
>>
>> Descriptif des serveurs :
>> - bi-xéon 3,4Ghz
>> - 3Go DDRAM
>> - disque système : 18Go ultra320 SCSI 10000tr/mn
>> - disque data et WAL postgres : 146Go ultra320 SCSI 15000tr/mn (c'est sur ce disque qu'est montée la
>> partition drbd)
>> - interfaces réseau 1Gbps
>> - debian sarge
>> - kernel 2.6.8-3-686-smp
>> - postgresql 7.4 (7.4.7) - je n'ai pas la possibilité de passer en version 8.1 (du fait de
>> l'ancienneté des clients utilisés).
>> - drbd 0.7 (0.7.10)
>>
>> Le raid réseau est assuré par le logiciel drbd (0.7) en mode synchrone avec un lien point à point 1
>> gigabits entre les serveurs. Les benchs disponibles sur le net et mes tests montrent une lenteur
>> apportée par la couche DRBD en écriture. Celle-ci n'est néanmoins pas significative et n'explique
>> pas le problème suivant.
>>
>
> Considérons cela comme axiomatique !
>
>>
>> J'ai environ 1000cnx/mn sur une base de 45 tables dont les plus grosses n'exédent pas 1,5 millions
>> de lignes.
>> Je n'ai pas activé les traces donc je ne sais pas quelles sont les tables les plus utilisées.
>> Je ne maitrise pas les clients, je ne peux donc pas minimiser le nombre d'ouverture et de fermeture
>> de session (si ce n'est par l'ajout d'un proxy).
>>
>> Mon problème est le suivant :
>> certains insert peuvent prendre jusqu'à 90s et ceci :
>> - alors que la majorité des insert prennent un temps correct (moins d'1 seconde)
>> - aléatoirement dans le temps (aucune corrélation possible avec des traitements)
>> - aléatoirement sur les tables (pas une table en particuliers)
>> - sans lock utilisé sur les tables
>> - alors qu'un vacuum analyze est fait toutes les nuits
>> - alors qu'autovacuum fonctionne (j'ai bien validé que ce n'était pas autovacuum qui crée ces problèmes)
>> - alors qu'un reindex des tables système est fait toutes les semaines
>> - alors qu'un reindex des tables système et des tables de données est fait tous les mois
>>
>>
>> A la vue de ces informations, avez-vous une idée des principaux problèmes pouvant causé ces lenteurs
>> à l'insertion ?
>>
>>
>> Voici ci joint la conf de postgresql adaptée en fonction du site de Varlena :
>>
>> tcpip_socket = true
>> max_connections = 1000
>> shared_buffers = 56250
>> sort_mem = 5120
>> vacuum_mem = 300000
>> max_fsm_pages = 200000
>> max_fsm_relations = 10000
>> wal_buffers = 256
>> checkpoint_segments = 16
>> checkpoint_timeout = 1800
>> commit_delay = 100000
>> effective_cache_size = 243750
>> default_statistics_target = 1000
>> syslog = 0
>> log_error_verbosity = default
>> log_min_error_statement = error
>> log_min_duration_statement = 1000
>> silent_mode = false
>> log_connections = true
>> log_pid = true
>> log_timestamp = true
>> stats_start_collector = true
>> stats_row_level = true
>> datestyle = 'SQL,European'
>> dynamic_library_path = '/usr/share/postgresql:/usr/lib/postgresql:/usr/lib/postgresql/lib'
>> deadlock_timeout = 2000
>>
>>
>
> La tennue des statistiques est essentielle pour les opérantions de
> maintenance. Vos tables ont-elles des index en CLUSTER ? Si oui sachez
> qu'avec votre version de PG, un VACUUM n'implique pas un CLUSTER. Qui
> lui même doit être suivi de préférence d'un ANALYSE pour que
> l'optimiseur fasse les meilleurs choix.
>
> (...)
>
> Stéphane.
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)

iD8DBQFEmAlbyavHQ2fnGwsRAjHMAJ9ilbC/PuzZwMM5Vhyx5dU/ugq0kQCgjHLB
d1DU/dKnmGQQVPgVmFmkLRQ=
=/FuV
-----END PGP SIGNATURE-----

Bonjour,

merci pour cette réponse, excusez-moi de ne pas vous avoir répondu plus tôt.

1/ Fermeture de connexions
J'ai vérifié la fermeture des connexions côté client tout semble se faire correctement.
D'autre part je ne souffre pas de manque de mémoire au niveau du serveur.

2/ Carte CSI-Raid Mylex DAC960
Pour ce qui est de la carte CSI-Raid Mylex DAC960, je n'ai pas la possibilité, pour l'instant de
remplacer le matériel.

3/ Scans séquentiels
Effectivement je remarque des lenteurs sur des inserts.

4/ Sync DRBD
J'utilise le protocol C pour la synchronisation DRBD qui est effectivement le plus lourd mais aussi
le plus adapté à un environnement de production.
J'ai constaté que DRBD génère des lenteurs à l'écriture mais de là à arriver à 40 voir 80 secondes ...
Je vais néanmoins essayer de monter une plateforme de test afin de valider mes dire.

Si d'autres idées vous viennent, n'hésitez pas !

Merci.

--

--
Julien WICQUART NEWTECH MULTIMEDIA
Responsable Exploitation 3 ch du Pigeonnier de la Cépière
Tel: +33 5 61 43 14 80 31081 TOULOUSE
Gsm: +33 6 88 17 16 30 http://www.newtech.fr/
Fax: +33 5 61 43 20 11
j(dot)wicquart(at)newtech(dot)fr

Stéphane wrote:

Je ne suis pas un EXPERT mais le seul souvenir de "lenteurs" qui arrivaient
à rendre un serveur presque inutilisable était du à des query pas "clearés".
La doc indique quelque part que l'utilisateur (ou le progammeur) doit
fermer ses queries, faute de quoi ils s'entassent et finissent par saturer
la mémoire, jusqu'à deconnexion de l'utilisateur.
A part ça, avec une carte SCSI-Raid Mylex DAC960 et deux disques en raid-1,
ça va bien plus vite qu'avec du SCSI ordinaire et je ne connais pas drbd ...
Seul autre problème, postgres fait volontier des recherches sequentielles,
ce qui peut être desactivé dans postgresql-conf "enable_seqscan=false"
mais ceci ne doit jouer que sur les recherches, pas les mises à jour, où
le problème pourrait provenir des "sync" que ferait faire drbd ... ?

Alain Lucari (Eurlix)

> julien WICQUART a écrit :
>> Bonjour,
>>
>> j'utilise un serveur postgresql 7.4 (2 serveurs en mode actif/secours) travaillant sur une partition
>> en raid réseau pour les datas et le WAL (mode fsync).
>>
>> Descriptif des serveurs :
>> - bi-xéon 3,4Ghz
>> - 3Go DDRAM
>> - disque système : 18Go ultra320 SCSI 10000tr/mn
>> - disque data et WAL postgres : 146Go ultra320 SCSI 15000tr/mn (c'est sur ce disque qu'est montée la
>> partition drbd)
>> - interfaces réseau 1Gbps
>> - debian sarge
>> - kernel 2.6.8-3-686-smp
>> - postgresql 7.4 (7.4.7) - je n'ai pas la possibilité de passer en version 8.1 (du fait de
>> l'ancienneté des clients utilisés).
>> - drbd 0.7 (0.7.10)
>>
>> Le raid réseau est assuré par le logiciel drbd (0.7) en mode synchrone avec un lien point à point 1
>> gigabits entre les serveurs. Les benchs disponibles sur le net et mes tests montrent une lenteur
>> apportée par la couche DRBD en écriture. Celle-ci n'est néanmoins pas significative et n'explique
>> pas le problème suivant.
>>
>>
>> J'ai environ 1000cnx/mn sur une base de 45 tables dont les plus grosses n'exédent pas 1,5 millions
>> de lignes.
>> Je n'ai pas activé les traces donc je ne sais pas quelles sont les tables les plus utilisées.
>> Je ne maitrise pas les clients, je ne peux donc pas minimiser le nombre d'ouverture et de fermeture
>> de session (si ce n'est par l'ajout d'un proxy).
>>
>> Mon problème est le suivant :
>> certains insert peuvent prendre jusqu'à 90s et ceci :
>> - alors que la majorité des insert prennent un temps correct (moins d'1 seconde)
>> - aléatoirement dans le temps (aucune corrélation possible avec des traitements)
>> - aléatoirement sur les tables (pas une table en particuliers)
>> - sans lock utilisé sur les tables
>> - alors qu'un vacuum analyze est fait toutes les nuits
>> - alors qu'autovacuum fonctionne (j'ai bien validé que ce n'était pas autovacuum qui crée ces problèmes)
>> - alors qu'un reindex des tables système est fait toutes les semaines
>> - alors qu'un reindex des tables système et des tables de données est fait tous les mois
>>
>>
>> A la vue de ces informations, avez-vous une idée des principaux problèmes pouvant causé ces lenteurs
>> à l'insertion ?
>>
>>
>> Voici ci joint la conf de postgresql adaptée en fonction du site de Varlena :
>>
>> tcpip_socket = true
>> max_connections = 1000
>> shared_buffers = 56250
>> sort_mem = 5120
>> vacuum_mem = 300000
>> max_fsm_pages = 200000
>> max_fsm_relations = 10000
>> wal_buffers = 256
>> checkpoint_segments = 16
>> checkpoint_timeout = 1800
>> commit_delay = 100000
>> effective_cache_size = 243750
>> default_statistics_target = 1000
>> syslog = 0
>> log_error_verbosity = default
>> log_min_error_statement = error
>> log_min_duration_statement = 1000
>> silent_mode = false
>> log_connections = true
>> log_pid = true
>> log_timestamp = true
>> stats_start_collector = true
>> stats_row_level = true
>> datestyle = 'SQL,European'
>> dynamic_library_path = '/usr/share/postgresql:/usr/lib/postgresql:/usr/lib/postgresql/lib'
>> deadlock_timeout = 2000
>>
>>

Bonjour,

L'application fait effectivement d'autre action mais les temps dont
je parle sont relevés dans les logs de PostgreSQL (slow query). Donc
il s'agit vraiment du temps d'exécution de la requêtes.

De plus ces lenteurs arrivent à toutes heures de la journée et à priori
sur l'ensemble des tables (même petites : 100 enregistrements).

La raison pour laquelle j'ai posté ce mail sur la liste est de savoir si
un méchanisme particuliers du fonctionnement de Postgres pourrait expliquer
ces temps de réponse d'insert de 80s.

L'utilisation de la couche DRBD apporte effectivement des lenteurs en écriture
et d'après ce que j'ai pu lire hier sur les nouvelles versions peut-être quelques
lenteurs en lecture.
Prenons alors l'hypothèse que nous travaillons avec un mauvais disque.

Est-ce que l'utilisation d'un mauvais disque peut entraîner des lenteurs aussi importantes
répétées mais non permanentes et sans périodicité remarquée pour une insertion sur une table
de 100 enregistrements.
Si oui, dans quels cas cela arrive t'il ? ( pas assez de mémoire, donc utilisation du disque;
étape d'écriture des tampons WAL sur le disque; ... )

--
Julien WICQUART

Pierre Didelon wrote:
>
>
> julien WICQUART wrote:
>
>> Bonjour,
>>
>> les insert ne violent pas de clé primaire et j'ai bien validé dans les
>> logs qu'il n'y ai pas ce genre d'informations.
>>
>> Merci.
>>
>> --
>> Julien WICQUART
> Etes vous sur de rien faire d'autre dans votre appli?
> Le même insert dans psql donnerait le même temps ou pas?
> Excusez mes questions primaires... j'essaye de comprendre.
> Pierre Didelon
>
>

julien WICQUART wrote:

> Est-ce que l'utilisation d'un mauvais disque peut entraîner des lenteurs aussi
importantes
> répétées mais non permanentes et sans périodicité remarquée pour une insertion
sur une table
> de 100 enregistrements.
> Si oui, dans quels cas cela arrive t'il ? ( pas assez de mémoire, donc
utilisation du disque;
> étape d'écriture des tampons WAL sur le disque; ... )

Vous pourriez comparer la sortie de la commande vmstat en
situation normale avec la sortie de la même commande au moment
où cette lenteur est observée.

vmstat indique déjà si la machine swappe ou non, le niveau des
E/S disque et l'usage des processeurs, ça peut déjà renseigner
pas mal quand on cherche quelle est la partie du système qui
engorge.

--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org