5.1.1   Single-process tests with HDD¶

This testing was mostly a part of a development process for initial version of PPDB implementation using baseline schema without big optimizations. Ticket DM-6370 contains lots of details of this initial testing which was done with spinning disks. Tests ran in single-process mode, without splitting FOV into multiple tiles, all processing happened in the same client process.

It quickly became clear that spinning disks do not provide sufficient performance for PPDB access pattern due to high latency. Modifying primary key definition of the DIAObject table with MySQL/InnoDB backend improved data locality and selection performance for that table. Baseline schema defines PK for DIAObject table as (diaObjectId, validityStart), modified PK adds new leading column (htm20, diaObjectId, validityStart). MySQL/InnoDB stores data together with PK, having spatial column as first PK column leads to better data locality. This change has no effect on data locality for PostgreSQL.

Even after this modification both MySQL and PostgreSQL performance on spinning disks was inadequate. Even for relatively small number of visits around 5,000 time to read data from DIAObject table for single visit was at the level of 20 seconds (DIASources were not even read from database in the first series of tests), time to store all records from one visit is at the level of 100 seconds. It is obvious that concurrency is not going to improve situation drastically due to the IOPS limitation of spinning disks.

5.1.2   Single-process tests with SSD¶

For next series of tests SSD-based storage was used, with either SATA or NVMe disks, ticket DM-8966 covers those tests. Quick summary of these test:

• Performance is drastically better than with spinning disks.
• SATA and NVMe disks show very similar performance numbers.
• MySQL and PostgreSQL numbers also look very similar.
• Time to read or write data grows approximately linearly with the number of visits and amount of data in database.
• Typical numbers for 15k visits is 10 seconds for select (which now includes reading of sources in addition to objects) and about 15 seconds for inserts, MySQL performance is slightly worse for inserts.
• Largest contribution to select time is due to DIAObject select,

Ticket DM-8965 tried to improve timing for DIAObject operations by de-normalizing that table into two separate tables. Baseline schema for DIAObject has begin/end times for validity interval, and intervals should cover whole range without gaps, so that begin time of one record is always an end time for some other record with the same object ID. AP pipeline only reads latest version of each DIAObject (with validity end time at +Infinity), and keeping spatial index of all interval is unnecessary overhead. Keeping latest version of each DIAObject in a separate table with spatial index we can speed up both select time (by reducing number of object in spacial index) and insert time (by excluding spatial column from PK for all intervals and not updating validity end time).

Extended schema for DIAObject now consists of two tables:

• DIAObject table with the same columns and indices as in baseline, except that validityEnd column is not filled (it should be dropped from schema entirely),
• DIAObjectLast table with same columns as in DIAObject table, this table contains latest version of each DIAObject.

All select operation in AP pipeline select data from DIAObjectLast table, store operation for new DIAObjects updates both tables. Updates of DIAObjectLast table can potentially be made faster by updating records in place, but that would require relaxing transaction isolation and may not be supported by some backends.

With this updated schema select of DIAObject runs twice as fast compared to previous tests. MySQL also showed better insert performance when using REPLACE query instead of REMOVE+INSERT for DIAObjectLast. PostgreSQL insert performance for two tables was slightly worse than insert into single table, in-place update (UPSERT) was not implemented for PostgreSQL in this test.

5.1.3   Multi-process tests with SSD¶

Ticket DM-9301 runs tests on the same platform but in multi-process setup splitting FOV region into square tiles with either 5x5 or 15x15 split. Each tile is processed in a separate process and all of them run concurrently. Fork mode is used in this case, with server and all client processes running on the same machine. Same DIAObjectLast table was used for optimization of DIAObject access.

Summary of findings for this series of tests:

• multi-process setup runs significantly faster than single-process
• 15x15 tiling runs faster than 5x5
• PostgreSQL performs better than MySQL
• with PostgreSQL performance of NVMe storage is better than SATA

Figure 1 shows wall clock time per visit as a function of visit number for 15x15 tiling with PostgreSQL and NVMe storage. Note that on this and other plots boxes and whiskers signify quartiles, not RMS; and whiskers cover range of all observed values. Small red dots show average value.

Figure 1 Real time per visit as a function of visit number.

5.1.4   Summary of the results from IN2P3¶

The results of all above tests could be summarized as:

• Spinning disk storage performance is clearly inadequate for PPDB.
• SSD storage shows promising results at relatively low number of visits with concurrent tile/CCD processing.
• Processing time shows approximately linear dependency on the size of the data in database and number of visits.
• Further studies with larger data volumes are clearly needed to understand scaling behavior.

5.2.1   Initial tests¶

Ticket DM-14712 provides a long story of the attempt to understand and control Oracle behavior with PPDB. Some notable updates to implementation that were implemented for Oracle are:

• DIAObjectLast table is created as Index-Organized Table (IOT) to reduce additional access to heap data.
• This also required reduction of the width of the table as IOT performance with wide table was unacceptable, leaving nly about 15 columns in that table that are needed by AP pipeline helped to improve performance.

Cluster storage included both spinning disks and SSD, for initial testing I tried to compare SSD and spinning disk performance but results were inconclusive, performance with HDD was not much worse than with SSD, this could be due to large in-memory cache of the array controller.

A lot of time and effort was spent trying to understand significant performance drop observed for small data size (low visit count). The effect was seen as quickly growing processing time for visit which then quickly dropped to a reasonable numbers. Figure 2 show this behavior.

Figure 2 Plot illustrating Oracle performance degradation at low visit numbers.

Database administrator explained that this unfortunate behavior could be remedied by pre-loading table statistics that is needed for optimizer, but that statistics need to be obtained first from running on a larger volumes of data. Several attempt to find workarounds based on query hints were unsuccessful.

Summary from these initial tests (copied from JIRA ticket):

• With freshly initialized schema optimizer prefers (FAST) FULL INDEX SCAN which is significantly worse than INDEX RANGE SCAN plan.
• It looks like optimizer needs to have significant volume of data in a table before it switches to a more efficient plan, I estimate some thing like 10-20 million rows.
• I think stats collection has to be enabled for that too.
• We failed to find a way to force Oracle to lock into a better plan using query hints.
• IOT works reasonably well if table has small number of columns, I think this is what we want for production.

5.2.2   Testing multi-node clients¶

Pervious tests were running prototype in a fork mode using single host on LSST verification cluster. One machine with relatively large number of cores can reasonably handle CPU load from 255 forked processes, but fork mode has one significant drawback in that it needs to open new database connection in each forked process. More efficient approach would be to have per-CCD processes always running and always connected to the database eliminating overhead of making new database connections. This approach was implemented in the new series of Oracle tests using MPI for inter-process communication. MPI also allowed us to use more than one node on client side which eliminates potential client-side bottleneck from non-shared memory.

Ticket DM-16404 describes the results of running Oracle tests using MPI mode with AP prototype running on several machines from LSST verification cluster. To estimate the effect of permanent database connections test was initially configured to close and re-establish connection on every visit but later was switched to permanent connection mode. Figure 3 shows the effect of that switch, per-visit processing time was reduced by about 2 seconds. Figure 4 shows the fit of the data in the region with permanent connections.

Figure 3 Plot illustrating the effect of keeping database connection, after visit 10,000 connections were made permanent. This plot excludes initial region with poor performance.

Figure 4 Fit of the above scatter plot for visits above 10,000.

In addition to total per-visit time collected statistics included time per individual types of database query, e.g. selecting or saving DIAObjects. Comparing visit dependency of these times shows that fastest growing value is the time to select DIASource history, followed closely by time to select DIAForcedSource history. Both timings show approximately linear growth with the number of visits. Figure 5 shows these dependencies. Scaling these two queries to 12 months as required by AP pipeline is probably a most significant problem in PPDB. Figure 6 shows insert time as a function of visit number. Full time (marked as “store_real” on plot) shows linear dependency and is significantly lower than select time. Total time is dominated by insert time for DIAObject, that time is much higher than insert time for DAISource and DIAForcedSource, this is due to more complex indices needed for DIAObject.

Figure 5 Time for different select queries as function of visit number. Top line is a combined sum of three other contributions.

Figure 6 Time for different insert queries as function of visit number. Top line is a combined sum of all individual contributions.

Figure 7 shows fraction of the visits with total visit time higher than 10 seconds as a function of visit number. This plot is for illustration only, it is difficult to interpret its behavior without understanding many details of prototype or its execution environment.

Figure 7 Fraction of visits with total total visit time higher than 10 seconds.

5.2.3   Summary of Oracle tests¶

In general performance of Oracle server is comparable with the numbers from PostgreSQL test in the region where they overlap (below 15 visits) even though those numbers were obtained in very different hardware setup. Fit of the data shows that Oracle performance drops somewhat faster with the number of visits. At 30k visits prototype spends about 10 seconds on data persistency which could still be reasonable for AP pipeline. With linear behavior it is clear that we need some different approach to scale this beyond one month of data.

The issue with quick initial performance drop for Oracle has not been understood or satisfactory resolved, requiring additional step to collect statistics and pre-load it may be a significant drawback for production activities.