pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTuning Tips for a new Server
11+ messages / 4 participants
[nested] [flat]
* Tuning Tips for a new Server
@ 2011-08-17 01:35 Ogden <lists@darkstatic.com>
2011-08-17 13:41 ` Re: Tuning Tips for a new Server Andy Colson <andy@squeakycode.net>
2011-08-17 14:44 ` Re: Tuning Tips for a new Server Tomas Vondra <tv@fuzzy.cz>
0 siblings, 2 replies; 11+ messages in thread
From: Ogden @ 2011-08-17 01:35 UTC (permalink / raw)
To: pgsql-performance
Hope all is well. I have received tremendous help from this list prior and therefore wanted some more advice.
I bought some new servers and instead of RAID 5 (which I think greatly hindered our writing performance), I configured 6 SCSI 15K drives with RAID 10. This is dedicated to /var/lib/pgsql. The main OS has 2 SCSI 15K drives on a different virtual disk and also Raid 10, a total of 146Gb. I was thinking of putting Postgres' xlog directory on the OS virtual drive. Does this even make sense to do?
The system memory is 64GB and the CPUs are dual Intel E5645 chips (they are 6-core each).
It is a dedicated PostgreSQL box and needs to support heavy read and moderately heavy writes.
Currently, I have this for the current system which as 16Gb Ram:
max_connections = 350
work_mem = 32MB
maintenance_work_mem = 512MB
wal_buffers = 640kB
# This is what I was helped with before and made reporting queries blaze by
seq_page_cost = 1.0
random_page_cost = 3.0
cpu_tuple_cost = 0.5
effective_cache_size = 8192MB
Any help and input is greatly appreciated.
Thank you
Ogden
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tuning Tips for a new Server
2011-08-17 01:35 Tuning Tips for a new Server Ogden <lists@darkstatic.com>
@ 2011-08-17 13:41 ` Andy Colson <andy@squeakycode.net>
2011-08-17 14:28 ` Re: Tuning Tips for a new Server Ogden <lists@darkstatic.com>
1 sibling, 1 reply; 11+ messages in thread
From: Andy Colson @ 2011-08-17 13:41 UTC (permalink / raw)
To: Ogden <lists@darkstatic.com>; +Cc: pgsql-performance
On 8/16/2011 8:35 PM, Ogden wrote:
> Hope all is well. I have received tremendous help from this list prior and therefore wanted some more advice.
>
> I bought some new servers and instead of RAID 5 (which I think greatly hindered our writing performance), I configured 6 SCSI 15K drives with RAID 10. This is dedicated to /var/lib/pgsql. The main OS has 2 SCSI 15K drives on a different virtual disk and also Raid 10, a total of 146Gb. I was thinking of putting Postgres' xlog directory on the OS virtual drive. Does this even make sense to do?
>
> The system memory is 64GB and the CPUs are dual Intel E5645 chips (they are 6-core each).
>
> It is a dedicated PostgreSQL box and needs to support heavy read and moderately heavy writes.
>
> Currently, I have this for the current system which as 16Gb Ram:
>
> max_connections = 350
>
> work_mem = 32MB
> maintenance_work_mem = 512MB
> wal_buffers = 640kB
>
> # This is what I was helped with before and made reporting queries blaze by
> seq_page_cost = 1.0
> random_page_cost = 3.0
> cpu_tuple_cost = 0.5
> effective_cache_size = 8192MB
>
> Any help and input is greatly appreciated.
>
> Thank you
>
> Ogden
What seems to be the problem? I mean, if nothing is broke, then don't
fix it :-)
You say reporting query's are fast, and the disk's should take care of
your slow write problem from before. (Did you test the write
performance?) So, whats wrong?
-Andy
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tuning Tips for a new Server
2011-08-17 01:35 Tuning Tips for a new Server Ogden <lists@darkstatic.com>
2011-08-17 13:41 ` Re: Tuning Tips for a new Server Andy Colson <andy@squeakycode.net>
@ 2011-08-17 14:28 ` Ogden <lists@darkstatic.com>
2011-08-17 15:08 ` Re: Tuning Tips for a new Server Tomas Vondra <tv@fuzzy.cz>
0 siblings, 1 reply; 11+ messages in thread
From: Ogden @ 2011-08-17 14:28 UTC (permalink / raw)
To: Andy Colson <andy@squeakycode.net>; +Cc: pgsql-performance
On Aug 17, 2011, at 8:41 AM, Andy Colson wrote:
> On 8/16/2011 8:35 PM, Ogden wrote:
>> Hope all is well. I have received tremendous help from this list prior and therefore wanted some more advice.
>>
>> I bought some new servers and instead of RAID 5 (which I think greatly hindered our writing performance), I configured 6 SCSI 15K drives with RAID 10. This is dedicated to /var/lib/pgsql. The main OS has 2 SCSI 15K drives on a different virtual disk and also Raid 10, a total of 146Gb. I was thinking of putting Postgres' xlog directory on the OS virtual drive. Does this even make sense to do?
>>
>> The system memory is 64GB and the CPUs are dual Intel E5645 chips (they are 6-core each).
>>
>> It is a dedicated PostgreSQL box and needs to support heavy read and moderately heavy writes.
>>
>> Currently, I have this for the current system which as 16Gb Ram:
>>
>> max_connections = 350
>>
>> work_mem = 32MB
>> maintenance_work_mem = 512MB
>> wal_buffers = 640kB
>>
>> # This is what I was helped with before and made reporting queries blaze by
>> seq_page_cost = 1.0
>> random_page_cost = 3.0
>> cpu_tuple_cost = 0.5
>> effective_cache_size = 8192MB
>>
>> Any help and input is greatly appreciated.
>>
>> Thank you
>>
>> Ogden
>
> What seems to be the problem? I mean, if nothing is broke, then don't fix it :-)
>
> You say reporting query's are fast, and the disk's should take care of your slow write problem from before. (Did you test the write performance?) So, whats wrong?
I was wondering what the best parameters would be with my new setup. The work_mem obviously will increase as will everything else as it's a 64Gb machine as opposed to a 16Gb machine. The configuration I posted was for a 16Gb machine but this new one is 64Gb. I needed help in how to jump these numbers up.
Thank you
Ogden
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tuning Tips for a new Server
2011-08-17 01:35 Tuning Tips for a new Server Ogden <lists@darkstatic.com>
2011-08-17 13:41 ` Re: Tuning Tips for a new Server Andy Colson <andy@squeakycode.net>
2011-08-17 14:28 ` Re: Tuning Tips for a new Server Ogden <lists@darkstatic.com>
@ 2011-08-17 15:08 ` Tomas Vondra <tv@fuzzy.cz>
0 siblings, 0 replies; 11+ messages in thread
From: Tomas Vondra @ 2011-08-17 15:08 UTC (permalink / raw)
To: Ogden <lists@darkstatic.com>; +Cc: Andy Colson <andy@squeakycode.net>; pgsql-performance
On 17 Srpen 2011, 16:28, Ogden wrote:
> I was wondering what the best parameters would be with my new setup. The
> work_mem obviously will increase as will everything else as it's a 64Gb
> machine as opposed to a 16Gb machine. The configuration I posted was for
> a 16Gb machine but this new one is 64Gb. I needed help in how to jump
> these numbers up.
Well, that really depends on how you come to the current work_mem settings.
If you've decided that with this amount of work_mem the queries run fine
and higher values don't give you better performance (because the amount of
data that needs to be sorted / hashed) fits into the work_mem, then don't
increase it.
But if you've just set it so that the memory is not exhausted, increasing
it may actually help you.
What I think you should review is the amount of shared buffers,
checkpoints and page cache settings (see this for example
http://notemagnet.blogspot.com/2008/08/linux-write-cache-mystery.html).
Tomas
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tuning Tips for a new Server
2011-08-17 01:35 Tuning Tips for a new Server Ogden <lists@darkstatic.com>
@ 2011-08-17 14:44 ` Tomas Vondra <tv@fuzzy.cz>
2011-08-17 16:39 ` Re: Tuning Tips for a new Server Ogden <lists@darkstatic.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tomas Vondra @ 2011-08-17 14:44 UTC (permalink / raw)
To: Ogden <lists@darkstatic.com>; +Cc: pgsql-performance
On 17 Srpen 2011, 3:35, Ogden wrote:
> Hope all is well. I have received tremendous help from this list prior and
> therefore wanted some more advice.
>
> I bought some new servers and instead of RAID 5 (which I think greatly
> hindered our writing performance), I configured 6 SCSI 15K drives with
> RAID 10. This is dedicated to /var/lib/pgsql. The main OS has 2 SCSI 15K
> drives on a different virtual disk and also Raid 10, a total of 146Gb. I
> was thinking of putting Postgres' xlog directory on the OS virtual drive.
> Does this even make sense to do?
Yes, but it greatly depends on the amount of WAL and your workload. If you
need to write a lot of WAL data (e.g. during bulk loading), this may
significantly improve performance. It may also help when you have a
write-heavy workload (a lot of clients updating records, background writer
etc.) as that usually means a lot of seeking (while WAL is written
sequentially).
> The system memory is 64GB and the CPUs are dual Intel E5645 chips (they
> are 6-core each).
>
> It is a dedicated PostgreSQL box and needs to support heavy read and
> moderately heavy writes.
What is the size of the database? So those are the new servers? What's the
difference compared to the old ones? What is the RAID controller, how much
write cache is there?
> Currently, I have this for the current system which as 16Gb Ram:
>
> max_connections = 350
>
> work_mem = 32MB
> maintenance_work_mem = 512MB
> wal_buffers = 640kB
Are you really using 350 connections? Something like "#cpus + #drives" is
usually recommended as a sane number, unless the connections are idle most
of the time. And even in that case a pooling is recommended usually.
Anyway if this worked fine for your workload, I don't think you need to
change those settings. I'd probably bump up the wal_buffers to 16MB - it
might help a bit, definitely won't hurt and it's so little memory it's not
worth the effort I guess.
>
> # This is what I was helped with before and made reporting queries blaze
> by
> seq_page_cost = 1.0
> random_page_cost = 3.0
> cpu_tuple_cost = 0.5
> effective_cache_size = 8192MB
Are you sure the cpu_tuple_cost = 0.5 is correct? That seems a bit crazy
to me, as it says reading a page sequentially is just twice as expensive
as processing it. This value should be abou 100x lower or something like
that.
What are the checkpoint settings (segments, completion target). What about
shared buffers?
Tomas
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tuning Tips for a new Server
2011-08-17 01:35 Tuning Tips for a new Server Ogden <lists@darkstatic.com>
2011-08-17 14:44 ` Re: Tuning Tips for a new Server Tomas Vondra <tv@fuzzy.cz>
@ 2011-08-17 16:39 ` Ogden <lists@darkstatic.com>
2011-08-17 18:56 ` Re: Tuning Tips for a new Server Tomas Vondra <tv@fuzzy.cz>
0 siblings, 1 reply; 11+ messages in thread
From: Ogden @ 2011-08-17 16:39 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: pgsql-performance
On Aug 17, 2011, at 9:44 AM, Tomas Vondra wrote:
> On 17 Srpen 2011, 3:35, Ogden wrote:
>> Hope all is well. I have received tremendous help from this list prior and
>> therefore wanted some more advice.
>>
>> I bought some new servers and instead of RAID 5 (which I think greatly
>> hindered our writing performance), I configured 6 SCSI 15K drives with
>> RAID 10. This is dedicated to /var/lib/pgsql. The main OS has 2 SCSI 15K
>> drives on a different virtual disk and also Raid 10, a total of 146Gb. I
>> was thinking of putting Postgres' xlog directory on the OS virtual drive.
>> Does this even make sense to do?
>
> Yes, but it greatly depends on the amount of WAL and your workload. If you
> need to write a lot of WAL data (e.g. during bulk loading), this may
> significantly improve performance. It may also help when you have a
> write-heavy workload (a lot of clients updating records, background writer
> etc.) as that usually means a lot of seeking (while WAL is written
> sequentially).
The database is about 200Gb so using /usr/local/pgsql/pg_xlog on a virtual disk with 100Gb should not be a problem with the disk space should it?
>> The system memory is 64GB and the CPUs are dual Intel E5645 chips (they
>> are 6-core each).
>>
>> It is a dedicated PostgreSQL box and needs to support heavy read and
>> moderately heavy writes.
>
> What is the size of the database? So those are the new servers? What's the
> difference compared to the old ones? What is the RAID controller, how much
> write cache is there?
>
I am sorry I overlooked specifying this. The database is about 200Gb and yes these are new servers which bring more power (RAM, CPU) over the last one. The RAID Controller is a Perc H700 and there is 512Mb write cache. The servers are Dells.
>> Currently, I have this for the current system which as 16Gb Ram:
>>
>> max_connections = 350
>>
>> work_mem = 32MB
>> maintenance_work_mem = 512MB
>> wal_buffers = 640kB
>
> Are you really using 350 connections? Something like "#cpus + #drives" is
> usually recommended as a sane number, unless the connections are idle most
> of the time. And even in that case a pooling is recommended usually.
>
> Anyway if this worked fine for your workload, I don't think you need to
> change those settings. I'd probably bump up the wal_buffers to 16MB - it
> might help a bit, definitely won't hurt and it's so little memory it's not
> worth the effort I guess.
So just increasing the wal_buffers is okay? I thought there would be more as the memory in the system is now 4 times as much. Perhaps shared_buffers too (down below).
>>
>> # This is what I was helped with before and made reporting queries blaze
>> by
>> seq_page_cost = 1.0
>> random_page_cost = 3.0
>> cpu_tuple_cost = 0.5
>> effective_cache_size = 8192MB
>
> Are you sure the cpu_tuple_cost = 0.5 is correct? That seems a bit crazy
> to me, as it says reading a page sequentially is just twice as expensive
> as processing it. This value should be abou 100x lower or something like
> that.
These settings are for the old server, keep in mind. It's a 16GB machine (the new one is 64Gb). The value for cpu_tuple_cost should be 0.005? How are the other ones?
> What are the checkpoint settings (segments, completion target). What about
> shared buffers?
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 - was 0.5
#checkpoint_warning = 30s # 0 disables
And
shared_buffers = 4096MB
Thank you very much
Ogden
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tuning Tips for a new Server
2011-08-17 01:35 Tuning Tips for a new Server Ogden <lists@darkstatic.com>
2011-08-17 14:44 ` Re: Tuning Tips for a new Server Tomas Vondra <tv@fuzzy.cz>
2011-08-17 16:39 ` Re: Tuning Tips for a new Server Ogden <lists@darkstatic.com>
@ 2011-08-17 18:56 ` Tomas Vondra <tv@fuzzy.cz>
2011-08-17 19:09 ` Re: Tuning Tips for a new Server Ogden <lists@darkstatic.com>
2011-08-17 19:14 ` Re: Tuning Tips for a new Server Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Tomas Vondra @ 2011-08-17 18:56 UTC (permalink / raw)
To: Ogden <lists@darkstatic.com>; +Cc: Tomas Vondra <tv@fuzzy.cz>; pgsql-performance
On 17 Srpen 2011, 18:39, Ogden wrote:
>> Yes, but it greatly depends on the amount of WAL and your workload. If
>> you
>> need to write a lot of WAL data (e.g. during bulk loading), this may
>> significantly improve performance. It may also help when you have a
>> write-heavy workload (a lot of clients updating records, background
>> writer
>> etc.) as that usually means a lot of seeking (while WAL is written
>> sequentially).
>
> The database is about 200Gb so using /usr/local/pgsql/pg_xlog on a virtual
> disk with 100Gb should not be a problem with the disk space should it?
I think you've mentioned the database is on 6 drives, while the other
volume is on 2 drives, right? That makes the OS drive about 3x slower
(just a rough estimate). But if the database drive is used heavily, it
might help to move the xlog directory to the OS disk. See how is the db
volume utilized and if it's fully utilized, try to move the xlog
directory.
The only way to find out is to actualy try it with your workload.
>> What is the size of the database? So those are the new servers? What's
>> the difference compared to the old ones? What is the RAID controller, how
>> much write cache is there?
>
> I am sorry I overlooked specifying this. The database is about 200Gb and
> yes these are new servers which bring more power (RAM, CPU) over the last
> one. The RAID Controller is a Perc H700 and there is 512Mb write cache.
> The servers are Dells.
OK, sounds good although I don't have much experience with this controller.
>>> Currently, I have this for the current system which as 16Gb Ram:
>>>
>>> max_connections = 350
>>>
>>> work_mem = 32MB
>>> maintenance_work_mem = 512MB
>>> wal_buffers = 640kB
>>
>> Anyway if this worked fine for your workload, I don't think you need to
>> change those settings. I'd probably bump up the wal_buffers to 16MB - it
>> might help a bit, definitely won't hurt and it's so little memory it's
>> not
>> worth the effort I guess.
>
> So just increasing the wal_buffers is okay? I thought there would be more
> as the memory in the system is now 4 times as much. Perhaps shared_buffers
> too (down below).
Yes, I was just commenting that particular piece of config. Shared buffers
should be increased too.
>>> # This is what I was helped with before and made reporting queries
>>> blaze
>>> by
>>> seq_page_cost = 1.0
>>> random_page_cost = 3.0
>>> cpu_tuple_cost = 0.5
>>> effective_cache_size = 8192MB
>>
>> Are you sure the cpu_tuple_cost = 0.5 is correct? That seems a bit crazy
>> to me, as it says reading a page sequentially is just twice as expensive
>> as processing it. This value should be abou 100x lower or something like
>> that.
>
> These settings are for the old server, keep in mind. It's a 16GB machine
> (the new one is 64Gb). The value for cpu_tuple_cost should be 0.005? How
> are the other ones?
The default values are like this:
seq_page_cost = 1.0
random_page_cost = 4.0
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
Increasing the cpu_tuple_cost to 0.5 makes it way too expensive I guess,
so the database believes processing two 8kB pages is just as expensive as
reading one from the disk. I guess this change penalizes plans that read a
lot of pages, e.g. sequential scans (and favor index scans etc.). Maybe it
makes sense in your case, I'm just wondering why you set it like that.
>> What are the checkpoint settings (segments, completion target). What
>> about
>> shared buffers?
>
>
> #checkpoint_segments = 3 # in logfile segments, min 1, 16MB
> each
> #checkpoint_timeout = 5min # range 30s-1h
> checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0
> - 1.0 - was 0.5
> #checkpoint_warning = 30s # 0 disables
You need to bump checkpoint segments up, e.g. 64 or maybe even more. This
means how many WAL segments will be available until a checkpoint has to
happen. Checkpoint is a process when dirty buffers from shared buffers are
written to the disk, so it may be very I/O intensive. Each segment is
16MB, so 3 segments is just 48MB of data, while 64 is 1GB.
More checkpoint segments result in longer recovery in case of database
crash (because all the segments since last checkpoint need to be applied).
But it's essential for good write performance.
Completion target seems fine, but I'd consider increasing the timeout too.
> shared_buffers = 4096MB
The usual recommendation is about 25% of RAM for shared buffers, with 64GB
of RAM that is 16GB. And you should increase effective_cache_size too.
See this: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
Tomas
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tuning Tips for a new Server
2011-08-17 01:35 Tuning Tips for a new Server Ogden <lists@darkstatic.com>
2011-08-17 14:44 ` Re: Tuning Tips for a new Server Tomas Vondra <tv@fuzzy.cz>
2011-08-17 16:39 ` Re: Tuning Tips for a new Server Ogden <lists@darkstatic.com>
2011-08-17 18:56 ` Re: Tuning Tips for a new Server Tomas Vondra <tv@fuzzy.cz>
@ 2011-08-17 19:09 ` Ogden <lists@darkstatic.com>
1 sibling, 0 replies; 11+ messages in thread
From: Ogden @ 2011-08-17 19:09 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: pgsql-performance
On Aug 17, 2011, at 1:56 PM, Tomas Vondra wrote:
> On 17 Srpen 2011, 18:39, Ogden wrote:
>>> Yes, but it greatly depends on the amount of WAL and your workload. If
>>> you
>>> need to write a lot of WAL data (e.g. during bulk loading), this may
>>> significantly improve performance. It may also help when you have a
>>> write-heavy workload (a lot of clients updating records, background
>>> writer
>>> etc.) as that usually means a lot of seeking (while WAL is written
>>> sequentially).
>>
>> The database is about 200Gb so using /usr/local/pgsql/pg_xlog on a virtual
>> disk with 100Gb should not be a problem with the disk space should it?
>
> I think you've mentioned the database is on 6 drives, while the other
> volume is on 2 drives, right? That makes the OS drive about 3x slower
> (just a rough estimate). But if the database drive is used heavily, it
> might help to move the xlog directory to the OS disk. See how is the db
> volume utilized and if it's fully utilized, try to move the xlog
> directory.
>
> The only way to find out is to actualy try it with your workload.
Thank you for your help. I just wanted to ask then, for now I should also put the xlog directory in the /var/lib/pgsql directory which is on the RAID container that is over 6 drives. You see, I wanted to put it on the container with the 2 drives because just the OS is installed on it and has the space (about 100Gb free).
But you don't think it will be a problem to put the xlog directory along with everything else on /var/lib/pgsql/data? I had seen someone suggesting separating it for their setup and it sounded like a good idea so I thought why not, but in retrospect and what you are saying with the OS drives being 3x slower, it may be okay just to put them on the 6 drives.
Thoughts?
Thank you once again for your tremendous help
Ogden
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tuning Tips for a new Server
2011-08-17 01:35 Tuning Tips for a new Server Ogden <lists@darkstatic.com>
2011-08-17 14:44 ` Re: Tuning Tips for a new Server Tomas Vondra <tv@fuzzy.cz>
2011-08-17 16:39 ` Re: Tuning Tips for a new Server Ogden <lists@darkstatic.com>
2011-08-17 18:56 ` Re: Tuning Tips for a new Server Tomas Vondra <tv@fuzzy.cz>
@ 2011-08-17 19:14 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-08-17 19:22 ` Re: Tuning Tips for a new Server Ogden <lists@darkstatic.com>
1 sibling, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2011-08-17 19:14 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: Ogden <lists@darkstatic.com>; pgsql-performance
On Wed, Aug 17, 2011 at 12:56 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
>
> I think you've mentioned the database is on 6 drives, while the other
> volume is on 2 drives, right? That makes the OS drive about 3x slower
> (just a rough estimate). But if the database drive is used heavily, it
> might help to move the xlog directory to the OS disk. See how is the db
> volume utilized and if it's fully utilized, try to move the xlog
> directory.
>
> The only way to find out is to actualy try it with your workload.
This is a very important point. I've found on most machines with
hardware caching RAID and 8 or fewer 15k SCSI drives it's just as
fast to put it all on one big RAID-10 and if necessary partition it to
put the pg_xlog on its own file system. After that depending on the
workload you might need a LOT of drives in the pg_xlog dir or just a
pair. Under normal ops many dbs will use only a tiny % of a
dedicated pg_xlog. Then something like a site indexer starts to run,
and writing heavily to the db, and the usage shoots to 100% and it's
the bottleneck.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tuning Tips for a new Server
2011-08-17 01:35 Tuning Tips for a new Server Ogden <lists@darkstatic.com>
2011-08-17 14:44 ` Re: Tuning Tips for a new Server Tomas Vondra <tv@fuzzy.cz>
2011-08-17 16:39 ` Re: Tuning Tips for a new Server Ogden <lists@darkstatic.com>
2011-08-17 18:56 ` Re: Tuning Tips for a new Server Tomas Vondra <tv@fuzzy.cz>
2011-08-17 19:14 ` Re: Tuning Tips for a new Server Scott Marlowe <scott.marlowe@gmail.com>
@ 2011-08-17 19:22 ` Ogden <lists@darkstatic.com>
2011-08-17 19:44 ` Re: Tuning Tips for a new Server Tomas Vondra <tv@fuzzy.cz>
0 siblings, 1 reply; 11+ messages in thread
From: Ogden @ 2011-08-17 19:22 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Tomas Vondra <tv@fuzzy.cz>; pgsql-performance
On Aug 17, 2011, at 2:14 PM, Scott Marlowe wrote:
> On Wed, Aug 17, 2011 at 12:56 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
>>
>> I think you've mentioned the database is on 6 drives, while the other
>> volume is on 2 drives, right? That makes the OS drive about 3x slower
>> (just a rough estimate). But if the database drive is used heavily, it
>> might help to move the xlog directory to the OS disk. See how is the db
>> volume utilized and if it's fully utilized, try to move the xlog
>> directory.
>>
>> The only way to find out is to actualy try it with your workload.
>
> This is a very important point. I've found on most machines with
> hardware caching RAID and 8 or fewer 15k SCSI drives it's just as
> fast to put it all on one big RAID-10 and if necessary partition it to
> put the pg_xlog on its own file system. After that depending on the
> workload you might need a LOT of drives in the pg_xlog dir or just a
> pair. Under normal ops many dbs will use only a tiny % of a
> dedicated pg_xlog. Then something like a site indexer starts to run,
> and writing heavily to the db, and the usage shoots to 100% and it's
> the bottleneck.
I suppose this is my confusion. Or rather I am curious about this. On my current production database the pg_xlog directory is 8Gb (our total database is 200Gb). Does this warrant a totally separate setup (and hardware) than PGDATA?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Tuning Tips for a new Server
2011-08-17 01:35 Tuning Tips for a new Server Ogden <lists@darkstatic.com>
2011-08-17 14:44 ` Re: Tuning Tips for a new Server Tomas Vondra <tv@fuzzy.cz>
2011-08-17 16:39 ` Re: Tuning Tips for a new Server Ogden <lists@darkstatic.com>
2011-08-17 18:56 ` Re: Tuning Tips for a new Server Tomas Vondra <tv@fuzzy.cz>
2011-08-17 19:14 ` Re: Tuning Tips for a new Server Scott Marlowe <scott.marlowe@gmail.com>
2011-08-17 19:22 ` Re: Tuning Tips for a new Server Ogden <lists@darkstatic.com>
@ 2011-08-17 19:44 ` Tomas Vondra <tv@fuzzy.cz>
0 siblings, 0 replies; 11+ messages in thread
From: Tomas Vondra @ 2011-08-17 19:44 UTC (permalink / raw)
To: Ogden <lists@darkstatic.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Tomas Vondra <tv@fuzzy.cz>; pgsql-performance
On 17 Srpen 2011, 21:22, Ogden wrote:
>> This is a very important point. I've found on most machines with
>> hardware caching RAID and 8 or fewer 15k SCSI drives it's just as
>> fast to put it all on one big RAID-10 and if necessary partition it to
>> put the pg_xlog on its own file system. After that depending on the
>> workload you might need a LOT of drives in the pg_xlog dir or just a
>> pair. Under normal ops many dbs will use only a tiny % of a
>> dedicated pg_xlog. Then something like a site indexer starts to run,
>> and writing heavily to the db, and the usage shoots to 100% and it's
>> the bottleneck.
>
> I suppose this is my confusion. Or rather I am curious about this. On my
> current production database the pg_xlog directory is 8Gb (our total
> database is 200Gb). Does this warrant a totally separate setup (and
> hardware) than PGDATA?
This is not about database size, it's about the workload - the way you're
using your database. Even a small database may produce a lot of WAL
segments, if the workload is write-heavy. So it's impossible to recommend
something except to try that on your own.
Tomas
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2011-08-17 19:44 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-17 01:35 Tuning Tips for a new Server Ogden <lists@darkstatic.com>
2011-08-17 13:41 ` Andy Colson <andy@squeakycode.net>
2011-08-17 14:28 ` Ogden <lists@darkstatic.com>
2011-08-17 15:08 ` Tomas Vondra <tv@fuzzy.cz>
2011-08-17 14:44 ` Tomas Vondra <tv@fuzzy.cz>
2011-08-17 16:39 ` Ogden <lists@darkstatic.com>
2011-08-17 18:56 ` Tomas Vondra <tv@fuzzy.cz>
2011-08-17 19:09 ` Ogden <lists@darkstatic.com>
2011-08-17 19:14 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-08-17 19:22 ` Ogden <lists@darkstatic.com>
2011-08-17 19:44 ` Tomas Vondra <tv@fuzzy.cz>
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