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?
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 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.
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.
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
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.
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.