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}

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