Fine Tuning Postgresql

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Fine Tuning Postgresql

Shane McCoy
Hey! I went back to 'fine tune' postgres (as i realized i had skipped the step earlier) and had a few quick questions; (using Ubuntu 14.04.1)

For postgresql.conf

1.tcpip_socket = true # Replaced with listen_addresses in Postgres 8.0+
I put;
#listen_addresses = '*'        

2. max_connections = 32 (was 100)

3. work_mem=2048MB (was 1mb)

4.
There were no settings to adjust in etc/sysctl.conf so i added the lines;
kern.sysv.shmmax=52428800
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8
kern.sysv.shmall=25600

Just want to make sure i correctly set these. Thanks!
Shane M.




------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: Fine Tuning Postgresql

joe carlson
Hi Shane,

Take a look at the book by Greg Smith ‘Postgres 9.0 High Performance’, or search You Tube for his lectures. Or check out pgtune (https://github.com/gregs1104/pgtune). He has a lot to say about setting these parameters.

There is not a single answer on the settings for work_mem, shared_buffers and so on, since these depend on your hardware, db size and application.

Joe Carlson
On Dec 10, 2014, at 12:56 PM, Shane McCoy <[hidden email]> wrote:

Hey! I went back to 'fine tune' postgres (as i realized i had skipped the step earlier) and had a few quick questions; (using Ubuntu 14.04.1)

For postgresql.conf

1.tcpip_socket = true # Replaced with listen_addresses in Postgres 8.0+
I put;
#listen_addresses = '*'        

2. max_connections = 32 (was 100)

3. work_mem=2048MB (was 1mb)

4.
There were no settings to adjust in etc/sysctl.conf so i added the lines;
kern.sysv.shmmax=52428800
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8
kern.sysv.shmall=25600

Just want to make sure i correctly set these. Thanks!
Shane M.



------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema


------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: Fine Tuning Postgresql

Karl O. Pinc
In reply to this post by Shane McCoy
On 12/10/2014 02:56:33 PM, Shane McCoy wrote:
> Hey! I went back to 'fine tune' postgres (as i realized i had skipped
> the
> step earlier) and had a few quick questions; (using Ubuntu 14.04.1)

<snip>

> Just want to make sure i correctly set these. Thanks!

As said, every system is different.

I believe the rule of thumb is to configure, and use,
1/4 of your RAM for shared memory.  Could be wrong,
I'm tired at the moment.

A good place to go for help with your specific system
and use-case is the irc channel #postgresql on irc.freenode.net.



Karl <[hidden email]>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: Fine Tuning Postgresql

Nathan Weeks
In reply to this post by Shane McCoy
I'll add that the PostgreSQL documentation states that starting with
PostgreSQL 9.3, SysV shared memory limits don't need to be adjusted in
most cases, as POSIX shared memory & mmap() are used for most
purposes:

https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#Switch_to_Posix_shared_memory_and_mmap.28.29

--
Nathan Weeks
IT Specialist
USDA-ARS Corn Insects and Crop Genetics Research Unit
Crop Genome Informatics Laboratory
Iowa State University
http://weeks.public.iastate.edu/



On Thu, Dec 11, 2014 at 12:24 AM,
<[hidden email]> wrote:

> Message: 1
> Date: Wed, 10 Dec 2014 22:17:42 -0600
> From: "Karl O. Pinc" <[hidden email]>
> Subject: Re: [Gmod-schema] Fine Tuning Postgresql
> To: Shane McCoy <[hidden email]>
> Cc: [hidden email]
> Message-ID: <1418271462.3413.12@slate>
> Content-Type: text/plain; charset=us-ascii
>
> On 12/10/2014 02:56:33 PM, Shane McCoy wrote:
>> Hey! I went back to 'fine tune' postgres (as i realized i had skipped
>> the
>> step earlier) and had a few quick questions; (using Ubuntu 14.04.1)
>
> <snip>
>
>> Just want to make sure i correctly set these. Thanks!
>
> As said, every system is different.
>
> I believe the rule of thumb is to configure, and use,
> 1/4 of your RAM for shared memory.  Could be wrong,
> I'm tired at the moment.
>
> A good place to go for help with your specific system
> and use-case is the irc channel #postgresql on irc.freenode.net.
>
>
>
> Karl <[hidden email]>
> Free Software:  "You don't pay back, you pay forward."
>                  -- Robert A. Heinlein

------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
Reply | Threaded
Open this post in threaded view
|

Re: Fine Tuning Postgresql

Siddhartha Basu
In reply to this post by Shane McCoy
Hi Shane,
There is also a gmod wiki page about postgresql
http://gmod.org/wiki/PostgreSQL_Performance_Tips

thanks,
-siddhartha

On Wed, 10 Dec 2014, Shane McCoy wrote:

>    Hey! I went back to 'fine tune' postgres (as i realized i had skipped the
>    step earlier) and had a few quick questions; (using Ubuntu 14.04.1)
>
>    For postgresql.conf
>
>    1.tcpip_socket = true # Replaced with listen_addresses in Postgres 8.0+
>    I put;
>    #listen_addresses = '*'A A A A A A A A
>
>    2. max_connections = 32 (was 100)
>
>    3. work_mem=2048MB (was 1mb)
>
>    4.
>    There were no settings to adjust in etc/sysctl.conf so i added the lines;
>    kern.sysv.shmmax=52428800
>    kern.sysv.shmmin=1
>    kern.sysv.shmmni=32
>    kern.sysv.shmseg=8
>    kern.sysv.shmall=25600
>
>    Just want to make sure i correctly set these. Thanks!
>    Shane M.

> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
> http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk

> _______________________________________________
> Gmod-schema mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-schema


------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema