Talking about expected performance magnitudes: On one of our OpenACS-based systems, we have currently 400 mio acs-objects (422,722,032), which are tuples of a single table. The full system has 1,168 tables and 452 views. On this system, we see an average response time from the DB of <1ms:
statements 460.5M gethandles 14.7M handles 170 connected 73 used 130 waittime 12.686245 sqltime 331744.583118 avgwaittime 862.3ns avgsqltime 720.4µs
But of course, on every large db one can get slow responses from the DB, it really depends on the queries.
Concerning "good" and "bad" queries for nsdbi: The simple rule is to use always bin variables, and avoid plain Tcl substitution.
Good (with bind variable):
set x 1
dbi_1row {select column from table where key = :x}
Bad (without bind variable):
set x 1
dbi_1row "select column from table where key = $x"
If one has e.g. 10.000 different values for variable x, in the first case, one ends up with just a single prepared statement, in the latter case with 10K prepared statements.
When nsdbi has to clean up its prepared cache, performance degrades, since potentially, for every statement there will be a cleanup operation, the generation of a new prepared statement, and then the execution of the query. The first two steps require in addition mutex locks, thus reducing concurrency. When there is already the prepared statement, just the last step is needed.
