Hi Nima,
you should log the queries in your postgresql instance. Then you can analyze the longest running/slowest queries. You can use the "Postgres Query Analyzer" to do that. get it here: http://pqa.projects.postgresql.org/
In the postgresql.conf file you have to activate:
# - What to Log -
log_duration = true
log_pid = true
log_statement = true
log_timestamp = true
I would also propose:
# - Query/Index Statistics Collector -
stats_start_collector = true
stats_command_string = true
Then you can interactively see the current running queries with "select * from pg_stat_activity ;"
In addition you should monitor aolserver thread usage with the telemetry page from the "nstelemetry" package (from aolserver.com). Maybe you have to few aolserver threads available (ns_section "ns/server/${server}" MaxThreads). With the telemetry page you can also find out how may page-request are currently in the wait queue of the aolserver.
In the proc "db_with_handle" add some code to find out how long you have to wait for available db-handles:
<snip>
set pool [db_nth_pool_name -dbn $dbn $db_state(n_handles_used)]
set start_time [clock clicks -milliseconds]
set errno [catch {
set timer [clock clicks -milliseconds]
set db [ns_db gethandle $pool]
set duration [expr [clock clicks -milliseconds] - $timer]
if { $duration > 100 } {
ns_log Notice "Got db-handle from $pool after $duration MS"
}
} error]
</snip>
Maybe you have to few db-handles available and therefor threads are hanging around waiting without doing anything. Keep in mind that you have to increase the available db-connections when you rise the number of aolserver threads.
Install some system statistic collector. I propose HotSanic, it generates nice html-stats you can link into your web-site. (get it here: http://hotsanic.sourceforge.net/). You can use it to find bottlenecks and monitor resource usage.
Last but not least i could provide you with a simple benchmarking application that starts aolserver threads and issues db-queries. requests are not coming from the outside of the server (via http) but from within, but it works quite nice to stress the server. It is written in xotcl and you would have to modify it for your needs, but the framework of starting threads, monitoring transactions, running custom transactions and the like should provide you with a start.
hope that helps, peter
