Sun Nov 17, 2019 9:29 am
Login Register Lost Password? Contact Us


MySql connections staying open but not reused

Questions around writing code and queries

Fri Mar 24, 2017 1:56 pm Change Time Zone

Given a simple table

Code: Select all
CREATE TABLE TestTable (id int);

with 10 records, if I run

Code: Select all
IMPORT MySQL;

TestLayout := RECORD
  INTEGER Id;
END;

DATASET(TestLayout) LoadTestTable(INTEGER minLimit = 0) := EMBED(mySql : server('myserver'), database('mydb'), user('myuser'), password('mypass'), port('myport'))
  SELECT ID FROM TestTable WHERE ID > ?;
ENDEMBED;

LoadTestTable();
LoadTestTable(5);

the db connection gets closed (SHOW PROCESSLIST in MySql does not show any connections in sleep state).

If I just append

Code: Select all
DATASET(
  [
    {'A', 1},
    {'B', 2},
    {'A', 3},
    {'C', 4},
    {'B', 5}
  ],
  {STRING S, INTEGER I}
);

at the end of that code, and run it all, the connection stays in the sleep state. Every time I run the code, a new connection is opened and remains open until the timeout (default 8h on MySql). After a while "mysql: Failed to connect (Too many connections)" happens.

I would expect the connection to be closed every time when the query in the embed is completed, or at least to be reused if it is kept open. As a workaround we've reduced the wait-timeout on MySql to 5min, but I would like to understand this behaviour better.

So, am I doing something wrong here, is there a bug causing connections to sometimes stay open, or is this expected behaviour (and if so, what are the rules)?
NP
 
Posts: 11
Joined: Fri Mar 24, 2017 1:13 pm

Thu Apr 20, 2017 2:12 pm Change Time Zone

Can you provide a few details:

What version of the HPCC platform are you running?

Are you running against Thor? Roxie? Eclagent?

The MySQL plugin will attempt to cache connections to be reused by future queries, and it may be that what you are observing is related to that - though if the number is continuing to rise then it suggests something is not working as intended.
richardkchapman
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 109
Joined: Fri Jun 17, 2011 8:59 am

Thu Apr 20, 2017 2:14 pm Change Time Zone

My guess would be that the addition of the extra DATASET statement pushes the query from being simple enough to run purely on eclagent (which will terminate at the end of each run and free the connections) to running on thor (which stays loaded and should try to cache the connections for reuse). But I'd have to try to reproduce it to be sure (and the information I requested would be useful for that)
richardkchapman
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 109
Joined: Fri Jun 17, 2011 8:59 am

Thu Apr 20, 2017 2:57 pm Change Time Zone

A little experimentation confirms that the difference between your scenarios is that one will hit thor while the other does not - and that thor is leaking MySQL connections when a workunit is unloaded.

I have raised a ticket at https://track.hpccsystems.com/browse/HPCC-17450

You should be able to work around the issue by requesting that the MySQL connection is not cached by adding CACHE(0) to the options list on the EMBED statement.
richardkchapman
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 109
Joined: Fri Jun 17, 2011 8:59 am

Thu Apr 20, 2017 2:58 pm Change Time Zone

(and thanks for the report - I wish all problem reports were as precise as this, with simple steps to reproduce!)
richardkchapman
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 109
Joined: Fri Jun 17, 2011 8:59 am

Fri Apr 21, 2017 9:17 am Change Time Zone

Hi Richard,

Thanks for looking into this.

Sorry, a bit late, but -

richardkchapman wrote:Are you running against Thor? Roxie? Eclagent?
I was running this on THOR when I spotted the problem.

richardkchapman wrote:What version of the HPCC platform are you running?
6.2.2

richardkchapman wrote:and that thor is leaking MySQL connections when a workunit is unloaded.
I thought it might be something like that.

You should be able to work around the issue by requesting that the MySQL connection is not cached by adding CACHE(0) to the options list on the EMBED statement.
thanks for the workaround! I tried a few things myself, but could not find one that worked. Our workaround was to get mysql to kill off connections after 5 minutes ;)

richardkchapman wrote:(and thanks for the report - I wish all problem reports were as precise as this, with simple steps to reproduce!)
Glad it was helpful
NP
 
Posts: 11
Joined: Fri Mar 24, 2017 1:13 pm


Return to Programming

Who is online

Users browsing this forum: Bing [Bot] and 2 guests

cron