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: 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