MySQL problem with many concurrent connections

Hi, here's a sixcore with 32 GB RAM. I've installed MySQL 5.1.47 (backport). Config is nearly standard, except max_connections, which is set to 2000. On the other hand there is PHP5.3/FastCGI on nginx. There is a very simple php application which should be served. NGINX can handle thousands of request parallel on this machine. This application accesses MySQL via mysqli. When using non-persistent connections in mysqli there is a problem when reaching 100 concurrent connections.

[error] 14074#0: *296 FastCGI sent in stderr: "PHP Warning: mysqli::mysqli(): [2002] Resource temporarily unavailable (trying to connect via unix:///tmp/mysqld.sock) in /var/www/libs/db.php on line 7

I've no idea to solve this. Connecting via tcp to mysql is terrible slow. The interesting thing is, when using persistent connections (add 'p:' to hostname in mysqli) the first 5000-10000 thousand requests fail with the same error as above until max connections (from webserver, set to 1500) is reached. After the first requests MySQL keeps it 1500 open connections and all is fine, so that I can make my 1500 concurrent requests. Huh? Is it possible, that this is a problem with PHP FastCGI?

From serverfault

• It's possible that, if you connect 10,000 times at once, 1500 of those 10,000 will be accepted and the remaining 8500 will be rejected (as in, will not be delayed until the persistent connection is available). Then, subsequent requests will use the 1500-large persistent pool.

  Is there a problem with opening the persistent connections once when you start up the server?

  Borealid : @cuti123: so why don't you set up the persistent connections before starting the application for real? The problem may be that your DB server is limiting the maximum rate of opening new connections.
  From Borealid

• I wrote, that the "resource temporarily unavailable" error occurs every time, already when using only some hundred non-persistent connections. Now, I've added

  usleep(rand(500000,1500000));
  

  before mysql connect call in my php script, so that this script sleeps for a random time (0.5-1.5 seconds) before connecting to mysql. And tadaaa, the problem doesn't occur with non-persistent connection. So could it possibly be, that MySQL has a problem with massive concurrent thread creation?

  Borealid : Or maybe it's that MySQL (sensibly) rate-limits your connections to protect itself? See http://mysqlha.blogspot.com/2007/08/rate-limiting-for-innodb.html

  : I don't think so, because the problem is already the connection to MySQL and not a query. At this point it doesn't have to do something with InnoDB (IMHO).
  From

• This sounds like maybe connections aren't being cleaned up in a timely manner. "show processlist" should show the connections if that's the case. Looking at the MySQL documentation, it appears this may be common with PHP unless certain PHP parameters are tweaked. You may want to look through the article and comments at http://dev.mysql.com/doc/refman/5.0/en/too-many-connections.html

  Another possibility brought up by the very slow TCP connections is perhaps MySQL (or tcpwrappers) is trying to do hostname lookup for access and the lookup is slow. This seems unlikely when using unix sockets, but in case it's trying to match localhost or system hostnames it may be worth looking into whether any access rules could be moved to IP or removed.

  From Jeremy M

• Thanks for your replies. For me it looks like it's a problem with concurrent thread creating by MySQL. Because when 1500 persistent connections are established no more error occurs and these connections can be used by 1500 scripts at one time, so I don't believe in a problem with parallel access to socket or a TCP problem.

  To sum up

  • ulimit=32768, max_connections=2000, php_fastcgi_children=1500
  • execution of 1500 concurrent requests with non-persistent connections always fails with this "Resource temporarily unavailable" (only a small portion of request is handled)
  • when adding random delay of 0.5-1.5 seconds before connect to database (with non-persistent connections) this problem completely disappears
  • when using persistent connections, this problem occurs all the time until all php fastcgi children have established their persistent connection to database (so when mysql processlist shows less than 1500 connections, this failure occurs still, but not so often)
  • when using persistent connections and all php processes have established a connection to db, the failure doesn't occur anymore
  From

• what OS is this running on top of? Windows has a terribly long time out on TCP connections. Something on the order of 90 seconds. You have to make some registry edits to bring back down to something shorter. Also helps to raise the port limit of 10k all to something higher.

  : I'm using Debian Lenny. The following additional settings were made: echo 131072 > /proc/sys/net/nf_conntrack_max echo 131072 > /proc/sys/net/netfilter/nf_conntrack_max echo 3 > /proc/sys/net/netfilter/nf_conntrack_tcp_timeout_time_wait echo 1 > /proc/sys/net/ipv4/tcp_tw_recycle echo 1 > /proc/sys/net/ipv4/tcp_tw_reuse But remember, all interprocess communication is made via sockets, so these settings doesn't affect this.
  From aduljr

• Have you also adjusted the max_user_connections parameter? It could be that nginx is treated as a single user, and is capped by that parameter.

  From blacklotus

Possible to IPSec VPN Tunnel Public IP Addresses?

A customer uses an IBM SAS product over the internet. Traffic flows from the IBM hosting data center to the customer network through Juniper VPN appliances. IBM says they're not tunneling private IP addresses. IBM says they're tunneling public IP addresses. Is this possible? What does this look like in the VPN configuration and in the packets? I'd like to know what the source/destination ip/ports would look like in the encrypted tunneled IPSec Payload and in the IP packet carrying the IPSec Payload.

IPSec Payload: source:1.1.1.101:1001 destination:2.2.2.101:2001 IP Packet: source:1.1.1.1:101 destination:2.2.2.1:201

Is it possible to send public IP addresses through an IPSec VPN tunnel? Is it possible for IBM to send a print job from a server on their network using the static-nat public address over a VPN to a printer at a customer network using the printer's static-nat public address? Or can a VPN not do this? Can a VPN only work with interesting traffic from and to private IP addresses?

From serverfault caleban

• Of course it's possible. Technically-speaking, there's nothing different about public IP addresses versus RFC1918 addresses other than that fact that RFC1918 addresses have been reserved for private use.

  In your IPsec config, you can match and tunnel public addresses just like you can RFC1918 addresses.

  From ErikA

• Yes, we tunnel from our corporate network to public IP range in the datacenter. This way, we can allow all traffic from the corp vlans to the datacenter, while restricting flow from the outside to our data centers.

  IBM may not allow tunneling to your private network, since the latter may conflict with their internal networks. For example, the printer you want to connect is in the 10.10.10.0/24, a corp vlan. If the SAS product is in some vlan, say 172.31.0.0/24, which is managed by IBM networking team, they are not going to tunnel the traffic from 172.31.0.0/24 to 10.10.10.0/24, even though such a IPSec tunnel is possible. If they create such a tunnel, they will have problems in the future to make use of their own 10.10.10.0/24 VLAN.

  The best option for you to is this:

  1. Create a tunnel between IBM and public IP range of your company.
  2. Create a NAT and/PAT between publicIP:port to printerIP:port
  From RainDoctor

• is it possible to NAT a private IP address(inside) to a public IP address (outside) and then send the public IP through IPSec tunnel?

  From karwan

How to setup nginx + thin + multiple domains + multiple virtual hosts?

I have a VPS. I create websites with Rails. I have several domains.

Now I want to install nginx + thin, and configure them to support multiple hosts, let each host have a seperated domain.

I have searched a lot, and now nginx and thin are installed and running, but I don't know how to combined them togetther.

Is there any articles or documents can help me? Thanks in advance.

From serverfault Freewind

• I got it.

  http://articles.slicehost.com/2009/4/17/centos-thin-web-server-for-ruby

  http://articles.slicehost.com/2009/4/17/centos-nginx-rails-and-thin

  http://cloudservers.rackspacecloud.com/index.php/CentOS_-_Adding_an_Nginx_init_script

  http://cloudservers.rackspacecloud.com/index.php/CentOS_-_Nginx_from_source_layout

  From Freewind

Apache redirect by without URL rewriting

Is there a way I could achieve redirecting to another site, without losing the original URL? So, what we'd want to achieve is to be able to access:
somesite.com/some/url -> someothersite.com/some/url
but we'd like to retain somesite.com/*.
In other words, we'd want to access every URL of someothersite.com via somesite.com.

From serverfault pugmarx

• If you're trying to redirect just a subdirectory of the server, you can use mod_rewrite like this:

  RewriteCond %{REQUEST_URI} /some/url [NC]
  RewriteRule ^(.*)$ other.example.com/$1 [R]
  

  If your trying to show the content of the other site without the user seeing the other url, you'll need both mod_rewrite and mod_proxy

  RewriteCond %{REQUEST_URI} /some/url [NC]
  RewriteRule ^(.*)$ http://other.example.com/$1 [P]
  

  Other configuration steps may be necessary for mod_proxy, depending on your environment.

  Apache's Advanced mod_rewrite Guide may be of benefit.

  From Chris S

• I'd just throw in a file that simply redirects the user with an HTML meta-refresh, or if you want to redirect the user in the most accepted way possible, throw in a 301 redirect rule in an .htaccess file somewhere on the site.

  Example:

  Redirect 301 /some/url http://newsite.com/some/url
  

  Chris S : A meta-refresh would affect the www.example.com page as well; and you'd have to add it to every single page in the whole site for it to work correctly. Adding a redirect in an .htaccess file wouldn't work if there's no default record for example.com.

  Christian Paredes : Hm, good point. I've probably read the question wrong: I see that he wants to redirect EVERY URL from somesite.com to someothersite.com; however, this could use some clarification.

  pugmarx : Added clarification as per your inputs. :)
  From Christian Paredes

• Are you actually looking for a redirect, or to have that other site's content appear under your original URL (more proxying)? I'll assume that the other server is your content and either internal/backend or if public you're aware of the negative SEO implications. If someothersite.com is not your site and content, you probably shouldn't be doing this.

  You'll need to have mod_proxy and mod_rewrite running, which is likely uncommenting the lines mentioning it if not already done.

  Then in the somesite.com config:
  RewriteEngine on
  RewriteRule /some/url(.*) http://someothersite.com/some/url$1 [P,L]

  From Jeremy M

Mysterious timeouts in MSSQL 2000

I have a query that has been working fine for ages (2+ years).

All of a sudden it went from taking 3 secs to 30+ to execute. The table indexes have minimal fragmentation, but rebuilding the indexes "fixes" it for about a day.

When I go to check the fragmentation it is around .5 (not bad). The table only sees about 100 inserts and another 100 updates a day.

There are no locks on the table when it happens. Any ideas of what to check?

From serverfault Christopher Kelly

• Check if your Statistics is Out-of-date and update it accordingly.

  gbn : index stats are updated as part of an index rebuild.
  From DaniSQL

• What does the execution plan look like when it is fine, vs when it isn't working correctly?

  Are you rebuilding the indexes or defragging the indexes? How big is the table? When was the last time you updated stats on the table?

  Christopher Kelly : table has a grand total of 4 integer columns and ~20000 rows

  Farseeker : +1 for execution plan

  mrdenny : How much data is changing on the table daily? It is really sounding like the stats are out of date. Can you post the execution plans?
  From mrdenny

• It's probably parameter sniffing (StackOverflow search).

  The index rebuild implicitly rebuilds index statistics which invalidates the cached plan which "fixes" it.

  From gbn

404 on new install of Glassfish

I am BRAND NEW to Glassfish, so I thought I would install a copy and poke around (I'm actually looking at a number of Java servers for personal edification). So, I installed a basic copy of Glassfish 3 on Ubuntu 10 and java-6-openjdk.

My first run through, I tried using sudo ./glassfish-3.0.1-unix.sh. Unfortunately, when I called ./asadmin start-domain and then navigated to the admin panel (at the default location), I found only this (there is more HTML on the page, but this is the only text. I can provide the entire HTML doc if needed.):

<div id="copyright" style="width: 720px; margin-left: auto; margin-right: auto; padding: 5px;"> 
        <span>Copyright © 2005, 2010, Oracle and/or its affiliates. All rights reserved.  U.S. Government Rights - Commercial software. Government users are subject to the Oracle standard license agreement and applicable provisions of the FAR and its supplements. Use is subject to license terms. This distribution may include materials developed by third parties. Portions may be derived from Berkeley BSD systems, licensed from U. of CA. Oracle, Java and GlassFish are registered trademarks of Oracle and/or its affiliates.</span> 
    </div> 

Also, the form with the ID "loginButton" is clearly missing from the HTML.

So, I uninstalled that version of Glassfish and installed using sudo ./glassfish-3.0.1-web-unix.sh. This had the same error. Finally, I tried installing it without using sudo, but I ran into the same error.

How do I get the stupid admin page working?

From serverfault Christopher W. Allen-Poole

• Have you tried it with the real version of java rather than openjdk?

  Christopher W. Allen-Poole : That worked immediately. Thank you.
  From JamesRyan

How do I edit Network Solution's DNS settings to allow no-www?

I have a domain registered through Network Solutions, foo.com. I have it setup to point to a Blogger account I have setup. I followed the instructions at:

• How do I use a custom domain name on my blog?

It loads http://www.foo.com fine.

But when I goto http://foo.com, it redirects to a subdomain that no longer exists (example: http://abc.foo.com). I think it's from an old DNS setting I had setup at 1&1 hosting a while ago. I no longer have access to my 1&1 account, so I can't view what DNS settings were in place.

How do I edit the DNS Settings at Network Solutions so that http://foo.com also points to where http://www.foo.com also points to?

From serverfault

• DNS and HTTP are completely separate protocols.

  HTTP handles redirects. So if the url is changing (redirect) it's in the HTTP Server configuration.

  DNS handles A & C-Name records; and the default record for foo.com must exist as it redirect. Sounds like DNS is setup correctly (probably).

  From Chris S

• I ended up adding a CNAME record. So whenever http://abc.foo.com occurs, it redirects back to http://www.foo.com.

  Host Aliases (CNAME Records)
  Alias a domain name for another domain.
  
  Alias                 TTL      Refers to Host Name
  
  abc.foo.com         7200     www.foo.com 
  

  This seems to have fixed my problem for me.

  Chris S : This is **not a redirect**. Unless you have code in your HTTP configuration that redirects all non www prefixed traffic, it'll just be another way to access you're site. Users will see abc.foo.com; not www.foo.com.

  : I don't know anything about code in an HTTP configuration since this the site is hosted by Blogger (blogspot.com). Also, I'm not sure I'm using the term 'redirect' correctly. Previous to this CNAME addition, http://foo.com loaded the url http://abc.foo.com, which doesn't exist, a Google error page occurs. Now typing in http://foo.com brings up http://www.foo.com. I'd rather it be http://foo.com, but I think Blogger requires www.
  From