Mysql database slow responding to connects when having only few concurrent connections

Question

We have a database setup with in master/slave. It runs innodb and its on a 8 core with 32gb.

When i run "ab" to do a load test on a website (4 frontend webservers) which does the query's with 10 concurrent users on the database it will handle the first batch 'ok' after which the average response time will go up.

    Percentage of the requests served within a certain time (ms)
    50%     20
    66%     26
    75%     31
    80%     34
    90%     46
    95%   5018
    98%   5025
    99%   5033
    100%   5041 (longest request)

As you can see, the most are going up to 5 seconds each while the first batch was below 50 ms (which would be ok) i thought it might have something to do with those 5 seconds which stands out. I added skip resolving, but also did not help.

The example query i am using is:

  SELECT imageURL, linkURL, imageWidth, imageHeight, androidLinkURL
  FROM stuff
  WHERE clued = 1 AND status = 'a'
  ORDER BY RAND()
  LIMIT 1;

The load of the machine itself sits pretty much idle (0.3) and when i connect to the server and issue a 'show processlist' i would expect to see at least a couple of these query's coming by. I am only using the master server to talk too, same issue is also on the slave though.

Before this started i thought it was max open files, so i increased that but with only 10 connections its not. Then i decreased the time_waits for connection because that may run out, again 10 connections should not pose a problem. I also had massive IO caused by the innodb logging which i turned to 1.

 innodb_flush_log_at_trx_commit 1 

I also saw my connections coming in from both ipv4 and ipv6, so i binded the mysql to ipv4 only. I checked dmesg to see if some syn attack might be causing the problem and tweaked that too but again only 10 concurrent should not be a problem.

I also saw now and then some writes to tmp file, even though it was not in this particular test query, so i also changed that to a ramfs filesystem but also did not help.

Basicly when this happens, connecting to the server grinds to a halt and starts stacking up. Above query is just a example, other query's are cached on php with memcached.

You can find my variables here: http://pastebin.com/YLASDSa5 (disregard the "logging" options that are turned on, those are normally off)

UPDATE: I believe its is no longer a specific database issue. I have installed apache locally on the box of the database server and i see no issues at all. Doing a load test from another box also works fine to the apcache on the box, is also fine.

Here is the weird part, if i do a load test with 10 concurrent users and set my resolv.conf timeout to 1 second it will stall with 1 second as shown in the load test example. If i do 100 concurrent connenctions i get this:

 50%   1084
 66%   1099
 75%   1121
 80%   1156
 90%   2146
 95%   2177
 98%  10002
 99%  10002
100%  10005 (longest request)

As if it has increments of 1 second, then to 2, then in the end to 10. Its something to do with the dns, but i am clueless to what.

Feel free to move the question to the appropriate exchange since it no longer fits Stack Exchange.

Answer 1

The problem was that we have a haproxy in front of the setup. Each call to a webserver was creating a dns query on each of the webservers because the virtualhost was a "servername" with no ip supplied. Adding a local dns bind caching daemon solved this issue although its not the best sollution. (also didn't show up in the beginning since it was udp traffic)

So in the end this was not database related.