jonas blog

Brewing in MySQL Cluster 7.2.x

2011-10-13T10:01:00.001+02:00

Admittedly MySQL Cluster have some way to go before monitoring becomes best in class. But, we are progressing!
In 7.1 we introduced NDBINFO, which is an infrastructure that enables presenting information from within the cluster in SQL format.
And here are a 4 new tables that are currently brewing

ndbinfo.transactions and ndbinfo.operations

mysql> select COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT from information_schema.columns where TABLE_NAME = 'ndb$transactions';
+----------------+-----------+---------------------------------+
| COLUMN_NAME    | DATA_TYPE | COLUMN_COMMENT                  |
+----------------+-----------+---------------------------------+
| node_id        | int       | node id                         |
| block_instance | int       | TC instance no                  |
| objid          | int       | Object id of transaction object |
| apiref         | int       | API reference                   |
| transid        | varchar   | Transaction id                  |
| state          | int       | Transaction state               |
| flags          | int       | Transaction flags               |
| c_ops          | int       | No of operations in transaction |
| outstanding    | int       | Currently outstanding request   |
| timer          | int       | Timer (seconds)                 |
+----------------+-----------+---------------------------------+
mysql> select COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT from information_schema.columns where TABLE_NAME = 'ndb$operations';
+----------------+-----------+-------------------------------+
| COLUMN_NAME    | DATA_TYPE | COLUMN_COMMENT                |
+----------------+-----------+-------------------------------+
| node_id        | int       | node id                       |
| block_instance | int       | LQH instance no               |
| objid          | int       | Object id of operation object |
| tcref          | int       | TC reference                  |
| apiref         | int       | API reference                 |
| transid        | varchar   | Transaction id                |
| tableid        | int       | Table id                      |
| fragmentid     | int       | Fragment id                   |
| op             | int       | Operation type                |
| state          | int       | Operation state               |
| flags          | int       | Operation flags               |
+----------------+-----------+-------------------------------+

these two tables show currently ongoing transactions resp. currently ongoing operations.
ndbinfo.transactions roughly corresponds to information_schema.INNODB_TRX
ndbinfo.operations roughly corresponds to information_schema.INNODB_LOCKS
the information provided is collected without any kind of locks
the information provided is collected by iterating internal data-structures. Hence output does not necessarily represent a state that has existed (i.e not a snapshot)

one missing piece of this puzzle is how to map a ndb transaction id, to a mysql connection id.
when (if?) this information is available, one could e.g join information_schema.processlist with ndbinfo.operations too see locks are being held by a certain connection.(suggestion on how to gather/expose this is welcome).

ndbinfo.threadblocks and ndbinfo.threadstat

mysql> select COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT from information_schema.columns where TABLE_NAME = 'ndb$threadblocks';
+----------------+-----------+----------------+
| COLUMN_NAME    | DATA_TYPE | COLUMN_COMMENT |
+----------------+-----------+----------------+
| node_id        | int       | node id        |
| thr_no         | int       | thread number  |
| block_number   | int       | block number   |
| block_instance | int       | block instance |
+----------------+-----------+----------------+

mysql> select COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT from information_schema.columns where TABLE_NAME = 'ndb$threadstat';
+----------------+-----------+------------------------------------------+
| COLUMN_NAME    | DATA_TYPE | COLUMN_COMMENT                           |
+----------------+-----------+------------------------------------------+
| node_id        | int       | node id                                  |
| thr_no         | int       | thread number                            |
| thr_nm         | varchar   | thread name                              |
| c_loop         | bigint    | No of loops in main loop                 |
| c_exec         | bigint    | No of signals executed                   |
| c_wait         | bigint    | No of times waited for more input        |
| c_l_sent_prioa | bigint    | No of prio A signals sent to own node    |
| c_l_sent_priob | bigint    | No of prio B signals sent to own node    |
| c_r_sent_prioa | bigint    | No of prio A signals sent to remote node |
| c_r_sent_priob | bigint    | No of prio B signals sent to remote node |
| os_tid         | bigint    | OS thread id                             |
| os_now         | bigint    | OS gettimeofday (millis)                 |
| os_ru_utime    | bigint    | OS user CPU time (micros)                |
| os_ru_stime    | bigint    | OS system CPU time (micros)              |
| os_ru_minflt   | bigint    | OS page reclaims (soft page faults       |
| os_ru_majflt   | bigint    | OS page faults (hard page faults)        |
| os_ru_nvcsw    | bigint    | OS voluntary context switches            |
| os_ru_nivcsw   | bigint    | OS involuntary context switches          |
+----------------+-----------+------------------------------------------+

these two tables shows currently which blocks run in which thread resp. statistics per thread.

the statistics are from data-node started, so to see trend, one need to snapshot table,and compare with snapshot.
the fields starting with os_ru_ are gather with getrusage(RUSAGE_THREAD) (or equivalent)

lots of numbers! and I'm honestly not quite sure how to interpret them
a few simple rules might be that (for a non idle cluster)

user time should be high and system should be low
involuntary context switches should be low
page faults should be low

STANDARD DISCLAIMER

the exact format of the tables might (will) change before reaching a release
it's currently unknown when(if?) they will reach a release near you

new features in MySQL Cluster 7.2.1

2011-10-10T09:12:00.000+02:00

AQL (aka push down join)
Further improvements and refinements compared to 7.2.0 from April
Index statistics
A long over due feature, that aims to reduce(minimize) need of manual query tuning that previously has been essential for efficient SQL usage with ndb.
memcache access support
Active-Active replication enhancements
Various internal limits has been increased
- Max row-size now 14k (previously 8k)
- Max no of columns in table now 512 (previously 128)
Rebase to mysql-5.5 (7.2.1 is based on mysql-5.5.15)
Improved support for geographically separated cluster
(note: single cluster...i.e not using asynchronous replication)

Brief introduction to AQL (aka join pushdown)

Basic concept is to evaluate joins down in data-nodes instead(in addition to) of in mysqld.
Ndb will examine query plan created by mysqld, and construct a serialized definition of this join, ship it down to data-nodes.
This join will in the data-nodes be evaluated in parallel (if appropriate), and the result set will be sent back to mysqld using a streaming interface.
Performance gain (latency reduction) is normally in the range of 20x for a 3-way join.

Brief introduction to Index statistics

The index statistics works a lot like Innodb persistent statistics.
When you execute analyze table T, data nodes will scan the indexes of T and produce a histogram of each index.
This histogram is stored in tables in ndb (mysql.ndb_index_stat_head and mysql.ndb_index_stat_sample).The histogram can then be used by any mysqld connected to this cluster.The histogram will not be generated until a new analyze table T is requested.

Brief introduction to Active-Active enhancements

MySQL Cluster has supported active-active asynchronous replication with conflict detection and conflict resolution since 6.3.
In prior version, the schema had to be modified, adding a timestamp column to each table and application has to be modified to maintain this timestamp column.
In this new version, no schema modification is required and no application modification is needed.
In previous version, conflict detection/resolution was performed on row-by-row basis.
In this new version, transaction boundaries are respected.
E.g in a row R is determined to be in conflict, not only this row-change will be resolved,
but entire transaction T that modified the row will be resolved and all transactions depending on the T transitively.
Longer descriptions can be found here and here

Sorry for omitting hex dumps and/or unformatted numbers

Mandatory late update: the join described here has now gained an extra 2x (but this improvement did not make 7.2.1)

Star schema benchmark on MySQL Cluster 7.2

2011-05-10T08:03:00.008+02:00

I decided to try the star schema benchmark on our latest 7.2 release (link). Star schema benchmark is an analytics oriented benchmark, and MySQL Cluster has not been developed to address this kind of workload. Nevertheless I couldn't resist trying...

Setup

2 data-nodes each running on a 4-way Xeon E7420 @ 2.13GHx (total 16 cores) 256Gb RAM
The mysqld was co-located with one of the data-nodes
I used memory tables

Results

Queries: link

Query	sf10	sf100
Q1.1	5	62
Q1.2	0.4	4.6
Q1.3	0.1	1.1
Q2.1	9	95
Q2.2	48	495
Q2.3	51	517
Q3.1	47	481
Q3.2	47	476
Q3.3	2	20
Q3.4	2	19
Q4.1	57	572
Q4.2	57	574
Q4.3	12	120

(time in sec, less time is better)

Facts

I have done no analysis to why some queries are faster than others
This type of queries is currently not in focus of our efforts to improve join performance (nor has it been so far)
for lineorder table I specified max_rows=500000000
ndb_join_pushdown=on (default in 7.2)

Thoughts and guesses

sf100 is almost exactly 10x slower than sf10. So I think a reasonable guess is that sf1000 is 10x slower than sf100
Star schema benchmark is an easy benchmark (compared to e.g TPC-H)
I think results looks quite good
My guess is that scaling out (e.g to 4 nodes) would scale well (although I didn't try) (e.g scale out by running 2 ndbmtd on each machine)
Running high write load in parallel doesnt increase query times signficantly (as tested in presentation page 45-46) which is quite cool (although I didn't try this time)

Load procedure for sf100

I manually split the 61Gb file (lineorder) into 10 6Gb files (using split(1))
Then I loaded these 10 in parallel with 10 clients using load data infile
I disabled indexes while loading using ndb_restore --disable-indexes (from an backup made on empty db containing just schema definition)
I then rebuilt the indexes using ndb_restore --rebuild-indexes using BuildIndexThreads=15 in config.ini

(for sf10 i did nothing special)

Load time for sf100

4h including rebuilding of indexes

References

Blog where percona tries SSB

Last minute update

OleJohn reports that Q2.2 and Q2.3 should be 10x faster if forcing a different join order (e.g by using straight_join or force index). But this I haven't tried

distributed pushed down joins - webinar and new preview available

2010-11-04T11:51:00.004+01:00

as part of the webinar tonight we made a new preview release src and binary (linux x86-64 glibc23).

this includes pushing of ref that was not supported in last preview release as well as latest and greatest new optimizations.

note that this is still not production ready code.
but...we welcome any feedback on it!

---

Nov 5: And feedback can be sent to spj-feedback@sun.com

distributed pushed down joins - progress and attitude

2010-08-25T11:46:00.008+02:00

we're now at a stage so we can test/benchmark scan/scan queries, i.e push most queries. there are still know bugs and things that we can't push.

to see how we performs, we tested a very old query/dataset, that a customer tried in the 4.1 days. the result at that time was a disaster, and they at that time decided to use something else.

it's a 11-way join with mainly REF accesses (i.e not many lookups)

i tested it on same hardware/configuration as for my UC presentation.
without join pushdown the average query time is 3.6s.
with join pushdown it's ~500ms, i.e ~7x improvement.

the nights of kni are deeply disappointed, and that's an attitude i like :-)

FYI: we also optimized TPCW::getBestSeller some more and are now at 42x on that hardware/configuration.

---

stay tuned

---

micro update: Found a bug when using ndbmtd...fixing that gave 200ms (i.e 18x)...also did some more optimizations and now record is 120ms (i.e 29x)

distributed pushed down joins - node-failure handling done

2010-05-20T13:54:00.003+02:00

node-failure handling was as expected lots of small changes to various pieces of the code.
some non compatible protocol changes performed (nice not to have any existing GA release to care about :)

also when doing this, I re-implemented entire "abort of join"-handling.
it should now also be capable of handling aborting more complex joins
(but node-failure is still an special case)

this means that "testSpj -n NF_Join" now passes!!

---

this is an important step towards a releasable version.

note: this has only been implemented in the branch which also contains extension for scan.vs.scan joins. I.e not in the branch released as preview.

and, OleJohn integrated support for filters on child-joins with mysqld.

---

And as always please provide feedback on preview release that we made!

distributed pushed down joins - more testing

2010-05-12T13:34:00.003+02:00

have now written and pushed ndbapi testing of our push-down join implementation (~2k LOC)

the following classes/programs was added

HugoQueryBuilder (class)
Construct a pseudo-random query
inputs: a list of tables and a options specifying which features should be used in query
HugoQueries (class)
Run an arbitrary query.
Note: Does only verify individual rows, not relations/result-set. For verifying result-sets we use random query generator
testSpj (program)
Ndbapi test-program using above classes to runs various tests for our cluster specific automatic testing framework
hugoJoin (program)
Stand-alone test-program that takes list of tables/options as command-line arguments
and using above classes constructs/runs random queries

Quite happy with this, as it's very much needed to get good coverage of the implementation,
especially error cases (e.g node-failure) which is hard to test from/with SQL.

---

And please provide feedback on preview release that we made!

distributed pushed down joins - more features

2010-05-07T14:02:00.003+02:00

update on latest accomplishments:

added support for filters (compare engine_condition_pushdown) on non-root operations, this in 2 flavors:
- - constant/immediate filters that is provided after NdbQuery has been defined, these may not contains filter conditions referencing other NdbQueryOperation's,
- - parameterized/linked filters, these type of programs must be provided when building the NdbQuery-object.
The constant filters should be "easy" to integrate with ndbapi/mysqld, but the parameterized/linked is harder as we need to add new features to NdbScanFilter.

Once this is integrated into mysqld, it will provide better performance for already pushable queries, see page 43 in my UC presentation (ref: filter).
added support for NdbQueryOpertation's referencing non-direct parent NdbQueryOperation's. This should also be quite easy to integrate into ndbapi/mysqld. This will mean that new types of queries will become pushable.
support for "to-many" joins is made so complete that it will get until we get an ndbapi for more elaborate testing. This will mean that lots of new queries will become pushable.

Note: all the additions has only been made inside ndb(mt)d so far, nothing new has (yet) been added to ndbapi/mysqld.

---

And please provide feedback on preview release that we made!

distributed push down joins - first "to many" join

2010-04-24T21:38:00.003+02:00

just managed to run the first "to-many" join inside the data-nodes.
the query correspondce to

SELECT t1.*, t2.*
FROM T1 t1
LEFT OUTER JOIN T1 as t2 on t2.pk >= t1.b

- the code inside the data-nodes is general...but incomplete (data-node later crashed)
- there is no ndbapi and no SQL,the program testing is a hard-coded c-program sending messages using the ndb-cluster wire-protocol
- the result-set is not managed (the c-program just hangs)
- hopefully converting this into usable code will be faster than last years post as ndbapi is now quite complete, and this is "only" an add-on.

for a bit more background look at my presentation from MySQL Conference 2010.

hard-core details:


r.bn: 245 "DBTC", r.proc: 2, gsn: 32 "SCAN_TABREQ" prio: 1
s.bn: 32768 "API", s.proc: 4, s.sigId: 0 length: 11 trace: 1 #sec: 2 fragInf: 0
 apiConnectPtr: H'00000027 requestInfo: H'08400a01:
  Parallellism: 1 Batch: 64 LockMode: 0 Keyinfo: 0 Holdlock: 0 RangeScan: 0 Desc
ending: 0 TupScan: 0
 ReadCommitted: 1 DistributionKeyFlag: 0 NoDisk: 1 spj: 1 attrLen: 0, keyLen: 0 
tableId: 7, tableSchemaVer: 1
 transId(1, 2): (H'00000000, H'00000400) storedProcId: H'0000ffff
 batch_byte_size: 32768, first_batch_size: 64
 H'00000027 H'00000000 H'08400a01 H'00000007 H'00000001 H'0000ffff H'00000000
 H'00000400 H'00000026 H'00008000 H'00000040
SECTION 0 type=generic size=1
 H'00000014
SECTION 1 type=generic size=33
 H'000f0002 H'00050002 H'00000010 H'00000007 H'00000001 H'00010001 H'00090003
 H'00000003 H'00000008 H'00000001 H'00000001 H'00000003 H'00010001 H'00000002
 H'00060000 H'00080002 H'00000009 H'10000020 H'00000001 H'00000012 H'00000002
 H'fff00002 H'ffe90000 H'000a0003 H'00000009 H'ffff0100 H'10000024 H'00000001
 H'00000012 H'00000003 H'fff00002 H'ffe90000 H'fffb0000
---- Received - Signal ----------------
r.bn: 32768 "API", r.proc: 4, r.sigId: -1 gsn: 5 "TRANSID_AI" prio: 1
s.bn: 249 "DBTUP", s.proc: 2, s.sigId: 166035 length: 3 trace: 1 #sec: 1 fragInf
: 0
 H'10000020 H'00000000 H'00000400
SECTION 0 type=linear size=7
 H'fff30004 H'00000003 H'00000001 H'00000002 H'ffe90008 H'00000014 H'00000000
---- Received - Signal ----------------
r.bn: 32768 "API", r.proc: 4, r.sigId: -1 gsn: 5 "TRANSID_AI" prio: 1
s.bn: 249 "DBTUP", s.proc: 2, s.sigId: 166092 length: 3 trace: 1 #sec: 1 fragInf
: 0
 H'10000020 H'00000000 H'00000400
SECTION 0 type=linear size=7
 H'fff30004 H'00000003 H'00000002 H'00000003 H'ffe90008 H'00000014 H'00000001
---- Received - Signal ----------------
r.bn: 32768 "API", r.proc: 4, r.sigId: -1 gsn: 5 "TRANSID_AI" prio: 1
s.bn: 249 "DBTUP", s.proc: 2, s.sigId: 166202 length: 3 trace: 1 #sec: 1 fragInf
: 0
 H'10000024 H'00000000 H'00000400
SECTION 0 type=linear size=9
 H'fff30004 H'00000003 H'00000001 H'00000002 H'ffe90008 H'00000014 H'00000000
 H'fffb0004 H'00000000
---- Received - Signal ----------------
r.bn: 32768 "API", r.proc: 4, r.sigId: -1 gsn: 5 "TRANSID_AI" prio: 1
s.bn: 249 "DBTUP", s.proc: 2, s.sigId: 166203 length: 3 trace: 1 #sec: 1 fragInf
: 0
 H'10000024 H'00000000 H'00000400
SECTION 0 type=linear size=9
 H'fff30004 H'00000003 H'00000002 H'00000003 H'ffe90008 H'00000014 H'00000001
 H'fffb0004 H'00000000
---- Received - Signal ----------------
r.bn: 32768 "API", r.proc: 4, r.sigId: -1 gsn: 5 "TRANSID_AI" prio: 1
s.bn: 249 "DBTUP", s.proc: 2, s.sigId: 166227 length: 3 trace: 1 #sec: 1 fragInf
: 0
 H'10000024 H'00000000 H'00000400
SECTION 0 type=linear size=9
 H'fff30004 H'00000003 H'00000001 H'00000002 H'ffe90008 H'00000014 H'00010002
 H'fffb0004 H'00000001
---- Received - Signal ----------------
r.bn: 32768 "API", r.proc: 4, r.sigId: -1 gsn: 5 "TRANSID_AI" prio: 1
s.bn: 249 "DBTUP", s.proc: 2, s.sigId: 166234 length: 3 trace: 1 #sec: 1 fragInf
: 0
 H'10000024 H'00000000 H'00000400
SECTION 0 type=linear size=9
 H'fff30004 H'00000003 H'00000002 H'00000003 H'ffe90008 H'00000014 H'00010003
 H'fffb0004 H'00000001

uploaded cluster pushdown join presentation...and preview-release

2010-04-15T03:13:00.006+02:00

presentation is here
src tarball, that do contain bugs if node-failure occurs, is here

thanks for attending for those of you that did
feedback on preview is welcome, e.g here

maybe we'll do a binary version...if so i'll make a new post

fast IO and ndb

2010-03-25T18:16:00.010+01:00

i read Marks blog entry about fast IO for PBXT, InnoDB (1.0.6 plugin) and MyISAM with some interest.

then there was the durable, not durable and really not durable blog in which Mark and LinuxJed i discussed cluster, and Mark wondered how much IO one could get from 1 data-node with ndb.

so I decided to try.
the setup is similar.
ndb cluster using disk-tables, with a tablespace stored in /dev/shm.

Data occupied 7.3G, which is slightly less than (some of) the others, this is as we don't support having columns with indexes stored on disk. I.e any column that has an index will be stored only in memory instead.

ndb does however not support "handler" statements, so I used oltp-point-select-all-cols instead.


sysbench --test=oltp --mysql-host=foobar --mysql-user=root \
--mysql-password=pw \
--mysql-db=test \
--oltp-table-size=40000000 \
--max-time=60 --max-requests=0 \
--mysql-table-engine=ndb \
--db-ps-mode=disable --mysql-engine-trx=yes \
--oltp-read-only --oltp-skip-trx --oltp-test-mode=simple \
--oltp-point-select-all-cols \
--oltp-dist-type=uniform \
--oltp-range-size=1000 \
--num-threads=1 --seed-rng=1 run

results are not super great...
so i did a ndbapi program (which is roughly the equivalent of handler-statements)

these number look a bit better. but datanode (ndbmtd) was almost idle when running this...

so i made another experiment. Instead of retrieving 1 row at a time (set@r = rand() % 40000000; select * from sbtest where id = @r) i changed to retrive 16 rows at a time (set @r1 = rand(); set @r2 = rand(); select * from sbtest where id in (@r1...@r16).

i believe these results are relevant given that mark's aim was to test fast IO,
and i think that this rewrite wont affect other SE as much as it does with ndb.
and those numbers was quite ok.

as an extra bonus, i also tried with using our memory tables (alter table sbtest storage memory), also with ndbapi and 16 rows at a time.

these tests was executed on a 16-core machine Intel(R) Xeon(R) CPU E7420@2.13GHz
and in my config.ini I had


DiskPageBufferMemory = 1GB
DiskIOThreadPool=8
FileSystemPath=/dev/shm

note: I first create tables in myisam, then converted them to ndb disk-tables by issuing "alter table sbtest storage disk tablespace TS engine = ndb" and all tests was using ndb-cluster-connection-pool=4

all results:


SQL      2756   4998   7133   9130  10720  12222  13305  14190  14626  15287  15547  15955  16143  16334  16507  16757
ndbapi   4177   7581  10319  13162  15245  17064  18874  20652  20850  24131  25976  24910  29832  30666  32625  34841
b=16     1520  11374  35454  53396  55601  63248  71819  78468 103324  97330  97572 111099 125564 126790 133873 141588
mm b=16 73004 128296 172207 207361 246907 270783 293753 312435 327006 345085 346924 374837 360747 372192 376887 394862

Dbspj update, teaser for UC

2010-03-15T15:07:00.007+01:00

5 months more...

reminder, what is Dbspj:
- It's a new feature for MySQL Cluster
- It gives the possibility to push-down SQL joins, i.e to evaluate the joins inside the data nodes.

latest and greatest:
- last 5 months spent on SQL integration
- big effort having it used only for cases that we actually support
- lots of testing using RQG
- a significant part of bugs found last couple weeks, has not been related to spj, but in fact "ordinary" optimizer bugs
- we think that it's quite usable now.

and the numbers:
- my plan was to present TPC-W as "realistic" numbers
- now they look more like fantastic numbers
- if i could, i would add a "25x" to my title
- visit my UC presentation (link) to learn more

and fyi: we also plan to provide a feature preview source (or binary) release for

(i will of course also disclose all information provided in UC presentation after the event)

Dbspj + mysqld, first trials

2009-10-22T16:13:00.004+02:00


mysql> create table T1 (a int, b int, primary key(a)) engine = ndb;
mysql> select x.a,x.b,z.a,z.b from T1 x, T1 y, T1 z where x.b=y.a and y.b=z.a;
+----+------+----+------+
| a  | b    | a  | b    |
+----+------+----+------+
| 31 |   47 | 63 |   31 |
| 63 |   31 | 47 |   63 |
| 47 |   63 | 31 |   47 |
|  5 |   47 | 63 |   31 |
+----+------+----+------+
4 rows in set (0.01 sec)

- entire query is executed with one request to data nodes
- code is only a hack (that bluntly examines mysqld's internal structures).
- list of limitations is so long that i can't write it here due to bandwidth restrictions
But still super cool!

Quote from Jan, that implemented experiment: "You can write queries that return correct result"

Dbspj preliminary numbers

2009-10-14T10:41:00.007+02:00

So some 5 month later...
- Dbspj has an ndbapi
- Dbspj works enough for simple benchmarks!

Reminder, what is Dbspj:
- It's a new feature for Ndb
- It gives the possibility to push-down linked operations (e.g in SQL terminology: joins)
- It currently only supports left-outer-joins, and only some kinds of joins
- It is currently *not* in anyway integrated with mysqld (for accelerating SQL access)

Anyway so here is the benchmark setup
2 computers
- ndbapi running on one
- 2 datanodes running on other

On images below:
- red is new code, blue is corresponding "current" code
- Y-axis is run-time, so lower is better
- X-axis is "depth", i.e no of tables joined

Note: this is debug-compiled, so the actually absolute numbers are
not that interesting...rather the comparison...

Query 1:
depth 1: select * from T t1, T t2 where T1.pk = constant and T2.pk = T1.pk
depth 2: select * from T t1, T t2, T t3 where T1.pk = constant and T2.pk = T1.pk and T3.pk = T2.pk
etc...

Query 2:
depth 1: select * from T t1, T t2 where T2.pk = T1.pk
depth 2: select * from T t1, T t2, T t3 where T2.pk = T1.pk and T3.pk = T2.pk
etc...

ArenaAllocator complete (Dbspj)

2009-05-06T12:05:00.004+02:00

and i created a new DataBuffer2, which has pool-type as template argument.
it was "impossible" to alter DataBuffer to do this...it was simply to hard-wired to ArrayPool. too bad.

---

so now everything in Dbspj can/will be using the ArenaAllocator...
rationale for ArenaAllocator is really not to have a "quick" release (the code still releases each object individually...to reset magic values) but that it's a kind of variable length allocation strategy that provide great locality of data...

---

also pushed infrastructure for "result-set correlation".
Dbspj will (at least for now) when joining only return rows once so in the case of a 2-way join, a SQL result set will return the rows from the first table one time *for each* match in second table.
Dbspj will not...(but ha_ndbcluster will have to do this...when presenting things for mysqld) and since it will not, there has to be a way to correlate rows from the 2 tables.
this we call "result-set correlation".

memory allocation in ndb(mt)d

2009-05-02T11:00:00.003+02:00

inside the data nodes a number of different techniques for allocating memory is used.
there are 2 main variants
- page allocations
- object allocations (e.i slab allocator)

and for the object allocations there are a number of different variants
- RWPool, "ordinary" slab, only objects of same type on 1 page, free-list per page and object
- WOPool, allocations which are short-lived, no free-list just ref-count
- ArrayPool, fixed array of packed object

and I'm just now introducing a ArenaAllocator (which will be used for spj)

---

- RWPool, WOPool and ArenaAllocator/Pool are "new", meaning that they are build from start with support for more dynamic memory handling in mind.
- ArrayPool is very fixed, and exists both in the structured form, and various hard-coded variants.

---

one thing in common for all the "new" allocators is that they use magic numbers per object, so that every time a pointer (once per signal) that validity of the object is checked, and a trap is generated on a invalid memory access.

another thing in common is that they are all used by 1 thread at a time, and only pages are allocated/freed to a global free-list.

---

- to support variable length data, linked list of fixed size objects are used (DataBuffer)
- for input data (from e.g sockets) a special DataBuffer with additional thread-safety code is used so that data can be passed between threads wo/ copying.

---

but there is no malloc/free new/delete :-)

distributed pushed-down join - part II

2009-05-01T12:38:00.003+02:00

now also support table/index scan as root node, i.e tree = [ scan ] ? [ lookup ] *
still with the limitations:
- a child may only be dependent on immediate parent
- still only left outer join.

i also completed the parameterized scan-filter inside tup but does not yet support this in spj or ndbapi.

currently working on
- the parameterized scan-filer in spj (and a tiny bit in the ndbapi).
- result set correlation

currently thinking on
- arena based allocator for spj-requests, (compare mysqld mem-root concept)

discussed with frazer what spj "really is", concluded that it probably is some kind of "data-flow-engine"...should probably find some hip acronym/term for it...

note:
current mental capacity is limiting spj to only one scan in a tree.
this limitation can of course be lifted...but not now, or my brain will fry.

---

wonder how i should make people giving me votes on planetmysql??

distributed pushed-down join

2009-04-23T01:55:00.005+02:00

just managed to run the first join inside the data-nodes.
the query correspondce to

SELECT t1.*, t2.*
FROM T1 t1
LEFT OUTER JOIN T1 t2 on t1.pk = t2.pk
WHERE t1.pk = avalue

- the code inside the data-nodes is general...but incomplete (e.g leaks memory, doesnt handle error correctly...)
- there is no ndbapi and no SQL,the program testing is a hard-coded c-program sending messages using the ndb-cluster wire-protocol
- the result-set is not managed (the c-program just hangs)

Summary:
- there is *many* things left to do, when this will actually hit a release is very uncertain (or if it ever will...)
- this is the coolest thing i implemented in a long long time

Details:
- the code is written so that the query and the parameters are sent separately so that we in the future could have the queries permanently stored in the data nodes.
- the query is:
SELECT t1.?1, t2.?2
FROM T1 t1
LEFT OUTER JOIN T1 t2 on t1.pk = t2.pk
WHERE t1.pk = ?3
i.e both values and projection is parameterized

- the serialized form of this request is 44bytes query and 32 byte parameters
- i could do a N-way (key-lookup) join with aribitrary tables and join-conditions using the code inside the data-node
- code *only* supports left-outer-joins and only lookups
- next step is doing scan+lookup

Hard-core details:


sh> cat mysql-cluster/ndb_1.out.log
DBSPJ: ::build()
DBSPJ:  - loop 0 pos: 1
DBSPJ: getOpInfo(1)
DBSPJ: createNode - seize -> ptrI: 57344
DBSPJ: lookup_build: len=5
DBSPJ: attrCnt: 1 -> len: 0
DBSPJ: param len: 4
DBSPJ: attrCnt: 1
DBSPJ:  - loop 1 pos: 6
DBSPJ: getOpInfo(1)
DBSPJ: createNode - seize -> ptrI: 57376
DBSPJ: lookup_build: len=5
DBSPJ: attrCnt: 1 -> len: 0
DBSPJ: added 57376 as child of 57344
DBSPJ: param len: 4
DBSPJ: attrCnt: 1
LQHKEYREQ to 6f70002
ClientPtr = H'0000e000 hashValue = H'87c3aa01 tcBlockRef = H'01080002
transId1 = H'00000000 transId2 = H'00000301 savePointId = H'00000000
Op: 0 Lock: 0 Flags: Simple Dirty NoDisk ScanInfo/noFiredTriggers: H'0
AttrLen: 0 (0 in this) KeyLen: 0 TableId: 4 SchemaVer: 1
FragId: 0 ReplicaNo: 0 LastReplica: 0 NextNodeId: 0
ApiRef: H'80000003 ApiOpRef: H'00000008
AttrInfo:
KEYINFO: ptr.i = 7(0xac3ee800) ptr.sz = 1(1)
H'0x00000000
ATTRINFO: ptr.i = 8(0xac3ee900) ptr.sz = 5(5)
H'0x00000000 H'0xffee0000 H'0x01080002 H'0x0000e000 H'0xfff00005
DBSPJ: execTRANSID_AI
execTRANSID_AI: ptr.i = 3(0xac3ee400) ptr.sz = 2(2)
H'0x00000004 H'0x00000000
LQHKEYREQ to 6f70002
ClientPtr = H'0000e020 hashValue = H'87c3aa01 tcBlockRef = H'01080002
transId1 = H'00000000 transId2 = H'00000301 savePointId = H'00000000
Op: 0 Lock: 0 Flags: Simple Dirty ScanInfo/noFiredTriggers: H'0
AttrLen: 0 (0 in this) KeyLen: 0 TableId: 4 SchemaVer: 1
FragId: 0 ReplicaNo: 0 LastReplica: 0 NextNodeId: 0
ApiRef: H'80000003 ApiOpRef: H'00000008
AttrInfo:
KEYINFO: ptr.i = 8(0xac3ee900) ptr.sz = 1(1)
H'0x00000000
ATTRINFO: ptr.i = 9(0xac3eea00) ptr.sz = 1(1)
H'0xfff00005

sh> cat api-signal-log.txt
---- Send ----- Signal ----------------
r.bn: 245 "DBTC", r.proc: 2, gsn: 12 "TCKEYREQ" prio: 1
s.bn: 32768 "API", s.proc: 3, s.sigId: 0 length: 8 trace: 1 #sec: 2 fragInf: 0
apiConnectPtr: H'00000020, apiOperationPtr: H'00000008
Operation: Read, Flags: Dirty Start Execute NoDisk IgnoreError Simple  spj
keyLen: 0, attrLen: 0, AI in this: 0, tableId: 4, tableSchemaVer: 1, API Ver: 5
transId(1, 2): (H'00000000, H'00000300)
-- Variable Data --
SECTION 0 type=generic size=1
H'00000000
SECTION 1 type=generic size=19
H'000b0002 H'00050001 H'00000000 H'00000004 H'00000001 H'00000001 H'00050001
H'00000001 H'00000004 H'00000001 H'00000001 H'00040001 H'00000000 H'00000008
H'fff00005 H'00040001 H'00000000 H'00000008 H'fff00005
---- Received - Signal ----------------
r.bn: 2047 "API", r.proc: 3, r.sigId: -1 gsn: 10 "TCKEYCONF" prio: 1
s.bn: 245 "DBTC", s.proc: 2, s.sigId: 241503 length: 9 trace: 1 #sec: 0 fragInf:
0
H'80000005 H'00000000 H'00000000 H'00000001 H'00000000 H'00000300 H'00000008
H'80000002 H'00000000
---- Received - Signal ----------------
r.bn: 2047 "API", r.proc: 3, r.sigId: -1 gsn: 5 "TRANSID_AI" prio: 1
s.bn: 249 "DBTUP", s.proc: 2, s.sigId: 241503 length: 22 trace: 1 #sec: 0 fragIn
f: 0
H'80000007 H'00000008 H'00000000 H'00000301 H'fff30004 H'0000001f H'00000000
H'00000005 H'ef76733c H'deece672 H'ffffffff H'80000007 H'00000008 H'00000000
H'00000301 H'fff30004 H'0000001f H'00000000 H'00000005 H'ef76733c H'deece672
H'ffffffff

mutex micro benchmark

2009-04-12T21:55:00.007+02:00

when working on mutex contention for ndbapi (mysql-cluster)
i decided to do some micro benchmarks.

The benchmark is threads that locks/unlocks an private mutex
and increment a counter.

The tests are:

mutex_aligned, pthread_mutex, lock/unlock, each mutex in separate cache-line
mutex_non_aligned, same as above but mutexes are packed together hence sharing cache-lines
spin_aligned, home-made spinlock (only x86), each spinlock in separate cache-line, the spinlock is an atomic operation for lock, and a full-barrier+assign for unlock
spin_non_aligned, same as above but spinlocks are packed together hence sharing cache-lines
lock_xadd, atomic-inc, (on sparc impl. using atomic.h, which uses cas i think)
xadd, (only x86), the non-smp (but irq) safe add variant for x86
gcc_sync_fetch_and_add, gcc intrinsic for atomic add
add_mb, "normal" add (on volatile variable) followed by a full-barrier
add, "normal" add (on volatile variable)
nop, a nop, just to see that thread start/stop does not affect test outcome noticable

The conclusions are:

atomic operations are very expensive
false sharing is a true disaster
it might be worth the effort to impl. both spinlocks and atomic-inc for sparc

Sorry for lousy html formatting :(

Intel(R) Core(TM)2 Quad CPU Q6600@2.40GHz (1-socket 4-cores)
		mops vs threads
op	ns/op	1	2	3	4	5	6	7
mutex_align	42	23	44	66	88	78	79	89
mutex_non_align	42	23	7	10	8	19	18	22
spin_align	16	60	121	182	234	184	196	212
spin_non_align	16	60	16	24	32	39	40	57
lock_xadd	8	117	235	352	470	357	352	411
xadd	2	342	684	1026	1368	855	1026	1196
gcc_sync_fetch_and_add	8	119	239	359	479	371	359	419
add_mb	5	171	342	513	684	455	513	598
add	2	398	797	1195	1594	996	1196	1394
nop	0	6357142	2870967	2870967	2119047	1390625	898989	687258

2 x Intel(R) Xeon(R) CPU X5355 @2.66GHz (2-socket 4 cores each)
		mops vs threads
op	ns/op	1	2	3	4	5	6	7	8	9	10	11	12	13	14
mutex_align	43	22	42	63	84	105	126	145	162	126	134	132	141	139	155
mutex_non_align	43	22	10	15	18	18	22	29	27	27	33	36	44	49	57
spin_align	17	56	112	166	210	275	292	273	260	318	270	345	312	354	346
spin_non_align	17	56	17	36	38	51	49	50	55	72	91	93	123	87	120
lock_xadd	10	98	195	289	377	467	513	504	504	442	420	525	512	582	605
xadd	2	380	742	1060	1189	1490	1610	1829	1350	1687	1760	1954	1714	1425	2006
gcc_sync_fetch_and_add	10	98	195	287	375	466	560	488	680	523	583	556	622	587	597
add_mb	7	126	252	369	479	587	589	770	719	598	650	639	686	649	602
add	2	443	861	974	1393	1775	1807	1904	1740	1986	2139	1679	1555	2307	2116
nop	0	4114457	3283653	2355172	1366000	903439	803529	617540	640712	532761	466530	410950	382418	351699	320356

SUNW,T5240, 2*(HT-64) 1415MHz SUNW,UltraSPARC-T2+ (2-socket 8-cores each, 8 threads/core)
		mops vs threads
op	ns/op	1	9	17	25	33	41	49	57	65	73	81	89	97	105	113	121
mutex_align	299	3	29	55	78	98	115	132	141	153	161	179	181	191	200	208	209
mutex_non_align	299	3	7	13	22	29	37	44	51	57	63	68	73	78	83	88	92
lock_xadd	70	14	125	232	326	408	472	506	538	536	520	512	503	499	492	487	477
add_mb	34	28	258	469	637	759	853	909	937	947	926	915	897	881	893	892	870
add	13	74	637	1020	1257	1398	1412	1356	1295	1222	1247	1273	1246	1265	1272	1286	1287
nop	0	184331	46367	24849	17520	12893	10676	9051	7616	6697	6196	5492	4960	4069	3739	3486	3289

bugs and more bugs

2009-04-09T13:57:00.000+02:00

have i worked on so far this year...
but 7.0 is soon to be released...and
things are starting to look reasonable

api-thread-contention TODOs

2009-01-13T11:53:00.003+01:00

currently working on a refactoring ndbapi for mysql-cluster, in order to decrease
mutex contention allowing to better utilize many threads on multi-core machines.
while doing so, i implement a "vanilla" ndbapi-backend which lacks features
current in there.

once this is working, i'll start adding optimizations...

reintroduce:
- zero copy receive
- optimizations for single threaded performance (less ctx switches)

introduce:
- split mutex(es)
- zero copy send (or actually 1-copy instead of 2, but zero sounds better)

shortstat

2008-12-05T19:24:00.002+01:00

i ran some simple stats on mysql releases


*** mysql-4.1..mysql-5.0
commits: 7790
diffstat: 4424 files changed, 1271855 insertions(+), 199555 deletions(-)
 /sql      255 files changed,  129725 insertions(+),  41964 deletions(-)
 /test    1509 files changed,  954053 insertions(+),  15940 deletions(-)

*** mysql-5.0..mysql-5.1
commits: 6411
diffstat: 10244 files changed, 2172077 insertions(+), 1349098 deletions(-)
 /sql       243 files changed,  151483 insertions(+),   69799 deletions(-)
 /test     3862 files changed, 1258333 insertions(+),  206729 deletions(-)

*** mysql-5.1..mysql-6.0
commits: 3546
diffstat: 3574 files changed, 679669 insertions(+), 82131 deletions(-)
 /sql      226 files changed,  63619 insertions(+), 16469 deletions(-)
 /test    1772 files changed, 292884 insertions(+), 33553 deletions(-)
 /storage 1184 files changed, 281298 insertions(+),  9179 deletions(-)

*** mysql-5.1..mysql-5.1-telco-6.2
commits: 761
diffstat: 841 files changed, 75105 insertions(+), 37063 deletions(-)
 /sql      72 files changed,  8673 insertions(+),  5544 deletions(-)
 /test    326 files changed, 12269 insertions(+), 18376 deletions(-)
 /storage 396 files changed, 52983 insertions(+), 12434 deletions(-)

*** mysql-5.1-telco-6.2..mysql-5.1-telco-6.3
commits: 347
diffstat:  455 files changed, 27372 insertions(+), 10033 deletions(-)
 /sql       39 files changed,  3471 insertions(+),   740 deletions(-)
 /test     215 files changed,  8735 insertions(+),  2251 deletions(-)
 /storage  182 files changed, 14990 insertions(+),  7031 deletions(-)

*** mysql-5.1-telco-6.3..mysql-5.1-telco-6.4
commits: 582
diffstat: 733 files changed, 73161 insertions(+), 30912 deletions(-)
 /sql      12 files changed,   472 insertions(+),   250 deletions(-)
 /test     48 files changed,  1151 insertions(+),   218 deletions(-)
 /storage 622 files changed, 70267 insertions(+), 30010 deletions(-)

--- conclusions

none

--- how to get git copy of mysql repository

git-clone git://ndb.mysql.com/mysql.git

--- script that produces stats


#!/bin/sh

R="mysql-4.1..mysql-5.0 mysql-5.0..mysql-5.1 mysql-5.1..mysql-6.0 mysql-5.1..mysql-5.1-telco-6.2 mysql-5.1-telco-6.2..mysql-5.1-t
elco-6.3 mysql-5.1-telco-6.3..mysql-5.1-telco-6.4"

for i in $R
do
        echo "*** $i"
        echo "commits: `git-log --no-merges $i | grep Author | wc -l`"
        echo "diffstat: `git-diff --shortstat $i`"
        echo " /sql     `git-diff --shortstat $i -- sql/`"
        echo " /test    `git-diff --shortstat $i -- mysql-test/`"
        if [ -z "`echo $i | grep mysql-5.0`" ]
        then
        echo " /storage `git-diff --shortstat $i -- storage/`"
        fi
        echo
done

950k reads per second on 1 datanode

2008-11-25T13:10:00.000+01:00

i spent last night adding 75% of the next step for our multi-threaded datanode.
and got new numbers...
the config is the same as earlier post, with the exception that
MaxNoOfExecutionThreads=8

flexAsynch -ndbrecord -temp -con 4 -t 16 -p 312 -a 2 -l 3 -r 2
insert average: 461584/s min: 451928/s max: 474254/s stddev: 2%
update average: 533083/s min: 530950/s max: 537351/s stddev: 0%
delete average: 564388/s min: 559265/s max: 567143/s stddev: 0%
read average: 948954/s min: 937288/s max: 959262/s stddev: 0%

also tried using SCI instead of gigabit ethernet
flexAsynch -ndbrecord -temp -con 4 -t 16 -p 256 -a 2 -l 3 -r 2
insert average: 568012/s min: 550389/s max: 578367/s stddev: 2%
update average: 599828/s min: 598480/s max: 602175/s stddev: 0%
delete average: 614036/s min: 612440/s max: 616496/s stddev: 0%
read average: 1012472/s min: 1003429/s max: 1024000/s stddev: 0%

i.e with SCI the 1M reads/sec limit is reached! (on 1 datanode)
i think this should also be achievable on ethernet by adding some
more optimizations (let api-application start transactions directly
on correct TC-thread)

---

comments:
1) the new "feature" is multi threading the transaction coordinator
aka MT-TC

2) this part will likely not make the mysql cluster 6.4.0-release

3) our multi-threading architecture seems promising,
in less than a month i managed to double the throughput
(in a admittedly unrealistic benchmark, but still)

4) the 25% missing from the current patch is node-failure handling
and a "rcu-like" lock which will be used for reading/updating distribution
(it's read for each operation, and updated during node-failure,node-recovery and
online table repartitioning)

700k reads per second on 1 datanode

2008-11-05T09:55:00.000+01:00

added multi connect to flexAsynch, got new numbers
everything else same as previous post

[jonas@n1 run]$ flexAsynch -ndbrecord -temp -con 2 -t 16 -p 512 -l 3 -a 2 -r 2
insert average: 360679/s min: 346150/s max: 370075/s stddev: 2%
update average: 373349/s min: 372465/s max: 374132/s stddev: 0%
delete average: 371014/s min: 357043/s max: 378523/s stddev: 2%
read average: 731042/s min: 702211/s max: 760631/s stddev: 2%

500k reads per second on 1 datanode

2008-11-03T15:20:00.002+01:00

just did some benchmarking on multi-threaded ndbd (binary called ndbmtd)
that is in the coming 6.4 release.

quite happy with results

--- results

[jonas@n1 run]$ flexAsynch -ndbrecord -temp -t 8 -p 512 -r 5 -a 2
insert average: 374200/s min: 374200/s max: 374200/s stddev: 0%
update average: 370947/s min: 370947/s max: 370947/s stddev: 0%
delete average: 395061/s min: 395061/s max: 395061/s stddev: 0%
read average: 537178/s min: 531948/s max: 543092/s stddev: 0%

---

this flexAsynch command will run with
- 8 threads
- 512 parallel transactions per thread
- 8 byte records.

note: during the reads, the datanode was *not* maxed out.

---

this was run on two identical computers,
2-socket, 4 cores per socket Intel(R) Xeon(R) CPU X5355 @ 2.66GHz

api-program was running on computer 1 (n1)
datanode was running on computer 2 (n2)

--- configuration

[cluster_config]
DataMemory=2000M
IndexMemory=150M

SendBufferMemory=8M
ReceiveBufferMemory=8M
LongMessageBuffer=64M

NoOfReplicas=1
ndb_mgmd=n1
ndbd=n2
mysqld=n1,n1,n1,n1
Diskless=1
MaxNoOfExecutionThreads=6
MaxNoOfConcurrentTransactions=16384

forks, add-on patch-sets and features

2008-10-16T10:00:00.000+02:00

so far little is happening in this area with MySQL Cluster.
would be interesting to get patches to cluster from a(ny) (huge-web) company...
wonder if that will ever happen...
maybe we don't use enough buzz-words

---

it could also be that we add features in a high enough pace ourselves,
preliminary benchmarks of our multi-threaded ndbmtd(4 threads)
shows up to 3.7 times better throughput than singled threaded ndbd.

create(drop) node(group) pushed

2008-09-15T09:48:00.002+02:00

this friday.

no (currently :) known bugs

end of think-period

2008-09-02T14:49:00.005+02:00

today, I think I finally cracked how to create(drop) a nodegroup.
basic concept is to
- temporary block gcp
- create(drop) the node group
- unblock gcp

(the same concept is btw used for adding a starting node to gcp)
the block should last for micro seconds

now it's only implementing it...

---

very happy that I now know how to proceed,
I've spent quite a lot of time trying to figure out a 100% safe
way of doing it...(wo/ blocking gcp)
but this solution will be efficient and fairly easy to implement.
(if any protocol dealing with (multi)node-failures can be considered easy)

status of create/drop node(group)

2008-08-30T11:22:00.004+02:00

status:
create/drop nodegroup now works with one noticeable exception
replication cant be connected while the nodegroup is added.
i'll try to find time to fix this next week.

howto:
- start a 2 node cluster
- create table T1
- stop ndb_mgmd, add 2 nodes, start ndb_mgmd
- either stop the 2 running nodes and restart all 4
or rolling restart the 2 running nodes, and then start the 2 new nodes
- ndb_mgm> create nodegroup n1,n2
- alter table T1 add partitions partitions 2

Tata! fully online scaling of the cluster

howto backwards:
- drop table T1
- ndb_mgm> drop nodegroup X
- ndb_mgm> n1,n2 stop -a
- stop ndb_mgmd, remove the 2 nodes, start ndb_mgmd
- either stop the 2 running nodes and restart them
or rolling restart them

(A nodegroup is allowed to be dropped if it does not contain any data)

side effect:
- I added the possibility to specify nodegroups per node in the config-file
(this I intend to use for testing, but maybe someone might find it interesting)

future:
- magnus is working on "online configuration change" in the ndb_mgmd
once this is complete/functional, we can add the "add node"-command
so that the entire procedure can be done wo/ node restarts.

---

In pain

2008-08-15T13:20:00.001+02:00

I miss bk

summer months

2008-07-04T09:15:00.003+02:00

june: customer issues and bugs
july: vacation
august: must complete add node (I haven't started, but work has been done by stewart)

----

No need to comment this...
if you want to make me happy, choose one of the earlier posts

boom-tjackalack! table-reorg is pushed

2008-06-08T19:14:00.003+02:00

so...now table-reorg is in 6.4.
pushbuild found a few problems...that are fixed.

what is left:
1) detailed test-prg (which will check consistency after each step, by pausing schema-trans)
2) handling of cluster-crash during reorg
only way right now, is to restore a backup if you get crash during reorg
3) node failure during might cause SUMA to not scan some fragments
(this bug is an old one, existing in 4.1, that also affect unique index build)
4) reorg-abort (in certain state) leaves REORG_MOVED bit on records,
cause subsequent reorgs (to different partitioning) to create inconsistent data.

Not too bad...
I do however think it's quite testable (although maybe not extremely interesting wo/ add node)

Will start on add-node...and fix problems above in parallel

almost push-time

2008-06-05T16:08:00.002+02:00

I've now:
- fixed error handling (although testing is still not 100%)
- pushed the grand unified table state patch
- pushed a few patches in the series...

No one commented asking for a snapshot,
so i decided to push into 6.4 instead.

Will just spend some more time testing/cleaning up...

response to comment with questions

2008-06-05T13:51:00.002+02:00

1) Which operations can I perform during a table reorg?
everything except DDL and node restart
ndb does currently only allow one DDL at a time, and the reorg is a DDL
ndb does currently prevent node restart while DDL in ongoing

2) What happens to an ongoing table reorg during
2a) node failure
reorg will be completed or aborted depending on how long it has progressed
(i.e if commit has been started)
2b) cluster failure, and recovery?
reorg will be completed or aborted depending on how long it has progressed
(i.e if commit has been written)

The reorg is committed after rows have been copied, but before rows has been
deleted/cleaned up

3) How do my a) SQL b) NDBAPI applications have to be changed to cope with table reorg?

Not at all, but
- your application can "hint" incorrectly if it does not check table state
and refresh it after reorg has been committed
- your application might encounter temporary errors due to the reorg,
this error is the same that you can get during a node restart, so no special
handling of this is needed.
And hopefully the temporary errors should be rare (testing will show...)

4) How can I trade off the duration of a reorganisation against its resource impact (CPU, Memory, Bandwidth etc.)

Currently you can't. speed is hard-coded. this will maybe be a future feature

5) What performance impact does re-org have on ongoing DML and query operations?

Don't know yet, not enough testing. debug-complied versions that I tested gave maybe 5-10% impact. (there is also another optimization that I want to do...which will reduce the impact)

6) What impact does re-org have on DDL operations?
Ongoing none, cause we only support one at a time.
And the re-org will prevent other DDL from starting while it's running

7) Will there be some easy way to re-org all cluster tables to balance across all available nodes?

write a stored procedure that list all tables, and reorgs them one by one.

8) How are indexes modified during table re-org?
ordered indexes are reorganised together with base table
unique indexes are currently untouched (this should probably change)

9) Which parts of the re-org are serial, and which are parallel?
Same as all other schema-transactions after wl3600.
I.e each operation-step is run parallel on each node,
but only one operation-step is run at a time.

This means that e.g copy and "cleanup" is run in parallel on all nodes.

10) Can I perform an online upgrade to a version of MySQL Cluster that supports re-org?

yes,

11) Can I restore a backup from an old version of MySQL Cluster and get online re-org features?

yes,

12) What are the down sides of this table re-org implementation?

none :-)
but there are some areas for improvement

3) Can re-org cope with heterogeneous NDBD nodes with different DataMemory capacities?

In the kernel, yes, but there is no SQL interface currently to expose this

14) How can I look at hash result to fragment id mapping tables?

Using a hand-written ndbapi program
(maybe will add this to ndb_desc)

---

Puh...
that comment held some may questions...
that i maybe should not be asking for more comments...

known bugs fixed

2008-05-28T06:35:00.002+02:00

now it's only the unknown remaining :-)

mysql-test-run now passes for all tests

current plan:
1) will now start on adding error handling in dict,
it shouldnt be that hard (or take that long)

2) i'll (likely) also do the "grand unified state"-patch in dict (but in 6.4)
aka remove table-state.

3) also now started thinking about add-node

4) maybe I should publish a snapshot...i'll wait to see if I get any comments
requesting it

---

I missed my normal comments-fishing the last posts.
Will try to keep the spirit (even if comments are few)

alter online table T add partition partitions N

2008-05-23T13:58:00.002+02:00

I now enabled SQL-interface to table-reorg.

The syntax (which is the same for other partition mgm) is
ALTER ONLINE TABLE T ADD PARTITION PARTITIONS N;

Also switched so that hashmap partitioning is used for all tables created using SQL.
And mysql-test-run works (including a new ndb_add_partition-test)
(except for some range/list partition testcases)

it's still kind of fragile. Error handling is sparse...

there are 3 known things which are easy to fix
- ndbapi transaction hinting/pruning does not work after/during a reorg
- unique indexes will not work after/during a reorg
- only 1 reorg per table is possible (SUMA caches distribution information incorrectly)

and one quite hard
- cluster crash *during* table-reorg

current plan is
1) fix 3 easy known problems
2) fix error handling
3) write detailed test program (pushed back again!)

wl3600++ complete

2008-05-20T11:17:00.003+02:00

I've coded and pushed wl3600++ to telco-6.4
- lots of code simplification
- lots of "duplicate" code removal

Also merged it into table-reorg clone.
And now system restart just started magically working.
So I can again run mysql-test-run

Will now fix any problems found by mysql-test-run.pl

---

wl3600++ clarification

2008-05-07T22:40:00.002+02:00

Just one thing...

it's obvious that the only long term correct solution
is to add a schema-log instead of the schema-file
but the rules/framework that i'm developing will be
so that a change like that is only (almost) on transaction level,
i.e operations will be almost unchanged...

however,
the schema-log will likely not happen this, next of year after next year...(my guess)

Just wanted to clarify...
(that i'm not an idiot, well at least not a complete one :-)

wl3600++

2008-05-07T15:07:00.002+02:00

Has now agreed on a way forward with pekka on wl3600
How to handle SchemaFile, batching & completeness needed for table-reorg.
What operations/transaction shall (not) do in different stages

Having a consistent model feels great!
(compared to the evolutionary mesh that is present today)

Now I only need to implement it...

---

I now have at least two distinct readers (comments from 2 persons)...
I'm blogging my way into fame

Core functionality(reorg) complete!

2008-04-30T13:45:00.002+02:00

transactions + scan + replication works!

Now remaining:
- error handling
- detailed unit testing
- durability of HashMap
- fix schema trans restart on SR
- fix unique index
- ndbapi interface to HashMap
- optimize COPY by using new operation(ZMOVE)
which creates less load and interacts better with replication
(currently the COPY produces events which is "correct" but not optimal)
- sql-support

And of course add-node :)

---

Personally I think this post deserves at least one comment...

Core functionality(reorg) almost done

2008-04-29T22:56:00.002+02:00

Core functionality is almost complete,
- all operations in place, running in correct order
- all transactions work correctly (with all synchronization)

Remaining is "only" 2 local functions, related to SUMA switchover.

---

Need to spend serious time on a contest to add to this blog,
to get more comments!

Eureka - SUMA switch over for table-reorg

2008-04-24T20:47:00.002+02:00

First it struck me that
- starting to double send events can be done wo/ synchronization, cause
1) this is basic techinque for node-failure handling
2) it does not matter if new-fragment does not contain full epoch, as it will contain
last part

Then (today) it struck me that
- turning off double send on old "home" for row does not either require synchronization
(except doing it on epoch-boundary) but different fragments can do it on different times, cause
- there is already double send ongoing, so no events will be lost

This makes the task relatively straightforward, but following is still needed,
- replication triggers must be turned off at epoch boundary
- replication triggers must be turned off 3 epochs after "turn off" has been initiated

- all of above means that it can be handled per node...(or maybe node group...)
- have to think more about potential per node group synchronization though...

Hope I'm right! Will discuss @office, to see if anyone can find any holes
(including me, as I got the idea today)

---

Still only one comment...I think I need to add a contest of something

What is table-reorg

2008-04-21T07:18:00.003+02:00

Table-reorg is the the procedure which will be executed on "alter table X add partitions Y" .
This you would typically do when you have added Y new nodes to your cluster.

The procedure is online, i.e transactions can run during the operation
and no extra memory will be needed on the "old" nodes.

The reorg is based on linear hashing (but wo/ the normal skewness in distribution)
E.g when going from 2 to 4 partitions 50% of the rows will be moved.

The copying is done in parallel on the "old" nodes,
and consistency is kept using triggers.

---

So I have atleast 1 reader...

Transactions now work!

2008-04-16T21:25:00.002+02:00

- Transactions now work correctly, both pk/uk and table/index scan
- I have decided how to do testing (single-step through reorg)

Fixing schema-transaction seems like a must now (for 6.4)
- schema-file flushing
- complete phase
Maybe I can come up with something else to do first...

---

Still no comments on my blog...wonder if I have any readers

Assorted notes

2008-03-28T16:10:00.003+01:00

- transaction consistency (big part is testing)
- fix schema trans complete phase (to 6.4 directly)
- durability of objects
- fix schema trans restart on SR
- fix unique index
- sql
- automagic HashMap creation

---

Will likely next build testing framework...for transaction consistency

table-reorg plan/progress II

2008-03-28T15:08:00.001+01:00

1) hashmap (done)
2) add partitions (done)
3) reorg-triggers (done)
4) reorg-copy (done)
5) reorg-delete (done)
6) consistent scan (partially done)

---

Held live-demo for office audience

table-reorg plan/progress

2008-03-27T12:07:00.003+01:00

1) hashmap (done)
2) add partitions (done)
3) reorg-triggers (done)
4) reorg-copy (done)
5) reorg-delete
6) consistent scan

done = runnable != complete

---

just "finished" reorg-copy, still needs polishing...

Announce

2008-03-26T20:31:00.001+01:00

Just started...we'll see if I use it