Listen to cloud Database management platform NETOPGO Introduction

This is a creation in Article, where the information may have evolved or changed.

➠ More technical dry please poke: Listen to the Cloud blog

Write off and on for nearly one months, listen to the first version of the cloud database management platform finally finished, the period to go back and forth changed a lot of small problems, now deployed to the production environment up.

At first, the back-end database cluster has only more than 10 node groups, and routine patrol work does not take much time and effort. With the growth of the business, in a short period of time the back-end cluster expansion to hundreds of nodes, then the daily patrol if still human flesh completed, the reason, the end may be not to do patrol or the fate of inspection, which day to think up a bit. Obviously it's not our style. So how to liberate the time and energy we spent on the patrol, we decided to write a tool to help us complete the inspection work, all we have to do is log on to the system, look and check.

In this version of the implementation of the features are not many, mostly for the current work of pain points to develop, the architecture is very simple, divided into reports and data collection two parts, data collection program mainly from two places to collect data, one is the online database to collect some indicators data, One is to call the cloud vendor's API to take DB capacity information.

The development language of the system is golang,netop is the abbreviation of our department, so simply call Netopgo. Front-end page is changed Jumpserver page, data Porter really write the front end.

The Beego used by the Web development framework. Reason, Jumpserver's front-end template is really a very good easy-to-learn template, Beego not to mention, powerful, beautiful and amazing! Direct, SD has a code, hehe hey.

1. Instrument panel

Here are some data overviews, a variety of totals, up to the current monthly data volume trends. Data capacity changes for each business library this month, as well as the number of slow queries the previous day ranked Top12. The dashboard provides quick links to jump, just click on the relevant numbers. For example, clicking on the total number of DB will jump to the DB list page.

2. User Rights

Divides three permissions: admin, DBA, and Guest. If the current user has insufficient permissions, the Access restricted page will prompt for no permissions or some buttons on the page are not available.

3. Host Management

This is primarily host list management and business Group list management. The host list is dependent on the business group list. This section of the guest and database administrators have permissions to control, such as the guest user logged on to view the list of hosts only readonly users of the remote login is available, other features are limited, click to remotely login will pop up a webshell, such as:

4. DB Management

DB Management is the center of gravity for this release, first look at the DB list

Here are all the examples on the line, and each instance has a chart and a slow SQL Portal link, and clicking on the chart will jump to the chart's page:

The chart currently has a change curve of data volume (daily), Qps&tps (statistics per 10 minutes) and number of slow queries (daily stats). Monitoring is not the focus of this system, at present, basically all production environment monitoring is another platform to support. I hope the system can show me the trend of data volume change, qps&tps situation of all instances, and most importantly, the details of slow query. If you click Slow query in the list, you will be redirected to the Slow query list page, such as:

This page will be a simple summary and statistics of slow SQL, while providing the ability to view SQL and specific execution plan, such as click to view the execution plan, will be to the production environment database to do an immediate execution plan analysis and return results, so catch slow SQL does not have to open the Black window (Xshell) Log in to the backend database to see the execution plan, which can be viewed directly on the front page. As follows

Schema list, this page also has a relatively large amount of information, including a list of all business libraries, this list is mainly for the Data Query window use, dynamic additions and deletions of the schema list, the equivalent of dynamic additions and deletions of data sources. It also shows the current data capacity of each business library and is very intuitive.

Since our back-end database uses partitioned tables, partitions are automatically maintained by the stored procedures, so we have the partition add and delete status of each business library backend to show here, click the minute button will jump to the Partition monitor list page, can clearly see which nodes of the added partition is not successful, if add failed, The red failed will be displayed. The hour and the day table are the same.

Ordinary work will have a lot of data query scenarios, research and development and testing of colleagues so much, if all the requests are directed at DBA a person, is also a headache problem, so in Netopgo opened a query window, the realization of the Authority partition, query SQL audit record and automatic back-end recognition function. Dynamic addition and deletion of data sources, the schema list to add a data source, in the query window can be immediately displayed and query. If the backend allows, the DBA can do anything, such as insert operations, as follows

If the backend is a proxy, even the DBA role can only support queries, as follows

If the Guest account, all the database can only be query permissions, as follows

If the query succeeds, it jumps to the results page, and if there are many columns beyond the width of the table, there will be scroll bars appearing below. As follows

Currently, for information security reasons, data export is not supported, but it is being considered to add a result export function to the DBA perspective.

The SQL executed in the Data Query window, whether or not successful, is logged in the audit log, and the audit log can be accessed by reviewing the audit log page, as follows:

The list shows the execution user, schema, status, and SQL for each SQL executed. You look at this list when other colleagues are inquiring, and it's very pleasurable.

5. Upgrade Recording function

Upgrade record function does not introduce workflow, so it is just a simple record, the current implementation of the application upgrade record, database upgrade record and fault record function, but we all hope not to manually input, it is best to mention the process system of their own records. This may be difficult to achieve from the present environment.

But from my own experience, this record has improved a lot compared to the previous one. Take the database upgrade record as an example, the previous upgrade record is placed in an NFS shared directory in an Excel table, the directory is deep, with the SQL file archive directory is not in a directory, each record needs to be two times the N-level directory, and if you want to find a previous upgrade of SQL, is not very good, Because the file is more, the DB upgrade record function is like this

When recording the upgrade record, the SQL file is uploaded directly to the server-side directory, and the list is provided with an entry to view the attachment, as follows

Click on the detailed content, will jump to the following page

Click on the attachment, if it is on the Chrome browser, will be directly in a new window to display the text content, if it is another browser, will download the file directly. Attachment directly on the browser is really handy. Click Accessories under Chrome

This version of the implementation of the function is basically these, the next plan in the query window of the page to support the back-end intermediate agent under the cluster to do DDL and DML changes, the middleware itself is no way to support these, so we implement the idea is to select the schema in the Query window, SQL is committed to all shards on the backend of the agent and is eventually returned to the execution state to achieve the purpose of authentication. Currently we are using the script to complete, a little low. This version has done quite a lot of preparation, so it won't be difficult to implement this feature. At present, Netopgo bound too many of our own business scenarios, follow-up if the function is perfect, will be in the commonality up and down, make an open source version out.

Original link: http://blog.tingyun.com/web/article/detail/600