Slow queries after Windows startup

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx

 

 

 

 

 

Attachment: smime.p7s
Description: S/MIME cryptographic signature

This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message.

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux