[Pgcluster-general] Clarifications for PgCluster 1.9

[mitani at sraw.co.jp]

Hi,

The lock level is different between simple SELECT and SELECT in a transaction.
Therefore, transaction query should replicate even it's having SELECT query.

In order to guarantee the isolation of transaction, each transaction are replicated on each sessions.

Regards,
----------------
At.Mitani
 
-- original message --
From: "K, Niranjan (NSN - IN/Bangalore)"<niranjan.k at nsn.com>
To: <mitani_nl at yahoo.co.jp>;<pgcluster-general at pgfoundry.org>
Sent: Wed, 18 Jun 2008 11:42:41 +0530
Subject: [Pgcluster-general] Clarifications for PgCluster 1.9

>
>Could you please let me know the following details.
>As part of the evalution, 
>- I would like to know the reason why SELECT query will get replicated
>is executed in a transaction or when the prepare is done on the SQL
>- Impact of using the transaction isolation for the above behaviour
>- Flow of replication for SELECT query or any SQL query. Please see the
>below mail.
>
>regards,
>Niranjan
>
>-----Original Message-----
>From: K, Niranjan (NSN - IN/Bangalore) 
>Sent: Saturday, June 14, 2008 1:26 PM
>To: 'mitani_nl at yahoo.co.jp'
>Subject: Read operations in PgCluster 1.9
>
> Thanks. Yes this was indeed the issue.
>
>But could you tell what is the background behind such concept. In the
>real scenarios, we would normally have transactions which could contain
>multiple SQL statements (Select/Updates/Inserts/delete). 
>
>What is the impact of such behaviour considering the settings related to
>transaction isloation. Postgres supports mainly 2 modes ie Read
>Committed and Serializable.
>
>I would also like to get into the design of the PgCluster, so, are there
>any Analysis or design documents. If not which parts of the code I will
>have to study to understand the replication design.
>
>Also in the link,
>http://pgcluster.projects.postgresql.org/structure_of_replication.html
>(in the last picture, which I assume is the only mode available in the
>PgCluster), first DB1 is operated with the SQL and then sent to the
>Replicator and further the SQL is sent to all the clusters parallely. If
>there is any error in any of the clusters (other than DB1), what will
>the response be got to the LB. Whether FAILURE or SUCCESS?, which means
>whether DB1 should retain the update that happened or rollback because
>there was some problem in updating one of the cluster (say DB3)?
>It's not clear what will the flow of SELECT SQL query be in case the SQL
>is sent to Replicator (ie when the SELECT is used in the transaction),
>what happens if there are inconsistencies in the clusters and which
>cluster's record will be considered?
>
>It would be really helpful, if you could share the replication design.
>
>Thanks in advance,
>Niranjan
>
>
>-----Original Message-----
>From: pgcluster-general-bounces at pgfoundry.org
>[mailto:pgcluster-general-bounces at pgfoundry.org] On Behalf Of ext
>mitani at sraw.co.jp
>Sent: Wednesday, June 11, 2008 1:52 PM
>To: pgcluster-general at pgfoundry.org
>Subject: Re: [Pgcluster-general] Read operations in PgCluster 1.9
>
>Hi,
>
>If prepared query was used in your benchmark tool, SELECT query might be
>replicated.
>And if the SELECT query was used during transaction (BEGIN - END), it
>also might be replicated.
>
>You can see what kind of queries are sent from the benchmark tool when
>you start replication server with debug option (-vn). 
> 
>Regards,
>---------------
>At.Mitani
>
>
>-- original message --
>From: "K, Niranjan (NSN - IN/Bangalore)"<niranjan.k at nsn.com>
>To: <pgcluster-general at pgfoundry.org>
>Sent: Wed, 11 Jun 2008 13:29:16 +0530
>Subject: [Pgcluster-general] Read operations in PgCluster 1.9
>
>>Hello,
>>
>>I was evaluating the pgcluster-1.9.0rc5 and the configurations are 2 
>>node cluster with 1 Cluster DB in each server (Master in 1 node & Slave
>
>>in another node) and 1 replicator in master node.
>>When i execute a tool (DB Benchmark), which executes SELECT SQL 
>>statements, the PgCluster expects 'PgReplicate' process to be up & 
>>running. I guess this is required only for INSERT, UPDATE, DELETE 
>>statements. I had brought down the replicator process intensionally 
>>because the performance (atleast for SELECT statements) was not what we
>
>>expected. Currently we get 115 transactions/ second in PgCluster and in
>
>>case of single node PotsgreSQL we get 2180 transactions/second.
>>What could be the potential problem. Why there is a need for SELECT 
>>query to be submitted to Replicator process?
>>
>>I have increased the shared_buffers from 32MB to 128 MB, so that the 
>>read rows have sufficient space in the cache. I also tried executing 
>>the DB Benchmark tool with single thread and with 10 threads. But could
>
>>not see any major improvement.
>>
>>I'am using ODBC driver "psqlodbc-08.03.0200".
>>
>>regards,
>>Niranjan
>>
>>
>>
>>_______________________________________________
>>Pgcluster-general mailing list
>>Pgcluster-general at pgfoundry.org
>>http://pgfoundry.org/mailman/listinfo/pgcluster-general
>>
>
>_______________________________________________
>Pgcluster-general mailing list
>Pgcluster-general at pgfoundry.org
>http://pgfoundry.org/mailman/listinfo/pgcluster-general
>_______________________________________________
>Pgcluster-general mailing list
>Pgcluster-general at pgfoundry.org
>http://pgfoundry.org/mailman/listinfo/pgcluster-general
>