Re: Example of Cluster SLOWER than InnoDB -- help?

[John Muehlhausen]

Harrison Fisk wrote:
> I would suggest trying the same thing with 5+
> threads all doing that in order to get a more
> realistic result of scalability and performance.

I am still in the world of a single G4 processor running the queue clients and the database, but here are my results for 5 senders and 5 receivers (10 total threads), total system throughput:

NDB: 116 messages per second
InnoDB: 151 m/s
InnoDB, innodb_flush_log_at_trx_commit=2: 208 m/s

With Cluster I saw 40% CPU usage by mysqld and 30% CPU usage by ndbd.
With InnoDB I saw 50-60% CPU usage by mysqld.

In the near term I'd like to get this up into the 5-10,000 per second range for 100 or so queues on an N-node cluster, ideally with the ability to go to 50,000+ (500-1000 queues) per second someday. I'm afraid, though, that the NDB nodes will spend most of their time coordinating access to the records that store last put and get sequence numbers as well as to the message records.

Here are the current schema and transactions:

CREATE TABLE `_get_Test0` (
`get_seqnum` bigint(20) unsigned NOT NULL default '0'
)
CREATE TABLE `_put_Test0` (
`put_seqnum` bigint(20) unsigned NOT NULL default '0'
)
CREATE TABLE `_messages_Test0` (
`seqnum` bigint(20) unsigned NOT NULL default '0',
`message_a` varchar(255) NOT NULL default '',
`message_b` varchar(255) NOT NULL default '',
`message_c` varchar(255) NOT NULL default '',
`message_d` varchar(255) NOT NULL default '',
`message_e` varchar(255) NOT NULL default '',
`message_f` varchar(255) NOT NULL default '',
`message_g` varchar(255) NOT NULL default '',
PRIMARY KEY (`seqnum`)
)

PUT method:
start transaction
// following two run in one mysql_query() command
update _queues._put_Test0 set put_seqnum=put_seqnum+1 where @seqnum := put_seqnum+1;
replace into _queues._messages_Test0 (seqnum, message_a, message_b, message_c, message_d, message_e, message_f, message_g) values (@seqnum, "019Hello, world! (980)", "", "", "", "", "", "")
commit

GET method:
select p.put_seqnum > g.get_seqnum from _queues._put_Test4 p, _queues._get_Test4 g
// if false then wait for notification from any sender, with timeout
select get_seqnum from _queues._get_Test4
select seqnum, message_a, message_b, message_c, message_d, message_e, message_f, message_g from _queues._messages_Test4 where seqnum > 1943 order by seqnum
for each message in result set
{
start transaction
// following two run in one mysql_query() command
update _queues._get_Test4 set get_seqnum=1944;
delete from _queues._messages_Test4 where seqnum = 1944
commit
}

There was no benefit to using "insert into" instead of "replace into" in the PUT.
Not deleting the messages record in the GET did not speed things up.