Vous n'êtes pas identifié(e).
Bonjour,
J'ai aujourd'hui monté un serveur Rsyslog sur une base de type Postgresql pour permettre la gestion et l'affichage des logs facilement.
Mais j'ai des soucis d'optimisation...
j'ai aujourd'hui un serveur RedHAT de 6G de RAM,
coté consommation cela ne semble pas énorme :
2153 postgres 20 0 962m 32m 31m S 0.0 0.6 0:02.22 postmaster
2174 postgres 20 0 966m 9m 8192 S 2.0 0.2 0:08.83 postmaster
2167 postgres 20 0 962m 7380 6620 S 0.0 0.1 0:00.30 postmaster
2172 postgres 20 0 964m 5600 3872 S 0.0 0.1 1:30.81 postmaster
mais une requête simple : SELECT count(*) FROM systemevents; prend énormément de temps (plus de 3 minutes)
il y a actuellement 75 000 000 lignes.
Configuration postgresql
listen_addresses = '*'
max_connections = 51 # (change requires restart)
shared_buffers = 756MB # min 128kB
work_mem = 30MB # min 64kB
maintenance_work_mem = 256MB # min 1MB
wal_buffers = 12MB # min 32kB
checkpoint_segments = 16 # in logfile segments, min 1, 16MB each
checkpoint_completion_target = 0.8 # checkpoint target duration, 0.0 - 1.0
effective_cache_size = 4GB
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = 'pg_log' # directory where log files are written,
log_filename = 'postgresql-%a.log' # log file name pattern,
log_truncate_on_rotation = on # If on, an existing log file of the
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 0 # Automatic rotation of logfiles will
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8' # locale for system error message
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.english'Si vous avez des idées d'optimisation, c'est un serveur dedier, donc qu'il me consomme 95% de ma RAM ne me gene pas, tant qu'il me permet de travailler rapidement.
Si vous avez des questions n’hésitez pas!
Cordialement
Dragondark De Lonlindil
Hors ligne
Bonjour,
Quelle est la version de postgres utilisée, et la volumétrie de la table ?
Si un parcours d'index seul n'est pas possible, le moteur va être obligé de lire toute la table, ce qui peut être assez long. Si une estimation du nombre de lignes vous suffit, vous pouvez utiliser une requête comme
SELECT n_live_tup FROM pg_stat_user_tables WHERE relname = 'systemevents';
Avez-vous des soucis de performance uniquement sur cette requête ?
Julien.
https://rjuju.github.io/
Hors ligne
Bonjour,
Non malheureusement ce n'est pas que cette requête.
ni qui me pose des soucis de performance ni que j'effectue.
si j'effectue une requête précise :
SELECT * FROM systemevents WHERE id = 50000000;La requête est très rapide
par contre une requête de type
SELECT * FROM systemevents WHERE receivedat < '2014-03-14' AND receivedat > '2014-02-14' LIMIT 10;met autant de temps que de calculer le nombre de ligne si ce n'est plus
j'ai actuellement dans le 80M de ligne sur la table.
il n'y a pas de requête imbriqué.
ce qui m'embête en soit : c'est que pendant l’exécution des requête ni le CPU ni la RAM n'est à fond (30% de CPU et 7% de RAM)
donc j'ai l'impression de m'être tromper sur la conf.
Version de postgresql : 8.4.18
Cordialement
Dragondark de Lonlindil
ps : Je vais essayer de jouer avec les index semblerais que j'en ai pas 
(il me semblais en avoir mis)
Dernière modification par dragondark (14/04/2014 09:53:10)
Hors ligne
Pouvez-vous fournir les plans d'exécution de ces requêtes ? S'il n'y a pas d'index il faudra parcourir toutes les lignes, ce qui sera forcément long.
Vous pouvez sinon doubler les paramètres de shared_buffers et maintenance_work_mem dans un premier temps.
Julien.
https://rjuju.github.io/
Hors ligne
S'il n'y a pas d'index il faudra parcourir toutes les lignes, ce qui sera forcément long.
Pas pour la deuxième qui a un "LIMIT 10"... sauf si évidemment on en trouve moins de dix qui satisfont la clausse WHERE.
Vous pouvez sinon doubler les paramètres de shared_buffers et maintenance_work_mem dans un premier temps.
Bof, modifier la conf sans savoir où se trouve le problème...
Il faut absolument avoir le résultat du EXPLAIN ANALYZE pour pouvoir en dire plus, ainsi que la définition des tables en question.
Guillaume.
Hors ligne
je n'ai pas le détail des requêtes que le logiciel utilise c'est pourquoi je fait mes test personnel :
je viens de rajouter un index sur le nom de l’équipement, après 3h d'attente de la réussite de la requête
CREATE INDEX CONCURRENTLY NomEquipement ON systemevents(fromhost);je test :
SELECT * FROM systemevents WHERE fromhost like 'XXXX' ORDER BY id desc LIMIT 10;mais j’attends encore le résultat
je vais augmenter ces paramètres
Hors ligne
il faut créer votre index avec varchar_pattern_ops (http://docs.postgresql.fr/9.2/indexes-opclass.html):
CREATE INDEX test_index ON test_table (col varchar_pattern_ops);Mais ça ne va fonctionner qu'avec un like sur la partie gauche => like 'xxx%'
Dernière modification par arthurr (14/04/2014 14:00:31)
Hors ligne
il n'y a normalement que 250/300 équipements différents, donc cela devrais pas gener, mais même sans %; j’attends encore le résultat de la commande que j'ai passé tout a l'heure
dès que je peux (j’attends la fin de l'autre requête je passe celle la :
par contre dois-je supprimer l'ancienne ou cela la remplace
CREATE INDEX CONCURRENTLY NomEquipement ON systemevents(fromhost varchar_pattern_ops);Dernière modification par dragondark (14/04/2014 14:31:18)
Hors ligne
si NomEquipement (Pg ne va pas tenir compte de la casse) existe, il faut supprimer l'index avant.
Si vous faites des like sans "%", autant conserver l'index actuel et utiliser une clause where avec "="
Pouvez vous poster un explain :
EXPLAIN SELECT * FROM systemevents WHERE fromhost = 'XXXX' ORDER BY id desc LIMIT 10;Hors ligne
je vais corriger l'index car ça peux varier
pour la requête :
rsyslog=> EXPLAIN SELECT * FROM systemevents WHERE fromhost = 'XXXX' ORDER BY id desc LIMIT 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..11974.83 rows=10 width=957)
-> Index Scan Backward using systemevents_pkey on systemevents (cost=0.00..99273730.89 rows=82902 width=957)
Filter: ((fromhost)::text = 'XXXX'::text)
(3 rows)
rsyslog=>Cela permet de dire quoi ?
est-ce que VACUUM FULL est intéressant pour gagner en temps ? ou ça me permettra uniquement de gagner de l'espace disque ?
Dernière modification par dragondark (14/04/2014 15:36:57)
Hors ligne
Il n'utilise pas l'index que vous venez de créer, mais celui qui est sur la primary key (id).
Pouvez vous faire un "analyze systemevents" pour refaire la requête avec le "explain" ?
Pour le vacuum full : il va bloquer votre base de données le temps de réécrire le table, au mieux vous allez gagner de la place sur votre disque
Cela reste une opération de maintenance et n'est pas utilisé sauf si vous avez supprimé / updaté beaucoup de lignes sans faire des vacuum (sans full) réguliers.
Hors ligne
Il utilise l'index sur la clé primaire. Cette clé primaire contient id, ce qui lui permet de récupérer les données déjà triées. Il y a de fortes chances qu'il ait l'impression que "fromhost='XXXX'" soit très fréquent auquel cas l'index n'est pas utile. De plus, le LIMIT 10 fait qu'il pense qu'il aura peu de données à lire dans l'index. Seul un EXPLAIN ANALYZE permettrait d'en dire plus.
Guillaume.
Hors ligne
Oh et pour le VACUUM FULL, si vous le faites en 8.4, n'oubliez pas de réindexer vos index...
Guillaume.
Hors ligne
Merci pour les indications
j'ai lancé l'analyze systemevents
rsyslog=> analyze systemevents;
ANALYZE
rsyslog=> EXPLAIN SELECT * FROM systemevents WHERE fromhost = 'XXXX' ORDER BY id desc LIMIT 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..13199.83 rows=10 width=957)
-> Index Scan Backward using systemevents_pkey on systemevents (cost=0.00..93450812.85 rows=70797 width=957)
Filter: ((fromhost)::text = 'XXXX'::text)
(3 rows)
rsyslog=>Pourtant l'index existe : (j'aime bien les commandes compliqué qui vont vite 
)
rsyslog=> select
rsyslog-> t.relname as table_name,
rsyslog-> i.relname as index_name,
rsyslog-> a.attname as column_name
rsyslog-> from
rsyslog-> pg_class t,
rsyslog-> pg_class i,
rsyslog-> pg_index ix,
rsyslog-> pg_attribute a
rsyslog-> where
rsyslog-> t.oid = ix.indrelid
rsyslog-> and i.oid = ix.indexrelid
rsyslog-> and a.attrelid = t.oid
rsyslog-> and a.attnum = ANY(ix.indkey)
rsyslog-> and t.relkind = 'r'
rsyslog-> and t.relname like 'systemevents'
rsyslog-> order by
rsyslog-> i.relname;
table_name | index_name | column_name
--------------+-------------------+--------------------
systemevents | nomequipement | fromhost
systemevents | systemevents_pkey | id
systemevents | timereporting | devicereportedtimeBon par contre je pense que le devicereportedtime ne sert a rien car j'ai que 8à20log/s sur 80M d'entrée ca fait pas pas beaucoup d’économie (me dire si je me trompe)
La plus part des requêtes seront
SELECT * FROM systemevents WHERE ORDER BY id desc LIMIT 25;
SELECT * FROM systemevents WHERE fromhost like 'XXXX%' ORDER BY id desc LIMIT 25;
SELECT * FROM systemevents WHERE fromhost = 'XXXX' AND devicereportedtime <'XXXX-XX-XX' AND devicereportedtime >'XXXX-XX-XX' ORDER BY id desc LIMIT 25;
SELECT * FROM systemevents WHERE fromhost = 'XXXX' AND facility <> 10 ORDER BY id desc LIMIT 25;La plus part du temps un host est indiqué entièrement ou presque
Hors ligne
Il semblerait que cela vienne du ORDER BY id
rsyslog=> EXPLAIN SELECT * FROM systemevents WHERE fromhost = 'XXXX' LIMIT 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Limit (cost=0.00..36.24 rows=10 width=957)
-> Index Scan using nomequipement on systemevents (cost=0.00..256534.19 rows=70797 width=957)
Index Cond: ((fromhost)::text = 'XXXX'::text)
(3 rows)
rsyslog=>Cela ne peux pas utiliser deux indexes ?
sur une recherche plus compliqué ça semble fonctionner
rsyslog=>
EXPLAIN SELECT * FROM systemevents WHERE fromhost = 'XXXX' AND devicereportedtime < '2014-03-01' AND devicereportedtime > '2014-04-01' ORDER BY id desc LIMIT 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=11074.79..11074.81 rows=10 width=957)
-> Sort (cost=11074.79..11075.67 rows=354 width=957)
Sort Key: id
-> Bitmap Heap Scan on systemevents (cost=9655.47..11067.14 rows=354 width=957)
Recheck Cond: (((fromhost)::text = 'XXXX'::text) AND (devicereportedtime < '2014-03-01 00:00:00'::timestamp without time zone) AND (devicereportedtime > '2014-04-01 00:00:00'::timestamp without time zone))
-> BitmapAnd (cost=9655.47..9655.47 rows=354 width=0)
-> Bitmap Index Scan on nomequipement (cost=0.00..1655.19 rows=70797 width=0)
Index Cond: ((fromhost)::text = 'XXXX'::text)
-> Bitmap Index Scan on timereporting (cost=0.00..7999.86 rows=380724 width=0)
Index Cond: ((devicereportedtime < '2014-03-01 00:00:00'::timestamp without time zone) AND (devicereportedtime > '2014-04-01 00:00:00'::timestamp without time zone))
(10 rows)
rsyslog=>Hors ligne
PostgreSQL peut utiliser deux index pour la clause WHERE. Il ne peut pas utiliser un index pour la clause WHERE et un autre pour la clause ORDER BY pour la requête que vous indiquez.
Un moyen de tester l'autre index serait de faire ceci :
BEGIN;
ALTER TABLE systemevents DROP CONTRAINST systemevents_pkey;
EXPLAIN SELECT * FROM systemevents WHERE fromhost = 'XXXX' ORDER BY id desc LIMIT 10;
ROLLBACK;(surtout faire un ROLLBACK pour annuler la suppression de la contrainte)
Le mieux est évidemment de faire un EXPLAIN ANALYZE.
Une hypothèse possible est que la valeur du work_mem est trop basse pour que PostgreSQL se dise qu'il peut faire le tri en mémoire. Et du coup, il préfère faire le tri par l'index. Sans compter que son estimation du besoin en mémoire est trop fort dans ces versions là, ce qui avait tendance à favoriser l'utilisation des index pour les tris. Et il me semble que cela n'a été corrigé que sur les versions récentes (donc out la 8.4).
Guillaume.
Hors ligne
C'est le genre de raisons ou cela me soule les processus entreprise ><"
où les versions date d'il y a 5 ans.
L'idée est bien;
rsyslog=> EXPLAIN SELECT * FROM systemevents WHERE fromhost = 'XXXX' ORDER BY id desc LIMIT 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Limit (cost=255199.89..255199.92 rows=10 width=957)
-> Sort (cost=255199.89..255376.89 rows=70797 width=957)
Sort Key: id
-> Bitmap Heap Scan on systemevents (cost=1680.96..253670.00 rows=70797 width=957)
Recheck Cond: ((fromhost)::text = 'XXXX'::text)
-> Bitmap Index Scan on nomequipement (cost=0.00..1663.26 rows=70797 width=0)
Index Cond: ((fromhost)::text = 'XXXX'::text)Bon par contre j'ai merder ^^',
Tu avais une erreur dans le mot CONSTRAINT, mais j'avais pas prévu qu'il m’arrête toute la procédure, donc quand je l'ai corrigé, le rollback n'était plus d’actualité 
est-ce que je ne peux pas la laisser supprimé? quel intérêt elle à dans mon environnement ? (je ne suis pas sur de saisir complément l’intérêt des index)
rsyslog=> ALTER TABLE systemevents ADD PRIMARY KEY (id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "systemevents_pkey" for table "systemevents"
ALTER TABLEj'ai augmenter le work_mem a 256M et le max connexion à 10
rsyslog=> EXPLAIN SELECT * FROM systemevents WHERE fromhost = 'XXXX' ORDER BY id desc LIMIT 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..13071.17 rows=10 width=957)
-> Index Scan Backward using systemevents_pkey on systemevents (cost=0.00..93162177.33 rows=71273 width=957)
Filter: ((fromhost)::text = 'XXXX'::text)
(3 rows)
rsyslog=>j'ai tester une requête sans le pkid, et elle était beaucoup plus rapide. la c'est revenu comme avant
Je te remercie pour l'aide que tu m'apportes
Dernière modification par dragondark (15/04/2014 09:53:50)
Hors ligne
Bon j'ai essayer de faire sans la Pkid voir ce que cela donnais, mais cela ne m'arrange pas sauf dans les cas ou j'ai besoin d'information directement sur le host
car un simple :
SELECT receivedat,message FROM systemevents order by id desc LIMIT 25;dure 15ans alors qu'avant ne durais qu'une seconde
faut que je trouve une autre solution
actuellement j'utilise une RedHat RHEL 6.2 x86_64
est-ce qu'une version plus récente que 8.4 existe ? d'après les liens de téléchargement directe j'en ai pas vue mais c'est peut être moi qui n'ai pas bien regardé
http://www.postgresql.org/download/linux/redhat/
je ne sais plus vraiment quoi regarder maintenant :x
ma dernière solution serrait de diviser en plusieurs tables
Hors ligne
est-ce que je ne peux pas la laisser supprimé? quel intérêt elle à dans mon environnement ? (je ne suis pas sur de saisir complément l’intérêt des index)
Ce n'est pas qu'un index, c'est une contrainte. Sans elle, le contrôle des données est supprimé. Est-ce que cela a une conséquence ou non dans ce cas précis, aucune idée. Mais bon, une table sans clé primaire, ça ressemble plus à une erreur qu'autre chose.
est-ce qu'une version plus récente que 8.4 existe ? d'après les liens de téléchargement directe j'en ai pas vue mais c'est peut être moi qui n'ai pas bien regardé hmm
http://www.postgresql.org/download/linux/redhat/
L'URL ci-dessus indique les versions de PostgreSQL proposé par la distribution. Pas les versions qui ont été disponibles après. Vous avez plutôt intérêt à aller regarder sur http://yum.postgresql.org/
ma dernière solution serrait de diviser en plusieurs tables
Il faudrait déjà déterminer quel est le problème avant de se lancer dans ce genre de complexité affreuse.
Commencez déjà par fournir un EXPLAIN ANALYZE de votre requête avec la définition des tables concernées et de leurs index, ainsi que la configuration de PostgreSQL.
Guillaume.
Hors ligne
Ma configuration actuellement :
# cat /evnt_log/postgresDB/postgresql.conf | grep ^[a-zA-Z0-9]
listen_addresses = '*'
max_connections = 10 # (change requires restart)
shared_buffers = 1536MB # min 128kB
work_mem = 256MB # min 64kB
maintenance_work_mem = 512MB # min 1MB
wal_buffers = 12MB # min 32kB
checkpoint_segments = 16 # in logfile segments, min 1, 16MB each
checkpoint_completion_target = 0.8 # checkpoint target duration, 0.0 - 1.0
effective_cache_size = 4GB
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = 'pg_log' # directory where log files are written,
log_filename = 'postgresql-%a.log' # log file name pattern,
log_truncate_on_rotation = on # If on, an existing log file of the
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 0 # Automatic rotation of logfiles will
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8' # locale for system error message
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.english'>Pour une des requêtes qui sont très très longue;
rsyslog=> EXPLAIN ANALYZE SELECT * FROM systemevents WHERE fromhost = 'XXXX' ORDER BY id desc LIMIT 20 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
---------
Limit (cost=0.00..26158.94 rows=20 width=957) (actual time=129134.836..131515.164 rows=20 loops=1)
-> Index Scan Backward using systemevents_pkey on systemevents (cost=0.00..94773822.13 rows=72460 width=957) (actual time=129134.833..131515.142 rows=20
loops=1)
Filter: ((fromhost)::text = 'XXXX'::text)
Total runtime: 131515.293 ms
(4 rows)
rsyslog=>Je suis entrain de coder pour faire un split des données dans plusieurs tables voir si cela arrangerais les choses.
en attendant d'avoir d'autres solutions.
à noté que si l'équipement et le nombre de ligne demandé sont dans les dernières la requete est très rapide mais ceux qui envoient beaucoup de log sont rarement ceux qui nous intéresse ^^
Dernière modification par dragondark (22/04/2014 15:56:52)
Hors ligne
Et que donne un "SELECT * FROM pg_stats WHERE tablename='systemevents' and attname='fromhost'" ?
Que donnent aussi un "SELECT count(*) FROM systemevents" et un "SELECT count(*) FROM systemevents WHERE fromhost='XXXX'" ? (XXXX ayant la même valeur que dans la requête pour laquelle vous avez donné le EXPLAIN ANALYZE).
Et enfin, que donne un "\d systemevents" ?
Merci.
Guillaume.
Hors ligne
Bonjour,
alors le premier résultat me liste une partie des équipements, en peu de temps
rsyslog=> SELECT * FROM pg_stats WHERE tablename='systemevents' and attname='fromhost';
schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
------------+--------------+----------+-----------+-----------+------------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+-------------
public | systemevents | fromhost | 0 | 12 | 217 | {liste 21 eqt) | {0.188233,0.175833,0.1531,0.0981667,0.0623,0.0125,0.0121,0.0111667,0.0110667,0.0106333,0.0105667,0.00946667,0.00826667,0.00823333,0.00773333,0.00723333,0.0068,0.0064,0.00603333,0.00603333,0.0059} | {liste 100 eqt) | 0.307883pour l'equipement, (il apparait pas dans la liste des équipements qui se trouvais dans la requete précédente
rsyslog=> SELECT count(*) FROM systemevents WHERE fromhost='XXXX';
count
-------
2838
(1 row)je dois être à peu prés vers les 80M d'enregistrement (ça stagne car je limite à 3mois d'enregistrement)
Et environ 600 équipements
'-'
rsyslog-> \d+ systemevents
ERROR: column "reltriggers" does not exist at character 41
LINE 1: SELECT relhasindex, relkind, relchecks, reltriggers, relhasr...Hors ligne
Donc il sait que la table contient moins de 0,59% d'occurence de XXXX. Ce qui est confirmé avec le SELECT count(*) de XXXX et total.
Ce qui est étonnant, c'est que son estimation du nombre de lignes renvoyées avec le filtre sur fromhost est un peu à l'ouest (70k au lieu de 2k). Il serait intéressant d'augmenter le niveau de stats pour cette colonne.
Guillaume.
Hors ligne
rsyslog=> EXPLAIN ANALYZE SELECT * FROM systemevents WHERE fromhost = 'XXXX' ORDER BY id desc LIMIT 20 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..26274.76 rows=20 width=957) (actual time=182283.090..238288.456 rows=20 loops=1)
-> Index Scan Backward using systemevents_pkey on systemevents (cost=0.00..85295739.77 rows=64926 width=957) (actual time=182283.086..238288.407 rows=20 loops=1)
Filter: ((fromhost)::text = 'XXXX'::text)
Total runtime: 238294.565 ms
(4 rows)
rsyslog=> SELECT count(*) FROM systemevents WHERE fromhost = 'XXXX';
count
-------
2750
(1 row)
rsyslog=>je viens de retest voir si j'avais pas fait une erreur mais non c'est bien le calcule qu'il fait.
si c'est bien ca que tu regardais.
une idée pour lui faire recalculer ces valeurs ?
Hors ligne
Il vous faut faire un ALTER TABLE SET STATISTICS, puis un ANALYZE. Par exemple :
ALTER TABLE systemevents ALTER fromhost SET STATISTICS 1000;
ANALYZE systemevents;Guillaume.
Hors ligne