pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSpecifications for a new server
13+ messages / 7 participants
[nested] [flat]
* Specifications for a new server
@ 2014-05-06 09:13 Johann Spies <johann.spies@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Johann Spies @ 2014-05-06 09:13 UTC (permalink / raw)
To: pgsql-performance
I am busy reading Gregory Smith' s PostgreSQL 9.0 High Performance and
when the book was written he seemed to me a bit sceptical about SSD's. I
suspect the reliability of the SSD's has improved significantly since then.
Our present server (128Gb RAM and 2.5 Tb disk space and 12 CPU cores -
RAID 10) will become a development server and we are going to buy a new
server.
At the moment the 'base' directory uses 1.5Tb of disk space and there is
still more data to come.
The database contains blbliometric data that receive updates on a weekly
basis but not much changes other than that except for cleaning of data by a
few persons.
Some of the queries can take many hours to finish.
On our present system there are sometimes more than 300GB in temporary
files which I suspect will not be the case on the new system with a much
larger RAM.
Analysis or the SAR-logs showed that there were too much iowait in the
CPU's on the old system which has a lower spec CPU than the ones considered
for the new system.
We are looking possibly the following hardware:
CPU: 2 x Ivy Bridge 8C E5-2667V2 3.3G 25M 8GT/s QPI - 16 cores
RAM: 24 x 32GB DDR3-1866 2Rx4 LP ECC REG RoHS - 768Gb
with enough disk space - about 4.8 Tb on RAID 10.
My question is about the possible advantage and usage of SSD disks in the
new server. At the moment I am considering using 2 x 200GB SSD' s for a
separate partion for temporary files and 2 x 100GB for the operating system.
So my questions:
1. Will the SSD's in this case be worth the cost?
2. What will the best way to utilize them in the system?
Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Specifications for a new server
@ 2014-05-06 09:25 DFE <mimmopasticcio@gmail.com>
parent: Johann Spies <johann.spies@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: DFE @ 2014-05-06 09:25 UTC (permalink / raw)
To: Johann Spies <johann.spies@gmail.com>; +Cc: pgsql-performance
I can suggest to have a disks' layout using at least two RAIDs:
1) RAID10 SSD (or 15kRPM HDD) SAS for O.S. and "pg_xlog" folder where PG
writes WAL files before checkpoint calls.
2) RAID10 using how many span is possible for the default DB folder.
Regards,
2014-05-06 11:13 GMT+02:00 Johann Spies <johann.spies@gmail.com>:
> I am busy reading Gregory Smith' s PostgreSQL 9.0 High Performance and
> when the book was written he seemed to me a bit sceptical about SSD's. I
> suspect the reliability of the SSD's has improved significantly since then.
>
> Our present server (128Gb RAM and 2.5 Tb disk space and 12 CPU cores -
> RAID 10) will become a development server and we are going to buy a new
> server.
>
> At the moment the 'base' directory uses 1.5Tb of disk space and there is
> still more data to come.
>
> The database contains blbliometric data that receive updates on a weekly
> basis but not much changes other than that except for cleaning of data by a
> few persons.
>
> Some of the queries can take many hours to finish.
>
> On our present system there are sometimes more than 300GB in temporary
> files which I suspect will not be the case on the new system with a much
> larger RAM.
>
> Analysis or the SAR-logs showed that there were too much iowait in the
> CPU's on the old system which has a lower spec CPU than the ones considered
> for the new system.
>
> We are looking possibly the following hardware:
>
> CPU: 2 x Ivy Bridge 8C E5-2667V2 3.3G 25M 8GT/s QPI - 16 cores
> RAM: 24 x 32GB DDR3-1866 2Rx4 LP ECC REG RoHS - 768Gb
>
> with enough disk space - about 4.8 Tb on RAID 10.
> My question is about the possible advantage and usage of SSD disks in the
> new server. At the moment I am considering using 2 x 200GB SSD' s for a
> separate partion for temporary files and 2 x 100GB for the operating system.
>
> So my questions:
>
> 1. Will the SSD's in this case be worth the cost?
> 2. What will the best way to utilize them in the system?
>
> Regards
> Johann
> --
> Because experiencing your loyal love is better than life itself,
> my lips will praise you. (Psalm 63:3)
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Specifications for a new server
@ 2014-05-06 11:07 Michael Stone <mstone+postgres@mathom.us>
parent: Johann Spies <johann.spies@gmail.com>
1 sibling, 2 replies; 13+ messages in thread
From: Michael Stone @ 2014-05-06 11:07 UTC (permalink / raw)
To: pgsql-performance
On Tue, May 06, 2014 at 11:13:42AM +0200, Johann Spies wrote:
>Analysis or the SAR-logs showed that there were too much iowait in the CPU's on
>the old system which has a lower spec CPU than the ones considered for the new
>system.
iowait means the cpu is doing nothing but waiting for data from the
disk. buying faster cpus means that they will be able to spend more time
waiting for data from the disk. you'd probably get much better bang for
the buck upgrading the storage subsystem than throwing more money at
cpus.
>We are looking possibly the following hardware:
>
>CPU: 2 x Ivy Bridge 8C E5-2667V2 3.3G 25M 8GT/s QPI - 16 cores
>RAM: 24 x 32GB DDR3-1866 2Rx4 LP ECC REG RoHS - 768Gb
>
>with enough disk space - about 4.8 Tb on RAID 10.
>My question is about the possible advantage and usage of SSD disks in the new
>server.
>At the moment I am considering using 2 x 200GB SSD' s for a separate
>partion for temporary files and 2 x 100GB for the operating system.
If you're talking about SSDs for the OS, that's a complete waste; there
is essentially no I/O relating to the OS once you've booted.
>So my questions:
>
>1. Will the SSD's in this case be worth the cost?
>2. What will the best way to utilize them in the system?
The best way to utilize them would probably be to spend less on the CPU
and RAM and more on the storage, and use SSD either for all of the
storage or for specific items that have a high level of I/O (such as the
indexes). Can't be more specific than that without a lot more
information about the database, how it is utilized, and what's actually
slow.
Mike Stone
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Specifications for a new server
@ 2014-05-06 11:15 Dorian Hoxha <dorian.hoxha@gmail.com>
parent: Michael Stone <mstone+postgres@mathom.us>
1 sibling, 1 reply; 13+ messages in thread
From: Dorian Hoxha @ 2014-05-06 11:15 UTC (permalink / raw)
To: pgsql-performance
Since the commitlog/WAL is sequential-write, does it mattert that much to
put it in ssd ?(i understand that it matters to put it in separate
disk-subsystem so the write/read patterns don't interfere)
On Tue, May 6, 2014 at 1:07 PM, Michael Stone <mstone+postgres@mathom.us>wrote:
> On Tue, May 06, 2014 at 11:13:42AM +0200, Johann Spies wrote:
>
>> Analysis or the SAR-logs showed that there were too much iowait in the
>> CPU's on
>> the old system which has a lower spec CPU than the ones considered for
>> the new
>> system.
>>
>
> iowait means the cpu is doing nothing but waiting for data from the disk.
> buying faster cpus means that they will be able to spend more time waiting
> for data from the disk. you'd probably get much better bang for the buck
> upgrading the storage subsystem than throwing more money at cpus.
>
>
> We are looking possibly the following hardware:
>>
>> CPU: 2 x Ivy Bridge 8C E5-2667V2 3.3G 25M 8GT/s QPI - 16 cores
>> RAM: 24 x 32GB DDR3-1866 2Rx4 LP ECC REG RoHS - 768Gb
>>
>> with enough disk space - about 4.8 Tb on RAID 10.
>> My question is about the possible advantage and usage of SSD disks in the
>> new
>> server.
>>
>
> At the moment I am considering using 2 x 200GB SSD' s for a separate
>> partion for temporary files and 2 x 100GB for the operating system.
>>
>
> If you're talking about SSDs for the OS, that's a complete waste; there is
> essentially no I/O relating to the OS once you've booted.
>
>
> So my questions:
>>
>> 1. Will the SSD's in this case be worth the cost?
>> 2. What will the best way to utilize them in the system?
>>
>
> The best way to utilize them would probably be to spend less on the CPU
> and RAM and more on the storage, and use SSD either for all of the storage
> or for specific items that have a high level of I/O (such as the indexes).
> Can't be more specific than that without a lot more information about the
> database, how it is utilized, and what's actually slow.
>
> Mike Stone
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Specifications for a new server
@ 2014-05-06 11:24 Michael Stone <mstone+postgres@mathom.us>
parent: Dorian Hoxha <dorian.hoxha@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Michael Stone @ 2014-05-06 11:24 UTC (permalink / raw)
To: pgsql-performance
On Tue, May 06, 2014 at 01:15:10PM +0200, Dorian Hoxha wrote:
>Since the commitlog/WAL is sequential-write, does it mattert that much to put
>it in ssd
No, assuming a good storage system with nvram write buffer.
Mike Stone
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Specifications for a new server
@ 2014-05-06 14:43 Dorian Hoxha <dorian.hoxha@gmail.com>
parent: Michael Stone <mstone+postgres@mathom.us>
0 siblings, 1 reply; 13+ messages in thread
From: Dorian Hoxha @ 2014-05-06 14:43 UTC (permalink / raw)
To: pgsql-performance
And nvram is ram on hardware-raid controller that is not erased on
reboot(battery) ?
Can this nvram be used also when the configuration is
jbod(justabunchofdisks) or some kind of raid(0/1/5/6/10 etc) is required to
use the nvram.
On Tue, May 6, 2014 at 1:24 PM, Michael Stone <mstone+postgres@mathom.us>wrote:
> On Tue, May 06, 2014 at 01:15:10PM +0200, Dorian Hoxha wrote:
>
>> Since the commitlog/WAL is sequential-write, does it mattert that much to
>> put
>> it in ssd
>>
>
> No, assuming a good storage system with nvram write buffer.
> Mike Stone
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Specifications for a new server
@ 2014-05-07 21:35 Tomas Vondra <tv@fuzzy.cz>
parent: Dorian Hoxha <dorian.hoxha@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tomas Vondra @ 2014-05-07 21:35 UTC (permalink / raw)
To: pgsql-performance
On 6.5.2014 16:43, Dorian Hoxha wrote:
> And nvram is ram on hardware-raid controller that is not erased on
> reboot(battery) ?
Yes.
> Can this nvram be used also when the configuration is
> jbod(justabunchofdisks) or some kind of raid(0/1/5/6/10 etc) is
> required to use the nvram.
That probably depends on the controller used. Some controllers (e.g.
Dell Perc H700 and such) don't even support JBOD - an easy way around
this is create single-drive RAID0 arrays (never tried it, though).
Why do you even want to use JBOD?
regards
Tomas
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PERFORM] Specifications for a new server
@ 2014-05-08 05:20 r.etzenhammer@t-online.de <r.etzenhammer@t-online.de>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 0 replies; 13+ messages in thread
From: r.etzenhammer@t-online.de @ 2014-05-08 05:20 UTC (permalink / raw)
To: pgsql-performance
Hello,
here I am - the lonely user of JBOD :-) I use exactly what you describe below, we have a couple of Dell Servers with battery backed H700 controllers. On one we let the controller do a RAID10 on others I use the single disks (as you describe in useless RAID0s).
What we do then is use those disks in a Solaris ZFS Pool as filesystem for our databases. The reason is, that with ZFS (maybe there are other fs around there which can do this, but far back in good old Sun-times it was the only one) I can build up a pool and mix nearline SAS with SSDs in a neat way. ZFS has the concept of an ZFS Intent Log (ZIL), where writes go first before migrated to the pool - and you can put them on seperate disks. And you have the adaptive replacement cache (ARC) you can also put on seperate disks, where hot data is cached. Now guess where we use the expensive SSDs.
It boils down to this:
- slow mechanical drives backed with nvram controller
- SSD expecially for the ARC (and ZIL)
- RAM for ZFS further caching
On my very first try when ZIL and ARC approached in Solaris my first idea was just put one SSD splitted to two partitions as ARC and ZIL to our exisiting database. I didn't change anything else (to be fair, that was a development system with out nvram controller) - gave me a boost in performace of about 10.
To be a bit more fair (I can, as I do not work for Sun - ehm Oracle): If this would work for someone else, the usage pattern should be kept in mind. Also setting and tuning a Solaris server is of some more work and ZFS itself uses a good bunch of CPU and RAM (we leave 8G for ZFS) and one would need much testing and tuning.
A positive effect we use is for backing up data - since ZFS offers atomic snapshots, we just snapshot the filesystem. The datafiles are guaranteed to be vaild as the WAL. A restore is to put the files back into place and restart Postgres - it will run a recovery and replay the WAL and everything is done. We used that to provide back in time recovery and warm standby with old postgres installations before replication was included into core.
Cheers,
Roland
> Can this nvram be used also when the configuration is
> jbod(justabunchofdisks) or some kind of raid(0/1/5/6/10 etc) is
> required to use the nvram.
That probably depends on the controller used. Some controllers (e.g.
Dell Perc H700 and such) don't even support JBOD - an easy way around
this is create single-drive RAID0 arrays (never tried it, though).
Why do you even want to use JBOD?
----------------------------------------------------------------
E-Mail-Postfach voll? Jetzt kostenlos E-Mail-Adresse @t-online.de sichern und endlich Platz für tausende E-Mails haben.
http://www.t-online.de/email-kostenlos
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Specifications for a new server
@ 2014-05-08 08:11 Johann Spies <johann.spies@gmail.com>
parent: Michael Stone <mstone+postgres@mathom.us>
1 sibling, 3 replies; 13+ messages in thread
From: Johann Spies @ 2014-05-08 08:11 UTC (permalink / raw)
To: pgsql-performance
On 6 May 2014 13:07, Michael Stone <mstone+postgres@mathom.us> wrote:
> On Tue, May 06, 2014 at 11:13:42AM +0200, Johann Spies wrote:
>
>> Analysis or the SAR-logs showed that there were too much iowait in the
>> CPU's on
>> the old system which has a lower spec CPU than the ones considered for
>> the new
>> system.
>>
>
> iowait means the cpu is doing nothing but waiting for data from the disk.
> buying faster cpus means that they will be able to spend more time waiting
> for data from the disk. you'd probably get much better bang for the buck
> upgrading the storage subsystem than throwing more money at cpus.
>
>
> In that case I apologise for making the wrong assumption. People who are
more experienced than me analyzed the logs told me that to their surprise
the CPU' s were under pressure. I just assumed that the iowait was the
problem having looked at the logs myself.
> If you're talking about SSDs for the OS, that's a complete waste; there is
> essentially no I/O relating to the OS once you've booted.
>
>
I also thought this might be an overkill but I was not sure.
>
> So my questions:
>>
>> 1. Will the SSD's in this case be worth the cost?
>> 2. What will the best way to utilize them in the system?
>>
>
> The best way to utilize them would probably be to spend less on the CPU
> and RAM and more on the storage, and use SSD either for all of the storage
> or for specific items that have a high level of I/O (such as the indexes).
> Can't be more specific than that without a lot more information about the
> database, how it is utilized, and what's actually slow.
>
>
I understand your remark about the CPU in the light of my wrong assumption
earlier, but I do not understand your remark about the RAM. The fact that
temporary files of up to 250Gb are created at times during complex queries,
is to me an indication of too low RAM.
Question: How do I dedicate a partition to indexes? Were do I configure
PostgreSQL to write them in a particular area?
Regards
Johann
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Specifications for a new server
@ 2014-05-08 08:28 Johann Spies <johann.spies@gmail.com>
parent: Johann Spies <johann.spies@gmail.com>
2 siblings, 1 reply; 13+ messages in thread
From: Johann Spies @ 2014-05-08 08:28 UTC (permalink / raw)
To: pgsql-performance
On 8 May 2014 10:11, Johann Spies <johann.spies@gmail.com> wrote:
>
> Question: How do I dedicate a partition to indexes? Were do I configure
> PostgreSQL to write them in a particular area?
>
>
>
I just discovered TABLESPACE which answered my question.
Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Specifications for a new server
@ 2014-05-08 11:44 Dorian Hoxha <dorian.hoxha@gmail.com>
parent: Johann Spies <johann.spies@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Dorian Hoxha @ 2014-05-08 11:44 UTC (permalink / raw)
To: Johann Spies <johann.spies@gmail.com>; +Cc: pgsql-performance
>
> Why do you even want to use JBOD?
>
Not for postgresql , but for distributed filesystems like hdfs/qfs (which
are supposed to work on JBOD) with hypertable on top (so the nvram would
help with the commits, since it is the biggest bottleneck when
writing(commits need to be saved to multiple servers before 'ok' is
returned in the client)).
On Thu, May 8, 2014 at 10:28 AM, Johann Spies <johann.spies@gmail.com>wrote:
>
>
>
> On 8 May 2014 10:11, Johann Spies <johann.spies@gmail.com> wrote:
>
>>
>> Question: How do I dedicate a partition to indexes? Were do I configure
>> PostgreSQL to write them in a particular area?
>>
>>
>>
> I just discovered TABLESPACE which answered my question.
>
> Regards
> Johann
>
> --
> Because experiencing your loyal love is better than life itself,
> my lips will praise you. (Psalm 63:3)
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Specifications for a new server
@ 2014-05-13 14:19 Michael Stone <mstone+postgres@mathom.us>
parent: Johann Spies <johann.spies@gmail.com>
2 siblings, 0 replies; 13+ messages in thread
From: Michael Stone @ 2014-05-13 14:19 UTC (permalink / raw)
To: pgsql-performance
On Thu, May 08, 2014 at 10:11:38AM +0200, Johann Spies wrote:
>I understand your remark about the CPU in the light of my wrong assumption
>earlier, but I do not understand your remark about the RAM. The fact that
>temporary files of up to 250Gb are created at times during complex queries, is
>to me an indication of too low RAM.
If you can afford infinite RAM, then infinite RAM is great. If your
working set size exceeds the memory size, then you will eventually need
to deal with disk IO. At that point, maybe a bit more memory will help
and maybe it will not--you'll be able to fit a little bit more working
data into memory, but that won't likely radically change the
performance. (If you can afford to fit *everything* you need into RAM
than that's ideal, but that's not the case for most people with
non-trival data sets.) What is certain is that improving the disk IO
performance will improve your overall performance if you're IO bound.
(And the mere existence of temporary files isn't an indication of
insufficient RAM if the system can utilize the memory more efficiently
with the files than it can without them--they could contain data that
isn't needed in a particular phase of a query, freeing up resources that
are needed for other data in that phase.)
Mike Stone
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Specifications for a new server
@ 2014-05-13 17:12 Jeff Janes <jeff.janes@gmail.com>
parent: Johann Spies <johann.spies@gmail.com>
2 siblings, 0 replies; 13+ messages in thread
From: Jeff Janes @ 2014-05-13 17:12 UTC (permalink / raw)
To: Johann Spies <johann.spies@gmail.com>; +Cc: pgsql-performance
On Thu, May 8, 2014 at 1:11 AM, Johann Spies <johann.spies@gmail.com> wrote:
>
>
>>
>> So my questions:
>>>
>>> 1. Will the SSD's in this case be worth the cost?
>>> 2. What will the best way to utilize them in the system?
>>>
>>
>> The best way to utilize them would probably be to spend less on the CPU
>> and RAM and more on the storage, and use SSD either for all of the storage
>> or for specific items that have a high level of I/O (such as the indexes).
>> Can't be more specific than that without a lot more information about the
>> database, how it is utilized, and what's actually slow.
>>
>>
>
> I understand your remark about the CPU in the light of my wrong assumption
> earlier, but I do not understand your remark about the RAM. The fact that
> temporary files of up to 250Gb are created at times during complex queries,
> is to me an indication of too low RAM.
>
Are these PostgreSQL temp files or other temp files? PostgreSQL doesn't
suppress the use of temp files just because you have a lot of RAM. You
would also have to set work_mem to a very large setting, probably
inappropriately large, and even that might not work because there other
limits on how much memory PostgreSQL can use for any given operation (for
example, you can't sort more than 2**32 (or 2**31?) tuples in memory, no
matter how much memory you have, and in older versions even less than
that). But that doesn't mean the RAM is not useful. The OS can use the
RAM to buffer the temp files so that they might not ever see the disk, or
might not be read from disk because they are still in memory.
SSD is probably wasted on temp files, as they are designed to be accessed
mostly sequentially.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2014-05-13 17:12 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-05-06 09:13 Specifications for a new server Johann Spies <johann.spies@gmail.com>
2014-05-06 09:25 ` DFE <mimmopasticcio@gmail.com>
2014-05-06 11:07 ` Michael Stone <mstone+postgres@mathom.us>
2014-05-06 11:15 ` Dorian Hoxha <dorian.hoxha@gmail.com>
2014-05-06 11:24 ` Michael Stone <mstone+postgres@mathom.us>
2014-05-06 14:43 ` Dorian Hoxha <dorian.hoxha@gmail.com>
2014-05-07 21:35 ` Tomas Vondra <tv@fuzzy.cz>
2014-05-08 05:20 ` r.etzenhammer@t-online.de <r.etzenhammer@t-online.de>
2014-05-08 08:11 ` Johann Spies <johann.spies@gmail.com>
2014-05-08 08:28 ` Johann Spies <johann.spies@gmail.com>
2014-05-08 11:44 ` Dorian Hoxha <dorian.hoxha@gmail.com>
2014-05-13 14:19 ` Michael Stone <mstone+postgres@mathom.us>
2014-05-13 17:12 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox