Hello,
I’m running PostgreSQL 9.3 on Windows 7 and I’m having a performance issue at startup. I have installed PostgreSQL as a service through Windows installer.
The database size is 3 Go, with 120 tables.
Every time I try to run queries right after Windows startup, it takes a huge amount of time.
If I restart the PostgreSQL Windows service, queries are way faster.
I have activated debug log and here is what I get before Windows restart:
duration: 2.000 ms parse
duration: 3.000 ms bind
duration: 0.000 ms execute
And after Windows restart:
duration: 364.000 ms parse
duration: 415.000 ms bind
duration: 0.000 ms execute
For information, the test query is:
SELECT t.typlen FROM pg_catalog.pg_type t, pg_catalog.pg_namespace n WHERE t.typnamespace=n.oid AND t.typname='name' AND n.nspname='pg_catalog'
It’s not related to the query itself since other queries give the same result (from 10x to 100x longer).
Here are my settings (all log and locale-related settings omitted on purpose):
bytea_output | escape | session |
checkpoint_segments | 45 | configuration file |
client_encoding | UNICODE | session |
client_min_messages | notice | session |
DateStyle | ISO, DMY | session |
debug_pretty_print | on | configuration file |
debug_print_plan | on | configuration file |
default_text_search_config | pg_catalog.french | configuration file |
listen_addresses | * | configuration file |
logging_collector | on | configuration file |
max_connections | 100 | configuration file |
max_stack_depth | 2MB | environment variable |
port | 5432 | configuration file |
shared_buffers | 128MB | configuration file |
TimeZone | GMT | user |
I run queries through JDBC driver (9.3-1100-jdbc4.jar). I know that the issue is not related to the PC, since it give the same result on a bunch of different computers.
I have two questions:
· What is the difference between restarting PostgreSQL service and restarting the computer? Is PostgreSQL relying on some kind of OS-level cache outside Windows service?
· How can I dig down deeper and see what’s causing PostgreSQL slowdown?
Thanks in advance for your help,
BR,
Guillaume POUSSEL | ♠Sogeti High Tech
guillaume.poussel@sogeti.com
