Re: Will I have a runaway primary key problem in the future if I do this? - Jayson Larose - com.mysql.lists.cluster

15 messages in com.mysql.lists.clusterRe: Will I have a runaway primary key...

From	Sent On	Attachments
Jayson Larose	04 Dec 2006 08:41
mizi...@aim.com	04 Dec 2006 09:20
Jayson Larose	04 Dec 2006 11:03
Kevin Burton	04 Dec 2006 13:28
Adam Dixon	04 Dec 2006 14:55
Kevin Burton	04 Dec 2006 15:23
Jayson Larose	04 Dec 2006 16:21
Anatoly Pidruchny	04 Dec 2006 17:23
Kevin Burton	04 Dec 2006 19:04
Jayson Larose	04 Dec 2006 20:51
Anatoly Pidruchny	04 Dec 2006 21:09
mizi...@aim.com	05 Dec 2006 08:56
mizi...@netscape.net	05 Dec 2006 09:10
Stewart Smith	12 Dec 2006 19:44
Stewart Smith	12 Dec 2006 19:46

Subject:	Re: Will I have a runaway primary key problem in the future if I do this?
From:	Jayson Larose (jay...@gni.com)
Date:	12/04/2006 11:03:48 AM
List:	com.mysql.lists.cluster

Hi, Michael.

The problem with this being, at least with MyISAM tables, which is what I'm familiar with, you cannot have a duplicate entry in your primary key. Thus, if I had a timestamp-based primary key were to try to insert 100 or so rows in one second, which is entirely plausible with this setup, I will get 1 insert and 99 errors. I'm reasonably sure this will be the case with NDB tables as well.

mizi...@aim.com wrote:

Hi,

have you considered, if it's possible in your setup, to add a primary key to your table? If you know it's not updated extremely often, a datetime based solution could work such as a column where you'd insert now() or UNIX time, to avoid the autoincrement. With all due respect to mysql cluster I think the less you trust to the outside world to provide the better so it wouldn't hurt to have a primary key you can actually control.

Thanks, Michael Izioumtchenko

-----Original Message----- From: jay...@gni.com To: clus...@lists.mysql.com Sent: Mon, 4 Dec 2006 11:42 AM Subject: Will I have a runaway primary key problem in the future if I do this?

Hello,

I plan an eventual move to MySQL cluster to increase the overall speed and reliability of my company's various database needs.

One of my databases contains a key/value set which gets updated every 5 or so minutes, when some SNMP servers are polled.

The table structure as current looks sort of like:

CREATE TABLE discoverables ( colo INT UNSIGNED NOT NULL, server INT UNSIGNED NOT NULL, description VARCHAR(255), value VARCHAR(255), KEY (colo), KEY (server) );

So, every time one of the pollers does its thing, it can simply do something like "DELETE FROM discoverables WHERE colo='1' AND server='45'", then repopulate the data for that server.

Since the NDB engine assigns a primary key to every table, and it is my understanding that at least with MYISAM tables, the primary key autoincrements with no regard for if there are empty index numbers below it, numericaly... the following two questions are brought up in my mind:

1) Am I going to have a table that hits its INT (or BIGINT, or whatever) max sometime in the future without periodic table rebuilding, even if there are only 10,000 or so rows in the table due high INSERT/DELETE volume and autoincrement?

2) I read that MySQL cluster performs partitioning by default on the primary key. Would there be any performance hit due to this "wandering primary key", like having all your data winding up on one NDB node?