What projects have you been working on recently?
A particularly challenging project which we have undertaken within the ATLAS database community is keeping track of individual collision events across the Large Hadron Collider (LHC) computing grid: building a centralized catalog of ATLAS events at CERN where we store key quantities for each particle collision event registered in ATLAS plus the pointers to the relevant data files. Our catalog has come to realization, serving a number of expanding use cases for the experiment with high efficiency. Moreover, it is provisioned to seamlessly load tens of billions of rows per year and to be able to scale for years ahead. This system was designed and deployed by a core team of three people (Elizabeth Gallas, Petya Vasileva, and myself), who each brought different knowledge and skills to the project.
Which challenges did you face in the process of project development?
The technical challenges were many. First of all, the expected 30-35 billion rows per year have a significant database footprint on the storage in terms of table and index segments. Other serious challenges were the data load speed in the database, the transactions size and the database undo volume. As data is organized in logical units of datasets, for data consistency reasons large database transactions of tens to hundreds of millions of rows have to be supported. With this comes the question: "What if by some reason an active transaction of so many rows has to be rolled back"? Such rollback operation has to be reasonably fast. Our system also has to be able to replace large datasets quickly for the case where the upstream data source refreshes its content. Last but not least, there were challenges in achieving acceptable database response time when performing data mining or numerous simple look-up searches.
What did the requirements imply for the design of the database?
I had to come up with an approach with high-efficiency in data organization and compaction in order to use minimal disk space. This also has a positive effect on minimizing the backup volume. Some elements of our database design which seemed appropriate for this system are not commonly used by the Oracle database developers’ community, so at many points early in the deployment, it was feared that we might come across an Oracle bug or could reach certain database limits. But chosen techniques proved robust and moreover provided the expected benefits in operational capacity and performance.
How are you able to handle such large amounts of data?
I took advantage as much as possible of the existing database features in Oracle 11g. Some of them we use for years and they proved to work reliably, others are (relatively) new or are not so popular. Examples include non-standard column data type to reduce the needed bytes per value, bulk insert and data de-duplication, list type partitioning with custom storage specification, and virtual columns. By now, the system hosts about 120 billion rows as the data ingestion rate has gone beyond the initially foreseen 30 billion rows per year. Currently in ATLAS we use Oracle Enterprise Edition 11.2.0.4 installed on midrange machines (20 CPU cores and 512 GB RAM per machine). We have four Oracle database clusters each having a dedicated role and different number of nodes serving a large variety of database applications.
What top 3 tips would you like to give others who plan to make their database most efficient and stable?
First of all, the global picture is important. Try to design and build the backbone of your database system on a basis for which you are certain that it works and have got experience in. Always have in mind the scalability aspect. Prepare for growing requirements over the time. When users are attracted by the functionality and performance of your system, they will certainly ask for more. Second, although the global picture is important, it also comes along with many small but vital technical details. Particularly relevant is the maintenance cost within the lifetime of the application. Last but not least, I know it is easier said than done because normally the business tasks are rather complex, but try to design for simplicity. The tendency in over-engineering will come with a high cost in the change management.
Further information:
Gancho Dimitrov reports on his exciting project experiences at CERN at DOAG 2017 Conference + Exhibition from November 21 to 24.
Tickets and registration
