Gustaf,
Thank you for your help and for the background information.
Thorpe
> On Sep 18, 2019, at 1:43 AM, Gustaf Neumann <neum...@wu.ac.at> 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
> naviserver-devel@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/naviserver-devel
_______________________________________________
naviserver-devel mailing list
naviserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/naviserver-devel
