Gustaf, Thank you for your help and for the background information.
Thorpe > On Sep 18, 2019, at 1:43 AM, Gustaf Neumann <[email protected]> wrote: > > 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 [expr 1024*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 > [email protected] > https://lists.sourceforge.net/lists/listinfo/naviserver-devel
_______________________________________________ naviserver-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/naviserver-devel
