CentOS - PostgreSQL 9.2.13 -> 9.4

On 08/18/2015 08:01 AM, Michael H wrote:

Hi,

I've been tuning our new database server, here's some info...

CentOS Linux release 7.1.1503 (Core)
3.10.0-229.11.1.el7.x86_64

8 x 16GB 1600MHz PC3-12800 DDR3 - 128GB total
2 x AMD Opteron 6386SE 2.8GHz/16-core/140w - 32 cores total
4 x 300GB SAS 10k HDD - raid 1+0 configuration
1GB FBWC for P-series smart array - cache enabled

I praised 9.4 for the new features and now I'm cursing it!

any info would be appreciated,

Without the respective postgresql.conf files, none of this means anything.

Sincerely,

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.

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

Here are the only things that I amended, all other settings are defaults.

maintenance_work_mem=2GB
checkpoint_segments=64
wal_keep_segments=128
max_prepared_transactions=10
max_wal_senders=3
wal_level=hot_standby
max_files_per_process=1000000
max_stack_depth=7MB
wal_buffers=64MB
shared_buffers=60GB
work_mem64MB

Other settings were changed and reverted again due to performance decreases.

I also dropped shared_buffers down to 32GB, TPS dropped further.

On 18/08/15 16:12, Joshua D. Drake wrote:

On 08/18/2015 08:01 AM, Michael H wrote:

Hi,

I've been tuning our new database server, here's some info...

CentOS Linux release 7.1.1503 (Core)
3.10.0-229.11.1.el7.x86_64

8 x 16GB 1600MHz PC3-12800 DDR3 - 128GB total
2 x AMD Opteron 6386SE 2.8GHz/16-core/140w - 32 cores total
4 x 300GB SAS 10k HDD - raid 1+0 configuration
1GB FBWC for P-series smart array - cache enabled

I praised 9.4 for the new features and now I'm cursing it!

any info would be appreciated,

Without the respective postgresql.conf files, none of this means anything.

Sincerely,

JD

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

On Tue, Aug 18, 2015 at 8:01 AM, Michael H <michael@wemoto.com> wrote:

Hi,

I've been tuning our new database server, here's some info...

CentOS Linux release 7.1.1503 (Core)
3.10.0-229.11.1.el7.x86_64

8 x 16GB 1600MHz PC3-12800 DDR3 - 128GB total
2 x AMD Opteron 6386SE 2.8GHz/16-core/140w - 32 cores total
4 x 300GB SAS 10k HDD - raid 1+0 configuration
1GB FBWC for P-series smart array - cache enabled

I'm using the CentOS provided packages for PostgreSQL
Version : 9.2.13
Release : 1.el7_1

I'm getting fairly good statistics from this server but after asking for
some advice I was pointed towards PostgreSQL 9.3 (posix memory management)
and PostgreSQL 9.4 (pg_replication_slots).

I dropped my original install of 9.2.13 above and went straight to the 9.4
from the PostgreSQL repositories.

How did you get your data from 9.2 to 9.4? Did you run ANALYZE on it
afterwards?

Are there any known issues with my kernel and PostgreSQL? I found this
post -

http://www.databasesoup.com/2014/09/why-you-need-to-avoid-linux-kernel-32.html

which states there are known issues up to kernel 3.10.. the reason I ask,
no matter how small or big a configuration change I make I can't match my
9.2.13 install. I'm seeing huge decreases in TPS on all my benchmarks.

for example, 9.2.13, my own extremely heavy SQL file being used here,
hence the lower TPS...

32 37.357197
64 34.145088
128 19.682544
256 9.910772
512 5.803358

compared to 9.4 - exactly the same tests and parameters configured (I also
started from defaults and tuned up as best I could).

32 14.982111
64 14.894859
128 14.277631
256 13.679516
512 13.679516

Pick the query that dropped in performance the most, then run it with
"explain (analyze, buffers)" and with track_io_timing turned on, and
compare this between the servers. Did the plan change, or just the time?

Cheers,

Jeff

8 x 16GB 1600MHz PC3-12800 DDR3 - 128GB total

shared_buffers=60GB

I would say 60GB is too high when you have 128GB system memory.
Try lowering it to shared_buffers=32GB and let the O/S handle more of the
work.

On Tue, Aug 18, 2015 at 11:49 AM, Jeff Janes <jeff.janes@gmail.com> wrote:

On Tue, Aug 18, 2015 at 8:01 AM, Michael H <michael@wemoto.com> wrote:

Hi,

I've been tuning our new database server, here's some info...

CentOS Linux release 7.1.1503 (Core)
3.10.0-229.11.1.el7.x86_64

8 x 16GB 1600MHz PC3-12800 DDR3 - 128GB total
2 x AMD Opteron 6386SE 2.8GHz/16-core/140w - 32 cores total
4 x 300GB SAS 10k HDD - raid 1+0 configuration
1GB FBWC for P-series smart array - cache enabled

I'm using the CentOS provided packages for PostgreSQL
Version : 9.2.13
Release : 1.el7_1

I'm getting fairly good statistics from this server but after asking for
some advice I was pointed towards PostgreSQL 9.3 (posix memory management)
and PostgreSQL 9.4 (pg_replication_slots).

I dropped my original install of 9.2.13 above and went straight to the
9.4 from the PostgreSQL repositories.

How did you get your data from 9.2 to 9.4? Did you run ANALYZE on it
afterwards?

Are there any known issues with my kernel and PostgreSQL? I found this
post -

http://www.databasesoup.com/2014/09/why-you-need-to-avoid-linux-kernel-32.html

which states there are known issues up to kernel 3.10.. the reason I ask,
no matter how small or big a configuration change I make I can't match my
9.2.13 install. I'm seeing huge decreases in TPS on all my benchmarks.

for example, 9.2.13, my own extremely heavy SQL file being used here,
hence the lower TPS...

32 37.357197
64 34.145088
128 19.682544
256 9.910772
512 5.803358

compared to 9.4 - exactly the same tests and parameters configured (I
also started from defaults and tuned up as best I could).

32 14.982111
64 14.894859
128 14.277631
256 13.679516
512 13.679516

Pick the query that dropped in performance the most, then run it with
"explain (analyze, buffers)" and with track_io_timing turned on, and
compare this between the servers. Did the plan change, or just the time?

Cheers,

Jeff

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On 08/18/2015 09:19 AM, Melvin Davidson wrote:

8 x 16GB 1600MHz PC3-12800 DDR3 - 128GB total

shared_buffers=60GB

I would say 60GB is too high when you have 128GB system memory.
Try lowering it to shared_buffers=32GB and let the O/S handle more of
the work.

I would also look at see if you are checkpointing either via segment
rollover or time.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.

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

Joshua D. Drake wrote:

On 08/18/2015 09:19 AM, Melvin Davidson wrote:

8 x 16GB 1600MHz PC3-12800 DDR3 - 128GB total

shared_buffers=60GB

I would say 60GB is too high when you have 128GB system memory.
Try lowering it to shared_buffers=32GB and let the O/S handle more of
the work.

I would also look at see if you are checkpointing either via segment
rollover or time.

If it works fine under 9.2, why would it be a problem in 9.3 and 9.4?
Keep in mind the question is not "let's make this as fast as possible"
but rather "let's make this on par with 9.2".

One thing to look at is the rate of WAL generation for a set number of
transactions. Maybe the later releases are generating more WAL due to
multixacts, for instance (prior to 9.3 these weren't wal-logged.)

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

Alvaro Herrera wrote:

One thing to look at is the rate of WAL generation for a set number of
transactions. Maybe the later releases are generating more WAL due to
multixacts, for instance (prior to 9.3 these weren't wal-logged.)

Also try 9.5alpha2, wherein bug #8470 is fixed, which is a big
performance regression in 9.3 and 9.4.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

On 08/18/2015 09:41 AM, Alvaro Herrera wrote:

Alvaro Herrera wrote:

One thing to look at is the rate of WAL generation for a set number of
transactions. Maybe the later releases are generating more WAL due to
multixacts, for instance (prior to 9.3 these weren't wal-logged.)

Also try 9.5alpha2, wherein bug #8470 is fixed, which is a big
performance regression in 9.3 and 9.4.

Is there a reason we didn't backport this? (A point to a thread is just
fine).

jD

--
Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.

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

Joshua D. Drake wrote:

On 08/18/2015 09:41 AM, Alvaro Herrera wrote:

Alvaro Herrera wrote:

One thing to look at is the rate of WAL generation for a set number of
transactions. Maybe the later releases are generating more WAL due to
multixacts, for instance (prior to 9.3 these weren't wal-logged.)

Also try 9.5alpha2, wherein bug #8470 is fixed, which is a big
performance regression in 9.3 and 9.4.

Is there a reason we didn't backport this? (A point to a thread is just
fine).

There's a thread somewhere, yeah, but the meat of it is that there were
other fixes for multixacts going in at the same time and we didn't want
people to be unable to upgrade to a version with those fixes if it later
turned out that the fix for #8470 had bugs.

Some time later, I remembered that I had developed two fixes for #8470,
one of which was cleaner but more invasive, and got pushed to 9.5;
another fix was much more self-contained but uglier and perhaps we could
consider it for 9.3 and 9.4. I'm out of resources for further multixact
fixes myself (therefore I'm very grateful to Andres, Thomas and Robert
who shouldered the burden of the last round of fixes), so I didn't
pursue this. If anyone else wants to pursue it, I can provide the
patch, which evolved somewhat from the last version of it I posted.

(Then again, the last time I asked for testing help on the topic of
#8470 nobody volunteered even though several people said they were
affected by the bug. Go figure.)

Cheers

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

I wrote:

One thing to look at is the rate of WAL generation for a set number of
transactions. Maybe the later releases are generating more WAL due to
multixacts, for instance (prior to 9.3 these weren't wal-logged.)

FWIW a very easy way to measure this is to look at the output of
"pg_xlogdump -r MultiXact" for the WAL generated during the testing
period.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

Hi Melvin,

On 18/08/15 17:19, Melvin Davidson wrote:

8 x 16GB 1600MHz PC3-12800 DDR3 - 128GB total

shared_buffers=60GB

I would say 60GB is too high when you have 128GB system memory.
Try lowering it to shared_buffers=32GB and let the O/S handle more of
the work.

I have tested all different shared_buffers settings across both
versions, from 8GB - 60GB. 8-32GB were optimal. in reality the
difference from 8 - 32 was minimal.

On Tue, Aug 18, 2015 at 11:49 AM, Jeff Janes <jeff.janes@gmail.com
<mailto:jeff.janes@gmail.com>> wrote:

On Tue, Aug 18, 2015 at 8:01 AM, Michael H <michael@wemoto.com
<mailto:michael@wemoto.com>> wrote:

Hi,

I've been tuning our new database server, here's some info...

CentOS Linux release 7.1.1503 (Core)
3.10.0-229.11.1.el7.x86_64

8 x 16GB 1600MHz PC3-12800 DDR3 - 128GB total
2 x AMD Opteron 6386SE 2.8GHz/16-core/140w - 32 cores total
4 x 300GB SAS 10k HDD - raid 1+0
configuration
1GB FBWC for P-series smart array - cache enabled

I'm using the CentOS provided packages for PostgreSQL
Version : 9.2.13
Release : 1.el7_1

I'm getting fairly good statistics from this server but after
asking for some advice I was pointed towards PostgreSQL 9.3
(posix memory management) and PostgreSQL 9.4 (pg_replication_slots).

I dropped my original install of 9.2.13 above and went straight
to the 9.4 from the PostgreSQL repositories.

How did you get your data from 9.2 to 9.4? Did you run ANALYZE on
it afterwards?

Are there any known issues with my kernel and PostgreSQL? I
found this post -
http://www.databasesoup.com/2014/09/why-you-need-to-avoid-linux-kernel-32.html

which states there are known issues up to kernel 3.10.. the
reason I ask, no matter how small or big a configuration change
I make I can't match my 9.2.13 install. I'm seeing huge
decreases in TPS on all my benchmarks.

for example, 9.2.13, my own extremely heavy SQL file being used
here, hence the lower TPS...

32 37.357197
64 34.145088
128 19.682544
256 9.910772
512 5.803358

compared to 9.4 - exactly the same tests and parameters
configured (I also started from defaults and tuned up as best I
could).

32 14.982111
64 14.894859
128 14.277631
256 13.679516
512 13.679516

Pick the query that dropped in performance the most, then run it
with "explain (analyze, buffers)" and with track_io_timing turned
on, and compare this between the servers. Did the plan change, or
just the time?

Cheers,

Jeff

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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

Hi Alvaro,

On 18/08/15 17:39, Alvaro Herrera wrote:

Joshua D. Drake wrote:

On 08/18/2015 09:19 AM, Melvin Davidson wrote:

8 x 16GB 1600MHz PC3-12800 DDR3 - 128GB total

shared_buffers=60GB

I would say 60GB is too high when you have 128GB system memory.
Try lowering it to shared_buffers=32GB and let the O/S handle more of
the work.

I would also look at see if you are checkpointing either via segment
rollover or time.

If it works fine under 9.2, why would it be a problem in 9.3 and 9.4?
Keep in mind the question is not "let's make this as fast as possible"
but rather "let's make this on par with 9.2".

My aim was simply to get 9.4 to match 9.2.13, I spent about 5 days
tweaking and re-testing many different configurations, When I initially
installed 9.2.13 (building a replacement server for our current 9.1
setup) I made two configuration changes (shared_buffers and work_mem)
and the stats were smashing 9.1.
I think I just expected the same kind of performance increase (or
atleast the same performance) when I went to 9.4.

I'm not expecting a 20% increase against 9.2.13, but I do expect to see
the same increase against 9.1.

Thanks

Michael

One thing to look at is the rate of WAL generation for a set number of
transactions. Maybe the later releases are generating more WAL due to
multixacts, for instance (prior to 9.3 these weren't wal-logged.)

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

Hi Alvaro,

On 18/08/15 17:41, Alvaro Herrera wrote:

Alvaro Herrera wrote:

One thing to look at is the rate of WAL generation for a set number of
transactions. Maybe the later releases are generating more WAL due to
multixacts, for instance (prior to 9.3 these weren't wal-logged.)

Also try 9.5alpha2, wherein bug #8470 is fixed, which is a big
performance regression in 9.3 and 9.4.

Unfortunately I can't get into alpha releases, nor beta. This server
will go into production in around 7 days time.

I had a month to get this dealt with originally, two weeks were spent
tweaking 9.2.13 (provided from CentOS base) with the intention of
staying with that version.

Then I wanted to see better TPS on high client numbers and moved to 9.4,
even this took a little persuasion with my colleague... 9.5 is
definitely a no.

Thanks

Michael

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

Hi Joshua,

On 18/08/15 16:12, Joshua D. Drake wrote:

On 08/18/2015 08:01 AM, Michael H wrote:

Hi,

I've been tuning our new database server, here's some info...

CentOS Linux release 7.1.1503 (Core)
3.10.0-229.11.1.el7.x86_64

8 x 16GB 1600MHz PC3-12800 DDR3 - 128GB total
2 x AMD Opteron 6386SE 2.8GHz/16-core/140w - 32 cores total
4 x 300GB SAS 10k HDD - raid 1+0 configuration
1GB FBWC for P-series smart array - cache enabled

I praised 9.4 for the new features and now I'm cursing it!

any info would be appreciated,

Without the respective postgresql.conf files, none of this means anything.

I think my main question was, are there any known issues with kernel
3.10 and postgresql 9.4? I've tried so many configuration changes that
I'm starting to look at other underlying issues that may be present.

Does anybody know if anything in this post relevant now?
http://www.databasesoup.com/2014/09/why-you-need-to-avoid-linux-kernel-32.html

Sincerely,

JD

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

A couple more links here about issues with kernel 3.10

http://www.databasesoup.com/2014/09/why-you-need-to-avoid-linux-kernel-32.html

/messages/by-id/20150203.174637.1316840640181577524.t-ishii@sraoss.co.jp

#the latter parts of this post get into the gritty of kernel 3.10 3.13 etc.

/messages/by-id/CADCcZRk_PY-EPJzAA0jirNa7W69GtWk1OhqT0on==oEEeS8U=Q@mail.gmail.com

So could my issue be that I'm using a bad kernel for postgresql-9.4???

Michael

On 19/08/15 10:00, Michael H wrote:

CentOS Linux release 7.1.1503 (Core)
3.10.0-229.11.1.el7.x86_64

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