Friday, August 01, 2014

US Visa System Database Crash and Performance Problems - Too Big to Handle for Oracle Database

These days we are witnessing performance and access problems with US Visa system. The culprit is Consular Consolidated Database  - data warehouse deployed as Oracle database on Windows operating system. Current database size is over 100TB ( allegedly one of the largest Oracle databases in the world ), growing double-digits percent per year, storing text and photos, with complex feeds and lookups into external and internal databases. Reason for crash and latest performance problems is a patch that was applied to resolve performance problems.

We think that there are multiple reasons this problem popped up:
- database size is clearly beyond what Oracle database software can comfortably handle
- mixed data types are futher exacerbating the problem ( photos ), as RDBMS is general, and Oracle in particular have a  problem with storing data beyond straightforward text and numbers
- inadequate hardware and operating system ( Wintel )

Possible origins of the problem are: bad analysis of business requirements, bad capacity planning, wrong architecture choice. Probably neither of these steps was done correctly.

Oracle database clearly has a problem with scale out beyond certain size ( couple of dozen TB ) and we are doubtful that even the latest software and hardware currently available from Oracle ( Oracle 12c In-Memory with Exadata on M6-32 Database Machine ) would provide adequate performance for future use on this database. Nevertheless, State Department chose Oracle again for upgrade ( can't get fired for buying Oracle ? ... that might change, even in government ).

Architecture that is better fitted for this purpose is one of many MPP databases available on the market which can comfortably handle large databases ( starting with venerable Teradata and on to SAP HANA, etc.), with photos stored outside database in file system.

Update ( 6/19/2016 - a year later ):  another multi day world wide outage ( still ongoing as of this day). It looks like it is blamed on hardware this time around. Whatever the root cause is, fact remains that Oracle based  US visa system is brittle, unstable and not in functional HA configuration.

No comments: