 Hello, everybody, and thank you for joining us today for the virtual Vertica BDC 2020. Today's breakout session is entitled, I Deep Dive in the Vertica Management Console Enhancements and Roadmap. I'm Jeff Healy, and with Vertica Marketing, I'll be your host for this breakout session. Joining me are Bobak Gandhi and Natalia Stavisky from Vertica Engineering. But before we begin, I encourage you to submit questions or comments during the virtual session. You don't have to wait. Just type your question or comment in the question box below the slides or click submit. It'll be a Q&A session at the end of the presentation. We'll answer as many questions as we're able to during that time. Any questions we don't address, we'll do our best to answer them offline. Alternatively, visit VerticaForums.Form.Vertica.com, post your questions there after the session. Our engineering team is planning to join the forums to keep the conversation going well after the event. Also, a reminder that you can nationalize your screen by clicking the double arrow button in the lower right corner of the slides. And yes, this virtual session is being recorded and will be available to view on-demand this week. We'll send you a notification as soon as it's ready. Now let's get started. Over to you, Bobak. All right. So, hello and welcome everybody during this presentation of deep dive into the Vertica Management Console, Enhancements and Boardmap, Myself, Bobak and my team member, Natalia Stavisky. We'll go over a few useful enhancements on Vertica Management Console discussing a few real scenarios. All right. So, today we will go forward with a brief introduction about the Management Console. Then we will discuss the benefits of using Management Console by going over a couple of user scenarios for taking, for the queries taking too long to run and receiving email alerts from Management Console. Then we will go over a few MC features for Vertica eOnMode databases like provisioning and reviving the eOnMode databases from MC. Using the subcluster and understanding the depot. Then we will go over some of the future enhancements on MC that we are planning. All right. So, let's get started. All right. So do you want to know about how to provision a new Vertica cluster from MC? How do you analyze and understand the database workload by monitoring the queries on the database? How do you balance the resource pools and use alerts and thresholds on MC? So the Management Console is basically your answer and we'll talk about its capabilities and new enhancements in this presentation. So just to give a brief overview of the Management Console, who uses Management Console? It's generally used by IT administrators and DB admins. Management Console can be used to monitor both eOnMode and Enterprise Mode databases. Why to use Management Console? You can use the Management Console for provisioning Vertica databases and cluster. You can manage the already existing Vertica databases and cluster you have and you can use various tools on Management Console like query execution, database designer, workload analyzer and set up alerts and thresholds to get notified by some of the some of the fair activities on the MC. So let's go over a few benefits of using Management Console. So using Management Console you can view and optimize the resource pool usage. Management Console helps you to identify some critical conditions on your Vertica cluster. Additionally you can set up various thresholds on MC and get alerted if those thresholds are triggered on the database. So now let's dig into the couple of scenarios. So for the first scenario we will discuss about queries taking too long and using workload analyzer to possibly help to solve the problem. In the second scenario we will go over an alert email that you received via Management Console and analyzing the problems and taking the required actions to solve the problem. So let's go over the scenario where queries are taking too long to run. So in this example we have this one query that we are running using the query execution on MC and for some reason we notice that it's taking about 14.8 seconds to execute this query which is higher than the expected runtime of the query. The query that we are running happens to be the query used by MC during the excellent monitoring. Notice that the table name and the schema name which is DC request issued and this schema used for excellent monitoring. Now in 10.0 MC we have redesigned the workload analyzer and recommendations feature to show the recommendations and allow you to execute those recommendations. In our example we have taken the table name and filtered the tuning descriptions to see if there are any tuning recommendations related to this table. As we see over here there are three tuning recommendations available for that table. So now in 10.0 MC you can select those recommendations and then run them. So let's run the recommendations. So once the recommendations are run successfully you can go and see all the process recommendations that you have run previously. Over here we see that there are three recommendations that we had selected earlier have successfully processed. Now we take the same query and run it on the query execution on MC and hey it's running really faster and we see it takes only 0.3 seconds to run the query and which is about like 98% decrease in original runtime of the query. So in this example we saw that using a workload analyzer tool on MC you can possibly triage and solve the issue for your queries which are taking too long to execute. So now let's go over another use case scenario where DB admins received some alert email messages from MC and would like to understand and analyze the problem. So to know more about what's going on on the database and proactively react to the problems DB admins using the management console can create set of thresholds and get alerted about the conditions on the database if the threshold values is reached and then respond to the problem thereafter. Now as a DB admin I see some email message notifications from MC and upon checking the emails I see that there are a couple of email alerts received from MC on my email. So one of the messages that I received was for query resource rejections greater than 5 for Mithful 7 and then around the same time I received another email from the MC for the failed queries greater than 5 and in this case I see there are 80 failed queries. So now let's go on the MC and investigate the problem. So before going into the deep investigation about failures let's review the threshold settings on MC. So as we see we have set up the thresholds under the database settings page for failed queries in last 10 minutes greater than 5 and MC should send an email to the individual if the threshold is triggered and also we have a threshold alert set up for queries and resource rejections in last 5 minutes for Mithful 7 set to greater than 5. There are various other thresholds on this page that you can set if you desire to. Now let's go and triage those email alerts about the failed queries and resource rejections that we had received. To analyze the failed queries let's take a look at the query statistics page on the database overview page on MC. Let's take a look at the resource pools graph and especially for the failed queries for each resource pools and over to the right under the failed query section I see in the last 24 hours there are about 6000 failed queries for Mithful 7 and now I switch the view to see the statistics for each user and on this page I see for user Mary Lee on the right hand side there are a high number of failed queries in the last 24 hours and no more about the failed queries for this user I can click on the graph for this user and get the reasons behind it. So let's click on the graph and see what's going on. And so clicking on this graph it takes me to the failed queries view on the query monitoring page for database activity step and over here I see there are a high number of failed queries for this user Mary Lee with the reasons stated as exceeding high limit to drill down more and to know more reasons behind it I can click on the plus icon on the left hand side for each failed queries to get the failure reason for each node on the database. So let's do that. And clicking the plus icon I see for the two nodes that are listed over here it says there are insufficient resources like memory and file handles for Mithful 7. Now let's go and analyze the Mithful 7 configurations and activities on it. So to do so I will go over to the resource pool monitoring view and select Mithful 7. I see the resource allocations for this resource pool is very low. For example the max memory is just one MB and the max concurrency is set to zero. That's very odd configuration for this resource pool. Also in the bottom right graph for the resource rejections for Mithful 7 the graph shows very high values for resource rejection. So since we saw some odd configurations and all resource allocations for Mithful 7 I would like to see when the settings were changed on the resource pools. So to do this I can review the audit logs available on the management console. So I can go on to the Vodika audit logs and see the logs for the resource pool. So adjusting the time range for the logs and filtering the logs for Mithful 7 I see on February 17th the memory and other attributes for Mithful 7 were modified. So now let's analyze the resource pool activity for Mithful 7 around the time when the configurations were changed. So in our case we are using external monitoring on MC for this database. So we can go back in time and see the statistics over the larger time range for Mithful 7. So viewing the activities for Mithful 7 around February 17th around the time when this configurations were changed we see a decrease in resource pool usage. Also on the bottom right we see the resource rejections for this Mithful 7 has an increase linear increase after the configurations were changed. I can select a point on the graph to get the more details about the resource rejections. Now to analyze the effects of the modifications on Mithful 7 let's go over to the query monitoring page. I will adjust the time range around the time when the configurations were changed for Mithful 7 and greater the activities queries for a user merrily and I see there are no completed queries for this user. Now taking a look at the failed queries step and adjusting the time range around the time when the configurations were changed I can do so because we are using external monitoring. So again adjusting the time I can see there are a high number of failed queries for this user. There are about like 10,000 failed queries for this user after the configurations were changed on this resource pool. So now let's go and modify the settings since we know after the configurations were changed this user was not able to run the queries. So you can change the resource pool settings of using management consoles database settings page and under the resource pool step. So selecting the Mithful 7 I see the same odd configurations for this resource pool that we saw earlier. So now let's go and modify the settings. So I will increase the max memory and modify the settings for Mithful 7 so that it has adequate resources to run the queries for the user. Hit apply on the right hand top to see the settings. Now let's do the evaluation after we change the resource pool attributes. So let's go over to the same query monitoring page and see if merrily user is able to run the queries for Mithful 7. We see that now after the configuration, after we change the configuration for Mithful 7 the user can run the query successfully and the count for completed queries has increased after we modified the settings for this Mithful 7 resource pool. And also viewing the resource pool monitoring page we can validate that after the new configurations for Mithful 7 has been applied and also the resource pool usage after the configuration change has increased. And also on the bottom right graph we can see that the resource rejections for Mithful 7 is decreased over the time after we modified the settings. And since we are using excellent monitoring for this database I can see that the trending data for this resource pools, the before and after effects of modifying the settings. So initially when the settings were changed there were high resource rejections and after we again modified the settings the resource rejections went down. So now let's go over to the provisioning and reviving the eOnMode Vodika database cluster using the management console on different platform. So management console supports provisioning and reviving of eOnMode databases on various cloud environments like AWS, the Google Cloud Platform and POS2H. So for provisioning the Vodika Management Console on Google Cloud Platform you can use the Launcher template or on AWS environment you can use the CloudFormation templates available for different OSs. Once you have provisioned Vodika Management Console you can provision the Vodika cluster and databases from MC itself. So to provision a new Vodika cluster you can select the Create New Database button available on the home page. This will open up the wizard to create a new database and cluster. In this example we are using the Google Cloud Platform. So the wizard will ask me for various authentication parameters for the Google Cloud Platform and if you are on AWS it will ask you for the authentication parameters for the AWS environment. And going forward on the wizard it will ask me to select the instance types I would like for the new Vodika cluster and also provide the container location URL for my eOnMode database and all the other preferences related to the new cluster. Once I have selected all the preferences for my new cluster I can review the setting and I can hit Create if all looks okay. So if I hit Create this will create a new, MC will create a new GCP instances because we are on the GCP environment in this example. It will create the cluster on this instance. It will create the Vodika eOnMode database on this cluster and it will, additionally you can load the test data on it if you like to. Now let's go over and revive the existing eOnMode database from the container location. So you can do the same using the management console by selecting the revive eOnMode database button on the home page. This will again open up the wizard for reviving the eOnMode database. Again in this example since we are using the GCP platform it will ask me for the Google Cloud Storage at authentication attributes and for reviving it will ask me for the communal location so I can enter the Google Storage bucket and my folder and it will discover all the eOnMode databases located under this folder and I can select one of the database that I would like to revive and it will ask me for other Vodika preferences and for this, for this database reviving and once I enter all the preferences and review all the preferences I can hit revive the database button on the wizard. So after I hit revive database it will create the GCP instances. The number of GCP instances that are created would be same as the number of hosts on the original Vodika cluster. It will install the Vodika cluster on this instances and it will revive the database and it will start the database and after starting the database it will be imported on MC so you can start monitoring on it. So in this example we saw you can provision and revive the Vodika database on the GCP platform. Additionally you can use AWS environment to provision and revive. Now since we have the eOnMode database on MC, Natalia will go over some eOnMode features on MC like managing the subcluster and the full activity monitoring. Over to you Natalia. Okay, thank you. Hello, my name is Natalia Stariski. I am also a member of Vertical Management Consult team and I will talk today about the work we did to allow users to manage subclusters using the Management Console and also the work we did to help users understand what's going on in their depot in the Vertica eOnMode database. So let's look at the picture of the subclusters. In the managed page of Vertica Management Console you can see here is the page that has two tabs and the tab that's active is subclusters. You can see that there are two subclusters are available in this database. And for each of the subclusters you can see subcluster properties, whether this is a primary subcluster or secondary, in this case primary is the default subcluster. It's indicated by a star. You can see what nodes belong to which subcluster. You can see the node state and node statistics. You can also easily add a new subcluster and we're going to do this. So once you click on the button, we launch the wizard that will take you through a few steps. You'll enter the name of the subcluster, indicate whether this is secondary or primary subcluster. I should mention that Vertica recommends having only one primary subcluster but we have both options here available. You will enter the number of nodes for your subcluster and once the subcluster has been created you can manage the subcluster. What other options for managing the subcluster we have here? You can scale up an existing subcluster and it's a similar approach. You launch the wizard and you specify how many nodes you want to end through your existing subcluster. You can scale down a subcluster and MC validates the requirements for maintaining minimal number of nodes to prevent database shutdown. So if you cannot remove any nodes from a subcluster, this option will not be available. You can stop a subcluster and depending on whether this is a primary subcluster or secondary subcluster, this option may be available or not available. In this picture we can see that for the default subcluster this option is not available. This is because shutting down the default subcluster will cause the database to shut down as well. You can terminate a subcluster and again the MC warns you not to terminate the primary subcluster and validates requirements for maintaining minimal number of nodes to prevent database shutdown. So now we are going to talk a little more about how the MC helps you to understand what's going on in your DPOL. So DPOL is one of the core of eOnMode database and what are the frequently asked questions about the DPOL? Is the DPOL size sufficient? Are a subset of users putting a high load on the database? What tables are fetched and evicted repeatedly? We call it refetched in DPOL. So here in the DPOL activity monitoring page we now have four tabs that allow you to answer those questions and we'll go a little more detail through each of them but I'll just mention what they are for now. At a glance shows you basic DPOL configuration and also shows you query executing, DPOL efficiency. We'll talk more about that and other tabs. DPOL content that shows you what tables are currently in your DPOL and DPOL pinning allows you to see what pinning policies have been created and to create new pinning policies. Now let's go through the scenario. Monitoring performance of workloads on one subcluster. As you know, eOnMode database allows users to have multiple subclusters and we'll explore how this feature is useful and how we can use a management console to make decisions regarding whether we would like to have multiple subclusters. So here we have in my setup a single subcluster called default subcluster. It has two users that are running queries that are accessing table mostly in schema public. So the query starts at executing and we can see that after fetching tables from communal which is a red line, the rest of the time the queries are executing in DPOL. The green line is indicating queries running in DPOL. The eOnMode DPOL is about 88% full, a steady flow and the DPOL size seems to be sufficient for query execution from DPOL only. That's the good case scenario. Now at around 17, 15, user Sherry got an urgent request to generate a report and she started running her queries. We can see that picture is quite different now. The table series queries are in a different schema and are much larger. Now we can see multiple lines in different colors. We can see a bunch of fetches and evictions which are indicated by blue and purple bars and a lot of queries are now spilling into communal. This is a red and orange lines. Orange lines indicate a query running partially in DPOL and partially getting fetched from communal and a red line is data fetched from communal storage. Let's click on one of the lines. Each data point on the line will take you to the query detail page where we can see more about what's going on. So this is the page that shows us what queries have been run in this particular time interval which is on top of this page in orange color. So that's about one minute time interval. And now we can see user Sherry among the users that are running queries. Sherry's query involves large tables and are running against a different schema. And we can see the clickstream schema in the name of the part of the query request. So what is happening? There is not enough people space for both the schema that's already in use and the one Sherry needs. As a result evictions and fetches have started occurring. And other questions we can ask ourselves to help us understand what's going on. So how about what tables are most frequently refetched? So for that we will go to the deep efficiency page and look at the middle chart here. We can see the larger version of this chart if we expand it. So now we have 10 tables listed that are most frequently being refetched. We can see that there is a clickstream schema and there are other schemas. So all of those tables are being used in the queries, fetched and then there is not enough space in a depot, they're getting evicted and they again refetched again. So what can be done to enable all queries to run in depot? Question one can be increase the depot size. So we can do this by running the following queries which specify which notes and storage location and the new depot size. And I should mention that we can run this query from the management console from the query execution page. So this would have helped us to increase the depot size. What other options do we have? For example, when increasing default size is not an option. We can also provision a second subcluster to isolate work loads like sherrys. So we are going to do this now and we'll provision a second subcluster using the manage page. Here we're creating subcluster for sherry or for work loads like yours. And we are going to create it with three nodes. So sherry subcluster has been created. We can see it here added to the list of the subclusters. It's a secondary subcluster. Sherry has been instructed to use the new sherry subcluster for your work. Now let's see what happened. We'll go again at the DevOctivity page and we'll look at the at-a-glance tab. We can see that around 1807 sherry switched to running queries on sherry subcluster. On the top of this page you can see subclusters selected. So we currently have two subclusters and I'm looking what happened to sherry subcluster once it has been provisioned. So sherry started using it and the lines after initial patching from DevOct, which was from a communal, which was a red line. After that all sherry's queries fit in DevOct, which is indicated by green line. Also the DevOct is pretty full on those nodes, about 90% full. But the queries are processed efficiently, there is no spilling into communal. So that's a good case scenario. Let's now go back and take a look at the original subcluster, default subcluster. So on the left portion of the chart you can see multiple lines that was activity before sherry switched to her own dedicated subcluster. At around 1807 after sherry switched from the subcluster to using her dedicated subcluster, there is no, she is no longer using the subcluster, she is not putting load on it. So the lines after that are turning in green color, which means the queries that are still running in default subcluster are all running in DevOct. We can also see that depo fetches and eviction bars, those purple and blue bars, are no longer showing significant numbers. Also we can check the second chart that shows community storage access. And we can see that the bars has also dropped, so there is no significant access for communal storage. So this problem has been solved. Each of the subclusters are serving queries from depo, and that's our most efficient scenario. Let's also look at the other tabs that we have for depo monitoring. Let's look at depo efficiency tab. It has six charts and they'll go through each one of them quickly. File res by location gives an indicator of where the majority of query execution took place in depo or in communal. Top-down refetches into depo, and we mentioned this chart earlier in our use case. It shows tables that are most frequently fetched and evicted and then fetched again. These are good candidates to get pinned if increasing depo size is not an option. Note that both of these charts have an option to select time interval using calendar widget, so you can get the information about the activity that happened during that time interval. Diffle pinning shows what portion of your depo is pinned, both by byte count and by table count. And the three tables at the bottom show depo structure. How long tables stay in depo? We would like tables to be fetched in depo and stay there for a long time. How often they are accessed? Again, the tables in depo, we would like to see them accessed frequently. And what the size range of tables in depo? Depo content. This tab allows us to search for tables that are currently in depo and also to see stats like table size in depo. How often tables were accessed and when were they last accessed. And the same information that's available for tables in depo is also available on projections and partition level for those tables. Diffle pinning. This tab allows users to see what policies are currently existing. And you can do this by clicking on the first radio button and click search. This will show you all existing policies that are already created. The second option allows you to search for a table and create a policy. You can also use the action column to modify existing policies or delete them. And the third option provides details about most frequently refetched tables including fetch count, total access count and number of refetched bytes. So all this information can help to make decisions regarding pinning specific table. So that was about it about the depo. And I should mention that the server team also has a very good presentation on the webinar on the e-on-mode database depo management and subcluster management that strongly recommended to attend or download this live presentation. Let's talk quickly about the Management Council roadmap. What we are planning to do in the future. So we are going to continue focusing on subcluster management. There is still a lot of things we can do here. Promoting demoting subclusters, load balancing across subclusters, scheduling subcluster actions, support for large cluster models. We'll continue working on a workload analyzer enhancement recommendation, on backup and restore from the MC, building custom threshold and e-on on HDFS support. Okay, so we are ready now to take any questions you may have now.