Server Testing Stumper
I blogged several times about some testing I have been doing recently. Well, I have run across something that just doesn’t make sense. I checked kernels and I/O schedulers and worked out what seems to do the best (2.6.22 kernel with deadline for those who are curious — I will post results soon). I had done all this with two servers that were identical and had 8 gb of RAM each. I then moved the 8 gb of RAM from one server to the other so one server had 16 gb of RAM. I then re-ran the last test with the only change being I raised the innodb buffer variable from four gb to eight gb.
I would have expected if there was any change it would be faster. I ran querybench five times and averaged the results as I had done with previous test. It was almost exactly 1500 qps slower than the previous run.
Anyone have any thoughts? I have run the test several times. I am rerunning the tests while closely examining the output of some of normal profile tools — top and iostat. It appears to me that the runs are io-bound if anything. The iowait percentage runs between the 25% - 40+ %. Top shows memory usage isn’t excessive (30% or 4.8 GB) and CPU percentage never tops 100% (so it doesn’t even max out one core of eight total cores).
My hunch is that the server is trying to fill up the buffer and IO is just bogging down the system. If I had a larger input file (I have 135 megs total of queries I am feeding into querybench) it might level out after “warming up” more.
My current formulae:
- I split the input queries into two files to warm the server up with the first set of queries
- I time both runs and then record the results of the second part (which is always higher than the warm up run)
Results show that . However I could be really wrong 
Feedback would be very appreciated!!
2 Comments so far
Leave a reply
I’d bet Innodb’s trying to be smart:
http://dev.mysql.com/doc/refman/5.1/en/innodb-adaptive-hash.html
it’s tailoring the adaptive indexes to the more memory available, perhaps?
I wrote about a particular bug in the adaptive query hashing here:
http://www.pythian.com/blogs/732/innodbs-adaptive-hash
— most importantly for starters: “You can see this bug implemented if you have many transactions that “holds adaptive hash latch” — see the “Transactions” section of the innodb_lock_monitor or in SHOW ENGINE INNODB STATUS”
What do the query cache statistics look like for both servers?
Does SHOW STATUS show that more queries are using full table scans than before?
Sheeri,
Thank for the input. I will check into it, but its going to be a couple of weeks. Wednesday morning I am going on a short vacation trip to Seattle for almost a week and then two days after I get back it is off to San Jose to meetings and then the users conference. A couple of people I know are speaking this year so I thought I would check it out
Once I get back from these trips then I will have time to look into it. Fortunately I have a bit of time before these servers have to go into production.