Menu
▾
▴
Re: [naviserver-devel] Question re nsdbi
|
From: Gustaf N. <ne...@wu...> - 2019-09-18 06:43:44
|
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
|
