Hot pot, Report and database story

Although the topic of this article is a little freehand, but it is purely technical analysis paste, borrowed a hot pot store story, discuss the report query scenario delay problem and a bit of database characteristics.

Long ago, there is an old-time hotpot shop, Customer Ying Men, business is booming. Hot pot, we all know, the first is poured into the bottom of the soup, such as soup boil, to put vegetables, meat, seafood and other food ingredients, such as cooked food can eat. From the food ingredients to eat into the mouth, there is a waiting process, the length of time depends on two factors, one is the hot pot under the charcoal is not enough, the fire is not burning sufficiently prosperous, the second is the food can not put too much, especially some frozen state of the ingredients, otherwise it will wait for a longer time.

For BI systems, a variety of ingredients is the data provided by the upstream system, the work of the BI system is processing data and the corresponding customer query request, is the process of heating the ingredients. The BI system, or as its core database, is this hotpot, the size of the disk equivalent to the pot capacity, CPU and memory equivalent to the charcoal under the pot.

This shop is more special place is to highlight the customer's honor, the food is the waiter is responsible for putting in the pot, diners eat. In addition, the hot pot management mode is each table of hot pot by a waiter responsible for the whole process management, including hot pot cleaning, serving and so on.

Different project teams are responsible for the development process of the bazaar, and the operation and maintenance links are the responsibility of their respective administrators, who are only users of the final report.

Under normal circumstances, the ingredients are put into the pot after 5 minutes to eat, the customer is satisfied. I do not know when it began, the situation has changed, the time the diners wait longer. Finally, one day, the hot pot for lunch was not eaten at dinner time, and the guests were annoyed to find the boss's complaint.

Business users are slow and slow to reflect report queries, and end up with patience.

The boss came to the lobby manager and chef to solve problems, after various tracking, research, we finally found the problem. Originally to the hot pot in the food ingredients, there is a waiter will add ice, we think he is a little confused, call him a small Hu bar. Xiao Hu is not a person in the fight, the store waiter is a lot of small Hu's hometown, also has this habit. Obviously, the ice will slow down the process of food heating, so the guests of the service will have to wait a long time to eat food, resulting in complaints from guests.

For BI systems, one of the main reasons for slow latency is that SQL is poorly written, resulting in very low query efficiency, which is what we call rotten SQL, like a little ice cubes, needless to consume a lot of system resources.

Just right, the boss think more hot pot also have problems, each hot pot cleaning is by different waiter responsible, management up also laborious, is not can engage a big hotpot, by the way to solve the problem of management.

It is not the best way to rely on technology to drive management. Ivan is somewhat doubtful, but sometimes it does.

Chef for the "small Hu plus ice" problem and boss management needs, put forward a solution, codenamed "Big pot." The plan includes two parts, one is the big pot of changing the fire, the second is the training for the little Hu. In particular, a large hotpot (an MPP database) is needed to replace the common hotpot (stand-alone database) currently used for each table, but with two conditions. First, the big pot firepower enough, low cost, it is not enough can also be extended (based on the X86 server, can be horizontally extended), and the second is the kitchen team to master the appropriate use of skills (because the batch processing has been used this technology, the team is familiar with this technology). Chef selected this big pot is nine Gongge hotpot, each table diners with a lattice, experience basic is unaffected. (This place is not a serious ^q^) (the project development team and users are faced with logically independent data marts, while the MPP database has a certain workload management capability.) At the same time, the introduction of new operating standards for Xiao Hu and his fellow people training, strictly prohibit the hot pot to add ice (release new SQL Development specification, require developers can not write rotten SQL)

"A big pot" because of its epoch-making revolutionary, immediately spread in the hot pot industry, Ivan also heard the plan, nagging his views.

First, the "Operation specification"

This old shop usually also attaches great importance to training, rectification, the waiter is more than two years old staff, has been to the small Hu have conducted many training. Similar training and norms have been long-term, repeated implementation, this time can really get rid of the bad habits of the small Hu?

For IT systems, of course, the basic skills of personnel is directly affect the delivery and operation of the quality of the system, if the same kind of training and norms have been long-term, repeated implementation, in the trainers and trainees have not replaced the situation, more detailed norms can improve the quality of SQL? Although this measure is politically correct, what kind of effect it can achieve is actually quite doubtful.

If it is the ordinary big pot, the waiter is operating at the same time, once someone adds ice, all diners are affected, not more difficult to find the troublesome little Hu? It would be nice if we could isolate the impact.

For the general system construction, only by meticulous management system to strengthen the level of implementation of personnel, in fact, the cost is high, the risk is big thing. The system architecture is designed to avoid this risk at a higher level through an elegant architecture design. A peer expert once said, "The real good system is to avoid the operation and maintenance events from the architecture design", in fact, that is the truth.

The banking regulator defines a series of operational and maintenance event definitions to oversee the safe operation of the bank's IT systems.
For example, a primary event: Due to an abnormal information system service, in the business service period caused two (including) the provinces (autonomous regions, municipalities) all sub-branch business can not be carried out up to 3 hours (inclusive), or a province (autonomous region, Municipalities) all sub-branches of the business can not be carried out up to 6 hours (inclusive) of events designated as a particularly significant emergency

Therefore, Ivan believes that training is necessary, but the steady operation of the system can not rely too much on the implementation level factors.

Again, "The structure of the big pot."

MPP database can this big pot solve the isolation problem? Ivan thought it might be debatable. A fundamental principle that many MPP databases will follow is to distribute the data evenly across all data nodes. For example, if a table has 1 billion records, and the MPP cluster has 10 nodes, each node stores an average of 100 million records. Queries on this table are pushed to each node, and the results are returned to the caller of the database. This way, if there is a rotten SQL, it will actually slow down all nodes, but when the MPP cluster is large enough, you can execute the rotten SQL faster.

To deal with the resource competition between different tasks, we should talk about "workload management" function. Simply put, workload management is the allocation of resources needed for different tasks to roughly lock in resources, not preempt resources, cause certain tasks to be affected, or even starve to death. The resources here include CPU, memory, and disk I/O, where disk I/O is often difficult to divide and manage because of its physical seek mode. Due to the characteristics of the above MPP architecture, data marts are combined into a single physical cluster, the data is evenly distributed to each node, and disk I/O competition can intensify, creating greater challenges for "workload management". Therefore, the isolation of the MPP pot may not be enough. For the "Small Hu plus ice" problem, if you can not do a good job load management, only by increasing the firepower of the way, is an extensive, resource accumulation of the way, not worth advocating. Even if there is a big pot, if one day Xiao Hu fever, add the whole bucket of ice, all diners still have no food, the potential operational risk is too big.

Is there a better big pot?

See here, people may have their own answers, Ivan thought the better solution should be the cloud database or a cloud-capable distributed database. A big difference between a distributed database and MPP is the storage rules for the data. The former is often not evenly distributed, but a limited copy of the data (usually 3 copies) is stored on several nodes in the cluster, and these storage nodes can be constrained by a certain management strategy. This separates the I/O competitive areas at least at the system level. That is, no matter how much ice the little Hu adds, if it only affects the diners he serves, the risk can be controlled within a limited range. Of course, in addition to strong workload management capabilities, the high availability of the system is also very important, any system concentration will accompany the concentration of risk, put aside the internal design of the system does not say, this logic level of a single point of reliance is often the managers have concerns, it needs to be treated with caution.

The MPP database discussed in this paper usually has a mature high-availability scheme, which is not the focus of this paper, so it is skipped.

Postscript

In the data age, the enterprise of information maturity usually constructs a lot of BI report system, every day management staff according to the data of the report adjusts the enterprise overall management decision and each concrete measure, action. In addition to the system to ensure accurate data, the most concern is the corresponding efficiency of the query is the delay time (Latency). BI system in order to provide user needs of the report to do a lot of ETL work, through preprocessing processing data and storage, the use of space to change the time of the way, improve the user and system interaction speed. Batch processing and online query if the same time period in the same database, will generate a lot of resources competition, because batch processing is a long task, from the current technology, it is difficult to coordinate. Therefore, the use of bulk and online physical separation of the way to avoid resource conflicts. Even after this is handled, there is a slow response to online report queries.

This article refines a simplified scenario and focuses on the problems that exist in the online query section, omitting the bulk portion.

Said back to hot pot, with the heat and sweat of summer will pass, eat hot pot season is far away? Ivan has been to the shop, really do not let customers put their own ingredients, but with "workload management" ability of the big pot has not yet met, maybe one day will really appear.