I'm writing a script to scrape data from a website and save it in a
database.

The web site offers games and supports multiple users, who play against
each other in multiple tournaments every day. The first time the script
is run, the idea is to capture data, including their opponents, for a
specified user over the past 24 hours. On subsequent runs, we capture
data for all users that we know about.

So, I expect that I'll be scraping more and more data as time goes by.
I've run it for a few days writing data to a SQLite database but after a
couple of weeks it now takes more than 24 hours to run to completion.
I'd like to see how long it takes if I use a MySQL database.
Unfortunately, on the second run the script reports the error:

"Can't create more that max_prepared_stmt_count statements (current
value 16382)"

Now, I could simply increase the value of the MySQL variable, maybe to
its maximum allowed value of 4194304. However, when it failed, it was
trying to handle 1000 users. I know from the SQLite prototype that I
need to be able to hand more than 80000 users. Upping the limit might
work and might not kill the machine hosting the database, but I'd really
like to find a better solution.

As far as I'm aware I'm only creating about a dozen (12) prepared
statements. I have noticed, however, that I have a large number of
tdbc::mysql::resultset objects - 16375 when the server complained, and I
wonder whether these objects create a prepared statement that they
release when they are destroyed. (I saw the same growth - of
tdbc::sqlite3::resultset objects - when saving to a SQLite database.)

I am nearly certain that I'm not creating these resultset objects
directly myself, I think all my interaction with tdbc is via db prepare
and statement foreach/allrows calls but I imagine that freach and/or
allrows will create a temporary resultset that it (should) manage itself.

Does the above description ring any bells with anyone here? If not, any
suggestions as to how I should proceed?

Alan

On 4/29/2024 9:03 AM, Alan Grunwald wrote:
>
> So, I expect that I'll be scraping more and more data as time goes by.
> I've run it for a few days writing data to a SQLite database but after a
> couple of weeks it now takes more than 24 hours to run to completion.
> I'd like to see how long it takes if I use a MySQL database.
> Unfortunately, on the second run the script reports the error:
>
> "Can't create more that max_prepared_stmt_count statements (current
> value 16382)"
>
....
>
> Does the above description ring any bells with anyone here? If not, any
> suggestions as to how I should proceed?
>
> Alan

I have used oratcl but without any tdbc wrapper so my experience with
tdbc is basically nil. But it looks like you may not be releasing some
of the objects that are created for you during normal operation. This
includes result sets, even though in your case, they may be empty.

Alternatively, have you tried using sqlite directly without tdbc around
it? Because sqlite already comes with a pretty good interface
especially from tcl.

Am 29.04.24 um 15:03 schrieb Alan Grunwald:
> I'm writing a script to scrape data from a website and save it in a
> database.
>
> The web site offers games and supports multiple users, who play against
> each other in multiple tournaments every day. The first time the script
> is run, the idea is to capture data, including their opponents, for a
> specified user over the past 24 hours. On subsequent runs, we capture
> data for all users that we know about.
>
> So, I expect that I'll be scraping more and more data as time goes by.
> I've run it for a few days writing data to a SQLite database but after a
> couple of weeks it now takes more than 24 hours to run to completion.
> I'd like to see how long it takes if I use a MySQL database.
> Unfortunately, on the second run the script reports the error:
>
> "Can't create more that max_prepared_stmt_count statements (current
> value 16382)"
>
> Now, I could simply increase the value of the MySQL variable, maybe to
> its maximum allowed value of 4194304. However, when it failed, it was
> trying to handle 1000 users. I know from the SQLite prototype that I
> need to be able to hand more than 80000 users. Upping the limit might
> work and might not kill the machine hosting the database, but I'd really
> like to find a better solution.
>
> As far as I'm aware I'm only creating about a dozen (12) prepared
> statements. I have noticed, however, that I have a large number of
> tdbc::mysql::resultset objects - 16375 when the server complained, and I
> wonder whether these objects create a prepared statement that they
> release when they are destroyed. (I saw the same growth - of
> tdbc::sqlite3::resultset objects - when saving to a SQLite database.)
>
> I am nearly certain that I'm not creating these resultset objects
> directly myself, I think all my interaction with tdbc is via db prepare
> and statement foreach/allrows calls but I imagine that freach and/or
> allrows will create a temporary resultset that it (should) manage itself.
>
> Does the above description ring any bells with anyone here? If not, any
> suggestions as to how I should proceed?
>
> Alan

Are you using close?

https://www.tcl.tk/man/tcl/TdbcCmd/tdbc_statement.htm
The close object command removes a statement and any result sets that
it has created. All system resources associated with the objects are freed.

https://www.tcl.tk/man/tcl/TdbcCmd/tdbc_resultset.htm
The close object command deletes the result set and frees any associated
system resources.

> I've run it for a few days writing data to a SQLite database but after a
> couple of weeks it now takes more than 24 hours to run to completion.

Are you using transaction and commit?
https://www.tcl-lang.org/man/tcl/TdbcCmd/tdbc_connection.htm
db begintransaction
db commit

https://www.magicsplat.com/articles/tdbc.html#_transactions

a example:
proc sqlInsertCsv {dbconn datalist} {
$dbconn begintransaction
set stmt [$dbconn prepare {
INSERT INTO csvdaten (one,two,three) VALUES (:one,:two,:three)}]

foreach dataline $datalist {
foreach {one two three} $dataline {
$stmt execute
}
}
$dbconn commit
$stmt close
}

Gregor

On 29/04/2024 20:28, greg wrote:

<snipped problem statement>

> Are you using close?
>
> https://www.tcl.tk/man/tcl/TdbcCmd/tdbc_statement.htm
> The close object command removes a statement and any result sets that
> it has created. All system resources associated with the objects are
freed.
>
> https://www.tcl.tk/man/tcl/TdbcCmd/tdbc_resultset.htm
> The close object command deletes the result set and frees any associated
> system resources.
>
I'm not consciously creating any resultset objects, so am not closing
any either.

Everything is wrapped up in a 'database' object, including around a
dozen long-lived statement objects. The statement objects are destroyed
and the database connection is closed when my 'database' object is
destroyed.

>
> > I've run it for a few days writing data to a SQLite database but
after a
> > couple of weeks it now takes more than 24 hours to run to completion.
>
> Are you using transaction and commit?
The whole web-scraping activity is wrapped in a try statement; the first
statement within the try is begintransation; if execution reaches the
end of the try the transaction is committed; if if there is an error,
the trasaction is rolled back.

I'm hoping to debug where and why the resultset leak is happening. If I
manage to sort that out and it doesn't fix the prepared statement leak,
I'll have to go and search for that.

> https://www.tcl-lang.org/man/tcl/TdbcCmd/tdbc_connection.htm
> db begintransaction
> db commit
>
> https://www.magicsplat.com/articles/tdbc.html#_transactions
>
>
> a example:
> proc sqlInsertCsv {dbconn datalist} {
> $dbconn begintransaction
> set stmt [$dbconn prepare {
> INSERT INTO csvdaten (one,two,three) VALUES (:one,:two,:three)}]
>
> foreach dataline $datalist {
> foreach {one two three} $dataline {
> $stmt execute
> }
> }
> $dbconn commit
> $stmt close
> }
>
>
>
> Gregor
>

I've just had another look at the tdbc_statement manual page and I see
that this may well be designed behaviour; I see that [statement execute]
returns a resultset object. I had not noticed that before
and am very sure that I haven't closed these objects.

It's far too late for me to start looking through my code to see whether
I do use the execute object command, and whether if I delete the
returned objects I can prevent the buildup - I'd be amazed if it
doesn't, but it's just about possible that I'm using the allrows object
command everywhere (which is what I usually do, and which manages the
resultset object internally.

I'll post to this thread again tomorrow with results and news on whether
this fixes the prepared statement buildup on the server side.

Thanks for pointing me at the documentation!

Am 30.04.24 um 00:00 schrieb Alan Grunwald:

> The whole web-scraping activity is wrapped in a try statement; the first
> statement within the try is begintransation; if execution reaches the
> end of the try the transaction is committed; if if there is an error,
> the trasaction is rolled back.

Yes, the better way is to use try.

> I've just had another look at the tdbc_statement manual page and I see
> that this may well be designed behaviour; I see that [statement execute]
> returns a resultset object. I had not noticed that before
> and am very sure that I haven't closed these objects.
>

According to the manual it should already happen with close of the tdbc
statement.

https://www.tcl.tk/man/tcl/TdbcCmd/tdbc_statement.htm
The close object command removes a statement and any result sets that
it has created. All system resources associated with the objects are freed.
!

I only got involved with introspection here after your question. I found
something new about tdbc for myself.

https://www.magicsplat.com/articles/tdbc.html#_introspection

and

https://www.tcl-lang.org/man/tcl/TdbcCmd/tdbc_connection.htm
db statements
The statements object command returns a list of statements that have
been created by prepare and preparecall statements against the given
connection and have not yet been closed.

db resultsets
The resultsets object command returns a list of result sets that have
been obtained by executing statements prepared using the given
connection and not yet closed.

On 30/04/2024 05:15, greg wrote:
> Am 30.04.24 um 00:00 schrieb Alan Grunwald:
>
>
> > The whole web-scraping activity is wrapped in a try statement; the first
> > statement within the try is begintransation; if execution reaches the
> > end of the try the transaction is committed; if if there is an error,
> > the trasaction is rolled back.
>
> Yes, the better way is to use try.
>
>
>> I've just had another look at the tdbc_statement manual page and I see
>> that this may well be designed behaviour; I see that [statement
>> execute] returns a resultset object. I had not noticed that before
>> and am very sure that I haven't closed these objects.
>>
>
> According to the manual it should already happen with close of the tdbc
> statement.
>
> https://www.tcl.tk/man/tcl/TdbcCmd/tdbc_statement.htm
>  The close object command removes a statement and any result sets that
> it has created. All system resources associated with the objects are freed.
> !
>
>
> I only got involved with introspection here after your question. I found
> something new about tdbc for myself.
>
> https://www.magicsplat.com/articles/tdbc.html#_introspection
>
> and
>
> https://www.tcl-lang.org/man/tcl/TdbcCmd/tdbc_connection.htm
> db statements
>  The statements object command returns a list of statements that have
> been created by prepare and preparecall statements against the given
> connection and have not yet been closed.
>
> db resultsets
> The resultsets object command returns a list of result sets that have
> been obtained by executing statements prepared using the given
> connection and not yet closed.
>
>
>
Executive summary

I had a look through my code and found that I _was_ using execute object
commands in several places and wasn't closing the resultset objects that
they returned. I changed my code so that I _did_ close the returned
resultset objects and that not only prevented the buildup of resultset
objects in my script, but also fixed the server-side prepared statements
problem.

Thank you

tl;dr

My database has a a few columns that refer to rows in other tables, so
the script has lots of code that goes

Execute a SELECT statement find the row in the reference table
if no such row
Execute an INSERT statement to add a reference row
endif

I was using a foreach object command to execute the SELECT statements,
but since they didn't return any database information I used execute for
the INSERT statements. I now understand why that is a VERY BAD idea.