The importance of DBA in system design and development

I. DBA's current adjustments to system performance and stability

Currently, the DBA's efforts to adjust the system performance are roughly as follows:
1. Optimizing On the hardware layer. This usually means spending money directly, buying equipment, and resizing.
2. Optimize the database, such as adjusting initialization parameters and the physical structure of the database.
3. Optimize the SQL of the application, such as analyzing statspack in the database and adjusting top SQL.
4. There are only a few applications that usually require high system stability. Prior to the launch of new applications, DBA should fully review and evaluate the SQL statements.
Problem: in the analysis, design, and development stages of the application system, according to the current situation, few DBAs are involved, and after the application system is launched or the development is basically completed, the tuning work that DBA can do is actually very limited.
2. Performance or stability of many application systems is still unsatisfactory

The performance of many application systems is not ideal, or the system data may encounter strange errors that are difficult to reproduce. These problems (especially performance problems) are sometimes not reflected in the early stages of the system, however, as the system runs and data increases, it gradually becomes difficult to solve. This will cause a lot of trouble for later system function expansion and user usage, it is often reflected that people who develop and design these systems do not understand databases! Based onOracleFor example:

Unreasonable underlying data structure
Due to the lack of assistance from professional DBAs, many underlying database table structures designed by the system have many problems. People who have done the system know that the underlying database structure is unreasonable and the transformation cost is almost the same as a reconstruction! I have seen an OLTP system. Its core table actually has 100 fields, with an average record exceeding 8 K. If you use an Oracle 8 K block by default, more than half of the rows must generate row links!

The worst thing is that people who design such a table structure think they have fully utilized redundancy to reduce the connections between tables. In fact, they do not know what is a paradigm or what is an update exception, according to the paradigm, this table should be split into two tables, but if you want to change almost allProgramChange all! Although the paradigm is not the higher the better, it is definitely something that the designer must thoroughly understand. In terms of redundancy, most DBAs believe that the cost of cascade update is very high, so redundancy should avoid cascade update. For the redundant use of relational database design, it is not a very easy-to-Master technique.

Unreasonable underlying database structure design has laid a heavy time bomb on the system's performance. This system could not run for less than a year at the customer's end, and the data volume was slightly increased, so the performance and stability would go down, the reconstruction cost is extremely high, and buying new servers is definitely only a cure. What if the underlying data table structure is designed by a senior DBA? Of course, if you want DBA to design the database table structure completely, DBA must have a very clear understanding of the business details of the entire system. For dBA, human resources are difficult. After all, maintaining an online server has occupied a lot of DBA resources, and leaders usually pay more attention to this.

Few leaders can recognize the role of DBA in system development and design. Compared with maintaining online systems and handling dB faults, DBA is equally important to the stability and performance of the entire system!
SQL Performance problems

System development usually has nothing to do with DBA. However, if DBA has a good understanding of the system, it can also make a lot of contributions at this time. For example, to check whether the data stream of the system service is correct, this requires some measures, such as sqltrace and 10046, to check in detail the logical implementation of the system, on the one hand, it is found that the system is too resource-consuming or the preparation of non-standard SQL statements for timely adjustment and optimization, on the other hand, it is found that the unreasonable database access in the system is not found online, at that time, it may have crashed. For example, when a page needs to display the category list of multiple items, the program may easily make a mistake, that is, read the same data multiple times with the same SQL statement, and apply it to the display of each list. If you only read it once, or simply cache it on the web layer (with an appropriate refresh policy ), this greatly reduces the I/O consumption of a single access to the page on the DB. Sometimes you may even check SQL statements that do not need to be executed at all, and frequently execute these functions that are irrelevant to you ...... At the same time, the inspection of data streams can also find some hidden System Bugs, which need to be based on DBA's understanding of business details.
Who said DBA will only spend money? If the I/O load of a server reaches the limit, most people can only choose to expand the capacity. At most, some functions can be reconstructed for some optimization. As can be seen from statspack, most of the system's I/O resources are occupied by SQL statements that should not be executed so frequently. They are not slow in a single execution, but the proportion of system resources occupied is unusually high, in every business, the inspection of these problems and the optimization of data streams are the greatest savings in system resources, that is, saving money! Only DBA can be competent in this job.

Concurrency Problems
Everyone knows that the system has concurrency, but when designing the system, we often design and code according to the single business thinking model, the possible problems of concurrent operations between the same service and different services are seldom considered. Generally, some "strange" and "impossible" errors occur in the system irregularly, which are very likely to be caused by concurrency, it often shows that the designer does not understand the database lock mechanism and cannot be well integrated with the business. Designers do not know databases, but DBAs do not know business, which leads to many problems that can be avoided.

The most typical example is the reservation of a hotel by Tom kytes. When two waiters simultaneously press the button to find the room, the result is that both of them have booked the same room. This is a classic problem, at present, it seems that it is easy to solve, isn't it just adding a lock? However, this is just an example. In your actual application system, if you add for update in this way, it may lead to other problems! For example, deadlock. In a complex business system, deadlock is not difficult to see. This is a design vulnerability of the designer. The designer needs to fully measure the business relationship and then set rules for table locking to avoid it as much as possible. Learning to use locks to ensure data integrity is still not enough, but also flexible application of locks, appropriate use of Optimistic Locking.

If important services are not discussed, direct pessimistic locking is also undesirable, which may cause some problems for system maintenance. In this case, some services may even cause massive data lock timing, the cost of OLTP is very high, seriously affecting the system concurrency. I once encountered a problem with error data. After analysis, it was determined that two different services are concurrent, and the necessary locks are missing, resulting in error data. However, based on the special nature of the service, the lock price is expensive. With the DBA's careful investigation, it is confirmed that the best of both worlds can be achieved through Optimistic Locking, that is, the time-of-Submission test. It can be seen from this that the health and integrity of data are sometimes in conflict with the system's concurrency capabilities, but experienced DBAs can teach you how to get the bestSolutionOf course, the premise is that DBA participates in the design and is familiar with the business.
System Architecture Problems
DBA is not a system architect, but the database is the core part of an application system. At the same time, because the database server is not as easy to expand as the application server, it is often the bottleneck of the overall system performance, therefore, when designing the system architecture, the architect should fully consider the opinions of the DBA, and consider the convenience or even feasibility of the DBA to adjust the performance of the SQL statements in the database!

Otherwise, the DBA and the development team may be too slow to respond to system performance problems or even helpless! I have seen an architecture that cannot implement the most common paging SQL statement in Oracle! Binding variables is not under consideration! In addition, some third-party components or architectures can help our system generate SQL statements, which of course saves time and accelerates development. However, in such a system, it may be difficult for a DBA to optimize an SQL statement, because developers have to modify a large number of items and the modification workload is large and takes a long time, in addition, a large amount of work is certainly more likely to bring new errors! Oracle Master Tom kytes once opposed to using this automatically generated SQL component or architecture in the classic book export one on one, this kind of things may bring performance and maintenance problems to your system!

If you have consulted a senior DBA about these problems, we believe that you will find and fix or adjust the architecture as soon as possible. In the later stage of system development, it is difficult to make essential adjustments to the architecture. If your system requires high efficiency and high concurrency access, it is recommended that Architects tend to respect the opinions of DBAs, which is very important for the overall system performance and continuous tuning. DBA must have a certain understanding of the system architecture and clarify what difficulties they encounter in the existing architecture.

3. Improve the performance and stability of the application system

In addition to the daily work of DBA, such as DB optimization, SQL optimization, and server maintenance, the DBA's work scope is extended to strengthen the DBA's control capability and decision-making power in the system development process.

1. Involve DBA in requirement analysis, fully understand user requirements, and understand and consider the implementation costs of these requirements from the DB perspective.

2, schema design must be determined by the DBA design or audit, this also requires the DBA must understand the business system, in order to organize the correct, good scalability of the E-R relationship.

3. Let DBAs participate in system design more deeply, and let DBAs know the business design details of applications as much as possible. This plays a decisive role for DBAs to review data streams. If conditions are met, business data streams should be used as one of the system documents for future repeated verification.

4. Before the system goes online, DBAs should review SQL statements and data stream logic in sqltrace. It is best to evaluate the database cost (such as I/O) of some important business functions.

5. Performance monitoring after the system goes online, making timely adjustments or restructuring to optimize the data access logic within a certain range.

As mentioned above, DBA's human resources must be insufficient. Therefore, it is correct and efficient to refine the DBA's work and divide the work. In some companies, DBAs have been divided into product DBAs with dedicated management online servers, and development DBAs with dedicated management and participation in system design. They fully guarantee the stability and efficiency of the system from different aspects and are worth learning!