Monitoring Ozone Levels with Postgresql
Lai, Alex
Brandon, Marty
Monitoring Ozone Levels with Postgresql Database Streaming Replication and Monitoring Postgres is used to manage data from the Ozone Monitoring Instrument aboard NASA's Aura spacecraft. The database implementation must handle large volumes of complex data transmitted continually from the satellite and generated by processing-intensive analyses performed by a team of atmospheric scientists. This talk will describe the architecture and some of the challenges faced. Focus will be given to our replication efforts, software developed for monitoring, and ongoing work to create a decentralized network of services commnicating through a RESTful interface. NASA and its international partners operate several Earth observing satellites that closely follow one after another along the same orbital track. This coordinated group of satellites, is called the Afternoon Constellation, or "A-Train", for short. Four satellites currently fly in the A-Train: Aqua, CloudSat, CALIPSO, and Aura. Each satellite has one or more observational instruments that are used together in the construction of high-definition three-dimensional images of the Earth's atmosphere and to monitor changes over time. Aura's instruments include the Ozone Monitoring Instrument (OMI). Data management and processing services for data harvested by OMI are provided by the OMI Science Support Team headquartered at Goddard Space Flight Center. Raw OMI data is received and initially processed at a ground station in Finland, then ingested into the system, where it is analyzed by scientists who submit processing jobs. Earth Science Data Types (ESDTs) are the products of these jobs, and one of the principal types of data managed in the database. Complex and abstract, ESDTs represent the interface between the raw science data and the data management system, and more than 900 are currently defined. Our current database implementation includes 10 clusters, each running Postgres 9.0.4, and divided into three production levels: development, testing, and operations. The central operations cluster handles on average about 200 commit statements per second, contains tables as large as 160 million rows, and is configured for streaming replication. New data is continually being added to the system, and the total quantitiy is increasing at a rate of about 60% per year. This influx of data, in addition to scientific analyses, can cause the load on the database to vary suddenly, and monitoring software has been developed to provide early warning of potential problems. The latest implementation of our software architecture uses decentralized services communicating through a RESTful interface. Databases are bundled together with their software component, and schema changes are managed using patch files. A utility has been created to apply the patches, and ensure schema consistency as the databases are amended. Perl's Rose-DB is used as an object-relational mapper, and database queries, via HTTP requests, are supported by encoding the query information into JSON. The new platform uses a different data model, making it necessary to sync between the two representations, and causing some difficulty with data duplication.
 And then this is Alex Lai here in the front row. Alex is the DBA and I'm the developer and assistant DBA on the only science support team at Goddard Space Flight Center. And we're going to talk about monitoring ozone levels with Postgres SQL. So I'm going to begin by giving just a little bit of background and Alex will talk about how data is brought into the system during a process we call ingestion. Alex is going to go over some of the challenges that we faced and then I'll describe some ongoing development in the software platform that affects how we manage the data. So about 450 miles above the Earth's surface is a formation of satellites that are in a polar orbit that takes them across the equator 14 times each day. And they call that formation the A train. And all of the members in the A train are Earth observing satellites so they've got their instruments pointed downward instead of outward. The caboose in this A train you can probably just barely see here is a satellite called Aura. Launched in July 2004, Aura has four different instruments for monitoring Earth's atmosphere. The ozone monitoring instrument is an instrument for monitoring stratospheric ozone levels and that's our instrument, the one that provides us data for our system. Because these guys are in a polar orbit and they go over the North Pole each time they go around the globe, the data is received at a far north ground station like this one in Svalbard, Norway. The data is processed a little bit at the ground station and converted into something called an Earth science data type or ESTT. This is not really the sort of data type that you would normally encounter in a database. It's really more like a procedure for processing a set of measurements. And they're defined by Earth scientists, which I'm not an Earth scientist, but they provide sort of the real interface between the data coming in from the satellite and our data management system. And right now there's more than 900 that have been defined that we have to store in the database. So these ESTTs get processed and are generated and the ultimate goal is that the atmospheric scientists will use them to use the results of all this processing to create atmospheric profiles and climate change models. So Alex is now going to tell you a little bit about the process of how the data gets in and processes. Good morning. Can you hear me okay? Here is, I want to show this diagram to have a high level of the system on the processing system. As you see in the box, you see the data provider, peerless slide showing the ground system, capture all the data coming down, and we'll either send raw data to us or they do some processing, certain level of data, and those are the provider of the data. And then we'll get into our input to our data processing system. And also we have a large group of scientists community and rely on output data to fit into the application and doing research. Those scientists will go through the web interface to making requests and the data gets delivered to the data receiver. And our OME project operator also can go through our web interface to monitoring our processing and also submitting jobs through the planner and see what kind of data need to be. Sometimes we need to reprocess data or any new product coming in or some sender changing the logic to how we manipulate the input data and come out with the final product and fill the web interface. This diagram is showing, as you know, that the data basically have two ways to store the data. The real data will not store in the database. Like the image or all the instrument capture data will be stored in the archive system. The data is a very high volume. And the other type of data we will have to process and they will store in the metadata server. That's how the post-grip capture tracking all the metadata in our system. The web two-way will trigger the ingest process of year-through-the-auto ingest which is relying on the rifle of the new data. We have a logic to checking, demon checking, any incoming file stored in our archive node. And then that will trigger the ingest process and based on certain criteria was set to doing the process of those data. And also the OME operator also can submit any job and controlling the ingest. We can override and seeing the planner and seeing the load and also we will be able to also submit a job that way. And those are what we need to explain a little bit more about our ongoing situation. We're using post-grip almost over 10 years to tracking all the metadata in our legacy system. Back in a few years ago we realized that the data schema needs to be reduced. So we are starting another new development server which you see in the lower corner. The MDX is a new system, ongoing development system. And basically that system already partially in operation. Some application already pointing to that. And the idea is we need to have the metadata update on both systems. But basically the priority is make sure that legacy system is ongoing without losing any data. So we're based on that once we update the metadata, legacy metadata and then we will be, another trigger will go into the MDX metadata server. Otherwise we do, from time to time we do have a little bit problem seeing that are there any reason that we didn't successfully have 100% all the data get into MDX metadata. So that's why you see a box on the Synconi database process, we're trying to catching any missing, any problem, file having successfully ingested in both systems. Yes. Can you repeat it again? So the data is coming through in this process and you're also syncing the database process from legacy system? No, there are two databases that is two different databases. It's not the same database. The scheme is a little different than the two. Yeah, I'm going to show you more in the following slide. There's one of the challenges we are facing, trying to updating the basically the same kind of metadata but in different format of the schema. They are totally different. We have different petition, all this have different. So I'm going to show you later. In the thing I want to talk about beside the synchronization, this is one of the backup as thing in our replication. Right now, this strategy we are using. I want to talk about the history about before we running NIO. We were running it. We still having no replication that time. We are pretty much based on the PGDOM to do the backup. I've seen our data getting very big and take time to doing the DUM. And also the performance will drop while doing that. At that time, we only have one server. And also after the DUM and the data ongoing update and between the first DUM and the second DUM, that window, we are pretty much the database crash and we are losing those data. And the replication NIO really help us to solving this problem. We still continue to do the DUM but less frequent DUM. And also we dump from the slave and it won't causing a performance in the master. I just want to mention some of the experience on the thing we are focused on. How to make sure the replication successfully. This is a kind of area that possibility of the warfire did not apply properly and then can cause the replication fail. So between the master and slave sometimes the network is unavailable for a period of time. We have to get notice about that. And also possibility SA may be deleting some warfire didn't aware of. And you see in the previous slide we have a PG DUM going on. Doses, a copy can take a longer period of time to finish. While the replication going on, we are dumping from the slave and they have some coordination need to be done. I'm going to show you some parameter. We are just to avoid the fail, complete the PG DUM while the replication going on. Actually this parameter showing is a max standby stream line delay. We set it to one to make sure replication in the waste day until the PG DUM finish. That avoiding the crash, crashing the PG DUM. We have some technique to, based on the PG PS operating system to showing the status and the house. And replication going on, we are kind of monitoring those status and if they have too long period of wait then we notice something is going on and the performer may have a little bit problem. So we are trying to have some tool automated tools based on those information to alert the SA and DBA. We are also using the log and capture some information there in case we need to troubleshooting the period of time, the performance issue. Also in PostScript, building a table, system table, you have those information. Also we can use those query to create how the log apply and if they are not move on certain period of time we also get alert and things like that. That's very helpful query table, system table will help us to monitor. And some kind of thing we also, a rule of that make sure our warfowl is in archive in case we need to rebuild or restore. And also make sure we don't patching the warfowl before actually the slay applied it. And we pay extra attention to those displays and also to monitoring the wait stay, how long the sing and also monitoring any dialogue situation happen. And that's just an email we've been using to alert. We haven't have any pager and we try to not work 24 hours. So we don't implement that. And because of our data, getting all the data, input data, we are in an archive server, the worst scenario we can always go back and repost the single data can be reproduced. This is just some notes about the performance issue. How much data we have and currently we have about 10 clusters of the server in different stages of the development. We have the development server and we have test server, we have operation server. And on average, it can be 200 come each per second. Sometimes it can heat up to 10,000. It depends on the workload. This is an average. And also accounting is very difficult to measure. They don't have really the standard we measure how big your database is and this is how I, you know, some indicator how big, I just want to show you the one of the biggest table. We have a 160 million row and the growing rate is about 40, 60 percent the last three years. And I just have some chart, you know, this is a legacy system we have. In 2012, we are already growing, I think, maybe expecting more. Sometimes it's very difficult to predict the growth and expressly, suddenly we may have a new provider, John, they suddenly want to be processing 10 year of data. They can suddenly hand forward data we have. So it's just trying to scale our system, see if we can handle that kind of load. There's a new system. That is a bigger challenge I already mentioned about synchronize that, you know, between you see, it's very cumbersome and also because the ongoing updated data make it difficult to, you identify 160 million row of data, which one is the new one, and also which one is different when we are trying to do synchronize, the script trying to identify, identify is like the hardest part to synchronize. Once you find out which one you need to synchronize, it's easy. That is, we're still working on trying to find a better way, maybe trying to see any audience can suggest a better way if you have different schemer how to do a synchronization. You see this is the legacy schema, I'm not going to go for any detail, but just show you the new one will be simplified, but basically partition the data in different table and things like that. And whenever the synchronization script is pretty much have to mapping the two different schemer and trying to find the right query to update the missing data in the new system. Some of the parameters I just want to show you, this list is not showing the complete list of the configuration in the PostGrip config file. I just want to put in the table, you know, side by side easier to identify, if it doesn't maybe helpful for the beginner, never use a replication, maybe helpful. The certain parameter, you know, master and slave are different. And probably you can read that, it's very straightforward. And PGCon have a lot of pre-registered documents, have a lot of very detailed information. And as you, as I pointed out before this, we make an adjustment because of the PGDOM thing, we put it minus that, what I trade off, the slave during the DOM will not be in the real time. Which is, it doesn't matter for us, our case doesn't need to be 100% real time in our slave. We're using a slave pretty much for the backup purpose and also some long query, a select query doesn't need to update. We're heating our slave to offloading the low from the master that help us to boot up our performance in the master. And this is some other parameter we're using. That's it for me and I'll turn it back to Marty. I'm one of the developers that's been working on the rewrite of our processing platform and some of the decisions that were made in that rewrite affect the way that we are going to manage the data from here on out. And so I wanted to go over some of those things. The big difference in the new platform is that we're doing a lot of decentralized kind of components so that instead of having a more monolithic design, we broke it all out. And these are web components like component X, Y, and Z here. And where it was appropriate, we also splintered a little piece of the database off and integrated that in with its component application. And these guys communicate with each other using REST. So, and I'm not sure if everyone here knows, but wasn't quite sure what to expect in the audience is our first PGCon. So I don't know how many people are developers, but REST is basically using standard HTTP request in order to interact with a web application. And there's three parts or three things that I think are interesting to mention on how this new architecture affects the data management. And one is concerns the web framework and one concerns the ORM that's integrated in. And the other is the system of database patches that we've implemented for doing schema updates. So I'll go over each one of these three. The web framework is, of course, just something that helps to facilitate creating a web application. In our case, we're using Pearl's Mojolicious framework. And Mojolicious implements a model view controller paradigm where, of course, the view is what the user sees. In this case, it's a web page. The model is a state of the system or the database most often. And the controller is kind of a mapping in between the interactions that the user sends in through the view to do something on the model. And here at the bottom, you can see here's some example mappings of URLs against this is REST. So we're using URLs for the users to do the interaction. They're sending in some sort of HTTP request and that eventually has an effect on the database. And so in this example, we've got a GET request which maps to a select statement on a person table. And we have a POST request which would map to an update or an insert statement on the same table. And a DELETE which would just map onto a DELETE. And that's all pretty standard, I guess, for a web framework. The thing that's a little interesting about our system is we've extended Mojolicious a bit so that when the web server starts up, it automatically recognizes the database that it's coupled with so it'll find out if it has an associated database, check the database for all the tables, and it'll generate all these URLs or routes that are used by a web application to do the basic CRUD operations. So you don't have to manually define all these things. The new architecture also has an integrated ORM. And of course that's an Optic Relational Mapper, I'm sure everybody here knows that. And similar to the web framework, we've done some things to help automate the operations that the ORM does. So as the server starts up, the ORM kicks in and also reads the config file. And we'll try to discover the database as well. And the ORM will generate the object methods needed for those CRUD operations. So we've basically got two levels of database operation mappings going on here. So the web server starts up and the web framework is going to generate a lot of routes, a lot of URL type routes that a user would use or the application that the user is using is going to use. And these then make use of the object methods generated by the ORM which can then go about manipulating the database on the back end. And so a lot of this happens just kind of magically. Whenever you create a new application with this framework and you define your database, all these methods and routes just get instantiated for you. Now that, I'll say one thing, that can be a little expensive. So if the database is any size at all, it's going through all these tables generating all the CRUD operations that would be needed to manipulate that. But luckily, since this is a web application, it does it all when the server starts up, so we only pay the price one time. And so far it hasn't been too much of a problem. So since we're doing sort of distributed development, lots of little components that can be developed individually, it was desirable to have a way that developers could create schema changes as they're working on the component. Because remember these components now, these web applications, have the database tightly integrated in there, their own PostgreSQL database in there. So what we did was in addition to the code and the Git repository for the component, we added the schema definition file in this base.sql file. And then as you make, or as changes are made to the schema of the database, developers add patch files very much in the same way that you might add a patch to software code. You add a DDL patch here. And so new patches come in and they're committed to the repository. Eventually you want to migrate these things out to a running instance of component X, or more exactly you want to migrate them out to the database that's running within component X. And the software is going to do a little bit of help in to maintaining the enforcing integrity on these patches to make sure things don't get crazy on that. These slides got a little mangled, I'm sorry. So let's imagine that you have a new patch that comes in here, so they've added patches. And as a patch is added to the Git repository and tested during the process of testing it and committing it to the repository, the schema definition file is rebuilt. It's regenerated to incorporate all the patches that have come later. And an entry is made in this text file called patchesapplied.txt, as sort of a bookkeeping method to keep track of all the patches that have gone into base.sequel. Now, when we're ready to apply new patches over to a running instance, there's an analogous table. You can't hardly see it here, but it's patches applied. It's just like the text file in the Git repository, but this is a regular PostgreSQL table that's keeping track of all the patches that have been applied to this instance of the database. So one thing that the patch software will do for us is take the schema of the running database, check and see which patches, it can compare the patchesapplied.txt file and the patchesapplied table to see which new patches would need to be added to this database. And it can do a dump of these things, sort of a hypothetical dump that you would get if you took the existing schema and applied all these new patches. And if you compare that to base.sequel, you should get the same thing, assuming nobody's done any unauthorized changes to your database. So it's a way of checking and making sure nobody's been mucking around when they should go through the patch mechanism and do it the way we've decided to do this. Not everybody likes that, by the way. How does this instance of the patch is like the data that goes out to the database? Yeah, so the procedure would be something like this. You check out your GET repository and all the patches are in there. They've all been previously committed. But this patch software is built on Perl's module build. It's an extension of this module. And module build is a software for managing the installation of modules normally. And in this case, we've just extended that to manage the installation of the patches. Is that answer your question? And in addition to using module build, we're using RoseDB as the object relational mapper. And as I said, Mojolicious is a rather new Perl framework that has gotten fairly popular in the last year. Okay, and I've also been working with Alex on some sort of monitoring tool for our databases. And as he said, we've got around nine or 10 clusters. There's several principle clusters that we have that we were really concerned with something happen so. But we've got a number of others. And what we'd like is kind of a simple tool that provides something like a weather forecast of how the clusters are doing. And so I created a little web app using Mojolicious. I've been doing this development work already on that that would read a config file of a JSON formatted config file. And then throw this stuff up on a screen, in a browser, actually. And it just gives an icon for each one of the clusters that are color coded to give an idea of how the thing is doing. And it's just a prototype right now. But we've got it reading the config file and throwing the stuff up on the screen. And then we've got a JavaScript event loop that will also create the SSH tunnels out to these databases and then start doing various monitoring operations. And that's the part where I'd love to have some feedback from people because I'm not sure exactly what's the best things to be checking. I know there's various units, commands. You can check some stat tables. If one of these guys go red or yellow, then you may want to click on it. In this case, I clicked on a green one and then it would give you more information. Again, this is a locally installed web application. These web applications you could create with Mojolicious are very light. So you could just check them out if you get repository and you can install it on your workstation in five minutes. And you don't have to bother the essays with root access or anything. And that's what we wanted, something simple to monitor on a databases. So any suggestions, I'd love to hear them. And I guess that brings us to the questions. I don't have it posted anywhere, but I have it on my laptop and I'll be glad to share it. This is something I wrote my own time too. So some of the stuff we couldn't give out, like the module build patch stuff is kind of company stuff. But the monitoring tool has been my own little project. Mojolicious is a convenient one? Is it something you're currently working on? They're kind of like cluster-ish because we have clusters of databases that process things. And the guy who started Clustericious tried to release some other software and we just have a devilishly hard time of it. Because we're actually contractors, we're working for the government, and they just put a mountain of red tape into trying to open source anything. So we're running a little bit of a bind on what we can release. Anything? Yeah. How do you store your measurement data in your R-class and how are they connected to the metadata? Good question. Maybe we can go back. And Alex may want to jump in on this. I think he mentioned this back in... Are you talking about data, real data from the instrument and the calm down with the image or all that? Exactly, he's raw data. We got hundreds of notes for serving in different data centers and we have a long history of the data. And those real data never moved to database. We're not just holding the metadata which is describing when the new raw data we store, the location, and when we take applied with the algorithm processing to what level, and how we classify what DSDP. We are moving by so-called archive set. All this information is a metadata which is in the database, a searchable or application point to that, and how we get access to delivered data and also our database tracking all the ordering status and all the processing even planning when we process all the information on the data. We have never been a software. This is a huge amount of data which is stored in the archive. How is this thought? I can answer that. Do you set up a connection to your database? Well, we have another piece of software that we wrote for this. It doesn't use a relational database. And what it does, as you said, there's millions of policy that are constantly coming in from the satellite because it never rests. And we do an MD5 sum on those and store them in a distributed file archive server that can span across multiple servers. But the location is determined by the MD5 sum on the contents of the file. So you can easily just generate the address on the fly like that. Do you ever use any standard software like HDL5 or something like this? Something self-written. It's self-written, but it's not. I mean, we're still using a lot of Unix-type stuff. I mean, just do the MD5 sum and store it in a directory. And the directory structure is based upon that MD5 digest. So it's generated by that. What you get back from that? It's not too complicated, really. The problem is because there's so many of them, as you said. And sometimes you have to rebalance the servers so that one server doesn't have too many. We did have this in a database. At one time, we were storing the locations in a database, but we've always had them just as files. So the web components mostly work with atmospheric scientists who process the data after it comes in. I think this is talking about the front end and back end. How the data is generated is the internal, pro kind of environment. You're talking about front end. The important front end is how do people know what stuff you have. They have a fix, a lot of applications, even other agencies, other scientists. They have their own project. They can write applications, search engines, you know, based on... As you see, I have previous slide, data receiver. They can actually not only go down over a file, that kind of user. You can develop an app, you know, target certain ozone data and things like that. And you can automatically get the data when the new data come in. Pretty much we allow them to search, you know, order, staging it. They can write applications. How the same goes, how to file, they can see through the application, that's how another way to deliver the data to. So they have different kind of channels, if you put the data. The file system, your story and the architecture is... Exactly. Exactly. Yeah, for storing files, that is the heart and soul. And there's a kind of a division of labor here that's created a little friction recently. So our team wrote the software, the stuff that does the MD5SUM and stores the files. But these SAs over at NASA are managing the hardware back there. And they can implement any kind of RAID array or whatever disk system they want. And recently there was a problem, like files weren't going in fast enough. There was some delays and there was a little bit of finger pointing. It didn't do this. Do you guys wrote this crazy software? But everything that you looked at, you tend to think that it was something wrong with the disk. It got resolved, but I don't know much about the hardware implementation. And it could change without R control. So many terabytes? Oh, many terabytes. No, it's... well, let's see. 2004 to now for Omi. So they spent about eight years worth of data. And they were all in the solution because you know there are many storage systems that are exposed with the results of the solution and the sense of connectivity. Yeah. You know what your old solution was. You know, the guy I worked closely with wrote this solution. And I think he thought it would be kind of neat to write it himself in a way. But we already had something in place and it was quite right. And not everybody agrees that we needed to write something ourselves, but we did. And there was some clever stuff you put into it. But as I mentioned, this has caused a little bit of friction. Do you have an application? I'm sorry. Do you have an application? An application? An application. Replication. Yeah, yeah, yeah. In a serious line, we have a representation reducing the seamless mass and the state by the representation. But this is for the metadata. Okay, thank you all very much. I'm sorry. You're talking about the metadata. Yeah, so far, one talk about around 25 feet, one database. We have two databases. We're talking about close to about 50 feet. So I'm not sure that that's a good way to measure how big the data is. Okay, thanks.
 And then this is Alex Lai here in the front row.  Alex is the DBA and I'm the developer and assistant DBA on the only science support team at Goddard Space Flight Center.  And we're going to talk about monitoring ozone levels with Postgres SQL.  So I'm going to begin by giving just a little bit of background and Alex will talk about how data is brought into the system during a process we call ingestion.  Alex is going to go over some of the challenges that we faced and then I'll describe some ongoing development in the software platform that affects how we manage the data.  So about 450 miles above the Earth's surface is a formation of satellites that are in a polar orbit that takes them across the equator 14 times each day.  And they call that formation the A train.  And all of the members in the A train are Earth observing satellites so they've got their instruments pointed downward instead of outward.  The caboose in this A train you can probably just barely see here is a satellite called Aura.  Launched in July 2004, Aura has four different instruments for monitoring Earth's atmosphere.  The ozone monitoring instrument is an instrument for monitoring stratospheric ozone levels and that's our instrument, the one that provides us data for our system.  Because these guys are in a polar orbit and they go over the North Pole each time they go around the globe, the data is received at a far north ground station like this one in Svalbard, Norway.  The data is processed a little bit at the ground station and converted into something called an Earth science data type or ESTT.  This is not really the sort of data type that you would normally encounter in a database.  It's really more like a procedure for processing a set of measurements.  And they're defined by Earth scientists, which I'm not an Earth scientist, but they provide sort of the real interface between the data coming in from the satellite and our data management system.  And right now there's more than 900 that have been defined that we have to store in the database.  So these ESTTs get processed and are generated and the ultimate goal is that the atmospheric scientists will use them to use the results of all this processing to create atmospheric profiles and climate change models.  So Alex is now going to tell you a little bit about the process of how the data gets in and processes.  Good morning. Can you hear me okay?  Here is, I want to show this diagram to have a high level of the system on the processing system.  As you see in the box, you see the data provider, peerless slide showing the ground system, capture all the data coming down,  and we'll either send raw data to us or they do some processing, certain level of data, and those are the provider of the data.  And then we'll get into our input to our data processing system.  And also we have a large group of scientists community and rely on output data to fit into the application and doing research.  Those scientists will go through the web interface to making requests and the data gets delivered to the data receiver.  And our OME project operator also can go through our web interface to monitoring our processing and also submitting jobs through the planner  and see what kind of data need to be.  Sometimes we need to reprocess data or any new product coming in or some sender changing the logic to how we manipulate the input data and come out with the final product  and fill the web interface.  This diagram is showing, as you know, that the data basically have two ways to store the data.  The real data will not store in the database.  Like the image or all the instrument capture data will be stored in the archive system.  The data is a very high volume.  And the other type of data we will have to process and they will store in the metadata server.  That's how the post-grip capture tracking all the metadata in our system.  The web two-way will trigger the ingest process of year-through-the-auto ingest which is relying on the rifle of the new data.  We have a logic to checking, demon checking, any incoming file stored in our archive node.  And then that will trigger the ingest process and based on certain criteria was set to doing the process of those data.  And also the OME operator also can submit any job and controlling the ingest.  We can override and seeing the planner and seeing the load and also we will be able to also submit a job that way.  And those are what we need to explain a little bit more about our ongoing situation.  We're using post-grip almost over 10 years to tracking all the metadata in our legacy system.  Back in a few years ago we realized that the data schema needs to be reduced.  So we are starting another new development server which you see in the lower corner.  The MDX is a new system, ongoing development system.  And basically that system already partially in operation.  Some application already pointing to that.  And the idea is we need to have the metadata update on both systems.  But basically the priority is make sure that legacy system is ongoing without losing any data.  So we're based on that once we update the metadata, legacy metadata and then we will be, another trigger will go into the MDX metadata server.  Otherwise we do, from time to time we do have a little bit problem seeing that are there any reason that we didn't successfully have 100% all the data get into MDX metadata.  So that's why you see a box on the Synconi database process, we're trying to catching any missing, any problem, file having successfully ingested in both systems.  Yes.  Can you repeat it again?  So the data is coming through in this process and you're also syncing the database process from legacy system?  No, there are two databases that is two different databases.  It's not the same database.  The scheme is a little different than the two.  Yeah, I'm going to show you more in the following slide.  There's one of the challenges we are facing, trying to updating the basically the same kind of metadata but in different format of the schema.  They are totally different.  We have different petition, all this have different.  So I'm going to show you later.  In the thing I want to talk about beside the synchronization, this is one of the backup as thing in our replication.  Right now, this strategy we are using.  I want to talk about the history about before we running NIO.  We were running it.  We still having no replication that time.  We are pretty much based on the PGDOM to do the backup.  I've seen our data getting very big and take time to doing the DUM.  And also the performance will drop while doing that.  At that time, we only have one server.  And also after the DUM and the data ongoing update and between the first DUM and the second DUM, that window, we are pretty much the database crash and we are losing those data.  And the replication NIO really help us to solving this problem.  We still continue to do the DUM but less frequent DUM.  And also we dump from the slave and it won't causing a performance in the master.  I just want to mention some of the experience on the thing we are focused on.  How to make sure the replication successfully.  This is a kind of area that possibility of the warfire did not apply properly and then can cause the replication fail.  So between the master and slave sometimes the network is unavailable for a period of time.  We have to get notice about that.  And also possibility SA may be deleting some warfire didn't aware of.  And you see in the previous slide we have a PG DUM going on. Doses, a copy can take a longer period of time to finish.  While the replication going on, we are dumping from the slave and they have some coordination need to be done.  I'm going to show you some parameter.  We are just to avoid the fail, complete the PG DUM while the replication going on.  Actually this parameter showing is a max standby stream line delay.  We set it to one to make sure replication in the waste day until the PG DUM finish.  That avoiding the crash, crashing the PG DUM.  We have some technique to, based on the PG PS operating system to showing the status and the house.  And replication going on, we are kind of monitoring those status and if they have too long period of wait  then we notice something is going on and the performer may have a little bit problem.  So we are trying to have some tool automated tools based on those information to alert the SA and DBA.  We are also using the log and capture some information there in case we need to troubleshooting the period of time, the performance issue.  Also in PostScript, building a table, system table, you have those information.  Also we can use those query to create how the log apply and if they are not move on certain period of time we also get alert and things like that.  That's very helpful query table, system table will help us to monitor.  And some kind of thing we also, a rule of that make sure our warfowl is in archive in case we need to rebuild or restore.  And also make sure we don't patching the warfowl before actually the slay applied it.  And we pay extra attention to those displays and also to monitoring the wait stay, how long the sing and also monitoring any dialogue situation happen.  And that's just an email we've been using to alert. We haven't have any pager and we try to not work 24 hours.  So we don't implement that.  And because of our data, getting all the data, input data, we are in an archive server, the worst scenario we can always go back and repost the single data can be reproduced.  This is just some notes about the performance issue.  How much data we have and currently we have about 10 clusters of the server in different stages of the development.  We have the development server and we have test server, we have operation server.  And on average, it can be 200 come each per second. Sometimes it can heat up to 10,000. It depends on the workload.  This is an average.  And also accounting is very difficult to measure. They don't have really the standard we measure how big your database is and this is how I, you know, some indicator how big, I just want to show you the one of the biggest table.  We have a 160 million row and the growing rate is about 40, 60 percent the last three years.  And I just have some chart, you know, this is a legacy system we have.  In 2012, we are already growing, I think, maybe expecting more. Sometimes it's very difficult to predict the growth and expressly, suddenly we may have a new provider, John, they suddenly want to be processing 10 year of data.  They can suddenly hand forward data we have. So it's just trying to scale our system, see if we can handle that kind of load.  There's a new system.  That is a bigger challenge I already mentioned about synchronize that, you know, between you see, it's very cumbersome and also because the ongoing updated data make it difficult to, you identify 160 million row of data, which one is the new one,  and also which one is different when we are trying to do synchronize, the script trying to identify, identify is like the hardest part to synchronize.  Once you find out which one you need to synchronize, it's easy.  That is, we're still working on trying to find a better way, maybe trying to see any audience can suggest a better way if you have different schemer how to do a synchronization.  You see this is the legacy schema, I'm not going to go for any detail, but just show you the new one will be simplified, but basically partition the data in different table and things like that.  And whenever the synchronization script is pretty much have to mapping the two different schemer and trying to find the right query to update the missing data in the new system.  Some of the parameters I just want to show you, this list is not showing the complete list of the configuration in the PostGrip config file.  I just want to put in the table, you know, side by side easier to identify, if it doesn't maybe helpful for the beginner, never use a replication, maybe helpful.  The certain parameter, you know, master and slave are different.  And probably you can read that, it's very straightforward.  And PGCon have a lot of pre-registered documents, have a lot of very detailed information.  And as you, as I pointed out before this, we make an adjustment because of the PGDOM thing, we put it minus that, what I trade off, the slave during the DOM will not be in the real time.  Which is, it doesn't matter for us, our case doesn't need to be 100% real time in our slave. We're using a slave pretty much for the backup purpose and also some long query, a select query doesn't need to update.  We're heating our slave to offloading the low from the master that help us to boot up our performance in the master.  And this is some other parameter we're using.  That's it for me and I'll turn it back to Marty.  I'm one of the developers that's been working on the rewrite of our processing platform and some of the decisions that were made in that rewrite affect the way that we are going to manage the data from here on out.  And so I wanted to go over some of those things.  The big difference in the new platform is that we're doing a lot of decentralized kind of components so that instead of having a more monolithic design, we broke it all out.  And these are web components like component X, Y, and Z here.  And where it was appropriate, we also splintered a little piece of the database off and integrated that in with its component application.  And these guys communicate with each other using REST.  So, and I'm not sure if everyone here knows, but wasn't quite sure what to expect in the audience is our first PGCon.  So I don't know how many people are developers, but REST is basically using standard HTTP request in order to interact with a web application.  And there's three parts or three things that I think are interesting to mention on how this new architecture affects the data management.  And one is concerns the web framework and one concerns the ORM that's integrated in.  And the other is the system of database patches that we've implemented for doing schema updates.  So I'll go over each one of these three.  The web framework is, of course, just something that helps to facilitate creating a web application.  In our case, we're using Pearl's Mojolicious framework.  And Mojolicious implements a model view controller paradigm where, of course, the view is what the user sees.  In this case, it's a web page.  The model is a state of the system or the database most often.  And the controller is kind of a mapping in between the interactions that the user sends in through the view to do something on the model.  And here at the bottom, you can see here's some example mappings of URLs against this is REST.  So we're using URLs for the users to do the interaction.  They're sending in some sort of HTTP request and that eventually has an effect on the database.  And so in this example, we've got a GET request which maps to a select statement on a person table.  And we have a POST request which would map to an update or an insert statement on the same table.  And a DELETE which would just map onto a DELETE.  And that's all pretty standard, I guess, for a web framework.  The thing that's a little interesting about our system is we've extended Mojolicious a bit so that when the web server starts up,  it automatically recognizes the database that it's coupled with so it'll find out if it has an associated database,  check the database for all the tables, and it'll generate all these URLs or routes that are used by a web application to do the basic CRUD operations.  So you don't have to manually define all these things.  The new architecture also has an integrated ORM.  And of course that's an Optic Relational Mapper, I'm sure everybody here knows that.  And similar to the web framework, we've done some things to help automate the operations that the ORM does.  So as the server starts up, the ORM kicks in and also reads the config file.  And we'll try to discover the database as well.  And the ORM will generate the object methods needed for those CRUD operations.  So we've basically got two levels of database operation mappings going on here.  So the web server starts up and the web framework is going to generate a lot of routes,  a lot of URL type routes that a user would use or the application that the user is using is going to use.  And these then make use of the object methods generated by the ORM which can then go about manipulating the database on the back end.  And so a lot of this happens just kind of magically.  Whenever you create a new application with this framework and you define your database,  all these methods and routes just get instantiated for you.  Now that, I'll say one thing, that can be a little expensive.  So if the database is any size at all, it's going through all these tables generating all the CRUD operations that would be needed to manipulate that.  But luckily, since this is a web application, it does it all when the server starts up, so we only pay the price one time.  And so far it hasn't been too much of a problem.  So since we're doing sort of distributed development, lots of little components that can be developed individually,  it was desirable to have a way that developers could create schema changes as they're working on the component.  Because remember these components now, these web applications, have the database tightly integrated in there,  their own PostgreSQL database in there.  So what we did was in addition to the code and the Git repository for the component,  we added the schema definition file in this base.sql file.  And then as you make, or as changes are made to the schema of the database, developers add patch files very much in the same way that you might add a patch to software code.  You add a DDL patch here.  And so new patches come in and they're committed to the repository.  Eventually you want to migrate these things out to a running instance of component X,  or more exactly you want to migrate them out to the database that's running within component X.  And the software is going to do a little bit of help in to maintaining the enforcing integrity on these patches to make sure things don't get crazy on that.  These slides got a little mangled, I'm sorry.  So let's imagine that you have a new patch that comes in here, so they've added patches.  And as a patch is added to the Git repository and tested during the process of testing it and committing it to the repository,  the schema definition file is rebuilt.  It's regenerated to incorporate all the patches that have come later.  And an entry is made in this text file called patchesapplied.txt, as sort of a bookkeeping method to keep track of all the patches that have gone into base.sequel.  Now, when we're ready to apply new patches over to a running instance, there's an analogous table.  You can't hardly see it here, but it's patches applied.  It's just like the text file in the Git repository, but this is a regular PostgreSQL table that's keeping track of all the patches that have been applied to this instance of the database.  So one thing that the patch software will do for us is take the schema of the running database, check and see which patches,  it can compare the patchesapplied.txt file and the patchesapplied table to see which new patches would need to be added to this database.  And it can do a dump of these things, sort of a hypothetical dump that you would get if you took the existing schema and applied all these new patches.  And if you compare that to base.sequel, you should get the same thing, assuming nobody's done any unauthorized changes to your database.  So it's a way of checking and making sure nobody's been mucking around when they should go through the patch mechanism and do it the way we've decided to do this.  Not everybody likes that, by the way.  How does this instance of the patch is like the data that goes out to the database?  Yeah, so the procedure would be something like this. You check out your GET repository and all the patches are in there.  They've all been previously committed.  But this patch software is built on Perl's module build.  It's an extension of this module.  And module build is a software for managing the installation of modules normally.  And in this case, we've just extended that to manage the installation of the patches.  Is that answer your question?  And in addition to using module build, we're using RoseDB as the object relational mapper.  And as I said, Mojolicious is a rather new Perl framework that has gotten fairly popular in the last year.  Okay, and I've also been working with Alex on some sort of monitoring tool for our databases.  And as he said, we've got around nine or 10 clusters.  There's several principle clusters that we have that we were really concerned with something happen so.  But we've got a number of others.  And what we'd like is kind of a simple tool that provides something like a weather forecast of how the clusters are doing.  And so I created a little web app using Mojolicious.  I've been doing this development work already on that that would read a config file of a JSON formatted config file.  And then throw this stuff up on a screen, in a browser, actually.  And it just gives an icon for each one of the clusters that are color coded to give an idea of how the thing is doing.  And it's just a prototype right now.  But we've got it reading the config file and throwing the stuff up on the screen.  And then we've got a JavaScript event loop that will also create the SSH tunnels out to these databases  and then start doing various monitoring operations.  And that's the part where I'd love to have some feedback from people because I'm not sure exactly what's the best things to be checking.  I know there's various units, commands.  You can check some stat tables.  If one of these guys go red or yellow, then you may want to click on it.  In this case, I clicked on a green one and then it would give you more information.  Again, this is a locally installed web application.  These web applications you could create with Mojolicious are very light.  So you could just check them out if you get repository and you can install it on your workstation in five minutes.  And you don't have to bother the essays with root access or anything.  And that's what we wanted, something simple to monitor on a databases.  So any suggestions, I'd love to hear them.  And I guess that brings us to the questions.  I don't have it posted anywhere, but I have it on my laptop and I'll be glad to share it.  This is something I wrote my own time too.  So some of the stuff we couldn't give out, like the module build patch stuff is kind of company stuff.  But the monitoring tool has been my own little project.  Mojolicious is a convenient one?  Is it something you're currently working on?  They're kind of like cluster-ish because we have clusters of databases that process things.  And the guy who started Clustericious tried to release some other software and we just have a devilishly hard time of it.  Because we're actually contractors, we're working for the government,  and they just put a mountain of red tape into trying to open source anything.  So we're running a little bit of a bind on what we can release.  Anything? Yeah.  How do you store your measurement data in your R-class and how are they connected to the metadata?  Good question. Maybe we can go back.  And Alex may want to jump in on this.  I think he mentioned this back in...  Are you talking about data, real data from the instrument and the calm down with the image or all that?  Exactly, he's raw data.  We got hundreds of notes for serving in different data centers and we have a long history of the data.  And those real data never moved to database.  We're not just holding the metadata which is describing when the new raw data we store, the location,  and when we take applied with the algorithm processing to what level, and how we classify what DSDP.  We are moving by so-called archive set.  All this information is a metadata which is in the database, a searchable or application point to that,  and how we get access to delivered data and also our database tracking all the ordering status  and all the processing even planning when we process all the information on the data.  We have never been a software.  This is a huge amount of data which is stored in the archive.  How is this thought?  I can answer that.  Do you set up a connection to your database?  Well, we have another piece of software that we wrote for this.  It doesn't use a relational database.  And what it does, as you said, there's millions of policy that are constantly coming in from the satellite because it never rests.  And we do an MD5 sum on those and store them in a distributed file archive server that can span across multiple servers.  But the location is determined by the MD5 sum on the contents of the file.  So you can easily just generate the address on the fly like that.  Do you ever use any standard software like HDL5 or something like this?  Something self-written.  It's self-written, but it's not.  I mean, we're still using a lot of Unix-type stuff.  I mean, just do the MD5 sum and store it in a directory.  And the directory structure is based upon that MD5 digest.  So it's generated by that.  What you get back from that?  It's not too complicated, really.  The problem is because there's so many of them, as you said.  And sometimes you have to rebalance the servers so that one server doesn't have too many.  We did have this in a database.  At one time, we were storing the locations in a database, but we've always had them just as files.  So the web components mostly work with atmospheric scientists who process the data after it comes in.  I think this is talking about the front end and back end. How the data is generated is the internal, pro kind of environment.  You're talking about front end.  The important front end is how do people know what stuff you have.  They have a fix, a lot of applications, even other agencies, other scientists.  They have their own project.  They can write applications, search engines, you know, based on...  As you see, I have previous slide, data receiver.  They can actually not only go down over a file, that kind of user.  You can develop an app, you know, target certain ozone data and things like that.  And you can automatically get the data when the new data come in.  Pretty much we allow them to search, you know, order, staging it.  They can write applications.  How the same goes, how to file, they can see through the application, that's how another way to deliver the data to.  So they have different kind of channels, if you put the data.  The file system, your story and the architecture is...  Exactly.  Exactly.  Yeah, for storing files, that is the heart and soul.  And there's a kind of a division of labor here that's created a little friction recently.  So our team wrote the software, the stuff that does the MD5SUM and stores the files.  But these SAs over at NASA are managing the hardware back there.  And they can implement any kind of RAID array or whatever disk system they want.  And recently there was a problem, like files weren't going in fast enough.  There was some delays and there was a little bit of finger pointing.  It didn't do this.  Do you guys wrote this crazy software?  But everything that you looked at, you tend to think that it was something wrong with the disk.  It got resolved, but I don't know much about the hardware implementation.  And it could change without R control.  So many terabytes?  Oh, many terabytes.  No, it's... well, let's see.  2004 to now for Omi.  So they spent about eight years worth of data.  And they were all in the solution because you know there are many storage systems that are exposed  with the results of the solution and the sense of connectivity.  Yeah.  You know what your old solution was.  You know, the guy I worked closely with wrote this solution.  And I think he thought it would be kind of neat to write it himself in a way.  But we already had something in place and it was quite right.  And not everybody agrees that we needed to write something ourselves, but we did.  And there was some clever stuff you put into it.  But as I mentioned, this has caused a little bit of friction.  Do you have an application?  I'm sorry.  Do you have an application?  An application?  An application.  Replication.  Yeah, yeah, yeah.  In a serious line, we have a representation reducing the seamless mass and the state  by the representation.  But this is for the metadata.  Okay, thank you all very much.  I'm sorry.  You're talking about the metadata.  Yeah, so far, one talk about around 25 feet, one database.  We have two databases.  We're talking about close to about 50 feet.  So I'm not sure that that's a good way to measure how big the data is.  Okay, thanks.
Monitoring_Ozone_Levels_with_Postgresql_0_131.png Monitoring_Ozone_Levels_with_Postgresql_132_611.png Monitoring_Ozone_Levels_with_Postgresql_612_1235.png Monitoring_Ozone_Levels_with_Postgresql_1236_2243.png Monitoring_Ozone_Levels_with_Postgresql_2244_2747.png Monitoring_Ozone_Levels_with_Postgresql_2748_3215.png Monitoring_Ozone_Levels_with_Postgresql_3216_4367.png Monitoring_Ozone_Levels_with_Postgresql_4368_4931.png Monitoring_Ozone_Levels_with_Postgresql_4932_5603.png Monitoring_Ozone_Levels_with_Postgresql_5604_8087.png Monitoring_Ozone_Levels_with_Postgresql_8088_13871.png Monitoring_Ozone_Levels_with_Postgresql_13872_14003.png Monitoring_Ozone_Levels_with_Postgresql_14004_15395.png Monitoring_Ozone_Levels_with_Postgresql_15396_15467.png Monitoring_Ozone_Levels_with_Postgresql_15468_17867.png Monitoring_Ozone_Levels_with_Postgresql_17868_20507.png Monitoring_Ozone_Levels_with_Postgresql_20508_20567.png Monitoring_Ozone_Levels_with_Postgresql_20568_20627.png Monitoring_Ozone_Levels_with_Postgresql_20628_20903.png Monitoring_Ozone_Levels_with_Postgresql_20904_21011.png Monitoring_Ozone_Levels_with_Postgresql_21012_21287.png Monitoring_Ozone_Levels_with_Postgresql_21288_21455.png Monitoring_Ozone_Levels_with_Postgresql_21456_21611.png Monitoring_Ozone_Levels_with_Postgresql_21612_21923.png Monitoring_Ozone_Levels_with_Postgresql_21924_22811.png Monitoring_Ozone_Levels_with_Postgresql_22812_22883.png Monitoring_Ozone_Levels_with_Postgresql_22884_23867.png Monitoring_Ozone_Levels_with_Postgresql_23868_24839.png Monitoring_Ozone_Levels_with_Postgresql_24840_24911.png Monitoring_Ozone_Levels_with_Postgresql_24912_24959.png Monitoring_Ozone_Levels_with_Postgresql_24960_26675.png Monitoring_Ozone_Levels_with_Postgresql_26676_27659.png Monitoring_Ozone_Levels_with_Postgresql_27660_27755.png Monitoring_Ozone_Levels_with_Postgresql_27756_29255.png Monitoring_Ozone_Levels_with_Postgresql_29256_29471.png Monitoring_Ozone_Levels_with_Postgresql_29472_30143.png Monitoring_Ozone_Levels_with_Postgresql_30144_31151.png Monitoring_Ozone_Levels_with_Postgresql_31152_32387.png Monitoring_Ozone_Levels_with_Postgresql_32388_32579.png Monitoring_Ozone_Levels_with_Postgresql_32580_32951.png Monitoring_Ozone_Levels_with_Postgresql_32952_34775.png Monitoring_Ozone_Levels_with_Postgresql_34776_35339.png Monitoring_Ozone_Levels_with_Postgresql_35340_38459.png Monitoring_Ozone_Levels_with_Postgresql_38460_39407.png Monitoring_Ozone_Levels_with_Postgresql_39408_39527.png Monitoring_Ozone_Levels_with_Postgresql_39528_39887.png Monitoring_Ozone_Levels_with_Postgresql_39888_40127.png Monitoring_Ozone_Levels_with_Postgresql_40128_40607.png Monitoring_Ozone_Levels_with_Postgresql_40608_40811.png Monitoring_Ozone_Levels_with_Postgresql_40812_41387.png Monitoring_Ozone_Levels_with_Postgresql_41388_42251.png Monitoring_Ozone_Levels_with_Postgresql_42252_42383.png Monitoring_Ozone_Levels_with_Postgresql_42384_43007.png Monitoring_Ozone_Levels_with_Postgresql_43008_43415.png Monitoring_Ozone_Levels_with_Postgresql_43416_47651.png Monitoring_Ozone_Levels_with_Postgresql_47652_48803.png Monitoring_Ozone_Levels_with_Postgresql_48804_51347.png Monitoring_Ozone_Levels_with_Postgresql_51348_52115.png Monitoring_Ozone_Levels_with_Postgresql_52116_54227.png Monitoring_Ozone_Levels_with_Postgresql_54228_54311.png Monitoring_Ozone_Levels_with_Postgresql_54312_54359.png Monitoring_Ozone_Levels_with_Postgresql_54360_54407.png Monitoring_Ozone_Levels_with_Postgresql_54408_54455.png Monitoring_Ozone_Levels_with_Postgresql_54456_54503.png Monitoring_Ozone_Levels_with_Postgresql_54504_69071.png
And then this is Alex Lai here in the front row. Alex is the DBA and I'm the developer and assistant DBA on the only science support team at Goddard Space Flight Center. And we're going to talk about monitoring ozone levels with Postgres SQL. So I'm going to begin by giving just a little bit of background and Alex will talk about how data is brought into the system during a process we call ingestion. Alex is going to go over some of the challenges that we faced and then I'll describe some ongoing development in the software platform that affects how we manage the data. So about 450 miles above the Earth's surface is a formation of satellites that are in a polar orbit that takes them across the equator 14 times each day. And they call that formation the A train. And all of the members in the A train are Earth observing satellites so they've got their instruments pointed downward instead of outward. The caboose in this A train you can probably just barely see here is a satellite called Aura. Launched in July 2004, Aura has four different instruments for monitoring Earth's atmosphere. The ozone monitoring instrument is an instrument for monitoring stratospheric ozone levels and that's our instrument, the one that provides us data for our system. Because these guys are in a polar orbit and they go over the North Pole each time they go around the globe, the data is received at a far north ground station like this one in Svalbard, Norway. The data is processed a little bit at the ground station and converted into something called an Earth science data type or ESTT. This is not really the sort of data type that you would normally encounter in a database. It's really more like a procedure for processing a set of measurements. And they're defined by Earth scientists, which I'm not an Earth scientist, but they provide sort of the real interface between the data coming in from the satellite and our data management system. And right now there's more than 900 that have been defined that we have to store in the database. So these ESTTs get processed and are generated and the ultimate goal is that the atmospheric scientists will use them to use the results of all this processing to create atmospheric profiles and climate change models. So Alex is now going to tell you a little bit about the process of how the data gets in and processes. Good morning. Can you hear me okay? Here is, I want to show this diagram to have a high level of the system on the processing system. As you see in the box, you see the data provider, peerless slide showing the ground system, capture all the data coming down, and we'll either send raw data to us or they do some processing, certain level of data, and those are the provider of the data. And then we'll get into our input to our data processing system. And also we have a large group of scientists community and rely on output data to fit into the application and doing research. Those scientists will go through the web interface to making requests and the data gets delivered to the data receiver. And our OME project operator also can go through our web interface to monitoring our processing and also submitting jobs through the planner and see what kind of data need to be. Sometimes we need to reprocess data or any new product coming in or some sender changing the logic to how we manipulate the input data and come out with the final product and fill the web interface. This diagram is showing, as you know, that the data basically have two ways to store the data. The real data will not store in the database. Like the image or all the instrument capture data will be stored in the archive system. The data is a very high volume. And the other type of data we will have to process and they will store in the metadata server. That's how the post-grip capture tracking all the metadata in our system. The web two-way will trigger the ingest process of year-through-the-auto ingest which is relying on the rifle of the new data. We have a logic to checking, demon checking, any incoming file stored in our archive node. And then that will trigger the ingest process and based on certain criteria was set to doing the process of those data. And also the OME operator also can submit any job and controlling the ingest. We can override and seeing the planner and seeing the load and also we will be able to also submit a job that way. And those are what we need to explain a little bit more about our ongoing situation. We're using post-grip almost over 10 years to tracking all the metadata in our legacy system. Back in a few years ago we realized that the data schema needs to be reduced. So we are starting another new development server which you see in the lower corner. The MDX is a new system, ongoing development system. And basically that system already partially in operation. Some application already pointing to that. And the idea is we need to have the metadata update on both systems. But basically the priority is make sure that legacy system is ongoing without losing any data. So we're based on that once we update the metadata, legacy metadata and then we will be, another trigger will go into the MDX metadata server. Otherwise we do, from time to time we do have a little bit problem seeing that are there any reason that we didn't successfully have 100% all the data get into MDX metadata. So that's why you see a box on the Synconi database process, we're trying to catching any missing, any problem, file having successfully ingested in both systems. Yes. Can you repeat it again? So the data is coming through in this process and you're also syncing the database process from legacy system? No, there are two databases that is two different databases. It's not the same database. The scheme is a little different than the two. Yeah, I'm going to show you more in the following slide. There's one of the challenges we are facing, trying to updating the basically the same kind of metadata but in different format of the schema. They are totally different. We have different petition, all this have different. So I'm going to show you later. In the thing I want to talk about beside the synchronization, this is one of the backup as thing in our replication. Right now, this strategy we are using. I want to talk about the history about before we running NIO. We were running it. We still having no replication that time. We are pretty much based on the PGDOM to do the backup. I've seen our data getting very big and take time to doing the DUM. And also the performance will drop while doing that. At that time, we only have one server. And also after the DUM and the data ongoing update and between the first DUM and the second DUM, that window, we are pretty much the database crash and we are losing those data. And the replication NIO really help us to solving this problem. We still continue to do the DUM but less frequent DUM. And also we dump from the slave and it won't causing a performance in the master. I just want to mention some of the experience on the thing we are focused on. How to make sure the replication successfully. This is a kind of area that possibility of the warfire did not apply properly and then can cause the replication fail. So between the master and slave sometimes the network is unavailable for a period of time. We have to get notice about that. And also possibility SA may be deleting some warfire didn't aware of. And you see in the previous slide we have a PG DUM going on. Doses, a copy can take a longer period of time to finish. While the replication going on, we are dumping from the slave and they have some coordination need to be done. I'm going to show you some parameter. We are just to avoid the fail, complete the PG DUM while the replication going on. Actually this parameter showing is a max standby stream line delay. We set it to one to make sure replication in the waste day until the PG DUM finish. That avoiding the crash, crashing the PG DUM. We have some technique to, based on the PG PS operating system to showing the status and the house. And replication going on, we are kind of monitoring those status and if they have too long period of wait then we notice something is going on and the performer may have a little bit problem. So we are trying to have some tool automated tools based on those information to alert the SA and DBA. We are also using the log and capture some information there in case we need to troubleshooting the period of time, the performance issue. Also in PostScript, building a table, system table, you have those information. Also we can use those query to create how the log apply and if they are not move on certain period of time we also get alert and things like that. That's very helpful query table, system table will help us to monitor. And some kind of thing we also, a rule of that make sure our warfowl is in archive in case we need to rebuild or restore. And also make sure we don't patching the warfowl before actually the slay applied it. And we pay extra attention to those displays and also to monitoring the wait stay, how long the sing and also monitoring any dialogue situation happen. And that's just an email we've been using to alert. We haven't have any pager and we try to not work 24 hours. So we don't implement that. And because of our data, getting all the data, input data, we are in an archive server, the worst scenario we can always go back and repost the single data can be reproduced. This is just some notes about the performance issue. How much data we have and currently we have about 10 clusters of the server in different stages of the development. We have the development server and we have test server, we have operation server. And on average, it can be 200 come each per second. Sometimes it can heat up to 10,000. It depends on the workload. This is an average. And also accounting is very difficult to measure. They don't have really the standard we measure how big your database is and this is how I, you know, some indicator how big, I just want to show you the one of the biggest table. We have a 160 million row and the growing rate is about 40, 60 percent the last three years. And I just have some chart, you know, this is a legacy system we have. In 2012, we are already growing, I think, maybe expecting more. Sometimes it's very difficult to predict the growth and expressly, suddenly we may have a new provider, John, they suddenly want to be processing 10 year of data. They can suddenly hand forward data we have. So it's just trying to scale our system, see if we can handle that kind of load. There's a new system. That is a bigger challenge I already mentioned about synchronize that, you know, between you see, it's very cumbersome and also because the ongoing updated data make it difficult to, you identify 160 million row of data, which one is the new one, and also which one is different when we are trying to do synchronize, the script trying to identify, identify is like the hardest part to synchronize. Once you find out which one you need to synchronize, it's easy. That is, we're still working on trying to find a better way, maybe trying to see any audience can suggest a better way if you have different schemer how to do a synchronization. You see this is the legacy schema, I'm not going to go for any detail, but just show you the new one will be simplified, but basically partition the data in different table and things like that. And whenever the synchronization script is pretty much have to mapping the two different schemer and trying to find the right query to update the missing data in the new system. Some of the parameters I just want to show you, this list is not showing the complete list of the configuration in the PostGrip config file. I just want to put in the table, you know, side by side easier to identify, if it doesn't maybe helpful for the beginner, never use a replication, maybe helpful. The certain parameter, you know, master and slave are different. And probably you can read that, it's very straightforward. And PGCon have a lot of pre-registered documents, have a lot of very detailed information. And as you, as I pointed out before this, we make an adjustment because of the PGDOM thing, we put it minus that, what I trade off, the slave during the DOM will not be in the real time. Which is, it doesn't matter for us, our case doesn't need to be 100% real time in our slave. We're using a slave pretty much for the backup purpose and also some long query, a select query doesn't need to update. We're heating our slave to offloading the low from the master that help us to boot up our performance in the master. And this is some other parameter we're using. That's it for me and I'll turn it back to Marty. I'm one of the developers that's been working on the rewrite of our processing platform and some of the decisions that were made in that rewrite affect the way that we are going to manage the data from here on out. And so I wanted to go over some of those things. The big difference in the new platform is that we're doing a lot of decentralized kind of components so that instead of having a more monolithic design, we broke it all out. And these are web components like component X, Y, and Z here. And where it was appropriate, we also splintered a little piece of the database off and integrated that in with its component application. And these guys communicate with each other using REST. So, and I'm not sure if everyone here knows, but wasn't quite sure what to expect in the audience is our first PGCon. So I don't know how many people are developers, but REST is basically using standard HTTP request in order to interact with a web application. And there's three parts or three things that I think are interesting to mention on how this new architecture affects the data management. And one is concerns the web framework and one concerns the ORM that's integrated in. And the other is the system of database patches that we've implemented for doing schema updates. So I'll go over each one of these three. The web framework is, of course, just something that helps to facilitate creating a web application. In our case, we're using Pearl's Mojolicious framework. And Mojolicious implements a model view controller paradigm where, of course, the view is what the user sees. In this case, it's a web page. The model is a state of the system or the database most often. And the controller is kind of a mapping in between the interactions that the user sends in through the view to do something on the model. And here at the bottom, you can see here's some example mappings of URLs against this is REST. So we're using URLs for the users to do the interaction. They're sending in some sort of HTTP request and that eventually has an effect on the database. And so in this example, we've got a GET request which maps to a select statement on a person table. And we have a POST request which would map to an update or an insert statement on the same table. And a DELETE which would just map onto a DELETE. And that's all pretty standard, I guess, for a web framework. The thing that's a little interesting about our system is we've extended Mojolicious a bit so that when the web server starts up, it automatically recognizes the database that it's coupled with so it'll find out if it has an associated database, check the database for all the tables, and it'll generate all these URLs or routes that are used by a web application to do the basic CRUD operations. So you don't have to manually define all these things. The new architecture also has an integrated ORM. And of course that's an Optic Relational Mapper, I'm sure everybody here knows that. And similar to the web framework, we've done some things to help automate the operations that the ORM does. So as the server starts up, the ORM kicks in and also reads the config file. And we'll try to discover the database as well. And the ORM will generate the object methods needed for those CRUD operations. So we've basically got two levels of database operation mappings going on here. So the web server starts up and the web framework is going to generate a lot of routes, a lot of URL type routes that a user would use or the application that the user is using is going to use. And these then make use of the object methods generated by the ORM which can then go about manipulating the database on the back end. And so a lot of this happens just kind of magically. Whenever you create a new application with this framework and you define your database, all these methods and routes just get instantiated for you. Now that, I'll say one thing, that can be a little expensive. So if the database is any size at all, it's going through all these tables generating all the CRUD operations that would be needed to manipulate that. But luckily, since this is a web application, it does it all when the server starts up, so we only pay the price one time. And so far it hasn't been too much of a problem. So since we're doing sort of distributed development, lots of little components that can be developed individually, it was desirable to have a way that developers could create schema changes as they're working on the component. Because remember these components now, these web applications, have the database tightly integrated in there, their own PostgreSQL database in there. So what we did was in addition to the code and the Git repository for the component, we added the schema definition file in this base.sql file. And then as you make, or as changes are made to the schema of the database, developers add patch files very much in the same way that you might add a patch to software code. You add a DDL patch here. And so new patches come in and they're committed to the repository. Eventually you want to migrate these things out to a running instance of component X, or more exactly you want to migrate them out to the database that's running within component X. And the software is going to do a little bit of help in to maintaining the enforcing integrity on these patches to make sure things don't get crazy on that. These slides got a little mangled, I'm sorry. So let's imagine that you have a new patch that comes in here, so they've added patches. And as a patch is added to the Git repository and tested during the process of testing it and committing it to the repository, the schema definition file is rebuilt. It's regenerated to incorporate all the patches that have come later. And an entry is made in this text file called patchesapplied.txt, as sort of a bookkeeping method to keep track of all the patches that have gone into base.sequel. Now, when we're ready to apply new patches over to a running instance, there's an analogous table. You can't hardly see it here, but it's patches applied. It's just like the text file in the Git repository, but this is a regular PostgreSQL table that's keeping track of all the patches that have been applied to this instance of the database. So one thing that the patch software will do for us is take the schema of the running database, check and see which patches, it can compare the patchesapplied.txt file and the patchesapplied table to see which new patches would need to be added to this database. And it can do a dump of these things, sort of a hypothetical dump that you would get if you took the existing schema and applied all these new patches. And if you compare that to base.sequel, you should get the same thing, assuming nobody's done any unauthorized changes to your database. So it's a way of checking and making sure nobody's been mucking around when they should go through the patch mechanism and do it the way we've decided to do this. Not everybody likes that, by the way. How does this instance of the patch is like the data that goes out to the database? Yeah, so the procedure would be something like this. You check out your GET repository and all the patches are in there. They've all been previously committed. But this patch software is built on Perl's module build. It's an extension of this module. And module build is a software for managing the installation of modules normally. And in this case, we've just extended that to manage the installation of the patches. Is that answer your question? And in addition to using module build, we're using RoseDB as the object relational mapper. And as I said, Mojolicious is a rather new Perl framework that has gotten fairly popular in the last year. Okay, and I've also been working with Alex on some sort of monitoring tool for our databases. And as he said, we've got around nine or 10 clusters. There's several principle clusters that we have that we were really concerned with something happen so. But we've got a number of others. And what we'd like is kind of a simple tool that provides something like a weather forecast of how the clusters are doing. And so I created a little web app using Mojolicious. I've been doing this development work already on that that would read a config file of a JSON formatted config file. And then throw this stuff up on a screen, in a browser, actually. And it just gives an icon for each one of the clusters that are color coded to give an idea of how the thing is doing. And it's just a prototype right now. But we've got it reading the config file and throwing the stuff up on the screen. And then we've got a JavaScript event loop that will also create the SSH tunnels out to these databases and then start doing various monitoring operations. And that's the part where I'd love to have some feedback from people because I'm not sure exactly what's the best things to be checking. I know there's various units, commands. You can check some stat tables. If one of these guys go red or yellow, then you may want to click on it. In this case, I clicked on a green one and then it would give you more information. Again, this is a locally installed web application. These web applications you could create with Mojolicious are very light. So you could just check them out if you get repository and you can install it on your workstation in five minutes. And you don't have to bother the essays with root access or anything. And that's what we wanted, something simple to monitor on a databases. So any suggestions, I'd love to hear them. And I guess that brings us to the questions. I don't have it posted anywhere, but I have it on my laptop and I'll be glad to share it. This is something I wrote my own time too. So some of the stuff we couldn't give out, like the module build patch stuff is kind of company stuff. But the monitoring tool has been my own little project. Mojolicious is a convenient one? Is it something you're currently working on? They're kind of like cluster-ish because we have clusters of databases that process things. And the guy who started Clustericious tried to release some other software and we just have a devilishly hard time of it. Because we're actually contractors, we're working for the government, and they just put a mountain of red tape into trying to open source anything. So we're running a little bit of a bind on what we can release. Anything? Yeah. How do you store your measurement data in your R-class and how are they connected to the metadata? Good question. Maybe we can go back. And Alex may want to jump in on this. I think he mentioned this back in... Are you talking about data, real data from the instrument and the calm down with the image or all that? Exactly, he's raw data. We got hundreds of notes for serving in different data centers and we have a long history of the data. And those real data never moved to database. We're not just holding the metadata which is describing when the new raw data we store, the location, and when we take applied with the algorithm processing to what level, and how we classify what DSDP. We are moving by so-called archive set. All this information is a metadata which is in the database, a searchable or application point to that, and how we get access to delivered data and also our database tracking all the ordering status and all the processing even planning when we process all the information on the data. We have never been a software. This is a huge amount of data which is stored in the archive. How is this thought? I can answer that. Do you set up a connection to your database? Well, we have another piece of software that we wrote for this. It doesn't use a relational database. And what it does, as you said, there's millions of policy that are constantly coming in from the satellite because it never rests. And we do an MD5 sum on those and store them in a distributed file archive server that can span across multiple servers. But the location is determined by the MD5 sum on the contents of the file. So you can easily just generate the address on the fly like that. Do you ever use any standard software like HDL5 or something like this? Something self-written. It's self-written, but it's not. I mean, we're still using a lot of Unix-type stuff. I mean, just do the MD5 sum and store it in a directory. And the directory structure is based upon that MD5 digest. So it's generated by that. What you get back from that? It's not too complicated, really. The problem is because there's so many of them, as you said. And sometimes you have to rebalance the servers so that one server doesn't have too many. We did have this in a database. At one time, we were storing the locations in a database, but we've always had them just as files. So the web components mostly work with atmospheric scientists who process the data after it comes in. I think this is talking about the front end and back end. How the data is generated is the internal, pro kind of environment. You're talking about front end. The important front end is how do people know what stuff you have. They have a fix, a lot of applications, even other agencies, other scientists. They have their own project. They can write applications, search engines, you know, based on... As you see, I have previous slide, data receiver. They can actually not only go down over a file, that kind of user. You can develop an app, you know, target certain ozone data and things like that. And you can automatically get the data when the new data come in. Pretty much we allow them to search, you know, order, staging it. They can write applications. How the same goes, how to file, they can see through the application, that's how another way to deliver the data to. So they have different kind of channels, if you put the data. The file system, your story and the architecture is... Exactly. Exactly. Yeah, for storing files, that is the heart and soul. And there's a kind of a division of labor here that's created a little friction recently. So our team wrote the software, the stuff that does the MD5SUM and stores the files. But these SAs over at NASA are managing the hardware back there. And they can implement any kind of RAID array or whatever disk system they want. And recently there was a problem, like files weren't going in fast enough. There was some delays and there was a little bit of finger pointing. It didn't do this. Do you guys wrote this crazy software? But everything that you looked at, you tend to think that it was something wrong with the disk. It got resolved, but I don't know much about the hardware implementation. And it could change without R control. So many terabytes? Oh, many terabytes. No, it's... well, let's see. 2004 to now for Omi. So they spent about eight years worth of data. And they were all in the solution because you know there are many storage systems that are exposed with the results of the solution and the sense of connectivity. Yeah. You know what your old solution was. You know, the guy I worked closely with wrote this solution. And I think he thought it would be kind of neat to write it himself in a way. But we already had something in place and it was quite right. And not everybody agrees that we needed to write something ourselves, but we did. And there was some clever stuff you put into it. But as I mentioned, this has caused a little bit of friction. Do you have an application? I'm sorry. Do you have an application? An application? An application. Replication. Yeah, yeah, yeah. In a serious line, we have a representation reducing the seamless mass and the state by the representation. But this is for the metadata. Okay, thank you all very much. I'm sorry. You're talking about the metadata. Yeah, so far, one talk about around 25 feet, one database. We have two databases. We're talking about close to about 50 feet. So I'm not sure that that's a good way to measure how big the data is. Okay, thanks.
Goddard Space Flight Center. And we're going to talk about monitoring ozone levels with Postgres how data is brought into the system during a over some of the challenges that we faced and then I'll describe some ongoing development in surface is a formation of satellites that are in the A train. And all of the members in the A train are Earth observing satellites so they've different instruments for monitoring Earth's atmosphere. The ozone monitoring instrument is an the data is received at a far north ground station and converted into something called an Earth science data type or ESTT. This is not they provide sort of the real interface between the data coming in from the satellite and our data management system. And right now there's more than 900 that have been defined that we have is that the atmospheric scientists will use them our input to our data processing system. And also we have a large group of scientists community and through the web interface to making requests and load and also we will be able to also submit a one of the backup as thing in our replication. and between the first DUM and the second DUM, properly and then can cause the replication fail. network is unavailable for a period of time. We table, system table, you have those information. Also we can use those query to create how the log data we have and currently we have about 10 And on average, it can be 200 come each per million row and the growing rate is about 40, 60 suddenly want to be processing 10 year of data. can handle that kind of load. There's a new difficult to, you identify 160 million row of mapping the two different schemer and trying to platform and some of the decisions that were made to manage the data from here on out. And so I difference in the new platform is that we're that in with its component application. And these with a web application. And there's three parts a model view controller paradigm where, of all these URLs or routes that are used by a web things. The new architecture also has an of the object methods generated by the ORM which all these tables generating all the CRUD changes are made to the schema of the database, developers add patch files very much in the same to a running instance of component X, or more as a patch is added to the Git repository and tested during the process of testing it and method to keep track of all the patches that have apply new patches over to a running instance, patch software is built on Perl's module build. addition to using module build, we're using RoseDB as the object relational mapper. And as I clusters. There's several principle clusters that simple to monitor on a databases. So any on the data. We have never been a software. This components mostly work with atmospheric scientists who process the data after it comes data and things like that. And you can