Determining database response time

We are in the process of replicating some databases where most of the operations would be read-only. And there are application servers that have created connection pools to these databases. We would like to run some kind of query that tells us that, this db is taking, say for example, 10ms to return and that database is taking 20secs to return the results. In this case, we would make the connection to the first db and route the user request to the first one. In case the first db is taking more time than the second one, we would route the request to the second one.

We are trying to figure out what could be the best algorithm to determine the database's response time. Say for example, I could run a simple select sql and calculate the time taken for its execution in both the dbs and determine which one is better. But this some how seems to not reflect the actual statistics of the db response time. Or may be I could calculate the weighted average of the query execution time for that database over a period of time and determine which one is better. I'm not sure which one would be a good way.

Has anyone faced a similar situation. If so, what's your experience? I would like to hear some thoughts on how people could implement this.

Thanks,
Kalyan. Received on Fri Feb 04 2005 - 17:54:02 CET