0

I am having trouble with my server. It is a CentOS RedHat Linux server and runs "Dashing" a Ruby/Sinatra-based dashboard.

I am trying to close the active connections as defined by my MySQL database "SHOW PROCESSLIST;"

Example.rb File

require 'mysql2'

SCHEDULER.every '10s'do

db = Mysql.new('host_name', 'database_name', 'password', 'table')

mysql1 = "SELECT `VAR` from `TABLE` ORDER BY `VAR` DESC LIMIT 1"
result1 = db.query(mysql1)
result1.each do |row|
strrow1 = row[0]
$num1 = strrow1.to_i
end
...

db.close

LINK[0] = { label: 'LABEL', value: $num1}
...

send_event('LABEL FOR HTML', { items: LINK.values })
end

However, after a few clicks back and forth, it is clear that the database does not drop the connections, but instead keeps them. This causes the browser to slow down to the point that loading a page becomes impossible and the output of the log reads:

"max_user_connections" reached

Can anyone think of a way to fix this?

Raisus
  • 148
  • 3
  • 23
  • More code is needed. If this is one of your jobs files, and your are opening the connection in your a scheduled Rufus block, but closing it at the end of the file, that is going to open a new connection every time your job runs, but will never close it. – Jeff Price Sep 17 '14 at 16:25
  • Use an ORM like [Sequel](http://sequel.jeremyevans.net) or [Active Record](http://guides.rubyonrails.org/active_record_querying.html) to talk to the DBM. They avoid the need to write DBM-specific code, allowing you to port quickly from one DBM to another. – the Tin Man Sep 17 '14 at 17:37
  • @JeffPrice Thanks for the feedback, it is a job file, the `db.close` line is not at the end-of-file, but I'll happily provide more code from the file. – Raisus Sep 18 '14 at 09:14
  • @theTinMan I'm trying to address the problem itself, that being that the files don't close the connection to the MySQL Database, rather than patching it over with a fix. – Raisus Sep 18 '14 at 10:04

1 Answers1

0

It is a best practice for DB/File/handle stuff to be in a begin/rescue/ensure block. It could be that something is happening and Rufus/Dashing is just being quiet about the error since they trap exceptions and go on their merry way. This would prevent your db connection from closing. The symptoms you are having could be from a similar problem, either way it's a good idea.

SCHEDULER.every '10s'do
  begin
    db = Mysql.new('host_name', 'database_name', 'password', 'table')

    # .... stuff ....
  rescue
    # what happens if an error happens? log it, toss it, ignore it?
  ensure
    db.close
  end

  # ... more stuff if you want ...
end
Jeff Price
  • 3,229
  • 22
  • 24
  • Okay... you put the begin, rescue, ensure inside the scheduler loop... Didn't try that. (I tried with it outside the loop, but to no avail, but I'll try your way and let you know) – Raisus Sep 18 '14 at 14:28
  • Tried it your way. It started off stable, but after a little while, I began seeing the same thing. Increasing amounts of active MySQL connections as shown by "SHOW PROCESSLIST;" – Raisus Sep 18 '14 at 14:42
  • Do you have other jobs/processes that might be connecting to the database? I am not a mysql admin, so I don't know what info you can get from "SHOW PROCESSLIST", but can you identify what applications/code is doing the connecting? It might be that you have another issue somewhere. – Jeff Price Sep 18 '14 at 15:32
  • Not that don't have the same code as the example as shown above. (EDIT: with the added begin, rescue, ensure you suggested) (EDIT2: MySQL doesn't help as it's all from the same username and database name) – Raisus Sep 18 '14 at 15:37
  • Is there any way you can get from mysql, who is opening the connections? turn up the logging info/level perhaps (again not a mysql admin). Maybe the gem you are using has some useful logging ability? Or I would just monkeypatch Mysql.new/close to log the open/close and try and figure out where/how connections are being opened. – Jeff Price Sep 18 '14 at 15:40
  • I'm not sure, but I think I've got it fixed by moving all code outside the scehduler, leaving just the MySQL queries and the Begin, Rescue and Ensure with the db.close inside. I've marked your answer as the accepted one as it did help. Thanks :D – Raisus Sep 19 '14 at 08:26