Keep mysql connection open

Question

I'm making a eggdrop tcl script to write activity of several public IRC channels to a database (over time this will be 10 to 15 channels I think). I have two options how to handle the database connection in mind.

1. An user says something -> Open a mysql connection to the database -> insert information about what the user said -> close the connection
2. Start the bot -> Open a mysql connection to the database -> Insert information when there is channel activity -> Wait for more information etc.

I think it's better to use case 1, but when there is much channel activity I think opening and closing a connection every time will cause a massive server load and slows things down drastically after a while.

What's the best way to do this?

Answer 1

If you want to keep the connection open just call

mysql::ping $dbhandle

from time to time.

This can be done with something like this:

proc keepMySqlOpen {dbhandle} {
    mysql::ping $dbhandle
    after 2000 [list keepMySqlOpen $dbhandle]
}
....
    set dbh [mysql::open ...]
    keepMySqlOpen $dbh
...

An other option is just to use mysql::ping before accessing the db, which should according to the mysqltcl manual reconnect if necessary. This might be the best of both worlds (let the connection time out if there is not much activity, keep it open otherwise).