MySQL performance tuning and architecture design--1th chapter MySQL Basic introduction

MySQL performance tuning and architecture design--1th chapter MySQL Basic introduction

Preface:
As one of the most popular open source database software, MySQL database software has been widely known. But in order to take care of the readers who are not familiar with MySQL, this chapter will make a brief introduction to MySQL. The main content includes MySQL each function module composition, each module works together the principle, the Query processing process and so on.

1. 1 MySQLServer Introduction

1.1.1 What is MySQL
MySQL is developed by MySQL AB (now owned by Sun, Sun has been acquired by Oracle) and is currently one of the most popular open source database management systems in the IT industry, and it is also a relational database management system that supports multi-threaded high concurrent multiuser.
MySQL database with its simple and efficient and reliable features, in the last few years from a little-known database system, into an IT industry is almost no unknown open source database management system. From a miniature embedded
System, to a small web site, to a large enterprise-class application, everywhere can be seen its presence. Why is an open-source database management system becoming so popular? Before I first contacted MySQL in 2003, I was very puzzled. Perhaps after I introduce you to the course of its development, the problem in my heart will disappear.

1.1.2 Difficult Birth
In 1985, a few like-minded lads in Sweden (led by David Axmark) set up a company that was the predecessor of MySQL AB. The company was not originally designed to develop database products, but it needed a database in the process of implementing their ideas. They want to be able to use open source products. But at that time there is no suitable choice, no way, then develop it yourself. At first, they simply designed a way to access data by using an indexed sequence, the predecessor of the Indexed sequential access method storage engine core algorithm, using ISAM to combine mSQL to achieve their application needs. In the early days, they were primarily providing data warehousing services to some of Sweden's largest retailers. In the process of system use, the combination of ISAM and mSQL is becoming more and more heavy with the increasing of data volume and the higher complexity of system. After analyzing the performance bottlenecks, they found that the problem was msql. Had to, they abandoned the msql, and re-developed a similar function of the data storage engine, which is the ISAM storage engine. You may have noticed that their main customer at the time was the data warehouse, and it should be easy to understand why, until now, MySQL is best at querying performance, rather than transactional processing (with third-party storage engines). The birth of the software, it is natural to give her a nice and easy to remember name. Today, MySQL AB still does not announce why the database system was named MySQL. It is rumored that MySQL was taken from the name of the daughter of Monty Widenius, one of the founders. Perhaps everyone would think that this is just my guess, not to disagree, in fact, it is not completely unfounded. You probably know that MySQL is recently developing a new generation of storage engine products to replace the MyISAM storage engine Maria, why is Maria? I am also interested in this issue, once and before MySQL CTO
David has communicated. The answer is that Maria was named after his little daughter. It seems that this is a custom for several MySQL founders to name their software. At the beginning of MySQL's birth, its function was very coarse, and the Business database management system which had been mature and stable for many years was completely unable to compare. The success of MySQL was very much the initial strategy that several founders adopted.

1.1.3 Seeking Development
When MySQL was born, it was the time when the internet began to grow at a high speed. MySQL AB meets the needs of Internet developers for database products by optimizing MySQL: Support for standardized query languages, efficient data access, no focus on transactional integrity, ease of use, and low cost. At that time, a large number of small companies are willing to use MySQL as a database application System database management system, so the number of MySQL users continue to grow, and further promote the continuous improvement and improvement of MySQL itself, into a very good virtuous circle. A reasonable grasp of the needs, accurate positioning of the target customers, for the development of the back of MySQL paved the way. We have seen that MySQL initially did not take the large enterprise management software database system to locate themselves, not all IT industry positioning as their target users, but the choice is not valued at that time a small number of web developers as their customers to focus on development. This approach may be worth learning from our IT business.

The rise of the 1.1.4 giants
It can be said that MySQL initially caught up with internet customers, creating today's great success of MySQL in the internet industry. Of course, MySQL's rapid development, but also inseparable from another very important factor, that is open source code. In 2000, MySQL released its source code and formally entered the open source world with the GPL (GNU general public License) license agreement. Although in the circumstances, open source is not so popular now, but that is the open source world began to really let most people accept and begin to praise the beginning stage. Of course, MySQL's success is not just because of these reasons, but we cannot deny that MySQL is the strategic nature of the strategy to allow MySQL to further expand their customer base on the way to the east. Since then the development of MySQL I think I do not need to continue to wordy, because basically can be from the official MySQL website (http://www.mysql.com) to get the corresponding answer.

1. 2 simple comparison of MySQL with other databases
We briefly introduced the evolution of MySQL, and learned the necessary conditions for MySQL to rise rapidly. Next, we make a basic use of MySQL in terms of functionality, performance, and its ease of use and other mainstream databases
Compare MySQL as a sufficient condition for the most popular open source database software of the moment.

1.2.1 Function Comparison
As a mature database management system, to meet a variety of business needs, the function will certainly be included in the key reference objects. MySQL Although in the early version of the function is very simple, can only do some very basic structured data access operations, but after years of improvement and refinement, has basically had all the common database management system required by the relevant functions.
MySQL basically implements most of the ANSI SQL 92 standards, and only a few parts that are not often used are not implemented. For example, with respect to field type support, another well-known open source database PostgreSQL supports the most complete type, while Oracle and some other business databases, such as DB2, Sybase, and so on, are relatively less than MySQL. This can be learned through the test report from the TCX Crash-me test suite. In terms of transactional support, although MySQL's own storage engine is not available, it has already implemented all of the four transaction isolation levels defined by the SQL 92 standard through the third-party plug-in storage engine InnoDB, but there may be some difference in how each implementation is implemented. This is rare in the current commercial database management system. For example, the well-known Oracle database has only implemented two of these (serializable and read commited), while PostgreSQL also supports four isolation levels.
However, there is a gap between MySQL and other databases in terms of programmable support, although the latest version of MySQL has started to provide some simple programmable support, such as starting to support Procedure, function, trigger, etc., but the supported features are relatively limited, Compared with other major commercial database management systems, there are still a large number of deficiencies. For example, Oracle has strong PL/SQL, there is a T-SQL Server, and PostgreSQL has a well-functioning pl/pgsql support.
Overall, although the MySQL database as a common database management system can not be compared with PostgreSQL for the time being, it is fully capable of meeting our general business needs and providing a strong enough service. And no matter what kind of database in the functional aspects are not able to claim that they are stronger than any other commercial common database management system, or even dare not to claim that they can own a database product all the features. Because each database management system has its own advantages, but also has its own limitations, which can only represent the direction of each product is different.

1.2.2 Ease of use comparison
Comparing the usability of the system, every user who has used MySQL can clearly feel the advantages of MySQL in this respect and other common database management systems. Especially for some large business database management systems such as Oracle, DB2, and Sybase, the ease of operation is obviously not at a level for ordinary users. MySQL has always been a simple and easy-to-use principle, it is this feature, attracting a large number of primary database users finally chose MySQL. It is also this batch of novice users, after a few years of growth, many have become advanced database users, but also has been accompanied by the growth of MySQL.
From the installation side, the MySQL installation package size is only about 100MB, these large commercial databases are not at the same order of magnitude. The ease of installation is much simpler than a commercial database such as Oracle, whether it is compiled with a compiled binary distribution package or a source code installation. From the database creation to compare, MySQL just need a simple CREATE DATABASE command, can complete the action of building the library in an instant, and compared to Oracle database, the creation of a database is simply a very large project. Of course, there are certain differences between the concepts of the database and cannot be denied.

1.2.3 Performance Comparison
Performance has always been a feature that MySQL prides itself on. In the process of testing the TPCC values of various databases by the authoritative third-party evaluation agencies, MySQL has always had excellent performance, and in all other commercially available general-purpose database management systems, only the Oracle database can be higher. As for the detailed performance data of various databases, I am not able to record here, we can fully through the online third-party evaluation agencies published data to understand the specific details.
MySQL has always adhered to a principle, that is, in the premise of ensuring sufficient stability, as far as possible to improve their processing capacity. In other words, in terms of performance and functionality, MySQL first considered the main features of the performance, MySQL wants to be a customer in order to meet 99% of the functional needs of the premise, spend most of the remaining energy to performance efforts, Instead of wishing to be a database product that is more powerful than any other database feature.

1.2.4 Reliability
About the reliability of the comparison, and there is not much detailed evaluation of comparative data, but from the current industry exchanges can be learned that several major commercial manufacturers of the reliability of the database is certainly not too many questionable. But as a representative of the open source database management system, MySQL also has a very good performance, and not as some people have doubts in the mind, because it is not provided by the commercial vendors, will not be stable enough not robust. From the current most popular Facebook such a large web site is the use of MySQL database, it can be seen that MySQL in the stability of reliability, and no more than our commercial manufacturers of products are not inferior. And in the top 10 of the world's largest web site, most of the business is running in the MySQL database environment, such as Yahoo, Google and so on.
In general, MySQL database has its own three principles in the development process: simple, efficient and reliable. From the simple comparison above, we can also see that in MySQL's own three principles, no one is not doing well. Moreover, although the function is not one of the three principles that MySQL itself pursues, but considering the rapid growth of the current user volume, the user needs more and more diversification, MySQL has to make a lot of effort in the function to constantly meet the new needs of customers. For example, the recent release of Eent Scheduler (similar to Oracle's job function), partition function, the self-developed Maria storage engine in the functional aspects of the expansion, Falcon storage engine support for transactions, etc. Has proved that MySQL has also begun to work tirelessly in terms of functionality. No one product can be perfect, nor can it be applied to all users. Only when we measure the characteristics of each product, we choose a product that is most suitable for ourselves.

1. 3 main scenarios for MySQL
The current MySQL user is said to have reached tens, many of which are enterprise-class users. Can be said to be the most popular open source database management system software now. No product can be omnipotent, nor can it be applied to all scenarios. So what scenario does MySQL apply to in what scenarios?

1. Web Site System
Web site, MySQL's largest customer base and the most important supporting force in MySQL's history, has been explained in the first MySQL server Introduction section.
MySQL is a Web site developer's favorite database management system, because the MySQL database installation configuration is very simple, the use of maintenance is not as complex as many large-scale commercial database management system, and excellent performance. Another very important reason is that MySQL is open source and fully free to use.

2. Logging System
MySQL database insert and query performance are very efficient, if the design is better, when using the MyISAM storage engine, the two can be unlocked, to achieve high concurrency performance. Therefore, MySQL is a great choice for systems that require a large number of inserts and query logging. such as processing user log, operation log, etc., are very suitable for the application scenario.

3. Data Warehouse System
With the rapid growth of data warehouse data now, we need more and more storage space. The increasing volume of data makes statistical analysis of data more and more inefficient and increasingly difficult. What to do? Here are a few of the main solutions, one is the use of expensive high-performance host to improve computing performance, high-end storage equipment to improve I/O performance, the results are ideal, but the cost is very high; the second is to improve overall computing performance and i/by replicating data to several inexpensive PC servers that use high-capacity hard drives o ability, effective, storage space has a certain limit, low cost; third, by splitting the data horizontally, using multiple inexpensive PC servers and local disks to hold the data, each machine has only one part of all the data, solves the problem of data volume, all PC server parallel computing, Also solve the problem of computing power, through the intermediate agent to allocate the operation of each machine task, can not only solve the computational performance problems and can solve the I/O performance problems, the cost is very low. In the above three scenarios, the second and third implementations of MySQL are
Have a greater advantage. Through the simple copy of MySQL function, can be very good to copy data from one host to another, not only in the LAN can be replicated, in the WAN same can. Of course, many people may say that other databases can do the same, not only MySQL has such a function. Sure, many databases can do the same, but MySQL is free, and most of the other databases are charged according to the number of hosts or the number of CPUs, and when we use a lot of PC servers, the license cost is quite staggering. The first scenario, basically all database systems can be implemented, but its high cost is not every company can afford.

4. Embedded System
The biggest limitation of the embedded environment to software system is that the hardware resource is very limited, and the software system running in the embedded environment must be a lightweight and low-consumption software. MySQL is very scalable in terms of resource usage, can be run in a resource-rich environment, or it can operate in very low-resource environments. It is a very suitable database system for the embedded environment, and MySQL has a version specifically for the embedded environment.

1. 4 Summary
From the initial birth, to the development of the most popular open source database management software, MySQL has gone through a long way, it is this unusual road, resulting in today's MySQL achievements. Through the information in this chapter, I think you should be more clear about MySQL most of the basic information, for the main features of MySQL, as well as the application scenario, have a preliminary understanding. In the following chapters we will cover these content in more detail.



Excerpt from: "MySQL performance tuning and architecture design" Jane Chaoyang

Reprint please specify the source:

Jesselzj
Source: http://jesselzj.cnblogs.com

MySQL performance tuning and architecture design--1th MySQL Basic introduction