Interpreting connect pool results

[jsrz]

The wording of the documentation discussing connect pool testing is confusing. I think I understand what it's trying to say, but I'd like to discuss it a little to make sure my understanding of this documentation is right.

Let's work through an example, and then I want to ask some questions.
I have a 3 node postgres cluster. 1 primary(RW), 2 secondary (RO). I configured the connect pool to send payment, delivery, new order to the primary, and order status and stock level to the secondary. I've also configured "tpcc" (not tpcc1, tpcc2, tpcc3 like the docs) as the single database for all 3 connections.

I run a short test with time profile enabled, a 1min ramp, and 6min run, and this is what i see:

Vuser 1:Test complete, Taking end Transaction Count.                                                     
Vuser 1:8 Active Virtual Users configured                                                                
Vuser 1:TEST RESULT : System achieved 302239 NOPM from 634685 PostgreSQL TPM                                                                                                                                       
Vuser 1:Gathering timing data from Active Virtual Users...                                                                                                                                                         
Vuser 4:VU4 processed neworder 263176 payment 262957 delivery 26338 stocklevel 26151 orderstatus 26226 transactions
Vuser 7:VU7 processed neworder 258338 payment 258159 delivery 25940 stocklevel 25657 orderstatus 25826 transactions
Vuser 2:VU2 processed neworder 262196 payment 262663 delivery 26434 stocklevel 26482 orderstatus 26513 transactions
Vuser 5:VU5 processed neworder 265924 payment 266117 delivery 26546 stocklevel 26458 orderstatus 26283 transactions
Vuser 3:VU3 processed neworder 268924 payment 267666 delivery 26858 stocklevel 27072 orderstatus 26952 transactions
Vuser 8:VU8 processed neworder 248794 payment 249146 delivery 24858 stocklevel 24692 orderstatus 24926 transactions
Vuser 9:VU9 processed neworder 257158 payment 257543 delivery 25681 stocklevel 25655 orderstatus 25835 transactions
Vuser 6:VU6 processed neworder 258944 payment 259338 delivery 25892 stocklevel 26118 orderstatus 25676 transactions
Vuser 4:FINISHED SUCCESS                                                                                 
Vuser 2:FINISHED SUCCESS                            
Vuser 7:FINISHED SUCCESS                            
Vuser 5:FINISHED SUCCESS                                                                                 
Vuser 6:FINISHED SUCCESS                            
Vuser 1:Calculating timings...                                                                           
Vuser 1:Writing timing data to /tmp/hdbxtprofile_633073DE5E9803E203430323.log                                                                                                                                      
Vuser 8:FINISHED SUCCESS                            
Vuser 9:FINISHED SUCCESS                            
Vuser 3:FINISHED SUCCESS                                                                                 
Timer: 7 minutes elapsed                            
Vuser 1:CLIENT SIDE TPM : neworder 297636 payment 297655 delivery 29792 stocklevel 29755 orderstatus 29748
Vuser 1:FINISHED SUCCESS

Questions:

• The reason Client Side result for neworder (297636) is slightly lower than the monitor user's NOPM (302239) is because the client side result factors in the 1min of ramp time, while the monitor user doesn't? Is this a correct interpretation?
• As a side experiment, I set ramp time to 0min. My client side new order was actually slightly higher (I don't have the number on hand) than the monitor user's NOPM. I would have expected them to be equal. In theory, they should be equal, correct?
• Is it fair to say that the purpose of the client side result is to help as a sanity check on the monitor user NOPM result? That is to say, the fact that I see these results as pretty close to each other, say's that I am in fact testing the entire cluster, and thus, I can take the NOPM result as a cluster wide performance metric? Is this a correct interpretation?
• Contrast this with the documentation, were 3 different databases are setup with the connections (tpcc1, tpcc2, tpcc3). The monitor user is only going to report results on tpcc1 database queries, hence the 3x difference between client result and monitor user. Is this a correct understanding? Basically, this is the scenario where sanity checking client side result against monitor user result should make you ask yourself if you are testing what you think you are testing (3dbs versus 1 db that is replicated across 3 nodes)? Correct?

[sm-shaw]

Hi, Many thanks for the questions and yes your understanding in the context of PostgreSQL is correct, probably the main reason for the confusion is that different databases can work in different ways in a cluster, with Oracle RAC the best example. I'll answer the questions directly in turn and address this for the last question that should explain the way it works.

• The reason Client Side result for neworder (297636) is slightly lower than the monitor user's NOPM (302239) is because the client side result factors in the 1min of ramp time, while the monitor user doesn't? Is this a correct interpretation?

Yes, this is correct, it is known, and it is also the same for the time profiling #233 so during the rampup phase not all virtual users will be connected and the data may not be cached so there is more I/O - both of these reasons will account for lower throughput.

• As a side experiment, I set ramp time to 0min. My client side new order was actually slightly higher (I don't have the number on hand) than the monitor user's NOPM. I would have expected them to be equal. In theory, they should be equal, correct?

In theory yes they should be equal but they are measured differently and we are relying on the database for the server side result whereas the client is counting how many times the client transaction is called. Also there are intentional rollbacks as part of new order so this will also account for some of the difference as the client just measures the number of times NEWORD is called whereas the server will report when it completed by updating the district table metric.

• Is it fair to say that the purpose of the client side result is to help as a sanity check on the monitor user NOPM result? That is to say, the fact that I see these results as pretty close to each other, say's that I am in fact testing the entire cluster, and thus, I can take the NOPM result as a cluster wide performance metric? Is this a correct interpretation?

Yes, this is exactly correct, it is a check on the server side result. So for example with Oracle RAC you can point the transactions to any of the nodes in the cluster as if it were one database instance and therefore any server you connect to with the main connection will report the same clusterwide NOPM metric. However, if you took this approach with a database that does not view transactions cluster wide (i.e. run new order transactions against multiple nodes in the database) then the numbers from client and server will not align. Therefore, the client side metrics enable you to clearly see whether your configuration makes sense in the context of running a valid test.

• Contrast this with the documentation, were 3 different databases are setup with the connections (tpcc1, tpcc2, tpcc3). The monitor user is only going to report results on tpcc1 database queries, hence the 3x difference between client result and monitor user. Is this a correct understanding? Basically, this is the scenario where sanity checking client side result against monitor user result should make you ask yourself if you are testing what you think you are testing (3dbs versus 1 db that is replaced across 3 nodes)? Correct?

Yes, again, this is exactly correct! Given more time I would have used Oracle RAC, the example is taken from a desktop/laptop to write the documentation, hence the discrepancy. But your understanding is correct - the workload enables you to direct individual transactions against multiple nodes in a cluster regardless of how the database itself handles these transactions and whether there is functionality to report cluster wide metrics, Therefore the client side metrics are an additional check to report where the individual virtual users sent their transactions.

[jsrz]

Thanks for the quick response. Ok, this all makes sense in my head now. :)