Обсуждение: postgresql-8.0.1 performance tuning

Поиск
Список
Период
Сортировка

postgresql-8.0.1 performance tuning

От
"Martin Fandel"
Дата:
Hi @ all,

i'm trying to tune my postgresql-db but i don't know if the values are right
set.

I use the following environment for the postgres-db:

######### Hardware ############
cpu: 2x P4 3Ghz
ram: 1024MB DDR 266Mhz

partitions:
/dev/sda3              23G  9,6G   13G  44% /
/dev/sda1              11G  156M  9,9G   2% /var
/dev/sdb1              69G   13G   57G  19% /var/lib/pgsql

/dev/sda is in raid 1  (2x 35GB / 10000upm / sca)
/dev/sdb is in raid 10 (4x 35GB / 10000upm / sca)
######### /Hardware ############

######### Config ############
/etc/sysctl.conf:
kernel.shmall = 786432000
kernel.shmmax = 786432000

/etc/fstab:
/dev/sdb1 /var/lib/pgsql reiserfs acl,user_xattr,noatime,data=writeback 1 2

/var/lib/pgsql/data/postgresql.conf
superuser_reserved_connections  = 2
shared_buffers                  = 3000
work_mem                        = 131072
maintenance_work_mem            = 131072
max_stack_depth                 = 2048
max_fsm_pages                   = 20000
max_fsm_relations               = 1000
max_files_per_process           = 1000
vacuum_cost_delay               = 10
vacuum_cost_page_hit            = 1
vacuum_cost_page_miss           = 10
vacuum_cost_page_dirty          = 20
vacuum_cost_limit               = 200
bgwriter_delay                  = 200
bgwriter_percent                = 1
bgwriter_maxpages               = 100
fsync                           = true
wal_sync_method                 = fsync
wal_buffers                     = 64
commit_delay                    = 0
commit_siblings                 = 5
checkpoint_segments             = 256
checkpoint_timeout              = 900
checkpoint_warning              = 30
effective_cache_size            = 10000
random_page_cost                = 4
cpu_tuple_cost                  = 0.01
cpu_index_tuple_cost            = 0.001
cpu_operator_cost               = 0.0025
geqo                            = true
geqo_threshold                  = 12
geqo_effort                     = 5
geqo_pool_size                  = 0
geqo_generations                = 0
geqo_selection_bias             = 2.0
deadlock_timeout                = 1000
max_locks_per_transaction       = 64
######### /Config ############

######### Transactions ############
we have about 115-300 transactions/min in about 65 tables.
######### /Transactions ############

I'm really new at using postgres. So i need some experience to set this
parameters in the postgresql- and the system-config. I can't find standard
calculations for this. :/ The postgresql-documentation doesn't help me to
set the best values for this.

The database must be high-availble. I configured rsync to sync the complete
/var/lib/pgsql-directory to my hot-standby. On the hotstandby i will make the
dumps of the database to improve the performance of the master-db.

In my tests the synchronization works fine. I synchronised the hole directory
and restarted the database of the hotstandby. While restarting, postgresql turned
back the old (not archived) wals and the database of my hotstandby was
consistent. Is this solution recommended? Or must i use archived wal's with
real system-snapshots?

best regards,

Martin Fandel

Re: postgresql-8.0.1 performance tuning

От
John A Meinel
Дата:
Martin Fandel wrote:

> Hi @ all,
>
> i'm trying to tune my postgresql-db but i don't know if the values are
> right
> set.
>
> I use the following environment for the postgres-db:
>
> ######### Hardware ############
> cpu: 2x P4 3Ghz
> ram: 1024MB DDR 266Mhz
>
> partitions:
> /dev/sda3              23G  9,6G   13G  44% /
> /dev/sda1              11G  156M  9,9G   2% /var
> /dev/sdb1              69G   13G   57G  19% /var/lib/pgsql
>
> /dev/sda is in raid 1  (2x 35GB / 10000upm / sca)
> /dev/sdb is in raid 10 (4x 35GB / 10000upm / sca)
> ######### /Hardware ############

You probably want to put the pg_xlog file onto /dev/sda rather than
having it in /dev/sdb. Having it separate from the data usually boosts
performance a lot. I believe you can just mv it to a different
directory, and then recreate it as a symlink. (Stop the database first :)

>
> ######### Config ############
> /etc/sysctl.conf:
> kernel.shmall = 786432000
> kernel.shmmax = 786432000
>
Not really sure about these two.

> /etc/fstab:
> /dev/sdb1 /var/lib/pgsql reiserfs
> acl,user_xattr,noatime,data=writeback 1 2
>
Seems decent.

> /var/lib/pgsql/data/postgresql.conf
> superuser_reserved_connections  = 2
> shared_buffers                  = 3000
> work_mem                        = 131072
> maintenance_work_mem            = 131072

These both seem pretty large. But it depends on how many concurrent
connections doing sorting/hashing/etc you expect. If you are only
expecting 1 connection, these are probably fine. Otherwise with 1GB of
RAM I would probably make work_mem more like 4096/8192.
Remember, running out of work_mem means postgres will spill to disk,
slowing that query. Running out of RAM causes the system to swap, making
everything slow.

> max_stack_depth                 = 2048
> max_fsm_pages                   = 20000
> max_fsm_relations               = 1000
> max_files_per_process           = 1000
> vacuum_cost_delay               = 10
> vacuum_cost_page_hit            = 1
> vacuum_cost_page_miss           = 10
> vacuum_cost_page_dirty          = 20
> vacuum_cost_limit               = 200
> bgwriter_delay                  = 200
> bgwriter_percent                = 1
> bgwriter_maxpages               = 100
> fsync                           = true
> wal_sync_method                 = fsync
> wal_buffers                     = 64
> commit_delay                    = 0
> commit_siblings                 = 5
> checkpoint_segments             = 256
> checkpoint_timeout              = 900
> checkpoint_warning              = 30
> effective_cache_size            = 10000
> random_page_cost                = 4
> cpu_tuple_cost                  = 0.01
> cpu_index_tuple_cost            = 0.001
> cpu_operator_cost               = 0.0025
> geqo                            = true
> geqo_threshold                  = 12
> geqo_effort                     = 5
> geqo_pool_size                  = 0
> geqo_generations                = 0
> geqo_selection_bias             = 2.0
> deadlock_timeout                = 1000
> max_locks_per_transaction       = 64
> ######### /Config ############
>
> ######### Transactions ############
> we have about 115-300 transactions/min in about 65 tables.
> ######### /Transactions ############
>
> I'm really new at using postgres. So i need some experience to set this
> parameters in the postgresql- and the system-config. I can't find standard
> calculations for this. :/ The postgresql-documentation doesn't help me to
> set the best values for this.
>
> The database must be high-availble. I configured rsync to sync the
> complete
> /var/lib/pgsql-directory to my hot-standby. On the hotstandby i will
> make the
> dumps of the database to improve the performance of the master-db.
>
I didn't think an rsync was completely valid. Probably you should look
more into Slony.
http://slony.info

It is a single-master asynchronous replication system. I believe it is
pretty easy to setup, and does what you really want.

> In my tests the synchronization works fine. I synchronised the hole
> directory
> and restarted the database of the hotstandby. While restarting,
> postgresql turned
> back the old (not archived) wals and the database of my hotstandby was
> consistent. Is this solution recommended? Or must i use archived wal's
> with
> real system-snapshots?
>
> best regards,
>
> Martin Fandel

John
=:->


Вложения

Re: postgresql-8.0.1 performance tuning

От
Cosimo Streppone
Дата:
Martin Fandel wrote:

> i'm trying to tune my postgresql-db but i don't know if the values are
> I use the following environment for the postgres-db:

I assumed you're running Linux here, you don't mention it.

> ######### Hardware ############
> cpu: 2x P4 3Ghz
> ram: 1024MB DDR 266Mhz

I think 1Gb RAM is quite minimal, nowadays.
Read below.

> partitions:
> /dev/sda3              23G  9,6G   13G  44% /
> /dev/sda1              11G  156M  9,9G   2% /var
> /dev/sdb1              69G   13G   57G  19% /var/lib/pgsql
>
> /dev/sda is in raid 1  (2x 35GB / 10000upm / sca)
> /dev/sdb is in raid 10 (4x 35GB / 10000upm / sca)

I've seen good performance boost (and machine load lowered)
switching to 15k rpm disks.

> ######### Config ############
> /etc/sysctl.conf:
> kernel.shmall = 786432000
> kernel.shmmax = 786432000

I think you have a problem here.
kernel.shmmax should *not* be set to an amount of RAM, but
to maximum number of shared memory pages, which on a typical linux system
is 4kb. Google around:

   http://www.google.com/search?q=kernel.shmall+tuning+postgresql+shared+memory

> /etc/fstab:
> /dev/sdb1 /var/lib/pgsql reiserfs acl,user_xattr,noatime,data=writeback 1 2

I use similar settings on ext3 (which I'm told it is slower than reiser
or xfs or jfs).

I indicate the values I use for a machine with 4Gb RAM
and more 15 krpm disks but layout similar to yours.
(3 x RAID1 arrays for os, logs, ... and 1 x RAID10 array with 12 disks)

For Pg configuration (others please comment on these values,
it is invaluable to have feedback from this list).

> /var/lib/pgsql/data/postgresql.conf
> superuser_reserved_connections  = 2
> shared_buffers                  = 3000
16384

> work_mem                        = 131072
32768

> maintenance_work_mem            = 131072
262144

> max_fsm_pages                   = 20000
200000

> fsync                           = true
false

> commit_delay                    = 0
> commit_siblings                 = 5
If you have an high transactions volume, you should
really investigate on these ones.

> effective_cache_size            = 10000
40000

> random_page_cost                = 4
Check out for unwanted "seq scans". If you have really fast
disks, you should experiment lowering a little this parameter.

> max_locks_per_transaction       = 64
512

> I'm really new at using postgres. So i need some experience to set this
> parameters in the postgresql- and the system-config. I can't find standard
> calculations for this. :/ The postgresql-documentation doesn't help me to
> set the best values for this.

There's no such thing as "standard calculations" :-)

> The database must be high-availble. I configured rsync to sync the complete
> /var/lib/pgsql-directory to my hot-standby
 > [...]
> In my tests the synchronization works fine. I synchronised the hole
> consistent.
 > [...]
 > Is this solution recommended? Or must i use archived wal's with
> real system-snapshots?

In some situations, I also used rsync to do the job.
Obviously, always stop the postmaster before syncing.

Maybe you can look at "slony", if you haven't yet.

   http://www.slony.info

--
Cosimo

Re: postgresql-8.0.1 performance tuning

От
Mark Kirkwood
Дата:
Cosimo Streppone wrote:
> ######### Config ############
>> /etc/sysctl.conf:
>> kernel.shmall = 786432000
>> kernel.shmmax = 786432000
>
>
> I think you have a problem here.
> kernel.shmmax should *not* be set to an amount of RAM, but
> to maximum number of shared memory pages, which on a typical linux system
> is 4kb. Google around:
>
>
>

This is somewhat confusing :

kernel.shmmax is in bytes (max single segment size)
kernel.shmall is in (4k) pages (max system wide allocated segment pages)

cheers

Mark



Re: postgresql-8.0.1 performance tuning

От
Cosimo Streppone
Дата:
Mark Kirkwood ha scritto:

> Cosimo Streppone wrote:
>
>> ######### Config ############
>>
>>> /etc/sysctl.conf:
>>> kernel.shmall = 786432000
>>> kernel.shmmax = 786432000
>>
>> I think you have a problem here.
>> kernel.shmmax should *not* be set to an amount of RAM, but

Sorry, I thought "shmall" but written "shmmax".
Thanks Mark!

--
Cosimo

Re: postgresql-8.0.1 performance tuning

От
Mark Kirkwood
Дата:
Cosimo Streppone wrote:
> Mark Kirkwood ha scritto:
>
>> Cosimo Streppone wrote:
>>
>>> ######### Config ############
>>>
>>>> /etc/sysctl.conf:
>>>> kernel.shmall = 786432000
>>>> kernel.shmmax = 786432000
>>>
>>>
>>> I think you have a problem here.
>>> kernel.shmmax should *not* be set to an amount of RAM, but
>
>
> Sorry, I thought "shmall" but written "shmmax".
> Thanks Mark!
>

Hehe - happens to me all the time!

On the shmall front - altho there is *probably* no real performance
impact setting it to the same as shmmax (i.e. allowing 4096 allocations
of size shmmax!), it is overkill. In addition it does allow for a DOS by
a program that allocates thousands of segments (or somehow starts
thousands of Pg servers on different ports...)!

For a dedicated Pg server I would size shmall using a calculation along
the lines of:

shmall = (no. of postgresql servers) * (shmmax/4096)


If there are other daemons on the box that need to use shared memory,
then add their likely requirements to shmall too!

cheers

Mark

Re: postgresql-8.0.1 performance tuning

От
"Martin Fandel"
Дата:
Hi John,

thank you very much for the answer :). I moved the pg_xlog to another
partition and made a symlink to it. Know the database is much more
faster than before. A sample select which was finished in 68seconds
before, is now finished in 58seconds :).

I will test the other changes today also and will write a feedback
after testing. :)

Thanks a lot. I'm very confusing to tuning the postgresql-db. #:-)

best regards
Martin


Am Dienstag, den 31.05.2005, 13:46 -0500 schrieb John A Meinel:
> Martin Fandel wrote:
>
> > Hi @ all,
> >
> > i'm trying to tune my postgresql-db but i don't know if the values are
> > right
> > set.
> >
> > I use the following environment for the postgres-db:
> >
> > ######### Hardware ############
> > cpu: 2x P4 3Ghz
> > ram: 1024MB DDR 266Mhz
> >
> > partitions:
> > /dev/sda3              23G  9,6G   13G  44% /
> > /dev/sda1              11G  156M  9,9G   2% /var
> > /dev/sdb1              69G   13G   57G  19% /var/lib/pgsql
> >
> > /dev/sda is in raid 1  (2x 35GB / 10000upm / sca)
> > /dev/sdb is in raid 10 (4x 35GB / 10000upm / sca)
> > ######### /Hardware ############
>
> You probably want to put the pg_xlog file onto /dev/sda rather than
> having it in /dev/sdb. Having it separate from the data usually boosts
> performance a lot. I believe you can just mv it to a different
> directory, and then recreate it as a symlink. (Stop the database first :)
>
> >
> > ######### Config ############
> > /etc/sysctl.conf:
> > kernel.shmall = 786432000
> > kernel.shmmax = 786432000
> >
> Not really sure about these two.
>
> > /etc/fstab:
> > /dev/sdb1 /var/lib/pgsql reiserfs
> > acl,user_xattr,noatime,data=writeback 1 2
> >
> Seems decent.
>
> > /var/lib/pgsql/data/postgresql.conf
> > superuser_reserved_connections  = 2
> > shared_buffers                  = 3000
> > work_mem                        = 131072
> > maintenance_work_mem            = 131072
>
> These both seem pretty large. But it depends on how many concurrent
> connections doing sorting/hashing/etc you expect. If you are only
> expecting 1 connection, these are probably fine. Otherwise with 1GB of
> RAM I would probably make work_mem more like 4096/8192.
> Remember, running out of work_mem means postgres will spill to disk,
> slowing that query. Running out of RAM causes the system to swap, making
> everything slow.
>
> > max_stack_depth                 = 2048
> > max_fsm_pages                   = 20000
> > max_fsm_relations               = 1000
> > max_files_per_process           = 1000
> > vacuum_cost_delay               = 10
> > vacuum_cost_page_hit            = 1
> > vacuum_cost_page_miss           = 10
> > vacuum_cost_page_dirty          = 20
> > vacuum_cost_limit               = 200
> > bgwriter_delay                  = 200
> > bgwriter_percent                = 1
> > bgwriter_maxpages               = 100
> > fsync                           = true
> > wal_sync_method                 = fsync
> > wal_buffers                     = 64
> > commit_delay                    = 0
> > commit_siblings                 = 5
> > checkpoint_segments             = 256
> > checkpoint_timeout              = 900
> > checkpoint_warning              = 30
> > effective_cache_size            = 10000
> > random_page_cost                = 4
> > cpu_tuple_cost                  = 0.01
> > cpu_index_tuple_cost            = 0.001
> > cpu_operator_cost               = 0.0025
> > geqo                            = true
> > geqo_threshold                  = 12
> > geqo_effort                     = 5
> > geqo_pool_size                  = 0
> > geqo_generations                = 0
> > geqo_selection_bias             = 2.0
> > deadlock_timeout                = 1000
> > max_locks_per_transaction       = 64
> > ######### /Config ############
> >
> > ######### Transactions ############
> > we have about 115-300 transactions/min in about 65 tables.
> > ######### /Transactions ############
> >
> > I'm really new at using postgres. So i need some experience to set this
> > parameters in the postgresql- and the system-config. I can't find standard
> > calculations for this. :/ The postgresql-documentation doesn't help me to
> > set the best values for this.
> >
> > The database must be high-availble. I configured rsync to sync the
> > complete
> > /var/lib/pgsql-directory to my hot-standby. On the hotstandby i will
> > make the
> > dumps of the database to improve the performance of the master-db.
> >
> I didn't think an rsync was completely valid. Probably you should look
> more into Slony.
> http://slony.info
>
> It is a single-master asynchronous replication system. I believe it is
> pretty easy to setup, and does what you really want.
>
> > In my tests the synchronization works fine. I synchronised the hole
> > directory
> > and restarted the database of the hotstandby. While restarting,
> > postgresql turned
> > back the old (not archived) wals and the database of my hotstandby was
> > consistent. Is this solution recommended? Or must i use archived wal's
> > with
> > real system-snapshots?
> >
> > best regards,
> >
> > Martin Fandel
>
> John
> =:->
>


Re: postgresql-8.0.1 performance tuning

От
"Steinar H. Gunderson"
Дата:
On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
>>fsync                           = true
> false

Just setting fsync=false without considering the implications is a _bad_
idea...

/* Steinar */
--
Homepage: http://www.sesse.net/

Re: postgresql-8.0.1 performance tuning

От
Cosimo Streppone
Дата:
Steinar wrote:

> On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
>
> > > fsync = true
> > false
>
> Just setting fsync=false without considering the implications is a _bad_
> idea...

I totally agree on that.

--
Cosimo


Re: postgresql-8.0.1 performance tuning

От
"Martin Fandel"
Дата:
Yes, i think also that this setting should be enabled :).

Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson:
> On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
> >>fsync                           = true
> > false
>
> Just setting fsync=false without considering the implications is a _bad_
> idea...
>
> /* Steinar */


Re: postgresql-8.0.1 performance tuning

От
"Martin Fandel"
Дата:
Hi,

hmmm i don't understand which are the best values for shmmax and shmall.
I've googled around but every site says something different.

I've 2GB of RAM now and set it to:

kernel.shmmax=715827882
kernel.shmall=2097152

Is that value ok for 2GB of RAM?

I've set the shared_buffers in my postgresql.conf to 87381
(87381*8*1024 = ~715827882).

Can I use www.powerpostgresql.com as reference to set this
parameters? Or which site can i use?

Best regards,
Martin

Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson:
> On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
> >>fsync                           = true
> > false
>
> Just setting fsync=false without considering the implications is a
_bad_
> idea...
>
> /* Steinar */


Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson:
> On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
> >>fsync                           = true
> > false
>
> Just setting fsync=false without considering the implications is a _bad_
> idea...
>
> /* Steinar */


Re: postgresql-8.0.1 performance tuning

От
"Martin Fandel"
Дата:
Ups,
i'm sorry. i've set the following values:

postgresql.conf:
shared_buffers                  = 70000
effective_cache_size            = 1744762
work_mem                        = 32768
maintenance_work_mem            = 262144
max_fsm_pages                   = 200000

sysctl.conf:
vm.swappiness=10
kernel.shmmax=715827882
kernel.shmall=2097152

Are the values ok for a 2 GB machine? I'm testing these settings
with contrib/pgbench. With this configuration i become up to 200tps
including connection establishing. Is that value ok for this hardware?:

1xP4 3Ghz (hyperthreading enabled)
2GB 266 Mhz RAM CL2.5

pg_xlog is on sda (raid1 with two 10k discs) and the database on
sdb(raid10 with four 10k discs).

My Linux distribution is Suse Linux 9.3 with postgresql 8.0.1.

best regards,
Martin

Am Donnerstag, den 02.06.2005, 14:50 +0200 schrieb Martin Fandel:
> Hi,
>
> hmmm i don't understand which are the best values for shmmax and shmall.
> I've googled around but every site says something different.
>
> I've 2GB of RAM now and set it to:
>
> kernel.shmmax=715827882
> kernel.shmall=2097152
>
> Is that value ok for 2GB of RAM?
>
> I've set the shared_buffers in my postgresql.conf to 87381
> (87381*8*1024 = ~715827882).
>
> Can I use www.powerpostgresql.com as reference to set this
> parameters? Or which site can i use?
>
> Best regards,
> Martin
>
> Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson:
> > On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
> > >>fsync                           = true
> > > false
> >
> > Just setting fsync=false without considering the implications is a
> _bad_
> > idea...
> >
> > /* Steinar */
>
>
> Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson:
> > On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
> > >>fsync                           = true
> > > false
> >
> > Just setting fsync=false without considering the implications is a _bad_
> > idea...
> >
> > /* Steinar */
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org


Re: postgresql-8.0.1 performance tuning

От
"Martin Fandel"
Дата:
I've forgotten the settings for the pgbench-tests. I use 150 clients
with 5 transactions each.


Am Donnerstag, den 02.06.2005, 15:10 +0200 schrieb Martin Fandel:
> Ups,
> i'm sorry. i've set the following values:
>
> postgresql.conf:
> shared_buffers                  = 70000
> effective_cache_size            = 1744762
> work_mem                        = 32768
> maintenance_work_mem            = 262144
> max_fsm_pages                   = 200000
>
> sysctl.conf:
> vm.swappiness=10
> kernel.shmmax=715827882
> kernel.shmall=2097152
>
> Are the values ok for a 2 GB machine? I'm testing these settings
> with contrib/pgbench. With this configuration i become up to 200tps
> including connection establishing. Is that value ok for this hardware?:
>
> 1xP4 3Ghz (hyperthreading enabled)
> 2GB 266 Mhz RAM CL2.5
>
> pg_xlog is on sda (raid1 with two 10k discs) and the database on
> sdb(raid10 with four 10k discs).
>
> My Linux distribution is Suse Linux 9.3 with postgresql 8.0.1.
>
> best regards,
> Martin
>
> Am Donnerstag, den 02.06.2005, 14:50 +0200 schrieb Martin Fandel:
> > Hi,
> >
> > hmmm i don't understand which are the best values for shmmax and shmall.
> > I've googled around but every site says something different.
> >
> > I've 2GB of RAM now and set it to:
> >
> > kernel.shmmax=715827882
> > kernel.shmall=2097152
> >
> > Is that value ok for 2GB of RAM?
> >
> > I've set the shared_buffers in my postgresql.conf to 87381
> > (87381*8*1024 = ~715827882).
> >
> > Can I use www.powerpostgresql.com as reference to set this
> > parameters? Or which site can i use?
> >
> > Best regards,
> > Martin
> >
> > Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson:
> > > On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
> > > >>fsync                           = true
> > > > false
> > >
> > > Just setting fsync=false without considering the implications is a
> > _bad_
> > > idea...
> > >
> > > /* Steinar */
> >
> >
> > Am Mittwoch, den 01.06.2005, 11:57 +0200 schrieb Steinar H. Gunderson:
> > > On Wed, Jun 01, 2005 at 07:30:37AM +0200, Cosimo Streppone wrote:
> > > >>fsync                           = true
> > > > false
> > >
> > > Just setting fsync=false without considering the implications is a _bad_
> > > idea...
> > >
> > > /* Steinar */
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
>


SHMMAX / SHMALL Was (Re: postgresql-8.0.1 performance tuning)

От
Paul McGarry
Дата:
On 6/1/05, Mark Kirkwood <markir@paradise.net.nz> wrote:
> Cosimo Streppone wrote:
> > ######### Config ############
> >> /etc/sysctl.conf:
> >> kernel.shmall = 786432000
> >> kernel.shmmax = 786432000
> >
> > I think you have a problem here.
> > kernel.shmmax should *not* be set to an amount of RAM, but
> > to maximum number of shared memory pages, which on a typical linux system
> > is 4kb. Google around:
> >
> This is somewhat confusing :
>
> kernel.shmmax is in bytes (max single segment size)
> kernel.shmall is in (4k) pages (max system wide allocated segment pages)

Can someone resummarize the situation with these linux parameters for
the dummies? I thought I had my calculations all sorted out but now
I've confused myself again.

The documentation at
http://www.postgresql.org/docs/8.0/interactive/kernel-resources.html
puts the same figure into both values but the posts here seem to
suggest that is wrong?
Or is it different on a 2.4 kernel and the documentation needs updating?

In my specific case I have about 800meg of memory on a linux 2.4 kernel box.

Based on the powerpostgresql.com Performance Checklist [1]  and
Annotated Postgresql.conf [2] I understand that:
-I should have less than 1/3 of my total memory as shared_buffers
-For my server 15000 is a fairly reasonable starting point for
shared_buffers which is ~120MB
-I have 100 max_connections.

So I was going to set SHMMAX to 134217728 (ie 128 Meg)

What should SHMALL be?

The current system values are
postgres@localhost:~/data$ cat /proc/sys/kernel/shmmax
33554432
postgres@localhost:~/data$ cat /proc/sys/kernel/shmall
2097152

ie SHMALL seems to be 1/16 of SHMMAX


Paul

[1] http://www.powerpostgresql.com/PerfList/
[2] http://www.powerpostgresql.com/Downloads/annotated_conf_80.html

Re: SHMMAX / SHMALL Was (Re: postgresql-8.0.1 performance

От
Mark Kirkwood
Дата:
Paul McGarry wrote:

> Based on the powerpostgresql.com Performance Checklist [1]  and
> Annotated Postgresql.conf [2] I understand that:
> -I should have less than 1/3 of my total memory as shared_buffers
> -For my server 15000 is a fairly reasonable starting point for
> shared_buffers which is ~120MB
> -I have 100 max_connections.
>
> So I was going to set SHMMAX to 134217728 (ie 128 Meg)
>
> What should SHMALL be?
>
> The current system values are
> postgres@localhost:~/data$ cat /proc/sys/kernel/shmmax
> 33554432
> postgres@localhost:~/data$ cat /proc/sys/kernel/shmall
> 2097152
>
> ie SHMALL seems to be 1/16 of SHMMAX
>

No - shmall is in 4k pages _ so this amounts to 8G! This is fine -
unless you wish to decrease it in order to prevent too many shared
memory applications running.

BTW - the docs have been amended for 8.1 to suggest shmmax=134217728 and
shmall=2097152 (was going to point you at them - but I cannot find them
on the Postgresql site anymore...).

There seems to be some longstanding confusion in the Linux community
about the units for shmall (some incorrect documentation from Oracle on
the issue does not help I am sure....) - to the point where I downloaded
kernel source to check (reproducing here):


linux-2.6.11.1/include/linux/shm.h:13->

#define SHMMAX 0x2000000         /* max shared seg size (bytes) */
#define SHMMIN 1             /* min shared seg size (bytes) */
#define SHMMNI 4096          /* max num of segs system wide */
#define SHMALL (SHMMAX/PAGE_SIZE*(SHMMNI/16)) /* max shm system wide
(pages) */
#define SHMSEG SHMMNI


Hope that helps

Best wishes

Mark

Re: SHMMAX / SHMALL Was (Re: postgresql-8.0.1 performance tuning)

От
"Martin Fandel"
Дата:
Aah ok :)

I've set my values now as follow (2GB RAM):

SHMMAX=`cat /proc/meminfo | grep MemTotal | cut -d: -f 2 | awk '{print
$1*1024/3}'`
echo kernel.shmmax=${SHMMAX} >> /etc/sysctl.conf
SHMALL=`expr ${SHMALL} / 4096 \* \( 4096 / 16 \)`
echo kernel.shmall=${SHMALL} >> /etc/sysctl.conf

sysctl.conf:
kernel.shmmax=708329472
kernel.shmall=44270592

postgresql.conf:
max_connections=500
shared_buffers=40000 # ~312MB, min. 1000, max ~ 83000

best regards,
Martin


Am Freitag, den 03.06.2005, 18:11 +1200 schrieb Mark Kirkwood:
> Paul McGarry wrote:
>
> > Based on the powerpostgresql.com Performance Checklist [1]  and
> > Annotated Postgresql.conf [2] I understand that:
> > -I should have less than 1/3 of my total memory as shared_buffers
> > -For my server 15000 is a fairly reasonable starting point for
> > shared_buffers which is ~120MB
> > -I have 100 max_connections.
> >
> > So I was going to set SHMMAX to 134217728 (ie 128 Meg)
> >
> > What should SHMALL be?
> >
> > The current system values are
> > postgres@localhost:~/data$ cat /proc/sys/kernel/shmmax
> > 33554432
> > postgres@localhost:~/data$ cat /proc/sys/kernel/shmall
> > 2097152
> >
> > ie SHMALL seems to be 1/16 of SHMMAX
> >
>
> No - shmall is in 4k pages _ so this amounts to 8G! This is fine -
> unless you wish to decrease it in order to prevent too many shared
> memory applications running.
>
> BTW - the docs have been amended for 8.1 to suggest shmmax=134217728 and
> shmall=2097152 (was going to point you at them - but I cannot find them
> on the Postgresql site anymore...).
>
> There seems to be some longstanding confusion in the Linux community
> about the units for shmall (some incorrect documentation from Oracle on
> the issue does not help I am sure....) - to the point where I downloaded
> kernel source to check (reproducing here):
>
>
> linux-2.6.11.1/include/linux/shm.h:13->
>
> #define SHMMAX 0x2000000         /* max shared seg size (bytes) */
> #define SHMMIN 1             /* min shared seg size (bytes) */
> #define SHMMNI 4096          /* max num of segs system wide */
> #define SHMALL (SHMMAX/PAGE_SIZE*(SHMMNI/16)) /* max shm system wide
> (pages) */
> #define SHMSEG SHMMNI
>
>
> Hope that helps
>
> Best wishes
>
> Mark


Re: SHMMAX / SHMALL Was (Re: postgresql-8.0.1 performance

От
Mark Kirkwood
Дата:
Martin Fandel wrote:
> Aah ok :)
>
> I've set my values now as follow (2GB RAM):
>
> SHMMAX=`cat /proc/meminfo | grep MemTotal | cut -d: -f 2 | awk '{print
> $1*1024/3}'`
> echo kernel.shmmax=${SHMMAX} >> /etc/sysctl.conf
> SHMALL=`expr ${SHMALL} / 4096 \* \( 4096 / 16 \)`
> echo kernel.shmall=${SHMALL} >> /etc/sysctl.conf
>
> sysctl.conf:
> kernel.shmmax=708329472
> kernel.shmall=44270592
>
> postgresql.conf:
> max_connections=500
> shared_buffers=40000 # ~312MB, min. 1000, max ~ 83000
>

Hmmm - shmall set to 168G... err why? Apologies for nit picking a little
- but shmall seems unreasonably high. I can't see much reason for
setting it bigger than (physical RAM in bytes)/4096 myself. So in your
case this is 2*(1024*1024*1024)/4096 = 524288

Cheers

Mark


Re: SHMMAX / SHMALL Was (Re: postgresql-8.0.1 performance tuning)

От
"Martin Fandel"
Дата:
ok i set it to 524288. ;)

Am Freitag, den 03.06.2005, 21:10 +1200 schrieb Mark Kirkwood:
> Martin Fandel wrote:
> > Aah ok :)
> >
> > I've set my values now as follow (2GB RAM):
> >
> > SHMMAX=`cat /proc/meminfo | grep MemTotal | cut -d: -f 2 | awk '{print
> > $1*1024/3}'`
> > echo kernel.shmmax=${SHMMAX} >> /etc/sysctl.conf
> > SHMALL=`expr ${SHMALL} / 4096 \* \( 4096 / 16 \)`
> > echo kernel.shmall=${SHMALL} >> /etc/sysctl.conf
> >
> > sysctl.conf:
> > kernel.shmmax=708329472
> > kernel.shmall=44270592
> >
> > postgresql.conf:
> > max_connections=500
> > shared_buffers=40000 # ~312MB, min. 1000, max ~ 83000
> >
>
> Hmmm - shmall set to 168G... err why? Apologies for nit picking a little
> - but shmall seems unreasonably high. I can't see much reason for
> setting it bigger than (physical RAM in bytes)/4096 myself. So in your
> case this is 2*(1024*1024*1024)/4096 = 524288
>
> Cheers
>
> Mark
>