[Postgres-xl-general] Recommended Tuning on Postgres-XL

FattahRozzaq ssoorruu at gmail.com
Wed Sep 2 06:09:06 PDT 2015


Hi Richard,

Thank you for responding me :)

I've Google'd and found this website: http://pgtune.leopard.in.ua/

I choose the system parameters:
OS: Linux
DB Type: Online Transaction Processing System
RAM: 128GB
Number of Connections: 9999 (the calculator refuse higher value than this)


Suggestion to be put on postgresql.conf (in datanode or coordinator?):
max_connections = 9999
shared_buffers = 32GB
effective_cache_size = 96GB
work_mem = 3355kB
maintenance_work_mem = 2GB
checkpoint_segments = 64
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100

Suggestion to modify /etc/sysctl.conf:
kernel.shmmax=68719476736
kernel.shmall=16777216

Where should I put the postgresql.conf modification? At datanode or coordinator?
As if the datanode can accept 9999 concurrent connections, should the
coordinator also be set the max_connections to 9999?

How much is the recommended value of max_prepared_transactions ?


Big Thanks!!
FattahRozzaq

On 02/09/2015, Silva, Richard <rsilva at javelin.mg> wrote:
> FattahRozzaq,
>
>
> Most of the standard Postgres tuning guides will get you pointed in the
> right direction.
>
> https://wiki.postgresql.org/wiki/Performance_Optimization
>
>
> I found pgTune to be a good little tool. It allows you to specify the
> workload type and will make the suggestions based on your platform. These
> suggestions then need to be propagated to the XL nodes. Aside from the
> basic tweaks, we have found it helpful to tweak the following.
>
> max_connections = increased slightly higher than you would normally to
> allow for node to node communication.
> max_prepared_transactions = avoid setting this low.
>
> Other things like maintenance_work_mem should only need tweaking when
> migrating data and then it can be set back to a normal level.
>
> Hope this helps.
>
> thank you...
> R!chard Silva  ETL Architect for Javelin Marketing Group
>
>
>
>
> On 9/2/15, 7:04 AM, "FattahRozzaq" <ssoorruu at gmail.com> wrote:
>
> Good night from Indonesia :),
>
> I hope this email finds you all well.
>
> I have 3 servers, each server contains:
> - OS Debian 7.0 (Wheezy)
> - postgres --version: (PostgreSQL) 9.2.4 (Postgres-XL 9.2.0)
> - CPU 24 cores
> - RAM 128GB
> - SSD 128GB
> - 1 Gigabit ethernet interface
>
> The topology:
> 1) Server-1
> - GTM
> - GTM Proxy
> - Coordinator Master-1
> - Coordinator Slave of Server-3
> - Datanode Master-1
> - Datanode Slave of Server-3
>
> 2) Server-2
> - Coordinator Master-2
> - Coordinator Slave of Server-1
> - Datanode Master-2
> - Datanode Slave of Server-1
>
> 3) Server-3
> - Coordinator Master-3
> - Coordinator Slave of Server-2
> - Datanode Master-3
> - Datanode Slave of Server-2
> - GTM Slave
>
> The application is a messaging/messenger/chatting application that
> will only use 1 database and 2 tables in Postgres-XL. The server will
> communicate with Postgres-XL via 1 Gigabit ethernet.
>
> I need to tune the Postgres-XL as efficient as it can.
> As far as I configured, the pgxc_ctl.conf is as attached (I'm really
> new to PostgreSQL, just start to learn it about 2 months ago.).
>
> I really need direct input from you all about the most optimal
> (postgresql.conf or pgxc_ctl) configuration for my requirement above
> with hardware specification above.
>
>
> Thank you in advance,
> FattahRozzaq
>
>
>
>
>
>
> Access Manager: This email is intended only for the person or entity to
> which it is addressed and may contain information that is privileged,
> confidential or otherwise protected from disclosure. Dissemination,
> distribution or copying of this e-mail or the information herein by anyone
> other than the intended recipient, or an employee or agent responsible for
> delivering the message to the intended recipient, is prohibited. If you have
> received this e-mail in error, please immediately notify us by calling the
> Help Desk at (855) 237-8324.
>




More information about the postgres-xl-general mailing list