Postgres 10.1 WIN | SSL langsam - wie schnneller machen ?

Konfiguration des PostgreSQL Datenbankservers.
ds2018
Neuer Benutzer
Beiträge: 10
Registriert: Fr 19. Jan 2018, 10:23

Postgres 10.1 WIN | SSL langsam - wie schnneller machen ?

Beitrag von ds2018 »

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

Bild
http://www.bilder-upload.eu/show.php?fi ... 355080.png

Der Server hat 8 GB RAM

postgresql.conf

Code: Alles auswählen

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?

akretschmer
Erfahrener Benutzer
Beiträge: 7715
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Postgres 10.1 WIN | SSL langsam - wie schnneller machen ?

Beitrag von akretschmer »

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.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

ds2018
Neuer Benutzer
Beiträge: 10
Registriert: Fr 19. Jan 2018, 10:23

Re: Postgres 10.1 WIN | SSL langsam - wie schnneller machen ?

Beitrag von ds2018 »

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.

akretschmer
Erfahrener Benutzer
Beiträge: 7715
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Postgres 10.1 WIN | SSL langsam - wie schnneller machen ?

Beitrag von akretschmer »

recht hohe Kosten, seq-scan. Möglicherweise fehlende Indexe. Aber Virenscanner sind auch immer gern Ursache von Problemen.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

ds2018
Neuer Benutzer
Beiträge: 10
Registriert: Fr 19. Jan 2018, 10:23

Re: Postgres 10.1 WIN | SSL langsam - wie schnneller machen ?

Beitrag von ds2018 »

Also Virenscanner ist deInstalliert

Habe REINDEX DATABASE <DBNAME> 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.
Zuletzt geändert von ds2018 am Di 23. Jan 2018, 08:14, insgesamt 1-mal geändert.

castorp
Erfahrener Benutzer
Beiträge: 1202
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Postgres 10.1 WIN | SSL langsam - wie schnneller machen ?

Beitrag von castorp »

Bitte erzeuge den Plan mit

Code: Alles auswählen

explain (analyze, buffers)
select ....
damit wir sehen können welcher Teil genau langsam ist.

Und bitte formatier' den Plan als code (siehe help/bbcode#f2r1) damit man das besser lesen kann.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

ds2018
Neuer Benutzer
Beiträge: 10
Registriert: Fr 19. Jan 2018, 10:23

Re: Postgres 10.1 WIN | SSL langsam - wie schnneller machen ?

Beitrag von ds2018 »

http://www.bilder-upload.eu/show.php?fi ... 692418.png

<code>
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)
</code>
Zuletzt geändert von ds2018 am Di 23. Jan 2018, 09:33, insgesamt 1-mal geändert.

akretschmer
Erfahrener Benutzer
Beiträge: 7715
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Postgres 10.1 WIN | SSL langsam - wie schnneller machen ?

Beitrag von akretschmer »

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?
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

ds2018
Neuer Benutzer
Beiträge: 10
Registriert: Fr 19. Jan 2018, 10:23

Re: Postgres 10.1 WIN | SSL langsam - wie schnneller machen ?

Beitrag von ds2018 »

http://www.bilder-upload.eu/show.php?fi ... 696872.png

<code>
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)

</code>

akretschmer
Erfahrener Benutzer
Beiträge: 7715
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Postgres 10.1 WIN | SSL langsam - wie schnneller machen ?

Beitrag von akretschmer »

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)
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Antworten