pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizing Postgresql server and FreeBSD for heavy read and writes
14+ messages / 7 participants
[nested] [flat]
* Optimizing Postgresql server and FreeBSD for heavy read and writes
@ 2010-02-03 15:10 Amitabh Kant <amitabhkant@gmail.com>
0 siblings, 4 replies; 14+ messages in thread
From: Amitabh Kant @ 2010-02-03 15:10 UTC (permalink / raw)
To: pgsql-performance
Hello
I have a server dedicated for Postgres with the following specs:
RAM 16GB, 146GB SAS (15K) x 4 - RAID 10 with BBU, Dual Xeon E5345 @
2.33GHz
OS: FreeBSD 8.0
It runs multiple (approx 10) databases ranging from 500MB to over 24 GB in
size. All of them are of the same structure, and almost all of them have
very heavy read and writes.
pgtune (http://pgfoundry.org/projects/pgtune/) suggests the settings to be
changed as :
maintenance_work_mem = 960MB # pg_generate_conf wizard 2010-02-03
checkpoint_completion_target = 0.9 # pg_generate_conf wizard 2010-02-03
effective_cache_size = 11GB # pg_generate_conf wizard 2010-02-03
work_mem = 160MB # pg_generate_conf wizard 2010-02-03
wal_buffers = 8MB # pg_generate_conf wizard 2010-02-03
checkpoint_segments = 16 # pg_generate_conf wizard 2010-02-03
shared_buffers = 3840MB # pg_generate_conf wizard 2010-02-03
max_connections = 100 # pg_generate_conf wizard 2010-02-03
While this gives me the changes for postgresql.conf, I am not sure of of
the chnages that I need to make in FreeBSD to support such large memory
allocations. The last time I tried, Postgres refused to start and I had to
fall back to the default settings.
I would appreciate if somebody could point out the sysctl/loader.conf
settings that I need to have in FreeBSD.
With regards
Amitabh Kant
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
@ 2010-02-03 15:12 Amitabh Kant <amitabhkant@gmail.com>
parent: Amitabh Kant <amitabhkant@gmail.com>
3 siblings, 1 reply; 14+ messages in thread
From: Amitabh Kant @ 2010-02-03 15:12 UTC (permalink / raw)
To: pgsql-performance
Forgot to add that I am using Postgres 8.4.2 from the default ports of
FreeBSD.
With regards
Amitabh Kant
On Wed, Feb 3, 2010 at 8:40 PM, Amitabh Kant <amitabhkant@gmail.com> wrote:
> Hello
>
> I have a server dedicated for Postgres with the following specs:
>
> RAM 16GB, 146GB SAS (15K) x 4 - RAID 10 with BBU, Dual Xeon E5345 @
> 2.33GHz
> OS: FreeBSD 8.0
>
> It runs multiple (approx 10) databases ranging from 500MB to over 24 GB in
> size. All of them are of the same structure, and almost all of them have
> very heavy read and writes.
>
> pgtune (http://pgfoundry.org/projects/pgtune/) suggests the settings to be
> changed as :
>
> maintenance_work_mem = 960MB # pg_generate_conf wizard 2010-02-03
> checkpoint_completion_target = 0.9 # pg_generate_conf wizard 2010-02-03
> effective_cache_size = 11GB # pg_generate_conf wizard 2010-02-03
> work_mem = 160MB # pg_generate_conf wizard 2010-02-03
> wal_buffers = 8MB # pg_generate_conf wizard 2010-02-03
> checkpoint_segments = 16 # pg_generate_conf wizard 2010-02-03
> shared_buffers = 3840MB # pg_generate_conf wizard 2010-02-03
> max_connections = 100 # pg_generate_conf wizard 2010-02-03
>
>
> While this gives me the changes for postgresql.conf, I am not sure of of
> the chnages that I need to make in FreeBSD to support such large memory
> allocations. The last time I tried, Postgres refused to start and I had to
> fall back to the default settings.
>
> I would appreciate if somebody could point out the sysctl/loader.conf
> settings that I need to have in FreeBSD.
>
> With regards
>
> Amitabh Kant
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
@ 2010-02-03 16:19 Reid Thompson <reid.thompson@ateb.com>
parent: Amitabh Kant <amitabhkant@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Reid Thompson @ 2010-02-03 16:19 UTC (permalink / raw)
To: Amitabh Kant <amitabhkant@gmail.com>; +Cc: reid.thompson@ateb.com; pgsql-performance
On Wed, 2010-02-03 at 20:42 +0530, Amitabh Kant wrote:
> Forgot to add that I am using Postgres 8.4.2 from the default ports of
> FreeBSD.
start with this page
http://www.postgresql.org/docs/8.4/static/kernel-resources.html
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
@ 2010-02-03 16:35 Ivan Voras <ivoras@freebsd.org>
parent: Amitabh Kant <amitabhkant@gmail.com>
3 siblings, 1 reply; 14+ messages in thread
From: Ivan Voras @ 2010-02-03 16:35 UTC (permalink / raw)
To: pgsql-performance
On 02/03/10 16:10, Amitabh Kant wrote:
> Hello
>
> I have a server dedicated for Postgres with the following specs:
>
> RAM 16GB, 146GB SAS (15K) x 4 - RAID 10 with BBU, Dual Xeon E5345 @
> 2.33GHz
> OS: FreeBSD 8.0
If you really do have "heavy read and write" load on the server, nothing
will save you from the bottleneck of having only 4 drives in the system
(or more accurately: adding more memory will help reads but nothing
helps writes except more drivers or faster (SSD) drives). If you can,
add another 2 drives in RAID 1 and move+symlink the pg_xlog directory to
the new array.
> maintenance_work_mem = 960MB # pg_generate_conf wizard 2010-02-03
> checkpoint_completion_target = 0.9 # pg_generate_conf wizard 2010-02-03
> effective_cache_size = 11GB # pg_generate_conf wizard 2010-02-03
> work_mem = 160MB # pg_generate_conf wizard 2010-02-03
> wal_buffers = 8MB # pg_generate_conf wizard 2010-02-03
> checkpoint_segments = 16 # pg_generate_conf wizard 2010-02-03
> shared_buffers = 3840MB # pg_generate_conf wizard 2010-02-03
> max_connections = 100 # pg_generate_conf wizard 2010-02-03
> I would appreciate if somebody could point out the sysctl/loader.conf
> settings that I need to have in FreeBSD.
Firstly, you need to run a 64-bit version ("amd64") of FreeBSD.
In /boot/loader.conf you will probably need to increase the number of
sysv ipc semaphores:
kern.ipc.semmni=512
kern.ipc.semmns=1024
This depends mostly on the number of connections allowed to the server.
The example values I gave above are more than enough but since this is a
boot-only tunable it is expensive to modify later.
In /etc/sysctl.conf you will need to increase the shared memory sizes,
e.g. for a 3900 MB shared_buffer:
kern.ipc.shmmax=4089446400
This is the maximum shared memory segment size, in bytes.
kern.ipc.shmall=1050000
This is the maximum amount of memory allowed to be used as sysv shared
memory, in 4 kB pages.
If the database contains many objects (tables, indexes, etc.) you may
need to increase the maximum number of open files and the amount of
memory for the directory list cache:
kern.maxfiles=16384
vfs.ufs.dirhash_maxmem=4194304
If you estimate you will have large sequential reads on the database,
you should increase read-ahead count:
vfs.read_max=32
Be sure that soft-updates is enabled on the file system you are using
for data. Ignore all Linux-centric discussions about problems with
journaling and write barriers :)
All settings in /etc/sysctl.conf can be changed at runtime (individually
or by invoking "/etc/rc.d/sysctl restart"), settings in loader.conf are
boot-time only.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
@ 2010-02-03 18:41 Andy Colson <andy@squeakycode.net>
parent: Amitabh Kant <amitabhkant@gmail.com>
3 siblings, 1 reply; 14+ messages in thread
From: Andy Colson @ 2010-02-03 18:41 UTC (permalink / raw)
To: Amitabh Kant <amitabhkant@gmail.com>; pgsql-performance
On 2/3/2010 9:10 AM, Amitabh Kant wrote:
> Hello
>
> I have a server dedicated for Postgres with the following specs:
>
> RAM 16GB, 146GB SAS (15K) x 4 - RAID 10 with BBU, Dual Xeon E5345 @
> 2.33GHz
> OS: FreeBSD 8.0
>
> It runs multiple (approx 10) databases ranging from 500MB to over 24 GB
> in size. All of them are of the same structure, and almost all of them
> have very heavy read and writes.
>
>
> With regards
>
> Amitabh Kant
What problems are you having? Is it slow? Is there something you are
trying to fix, or is this just the first tune up?
> memory allocations. The last time I tried, Postgres refused to start and
> I had to fall back to the default settings.
Its probably upset about the amount of shared mem. There is probably a
way in bsd to set the max amount of shared memory available. A Quick
google turned up:
kern.ipc.shmmax
Dunno if thats right. When you try to start PG, if it cannot allocate
enough shared mem it'll spit out an error message into its log saying
how much it tried to allocate.
Check:
http://archives.postgresql.org/pgsql-admin/2004-06/msg00155.php
> maintenance_work_mem = 960MB # pg_generate_conf wizard 2010-02-03
> checkpoint_completion_target = 0.9 # pg_generate_conf wizard 2010-02-03
> effective_cache_size = 11GB # pg_generate_conf wizard 2010-02-03
> work_mem = 160MB # pg_generate_conf wizard 2010-02-03
> wal_buffers = 8MB # pg_generate_conf wizard 2010-02-03
> checkpoint_segments = 16 # pg_generate_conf wizard 2010-02-03
> shared_buffers = 3840MB # pg_generate_conf wizard 2010-02-03
> max_connections = 100 # pg_generate_conf wizard 2010-02-03
Some of these seem like too much. I'd recommend starting with one or
two and see how it runs. Then increase if you're still slow.
Start with effective_cache_size, shared_buffers and checkpoint_segments.
Wait until very last to play with work_mem and maintenance_work_mem.
-Andy
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
@ 2010-02-03 20:43 Robert Haas <robertmhaas@gmail.com>
parent: Amitabh Kant <amitabhkant@gmail.com>
3 siblings, 1 reply; 14+ messages in thread
From: Robert Haas @ 2010-02-03 20:43 UTC (permalink / raw)
To: Amitabh Kant <amitabhkant@gmail.com>; +Cc: pgsql-performance
On Wed, Feb 3, 2010 at 10:10 AM, Amitabh Kant <amitabhkant@gmail.com> wrote:
> work_mem = 160MB # pg_generate_conf wizard 2010-02-03
Overall these settings look sane, but this one looks like an
exception. That is an enormous value for that parameter...
...Robert
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
@ 2010-02-03 21:59 Greg Smith <greg@2ndquadrant.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Greg Smith @ 2010-02-03 21:59 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Amitabh Kant <amitabhkant@gmail.com>; pgsql-performance
Robert Haas wrote:
> On Wed, Feb 3, 2010 at 10:10 AM, Amitabh Kant <amitabhkant@gmail.com> wrote:
>
>> work_mem = 160MB # pg_generate_conf wizard 2010-02-03
>>
>
> Overall these settings look sane, but this one looks like an
> exception. That is an enormous value for that parameter...
>
Yeah, I think I need to retune the suggestions for that parameter. The
idea behind the tuning profile used in the "web" and "OLTP" setups is
that you're unlikely to have all the available connections doing
something involving sorting at the same time with those workloads, and
when it does happen you want it to use the fastest approach possible
even if that takes more RAM so the client waiting for a response is more
likely to get one on time. That's why the work_mem figure in those
situations is set very aggressively: total_mem / connections, so on a
16GB server that comes out to the 160MB seen here. I'm going to adjust
that so that it's capped a little below (total_mem - shared_buffers) /
connections instead.
pgtune just got a major bit of refactoring recently from Matt Harrison
to make it more Python-esque, and I'll be pushing toward an official 1.0
with all the major loose ends cleaned up and an adjusted tuning model
that will be available before 9.0 ships. I'm seeing enough people
interested in it now to justify putting another block of work into
improving it.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
@ 2010-02-04 08:59 Amitabh Kant <amitabhkant@gmail.com>
parent: Reid Thompson <reid.thompson@ateb.com>
0 siblings, 0 replies; 14+ messages in thread
From: Amitabh Kant @ 2010-02-04 08:59 UTC (permalink / raw)
To: reid.thompson@ateb.com; +Cc: pgsql-performance
On Wed, Feb 3, 2010 at 9:49 PM, Reid Thompson <reid.thompson@ateb.com>wrote:
> On Wed, 2010-02-03 at 20:42 +0530, Amitabh Kant wrote:
> > Forgot to add that I am using Postgres 8.4.2 from the default ports of
> > FreeBSD.
>
> start with this page
> http://www.postgresql.org/docs/8.4/static/kernel-resources.html
>
>
I somehow missed the doc. Thanks Reid.
With regards
Amitabh Kant
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
@ 2010-02-04 09:02 Amitabh Kant <amitabhkant@gmail.com>
parent: Ivan Voras <ivoras@freebsd.org>
0 siblings, 2 replies; 14+ messages in thread
From: Amitabh Kant @ 2010-02-04 09:02 UTC (permalink / raw)
To: Ivan Voras <ivoras@freebsd.org>; +Cc: pgsql-performance
On Wed, Feb 3, 2010 at 10:05 PM, Ivan Voras <ivoras@freebsd.org> wrote:
> On 02/03/10 16:10, Amitabh Kant wrote:
>
>> Hello
>>
>> I have a server dedicated for Postgres with the following specs:
>>
>> RAM 16GB, 146GB SAS (15K) x 4 - RAID 10 with BBU, Dual Xeon E5345 @
>> 2.33GHz
>> OS: FreeBSD 8.0
>>
>
> If you really do have "heavy read and write" load on the server, nothing
> will save you from the bottleneck of having only 4 drives in the system (or
> more accurately: adding more memory will help reads but nothing helps writes
> except more drivers or faster (SSD) drives). If you can, add another 2
> drives in RAID 1 and move+symlink the pg_xlog directory to the new array.
>
>
Can't do anything about this server now, but would surely keep in mind
before upgrading other servers. Would you recommend the same speed
drives(15K SAS) for RAID 1, or would a slower drive also work here (10K SAS
or even SATA II)?
>
> maintenance_work_mem = 960MB # pg_generate_conf wizard 2010-02-03
>> checkpoint_completion_target = 0.9 # pg_generate_conf wizard 2010-02-03
>> effective_cache_size = 11GB # pg_generate_conf wizard 2010-02-03
>> work_mem = 160MB # pg_generate_conf wizard 2010-02-03
>> wal_buffers = 8MB # pg_generate_conf wizard 2010-02-03
>> checkpoint_segments = 16 # pg_generate_conf wizard 2010-02-03
>> shared_buffers = 3840MB # pg_generate_conf wizard 2010-02-03
>> max_connections = 100 # pg_generate_conf wizard 2010-02-03
>>
>
> I would appreciate if somebody could point out the sysctl/loader.conf
>> settings that I need to have in FreeBSD.
>>
>
> Firstly, you need to run a 64-bit version ("amd64") of FreeBSD.
>
>
Yes, its running amd64 arch.
> In /boot/loader.conf you will probably need to increase the number of sysv
> ipc semaphores:
>
> kern.ipc.semmni=512
> kern.ipc.semmns=1024
>
> This depends mostly on the number of connections allowed to the server. The
> example values I gave above are more than enough but since this is a
> boot-only tunable it is expensive to modify later.
>
> In /etc/sysctl.conf you will need to increase the shared memory sizes, e.g.
> for a 3900 MB shared_buffer:
>
> kern.ipc.shmmax=4089446400
> This is the maximum shared memory segment size, in bytes.
>
> kern.ipc.shmall=1050000
> This is the maximum amount of memory allowed to be used as sysv shared
> memory, in 4 kB pages.
>
> If the database contains many objects (tables, indexes, etc.) you may need
> to increase the maximum number of open files and the amount of memory for
> the directory list cache:
>
> kern.maxfiles=16384
> vfs.ufs.dirhash_maxmem=4194304
>
> If you estimate you will have large sequential reads on the database, you
> should increase read-ahead count:
>
> vfs.read_max=32
>
> Be sure that soft-updates is enabled on the file system you are using for
> data. Ignore all Linux-centric discussions about problems with journaling
> and write barriers :)
>
> All settings in /etc/sysctl.conf can be changed at runtime (individually or
> by invoking "/etc/rc.d/sysctl restart"), settings in loader.conf are
> boot-time only.
>
Thanks Ivan. That's a great explanation of the variables involved.
With regards
Amitabh Kant
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
@ 2010-02-04 09:16 Amitabh Kant <amitabhkant@gmail.com>
parent: Andy Colson <andy@squeakycode.net>
0 siblings, 0 replies; 14+ messages in thread
From: Amitabh Kant @ 2010-02-04 09:16 UTC (permalink / raw)
To: Andy Colson <andy@squeakycode.net>; +Cc: pgsql-performance
On Thu, Feb 4, 2010 at 12:11 AM, Andy Colson <andy@squeakycode.net> wrote:
> On 2/3/2010 9:10 AM, Amitabh Kant wrote:
>
>> Hello
>>
>> I have a server dedicated for Postgres with the following specs:
>>
>> RAM 16GB, 146GB SAS (15K) x 4 - RAID 10 with BBU, Dual Xeon E5345 @
>> 2.33GHz
>> OS: FreeBSD 8.0
>>
>> It runs multiple (approx 10) databases ranging from 500MB to over 24 GB
>> in size. All of them are of the same structure, and almost all of them
>> have very heavy read and writes.
>>
>>
>> With regards
>>
>> Amitabh Kant
>>
>
> What problems are you having? Is it slow? Is there something you are
> trying to fix, or is this just the first tune up?
>
This is the first tune up. The system has worked pretty fine till now, but
it does lag once in a while, and I would like to optimize it before it
becomes a bigger issue.
>
> > memory allocations. The last time I tried, Postgres refused to start and
> > I had to fall back to the default settings.
>
> Its probably upset about the amount of shared mem. There is probably a way
> in bsd to set the max amount of shared memory available. A Quick google
> turned up:
>
> kern.ipc.shmmax
>
> Dunno if thats right. When you try to start PG, if it cannot allocate
> enough shared mem it'll spit out an error message into its log saying how
> much it tried to allocate.
>
> Check:
> http://archives.postgresql.org/pgsql-admin/2004-06/msg00155.php
>
>
>
>
>
> > maintenance_work_mem = 960MB # pg_generate_conf wizard 2010-02-03
> > checkpoint_completion_target = 0.9 # pg_generate_conf wizard 2010-02-03
> > effective_cache_size = 11GB # pg_generate_conf wizard 2010-02-03
> > work_mem = 160MB # pg_generate_conf wizard 2010-02-03
> > wal_buffers = 8MB # pg_generate_conf wizard 2010-02-03
> > checkpoint_segments = 16 # pg_generate_conf wizard 2010-02-03
> > shared_buffers = 3840MB # pg_generate_conf wizard 2010-02-03
> > max_connections = 100 # pg_generate_conf wizard 2010-02-03
>
> Some of these seem like too much. I'd recommend starting with one or two
> and see how it runs. Then increase if you're still slow.
>
> Start with effective_cache_size, shared_buffers and checkpoint_segments.
>
> Wait until very last to play with work_mem and maintenance_work_mem.
>
>
> -Andy
>
I would keep that in mind. Thanks Andy
With regards
Amitabh
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
@ 2010-02-04 09:40 Ivan Voras <ivoras@freebsd.org>
parent: Amitabh Kant <amitabhkant@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Ivan Voras @ 2010-02-04 09:40 UTC (permalink / raw)
To: Amitabh Kant <amitabhkant@gmail.com>; +Cc: pgsql-performance
On 4 February 2010 10:02, Amitabh Kant <amitabhkant@gmail.com> wrote:
> On Wed, Feb 3, 2010 at 10:05 PM, Ivan Voras <ivoras@freebsd.org> wrote:
>>
>> On 02/03/10 16:10, Amitabh Kant wrote:
>>>
>>> Hello
>>>
>>> I have a server dedicated for Postgres with the following specs:
>>>
>>> RAM 16GB, 146GB SAS (15K) x 4 - RAID 10 with BBU, Dual Xeon E5345 @
>>> 2.33GHz
>>> OS: FreeBSD 8.0
>>
>> If you really do have "heavy read and write" load on the server, nothing
>> will save you from the bottleneck of having only 4 drives in the system (or
>> more accurately: adding more memory will help reads but nothing helps writes
>> except more drivers or faster (SSD) drives). If you can, add another 2
>> drives in RAID 1 and move+symlink the pg_xlog directory to the new array.
>
> Can't do anything about this server now, but would surely keep in mind
> before upgrading other servers. Would you recommend the same speed
> drives(15K SAS) for RAID 1, or would a slower drive also work here (10K SAS
> or even SATA II)?
Again, it depends on your load. It would probably be best if they are
approximately the same speed; the location of pg_xlog will dictate
your write (UPDATE / INSERT / CREATE) speed.
Writes to your database go like this: the data is first written to the
WAL (this is the pg_xlog directory - the transaction log), then it is
read and written to the "main" database. If the main database is very
busy reading, transfers from WAL to the database will be slower.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
@ 2010-02-04 09:52 Amitabh Kant <amitabhkant@gmail.com>
parent: Ivan Voras <ivoras@freebsd.org>
0 siblings, 0 replies; 14+ messages in thread
From: Amitabh Kant @ 2010-02-04 09:52 UTC (permalink / raw)
To: Ivan Voras <ivoras@freebsd.org>; +Cc: pgsql-performance
On Thu, Feb 4, 2010 at 3:10 PM, Ivan Voras <ivoras@freebsd.org> wrote:
> On 4 February 2010 10:02, Amitabh Kant <amitabhkant@gmail.com> wrote:
> > On Wed, Feb 3, 2010 at 10:05 PM, Ivan Voras <ivoras@freebsd.org> wrote:
> >>
> >> On 02/03/10 16:10, Amitabh Kant wrote:
> >>>
> >>> Hello
> >>>
> >>> I have a server dedicated for Postgres with the following specs:
> >>>
> >>> RAM 16GB, 146GB SAS (15K) x 4 - RAID 10 with BBU, Dual Xeon E5345 @
> >>> 2.33GHz
> >>> OS: FreeBSD 8.0
> >>
> >> If you really do have "heavy read and write" load on the server, nothing
> >> will save you from the bottleneck of having only 4 drives in the system
> (or
> >> more accurately: adding more memory will help reads but nothing helps
> writes
> >> except more drivers or faster (SSD) drives). If you can, add another 2
> >> drives in RAID 1 and move+symlink the pg_xlog directory to the new
> array.
> >
> > Can't do anything about this server now, but would surely keep in mind
> > before upgrading other servers. Would you recommend the same speed
> > drives(15K SAS) for RAID 1, or would a slower drive also work here (10K
> SAS
> > or even SATA II)?
>
> Again, it depends on your load. It would probably be best if they are
> approximately the same speed; the location of pg_xlog will dictate
> your write (UPDATE / INSERT / CREATE) speed.
>
> Writes to your database go like this: the data is first written to the
> WAL (this is the pg_xlog directory - the transaction log), then it is
> read and written to the "main" database. If the main database is very
> busy reading, transfers from WAL to the database will be slower.
>
Thanks Ivan. I have to go in for upgrade of couple of more servers. I will
be going in for RAID 1 (OS + pg_xlog ) and RAID 10 (Pgsql data), all of them
of same speed.
With regards
Amitabh Kant
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
@ 2010-02-04 10:07 Amitabh Kant <amitabhkant@gmail.com>
parent: Greg Smith <greg@2ndquadrant.com>
0 siblings, 0 replies; 14+ messages in thread
From: Amitabh Kant @ 2010-02-04 10:07 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; pgsql-performance
On Thu, Feb 4, 2010 at 3:29 AM, Greg Smith <greg@2ndquadrant.com> wrote:
> Robert Haas wrote:
>
> On Wed, Feb 3, 2010 at 10:10 AM, Amitabh Kant <amitabhkant@gmail.com> <amitabhkant@gmail.com> wrote:
>
>
> work_mem = 160MB # pg_generate_conf wizard 2010-02-03
>
>
> Overall these settings look sane, but this one looks like an
> exception. That is an enormous value for that parameter...
>
>
>
> Yeah, I think I need to retune the suggestions for that parameter. The
> idea behind the tuning profile used in the "web" and "OLTP" setups is that
> you're unlikely to have all the available connections doing something
> involving sorting at the same time with those workloads, and when it does
> happen you want it to use the fastest approach possible even if that takes
> more RAM so the client waiting for a response is more likely to get one on
> time. That's why the work_mem figure in those situations is set very
> aggressively: total_mem / connections, so on a 16GB server that comes out
> to the 160MB seen here. I'm going to adjust that so that it's capped a
> little below (total_mem - shared_buffers) / connections instead.
>
Thanks Robert & Greg. From what others have suggested, I am going in for
the following changes:
/boot/loader.conf:
kern.ipc.semmni=512
kern.ipc.semmns=1024
kern.ipc.semmnu=512
/etc/sysctl.conf:
kern.ipc.shm_use_phys=1
kern.ipc.shmmax=4089446400
kern.ipc.shmall=1050000
kern.maxfiles=16384
kern.ipc.semmsl=1024
kern.ipc.semmap=512
vfs.ufs.dirhash_maxmem=4194304
vfs.read_max=32
/usr/local/pgsql/data/postgresql.conf:
maintenance_work_mem = 960MB # pg_generate_conf wizard
2010-02-03
checkpoint_completion_target = 0.9 # pg_generate_conf wizard
2010-02-03
effective_cache_size = 11GB # pg_generate_conf wizard
2010-02-03
work_mem = 110MB # pg_generate_conf wizard
2010-02-03 Reduced as per Robert/Greg suggestions
wal_buffers = 8MB # pg_generate_conf wizard
2010-02-03
checkpoint_segments = 16 # pg_generate_conf wizard
2010-02-03
shared_buffers = 3840MB # pg_generate_conf wizard
2010-02-03
max_connections = 100 # pg_generate_conf wizard
2010-02-03
Hope this works out good in my case.
With regards
Amitabh Kant
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Optimizing Postgresql server and FreeBSD for heavy read and writes
@ 2010-02-04 11:19 Matthew Wakeling <matthew@flymine.org>
parent: Amitabh Kant <amitabhkant@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Matthew Wakeling @ 2010-02-04 11:19 UTC (permalink / raw)
To: Amitabh Kant <amitabhkant@gmail.com>; +Cc: Ivan Voras <ivoras@freebsd.org>; pgsql-performance
On Thu, 4 Feb 2010, Amitabh Kant wrote:
> On Wed, Feb 3, 2010 at 10:05 PM, Ivan Voras <ivoras@freebsd.org> wrote:
>> If you can, add another 2 drives in RAID 1 and move+symlink the pg_xlog
>> directory to the new array.
>
> Can't do anything about this server now, but would surely keep in mind
> before upgrading other servers. Would you recommend the same speed
> drives(15K SAS) for RAID 1, or would a slower drive also work here (10K SAS
> or even SATA II)?
The performance requirements for the WAL are significantly lower than for
the main database. This is for two reasons - firstly the WAL is
write-only, and has no other activity. The WAL only gets read again in the
event of a crash. Secondly, writes to the WAL are sequential writes, which
is the fastest mode of operation for a disc, whereas the main database
discs will have to handle random access.
The main thing you need to make sure of is that the WAL is on a disc
system that has a battery-backed up cache. That way, it will be able to
handle the high rate of fsyncs that the WAL generates, and the cache will
convert that into a simple sequential write. Otherwise, you will be
limited to one fsync every 5ms (or whatever the access speed of your WAL
discs is).
If you make sure of that, then there is no reason to get expensive fast
discs for the WAL at all (assuming they are expensive enough to not lie
about flushing writes properly).
Matthew
--
So, given 'D' is undeclared too, with a default of zero, C++ is equal to D.
mnw21, commenting on the "Surely the value of C++ is zero, but C is now 1"
response to "No, C++ isn't equal to D. 'C' is undeclared [...] C++ should
really be called 1" response to "C++ -- shouldn't it be called D?"
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2010-02-04 11:19 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-03 15:10 Optimizing Postgresql server and FreeBSD for heavy read and writes Amitabh Kant <amitabhkant@gmail.com>
2010-02-03 15:12 ` Amitabh Kant <amitabhkant@gmail.com>
2010-02-03 16:19 ` Re: Re: Optimizing Postgresql server and FreeBSD for heavy read and writes Reid Thompson <reid.thompson@ateb.com>
2010-02-04 08:59 ` Amitabh Kant <amitabhkant@gmail.com>
2010-02-03 16:35 ` Ivan Voras <ivoras@freebsd.org>
2010-02-04 09:02 ` Amitabh Kant <amitabhkant@gmail.com>
2010-02-04 09:40 ` Ivan Voras <ivoras@freebsd.org>
2010-02-04 09:52 ` Amitabh Kant <amitabhkant@gmail.com>
2010-02-04 11:19 ` Re: Optimizing Postgresql server and FreeBSD for heavy read and writes Matthew Wakeling <matthew@flymine.org>
2010-02-03 18:41 ` Andy Colson <andy@squeakycode.net>
2010-02-04 09:16 ` Amitabh Kant <amitabhkant@gmail.com>
2010-02-03 20:43 ` Robert Haas <robertmhaas@gmail.com>
2010-02-03 21:59 ` Re: Optimizing Postgresql server and FreeBSD for heavy read and writes Greg Smith <greg@2ndquadrant.com>
2010-02-04 10:07 ` Amitabh Kant <amitabhkant@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