Re: Slow queries after Windows startup - Mailing list pgsql-performance

Have you verified that this is isn't caused by cold filesystem caches?


On 11.01.2018 09:19, POUSSEL, Guillaume wrote:
> 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
> 
>  <mailto:guillaume.poussel@sogeti.com> guillaume.poussel@sogeti.com
> 
>  
> 
>  
> 
>  
> 
>  
> 
>  
> 
> 
> 
> 
> This message contains information that may be privileged or confidential and is the property of the Capgemini Group.
Itis intended only for the person to whom it is addressed. If you are not the intended recipient, you are not
authorizedto read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you
receivethis message in error, please notify the sender immediately and delete all copies of this message.
 
>