Windows slowness?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Windows slowness?

Mikkel Lauritsen
Hi all,

I have a query that runs much slower in Postgres on Windows than on
Linux, and I'm so far unable to explain why - the execution plans are
identical and the hardware is reasonably the same caliber.

Using explain analyze on the database running on Windows I get

->  Index Scan using event_pkey on event t1  (cost=0.56..0.95 rows=1
width=295) (actual time=0.075..0.075 rows=0 loops=229227)

The server is Postgres 12, and for reasons outside of my control it runs
on Windows 2012 on a virtual server. It has 4 cores and 32 GB ram
allocated on a Xeon E5 4660 v4, and running winsat shows satisfactory
disk and memory bandwidth and CPU performance.

If I copy the database to my laptop running Linux (Postgres 12 on Fedora
32, i7-9750H, 16 GB ram) I get the exact same execution plan. Explain
analyze says

->  Index Scan using event_pkey on event t1  (cost=0.56..0.95 rows=1
width=295) (actual time=0.008..0.008 rows=0 loops=229227)

Note that the index scans are more than 9 times faster on my laptop, and
the entire query executes about 12 times faster. I realize that each
core in the laptop CPU is faster than a server core and that
virtualization doesn't help performance, but I wouldn't expect that to
make the Windows box 10 times slower.

The table is freshly vacuumed. It has about 10M rows and takes about
2.6G disk space; the index is about 600M. Everything is cached; there's
basically no disk I/O happening while the query is executing.

The only Postgres configuration difference between the Windows and Linux
environments is shared_buffers, which is 4G on my laptop and 512M on the
Windows server, and effective_cache_size which are 8G on the laptop and
16G on the server.

I suspect that something is rotten in for example the provisioning of
the virtualization environment, but before I start pestering the
operations people I would really appreciate any comments on whether the
performance difference is to be expected or if there's some obvious
tuning to try.

Best regards & thanks,
   Mikkel Lauritsen


Reply | Threaded
Open this post in threaded view
|

Re: Windows slowness?

mountain the blue
mikkel,

sorry for being so stupid: did you exclude antivirus/firewall related issue?

Le mer. 10 juin 2020 à 21:41, Mikkel Lauritsen <[hidden email]> a écrit :
Hi all,

I have a query that runs much slower in Postgres on Windows than on
Linux, and I'm so far unable to explain why - the execution plans are
identical and the hardware is reasonably the same caliber.

Using explain analyze on the database running on Windows I get

->  Index Scan using event_pkey on event t1  (cost=0.56..0.95 rows=1
width=295) (actual time=0.075..0.075 rows=0 loops=229227)

The server is Postgres 12, and for reasons outside of my control it runs
on Windows 2012 on a virtual server. It has 4 cores and 32 GB ram
allocated on a Xeon E5 4660 v4, and running winsat shows satisfactory
disk and memory bandwidth and CPU performance.

If I copy the database to my laptop running Linux (Postgres 12 on Fedora
32, i7-9750H, 16 GB ram) I get the exact same execution plan. Explain
analyze says

->  Index Scan using event_pkey on event t1  (cost=0.56..0.95 rows=1
width=295) (actual time=0.008..0.008 rows=0 loops=229227)

Note that the index scans are more than 9 times faster on my laptop, and
the entire query executes about 12 times faster. I realize that each
core in the laptop CPU is faster than a server core and that
virtualization doesn't help performance, but I wouldn't expect that to
make the Windows box 10 times slower.

The table is freshly vacuumed. It has about 10M rows and takes about
2.6G disk space; the index is about 600M. Everything is cached; there's
basically no disk I/O happening while the query is executing.

The only Postgres configuration difference between the Windows and Linux
environments is shared_buffers, which is 4G on my laptop and 512M on the
Windows server, and effective_cache_size which are 8G on the laptop and
16G on the server.

I suspect that something is rotten in for example the provisioning of
the virtualization environment, but before I start pestering the
operations people I would really appreciate any comments on whether the
performance difference is to be expected or if there's some obvious
tuning to try.

Best regards & thanks,
   Mikkel Lauritsen


Reply | Threaded
Open this post in threaded view
|

Re: Windows slowness?

David Rowley
In reply to this post by Mikkel Lauritsen
On Thu, 11 Jun 2020 at 07:41, Mikkel Lauritsen <[hidden email]> wrote:
> I have a query that runs much slower in Postgres on Windows than on
> Linux

> Using explain analyze on the database running on Windows I get
>
> ->  Index Scan using event_pkey on event t1  (cost=0.56..0.95 rows=1
> width=295) (actual time=0.075..0.075 rows=0 loops=229227)

> If I copy the database to my laptop running Linux (Postgres 12 on Fedora
> 32, i7-9750H, 16 GB ram) I get the exact same execution plan. Explain
> analyze says
>
> ->  Index Scan using event_pkey on event t1  (cost=0.56..0.95 rows=1
> width=295) (actual time=0.008..0.008 rows=0 loops=229227)
>

> The table is freshly vacuumed. It has about 10M rows and takes about
> 2.6G disk space; the index is about 600M. Everything is cached; there's
> basically no disk I/O happening while the query is executing.

Can you confirm what: SELECT pg_relation_size('event_pkey'),
pg_relation_size('event'); says on each

> The only Postgres configuration difference between the Windows and Linux
> environments is shared_buffers, which is 4G on my laptop and 512M on the
> Windows server, and effective_cache_size which are 8G on the laptop and
> 16G on the server.

There is some slight advantage to having the buffers directly in
shared buffers. Having them in the kernel's page cache does still
require getting them into shared buffers. Going by these sizes it
seems much more likely that the Linux instance could have all buffers
in shared_buffers, but it seems likely the Windows instance won't. I
can't imagine that counts for 10x, but it surely must count for
something.

It would be good to see:

SET track_io_timing = on;
EXPLAIN (ANALYZE, BUFFERS) <the query>

David


Reply | Threaded
Open this post in threaded view
|

Re: Windows slowness?

Mikkel Lauritsen
Hi David,

Many thanks for your response - you wrote:

On 2020-06-10 23:08, David Rowley wrote:

> On Thu, 11 Jun 2020 at 07:41, Mikkel Lauritsen <[hidden email]> wrote:
>> I have a query that runs much slower in Postgres on Windows than on
>> Linux
>
>> Using explain analyze on the database running on Windows I get
>>
>> ->  Index Scan using event_pkey on event t1  (cost=0.56..0.95 rows=1
>> width=295) (actual time=0.075..0.075 rows=0 loops=229227)
>
>> If I copy the database to my laptop running Linux (Postgres 12 on
>> Fedora
>> 32, i7-9750H, 16 GB ram) I get the exact same execution plan. Explain
>> analyze says
>>
>> ->  Index Scan using event_pkey on event t1  (cost=0.56..0.95 rows=1
>> width=295) (actual time=0.008..0.008 rows=0 loops=229227)

--- snip ---

> Can you confirm what: SELECT pg_relation_size('event_pkey'),
> pg_relation_size('event'); says on each

1011384320 and 2753077248, respectively.

--- snip ---

> It would be good to see:
>
> SET track_io_timing = on;
> EXPLAIN (ANALYZE, BUFFERS) <the query>

I wasn't aware of that tracing option - thanks! For this particular plan
entry the output is

  Buffers: shared hit=896304 read=257234
  I/O Timings: read=11426.745

Some rows have been added to the table since my initial mail, so the
numbers may be slightly off.

As another reply has suggested I need to verify that somebody hasn't
accidentally misconfigured an antivirus client to scan the database
files. If that turns out to be the case I guess it's embarrassment of
the year for me :-/

Best regards,
   Mikkel Lauritsen