Dear Thorpe,
There is a problem with the nsdbi* drivers when the cache used for
prepared statements runs out. The driver deallocates in such situations
a prepared statement before being able to add the new one. On a busy
server, it might run in the same, when preparing the next SQL statement,
and so on. In such situations, performance will drop drastically. The
worst are SQL statements with filled-in constants (tcl-substituted
values) or "IN" clauses.
Try to avoid these.
Some background: A few years ago, i generalized the OpenACS SQL
interface to work either with the nsdbi* driver family or with the good
old nsdb* family. On our busy OpenACS servers the dbi-driver was sooner
or later dropping due to the mentioned reasons in such (re)allocation
loops. Due to lack in resources on my side, i never tried to solve the
problem in nsdbi* to avoid such problems. We switched on on all major
installations back to nsdb*.
Since the configured cache listed below is just 1MB (which is nothing on
today's machines), i would recommend to increase it substantially (e.g.
to 10MB), unless you have good reasons for not doing so. ... and replace
".. WHERE name = '$name' ... " by ".. WHERE name = :name ... " if not
done already.
-gn
On 17.09.19 20:57, THORPE MAYES via naviserver-devel wrote:
Hi,
This happened today:
I was getting thousands of these lines in my server log:
[17/Sep/2019:07:59:07][4924.7f82467f4700][-conn:mealdeliverysoftware:19:25314-]
Notice: dbipg: prepare dbipg_6242353/0 cols 0: deallocate dbipg_6238059
[17/Sep/2019:07:59:07][4924.7f82467f4700][-conn:mealdeliverysoftware:19:25314-]
Notice: dbipg: prepare dbipg_6242354/0 cols 0: deallocate dbipg_6238060
[17/Sep/2019:07:59:07][4924.7f82467f4700][-conn:mealdeliverysoftware:19:25314-]
Notice: dbipg: prepare dbipg_6242355/0 cols 0: deallocate dbipg_6238061
I restarted the server and the problem went away (maybe).
My guess is that configuration is either wrong or not complete. Here
is the section in my config file that deals with this:
# database configuration
ns_section "ns/server/${servername}/module/xxxxx"
ns_param default true ;# This is the default pool for mds
ns_param handles 2 ;# Max open handles to db.
ns_param maxwait 10 ;# Seconds to wait if handle
unavailable.
ns_param maxidle 0 ;# Handle closed after maxidle
seconds if unused.
ns_param maxopen 0 ;# Handle closed after maxopen
seconds, regardles of use.
ns_param maxqueries 0 ;# Handle closed after maxqueries
sql queries.
ns_param checkinterval 600 ;# Check for idle handles every 10
minutes.
ns_param maxhandles 0
ns_param timeout 10
ns_param maxrows 1000
ns_param cachesize [expr1024*1024]
# The following parameters are configured at server-startup.
ns_param user “xxxxxx"
ns_param password xxxxxxx
ns_param database “xxxxxx"
ns_param datasource "user=‘xxxxx' password=‘xxxxx' dbname=‘xxxxxx’"
This is also in the config file for my legacy code:
# database pool configuration
ns_section "ns/db/pool/${default_pool}"
ns_param driver postgres
ns_param connections 40
ns_param datasource localhost:5432:${database_name}
ns_param user xxxxxx
ns_param password xxxxxx
ns_param verbose true
ns_param logsqlerrors true
ns_param extendedtableinfo true
ns_param maxidle 1000000000
ns_param maxopen 1000000000
# Database pools accessible by server
ns_section "ns/server/${servername}/db"
ns_param pools "*"
ns_param defaultpool "${default_pool}"
I will appreciate any help/direction.
Thank you,
Thorpe
Thorpe Mayes
2313 Lockhill-Selma Road, Suite 164
San Antonio, Texas 78230-3007
Phone: (512) 394-8766
_______________________________________________
naviserver-devel mailing list
naviserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/naviserver-devel
