Log in

No account? Create an account

Adaptive Hash, Solaris vs Intel, and other comparisons....

« previous entry | next entry »
Aug. 31st, 2007 | 09:46 am

As of late I am really fed up with anecdotal information on MySQL.
One of the one's I hear is "with many processors you need to disable
Innodb's Adaptive Hash".

First things first, unless you roll up your sleeves and hack the code
there is no way to turn it off. There is no user configurable
variable, all there is is a single true/false boolean you can set in
the code. As many times as I have heard "turn it off" I had thought I
would discover that there was some setting I never knew about. It
turns out that this is not the case.

What you see below is a with/without adaptive test. It was key read
test, meaning that it just tested key reads and no writes. Users who
are using 8way and beyond machines today (and the Intel was an 8 way,
while the Sun T1000 can be considered a 32way), are not guys with
machine in their basement. Typically it is web, and typically the
machines are being used in read replication environments. Not all web
environments are super heavy key reads, but a lot are.

Command used:

$MYSQLSLAP --concurrency=$CONCURRENCY --iterations=10 --number-int-
cols=2 --number-char-cols=3 --auto-generate-sql --csv=/tmp
/$ENGINE-key-scale.csv --engine=$ENGINE --auto-generate-sql-add-
autoincrement --auto-generate-sql-load-type=key --auto-genera

For how I configure my my.cnf refer to this post (and I use the
5.2/6.0 codebase currently in tests):

The surprises for me was that the Adaptive Hash in Innodb made no
difference on the Intel platform. I ran the data twice, and in the
second run put printf()'s in the code just in case I had somehow
missed turning off the Adaptive Hash. I am still curious as to why it
made no difference.

On the T1000 running Solaris 10, it made an obvious difference.

The Apples to Orange comparison between Intel and Sparc is
interesting. The Sparc has an obvious advantage at lower number of
users (makes sense, it has a lot more "cores" then the Intel). The
Intel chips are faster though and as users increase it handles the
load much better. There could be a number of reasons for this, and I
have a couple of ideas why (a puzzle!).

Next week I am going to merge a patch into the 5.2/6.0 codebase that
allows multiplexing of connections over threads, which will reduce
the number of threads. This should have a dramatic effect on the
performance of the T1000 (and I am hoping in October to put together
a similar patch for Linux).

Adaptive Hash.jpg

Link | Leave a comment |

Comments {9}


(no subject)

from: shadowdaddy
date: Sep. 1st, 2007 12:21 am (UTC)

Hey! I saw something today about a MySQL conference/event of some sort happening in London soon. Are you coming over?

Reply | Thread

(no subject)

from: jamesd
date: Sep. 1st, 2007 03:04 pm (UTC)

User conference or training? If it's UC I'll probably be there but Brian wasn't around last year. The product management VP was around at last year's UC, a great opportunity to influence what gets put into the server.

Reply | Parent | Thread

London Customer Conference?

from: jamesd
date: Sep. 1st, 2007 04:05 pm (UTC)

Ah, must be the Customer Conference on 16 October. If you want MySQL to produce something, in the server or peripheral to it, go there, grab Robin Schumacher and explain why. It's part of why we now have SMTP support in NMAS. Also worth meeting David Axmark, one of MySQL's founders. Anders' session is a good one if you're interested in the variety of ways to do HA with MySQL.

Last year we had more attendees than space, a definite success.

Reply | Parent | Thread

(no subject)

from: jamesd
date: Sep. 1st, 2007 03:55 pm (UTC)

MyISAM's Concurrent_inserts=0 behavior is a good mental model for this. Since you're testing with no updates, you're not testing the case where it can be problematic to have the adaptive hash turned on. The lock taken to update the hash is the troublesome issue.

It's rare for the Support side to see people encountering this as a substantial bottleneck. I think it's likely to be a growing problem for the future, as total query rates systems can handle increase and the percentage of writes it takes for updating the hash index to be a significant factor falls.

It would be interesting to be able to turn this off, as for the query cache, just so it's possible to see if it's a net gain/loss for a specific situation more easily.

This isn't to say that your results aren't interesting. They are. But I think they are missing the point when it comes to why people think turning off the adaptive hash can be useful. Try testing to find the threshold of update percentage of total load at which having the adaptive hash turned on becomes problematic.

Reply | Thread

(no subject)

from: jamesd
date: Sep. 1st, 2007 03:56 pm (UTC)


Reply | Parent | Thread

Brian "Krow" Aker

(no subject)

from: krow
date: Sep. 1st, 2007 04:25 pm (UTC)

The next test will be with concurrent inserts, and the test after that will be with updates. I'm walking through all situations at the moment.

What the above though does tell me is that the advice of disabling this on read replication slaves is probably bad. Also that predicting whether or not it makes a difference in performance is not so easy.

Reply | Parent | Thread

(no subject)

from: jamesd
date: Sep. 1st, 2007 05:32 pm (UTC)

Agreed if the slaves are doing reads and not seeing updates. They'll just generate the hash index entries once and then it's just a matter of working set size and whether the hash index pushes out other things. Doubt it'll be a problem.

Reply | Parent | Thread


(no subject)

from: peter_zaitsev
date: Sep. 3rd, 2007 11:30 am (UTC)

What performance did you see with low amount of users ?

The problem I've seen with just disabling adaptive hash index is it makes performance so slower on the lower amount of threads.

Which can be too high price to pay for many applications.

Reply | Thread

(no subject)

from: jamesd
date: Sep. 18th, 2007 01:47 am (UTC)

bug #30738 contains interesting numbers for one particular class of query and the potential gain from improvements in the adaptive hash index.

Reply | Thread