Mysql running out of connections

One of the final instabilities is that we run out of connections which has a duplicate in #156. The trait usually works.

ERROR:wqflask.views:.handle_bad_request: 21:46:07 UTC 20180416: u'http://gn2.genenetwork.org/show_trait?trait_id=19437&dataset=BXDPublish'
ERROR:wqflask.views:.handle_bad_request: 21:46:07 UTC 20180416: Traceback (most recent call last):
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/flask/app.py", line 1639, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/production/gene/wqflask/wqflask/views.py", line 440, in show_trait_page
    template_vars = show_trait.ShowTrait(request.args)
  File "/home/production/gene/wqflask/wqflask/show_trait/show_trait.py", line 52, in __init__
    helper_functions.get_species_dataset_trait(self, kw)
  File "/home/production/gene/wqflask/utility/helper_functions.py", line 17, in get_species_dataset_trait
    self.dataset = data_set.create_dataset(start_vars['dataset'])
  File "/home/production/gene/wqflask/base/data_set.py", line 74, in create_dataset
    return dataset_class(dataset_name, get_samplelist)
  File "/home/production/gene/wqflask/base/data_set.py", line 510, in __init__
    self.retrieve_other_names()
  File "/home/production/gene/wqflask/base/data_set.py", line 571, in retrieve_other_names
    """ % (query_args))
  File "/home/production/gene/wqflask/db/call.py", line 44, in fetchone
    return logger.sql(query, helper)
  File "/home/production/gene/wqflask/utility/logger.py", line 108, in sql
    result = fun(sqlcommand)

  File "/home/production/gene/wqflask/base/data_set.py", line 510, in __init__
    self.retrieve_other_names()
  File "/home/production/gene/wqflask/base/data_set.py", line 571, in retrieve_other_names
    """ % (query_args))
  File "/home/production/gene/wqflask/db/call.py", line 44, in fetchone
    return logger.sql(query, helper)
  File "/home/production/gene/wqflask/utility/logger.py", line 108, in sql
    result = fun(sqlcommand)
  File "/home/production/gene/wqflask/db/call.py", line 42, in helper
    res = g.db.execute(query)
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1990, in execute
    connection = self.contextual_connect(close_with_result=True)
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2039, in contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2078, in _wrap_pool_connect
    e, dialect, self)
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1405, in _handle_dbapi_exception_noconnection
    exc_info
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2074, in _wrap_pool_connect
    return fn()
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/pool.py", line 376, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/pool.py", line 713, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/pool.py", line 480, in checkout
    rec = pool._do_get()
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/pool.py", line 1060, in _do_get
    self._dec_overflow()
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/pool.py", line 1057, in _do_get
    return self._create_connection()
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/pool.py", line 323, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/pool.py", line 449, in __init__
    self.connection = self.__connect()
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/pool.py", line 607, in __connect
    connection = self.__pool._invoke_creator(self)
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 97, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 385, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/MySQLdb/__init__.py", line 86, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/guix-profiles/gn2-latest/lib/python2.7/site-packages/MySQLdb/connections.py", line 204, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
OperationalError: (_mysql_exceptions.OperationalError) (1040, 'Too many connections')

According to mysql status the Mysql server was restarted after above error. Currently we have 100 connections possible and 26 are in use:

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.5.41, for debian-linux-gnu (x86_64) using readline 6.2

Connection id:          161
Current database:       db_webqtl
Current user:           webqtlout@penguin.uthsc.edu
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.95 Source distribution
Protocol version:       10
Connection:             lily.uthsc.edu via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 8 hours 40 min 25 sec

Threads: 26  Questions: 144071  Slow queries: 1  Opens: 182  Flush tables: 1  Open tables: 64  Queries per second avg: 4.614
--------------
show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

SHOW STATUS WHERE `variable_name` = 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 26    |
+-------------------+-------+


#threads
1 when I did a login on staging threads dropped to 1

(gunicorn restarting)

3 when I did a search on shh
5 when I did a combined search for POSITION=(chr1 25 30)
6 when I did another combined search for POSITION=(chr1 25 30)
7 when I did another combined search for POSITION=(chr1 25 30)

(these are gunicorn threads waking up, each has a pooled thread)

7 I went back to staging with has one pool of connections because it runs gunicorn-dev
8 when I did a combined search on staging for POSITION=(chr1 25 30)
9 when I did a combined search on staging for POSITION=(chr1 25 30)

whoa, search is adding to the pool. So that is one section that is not releasing threads.

10 http://gn2-staging.genenetwork.org/show_trait?trait_id=1433387_at&dataset=HC_M2_0606_P
11 http://gn2-staging.genenetwork.org/show_trait?trait_id=1433387_at&dataset=HC_M2_0606_P
12 http://gn2-staging.genenetwork.org/show_trait?trait_id=1433387_at&dataset=HC_M2_0606_P

so, the mapping page also adds threads

This is a script to continuously monitor the number of threads in use on P1:

while true ; do curl "http://gn2-staging.genenetwork.org/show_trait?trait_id=1433387_at&dataset=HC_M2_0606_P" > test.out ; mysql -u webqtlout -h lily.uthsc.edu -ppassword -e "SHOW STATUS WHERE variable_name = 'Threads_connected'" ; done

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 32    |
+-------------------+-------+
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  601k  100  601k    0     0  1866k      0 --:--:-- --:--:-- --:--:-- 1885k
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 33    |
+-------------------+-------+
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  601k  100  601k    0     0  1855k      0 --:--:-- --:--:-- --:--:-- 1873k
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 34    |
+-------------------+-------+
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0^C

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 50    |
+-------------------+-------+

I stopped it at 50. Zach, we can reproducibly add threads to the pool that are not released. Eventually they get cleaned up because gunicorn restarts Python ever so often. But you can see it is quite easy to run out of threads. The solution is not to add threads (100 should be enough), but to fix the items that are not releasing threads.

I think SQLalchemy manages its own threads alright. See

http://docs.sqlalchemy.org/en/latest/core/pooling.html

we should check whether we are doing the right thing. We also use mysqldb connector directly (notably in search):

grep -ri mysqldb *
scripts/maintenance/QTL_Reaper_v6.py:import MySQLdb
scripts/maintenance/QTL_Reaper_v6.py:con = MySQLdb.Connect(db='db_webqtl',user='username',passwd='', host="localhost")
scripts/maintenance/readProbeSetMean_v7.py:import MySQLdb
scripts/maintenance/readProbeSetMean_v7.py:        con = MySQLdb.Connect(db='db_webqtl',host='localhost', user='username',passwd=passwd)
scripts/maintenance/readProbeSetSE_v7.py:import MySQLdb
scripts/maintenance/readProbeSetSE_v7.py:        con = MySQLdb.Connect(db='db_webqtl',host='localhost', user='username',passwd=passwd)
scripts/maintenance/Update_Case_Attributes_MySQL_tab.py:import MySQLdb
scripts/maintenance/Update_Case_Attributes_MySQL_tab.py:mydb = MySQLdb.connect(host='localhost',
wqflask/maintenance/gen_select_dataset.py:import MySQLdb
wqflask/maintenance/gen_select_dataset.py:    Conn = MySQLdb.Connect(**parse_db_uri(SQL_URI))
wqflask/maintenance/generate_probesetfreeze_file.py:import MySQLdb
wqflask/maintenance/generate_probesetfreeze_file.py:    con = MySQLdb.Connect(db=webqtlConfig.DB_UPDNAME,
wqflask/utility/db_tools.py:from MySQLdb import escape_string as escape
wqflask/utility/AJAX_table.py:import MySQLdb
wqflask/utility/AJAX_table.py:                logger.warning("Creating new MySQLdb cursor (this method is OBSOLETE!)")
wqflask/utility/AJAX_table.py:                con = MySQLdb.Connect(db=webqtlConfig.DB_NAME,host=webqtlConfig.MYSQL_SERVER, user=webqtlConfig.DB_USER,passwd=webqtlConfig.DB_PASSWD)
wqflask/base/data_set.py:from MySQLdb import escape_string as escape
wqflask/base/species.py:#from MySQLdb import escape_string as escape
wqflask/base/mrna_assay_tissue_data.py:from MySQLdb import escape_string as escape
wqflask/base/trait.py:from MySQLdb import escape_string as escape
wqflask/db/webqtlDatabaseFunction.py:        logger.warning("Creating new MySQLdb cursor (this method is OBSOLETE!)")
wqflask/db/webqtlDatabaseFunction.py:        con = MySQLdb.Connect(db=webqtlConfig.DB_NAME, host=webqtlConfig.MYSQL_SERVER, user=webqtlConfig.DB_USER, passwd=webqtlConfig.DB_PASSWD)
wqflask/wqflask/do_search.py:from MySQLdb import escape_string as escape
wqflask/wqflask/do_search.py:    import MySQLdb
wqflask/wqflask/do_search.py:    db_conn = MySQLdb.Connect(db=webqtlConfig.DB_NAME,
wqflask/wqflask/network_graph/network_graph.py:from MySQLdb import escape_string as escape
wqflask/wqflask/correlation_matrix/show_corr_matrix.py:from MySQLdb import escape_string as escape
wqflask/wqflask/correlation/show_corr_results.py:from MySQLdb import escape_string as escape
wqflask/wqflask/heatmap/heatmap.py:from MySQLdb import escape_string as escape

and we should replace those with SQLAlchemy calls (the very least share that one connector).

Could just using the "escape" function from an MySQLdb import (and not actually creating a connection) cause an issue? Because I think that's the only way it's actually used that isn't in a script (or in code I removed, like AJAX_table.py or the place in webqtlDatabaseFunction, that will be gone when I push those changes).

On Tue, Apr 17, 2018 at 2:44 AM, Pjotr Prins notifications@github.com wrote:

threads

1 when I did a login on staging threads dropped to 1

(gunicorn restarting)

3 when I did a search on shh 5 when I did a combined search for POSITION=(chr1 25 30) 6 when I did another combined search for POSITION=(chr1 25 30) 7 when I did another combined search for POSITION=(chr1 25 30)

(these are gunicorn threads waking up, each has a pooled thread)

7 I went back to staging with has one pool of connections because it runs gunicorn-dev 8 when I did a combined search on staging for POSITION=(chr1 25 30) 9 when I did a combined search on staging for POSITION=(chr1 25 30)

whoa, search is adding to the pool. So that is one section that is not releasing threads.

10 http://gn2-staging.genenetwork.org/show_trait?trait_id=1433387_at&dataset=HC_M2_0606_P 11 http://gn2-staging.genenetwork.org/show_trait?trait_id=1433387_at&dataset=HC_M2_0606_P 12 http://gn2-staging.genenetwork.org/show_trait?trait_id=1433387_at&dataset=HC_M2_0606_P

so, the mapping page also adds threads

This is a script to continuously monitor the number of threads in use on P1:

while true ; do curl "http://gn2-staging.genenetwork.org/show_trait?trait_id=1433387_at&dataset=HC_M2_0606_P" > test.out ; mysql -u webqtlout -h lily.uthsc.edu -ppassword -e "SHOW STATUS WHERE variable_name = 'Threads_connected'" ; done

+-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 32 | +-------------------+-------+ % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 601k 100 601k 0 0 1866k 0 --:--:-- --:--:-- --:--:-- 1885k +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 33 | +-------------------+-------+ % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 601k 100 601k 0 0 1855k 0 --:--:-- --:--:-- --:--:-- 1873k +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 34 | +-------------------+-------+ % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0^C

+-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 50 | +-------------------+-------+

I stopped it at 50. Zach, we can reproducibly add threads to the pool that are not released. Eventually they get cleaned up because gunicorn restarts Python ever so often. But you can see it is quite easy to run out of threads. The solution is not to add threads (100 should be enough), but to fix the items that are not releasing threads.

I think SQLalchemy manages its own threads alright. See

http://docs.sqlalchemy.org/en/latest/core/pooling.html

we should check whether we are doing the right thing. We also use mysqldb connector directly (notably in search):

grep -ri mysqldb * scripts/maintenance/QTL_Reaper_v6.py:import MySQLdb scripts/maintenance/QTL_Reaper_v6.py:con = MySQLdb.Connect(db='db_webqtl',user='username',passwd='', host="localhost") scripts/maintenance/readProbeSetMean_v7.py:import MySQLdb scripts/maintenance/readProbeSetMean_v7.py: con = MySQLdb.Connect(db='db_webqtl',host='localhost', user='username',passwd=passwd) scripts/maintenance/readProbeSetSE_v7.py:import MySQLdb scripts/maintenance/readProbeSetSE_v7.py: con = MySQLdb.Connect(db='db_webqtl',host='localhost', user='username',passwd=passwd) scripts/maintenance/Update_Case_Attributes_MySQL_tab.py:import MySQLdb scripts/maintenance/Update_Case_Attributes_MySQL_tab.py:mydb = MySQLdb.connect(host='localhost', wqflask/maintenance/gen_select_dataset.py:import MySQLdb wqflask/maintenance/gen_select_dataset.py: Conn = MySQLdb.Connect(**parse_db_uri(SQL_URI)) wqflask/maintenance/generate_probesetfreeze_file.py:import MySQLdb wqflask/maintenance/generate_probesetfreeze_file.py: con = MySQLdb.Connect(db=webqtlConfig.DB_UPDNAME, wqflask/utility/db_tools.py:from MySQLdb import escape_string as escape wqflask/utility/AJAX_table.py:import MySQLdb wqflask/utility/AJAX_table.py: logger.warning("Creating new MySQLdb cursor (this method is OBSOLETE!)") wqflask/utility/AJAX_table.py: con = MySQLdb.Connect(db=webqtlConfig.DB_NAME,host=webqtlConfig.MYSQL_SERVER, user=webqtlConfig.DB_USER,passwd=webqtlConfig.DB_PASSWD) wqflask/base/data_set.py:from MySQLdb import escape_string as escape wqflask/base/species.py:#from MySQLdb import escape_string as escape wqflask/base/mrna_assay_tissue_data.py:from MySQLdb import escape_string as escape wqflask/base/trait.py:from MySQLdb import escape_string as escape wqflask/db/webqtlDatabaseFunction.py: logger.warning("Creating new MySQLdb cursor (this method is OBSOLETE!)") wqflask/db/webqtlDatabaseFunction.py: con = MySQLdb.Connect(db=webqtlConfig.DB_NAME, host=webqtlConfig.MYSQL_SERVER, user=webqtlConfig.DB_USER, passwd=webqtlConfig.DB_PASSWD) wqflask/wqflask/do_search.py:from MySQLdb import escape_string as escape wqflask/wqflask/do_search.py: import MySQLdb wqflask/wqflask/do_search.py: db_conn = MySQLdb.Connect(db=webqtlConfig.DB_NAME, wqflask/wqflask/network_graph/network_graph.py:from MySQLdb import escape_string as escape wqflask/wqflask/correlation_matrix/show_corr_matrix.py:from MySQLdb import escape_string as escape wqflask/wqflask/correlation/show_corr_results.py:from MySQLdb import escape_string as escape wqflask/wqflask/heatmap/heatmap.py:from MySQLdb import escape_string as escape

and we should replace those with SQLAlchemy calls (the very least share that one connector).

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/genenetwork/genenetwork2/issues/319#issuecomment-381883093, or mute the thread https://github.com/notifications/unsubscribe-auth/ABsEmI0rOC6lKtp5DyVcKVN7_h9Rn8aIks5tpZ11gaJpZM4TXu-o .

Ah wait I found something potentially strange. The query to get threads connected is helpful; at least now I can see when they're being connected and not removed.

edit: Nevermind, it still seems like MySQLdb isn't being used in any way (during GN2 runtime anyways) other than importing the "escape_string" function. It should never be creating any connections, but a single new thread is created and not closed when you load both the search and trait pages. This seems to imply that the issue isn't caused by creating trait or data set objects, because if it was it seems like more than 1 thread would be created when you do a search.

On Tue, Apr 17, 2018 at 10:46 AM, Zachary Sloan zachary.a.sloan@gmail.com wrote:

Could just using the "escape" function from an MySQLdb import (and not actually creating a collection) cause an issue? Because I think that's the only way it's actually used that isn't in a script (or in code I removed, like AJAX_table.py or the place in webqtlDatabaseFunction, that will be gone when I push those changes).

On Tue, Apr 17, 2018 at 2:44 AM, Pjotr Prins notifications@github.com wrote:

threads

1 when I did a login on staging threads dropped to 1

(gunicorn restarting)

3 when I did a search on shh 5 when I did a combined search for POSITION=(chr1 25 30) 6 when I did another combined search for POSITION=(chr1 25 30) 7 when I did another combined search for POSITION=(chr1 25 30)

(these are gunicorn threads waking up, each has a pooled thread)

7 I went back to staging with has one pool of connections because it runs gunicorn-dev 8 when I did a combined search on staging for POSITION=(chr1 25 30) 9 when I did a combined search on staging for POSITION=(chr1 25 30)

whoa, search is adding to the pool. So that is one section that is not releasing threads.

10 http://gn2-staging.genenetwork.org/show_trait?trait_id=1433387_at&dataset=HC_M2_0606_P 11 http://gn2-staging.genenetwork.org/show_trait?trait_id=1433387_at&dataset=HC_M2_0606_P 12 http://gn2-staging.genenetwork.org/show_trait?trait_id=1433387_at&dataset=HC_M2_0606_P

so, the mapping page also adds threads

This is a script to continuously monitor the number of threads in use on P1:

while true ; do curl "http://gn2-staging.genenetwork.org/show_trait?trait_id=1433387_at&dataset=HC_M2_0606_P" > test.out ; mysql -u webqtlout -h lily.uthsc.edu -ppassword -e "SHOW STATUS WHERE variable_name = 'Threads_connected'" ; done

+-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 32 | +-------------------+-------+ % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 601k 100 601k 0 0 1866k 0 --:--:-- --:--:-- --:--:-- 1885k +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 33 | +-------------------+-------+ % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 601k 100 601k 0 0 1855k 0 --:--:-- --:--:-- --:--:-- 1873k +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 34 | +-------------------+-------+ % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0^C

+-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 50 | +-------------------+-------+

I stopped it at 50. Zach, we can reproducibly add threads to the pool that are not released. Eventually they get cleaned up because gunicorn restarts Python ever so often. But you can see it is quite easy to run out of threads. The solution is not to add threads (100 should be enough), but to fix the items that are not releasing threads.

I think SQLalchemy manages its own threads alright. See

http://docs.sqlalchemy.org/en/latest/core/pooling.html

we should check whether we are doing the right thing. We also use mysqldb connector directly (notably in search):

grep -ri mysqldb * scripts/maintenance/QTL_Reaper_v6.py:import MySQLdb scripts/maintenance/QTL_Reaper_v6.py:con = MySQLdb.Connect(db='db_webqtl',user='username',passwd='', host="localhost") scripts/maintenance/readProbeSetMean_v7.py:import MySQLdb scripts/maintenance/readProbeSetMean_v7.py: con = MySQLdb.Connect(db='db_webqtl',host='localhost', user='username',passwd=passwd) scripts/maintenance/readProbeSetSE_v7.py:import MySQLdb scripts/maintenance/readProbeSetSE_v7.py: con = MySQLdb.Connect(db='db_webqtl',host='localhost', user='username',passwd=passwd) scripts/maintenance/Update_Case_Attributes_MySQL_tab.py:import MySQLdb scripts/maintenance/Update_Case_Attributes_MySQL_tab.py:mydb = MySQLdb.connect(host='localhost', wqflask/maintenance/gen_select_dataset.py:import MySQLdb wqflask/maintenance/gen_select_dataset.py: Conn = MySQLdb.Connect(**parse_db_uri(SQL_URI)) wqflask/maintenance/generate_probesetfreeze_file.py:import MySQLdb wqflask/maintenance/generate_probesetfreeze_file.py: con = MySQLdb.Connect(db=webqtlConfig.DB_UPDNAME, wqflask/utility/db_tools.py:from MySQLdb import escape_string as escape wqflask/utility/AJAX_table.py:import MySQLdb wqflask/utility/AJAX_table.py: logger.warning("Creating new MySQLdb cursor (this method is OBSOLETE!)") wqflask/utility/AJAX_table.py: con = MySQLdb.Connect(db=webqtlConfig.DB_NAME,host=webqtlConfig.MYSQL_SERVER, user=webqtlConfig.DB_USER,passwd=webqtlConfig.DB_PASSWD) wqflask/base/data_set.py:from MySQLdb import escape_string as escape wqflask/base/species.py:#from MySQLdb import escape_string as escape wqflask/base/mrna_assay_tissue_data.py:from MySQLdb import escape_string as escape wqflask/base/trait.py:from MySQLdb import escape_string as escape wqflask/db/webqtlDatabaseFunction.py: logger.warning("Creating new MySQLdb cursor (this method is OBSOLETE!)") wqflask/db/webqtlDatabaseFunction.py: con = MySQLdb.Connect(db=webqtlConfig.DB_NAME, host=webqtlConfig.MYSQL_SERVER, user=webqtlConfig.DB_USER, passwd=webqtlConfig.DB_PASSWD) wqflask/wqflask/do_search.py:from MySQLdb import escape_string as escape wqflask/wqflask/do_search.py: import MySQLdb wqflask/wqflask/do_search.py: db_conn = MySQLdb.Connect(db=webqtlConfig.DB_NAME, wqflask/wqflask/network_graph/network_graph.py:from MySQLdb import escape_string as escape wqflask/wqflask/correlation_matrix/show_corr_matrix.py:from MySQLdb import escape_string as escape wqflask/wqflask/correlation/show_corr_results.py:from MySQLdb import escape_string as escape wqflask/wqflask/heatmap/heatmap.py:from MySQLdb import escape_string as escape

and we should replace those with SQLAlchemy calls (the very least share that one connector).

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/genenetwork/genenetwork2/issues/319#issuecomment-381883093, or mute the thread https://github.com/notifications/unsubscribe-auth/ABsEmI0rOC6lKtp5DyVcKVN7_h9Rn8aIks5tpZ11gaJpZM4TXu-o .

While sleuthing make sure to read and understand the Alchemy page I added above. To be on the safe side we should always close connections once used. Connection pooling is not really needed because we fire up long 'running' pages anyway. You can use one database connection across a page. Other optimizations can come later. So, if we pool, disable. That is my suggestion. Close connections. See what happens. We do the same with elasticsearch already. A new connection is started for every page request.

@zsloan this is an interesting link: https://stackoverflow.com/questions/8645250/how-to-close-sqlalchemy-connection-in-mysql, especially the first answer. I suggest we use NullPool now and try to close every connection once it is used. NullPool won't cache pooled connections so there should be a direct relationship with connections shown to be in use.

Btw the escape function of MySQLdb should not use a connection, but we should not use those methords - replace them with SQLAlchemy which does not need escaping (it is built in).

Been stable for some time.

genenetwork / genenetwork2

Mysql running out of connections #319

threads

threads