Re: RAM of Postgres Server

Dear David G. and Gavin,

Please find the details as below:

* Q. What type of queries to be run on postgres database server? *

All types. Databases will be general OLTP.

*Q. Size of commonly accessed tables, indexes, ... ?*

Various. Anywhere from a few MB to over 100GB.

*Q. Database size for current and for future for all databases: *

Various. Anywhere from 100 – 1000GB and always growing.

*Q. Total Connection limit from the database through users and Application
on daily basis for all the database:*

Probably 4000+

*Q. CPU Core: *

Prod databases will have 32 cores of CPU and 128 GB of RAM.

If you want any other information then inform to me.

Regards,

SS

On Thu, Jan 7, 2016 at 8:51 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Thu, Jan 7, 2016 at 12:32 AM, Sachin Srivastava <
> ssr(dot)teleatlas(at)gmail(dot)com> wrote:
>
>> Dear David,
>>
>>
>>
>>
>>
>> Q: RAM holds data that is recently accessed - how much of that will you
>> have?
>>
>>
>>
>> Ans: Kindly confirm, as per your question “RAM holds data that is
>> recently accessed” : How we figured out that how much data we will
>> have. Is it depends of Total WAL files (total "checkpoint_segment" I have
>> given 32), am I correct or thinking wrong, please clarify to me.
>>
>>
>>
>> Right now we have 10 GB RAM for first database server and 3 GB RAM for
>> another database server.
>>
>>
> Using WAL to measure your active dataset is not going to work. WAL
> activity occurs when you WRITE data while in many cases the data in RAM is
> data that was written to the WAL a long time ago.
>
>
>>
>>
>>
>>
>> Q: Cores help service concurrent requests - how many of those will you
>> have? How fast will they complete?
>>
>>
>>
>> Ans: It’s means, if we have more core then we can do our work fast. Like
>> from 9.3 onwards for pg_dump as example, if machines having multiple cores
>> as the load can be shared among separate threads.
>>
>>
>>
>> So if possible to us then more core should be available on database
>> server for better performance, please clarify the benefit of more core to
>> me.
>>
>>
>>
>> Right now we have 1 core for first database server and 2 core for another
>> database server.
>>
>>
>>
>
> ​PostgreSQL is process-oriented and presently only uses a single process
> to service a single connection. Application software can upon up multiple
> connections - which is what pg_dump does. More rypically you'd have
> something like a web server where all of the incoming requests are funneled
> through a connection pool which then opens a number of connections to the
> database which it then shares among those requests.
>
> If you want advice you are going to have to give considerably more detail
> of your application and database usage patterns than you have.
>
> David J.
>