Thread: PostgreSQL db

Hi,

Hope I am not bothering you by sending this email.  I had some query regarding Postgres & while doing research online I
gotyour reference. We are using OpenERP an opensource ERP system. For one installation it require that there will be
3000simultaneous users accessing OpenERP server, hence same requests will come to PostgreSQL server.  And daily 500,000
(Halfmillion)records will be added that means 15,000,000 (15 Million) a month. 

Can Postgres handle 3000 simultaneous requests and handled 15million records addition month on month basis. With this
growthcan Postgresql handle the volume for next 10 years or so? Any recommendation on Server configuration; we will be
usingAmazon Cloud. 

Thanks
Amit

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

Amit Sharma <asharma@verts.co.in> wrote:

> Hope I am not bothering you by sending this email.  I had some
> query regarding Postgres & while doing research online I got your
> reference. We are using OpenERP an opensource ERP system. For one
> installation it require that there will be 3000 simultaneous
> users accessing OpenERP server, hence same requests will come to
> PostgreSQL server.  And daily 500,000 (Half million)records will
> be added that means 15,000,000 (15 Million) a month.
>
> Can Postgres handle 3000 simultaneous requests and handled
> 15million records addition month on month basis. With this growth
> can Postgresql handle the volume for next 10 years or so? Any
> recommendation on Server configuration; we will be using Amazon
> Cloud.

People often run much larger volumes than this on PostgreSQL, but
generally not without using connection pooling.

http://wiki.postgresql.org/wiki/Number_Of_Database_Connections

Personally, I managed a machine which typically had 3000 users
running queries against it while about 20 million
INSERT/UPDATE/DELETE statements per day were run against it.
Tuning showed best performance when we used connection pools with
35 connections for read-only queries and 6 connections for the DML.

Expect to spend some time adjusting the configuration for your
particular hardware and workload.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Amit Sharma <asharma@verts.co.in> wrote:
> "Kevin Grittner" <kgrittn@ymail.com> wrote:

>> Personally, I managed a machine which typically had 3000 users
>> running queries against it while about 20 million
>> INSERT/UPDATE/DELETE statements per day were run against it.
>> Tuning showed best performance when we used connection pools
>> with 35 connections for read-only queries and 6 connections for
>> the DML.

> May I know the machine configuration esp. the hardware
> configuration that you used for this setup?

It had 16 cores and 256GB RAM, a RAID controller with 256MB
battery-backed RAM configured for write-back for a 40 drive RAID 5
array, with a separate BBU controller for a RAID 1 pair for the OS
and another for the WAL (pg_xlog directory).

Oh, and the database was over 3TB and growing fairly quickly.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi,

I need to read files postgres log remotely.
I'm using the command:

pg_read_file select ('pg_log/postgresql-2014-06-11_000000.log', 0,
10000000);

The result is just the first line of the file.

How do I read every line of the file??

Grateful.
Eduardo Reis

Hi,

I need to read files postgres log remotely.
I'm using the command:

select pg_read_file('pg_log/postgresql-2014-06-11_000000.log' , 0 ,
10000000);

The result is just the first line of the file.

How do I read every line of the file?

Grateful.
Eduardo Reis

Hi Kevin,

Thanks for the prompt reply.

May I know the machine configuration esp. the hardware configuration that you used for this setup?

Thanks
Amit

----- Original Message -----
From: "Kevin Grittner" <kgrittn@ymail.com>
To: "Amit Sharma" <asharma@verts.co.in>, pgsql-admin@postgresql.org
Sent: Wednesday, June 11, 2014 10:28:10 AM
Subject: Re: [ADMIN] PostgreSQL db

Amit Sharma <asharma@verts.co.in> wrote:

> Hope I am not bothering you by sending this email.  I had some
> query regarding Postgres & while doing research online I got your
> reference. We are using OpenERP an opensource ERP system. For one
> installation it require that there will be 3000 simultaneous
> users accessing OpenERP server, hence same requests will come to
> PostgreSQL server.  And daily 500,000 (Half million)records will
> be added that means 15,000,000 (15 Million) a month.
>
> Can Postgres handle 3000 simultaneous requests and handled
> 15million records addition month on month basis. With this growth
> can Postgresql handle the volume for next 10 years or so? Any
> recommendation on Server configuration; we will be using Amazon
> Cloud.

People often run much larger volumes than this on PostgreSQL, but
generally not without using connection pooling.

http://wiki.postgresql.org/wiki/Number_Of_Database_Connections

Personally, I managed a machine which typically had 3000 users
running queries against it while about 20 million
INSERT/UPDATE/DELETE statements per day were run against it.
Tuning showed best performance when we used connection pools with
35 connections for read-only queries and 6 connections for the DML.

Expect to spend some time adjusting the configuration for your
particular hardware and workload.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

Frank Zhou <zhouxuesong@gmail.com> wrote:

> We are using client side pg_bouncer, do you think it is also an
> acceptable option?

It can be.  If you can configure it for transaction mode and use a
very small number of pools it can be very effective.  Other modes
don't help as much, although they can be easier to set up.

If you are using Java I found Apache dbcp to be excellent.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi, Kevin
I want to ask you about experience with java and dbcp pool, could you
describe this case in detail. I have one database and several app each
of this app have own datasource, which is a dbcp pool object. Maybe it
is good practice to use pgbouncer before dbcp data sources(but my
opinion it is not good practice to have 2 pool before database), or i
should calculate all dbcp datasources from all app with formula
sum(maxActive)=max connection - 3. And what about another java pool
realization, like HikariCP, do you try them?
This is my sample property file for dbcp:

    <bean id="dataSource"
class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
        <property name="driverClassName" value="org.postgresql.Driver" />
        <property name="url"
value="jdbc:postgresql://host.example.com:5432/db" />
        <property name="username" value="user" />
        <property name="password" value="password" />
        <property name="minIdle" value="1" />
        <property name="maxActive" value="20" />
        <property name="maxWait" value="30000" />
        <property name="validationQuery" value="SELECT 1" />
        <property name="removeAbandoned" value="true" />
        <property name="removeAbandonedTimeout" value="60" />
        <property name="logAbandoned" value="true" />
    </bean>

2014-06-13 6:28 GMT+04:00 Kevin Grittner <kgrittn@ymail.com>:
> Frank Zhou <zhouxuesong@gmail.com> wrote:
>
>> We are using client side pg_bouncer, do you think it is also an
>> acceptable option?
>
> It can be.  If you can configure it for transaction mode and use a
> very small number of pools it can be very effective.  Other modes
> don't help as much, although they can be easier to set up.
>
> If you are using Java I found Apache dbcp to be excellent.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin



--
Best Regards,
Seliavka Evgenii

Евгений Селявка <evg.selyavka@gmail.com> wrote:

> Hi, Kevin
> I want to ask you about experience with java and dbcp pool, could
> you describe this case in detail.

Well, the pooler I have the most experience with is one that I
designed and wrote under Java 1.0 for Wisconsin Courts.  It
continued to work well for us until the time I left, about 16 years
later, with minimal adjustments to take advantage of newer
capabilities in later versions of Java as they came along.  But
before I left they were looking at reworking their software stack
using packages from outside, and I reviewed various Java-based
poolers to see how well I thought they would work.  IMO, dbcp was
the best of the lot that I reviewed.

> I have one database and several app each of this app have own
> datasource, which is a dbcp pool object. Maybe it is good
> practice to use pgbouncer before dbcp data sources(but my opinion
> it is not good practice to have 2 pool before database), or i
> should calculate all dbcp datasources from all app with formula
> sum(maxActive)=max connection - 3.

The goal is to try to limit the total number of active database
transactions to the number that the database can handle
efficiently, or as close as you can get to that.  When you have
multiple pools in parallel, you need to add the maximum database
connections from all of them.  I would try to avoid a second layer
of pooling, but if you can't limit the numbers and sizes of the
Java pools enough to prevent performance problems due to
concurrency, putting pgbouncer in front of the server would not be
crazy.

> And what about another java pool realization, like HikariCP, do
> you try them?

That is not one I reviewed.

> This is my sample property file for dbcp:

I don't remember all the settings and what they do, but the goal
would be to queue requests to start a transaction when a pool was
already at its maximum.  My recollection is that this is what dbcp
does by default.  Well, one thing to look at: I might try setting
initial and maximum pool sizes the same, to avoid a lot of
connection opening and closing during shifts in load.  Logging
connections and disconnections will help you spot thrashing of that
sort.

The best thing is to try incremental changes to the sizing of the
pool with production load with your actual application, and closely
monitor results.  I know I was surprised by how much reducing the
pool size improved performance of our application and allowed it to
scale better with thousands of users.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Kevin thank you very much for your advice, they are helps me a lot.

2014-06-20 21:54 GMT+04:00 Kevin Grittner <kgrittn@ymail.com>:
> Евгений Селявка <evg.selyavka@gmail.com> wrote:
>
>> Hi, Kevin
>> I want to ask you about experience with java and dbcp pool, could
>> you describe this case in detail.
>
> Well, the pooler I have the most experience with is one that I
> designed and wrote under Java 1.0 for Wisconsin Courts.  It
> continued to work well for us until the time I left, about 16 years
> later, with minimal adjustments to take advantage of newer
> capabilities in later versions of Java as they came along.  But
> before I left they were looking at reworking their software stack
> using packages from outside, and I reviewed various Java-based
> poolers to see how well I thought they would work.  IMO, dbcp was
> the best of the lot that I reviewed.
>
>> I have one database and several app each of this app have own
>> datasource, which is a dbcp pool object. Maybe it is good
>> practice to use pgbouncer before dbcp data sources(but my opinion
>> it is not good practice to have 2 pool before database), or i
>> should calculate all dbcp datasources from all app with formula
>> sum(maxActive)=max connection - 3.
>
> The goal is to try to limit the total number of active database
> transactions to the number that the database can handle
> efficiently, or as close as you can get to that.  When you have
> multiple pools in parallel, you need to add the maximum database
> connections from all of them.  I would try to avoid a second layer
> of pooling, but if you can't limit the numbers and sizes of the
> Java pools enough to prevent performance problems due to
> concurrency, putting pgbouncer in front of the server would not be
> crazy.
>
>> And what about another java pool realization, like HikariCP, do
>> you try them?
>
> That is not one I reviewed.
>
>> This is my sample property file for dbcp:
>
> I don't remember all the settings and what they do, but the goal
> would be to queue requests to start a transaction when a pool was
> already at its maximum.  My recollection is that this is what dbcp
> does by default.  Well, one thing to look at: I might try setting
> initial and maximum pool sizes the same, to avoid a lot of
> connection opening and closing during shifts in load.  Logging
> connections and disconnections will help you spot thrashing of that
> sort.
>
> The best thing is to try incremental changes to the sizing of the
> pool with production load with your actual application, and closely
> monitor results.  I know I was surprised by how much reducing the
> pool size improved performance of our application and allowed it to
> scale better with thousands of users.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company



--
Best Regards,
Seliavka Evgenii