1.1.1   Spatial join performance¶

This test was run to determine how quickly we can do a spatial self-join (find objects within certain spatial distance of other objects) inside a single table. Ultimately, in our architecture, a single table represents a single partition (or sup-partition). The test involved trying various options and optimizations such as using different indexes (clustered and non clustered), precalculating various values (like COS(RADIANS(decl))), and reordering predicates. We run these tests for all reasonable table sizes (using MySQL and PostgreSQL). We measured CPU and disk I/O to estimate impact on hardware. In addition, we re-run these tests on the lsst10 machine at NCSA to understand what performance we may expect there for DC3b [11]. These tests are documented below. We found that PostgreSQL was 3.7x slower for spatial joins over a range of row counts, and reducing the row-count per partition to less than 5k rows was crucial in achieving lowering compute intensity, but that predicate selectivity could compensate for a 2-4x greater row count.

1.1.2   Building sub-partitions¶

Based on the “spatial join performance” test we determined that in order to speed up self-joins within individual tables (partitions), these partitions need to be very small, \(O(\mathrm{few~} K)\) rows. However, if we partition large tables into a very large number of small tables, this will result in unmanageable number of tables (files). So, we determined we need a second level of partitioning, which we call sub-partition on the fly. This test included:

• sub-partitioning through queries:
  1. one query to generate one sub-partition
  2. relying on specially introduced column (subPartitionId).
• segregating data into sub-partitions in a client C++ program, including using a binary protocol.

We timed these tests. This test is described below. These tests showed that it was fastest to build the on-the-fly sub-partitions using SQL in the engine, rather than performing the task externally and loading the sub-partitions back into the engine.

1.1.3   Sub-partition overhead¶

We also run detailed tests to determine overhead of introducing sub-partitions. For this test we used a 1 million row table, measured cost of a full table scan of such table, and compared it against scanning through a corresponding data set partitioned into sub-partitioned. The tests involved comparing in-memory with disk-based tables. We also tested the influence of introducing “skinny” tables, as well as running sub-partitioning in a client C++ program, and inside a stored procedure. These tests are described at below. The on-the-fly overhead was measured to be 18% for select * queries, but 3600% if only one column (the skinniest selection) was needed.

1.1.4   Avoiding materializing sub-partitions¶

We tried to run near neighbor query on a 1 million row table. A starting point is 1000 sec which is ~16 min 40 sec (based on earlier tests we determined it takes 1 sec to do near neighbor for 1K row table).

The testing included:

• Running near neighbor query by selecting rows with given subChunkId into in memory table and running near neighbor query there. It took 7 min 43 sec.
• Running near neighbor query by running neighbor once for each subChunkId, without building sub-chunks. It took 39 min 29 sec.
• Running near neighbor query by mini-near neighbor once for each subChunkId, without building sub-chunks, using in-memory table. It took 13 min 13 sec.

1.1.5   Billion row table / reference catalog¶

One of the catalogs we will need to support is the reference catalog, even in DC3b it is expected to contain about one billion rows. We have run tests with a table containing 1 billion rows catalog (containing USNO-B data) to determine how feasible it is to manage a billion row table without partitioning it. These tests are described in detail below. This revealed that single 1-billion row table usage is adequate in loading and indexing, but query performance was only acceptable when the query predicates selectivity using an index was a small absolute number of rows (1% selectivity is too loose). Thus a large fraction of index-scans were unacceptably slow and the table join speed was also slow.

1.1.6   Compression¶

We have done extensive tests to determine whether it is cost effective to compress LSST databases. This included measuring how different data types and indexes compress, and performance of compressing and decompressing data. These tests are described in details below. We found that table data compressed only 50%, but since indexes were not compressed, there was only about 20% space savings. Table scans are significantly slower due to CPU expense, but short, indexed queries were only impacted 40-50%.

1.1.7   Full table scan performance¶

To determine performance of full table scan, we measured:

1. raw disk speed with dd if=<large file> of=/dev/zero and got 54.7 MB/sec (2,048,000,000 bytes read in 35.71 sec)
2. speed of select count(*) from XX where muRA = 4.3 using a 1 billion row table. There was no index on muRA, so this forced a full table scan. Note that we did not do SELECT * to avoid measuring speed of converting attributes. The scan of 72,117,127,716 bytes took 28:49.82 sec, which is 39.8 MB/sec.

So, based on this test the full table scan can be done at 73% of the raw disk speed (using MySQL MyISAM).

1.1.8   Low-volume queries¶

A typical low-volume queries to the best of our knowledge can be divided into two types:

• analysis of a single object. This typically involves locating a small number of objects (typically just one) with given objectIds, for example find object with given id, select attributes of a given galaxy, extract time series for a given star, or select variable objects near known galaxy. Corresponding representative queries:

  SELECT * from Object where objectId=<xx>
  SELECT * from Source where objectId =<xx>
  

• analysis of objects meeting certain criteria in a small spatial region. This can be represented by a query that selects objects in a given small ra/dec bounding box, so e.g.:

  SELECT * FROM Object
  WHERE ra BETWEEN :raMin AND :raMax
  AND decl BETWEEN :declMin AND :declMax
  AND zMag BETWEEN :zMin AND :zMax
  

Each such query will typically touch one or a few partitions (few if the needed area is near partition edge). In this test we measured speed for a single partition.

Proposed partitioning scheme will involve partitioning each large table into a “reasonable” number of partitions, typically measured in low tens of thousands. Details analysis are done in the storage spreadsheet [7]. Should we need to, we can partition the largest tables into larger number of smaller partitions, which would reduce partition size. Given the hardware available and our time constraints, so far we have run tests with up to 10 million row partition size.

We determined that if we use our custom spatial index (“subChunkId”), we can extract 10K rows out of a 10 million row table in 30 sec. This is too long – low volume queries require under 10 sec response time. However, if we re-sort the table based on our spatial index, that same query will finish in under 0.33 sec.

We expect to have 50 low volume queries running at any given time. Based on details disk I/O estimates, we expect to have ~200 disk spindles available in DR1, many more later. Thus, it is likely majority of low volume queries will end up having a dedicated disk spindle, and for these that will end up sharing the same disk, caching will likely help.

Note that these tests were done on fairly old hardware (7 year old).

In summary, we demonstrated low-volume queries can be answered through an index (objectId or spatial) in well under 10 sec.

1.1.9   Solid state disks¶

We also run a series of tests with solid state disks to determine where it would be most cost-efficient to use solid state disks. The tests are described in details in [8]. We found that concurrent query execution is dominated by software inefficiencies when solid-state devices (SSDs) with fast random I/O are substituted for slow disks. Because the cost per byte is higher for SSDs, spinning disks are cheaper for bulk storage, as long as access is mostly sequential (which can be facilitated with shared scanning). However, because the cost per random I/O is much lower for SSDs than for spinning disks, using SSDs for serving indexes, exposure metadata, perhaps even the entire Object catalog, as well as perhaps for temporary storage is advised. This is true for the price/performance points of today’s SSDs. Yet even with high IOPS performance from SSDs, table-scan based selection is often faster than index-based selection: a table-scan is faster than an index scan when >9% of rows are selected (cutoff is >1% for spinning disk). The commonly used 30% cutoff does not apply for large tables for present storage technology.

1.2.1   DC1: data ingest¶

We ran detailed tests to determine data ingest performance. The test included comparing ingest speed of MySQL against SQL Server speed, and testing different ways of inserting data to MySQL, including direct ingest through INSERT INTO query, loading data from ASCII CSV files. In both cases we tried different storage engines, including MyISAM and InnoDB. Through these tests we determined the overhead introduced by MySQL is small (acceptable). Building indexes for large tables is slow, and requires making a full copy of the involved table. These tests are described in details in Docushare Document-1386. We found that as long as indexes are disabled during loading, ingest speed is typically CPU bound due to data conversion from ASCII to binary format. We also found that ingest into InnoDB is usually ~3x slower than into MyISAM, independently of table size.

1.2.2   DC2: source/object association¶

One of the requirements is to associated DiaSource with Object is almost real-time. Detailed study how to achieve that has been done in conjunction with the Data Challenge 2. The details are covered below and the pages linked from there. We determined that we need to maintain a narrow subset of the data, and fetch it from disk to memory right before the time-critical association in order to minimize database-related delays.

1.2.3   DC3: catalog construction¶

In DC3 we demonstrated catalog creation as part of the Data Release Production.

1.2.4   Winter-2013 Data Challenge: querying database for forced photometry¶

Prior to running Winter-2013 Data Challenge, we tested performance of MySQL to determine whether the database will be able to keep up with forced photometry production which runs in parallel. We determined that a single MySQL server is able to easily handle 100-200 simultaneous requests in well under a second. As a result we chose to rely on MySQL to supply input data for forced photometry production. Running the production showed it was the right decision, e.g., the database performance did not cause any problems. The test is documented below.

1.2.5   Winter-2013 Data Challenge: partitioning 2.6 TB table for Qserv¶

The official Winter-2013 production database, as all past data challenged did not rely on Qserv, instead, plain MySQL was used instead. However, as an exercise we partitioned and loaded this data set into Qserv. This data set relies on table views, so extending the administrative tools and adding support for views inside Qserv was necessary. In the process, administrative tools were improved to flexibly use arbitrary number of batch machines for partitioning and loading the data. Further, we added support for partitioning RefMatch* tables; RefMatch objects and sources have to be partitioned in a unique way to ensure they join properly with the corresponding Object and Source tables.

1.2.6   Winter-2013 Data Challenge: multi-billion-row table¶

The early Winter 2013 production resulted in 2.6 TB database; the largest table, ForcedSource, had nearly 4 billion rows.[*] Dealing with multi-billion row table is non-trivial are requires special handling and optimizations. Some operations, such as building an index tend to take a long time (tens of hours), and a single ill-tuned variable can result in 10x (or worse) performance degradation. Producing the final data set in several batches was in particular challenging, as we had to rebuild indexes after inserting data from each batch. Key lessons learned have been documented at https://dev.lsstcorp.org/trac/wiki/mysqlLargeTables. Issues we uncovered with MySQL (myisamchk) had been reported to the MySQL developers, and were fixed immediately.

In addition, some of the more complex queries, in particular these with spatial constraints had to be optimized.[†] The query optimizations have been documented at https://dev.lsstcorp.org/trac/wiki/db/MySQL/Optimizations.

2.2.1   Worker failure¶

These tests are meant to simulate worker failure in general, including spontaneous termination of a worker process and/or inability to communicate with a worker node.

When a relevant worker (i.e. one managing relevant data) has failed prior to query execution, either 1) duplicate data exists on another worker node, in which case XRootD silently routes requests from the master to this other node, or 2) the data is unavailable elsewhere, in which case XRootD returns an error code in response to the master’s request to open for write. The former scenario has been successfully demonstrated during multi-node cluster tests. In the latter scenario, Qserv gracefully terminates the query and returns an error to the user. The error handling of the latter scenario involves recently developed logic and has been successfully demonstrated on a single-node, multi-worker process setup.

Worker failure during query execution can, in principle, have several manifestations.

1. If XRootD returns an error to the Qserv master in response to a request to open for write, Qserv will repeat request for open a fixed number (e.g. 5) of times. This has been demonstrated.
2. If XRootD returns an error to the Qserv master in response to a write, Qserv immediately terminates the query gracefully and returns an error to the user. This has been demonstrated. Note that this may be considered acceptable behavior (as opposed to attempting to recover from the error) since it is an unlikely failure-mode.
3. If XRootD returns an error to the Qserv master in response to a request to open for read, Qserv will attempt to recover by re-initializing the associated chunk query in preparation for a subsequent write. This is considered the most likely manifestation of worker failure and has been successfully demonstrated on a single-node, multi-worker process setup.
4. If XRootD returns an error to the Qserv master in response to a read, Qserv immediately terminates the query gracefully and returns an error to the user. This has been demonstrated. Note that this may be considered acceptable behavior (as opposed to attempting to recover from the error) since it is an unlikely failure-mode.

2.2.2   Data corruption¶

These tests are meant to simulate data corruption that might occur on disk, during disk I/O, or during communication over the network. We simulate these scenarios in one of two ways. 1) Truncate data read via XRootD by the Qserv master to an arbitrary length. 2) Randomly choose a single byte within a data stream read via XRootD and change it to a random value. The first test necessarily triggers an exception within Qserv. Qserv responds by gracefully terminating the query and returning an error message to the user indicating the point of failure (e.g. failed while merging query results). The second test intermittently triggers an exception depending on which portion of the query result is corrupted. This is to be expected since Qserv verifies the format but not the content of query results. Importantly, for all tests, regardless of which portion of the query result was corrupted, the error was isolated to the present query and Qserv remained stable.

2.2.3   Future tests¶

Much of the Qserv-specific fault tolerance logic was recently developed and requires additional testing. In particular, all worker failure simulations described above must be replicated within a multi-cluster setup.

4.1.1   Justification¶

• we want to keep the number of partitions <30K. Each partition = table = 3 files. 200K partitions would = 2GB of internal structure to be managed by xrootd (in memory).
• we want relatively small sub-partitions (<2K rows) in order for near neighbor query to run fast, see the analysis above.
• sub-partitions can’t be too small because the overlap will start playing big role, eg overlap over ~20% starts to become unacceptable.

Object density will not vary too much, and will be ~ 5 x 10⁵ / deg², or 140 objects / sqArcmin. (star:galaxy ratio will vary, but once we add both together it wont because the regions that have very high star densities also have very high extinction, so that background galaxies are very difficult to detect)

So a 1K-row subpartition will roughly cover 3x3 sq arcmin. Given SDSS precalculated neighbors for 0.5 arcmin distance, this looks reasonable (eg, the overlap is ~ 17% for the largest distance searched.)

4.2.1   Test 1: CREATE TABLE SELECT FROM WHERE approach¶

The simplest approach is to run in the loop

CREATE TABLE sp_xxx ENGINE=MEMORY SELECT * FROM XX100k where subChunkId=xxx;

for each sub chunk, where xxx is a subChunkId.

Timing: ~ 1.5 sec

4.2.2   Test 2: Segregating in a client program (ascii)¶

Second, we created a C++ program that does the following:

• SELECT * from XX100k (reads the whole 100k row partition into memory)
• segregate in memory rows based on subChunkId value
• buffer them (a buffer per subChunkId)
• flush each buffer to a table using INSERT INTO sp_xxx VALUES (1,2,3), (4,3,4), ..., (3,4,5)

Note that the values are sent from mysqld to the client as ascii, and sent back from the client back to the server as ascii too.

The client job was run on the same machine where the mysqld run.

Timing: ~3x longer than the first test. Only ~2-3% of the total elapsed time was spent in the client code, the rest was waiting for mysqld, so optimizing the client code won’t help.

In summary, it is worse than the first test.

4.2.3   Test 3: Segregating in a client (binary protocol)¶

We run a test similar to the previous one, but to avoid the overhead of converting rows to ascii we used the binary protocol. In practice,

• reading from the input table was done through a prepared statement
• writing to a subChunk tables was done through prepared statements INSERT INTO x VALUES (?,?,?), (?,?,?), ..., (?,?,?), and values were appropriately bound.

Note that a prep statement had to be created for each output table (table names can’t parameterized).

Relevant code is here.

Timing: ~2x longer than the first test. Similarly to the previous test, only 2-3% of time was spent in the client code.

Using CLIENT_COMPRESS flag makes things ~2x worse.

In summary, it is better then the previous test, but still worse than the first test.

6.4.1   Relevance to real data¶

This table is narrow compared to the expected column count for LSST, and only 7 of 16 columns are floating point fields. It may be appropriate to drop/add some of the integer columns so that the fraction of floating point columns is the same as what we will be expecting in LSST. For reference, here are the type distributions of columns for the LSST Object table.

DC3a [2] is about 3/4 floating-point, and the current DC3b [11] estimate is about 90% floating point.

Removing the all the non-float/double columns from the table leaves 7 float columns and 1 bigint column, giving ~88% floating point values. The resulting truncated table was tested as well, although its bit-entropy may not match real data better than the original int-heavy table.

6.4.2   Hardware configuration¶

We tested on lsst-dev03, which is a Sun Fire V240 with Solaris 10 and 16G of memory. The database was stored on a dedicated (for practical purposes) disk that was 32% full after loading. MySQL 5.0.27 was used.

6.4.3   Test Queries¶

q1: Retrieve one entire row

SELECT *
FROM  %s
WHERE id = 40000;

q2: Retrieve about 10% of rows

SELECT  *
FROM    %s
WHERE   bmag - rmag > 10;

q1b: Retrieve bmag from one row

SELECT bmag
FROM  %s
WHERE id = 40000;

q2b: Retrieve bmag from about 10% of rows

SELECT  bmag
FROM    %s
WHERE   bmag - rmag > 10;

6.4.4   Numbers¶

Bulk Performance for 100 million rows: Times in seconds

Truncated table (7/8 float)

6.6.1   Measurement variability¶

Results seemed largely reproducible until additional steps were taken to control conditions (via “flush table” and server restarting). Oddly enough, flushing tables or restarting the server seemed to improve performance for non-indexed, non-compressed situations (compressed tables have not been retested).

So far, none of the tests have included flushing OS disk buffers, since we don’t know of a quick way in Solaris (and the machine has 16G of memory).

MySQL performance itself seems to be quite complex, and sometimes surprising (should “flush table” improve performance?). To get better than factor-of-two estimates, we need to control conditions more aggressively and retest.

7.3.1   Data ingest¶

Loading data done via

LOAD DATA INFILE 'x.csv' INTO TABLE XX FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'"

took 1h 19 min.

iostat showed steady 27 MB/sec write I/O, disk was 18% busy, CPU 90% busy.

The db file size (MYD): 60 GB

7.3.2   Building key¶

Command:

alter table add key(bMag)

CPU ~30% busy. Disk ~75% busy doing 41 MB/sec write and 21 MB/sec read

Took 5h 11min

MYI file size: 12 GB

7.3.3   Adding primary key¶

(This is not a typical operation - we won’t be doing it in LSST)

Command:

ALTER TABLE XX
ADD COLUMN objectId BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Took 15h 36min

MYD grew to 68 GB, MYI grew to 26 GB

Index for objectId takes 14 GB. (theoretically, 1 billion x 8 bytes is 8 GB, so 43% overhead)

7.3.4   Full table scan¶

Command:

-- note, there is no index on r2Mag
select count(*) from XX where r2Mag > 5.6

Takes 30 min

iostat shows between 36-42 MB/sec read I/O, which makes sense (68 GB MYD file in 30 min = 38 MB/sec). This is 84% of the best this disk can do.

7.3.5   Selecting through index¶

-- there is an index on bMag
select count(*) from XX where  bMag > 4;

-- this returns 0 rows
select count(*) from XX where  bMag > 40;

7.3.6   Speed of join¶

We may want to join USNO-B objects with eg SDSS objects on the fly (assuming objectId are synchronized)...

-- create dummy sdss table
CREATE TABLE sdss (
  objectId  BIGINT NOT NULL PRIMARY KEY,
  sdss1     FLOAT NOT NULL,
  sdss2     FLOAT NOT NULL,
  sdss3     FLOAT NOT NULL,
  sdss4     FLOAT NOT NULL,
  sdss5     FLOAT NOT NULL,
  sdss6     FLOAT NOT NULL
);

-- in this case it is 25% of usno rows
INSERT INTO sdss
SELECT objectId, bMag, bFlag, rMag, rFlag,b2Mag, b2Flag
FROM XX
WHERE objectId % 4 = 0;

The loading took 1 h 21 min.

Doing the join:

CREATE TABLE refCat1
SELECT * FROM XX
JOIN sdss using (objectId);

Takes 3 h 54 min

It involved reading 68+8 GB and writing 23 GB. That gives speed 7 MB/sec, which is ~15% of the raw disk speed.

7.3.7   Full USNOB-1 Schema¶

In Informix format (easy to map to MySQL)

create table "informix".usno_b1
  (
    usno_b1 char(12) not null ,
    tycho2 char(12),
    ra decimal(9,6) not null ,
    dec decimal(8,6) not null ,
    e_ra smallint not null ,
    e_dec smallint not null ,
    epoch decimal(5,1) not null ,
    pm_ra integer not null ,
    pm_dec integer not null ,
    pm_prob smallint,
    e_pm_ra smallint not null ,
    e_pm_dec smallint not null ,
    e_fit_ra smallint not null ,
    e_fit_dec smallint not null ,
    ndet smallint not null ,
    flags char(3) not null ,
    b1_mag decimal(4,2),
    b1_cal smallint,
    b1_survey smallint,
    b1_field smallint,
    b1_class smallint,
    b1_xi decimal(4,2),
    b1_eta decimal(4,2),
    r1_mag decimal(4,2),
    r1_cal smallint,
    r1_survey smallint,
    r1_field smallint,
    r1_class smallint,
    r1_xi decimal(4,2),
    r1_eta decimal(4,2),
    b2_mag decimal(4,2),
    b2_cal smallint,
    b2_survey smallint,
    b2_field smallint,
    b2_class smallint,
    b2_xi decimal(4,2),
    b2_eta decimal(4,2),
    r2_mag decimal(4,2),
    r2_cal smallint,
    r2_survey smallint,
    r2_field smallint,
    r2_class smallint,
    r2_xi decimal(4,2),
    r2_eta decimal(4,2),
    i_mag decimal(4,2),
    i_cal smallint,
    i_survey smallint,
    i_field smallint,
    i_class smallint,
    i_xi decimal(4,2),
    i_eta decimal(4,2),
    x decimal(17,16) not null ,
    y decimal(17,16) not null ,
    z decimal(17,16) not null ,
    spt_ind integer not null ,
    cntr serial not null
  );

revoke all on "informix".usno_b1 from "public" as "informix";

create index "informix".usno_b1_cntr on "informix".usno_b1 (cntr)  using btree ;
create index "informix".usno_b1_dec on "informix".usno_b1 (dec)    using btree ;
create index "informix".usno_b1_spt_ind on "informix".usno_b1     (spt_ind) using btree ;

8.2.1   Stripes¶

In this approach, each table partition contains Objects having positions falling into a certain declination range. Since a FOV will usually only overlap a small ra range within a particular stripe, indexes are necessary to avoid a table scan of the stripe when reading data into memory.

There are two indexing strategies under consideration here - the first indexes each stripe on ra (and also clusters data on ra). The second indexes and clusters each stripe on (zoneId, ra). In both cases the height of a stripe is 1.75 degrees. The height of a zone is set to 1 arcminute.

8.2.2   Chunks¶

In this approach, a table partition corresponds to an ra/dec box - a chunk - on the sky. See chunkgen.cpp for details on how chunks are generated. Basically, the sky is subdivided into stripes of constant height (in declination), and each stripe is further split into chunks of constant width (in right ascension). The number of chunks per stripe is chosen such that the minimum distance between two points in non adjacent chunks of the same stripe never goes below some limit.

There are no indexes kept for the on-disk tables at all - not even a primary key - so each chunk table is scanned completely when read. Two chunk granularities are tested: the first partitions 1.75 degree stripes into chunks at least 1.75 degrees wide (meaning about 9 chunks must be read in per FOV), the second partitions 0.35 degree stripes into chunks at least 0.35 degrees wide (so about 120 chunks must be examined per FOV).

So with coarse chunks, each logical table (Object, DIASource) is split into 13218 physical tables. With fine chunks, 335482 physical tables are needed. Due to OS filesystem limitations, chunk tables will have to be distributed among multiple databases (this isn’t currently implemented).

8.3.1   Hardware¶

• SunFire V240
• 2 UltraSPARC IIIi CPUs, 1503 MHz
• 16 GB RAM
• 2 Sun StoreEdge T3 arrays, 470GB each, configured in RAID 5, sustained sequential write speed (256KB blocks) 150 MB/sec, read: 146 MB/sec
• OS: Sun Solaris sun4x_510
• MySQL: version 5.0.27

8.3.2   General Notes¶

Each test is run with both “skinny” objects (USNO-B with some additions, ~100bytes per row), and “fat” objects (USNO-B plus 200 DOUBLE columns set to random values, ~1.7kB per row) that match the expected row size (including overhead) of the LSST Object table. Any particular test is always run twice in a row: this should shed some light on how OS caching of files affects the results. Between sets of tests that touch the same tables, the bustcache program is run in an attempt to flush the operating system caches (it does this by performing random 1MB reads from the USNO-B data until 16GB of data have been read).

DIA sources were generated using the objgen program to pick a random subset of Object in the test FOVs. Approximately 1 in 100 objects were picked for the subset, and each then had its position perturbed according to a normal distribution with sigma of 2.5e-4 degrees (just under 1 arcsecond).

8.3.3   Test descriptions¶

• Read Object data from the test FOVs into in-memory table(s) with no indexes whatsoever.
• Read Object data from the test FOVs into in-memory table(s) that have the required indexes created before data is loaded. These indexes are:
  • a primary key on id (hash index) and
  • a B-tree index on zoneId for fine chunks, or (for all others) a composite index on (zoneId, ra).
• Read Object data from the test FOVs into in-memory table(s) with no indexes whatsoever, then create indexes (the same ones as above) after loading finishes.

Note that all tests except the fine chunking tests place objects into a single InMemoryObject table (and DIA sources into a single InMemoryDIASource table) which are then used to for cross-matching tests. The fine chunking tests place each on-disk table into a separate in-memory table. This complicates the crossmatch implementation somewhat, but allows for reading many chunk tables in parallel without contention on inserts to a single in-memory table. It allows crossmatch to be parallelized by having different clients call the matching routine for different sub-regions of the FOV (each client is handled by a single thread on the MySQL server).

The following variations on the basic crossmatch are tested (all on in-memory tables):

• Use both match-orders: objects to DIA sources and DIA sources to objects
• Test both slim and wide matching:
  • a slim match stores results simply as pairs of keys by which objects and DIA sources can be looked up
  • a wide match stores results as a key for a DIA source along with the values of all columns for the matching object. This is important for the fine chunk case, since looking up objects becomes painful when they can be in one of many in-memory tables.

Note that all crossmatches are using a zone-height of 1 arc-minute and a match-radius of 0.000833 degrees (~3 arcseconds).

8.4.1   Stripes with ra indexes: Performance Summary¶

For the high density FOV

• 3044468 objects are read into memory
• ? objects read from disk

For the low density FOV

• 76073 objects are read into memory
• ? objects read from disk

For the average density FOV

• 373763 objects are read into memory
• ? objects read from disk

8.4.2   Stripes with (zoneId,ra) indexes: Performance Summary¶

For the high density FOV

• 3044282 objects are read into memory
  • note: this is less than the other stripe test (which filters by ra and dec rather than ra and zoneId) due to an off by one error in the zone bounds I was using for the insert query. This slight error doesn’t warrant rerunning the test.
• ? objects read from disk

For the low density FOV

• 76274 objects are read into memory
• ? objects read from disk

For the average density FOV

• 373804 objects are read into memory
• ? objects read from disk

Currently the load test reads in the high density FOV with fat rows in 133 minutes (many times slower than all other approaches). The test is completely IO bound (disk is 80-98% busy, transfer rate is between about 800kB/s and 4MB/s with an average closer to 1MB/s, ~120 r/s sustained). Performance is seek limited - we could attack this problem by putting the indexes for each stripe on separate disks from the data, and furthermore by making sure adjacent stripes don’t share any disk. Due to the extremely poor performance, full test results are omitted. Note also that clustering the stripe tables involved in the test took almost a full week (the tables involved too large for in-memory sorting).

8.4.3   Coarse Chunks: Performance¶

For the high density FOV

• 3044468 objects are read into memory
• 5865220 objects read from disk

For the low density FOV

• 76073 objects are read into memory
• ? objects read from disk

For the average density FOV

• 373763 objects are read into memory
• ? objects read from disk

8.4.4   Fine Chunks: Performance¶

For the high density FOV

• 3596852 objects are read into memory
• 3596852 objects read from disk

For the low density FOV

• 105847 objects are read into memory
• 105847 objects read from disk

For the average density FOV

• 446289 objects are read into memory
• 446289 objects read from disk

8.4.5   Crossmatch performance¶

Zone height is 1 arc-minute and match distance is 3 arcseconds for all tests.

A single in-memory table of objects matched against a single in-memory table of DIA sources (and vice versa) :

For the fine chunking scheme, the match is processed one stripe at a time (for stripes overlapping the FOV) yielding 11 units of work that are currently executed in serial fashion, but could be run in parallel.