Postgres 10.1 WIN | SSL langsam - wie schnneller machen ?

Hallo,
ich habe da einen virtuellen Windows Server (VMWare) auf dem PostgreSQL 10.1 läuft.
Auf diesem ist SSL aktiv.

Es liegt eine DB mit ca. 38 GB drauf es wird ein SELECT auf eine Tabelle gemacht
diese hat ca. 8 GB.
Die Abfrage dauert ca. 4 min 30 sec.
Aut einem Laptop ohne SSL dauert diese ca. 7 sec.

Die DISK auf dem die DB liegt habe ich mit CrystalDiskMark 6 gemessen
das sieht gut aus

Der Server hat 8 GB RAM

postgresql.conf

listen_addresses = '*'		# what IP address(es) to listen on;
					# comma-separated list of addresses;
					# defaults to 'localhost'; use '*' for all
					# (change requires restart)
port = 5432				# (change requires restart)
max_connections = 25
ssl = on
shared_buffers = 512MB
					# (change requires restart)
work_mem = 52428kB
maintenance_work_mem = 512MB
dynamic_shared_memory_type = windows	# the default is the first option

wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 2GB

checkpoint_completion_target = 0.9

effective_cache_size = 6GB
default_statistics_target = 100

log_destination = 'stderr'		# Valid values are combinations of
					# stderr, csvlog, syslog, and eventlog,
					# depending on platform.  csvlog
					# requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on		# Enable capturing of stderr and csvlog
					# into log files. Required to be on for
					# csvlogs.
					# (change requires restart)
log_directory = pg_log
log_timezone = 'CET'
datestyle = 'iso, dmy'
timezone = 'CET'
lc_messages = 'German_Germany.1252'			# locale for system error message
lc_monetary = 'German_Germany.1252'			# locale for monetary formatting
lc_numeric = 'German_Germany.1252'			# locale for number formatting
lc_time = 'German_Germany.1252'				# locale for time formatting
default_text_search_config = 'pg_catalog.german'
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
shared_preload_libraries = 'pg_stat_statements'   # (change requires restart)
pg_stat_statements.max = 1000
pg_stat_statements.track = all

Wie bekomm ich das gelöst, möglichst ohne auf SSL zu verzichten?

Sind beide Tests ansonsten gleich?

  • vergleichbare Hardware
  • gleiche Tabelle
  • selbe Größe von Tablebloat etc.
  • sind das (beides) Verbindungen via Socket oder TCP/IP
  • wird die reine Abfragezeit gemessen oder ist da eine Applikation noch involviert?

Um das sinnvoll vergleichen zu können, wäre ein EXPLAIN (analyse, buffers) … recht sinnvoll.

Hallo akretschmer,
vielen dank

gleiche Tabelle

Die DBs sind identisch somit auch die Tabellen

selbe Größe von Tablebloat etc.

ja

sind das (beides) Verbindungen via Socket oder TCP/IP

Ja beides via TCP

wird die reine Abfragezeit gemessen oder ist da eine Applikation noch involviert?

direkt via PSQL verbunden \timing

Um das sinnvoll vergleichen zu können, wäre ein EXPLAIN (analyse, buffers) … recht sinnvoll.

QUERY PLAN

Gather Merge (cost=3260823.01…3260942.25 rows=1022 width=12)
Workers Planned: 2
→ Sort (cost=3259822.99…3259824.27 rows=511 width=12)
Sort Key: ttimestamp
→ Parallel Seq Scan on sd_eventhistory (cost=0.00…3259800.00 rows=511 width=12)
Filter: ((itype = 9) AND (iunitid = 2) AND (isliceid = 11) AND ((ctxt2)::text = ‘P_Drvs.afbDrv[giDRV_W31].stFrDrvPar.fP526DisplNegTorque’::text))

Wobei der Server z.Z. hohe CPU Last hat… von einem anderen Prozess (nicht postgres)


Aktuell ist der Virenscanner etwas im verdacht…
ticket beim AV Hersteller läuft.

recht hohe Kosten, seq-scan. Möglicherweise fehlende Indexe. Aber Virenscanner sind auch immer gern Ursache von Problemen.

Also Virenscanner ist deInstalliert

Habe REINDEX DATABASE gemacht

danach dann nochmal den EXPLAIN

EXPLAIN Select ttimestamp,freal1 from public.sd_eventhistory WHERE itype=9 and iunitid=2 and isliceid=11 and ctxt2=‘P_Drvs.afbDrv[giDRV_W31].stFrDrvPar.fP526DisplNegTorque’ order by ttimestamp;

QUERY PLAN

Gather Merge (cost=3260823.01…3260942.25 rows=1022 width=12)
Workers Planned: 2
-> Sort (cost=3259822.99…3259824.27 rows=511 width=12)
Sort Key: ttimestamp
-> Parallel Seq Scan on sd_eventhistory (cost=0.00…3259800.00 rows=511 width=12)
Filter: ((itype = 9) AND (iunitid = 2) AND (isliceid = 11) AND ((ctxt2)::text = ‘P_Drvs.afbDrv[giDRV_W31].stFrDrvPar.fP526DisplNegTorque’::text))
(6 Zeilen)

QUERY mit PSQL ( Remote via 100 MB/s )

Zeit: 227344,140 ms (03:47,344)


QUERY per pgAdmin4 ( auf dem Server gestartet ) ca. 3 min 23 sec


Habe mit iperf die Netzgeschwindigkeit gemessen.
Bei einem PC mit 1GB/s erreiche ich ca. 927 MB/s bei einem mit 100MB/s erreiche ich ca. 95MB/s
daher gehe ich davon aus das das Netz OK ist.

Bitte erzeuge den Plan mit

explain (analyze, buffers)
select ....

damit wir sehen können welcher Teil genau langsam ist.

Und bitte formatier’ den Plan als code (siehe https://www.pg-forum.de/help/bbcode#f2r1) damit man das besser lesen kann.


QUERY PLAN

Gather Merge (cost=3260823.01…3260942.25 rows=1022 width=12) (actual time=248527.904…248527.958 rows=223 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=252 read=749217
-> Sort (cost=3259822.99…3259824.27 rows=511 width=12) (actual time=248505.041…248505.045 rows=74 loops=3)
Sort Key: ttimestamp
Sort Method: quicksort Memory: 27kB
Buffers: shared hit=763 read=2372142
-> Parallel Seq Scan on sd_eventhistory (cost=0.00…3259800.00 rows=511 width=12) (actual time=589.326…248504.524 rows=74 loops=3)
Filter: ((itype = 9) AND (iunitid = 2) AND (isliceid = 11) AND ((ctxt2)::text = ‘P_Drvs.afbDrv[giDRV_W31].stFrDrvPar.fP526DisplNegTorque’::text))
Rows Removed by Filter: 35477965
Buffers: shared hit=707 read=2372142
Planning time: 1.318 ms
Execution time: 248531.913 ms
(14 Zeilen)

Zeile 10 und 11 in Deinem Bild (Hint: besser den Text zeigen, z.B. via https://explain.depesz.com/ ) zeigen mir, daß da sehr offensichtlich Indexe fehlen.

Was sagt ein (in psql gemachtes)

\d sd_eventhistory

aus?

db01=# \d sd_eventhistory Tabelle »public.sd_eventhistory« Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert ------------+-----------------------------+--------------+---------------+--------------------------------------------- id | bigint | | not null | nextval('sd_eventhistory_id_seq'::regclass) ttimestamp | timestamp without time zone | | | iprojnr | integer | | | isliceid | smallint | | | iunitid | smallint | | | ioriginid | integer | | | imsgid | bigint | | | itype | integer | | | iint1 | integer | | | iint2 | integer | | | iint3 | integer | | | freal1 | real | | | freal2 | real | | | ctxt1 | character varying(1024) | | | ctxt2 | character varying(256) | | | Indexe: "sd_eventhistory_pkey" PRIMARY KEY, btree (id) "idxinsert_sd_eventhistory" UNIQUE, btree (ttimestamp, imsgid, iprojnr, isliceid, ioriginid, iunitid, itype) "idxlastts_sd_eventhistory" btree (iprojnr, isliceid, iunitid)

wie vermutet, Deine Indexe passen nicht zu DIESER Abfrage.

Ein Index auf (itype,iunitid,isliceid) und vielleicht auf ctxt2 könnten helfen. Hängt aber auch davon ab, wie die Daten verteilt sind. Und auch davon, was es noch so an Abfragen gibt. Man kann Indexe kombinieren (wie Dein idxinsert_sd_eventhistory), dann müssen aber alle vorn stehenden Spalten auch im WHERE vorkommen, also ttimestamp, imsgid, iprojnr, …) - je mehr, umso besser. Wenn das nicht immer der Fall ist, dann eher einzelne Indexe, PG greift dann zu einem Bitmap Index Scan.

Um den Index auf ctxt2 kleiner zu halten könnte der auf auf md5(ctxt2) erstellt werden, dann müßte die Abfrage auf

(md5((ctxt2)::text) = md5(‘P_Drvs.afbDrv[giDRV_W31].stFrDrvPar.fP526DisplNegTorque’::text)))

umgeschrieben werden (funktionaler Index)

Danke für die Infos.
Die DB kommt aus der Fachabteilung, da kann ich leider nichts dazu sagen,

warum fragst Du dann?

Ich kann ja mal einen Performance-Workshop bei Euch machen, wohin müßte das Angebot gehen?

Vielen Dank

Ein Index auf (itype,iunitid,isliceid) und vielleicht auf ctxt2 könnten helfen.

Danke wie lege ich diesen an ?

Die DB kommt aus der Fachabteilung, daher weiss ich nicht viel dazu.

Command:     CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

Danke!!!

Vorsicht dabei. Je nach Tabellengröße kann die Indexerstellung längere Zeit dauern - und dabei die Tabelle sperren. Mit CREATE INDEX CONCURRENTLY vermeidet die Sperren.

Hallo,
danke, habe erst mal nichts an den Indexen gemacht.

Aber ich habe an dem Parameter: random_page_cost in der postgres.conf geschraubt.

Standart ist wohl random_page_cost = 4
hab diesen auf random_page_cost = 1.1 angepasst.

Nun dauert der SELECT noch beim 3 durchlauf: ca. 8 sec
erste Abfrage dauerte ca. 55 sec

Jetzt kommt der Virenscanner wieder drauf, dann mal schauen wie sich die Zeit verschlechtert

Auch mit Virenscanner komme ich auf die ca. 8 sec

Beim dritten Durchlauf hast Du die Tabelle im Cache, die schnellere Ausführung dürfte hier nicht Folge von dem runtergesetzten random_page_cost sein. Diese Änderung würde nur dazu führen, daß deutlich eher ein Indexscan verwended wird, allerdings hast Du für die gezeigte Abfrage keinen passenden Index. Vermutlich hast Du also den selben Ausführungsplan. Wenn Du die Ausführung mit explain (analyse, buffers) select … Dir anschaust (mal bei kaltem Cache, also nach kompletten Neustart, und dann mit warmen Cache) wirst Du das sehen.