The value of DBA in the sustainable development of enterprises

In today's highly competitive market environment, cost control is undoubtedly a very important part for enterprises to improve their competitiveness. As many competitors will provide homogeneous products and services, enterprises must start from controlling internal costs. As an IT department with relatively large internal expenses, they should first bear the brunt of maximizing the potential of the system. DBAs should introduce new methods, new ideas, and expand their horizons, not just limited to database maintenance. In general, what DBA can do for this goal can be divided into three aspects: system optimization, application optimization, and process optimization.

First, let's talk about system optimization. This is what most DBAs need to access. When an enterprise sets a business goal, IT directors need to transform the business goal into the system's carrying capacity, so as to ensure that the system will not be dragged down. This target has been set to DBA. DBA should divide it into three parts: Host and operating system, storage and database.

The host optimization starts with the hardware selection. We need to determine what the target capacity of our system is, what hardware needs to be purchased to support the capacity, and quantifiable indicators, with quantifiable indicators, we can test to determine which machine can meet the performance requirements. Finally, we need to consider the overall cost and select a machine that can meet the target capacity and has the highest cost performance ratio. Because the hardware upgrade is very rapid, a new generation of hosts is generally launched in two years. Therefore, when selecting a machine, only enough machines are selected instead of one purchase, any high-end machine will be replaced by a higher-end machine after two years. In general, at the early stage of enterprise development, using LINUX pc server is the optimal solution. Compared with windows systems, linux systems run more stably for a long time, compared with unix systems, linux also reflects the economy. With the rapid development of enterprise business and higher system availability, we need to consider the use of UNIX system servers and various high-availability solutions to ensure system stability, provide support for business development. After the system is built, we need to focus on the host and operating system, continuously track the running status of the system, and optimize the system bottleneck.

For storage optimization, DBAs need to have a good understanding of various storage systems and understand the advantages and disadvantages of DAS, NAS, and SAN. To select different storage solutions for different applications, for example, if the performance requirements of a system are not the first, but the economy is very important, the DAS may be a good choice. NAS may be the first choice if a system requires fast migration. If a system pursues performance, SAN can be used as its solution. In addition, various storage manufacturers have also proposed their own data lifecycle management implementation solutions, so that users can put the most important data on the core storage, secondary System and historical data can be distributed on low-cost level-2 or level-3 storage, saving users as much cost as possible. In addition, the optimization of storage details can be concentrated on raid division, storage read/write cache division, storage iops, throughput, cpu utilization, and response time monitoring. DBAs must first do the right thing (select the right system for different applications), and then do the right thing (focus on technical details, focus on performance, use the knowledge reasonably in optimization ).

To optimize DBAs on databases, you must first find a staged optimization method. Generally, database optimization involves the following steps:

1. Set Optimization objectives;
2. Record the current performance;
3. Find the current bottleneck;
4. Bottlenecks of Optimization;
5. Implement optimization strategies;
6. measure and record the optimized performance;
7. Repeat steps 3-6 until the optimization goal is reached.

In general, the optimization goal in the Enterprise will basically be linked to the database throughput and the database Request Response Time. After determining the optimization goal, we can view the current performance, view the database performance monitoring report, view the database wait events, and view the top n SQL, locate the bottleneck together with the performance reports on the operating system, then optimize the bottlenecks accordingly, compare the performance differences before and after optimization, and repeat this process to achieve the optimization goal. Because enterprise application systems may change every day, DBAs must pay attention to performance issues every day. optimization is a long-term continuous process, and the ultimate goal is to maximize the system energy.

Next we will talk about application optimization. In fact, when the system load is high, it is caused by poor SQL statements in the application. Controlling the quality of SQL statements is a top priority for DBAs. I have also helped others optimize many systems. Usually, problems occur due to poor SQL statement writing. The created index is not created and does not need to be associated with related statements, high-concurrency full table scanning results in a high system load. At this time, some companies will consider upgrading hardware, storage, and host. Usually, they will choose hardware that is much higher than the actual capacity, you can use pc server to select minicomputers and rac for minicomputers. In fact, if you have a professional dba, there is no need to upgrade the optimized system, all IT investment can be saved.

For companies that develop their own applications, DBAs should take the initiative to establish an SQL training system and regularly explain SQL-related knowledge to developers. It is best to explain the problematic SQL as a case study, the effect is obvious. This is because SQL statements are written by programmers in applications. In many cases, DBAs will not find statements with performance problems until the application is launched, at this time, the business system has a major or minor impact. The dba needs to eliminate the problem in the initial stage. Of course, training is not enough. DBAs need to use the strength of the test department to discover problematic statements as much as possible during the test phase. In this way, a complete set of development databases and test databases must be established, the table structures and objects of the development database and test database must be consistent with those of the product database. Taking oracle as an example, dba can use the pl/SQL script to compare the data structures of the two databases and automatically synchronize them, you can also regularly synchronize data from the product database to the development and test database to ensure the effectiveness of the data in the development and test database. No problematic statements cannot be found in the test because the data in the development and test database is indeed lost.

In addition, DBAs are required to optimize the application system architecture. For example, we can create a cache server in front of the database, save the pages or java objects that have been accessed. This work will be done by developers. dba can provide the most frequently accessed statements and the most frequently accessed objects, then, developers implement cache through programs. When an application request comes in, they first access the cache server. If the page or object to be accessed already exists on the cache server, therefore, the database does not need to be requested. In this case, the pressure on the database will be greatly reduced. Because the cache server is basically composed of pc sever clusters, the horizontal scalability is relatively strong and the price is relatively low, therefore, when an enterprise grows to a certain scale, you can consider adding a cache layer to the application layer rather than upgrading the database hardware. DBAs must be aware of this and be able to explore all the areas that can be optimized, looking at new methods and new ideas from another perspective is not limited to database optimization.

Finally speaking of process optimization, a complete system is required to ensure stable and fast operation of the database system. From the start of device selection, you need to organize a selection team to take the device selection as a project. You need DBA (SA), test engineers, and developers to form a project team, PM is required to supervise the entire project.

DBA is responsible for data preparation, test system construction, test engineers are responsible for writing test cases, developers are responsible for organizing the establishment and configuration of application server, PM coordinates the resources of the entire project and controls the project time. After the test is completed, the DBA should work out a system upgrade plan as a PM, control the resources and schedule of the project, and communicate with relevant departments. After the hardware is released, a test process is required to ensure that the applications developed by programmers do not have SQL problems. The system can be launched only when the test passes. After the application is released, the DBA should collect problematic SQL statements and return them to the programmer to modify these SQL statements. At the same time, the DBA should provide the developer with stage SQL knowledge training. The dba team should also establish a training system, so that the entire dba team can share business knowledge and improve the technical level of all DBAs. The purpose of these tasks is to optimize the database system, minimize mistakes, detours, and use the least amount of resources to achieve the maximum effect.

In modern enterprises, DBA is not only a simple database maintenance personnel. To provide high value-added services, dba must use its own knowledge and skills to constantly optimize the system, reduce IT expenditure, and improve the enterprise.

Competitiveness. To be an excellent DBA and sustainable database practitioner, you should not only master the operating system, storage, database, good communication skills, but also the ability to manage projects, we also need to have a keen sense of the latest technology in the industry, have the global IT architecture planning capability, and will always grow in learning.

Related Articles]

• DBA management experience

• What does DBA write on its work list?

• Four survival rules that a qualified DBA should abide