Rails Too Many Connections Mysql Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about too many connections mysql fix hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges mysql too many connections solution Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each
Mysql Error Too Many Connections Fix
other. Join them; it only takes a minute: Sign up mySQL “Too many connections” error influenced by number of mongrel instances? up vote 1 down vote favorite 1 Recently I have started getting mySQL "too many connection" errors at
Error 1040 (00000): Too Many Connections
times of high traffic. My rails app runs on a mongrel cluster with 2 instances on a shared host. Some recent changes that might be driving it: Traffic to my site has increased. I am now averaging about 4K pages a day. Database size has increased. My largest table has ~ 100K rows. Some associations could return several hundred instances in the worst case, though most are far less. I have added some features that increased the number mysql too many connections flush and size of database calls in some actions. I have done a code review to reduce database calls, optimize SQL queries, add missing indexes, and use :include for eager loading. However, many of my methods still make 5-10 separate SQL calls. Most of my actions have a response time of around 100ms, but one of my most common actions averages 300-400ms, and some actions randomly peak at over 1000ms. The logs are of little help, as the errors seem to occur randomly, or at least the pattern does not appear related to the actions being called or data being accessed. Could I alleviate the error by adding additional mongrel instances? Or are the mySQL connections limited by the server, and thus unrelated to the number of processes I divide my traffic across? Is this most likely a problem with my coding, or should I be pressing my host for more capacity/less load on the shared server? mysql ruby-on-rails mongrel-cluster share|improve this question asked Mar 15 '11 at 20:27 Ed Haywood 20519 add a comment| 3 Answers 3 active oldest votes up vote 1 down vote accepted Are you caching anything? It's an important part of alleviating application and database load. The Rails Guides have a section on caching. share|improve this answer answered Mar 15 '11 at 20:36 Andrew Marshall 63.9k12134153 I am not caching now, but I have l
Connectors More MySQL.com Downloads Developer Zone Section Menu: Documentation Home MySQL 5.7 Reference Manual Preface and Legal Notices General Information Installing and Upgrading MySQL
Mysql Too Many Connections Reset
Using MySQL as a Document Store Tutorial MySQL Programs MySQL Server too many connections mysql java Administration Security Backup and Recovery Optimization Language Structure Globalization Data Types Functions and Operators SQL Statement Syntax mariadb too many connections The InnoDB Storage Engine Alternative Storage Engines High Availability and Scalability Replication MySQL Cluster NDB 7.5 Partitioning Stored Programs and Views INFORMATION_SCHEMA Tables MySQL Performance Schema MySQL http://stackoverflow.com/questions/5317646/mysql-too-many-connections-error-influenced-by-number-of-mongrel-instances sys Schema Connectors and APIs Extending MySQL MySQL Enterprise Edition MySQL Workbench MySQL 5.7 Frequently Asked Questions Errors, Error Codes, and Common Problems Sources of Error Information Types of Error Values Server Error Codes and Messages Client Error Codes and Messages Problems and Common Errors How to Determine What Is Causing a Problem Common Errors When http://dev.mysql.com/doc/en/too-many-connections.html Using MySQL Programs Access denied Can't connect to [local] MySQL server Lost connection to MySQL server Client does not support authentication protocol Password Fails When Entered Interactively Host 'host_name' is blocked Too many connections Out of memory MySQL server has gone away Packet Too Large Communication Errors and Aborted Connections The table is full Can't create/write to file Commands out of sync Ignoring user Table 'tbl_name' doesn't exist Can't initialize character set File Not Found and Similar Errors Table-Corruption Issues Administration-Related Issues Problems with File Permissions How to Reset the Root Password What to Do If MySQL Keeps Crashing How MySQL Handles a Full Disk Where MySQL Stores Temporary Files How to Protect or Change the MySQL Unix Socket File Time Zone Problems Query-Related Issues Case Sensitivity in String Searches Problems Using DATE Columns Problems with NULL Values Problems with Column Aliases Rollback Failure for Nontransactional Tables Deleting Rows from Related Tables Solving Problems with No Matching Rows Problems with Floating-Point Values Optimizer-Related Issue
Sign in Pricing Blog Support Search GitHub This repository Watch 77 https://github.com/brianmario/mysql2/issues/606 Star 1,523 Fork 343 brianmario/mysql2 Code Issues 33 Pull requests 10 Projects 0 Pulse Graphs New issue mysql2 gem improperly closing MySQL connections #606 http://vigram-software-generals.blogspot.com/2014/03/ruby-threads-activerecord-too-many.html Closed dtest opened this Issue Apr 2, 2015 · 26 comments Labels None yet Milestone No milestone Assignees No one assigned too many 8 participants dtest commented Apr 2, 2015 The mysql2 gem doesn't properly close the connection, even when explicitly calling the 'client.close' method, resulting in increase of 'Aborted_clients' counter indicating an improper closure of the connection. My setup is MySQL 5.6.23 installed from MySQL's ubuntu repo on an too many connections Ubuntu Trusty EC2 instance, mysql2 gem v0.3.18, ruby 2.0.0p353 (embedded sensu version), libmysqlclient18 dtest@ip-10-1-1-73:~# /opt/sensu/embedded/bin/ruby --version ruby 2.0.0p353 (2013-11-22 revision 43784) [x86_64-linux] dtest@ip-10-1-1-73:~# /opt/sensu/embedded/bin/gem list | grep mysql mysql (2.9.1) mysql2 (0.3.18) dtest@ip-10-1-1-73:~# ldd /opt/sensu/embedded/lib/ruby/gems/2.0.0/gems/mysql2-0.3.18/lib/mysql2/mysql2.so linux-vdso.so.1 => (0x00007fffa07fe000) libruby.so.2.0 => /opt/sensu/embedded/lib/libruby.so.2.0 (0x00007f8bde4ec000) libmysqlclient.so.18 => /usr/lib/x86_64-linux-gnu/libmysqlclient.so.18 (0x00007f8bddf35000) libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f8bddd10000) libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f8bdd94b000) librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f8bdd743000) libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f8bdd53e000) libcrypt.so.1 => /lib/x86_64-linux-gnu/libcrypt.so.1 (0x00007f8bdd305000) libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f8bdcfff000) /lib64/ld-linux-x86-64.so.2 (0x00007f8bdeba9000) libz.so.1 => /opt/sensu/embedded/lib/libz.so.1 (0x00007f8bdcde1000) libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f8bdcadd000) libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f8bdc8c7000) dtest@ip-10-1-1-73:~# mysql --version mysql Ver 14.14 Distrib 5.6.23, for Linux (x86_64) using EditLine wrapper I have the following example script: #!/usr/bin/ruby require 'rubygems' if RUBY_VERSION < '1.9.0' require 'mysql2' puts Time.now.strftime("%d/%m/%Y %H:%M:%S")+"-Before connect"; # socket client = Mysql2::Client.new(:host => 'localhost', :username => 'ruby', :password => 'test', :socket => "/var/lib/mysql/mysql.sock"); # tcp #client = Mysql2::Client.new(:host => '127.0.0.1', :userna
I could see 150+ active DB connections established from Rails App. How could my Rails App can open 150+ connections, though I mentioned AR connection pool size as 30. Baffled me !!! Following is my development environment, Ruby(2.0) + Rails(4.0) + Unicorn + Nginx+SideKiq+ MultiTenant No.of Unicorn workers: 5 No.of SideKiq processes: 1 AR Connection pool size: 30 According to experts, it would establish 5 * 1+ 30 = 35 maximum connections. Then why is it making 150+ connections in my App? Started Researching !!! Found a magical sentence saying that the above calculation is true only for single threaded applications. Oh Yes! In my Rails app, I am spawning application level Threads based on no.of records being processed. So each Thread will try to make a connection to DB. Look at the following example, class Article < ActiveRecord::Base def self.publish_all threads = [] Article.by_unpublished.find_in_batches(batch_size: BATCH_CONST) do |articles| threads << Thread.new do ActiveRecord::Base.connection_pool.with_connection do articles.each do |article| article.publish end end end end threads.map(&:join) end end According to Rails Doc 'with_connection' method will Check-In the connection back to connection pool once after executing the Block given. But in the above example, it's not releasing the connection rather it holds as active connection. So further requests to server keep creating new connections, this strange behaviour is the root cause of connection leak issue. Solution !!! Yes you are right, Check-In the connection explicitly. So I created a utility class and wrapped around Thread invocation. For an example, class ThreadUtility def self.with_connection(&block) begin yield block rescue Exception => e raise e ensure # Check the connection back in to the connection pool ActiveRecord::Base.connection.close if ActiveRecord::Base.connection end end end class Article < ActiveRecord::Base def self.publish_all threads = [] Article.by_unpublished.find_in_batches(batch_size: BATCH_CONST) do |articles| threads << Thread.new do ThreadUtility.with_connection do articles.each do |article| article.publish end end end end threads.map(&:join) end end Above example completely solved my connection leak issue, No more "Too Many Connections" notice from server. Another strange behaviour which I would like to share here from above example is, whenever server spawning new thread, it creates new connection rather than reusing existing connection from po