?

Log in

No account? Create an account

GUID VS Autoincrement, Take Two

« previous entry | next entry »
Mar. 26th, 2007 | 11:37 am

One of the projects I am looking at right now is performance on eight
proc (2 proc/4core) machines that Intel is providing.

An interesting situation I've noticed is the behavior in Innodb in
regards to Innodb and primary key INSERTS. From observing behavior
with GUID vs Autoincrement, Innodb runs into issues around ~75
concurrent acting connections (aka not idle). GUID size aside, using
a different key then an Autoincrement seems to help with scaling
Innodb on multiple CPU's (looking at this with a 4proc machine shows
that this issue show up at a later scaling point).

None of the other MySQL/3rd party engines behaved this way in
testing, so it looks to be a localized problem in Innodb. Blackhole
just gives you a base cost for an INSERT statement with no disk IO.

The test used 100K of rows and inserted them by splitting them across
the number of total connection (which gives you an estimate of
behavior by throwing a bigger machine/connections at a current problem).
Picture 1.png

Link | Leave a comment | Share

Comments {2}

peter_zaitsev

(no subject)

from: peter_zaitsev
date: Mar. 28th, 2007 09:55 am (UTC)
Link

Thanks Brian,

Innodb uses pretty much table level locks for auto_increment so no surprises here. Heikki however mentioned me there are some patches being done to solve this in MySQL 5.1

The other thing I should note it is not really about GUID at all it is about problems with auto_increment. If you can generate integer values on the application size you should have same results.

Reply | Thread

Brian "Krow" Aker

(no subject)

from: krow
date: Mar. 28th, 2007 04:38 pm (UTC)
Link

I have seen him mention that he was going to fix the Autoincrement issue, but its not happened yet.

Right, its not about having a unique ID field not generated by the database.

Reply | Parent | Thread