Thread: 20% performance drop on PostgreSQL 9.2 from kernel 3.5.3 to 3.6-rc5 on AMD chipsets

20% performance drop on PostgreSQL 9.2 from kernel 3.5.3 to 3.6-rc5 on AMD chipsets

From

Nikolay Ulyanitsky

Date:

14 September 2012, 10:40:34

Hi
I compiled the 3.6-rc5 kernel with the same config from 3.5.3 and got
the 15-20% performance drop of PostgreSQL 9.2 on AMD chipsets (880G,
990X).

CentOS 6.3 x86_64
PostgreSQL 9.2
cpufreq scaling_governor - performance

# /etc/init.d/postgresql initdb
# echo "fsync = off" >> /var/lib/pgsql/data/postgresql.conf
# /etc/init.d/postgresql start
# su - postgres
$ psql
# create database pgbench;
# \q

# pgbench -i pgbench && pgbench -c 10 -t 10000 pgbench
tps = 4670.635648 (including connections establishing)
tps = 4673.630345 (excluding connections establishing)[/code]

On kernel 3.5.3:
tps = ~5800

1) Host 1 - 15-20% performance drop
AMD Phenom(tm) II X6 1090T Processor
MB: AMD 880G
RAM: 16 Gb DDR3
SSD: PLEXTOR PX-256M3 256Gb

2) Host 2 - 15-20% performance drop
AMD Phenom(tm) II X6 1055T Processor
MB: AMD 990X
RAM: 32 Gb DDR3
SSD: Corsair Performance Pro 128Gb

3) Host 3 - no problems - same performance
Intel E6300
MB: Intel® P43 / ICH10
RAM: 4 Gb DDR3
HDD: SATA 7200 rpm

Kernel config - http://pastebin.com/cFpg5JSJ

Any ideas?

Thx

Re: 20% performance drop on PostgreSQL 9.2 from kernel 3.5.3 to 3.6-rc5 on AMD chipsets

From

Daniel Farina

Date:

14 September 2012, 11:46:18

On Fri, Sep 14, 2012 at 12:40 AM, Nikolay Ulyanitsky <lystor@gmail.com> wrote:
> Hi
> I compiled the 3.6-rc5 kernel with the same config from 3.5.3 and got
> the 15-20% performance drop of PostgreSQL 9.2 on AMD chipsets (880G,
> 990X).
>
> CentOS 6.3 x86_64
> PostgreSQL 9.2
> cpufreq scaling_governor - performance
>
> # /etc/init.d/postgresql initdb
> # echo "fsync = off" >> /var/lib/pgsql/data/postgresql.conf
> # /etc/init.d/postgresql start
> # su - postgres
> $ psql
> # create database pgbench;
> # \q
>
> # pgbench -i pgbench && pgbench -c 10 -t 10000 pgbench
> tps = 4670.635648 (including connections establishing)
> tps = 4673.630345 (excluding connections establishing)[/code]
>
> On kernel 3.5.3:
> tps = ~5800
>
> 1) Host 1 - 15-20% performance drop
> AMD Phenom(tm) II X6 1090T Processor
> MB: AMD 880G
> RAM: 16 Gb DDR3
> SSD: PLEXTOR PX-256M3 256Gb
>
> 2) Host 2 - 15-20% performance drop
> AMD Phenom(tm) II X6 1055T Processor
> MB: AMD 990X
> RAM: 32 Gb DDR3
> SSD: Corsair Performance Pro 128Gb
>
> 3) Host 3 - no problems - same performance
> Intel E6300
> MB: Intel® P43 / ICH10
> RAM: 4 Gb DDR3
> HDD: SATA 7200 rpm
>
> Kernel config - http://pastebin.com/cFpg5JSJ
>
> Any ideas?

Did you tell LKML? It seems like a kind of change that could be found
using git bisect of Linux, albiet laboriously.

--
fdr

Re: 20% performance drop on PostgreSQL 9.2 from kernel 3.5.3 to 3.6-rc5 on AMD chipsets

From

Marcos Ortiz

Date:

14 September 2012, 17:57:04

Regards, Nikolay.
Like Daniel said to you, I encourage to inform all your findings to the LKML to
report all these problems.

Only one las t question: Did you tune the postgresql.conf for every system? or
Did you use the default configuration ?

Best wishes

On 09/14/2012 04:45 AM, Daniel Farina wrote:

On Fri, Sep 14, 2012 at 12:40 AM, Nikolay Ulyanitsky <lystor@gmail.com> wrote:

Hi
I compiled the 3.6-rc5 kernel with the same config from 3.5.3 and got
the 15-20% performance drop of PostgreSQL 9.2 on AMD chipsets (880G,
990X).

CentOS 6.3 x86_64
PostgreSQL 9.2
cpufreq scaling_governor - performance

# /etc/init.d/postgresql initdb
# echo "fsync = off" >> /var/lib/pgsql/data/postgresql.conf
# /etc/init.d/postgresql start
# su - postgres
$ psql
# create database pgbench;
# \q

# pgbench -i pgbench && pgbench -c 10 -t 10000 pgbench
tps = 4670.635648 (including connections establishing)
tps = 4673.630345 (excluding connections establishing)[/code]

On kernel 3.5.3:
tps = ~5800

1) Host 1 - 15-20% performance drop
AMD Phenom(tm) II X6 1090T Processor
MB: AMD 880G
RAM: 16 Gb DDR3
SSD: PLEXTOR PX-256M3 256Gb

2) Host 2 - 15-20% performance drop
AMD Phenom(tm) II X6 1055T Processor
MB: AMD 990X
RAM: 32 Gb DDR3
SSD: Corsair Performance Pro 128Gb

3) Host 3 - no problems - same performance
Intel E6300
MB: Intel® P43 / ICH10
RAM: 4 Gb DDR3
HDD: SATA 7200 rpm

Kernel config - http://pastebin.com/cFpg5JSJ

Any ideas?

Did you tell LKML? It seems like a kind of change that could be found
using git bisect of Linux, albiet laboriously.

Marcos Luis Ortíz Valmaseda
Data Engineer && Sr. System Administrator at UCI
about.me/marcosortiz
My Blog
Tumblr's blog
@marcosluis2186

Re: 20% performance drop on PostgreSQL 9.2 from kernel 3.5.3 to 3.6-rc5 on AMD chipsets

From

Nikolay Ulyanitsky

Date:

14 September 2012, 18:04:16

On 14 September 2012 11:45, Daniel Farina <daniel@heroku.com> wrote:

> Did you tell LKML? It seems like a kind of change that could be found

> using git bisect of Linux, albiet laboriously.

Hi, Daniel

I sent it to linux-kernel@vger.kernel.org on Fri, 14 Sep 2012 10:47:44 +0300.

On 14 September 2012 17:56, Marcos Ortiz <mlortiz@uci.cu> wrote:

> Only one las t question: Did you tune the postgresql.conf for every system? or

> Did you use the default configuration ?

Hi, Marcos

I have the issue with default and tuned configuration on all AMD systems:

Tuned config for 32Gb RAM:

#------------------------------------------------------------------------------

# Connection Settings -

#------------------------------------------------------------------------------

listen_addresses = '*'

port = 5432

max_connections = 50

#------------------------------------------------------------------------------

# OPTIMIZATIONS

#------------------------------------------------------------------------------

shared_buffers = 7680MB

effective_cache_size = 22GB

work_mem = 576MB

maintenance_work_mem = 2GB

wal_buffers = 16MB

fsync = off

synchronous_commit = off

#------------------------------------------------------------------------------

# PLANNER

#------------------------------------------------------------------------------

default_statistics_target = 100

constraint_exclusion = off

#------------------------------------------------------------------------------

# CHECKPOINTS

#------------------------------------------------------------------------------

checkpoint_timeout = 5min

checkpoint_segments = 16

checkpoint_completion_target = 0.9

#------------------------------------------------------------------------------

# AUTOVACUUM PARAMETERS

#------------------------------------------------------------------------------

autovacuum = on

autovacuum_naptime = 5min

autovacuum_max_workers = 1

autovacuum_vacuum_scale_factor = 0.0001

autovacuum_analyze_scale_factor = 0.0001

autovacuum_vacuum_threshold = 100

autovacuum_analyze_threshold = 100

autovacuum_vacuum_cost_delay = 1ms

#------------------------------------------------------------------------------

# ERROR REPORTING AND LOGGING

#------------------------------------------------------------------------------

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%a.log'

log_truncate_on_rotation = on

log_rotation_age = 1d

log_rotation_size = 0

log_line_prefix = '%t %d %u '

Re: 20% performance drop on PostgreSQL 9.2 from kernel 3.5.3 to 3.6-rc5 on AMD chipsets

From

Craig James

Date:

14 September 2012, 18:29:06

On Fri, Sep 14, 2012 at 12:40 AM, Nikolay Ulyanitsky <lystor@gmail.com> wrote:

Hi
I compiled the 3.6-rc5 kernel with the same config from 3.5.3 and got
the 15-20% performance drop of PostgreSQL 9.2 on AMD chipsets (880G,
990X).

Did you compile the AMD code on the AMD system?

We use a different open-source project that provides chemistry functionality, and discovered the hard way that the code optimizer is specific to each chip. Code compiled on Intel chips would sometimes run 50% slower on AMD chips (and vice versa). When we compiled the Intel code using Intel computers and AMD code using AMD computers, the performance difference disappeared.

There's probably an optimizer flag somewhere that would allow you to force it to compile for one chip or the other, but by default it seems to pick the one you're running on.

Craig

CentOS 6.3 x86_64
PostgreSQL 9.2
cpufreq scaling_governor - performance

# /etc/init.d/postgresql initdb
# echo "fsync = off" >> /var/lib/pgsql/data/postgresql.conf
# /etc/init.d/postgresql start
# su - postgres
$ psql
# create database pgbench;
# \q

# pgbench -i pgbench && pgbench -c 10 -t 10000 pgbench
tps = 4670.635648 (including connections establishing)
tps = 4673.630345 (excluding connections establishing)[/code]

On kernel 3.5.3:
tps = ~5800

1) Host 1 - 15-20% performance drop
AMD Phenom(tm) II X6 1090T Processor
MB: AMD 880G
RAM: 16 Gb DDR3
SSD: PLEXTOR PX-256M3 256Gb

2) Host 2 - 15-20% performance drop
AMD Phenom(tm) II X6 1055T Processor
MB: AMD 990X
RAM: 32 Gb DDR3
SSD: Corsair Performance Pro 128Gb

3) Host 3 - no problems - same performance
Intel E6300
MB: Intel® P43 / ICH10
RAM: 4 Gb DDR3
HDD: SATA 7200 rpm

Kernel config - http://pastebin.com/cFpg5JSJ

Any ideas?

Thx

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: 20% performance drop on PostgreSQL 9.2 from kernel 3.5.3 to 3.6-rc5 on AMD chipsets

From

Nikolay Ulyanitsky

Date:

14 September 2012, 18:35:22

Hi, Craig

On 14 September 2012 18:29, Craig James <cjames@emolecules.com> wrote:
> Did you compile the AMD code on the AMD system?

Yes
And it is optimized for Generic-x86-64 (CONFIG_GENERIC_CPU).

Re: 20% performance drop on PostgreSQL 9.2 from kernel 3.5.3 to 3.6-rc5 on AMD chipsets

From

Andrea Suisani

Date:

18 September 2012, 10:44:19

On 09/14/2012 10:45 AM, Daniel Farina wrote:
> On Fri, Sep 14, 2012 at 12:40 AM, Nikolay Ulyanitsky <lystor@gmail.com> wrote:
>> Hi
>> I compiled the 3.6-rc5 kernel with the same config from 3.5.3 and got
>> the 15-20% performance drop of PostgreSQL 9.2 on AMD chipsets (880G,
>> 990X).

[cut]

>> Kernel config - http://pastebin.com/cFpg5JSJ
>>
>> Any ideas?
>
> Did you tell LKML? It seems like a kind of change that could be found
> using git bisect of Linux, albiet laboriously.


just a pointer to LKML thread:

https://lkml.org/lkml/2012/9/14/99

it seems that kernel dev were able to find the root cause
after bisecting kernel source.

Borislav Petkov says that regression disappears
after reverting this commit:

commit 970e178985cadbca660feb02f4d2ee3a09f7fdda
Author: Mike Galbraith <efault@gmx.de>
Date:   Tue Jun 12 05:18:32 2012 +0200



Andrea

Re: 20% performance drop on PostgreSQL 9.2 from kernel 3.5.3 to 3.6-rc5 on AMD chipsets

From

Merlin Moncure

Date:

18 September 2012, 16:54:40

On Tue, Sep 18, 2012 at 2:44 AM, Andrea Suisani <sickpig@opinioni.net> wrote:
> On 09/14/2012 10:45 AM, Daniel Farina wrote:
>>
>> On Fri, Sep 14, 2012 at 12:40 AM, Nikolay Ulyanitsky <lystor@gmail.com>
>> wrote:
>>>
>>> Hi
>>> I compiled the 3.6-rc5 kernel with the same config from 3.5.3 and got
>>> the 15-20% performance drop of PostgreSQL 9.2 on AMD chipsets (880G,
>>> 990X).
>
>
> [cut]
>
>
>>> Kernel config - http://pastebin.com/cFpg5JSJ
>>>
>>> Any ideas?
>>
>>
>> Did you tell LKML? It seems like a kind of change that could be found
>> using git bisect of Linux, albiet laboriously.
>
>
>
> just a pointer to LKML thread:
>
> https://lkml.org/lkml/2012/9/14/99

There's some interesting discussion of postgres spinlocks in the thread:

"Yes, postgress performs loads better with it's spinlocks, but due to
that, it necessarily _hates_ preemption."

merlin

Re: 20% performance drop on PostgreSQL 9.2 from kernel 3.5.3 to 3.6-rc5 on AMD chipsets

From

Andrea Suisani

Date:

18 September 2012, 17:25:39

[cut]

>>>> Kernel config - http://pastebin.com/cFpg5JSJ
>>>>
>>>> Any ideas?
>>>
>>>
>>> Did you tell LKML? It seems like a kind of change that could be found
>>> using git bisect of Linux, albiet laboriously.
>>
>> just a pointer to LKML thread:
>>
>> https://lkml.org/lkml/2012/9/14/99
>
> There's some interesting discussion of postgres spinlocks in the thread:
>
> "Yes, postgress performs loads better with it's spinlocks, but due to
> that, it necessarily _hates_ preemption."

another one:

https://lkml.org/lkml/2012/9/15/39

quoting the relevant piece:

On Sat, Sep 15, 2012 at 06:11:02AM +0200, Mike Galbraith wrote:
 > My wild (and only) theory is that this is userspace spinlock related.
 > If so, starting the server and benchmark SCHED_BATCH should not only
 > kill the regression, but likely improve throughput as well.

after that message Borislav Petkov tried to
to exactly that using schedtool(8) (tool to query and set CPU
scheduling parameters)

and he can confirm that performace are "even better than the results with 3.5
(had something around 3900ish on that particular configuration)."


Andrea

Re: 20% performance drop on PostgreSQL 9.2 from kernel 3.5.3 to 3.6-rc5 on AMD chipsets

From

Andrea Suisani

Date:

03 October 2012, 14:46:03

Hi

On 09/18/2012 09:44 AM, Andrea Suisani wrote:
> On 09/14/2012 10:45 AM, Daniel Farina wrote:
>> On Fri, Sep 14, 2012 at 12:40 AM, Nikolay Ulyanitsky <lystor@gmail.com> wrote:
>>> Hi
>>> I compiled the 3.6-rc5 kernel with the same config from 3.5.3 and got
>>> the 15-20% performance drop of PostgreSQL 9.2 on AMD chipsets (880G,
>>> 990X).
>
> [cut]
>
>>> Kernel config - http://pastebin.com/cFpg5JSJ
>>>
>>> Any ideas?
>>
>> Did you tell LKML? It seems like a kind of change that could be found
>> using git bisect of Linux, albiet laboriously.
>
>
> just a pointer to LKML thread:
>
> https://lkml.org/lkml/2012/9/14/99

[cut]

today Jonathan Corbet has posted a good write-up on lwn.net

"How 3.6 nearly broke PostgreSQL"

http://lwn.net/SubscriberLink/518329/672d5c68286f9c18/

this is definitely a worth reading piece.

Andrea