• PHP
  • Ruby on Rails
  • MySQL
  • Linux
    • SELINUX
    • Fedora
    • debian
  • Apache
  • nginx
  • AJAX
Albertech.net

Tag Archives: Too Many Connections

MySQL: Avoiding the Mysql:Too many connections error

July 17, 2009 2:37 pm / Albertech.net

 If your site experiences the infamous “Mysql : Too many connections” error, you will need to tune your MySQL server to handle the load of a heavily used website. 

 There are some configuration variables that you can adjust to make your site handle more connections.

  • An easy way to identify which variables need to be changed can be done through phpMyAdmin. Login to your phpMyAdmin control panel and search for the “Show MySQL runtime information” module. Scroll down the page and look for entries that are in red. These are suggested areas in your MySQL configuration that need to be looked at.

  • The wait_timeout default in MySQL is 28840 seconds (8 hours), which can seriously limit the number of open connections you have on your page. I adjusted mine to 60 seconds. This frees up the thread after idle timeout, which allows for more connections to connect. The 8 hour timeframe is fine if you only have a few connections to the server. For popular websites, its best to make this value as low as possible. Set this variable to the maximum time your page typically runs. For instance, if you have a page that needs to retrieve some data processed from another server (60-80 seconds) and you only use one mysql_connect, you may lose the mysql connection for processing the remaining parts of the page if your wait_timeout is too low. If the processing takes much longer, a workaround is to use multiple mysql_connects to prevent an idle connection from disconnecting.

  • DNS issues. If your DNS suddenly stops working, this will consume all your MySQL connections. MySQL tries to resolve the IP address for every connection it gets, so this will start creating many more threads than released if it DNS is down. In Linux, edit the /etc/hosts file and manually add in your web server IPs/names to this (if you have static IPs) This will speed up performance by resolving IPs locally as opposed to through DNS. You can identify DNS issues or connection attempts through “Failed attempts” section in phpMyAdmin runtime page. A significant number of failed attempts can mean either someone is trying to break into your MySQL box or your DNS isn’t working right.

  • Increase the key_buffer value (for MyISAM tables) This helps handle indexes for temporary tables. If you have lots of key misses, its important to increase the size of the key_buffer. You can do this by comparing the key_reads vs. key_read_requests value.

  • Increase the innodb_buffer_pool_size (for innodb tables)

  • Increase the table_cache value. If you look at your phpMyAdmin runtime “Opened_tables” field and see a large number (over 1,000) you will need to increase your table_cache. This will keep tables in memory to speed up peformance. Default is only 64. I’ve upped mine to over 2,000 since I have many databases and tables running on my server.

  • For more information, check out these resources:
    http://www.ibm.com/developerworks/linux/library/l-tune-lamp-3.html 
    http://dev.mysql.com/tech-resources/presentations/presentation-oscon2000-20000719/index.html

 

Share this:

  • Facebook
  • Google
  • Twitter
  • Print
  • Email
Posted in: MySQL / Tagged: error, MySQL, too many connections, wait_timeout

Categories

  • AJAX
  • Android
  • Apache
  • Canon Cameras
  • Cloud
  • CMS
  • Computer Mods
  • Conferences
  • Deals
  • debian
  • Fedora
  • Flash
  • Frameworks
  • git
  • Hardware
  • HTML
  • IDE
  • iPhone
  • iPhone App Review
  • jQuery
  • Linux
  • Mac OS X
  • MySQL
  • nginx
  • PHP
  • portfolio
  • Puppet
  • Ruby on Rails
  • Script Reviews
  • SELINUX
  • Software
  • Software Review
  • SQL Server
  • statistics
  • Tech
  • Tomcat
  • Uncategorized
  • VMWARE
  • VPS
  • Windows
  • wordpress
  • Zend Framework

Blogroll

  • DragonAl Flickr
  • Dropbox – Free 2GB Account
  • James' Blog
  • Javascript Compressor
  • PHP Builder Community
  • PHP-Princess.net
  • Rubular – Regular Expression Validator
  • The Scale-Out Blog
  • Tiny MCE

Tags

activation AJAX android antec Apache AWS awstats canon coda codeigniter debian enclosure external free G1 install vmware tools Internet Explorer iphone 5 jquery Linux mx-1 MySQL office 2007 OSX photoshop PHP plugin plugins portfolio redesigned website review rewrite script security SELinux ssh tinymce tutorial upgrade VMWARE vmware server wordpress wordpress mu XSS zend framework
© Copyright 2013 Albertech.net
Infinity Theme by DesignCoral / WordPress
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.