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: '[EMAIL PROTECTED]' 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of ext [EMAIL PROTECTED] Sent: Wednesday, June 11, 2008 1:52 PM To: [email protected] 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)"<[EMAIL PROTECTED]> To: <[email protected]> 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 >[email protected] >http://pgfoundry.org/mailman/listinfo/pgcluster-general > _______________________________________________ Pgcluster-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgcluster-general _______________________________________________ Pgcluster-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgcluster-general
