wiki:db/BuildingIndex
Last modified 6 years ago Last modified on 02/01/2013 02:11:38 PM

Building Index

Testing speed of building an idex. The test was run using table:

CREATE TABLE o (
  objectId bigint(20) NOT NULL,
  x_chunkId int(11) DEFAULT NULL,
  x_subChunkId int(11) DEFAULT NULL
) ENGINE=MyISAM

and the command was "ALTER TABLE o ADD PRIMARY KEY(objectId)"

rows [K] elapsed time [sec] rows/sec
1 0.31 3,226
10 0.51 19,608
100 1.39 71,942
1,000 5.86 170,648
10,000 130.82 76,441
100,000 2,101.16 47,593
200,000 4,300.25 46,509
300,000 6,515.68 46,043
400,000 8,764.37 45,639
500,000 11,059.16 45,211
600,000 13,404.72 44,760
700,000 15,666.41 44,682
800,000 17,872.14 44,762
1,600,000 36,303.64 44,073

~140 writes/sec (~1.2 MB/sec). CPU utilization bursty: close to 100% every 25-30 sec, otherwise very low.

The test was run on lsst-db2 (Dual Core AMD Opteron Processor 270, cpu MHz: 1992.061, cache size: 1024 KB, 4GB memory, the disk used is capable of 138MB/sec sequential read, 100MB/sec sequential write (as measured by dd)