Shared_buffers hint

Shared_buffers hint

am 06.10.2008 13:52:59 von Rafael Domiciano

------=_Part_25074_18868574.1223293979622
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

HI people,
I'm tuning a server that is a long time abandoned, and this server is a
little slow.
Now, in my postgresql.conf I have:
shared_buffers = 100Mb
mas_fsm_relations = 15000
mas_fsm_pages = 1600000
max_connections = 300

And I changin' to:
shared_buffers = 1000Mb
(The server has 2 Gb of memory)
mas_fsm_relations = 15000
(The Vacuum noticed me 608 relations)
mas_fsm_pages = 1600000
(The Vacuum noticed me 500800 pages)
max_connections = 300
(I did a "ps axf | grep postgres | wc -l" and the bash brings to me the
number of 120 and all the people is not online at now)

My qustion is about shared_buffers, in postgresql.conf I have: min 128kB or
max_connections*16kB # (change requires restart)
So I did:
300 * (16Kb / 1024) = 4,69?
What this result means?

Thnks all,

Rafael Domiciano

------=_Part_25074_18868574.1223293979622
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

HI people,

I'm tuning a server that is a long time abandoned, and this server is a little slow.
Now, in my postgresql.conf I have:
shared_buffers = 100Mb

mas_fsm_relations = 15000
mas_fsm_pages = 1600000
max_connections = 300

And I changin' to:
shared_buffers = 1000Mb
   (The server has 2 Gb of memory)

mas_fsm_relations = 15000
   (The Vacuum noticed me 608 relations)
mas_fsm_pages = 1600000
   (The Vacuum noticed me 500800 pages)

max_connections = 300
   (I did a "ps axf | grep postgres | wc -l" and the bash brings to me the number of 120 and all the people is not online at now)


My qustion is about shared_buffers, in postgresql.conf I have: min 128kB or max_connections*16kB  # (change requires restart)

So I did:
300 * (16Kb / 1024) = 4,69?
What this result means?

Thnks all,

Rafael Domiciano


------=_Part_25074_18868574.1223293979622--

Re: Shared_buffers hint

am 06.10.2008 15:23:13 von Marcelo Martins

--Apple-Mail-3-179058027
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit

Hi Rafael,

The result there means that the minimal should be 4.69Mb for
shared_buffers
As a rule of thumb the shared buffers should be between 10% - 15% of
available RAM that assuming such is a dedicated PG server.
I have worked on some servers where I set shared buffers lower than
10%, it's really a matter of trying out what value works best for you.


Marcelo
Linux/Solaris System Administrator
http://www.zeroaccess.org

On Oct 6, 2008, at 6:52 AM, Rafael Domiciano wrote:

> HI people,
>
> I'm tuning a server that is a long time abandoned, and this server
> is a little slow.
> Now, in my postgresql.conf I have:
> shared_buffers = 100Mb
> mas_fsm_relations = 15000
> mas_fsm_pages = 1600000
> max_connections = 300
>
> And I changin' to:
> shared_buffers = 1000Mb
> (The server has 2 Gb of memory)
> mas_fsm_relations = 15000
> (The Vacuum noticed me 608 relations)
> mas_fsm_pages = 1600000
> (The Vacuum noticed me 500800 pages)
> max_connections = 300
> (I did a "ps axf | grep postgres | wc -l" and the bash brings to
> me the number of 120 and all the people is not online at now)
>
> My qustion is about shared_buffers, in postgresql.conf I have: min
> 128kB or max_connections*16kB # (change requires restart)
> So I did:
> 300 * (16Kb / 1024) = 4,69?
> What this result means?
>
> Thnks all,
>
> Rafael Domiciano


--Apple-Mail-3-179058027
Content-Type: text/html;
charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

-webkit-line-break: after-white-space; ">Hi =
Rafael,


The result there means that the minimal =
should be 4.69Mb for shared_buffers
As a rule of thumb the =
shared buffers should be between 10% - 15% of available RAM =
that assuming such is a dedicated PG server.
I have worked on =
some servers where I set shared buffers lower than 10%,  it's =
really a matter of trying out what value works best for =
 you.


apple-content-edited=3D"true"> style=3D"border-collapse: separate; color: rgb(0, 0, 0); font-family: =
Helvetica; font-size: 12px; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; =
white-space: normal; widows: 2; word-spacing: 0px; =
-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: =
0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: =
auto; -webkit-text-stroke-width: 0; ">
break-word; -webkit-nbsp-mode: space; -webkit-line-break: =
after-white-space; "> style=3D"border-collapse: separate; color: rgb(0, 0, 0); font-family: =
Helvetica; font-size: 12px; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; =
widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; =
-webkit-border-vertical-spacing: 0px; =
-webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: =
auto; -webkit-text-stroke-width: 0px; ">
break-word; -webkit-nbsp-mode: space; -webkit-line-break: =
after-white-space; ">
Marcelo
Linux/Solaris System =
Administrator
v>

On Oct 6, 2008, at 6:52 AM, =
Rafael Domiciano wrote:

class=3D"Apple-interchange-newline">
dir=3D"ltr">HI people,

I'm tuning a server that is a =
long time abandoned, and this server is a little slow.
Now, in =
my postgresql.conf I have:
shared_buffers =3D 100Mb
=
mas_fsm_relations =3D 15000
mas_fsm_pages =3D =
1600000
max_connections =3D 300

And I =
changin' to:
shared_buffers =3D 1000Mb
class=3D"Apple-style-span" style=3D"font-weight: bold;">   =
(The server has 2 Gb of memory)
mas_fsm_relations =3D =
15000
bold;">   (The Vacuum noticed me 608 =
relations)
mas_fsm_pages =3D 1600000
class=3D"Apple-style-span" style=3D"font-weight: bold;">   =
(The Vacuum noticed me 500800 pages)
=
max_connections =3D 300
style=3D"font-weight: bold;">   (I did a "ps axf | grep =
postgres | wc -l" and the bash brings to me the number of 120 and all =
the people is not online at now)
class=3D"Apple-style-span" style=3D"font-weight: =
bold;">
My qustion is about class=3D"Apple-style-span" style=3D"font-weight: bold;"> =
shared_buffers
, in postgresql.conf I have:  class=3D"Apple-style-span" style=3D"font-weight: bold; ">min 128kB or =
max_connections*16kB  # (change requires restart)
=
So I did:
300 * (16Kb / 1024) =3D 4,69?
What =
this result means?

Thnks =
all,

Rafael =
Domiciano

=

--Apple-Mail-3-179058027--

Re: Shared_buffers hint

am 06.10.2008 15:42:36 von Scott Marlowe

On Mon, Oct 6, 2008 at 5:52 AM, Rafael Domiciano
wrote:
> HI people,
> I'm tuning a server that is a long time abandoned, and this server is a
> little slow.

What version pgsql is it running? If it's 7.4 or before, you should
look at upgrading it. If you can't then a shared buffer setting in
the 1000 to 10000 range is generally reasonable, but large
shared_buffer settings (i.e. over 10000) are counterproductive for 7.4
and before.

> Now, in my postgresql.conf I have:
> shared_buffers = 100Mb

So it's a pretty modern version, since old ones can't read 100Mb as a setting.

> mas_fsm_relations = 15000
> mas_fsm_pages = 1600000
> max_connections = 300
> And I changin' to:
> shared_buffers = 1000Mb
> (The server has 2 Gb of memory)

50% is kind of big. Generally you either want it small enough that
the OS can do the majority of the caching (it's usually better at
caching large amounts of data) or large enough that the kernel cache
doesn't come into play much. 50% means that everything is buffered
exactly twice.

> mas_fsm_relations = 15000
> (The Vacuum noticed me 608 relations)
> mas_fsm_pages = 1600000
> (The Vacuum noticed me 500800 pages)
> max_connections = 300
> (I did a "ps axf | grep postgres | wc -l" and the bash brings to me the
> number of 120 and all the people is not online at now)
> My qustion is about shared_buffers, in postgresql.conf I have: min 128kB or
> max_connections*16kB # (change requires restart)
> So I did:
> 300 * (16Kb / 1024) = 4,69?
> What this result means?

That's just the minimum the server needs to operate. Not operate
well, just operate.

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

Re: Shared_buffers hint

am 06.10.2008 15:56:51 von Rafael Domiciano

------=_Part_305_15961128.1223301411178
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I'm using 8.3.3 version in this machine..!So, the shared_buffers set to
100Mb is ok? Or 500Mb is better?
This server we call "Reporter Server", so it's used to do heavy reports for
a few users only (I could say 5 users).
What could be the best config for my server with:
2 Gb RAM
300 GB HD
Postgres 8.3
Dual Core

2008/10/6 Scott Marlowe

> On Mon, Oct 6, 2008 at 5:52 AM, Rafael Domiciano
> wrote:
> > HI people,
> > I'm tuning a server that is a long time abandoned, and this server is a
> > little slow.
>
> What version pgsql is it running? If it's 7.4 or before, you should
> look at upgrading it. If you can't then a shared buffer setting in
> the 1000 to 10000 range is generally reasonable, but large
> shared_buffer settings (i.e. over 10000) are counterproductive for 7.4
> and before.
>
> > Now, in my postgresql.conf I have:
> > shared_buffers = 100Mb
>
> So it's a pretty modern version, since old ones can't read 100Mb as a
> setting.
>
> > mas_fsm_relations = 15000
> > mas_fsm_pages = 1600000
> > max_connections = 300
> > And I changin' to:
> > shared_buffers = 1000Mb
> > (The server has 2 Gb of memory)
>
> 50% is kind of big. Generally you either want it small enough that
> the OS can do the majority of the caching (it's usually better at
> caching large amounts of data) or large enough that the kernel cache
> doesn't come into play much. 50% means that everything is buffered
> exactly twice.
>
> > mas_fsm_relations = 15000
> > (The Vacuum noticed me 608 relations)
> > mas_fsm_pages = 1600000
> > (The Vacuum noticed me 500800 pages)
> > max_connections = 300
> > (I did a "ps axf | grep postgres | wc -l" and the bash brings to me
> the
> > number of 120 and all the people is not online at now)
> > My qustion is about shared_buffers, in postgresql.conf I have: min 128kB
> or
> > max_connections*16kB # (change requires restart)
> > So I did:
> > 300 * (16Kb / 1024) = 4,69?
> > What this result means?
>
> That's just the minimum the server needs to operate. Not operate
> well, just operate.
>

------=_Part_305_15961128.1223301411178
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I'm using 8.3.3 version in this machine..!
So, the shared_buffers set to 100Mb is ok? Or 500Mb is better?
This server we call "Reporter Server", so it's used to do heavy reports for a few users only (I could say 5 users).

What could be the best config for my server with:
2 Gb RAM
300 GB HD
Postgres 8.3
Dual Core

2008/10/6 Scott Marlowe <>

On Mon, Oct 6, 2008 at 5:52 AM, Rafael Domiciano

<> wrote:

> HI people,

> I'm tuning a server that is a long time abandoned, and this server is a

> little slow.



What version pgsql is it running?  If it's 7.4 or before, you should

look at upgrading it.  If you can't then a shared buffer setting in

the 1000 to 10000 range is generally reasonable, but large

shared_buffer settings (i.e. over 10000) are counterproductive for 7.4

and before.



> Now, in my postgresql.conf I have:

> shared_buffers = 100Mb



So it's a pretty modern version, since old ones can't read 100Mb as a setting.



> mas_fsm_relations = 15000

> mas_fsm_pages = 1600000

> max_connections = 300

> And I changin' to:

> shared_buffers = 1000Mb

>    (The server has 2 Gb of memory)



50% is kind of big.  Generally you either want it small enough that

the OS can do the majority of the caching (it's usually better at

caching large amounts of data) or large enough that the kernel cache

doesn't come into play much.  50% means that everything is buffered

exactly twice.



> mas_fsm_relations = 15000

>    (The Vacuum noticed me 608 relations)

> mas_fsm_pages = 1600000

>    (The Vacuum noticed me 500800 pages)

> max_connections = 300

>    (I did a "ps axf | grep postgres | wc -l" and the bash brings to me the

> number of 120 and all the people is not online at now)

> My qustion is about shared_buffers, in postgresql.conf I have: min 128kB or

> max_connections*16kB  # (change requires restart)

> So I did:

> 300 * (16Kb / 1024) = 4,69?

> What this result means?



That's just the minimum the server needs to operate.  Not operate

well, just operate.




------=_Part_305_15961128.1223301411178--

Re: Shared_buffers hint

am 06.10.2008 16:06:01 von Scott Marlowe

On Mon, Oct 6, 2008 at 7:56 AM, Rafael Domiciano
wrote:
> I'm using 8.3.3 version in this machine..!
> So, the shared_buffers set to 100Mb is ok? Or 500Mb is better?
> This server we call "Reporter Server", so it's used to do heavy reports for
> a few users only (I could say 5 users).
> What could be the best config for my server with:
> 2 Gb RAM
> 300 GB HD
> Postgres 8.3
> Dual Core

100M is probably adequate. 2G is a pretty small database server
memory wise. I assume by 300G HD you mean a single hard drive. Since
a single hard drive is going to limit the speed at which you can
access data from it, I'd leave shared_buffers at 100M and let the OS
cache data for you. Also, look at work_mem. You might want to set
those few heavy users to have more work_mem than the other users.

alter user heavyuser set work_mem=128000;

note that work_mem is per user sort, so it's quite possible to exhaust
main memory if you set it high for everybody and they all do sorts on
large sets suddenly.

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

Re: Shared_buffers hint

am 08.10.2008 15:40:29 von Rafael Domiciano

------=_Part_56255_33059392.1223473229782
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Ok,
I'm trying this...

Thnks a lot!

2008/10/6 Scott Marlowe

> On Mon, Oct 6, 2008 at 7:56 AM, Rafael Domiciano
> wrote:
> > I'm using 8.3.3 version in this machine..!
> > So, the shared_buffers set to 100Mb is ok? Or 500Mb is better?
> > This server we call "Reporter Server", so it's used to do heavy reports
> for
> > a few users only (I could say 5 users).
> > What could be the best config for my server with:
> > 2 Gb RAM
> > 300 GB HD
> > Postgres 8.3
> > Dual Core
>
> 100M is probably adequate. 2G is a pretty small database server
> memory wise. I assume by 300G HD you mean a single hard drive. Since
> a single hard drive is going to limit the speed at which you can
> access data from it, I'd leave shared_buffers at 100M and let the OS
> cache data for you. Also, look at work_mem. You might want to set
> those few heavy users to have more work_mem than the other users.
>
> alter user heavyuser set work_mem=128000;
>
> note that work_mem is per user sort, so it's quite possible to exhaust
> main memory if you set it high for everybody and they all do sorts on
> large sets suddenly.
>

------=_Part_56255_33059392.1223473229782
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Ok,

I'm trying this...

Thnks a lot!

2008/10/6 Scott Marlowe <>

On Mon, Oct 6, 2008 at 7:56 AM, Rafael Domiciano

<> wrote:

> I'm using 8.3.3 version in this machine..!

> So, the shared_buffers set to 100Mb is ok? Or 500Mb is better?

> This server we call "Reporter Server", so it's used to do heavy reports for

> a few users only (I could say 5 users).

> What could be the best config for my server with:

> 2 Gb RAM

> 300 GB HD

> Postgres 8.3

> Dual Core



100M is probably adequate.  2G is a pretty small database server

memory wise.  I assume by 300G HD you mean a single hard drive.  Since

a single hard drive is going to limit the speed at which you can

access data from it, I'd leave shared_buffers at 100M and let the OS

cache data for you.  Also, look at work_mem.  You might want to set

those few heavy users to have more work_mem than the other users.



alter user heavyuser set work_mem=128000;



note that work_mem is per user sort, so it's quite possible to exhaust

main memory if you set it high for everybody and they all do sorts on

large sets suddenly.




------=_Part_56255_33059392.1223473229782--

About pg_hba.conf

am 08.10.2008 20:54:40 von fabrixio1

--_0cb5304f-e3b0-494c-bbb1-d883ff409b73_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


hi people=20

I can set an entry with regular expressions in the file pg_hba.conf?=20

For example if I have two databases called foo.1 and foo.2.=20
I can put in the file pg_hba.conf the next entry ?=20

host myUser foo.? 192.168.0.0/24 md5=20
or=20
host myUser foo.[0-9]+ 192.168.0.0/24 md5=20

greetings ...
____________________________________________________________ _____
Live Search premia tus b=FAsquedas=2C ll=E9vate hasta =A1Un Auto!
http://www.ganabuscando.com/Default.aspx=

--_0cb5304f-e3b0-494c-bbb1-d883ff409b73_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable






hi people

I can set an entry with re=
gular expressions in the file pg_hba.conf?

For example if I have t=
wo databases called foo.1 and foo.2.
I can put in the file pg_hba.conf=
the next entry ?

host myUser foo.? 192.168.0.0/24 md5
or
=
host myUser foo.[0-9]+ 192.168.0.0/24 md5

greetings ...


>Conoce el perfil completo de todos tus amigos de Windows Live Messenger ju=
sto aqu=ED: w'>Windows Live Spaces
=

--_0cb5304f-e3b0-494c-bbb1-d883ff409b73_--