Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I don't see that you've actually mentioned anything specific that suggests that this has anything to do with MySQL... such as adding some benchmarking to your application code to time the specific database-related actions and see precisely what action is taking so long. Also, ab seems to provide some distinctly invalid results in some cases: stackoverflow.com/questions/20480182 ... you should seriously stop tweaking MySQL until you understand what, specifically, if anything, is the actual bottleneck. –  Michael - sqlbot Dec 29 '13 at 0:57
    
i've been digging further. I played with the resolv.conf 'options timeout:3' and then with ab i get instead of the 5 seconds, i get 3 seconds. I also recreated the complete database on another machine and it runs perfect. I just don't understand what has happened to this box. Adding that no-resolv option to mysql didn't help, so it could be outside of mysql too. (and yes i also tried other nameservers) –  Erik-Jan Riemers Dec 29 '13 at 1:05
    
To clarify, your application is resolving the database server's IP via DNS? (If yes, then...) Does adding the database server's hostname in the app server's /etc/hosts file clear up the problem? –  Michael - sqlbot Dec 29 '13 at 4:39
    
I tried adding in all the names to /etc/hosts on all the boxes involved. No change. When i use AB directly from app/webserver 1 -> DB it works (installed apache on the db) but when i say on the app/webserver run AB with the host of the machine i am running on, it will go through that webserver and then it fails. As if its a apache issue, but yet i can change the response time by changing the resolv.conf on the database. –  Erik-Jan Riemers Dec 29 '13 at 10:18
    
httpd.apache.org/docs/2.2/mod/core.html#hostnamelookups HostnameLookups isn't on, in apache, is it? Or are you doing anything else described in that section that would trigger an unexpected dns query? Also, if you stop/start apache, are there any warnings? If using a DNS server that you control, there should be a way to get it to log every query, so you can see just what's being looked up. Google "rndc querylog" if the DNS server is running BIND. –  Michael - sqlbot Dec 29 '13 at 15:49

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.