Re: Tuning the configuration

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello.
I need to tune a postgres installation I've just made to get a better
performance. I use two identical servers with a hot replication
configuration. The two servers have the following hardware:

Dual Processor Intel Xeon E5-2640V2 20Mb cache 2.00Ghz,
Ram Mem. 32Gb DDR-3 Ecc Registered,
Controller MegaRaid 8-ports 1Gb cache,
4 Enterprise Hdd NL Sas 600 4Tb Sata,
2 Samsung SSD 840 Pro Series 512Gb,
2 Hdd 500 Gb

I made a software raid with the last two hard disks with ext4 and I
installed Ubuntu 14.04.1 LTS (I have to use this SO) on it. I made a
hardware raid with the four SAS hard disks and I mount the partition
on it with ext4 without journaling and I put the database on it.

Now I have two more steps to do.

1- could you please help tuning the configuration? What are the best
value I should use for wal_buffers and shared_buffers?
2- I would like to use the two SDD to store the wal file. Do you think
it is useful or how should I use them?

Thank you for your answers.

Best Regards,
Maila Fatticcioni
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iEYEARECAAYFAlSII/gACgkQi2q3wPb3FcPUuACgg2m2o9dQWavLrN2EmmmCpGEv
YnMAoN0R/gejcKwnxf0qFPKXtaGaIG1A
=oLxU
-----END PGP SIGNATURE-----

On Wed, Dec 10, 2014 at 2:44 AM, Maila Fatticcioni
<mfatticcioni(at)mbigroup(dot)it> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello.
> I need to tune a postgres installation I've just made to get a better
> performance. I use two identical servers with a hot replication
> configuration. The two servers have the following hardware:
>
> Dual Processor Intel Xeon E5-2640V2 20Mb cache 2.00Ghz,
> Ram Mem. 32Gb DDR-3 Ecc Registered,
> Controller MegaRaid 8-ports 1Gb cache,
> 4 Enterprise Hdd NL Sas 600 4Tb Sata,
> 2 Samsung SSD 840 Pro Series 512Gb,
> 2 Hdd 500 Gb
>
> I made a software raid with the last two hard disks with ext4 and I
> installed Ubuntu 14.04.1 LTS (I have to use this SO) on it. I made a
> hardware raid with the four SAS hard disks and I mount the partition
> on it with ext4 without journaling and I put the database on it.
>
> Now I have two more steps to do.
>
> 1- could you please help tuning the configuration? What are the best
> value I should use for wal_buffers and shared_buffers?
> 2- I would like to use the two SDD to store the wal file. Do you think
> it is useful or how should I use them?
>
> Thank you for your answers.
>
> Best Regards,
> Maila Fatticcioni
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1
>
> iEYEARECAAYFAlSII/gACgkQi2q3wPb3FcPUuACgg2m2o9dQWavLrN2EmmmCpGEv
> YnMAoN0R/gejcKwnxf0qFPKXtaGaIG1A
> =oLxU
> -----END PGP SIGNATURE-----
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

We used [1] to great effect when setting our server up. We have not
had to diverge much from the recommendations in that document.

Generally, the specifics of tuning depend on the workload of your
specific instance.

[1] https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 12/10/2014 06:47 PM, Patrick Krecker wrote:
> On Wed, Dec 10, 2014 at 2:44 AM, Maila Fatticcioni
> <mfatticcioni(at)mbigroup(dot)it> wrote: Hello. I need to tune a postgres
> installation I've just made to get a better performance. I use two
> identical servers with a hot replication configuration. The two
> servers have the following hardware:
>
> Dual Processor Intel Xeon E5-2640V2 20Mb cache 2.00Ghz, Ram Mem.
> 32Gb DDR-3 Ecc Registered, Controller MegaRaid 8-ports 1Gb cache, 4
> Enterprise Hdd NL Sas 600 4Tb Sata, 2 Samsung SSD 840 Pro Series
> 512Gb, 2 Hdd 500 Gb
>
> I made a software raid with the last two hard disks with ext4 and
> I installed Ubuntu 14.04.1 LTS (I have to use this SO) on it. I
> made a hardware raid with the four SAS hard disks and I mount the
> partition on it with ext4 without journaling and I put the database
> on it.
>
> Now I have two more steps to do.
>
> 1- could you please help tuning the configuration? What are the
> best value I should use for wal_buffers and shared_buffers? 2- I
> would like to use the two SDD to store the wal file. Do you think
> it is useful or how should I use them?
>
> Thank you for your answers.
>
> Best Regards, Maila Fatticcioni
>>
>>
>> -- Sent via pgsql-performance mailing list
>> (pgsql-performance(at)postgresql(dot)org) To make changes to your
>> subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>
> We used [1] to great effect when setting our server up. We have
> not had to diverge much from the recommendations in that document.
>
> Generally, the specifics of tuning depend on the workload of your
> specific instance.
>
> [1] https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
>

Hello.
Indeed I followed this document to set up my configuration. I am glad
that you recommend this as well.

Eventually I use this setup:

max_connections = 150
shared_buffers = 8GB
work_mem = 32MB
checkpoint_segments = 128
checkpoint_completion_target = 0.9

Best Regards,
Maila Fatticcioni
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iEYEARECAAYFAlSJUaEACgkQi2q3wPb3FcPsuQCeLR5P49d60anErETNiX0iHNLe
Eu4An0QN3nzr/kvlPUTm9Q1A0GkjB/gw
=kdGU
-----END PGP SIGNATURE-----

On 12/10/2014 11:44 AM, Maila Fatticcioni wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello.
> I need to tune a postgres installation I've just made to get a better
> performance. I use two identical servers with a hot replication
> configuration. The two servers have the following hardware:
>
> Dual Processor Intel Xeon E5-2640V2 20Mb cache 2.00Ghz,
> Ram Mem. 32Gb DDR-3 Ecc Registered,
> Controller MegaRaid 8-ports 1Gb cache,
> 4 Enterprise Hdd NL Sas 600 4Tb Sata,
> 2 Samsung SSD 840 Pro Series 512Gb,
> 2 Hdd 500 Gb
>
> I made a software raid with the last two hard disks with ext4 and I
> installed Ubuntu 14.04.1 LTS (I have to use this SO) on it. I made a
> hardware raid with the four SAS hard disks and I mount the partition
> on it with ext4 without journaling and I put the database on it.

Leaving aside all the valid points Patrick already made, as of late I've found
xfs a better choice for Postgres, performance wise.

> Now I have two more steps to do.
>
> 1- could you please help tuning the configuration? What are the best
> value I should use for wal_buffers and shared_buffers?

it's probably outdated but you could try to read Greg Smith's
"PostgreSQL 9.0 High Performance", because at least you
could have an idea of almost all the attack-points you
could use to increase you overall performance.

Even in the archive of this very mailinglist you'll surely find
a lot of good advice, e.g. one that I've read here recently is
avoid using any kernels between ver 3.0 and 3.8
(http://www.databasesoup.com/2014/09/why-you-need-to-avoid-linux-kernel-32.html)

> 2- I would like to use the two SDD to store the wal file. Do you think
> it is useful or how should I use them?

I definitely would give it a try.

Andrea

> On 11 Dec 2014, at 15:02, Andrea Suisani <sickpig(at)opinioni(dot)net> wrote:
>
> On 12/10/2014 11:44 AM, Maila Fatticcioni wrote:
>> 2- I would like to use the two SDD to store the wal file. Do you think
>> it is useful or how should I use them?
>
> I definitely would give it a try.
>

I don't understand the logic behind using drives,
which are best for random io, for sequent io workloads.

Better use 10k sas with BBU raid for wal, money wise.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 12/11/2014 01:11 PM, Evgeniy Shishkin wrote:
>
>> On 11 Dec 2014, at 15:02, Andrea Suisani <sickpig(at)opinioni(dot)net>
>> wrote:
>>
>> On 12/10/2014 11:44 AM, Maila Fatticcioni wrote:
>>> 2- I would like to use the two SDD to store the wal file. Do
>>> you think it is useful or how should I use them?
>>
>> I definitely would give it a try.
>>
>
>
> I don't understand the logic behind using drives, which are best
> for random io, for sequent io workloads.
>
> Better use 10k sas with BBU raid for wal, money wise.
>
>
>

Would you mind to explain me better why you do suggest me to use the
sas raid for wal please?

Thanks,
M.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iEYEARECAAYFAlSJkokACgkQi2q3wPb3FcOOZQCgrhy3sOP3Jds1eGlPqjSW+GhM
xFIAn3YbZgEFAlwTC+SX7GG2My0pElys
=Bsn7
-----END PGP SIGNATURE-----

> Would you mind to explain me better why you do suggest me to use the
> sas raid for wal please?

SSDs are known to shine when they have to deal with random access pattern
rather than sequential, on the other hand 10/15K rpm SAS disk is known to be
better for sequential io workloads (in general "rotating" disk use to be
better at sequential rather than random access)

Having said that it seems that SSDs are catching up, see:

http://www.anandtech.com/show/6935/seagate-600-ssd-review/5

Andrea

On 12/11/2014 01:11 PM, Evgeniy Shishkin wrote:
>
>> On 11 Dec 2014, at 15:02, Andrea Suisani <sickpig(at)opinioni(dot)net> wrote:
>>
>> On 12/10/2014 11:44 AM, Maila Fatticcioni wrote:
>>> 2- I would like to use the two SDD to store the wal file. Do you think
>>> it is useful or how should I use them?
>>
>> I definitely would give it a try.
>>
>
>
> I don't understand the logic behind using drives,
> which are best for random io, for sequent io workloads.
>
> Better use 10k sas with BBU raid for wal, money wise.

Well since Malia had already used the 4 sas hd for the DB,
I thought that it'd be quite quick to setup a raid1 array
(even at software level, e.g. using md), placing pg_xlog
in such array and measure the performance.

As a following step, depending on the time constraints involved,
Malia could rearrange the disk setup enterly and use the SAS
disks as location for pg_xlog.

Andrea

________________________________________
From: pgsql-performance-owner(at)postgresql(dot)org <pgsql-performance-owner(at)postgresql(dot)org> on behalf of Evgeniy Shishkin <itparanoia(at)gmail(dot)com>
Sent: Thursday, December 11, 2014 7:11 AM
To: Andrea Suisani
Cc: mfatticcioni(at)mbigroup(dot)it; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Tuning the configuration

> On 11 Dec 2014, at 15:02, Andrea Suisani <sickpig(at)opinioni(dot)net> wrote:
>
> On 12/10/2014 11:44 AM, Maila Fatticcioni wrote:
>> 2- I would like to use the two SDD to store the wal file. Do you think
>> it is useful or how should I use them?
>
> I definitely would give it a try.
>

> I don't understand the logic behind using drives,
> which are best for random io, for sequent io workloads.

> Better use 10k sas with BBU raid for wal, money wise.

Very much agree with this. Because SSD is fast doesn't make it suited for certain things, and a streaming sequential 100% write workload is one of them. I've worked with everything from local disk to high-end SAN and even at the high end we've always put any DB logs on spinning disk. RAID1 is generally sufficient. SSD is king for read heavy random I/O workload.

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

On 12/12/14 11:36, Eric Pierce wrote:
>
> ________________________________________
> From: pgsql-performance-owner(at)postgresql(dot)org <pgsql-performance-owner(at)postgresql(dot)org> on behalf of Evgeniy Shishkin <itparanoia(at)gmail(dot)com>
> Sent: Thursday, December 11, 2014 7:11 AM
> To: Andrea Suisani
> Cc: mfatticcioni(at)mbigroup(dot)it; pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Tuning the configuration
>
>> On 11 Dec 2014, at 15:02, Andrea Suisani <sickpig(at)opinioni(dot)net> wrote:
>>
>> On 12/10/2014 11:44 AM, Maila Fatticcioni wrote:
>>> 2- I would like to use the two SDD to store the wal file. Do you think
>>> it is useful or how should I use them?
>>
>> I definitely would give it a try.
>>
>
>
>> I don't understand the logic behind using drives,
>> which are best for random io, for sequent io workloads.
>
>> Better use 10k sas with BBU raid for wal, money wise.
>
> Very much agree with this. Because SSD is fast doesn't make it suited for certain things, and a streaming sequential 100% write workload is one of them. I've worked with everything from local disk to high-end SAN and even at the high end we've always put any DB logs on spinning disk. RAID1 is generally sufficient. SSD is king for read heavy random I/O workload.
>

Mind you wal is a little different - the limiting factor is (usually)
not raw sequential speed but fsync latency. These days a modern SSD has
fsync response pretty much equal to that of a card with BBU + spinners -
and has "more" high speed storage available (cards usually have only a
1G or so of RAM on them).

regards

Mark

> Very much agree with this. Because SSD is fast doesn't make it suited for certain things, and a streaming sequential 100% write workload is one of them. I've worked with everything from local disk to high-end SAN and even at the high end we've always put any DB logs on spinning disk. RAID1 is generally sufficient. SSD is king for read heavy random I/O workload.

1. Here we found SSD sustained serial writes were faster on SSD than to disk, by a factor of 3, both in RAID and single disk configurations.

2. Also, something to watch out for is extended stalls due to synchronous write activity / clearing out of cache, when a lot of data has been building up in write caches. By placing the WAL on the same disk as the ordinary database, you avoid having too much dirty cache building up because the WAL forces the disk to flush more often. So you can trade off some DB filesystem performance here to avoid blocking / IO lag spikes.

3. There's also the question of disk bays. When you have extra disks for OS, for logs, etc. , in some situations you're using up disks that could be used to extend your main database filesystem, particularly when those disks also need to be protected by the appropriate RAID mirrors and RAID hotspares. It can be cheaper to put the logs to SSD than to have 1 extra hdd + its RAID1 mirror + its hotspare + possible shelfspare, plus pay for a bigger chassis to have 3 more disk bays.

4. Finally there's the issue of simplicity. If you get a fast SSD and run OS/logs/DB off a single RAID volume, there's less chance for error when some unlucky person has to do an emergency fix/rebuild later, than if they have to check disk caching policy etc across a range of devices and ensure different parts of the filesystem are mounted in all the right places. Makes documentation easier.

Graeme Bell

>
> I don't understand the logic behind using drives,
> which are best for random io, for sequent io workloads.

Because they are also best for sequential IO. I get 1.3-1.4GB/second from 4 SSDs in RAID or >500MB/s for single disk systems, even with cheap models.
Are you getting more than that from high-end spinning rust?

Graeme.

> On 16 Dec 2014, at 14:51, Graeme B. Bell <grb(at)skogoglandskap(dot)no> wrote:
>
>>
>> I don't understand the logic behind using drives,
>> which are best for random io, for sequent io workloads.
>
> Because they are also best for sequential IO. I get 1.3-1.4GB/second from 4 SSDs in RAID or >500MB/s for single disk systems, even with cheap models.
> Are you getting more than that from high-end spinning rust?

I better use ssd for random iops when database doesn't fit in ram.
For wal logs i use raid with bbu cache and couple of sas drives.