Hello, Kevin, Adam, et al.

The data being returned from the SNMP servers are key->value pairs, but each individual server that is polled will be giving back the same keys, so you run into the same duplicate primary key issue if you try to base your primary key off 'description' as in my sample database spec.

My question is more along the lines of how data will be allocated between the various NDB nodes during the course of the entire contents of the table getting deleted and recreated every polling cycle.

For example, using the original sample table I mentioned:

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

There is of course an 'invisible' BIGINT NOT NULL AUTO_INCREMENT primary key hiding in there, which gets incremented each insert.

When the poller for colo '1' wakes up, polls the various servers in it's polling jurisdiction it does the following: DELETE FROM discoverables WHERE colo='1'; INSERT INTO discoverables VALUES ( ('1','1','foo','bar'), ('1','1','baz','gonk'), ('1','2','foo','morestuff'), ('1','2','baz','yetmorestuff'), (...this continues for potentially several thousand records...) );

Then the poller for colo '2' wakes up, and does the same thing to it's subset of the data: DELETE FROM discoverables WHERE colo='2'; INSERT INTO discoverables VALUES ( ('2','1','foo','cheeseburger'), ('2','1','baz','twinkie'), (...et cetera...) );

And this happens on for as many colos as there are. Every 5 minutes or so.

How does all this deleting and inserting affect the state of the cluster? The MySQL 5.1 Reference Manual states in section 16.2.4 that:

Note: Also beginning with MySQL 5.1.6, tables using the NDB Cluster storage engine are implicitly partitioned by KEY, again using the table's primary key as the partitioning key. In the event that the Cluster table has no explicit primary key, the “hidden” primary key generated by the NDB storage engine for each Cluster table is used as the partitioning key.

( http://dev.mysql.com/doc/refman/5.1/en/partitioning-key.html )

Does this mean that as old, lower-ordered keys are deleted and new, higher-ordered keys are added to the database, the fields are actively being shifted amongst the various NDB nodes?

Kevin Burton wrote: