Report on Cross Matching Catalogues

1. Introduction

This document reports work carried out during 2003 July to September.  The main aims were:

1. To compare the ease of use and scalability of three DBMS: DB2, MySQL, and Postgres.  We were particularly interested in their ability to handle spatial data and carry out a spatial join between two tables representing source catalogues.
   
2. To compare the performance of two spatial join algorithms: (a) using a true spatial index e.g. based on an R-tree, (b) pixel-code method using a 2-d to 1-d mapping function.

The reasons for choosing these three DBMS to evaluate were:

• Postgres is a full-featured open source DBMS which has for some time supported spatial data types and R-tree indexing.
• MySQL, another open-source DBMS, has a reputation for fast execution of simple queries.  The latest alpha release has limited support for spatial data types with R-tree indexing.
• DB2 is a major commercial DBMS which is free for academic use under the IBM Scholars programme; it has a spatial indexing option (Spatial Extender) using multi-level grid-file indexing, and DB2 also supports advanced features such as parallel hardware and distributed databases.

2. Documentation

Postgres

Mysql

DB2

3. Installation, Configuration, and Tuning

Postgres

MySQL

DB2

4. General Usability

Postgres

• After creating an index it is necessary to perform an ANALYZE operation, otherwise the index is not used in subsequent queries.  In dynamic environments the operation has to be repeated regularly, but for a static table once is enough.  Index creation takes a bit longer because of this, the times were included in those reported here.
  
• Floating-point constants are taken as double-precision by default with no automatic casting to other floating-point types.  This causes problems when using an index on a real column.  Thus the syntax SELECT * FROM table WHERE vmag BETWEEN 12.5 AND 12.6 ;  would not use an index on the (real) column vmag, it needed to be re-phrased thus: SELECT * FROM table WHERE vmag BETWEEN 12.5::FLOAT4 AND 12.6::FLOAT4 ;
• In a few cases I found that queries took longer when an index was available than when it was absent, or when two indices were provided than when only one was provided.  It is possible to influence the query plan, but I did not have time to explore this fully.
   
  

MySQL

DB2

• The db2 command provides a simple way of entering SQL, here the lines need no semi-colon at the end, so therefore long lines need a continuation marker.  I could not find this documented anywhere, but found by trial and error that "\" worked.  The db2 interface provided no execution or elapsed time information.  There was no command-line recall or editing, no doubt designed for the user who always manages to get multi-line SQL commands perfect every time: I wonder if such users exist?
  
• The db2batch command seemed similar, but had the bonus of providing elapsed time information.  This could be augmented by cpu-time if db2batch was started with the right command switch.  The cpu-time was listed anyway, but was always zero without this switch: why it was not the default is hard to understand.  The other switches for db2 and db2batch were quite different for identical functionality, presumably written by different teams who did not bother to talk to one another.  Db2batch required a semi-colon, but no continuation marker.  It also omitted to provide command-line recall and editing.  I would have used db2batch all the time, but it did not support all SQL commands, only those in some (undocumented) core set.  The data loading commands had to be issued from db2, why I cannot imagine.
  
• The db2cc command invoked something called the "control centre" - a GUI which allowed other GUIs to be started, including the "command centre".  This also allowed SQL commands to be entered.  These also required a semi-colon, so needed no continuation marker.  This would have been a good interface, had it not been programmed incompetently in out-of-date Java.  To my amazement I found that it did not support cut/paste to/from other windows on my screen. I saw many critical comments on the GUIs on various newsgroups, saying, essentially that they were a waste of time.  I came to agree with these comments.

• There seems no way in a single SQL statement to select from one table and have it generate a new table as the result.  This is possible in Postgres (using SELECT INTO table) and in MySQL (using INSERT INTO table SELECT), but in DB2 it required two statements, one to create a new table (with all the right column names and data types) and then a second one to do the INSERT INTO.  This is because, I guess, the normal way SQL is used is just to produce text; the idea that one might want to make a selection and then work on it further is alien to the SQL way of thinking, though this is just what scientists want to do all the time.  The lack of such a facility in DB2 was a serious drawback, not only in impeding data exploration, but because all my SQL scripts had to be altered yet again.
• I could not find any way of stopping a run-away query.  Control/C and the like had no effect.  If I killed the process it seemed to prevent me using database and when loading it left it in an inconsistent state. I generally had to stop and re-start the server (using a different user-name, naturally).  In one case this did not work, and I had to re-boot the machine.
• The transaction log filled up unexpectedly and stopped progress.  I could not find out how to eliminate these logs nor how to disable logging, but eventually discovered how to make more space for these logs, and I increased it by a factor of one thousand.  The documentation suggests that several of these parameters are set by default to values too small for modern computers. 
  

5. Data Loading

Postgres

COPY mytable FROM '/home/cgp/db/data2/psc_baa.csv' WITH DELIMITER '|' ;

MySQL

LOAD DATA INFILE '/home/cgp/db/data2/psc_baa.csv' INTO TABLE mytable FIELDS TERMINATED BY "|" ; 

DB2

LOAD FROM '/home/cgp/db/data2/psc_baa.csv' OF DEL MODIFIED BY COLDEL| INSERT INTO mytable ;

SQL3501W  The table space(s) in which the table resides will not be placed in 

backup pending state since forward recovery is disabled for the database.

6. Spatial Indexing

Postgres

MySQL

insert into us values
 (0.000286,0.07725278,4.06E-05,NULL,19.78,20.59,19.77,17.34,
polygonfromtext('polygon((  0.00084  0.07781, -0.00027  0.07781, -0.00027  0.07670,
 0.00084  0.07670,  0.00084  0.07781))'))

select ura,udec,astext(ubox) from us where
 MBRContains(GeomFromText('Polygon((123.45 0.05, 123.46 0.05, 123.46 1,
123.45 1, 123.45 0.05))'),ubox);

DB2

db2gse.st_polygon(
>    'polygon(('||char(x-radius)||' '||char(y-radius)||','||
>                 char(x-radius)||' '||char(y+radius)||','||
>                 char(x+radius)||' '||char(y+radius)||','||
>                 char(x+radius)||' '||char(y-radius)||','||
>                 char(x-radius)||' '||char(y-radius)||'))', 0)

7. Performance Comparisons

7.1 Joins using R-trees with Mysql and Postgres

The main test joined a section of 2MASS with an overlapping section of USBN-B.

• 2mass: 5 million rows of data, file psc_baa covered 0 to 360 degs RA, and 0.0 to 1.67 degs Dec, but with just these columns selected: RA, DEC, position-error, three magnitudes, error-box.
• USNO-B: 3.6 million rows of data, covering 0 to 360 degs RA, 0.0 to 0.6 degs Dec., with these columns selected: RA, DEC, position-error, five magnitudes, error-box.

The tests were carried out on the main node of hydra, which has dual-Xeon 2.2 GHz processors, and 1 GB of memory.

The speed advantage of Mysql at all the earlier stages was lost in carrying out the join. The support for spatial data and R-trees has only just been introduced, so it is perhaps not surprising that it has not yet been optimised. On these figures, Postgres is the clear winner.

7.2 Simple Query Performance of Mysql and Postgres

It also seemed worth comparing the performance of Mysql and Postgres with a more substantial chunk of data. These tests used ten files of 2mass data, totalling around 50 million rows.

This Postgres result was so odd that it was repeated: subsequent indexed selects took 3.9 seconds, and 0.7 seconds. Presumably some information needed to be stored in the cache before the query was executed efficiently.

7.3 Inner and Outer Join

7.4 Pixel-code versus R-tree

The pixel-code method is described in earlier documents: SkyIndexing and SpatialIndexing and was carried out using both Mysql and Postgres. The times for a spatial join using R-trees was only performed using Postgres, since the performance of Mysql was so poor.

Conclusion: here as well Postgres is somewhat faster than Mysql, and the R-tree indexing spatial join is faster than the pixel-code method using the same DBMS.  This is evident using the rather small tables in this example, but the difference is larger as the table size is increased.  This table compares the two methods using Postgres alone.

8. Summary

cgp 2003 Sept 29