Shulou(Shulou.com)06/01 Report--

Let's talk about the high-availability solutions for simply comparing Oracle and MySQL. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, and I'm sure you'll benefit from reading this article that simply compares the high-availability solutions of Oracle and MySQL.

Oracle's solution supported the needs of the core business during the rapid development of Ali. It's about this kind of architecture, which looks huge. The RAC inside is an aristocrat, with expensive commercial storage, extremely high network bandwidth requirements, front-end a large number of small computer services and high licence costs. Very typical of IOE's classic architecture.

If you want to consider remote disaster recovery, then the resource allocation should be double, and the budget should be doubled.

The architecture of MySQL is relatively more civilian, ordinary pc is fine, but the order of magnitude is high, business split, horizontal split can horizontally expand into a very large number of nodes, the MySQL clusters of many large Internet companies are hundreds of scale, thousands of are not uncommon. With so many service resources, there is still a probability of failure, so ensuring the sustainable access of business services is the key to the technical solution. According to the structure of MHA, it is basically the MHA Manager node that is responsible for the status of the entire cluster, just like a neighborhood committee aunt who knows everything about the residents like the back of her hand.

Of course, the above statement is too general, let's start with some details. For example, let's talk about the Internet first.

Oracle still has very strict requirements for the network. Generally, it requires 2 physical network cards, and each CVM needs at least 3 IP and Public IP,private IP,VIP. Besides shared storage, at least 2 computing nodes are required.

Private IP is mutual trust between nodes, and Public IP and VIP are in the same network segment. To put it simply, VIP is external and is the drift IP of the network where public IP resides. In 10g, VIP is used to do load balancing. 11g started to have scan-IP, but the original VIP is still retained, so the network configuration requirements in Oracle are still very high. Put aside the shared storage, the core of the construction is the network configuration, the network is general.

Scan-IP can also continue to expand to support up to 3 scan-ip, as shown in the following figure

Of course, the network level is not only these, this aspect of the bright spot Oracle is very professional. We need to know about TAF. In my book Oracle DBA work Notes, I wrote:

TAF (Transparent Application Failover) is a transparent failover for applications in Oracle, which is especially widely used in RAC environments. It is true that Load Balance has been greatly improved in RAC, from the Load Balance of multiple VIP addresses in the 10g version to the SCAN in the 11g version.

However, in the implementation of Failover, there are still certain usage restrictions. For example, the default implementation of SCAN-IP in 11g does not have the option of Failover by default. If one of the two nodes dies, then continuing the query in the original connection will indicate that session has been disconnected and needs to be reconnected. The client-side TAF will mainly discuss some simple contents of Failover Method and Failover Type.

(1) Failover Method

The main idea of Failover Method is to exchange for failover time or resources for implementation.

To put it this way, suppose we have two nodes. If a session is connected to node 2, and node 2 suddenly dies, in order to deal with the case of Failover more quickly, Failover Method has two types: preconnect and basic.

-the pre-connection method of preconnect will still take up more resources, and some additional resources will be occupied in advance on each node, so it will be relatively smoother and faster when switching.

-basic this way, when Failover occurs, you switch the corresponding resources. There will be some stutters in the middle, but the consumption of resources is relatively much smaller.

To put it simply, basic will only judge when a failure occurs, while preconnect is prepared in advance; from the practical application, basic is more general and is the default failover mode.

(2) Failover Type

The Failover Type implementation is richer, more flexible, and very powerful. At this time, the control granularity can be controlled according to the execution of the user SQL, which can be divided into select and session. This is illustrated by a small example.

For example, we have a large query on node 2, and node 2 suddenly dies. For a query that is being executed, for example, there are 10,000 pieces of data, and 8000 pieces of data were found when the failure occurred. So what should we do with the remaining 2000?

The first way is to use select; to complete the failover and continue to return the remaining 2,000 records, of course, there will be some context switching, which is transparent to the user.

The second way is that session; disconnects directly and requires re-query.

In the 10g version, the configuration to achieve Load Balance+Failover with the help of VIP configuration is as follows:

Racdb= (DESCRIPTION = (ADDRESS= (PROTOCOL= TCP) (HOST=192.168.3.101) (PORT= 1521)) (ADDRESS= (PROTOCOL= TCP) (HOST=192.168.3.201) (PORT= 1521)) (LOAD_BALANCE = yes) (FAILOVER = ON) (CONNECT_DATA = (SERVER= DEDICATED) (SERVICE_NAME = racdb) (FAILOVER_MODE = (TYPE= SELECT) (METHOD= BASIC) (RETRIES = 30) (DELAY = 5) if 11g SCAN-IP also wants to further extend Failover, you also need to set failover_mode and the corresponding type. RACDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = rac-scan) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RACDB)

From this point of view, Oracle's plan is really elaborate. Let's take a look at MySQL's plan.

The distributed scheme makes MySQL look like a Swiss cow knife. For the requirements at the network level, it can be said that MySQL has no requirements. If you apply for one master and one slave, you only need 4 IP (master, slave, VIP,MHA_Manager (consider a manager node)), and one master and two slaves are five.

At this point, MySQL does not support the so-called load balancing. It can be diverted through front-end business, such as using middleware proxy, or continuous split. After reaching a certain granularity, the demand can be met by architecture design. Because logic-based replication is easy to expand, "one master, more than one slave" is very common, the cost is not high, the delay can not say no, but very low, can meet the vast majority of Internet business needs.

When it comes to the conditions that trigger MHA handover, from the network level, the following red dots are potential hidden dangers, some are network interruptions, some are network delays, and when a failure occurs, data or performance stability can be customized based on your own needs. From this point of view, there is a probability of data loss. It is definitely not a highly consistent lossless replication.

Looking at the two solutions as a whole, RAC is centralized sharing. In addition to sharing at the storage level, multicast at the network level will also increase the cost of communication between nodes. Therefore, RAC has a great demand for the network. If there is delay, it is very dangerous, and it will be very embarrassing if brain fissure occurs. MySQL MHA's scheme is distributed. To support a large number of environments, the cost of communication between nodes is much lower. However, from the perspective of data architecture, although it is not shared storage, the cost of storage is higher than that of RAC (not the price of storage, but the size of stored data) because of the replicated data distribution.

Is there anything you don't understand about the above simple comparison of the high-availability solutions of Oracle and MySQL? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.