To achieve these design goals, our system is set up as follows:

• The RTS has two servers, each with its own local database: one is primary, the other operates as a shadow. Event information from the RTS databases are replicated to the SCEDC databases within 4 seconds and applications accessing the SCEDC database use data generated by the primary system.
• The SCEDC has two independent databases on two separate servers that are continually synchronized with one another.
• The two sets of RTS and SCEDC servers are housed in separate buildings: the USGS building in Pasadena and in the Seismo Lab at Caltech. The systems can operate independently from either site.
• The most common queries done on the database are for parameters of most the recent earthquakes (magnitude, location, time) and associated waveforms. The most frequent queries are done by SCEDC/SCSN internal applications, which poll the database at regular intervals to get the most up-to-date information about new events. Other common queries are catalog searches made by the public and researchers, either through the web catalog on the SCEDC website or STP. These queries also request parametric and waveform data, but may span over a long period of time. As a result, the database schema has been specifically designed to optimize for both types of searches and a number of database indexes have created to increase performance. In fact, indexes account for 43% of space used by objects in the SCEDC database.

Why use Oracle?

• Caltech owns an Oracle Enterprise site license that provides the database server software and Advanced Replication feature. The SCEDC pays Oracle directly for licensing the partitioning feature.
• Oracle allows objects such as tables and indexes to be partitioned, i.e., objects are divided into smaller, more manageable portions. The SCEDC database is currently is 49 Gigabytes and the largest table (waveform) is 9 Gigabytes. The tables which contain waveform, amplitude, and arrival data, are partitioned by year, so users can query the entire table, or they can reference a smaller piece of the table, which significantly improves performance. Partitioning is also a method to reduce maintenance because administration can be focused on particular portions of tables, dividing the maintenance process into more manageable segments.
• Oracle database software with Advanced Replication allows our system to have multiple, continually-synchronized databases. Oracle also provides stored procedures and integration with the Java which is used by post-processing applications. Further information on Advanced Replication is included at the end of this article.

Looking to the future, we are exploring a switch to an open-source database system such as MySQL for our main systems. Clearly, migrating to a system with the same functionality and performance at a fraction of the cost is desirable and we have been impressed by the speed of MySQL in our performance tests. However, the current production release of MySQL (4.0) lacks a number of features that are used heavily by our system:

Multi-master replication
Stored procedures
Views
Triggers
Sequences

Many of these features are slated to be included in the 5.1 release, in the meantime we continue to monitor for new developments, including PostgreSQL (pgSQL).

Oracle Advanced Replication

The SCEDC/SCSN database system uses Oracle's Advanced Replication feature to replicate data among four databases. Each of our databases have a separate copy of the data... When any transactional statement (such as inserts, updates or deletes) is done on the database, it sends these instructions to the other databases for them to perform on their data.

The system employs both one-way and two-way (also known as "multi-master") replication. The RTS-to-SCEDC replication is one-way: the source database (RTS) pushes the data to the target database, but does not receive updates from the SCEDC databases. Data on the RTS are kept for one week before they are purged. The SCEDC archive databases use two-way, multi-master replication to push updates from either database to the other i.e., the target database is also a source, so the two databases are synchronized.

Advanced Replication can be thought of as a collection of tables, stored procedures, and triggers in the database. When a transactional statement, an insert for example, is executed on a replicated table, it sets off a trigger (a program stored inside the database), that instructs the database to store all necessary information to execute the original insert statement into a queue which is also stored inside the database. At regular intervals (every 4 seconds), an Oracle job is executed to look for any outstanding transactions in the queue. If any are found, they are pushed to the remote database site. If this push is successful, the database marks the transaction as sent. Another Oracle job (executed at every 10 minutes) then removes all sent transactions.

Benefits:

• The ability to have two database archives that are continually synchronized allows the Data Center to load-balance applications which provides better performance.
• Having two independent databases on separate servers allows for the possibility of failover if one database should become unavailable. Having replicated data means that each database has its own copy of the data, so if the database becomes disconnected from the system (e.g., in a network outage) the local database objects are still accessible.
• By storing transactions in a queue, the system also has the ability to send these transactions to the target database at a later time, allowing the target database to resynchronize gracefully. Because the process of manually synchronizing databases can be time consuming, this functionality has proven to be very useful when a database becomes unavailable due to maintenance or unforeseen failure.
• The Advanced Replication feature allows for interoperability, which means that the databases and servers do not have to be at the same version level or operating system (within limits). This allows the DBA flexibility in upgrading database versions and flexibility in choosing operating systems. For example, the SCEDC is currently testing an Oracle 10g development database on a Linux platform within our Oracle 9i Solaris system. It is also fairly easy to add additional databases and/or replicated objects in this system. For example, as part of our efforts to integrate with the NCEDC in Berkeley, the SCEDC is using this method of replication to share station data.

Costs:

• Synchronizing databases every 4 seconds requires a substantial amount of database resource overhead. Especially costly are large batch operations where several millions rows are affected. Although normal transactions involving the seismic network never exceed 10,000 rows per transaction, activities such as legacy data migration or data quality control can severely impact system performance and they are usually done with replication temporarily suspended.
• There is also added administrative cost needed to maintain replication triggers and stored procedures. Simple maintenance operations on a single database, such as altering table structure, become significantly more complicated within a replicated environment. Failure to run these procedures properly can result in the object being unavailable for update on all databases, not just the original target database.