Be based on the implementation of the database group system of MySQL
Tuesday, March 03, 2009 by rain
The current situation of technique of group of the first database
At present database group system applies more successfully,Applied range is wider is:The Oracle9 of Oracle company and IBM company DB2.The technology that Oracle9 uses Shared-storage,The technology that DB2 chose Shared-nothing,Both has accident each.
The theoretical foundation of newest database group system is distributed computation,Distributing data to every node,All computational node are collateral process data,result collect.Such means is the most perfect undoubtedly.But still cannot realize all function at present.
The relevant data that asks referenced Oracle and IBM website to go up to the technology of Shared-storage and Shared-nothing.
The second at present database application state
At present database application state is divided roughly it is two kinds,The first kind is data bulk is under 100G,Database visit is frequent,The request is concentrated.The application that basically is Web APP type,For example:Website,Forum.The applies visit database characteristic of these Web APP types is:The visit is frequent,The inquiry that database per sec. wants to accept above thousands of times,Need often increases data,At the same time logarithm occupies answer speed the demand is higher.Another kind is the application that is used at data of history of scientific computation, memory,Data bulk often achieves hundreds of G.These characteristics that apply visit database are:Operate for inquiry more,Data is group by group, time, concentration enters a database,The record of the database is very much,Gathered much data,To the database answer speed to do not have too high demand.
The third reveals exposed problem
The first kind of application,Because the visit is more frequent,And to support more visits,Web Server used laden and balanced group commonly,But to the database,Because cannot realize group to operate,The request of per sec. increases ceaselessly,As the addition of server load,The rate that answers individual plea is slower and slower,If library file is bigger,Appear the time that lock up a watch still can appear to grow the business that waits for an influence to visit efficiency too when keeping an operation.
The second kind of application,Basically be data file too big,Handle data to need many time every time,If a statement needs clerical error,spend a few hours to come redo inquires.
How is the fourth solved
Above all ought to from statement of hardware, software, program, index, SQL these a few respects undertake optimizing,If insoluble still problem,The group that we are about to consider database system (collateral processing) .
To the first kind application,Move normally in database server,Below the case with not high load,Of the situation that uses pair of database systems or satisfaction.But after database system load is exorbitant,Can appear the time lengthen of the request that finish,The requirement time of short of system.Since because cross much request,load is,cause,We are adopted partake the means of the request,The request that lets one share goes visitting a server,The load of server of stage letting sheet is reduced,Solve a problem thereby.
To the second kind application,With respect to need distributed calculative system was solved,Average system is helpless.
The first kind of application of the fiveth needle to "Linux+Apache+PHP+MySQL" of the problem settle way
The settlement of a real case:
I encountered such problem between the job,The group that three machines that our Web Server is Linux+Apache+Php form,MySQL moves in SUN450,On the platform of 2G memory.Because the visit of WEB is measured in the height when almost full load runs,LoadAvg(is one minute in the process amount that is in Running condition) it is between 10-20,Report comes out even if many request was hanged when visitting a database,Bring about a request to was not finished,Next request comes in again,Final vicious circle.LoadAvg can rise in instantaneous violent wind to 800 above.Database there worse,LoadAvg is achieved 300 much,The line Cheng of the database is very much,CPU at condition of switch line Cheng,This moment unless Restart MySQL,Otherwise how won't good.Be opposite SQL statement is optimized the problem still cannot be solved very well after finishing,We increased server of a database,Through the data synchronism mechanism of MySQL,The data that lets two databases go up keeps synchronous,Revised one part to be able to happen only read the Php course that takes an operation,Make these program join additional a database,It is a laden depart to go out one part,The problem got the abecedarian is solved.But business is done later big,We increased much stage server again,Modified a lot of programs,Detached their logarithm occupies a library read take an operation,The server with different visit.
Of plan of the sixth MySQL-HA-Proxy put forward
The means implementation that adopts modification program departs systematic load,It is a very thorny issue,Project huge,And cannot make a mistake,Because besides advocate the server can be written, modification data,And the data that the server of other can pass data synchronism to update oneself only,If you undertook writing to those databases,operate so,The result will be disastrous.
If we can have sentence of SQL of sorting of a program,The type according to him (read take / write) ,The server that conveys to differ respectively,Return the result again next.The means of the PROXY that uses a kind of similar HTTP,Such kind that we do not need to adopt modification source program will divide a load,If again can according to the laden state of the server,The position that perhaps expresses (usable / the lock is decided) ,Will judge should request this to allocate which server,The result that revises source program to be able to be achieved than us then still is close friends.
How to correspond between the seventh MySQL Client and Server
Look about,Also did not find an article about Mysql communication agreement,Look analysed the source program of Mysql only.The code that looks for Mysql 3.23.49 then,Open Sniffer tool.The communication agreement of MySQL may have been changed for many times,Inside the version of 3.23.49,The version of communication agreement is actually 10.
Analysed communication agreement simply,Neat now as follows,Some places still are not very perfect,The code that because I do not have too much time to grind carefully really,reads Mysql,At present I understood these only.
Length of deflection area type (Byte) shows 0 HEAD Data Length 3 1 2 3 FLAG 1 =0 general communication ceases
=1 is multistage information
=2 attestation is returned
%26gt; 4 DATA CMD Code 1 of word of 2 paragraphs of ends 5 Message DataLength - 1
When FLAG=0, 2 when the definition of CMD Code and Message
The structural 00 condition of CMDCode type Message piles up deflection type Length(byte) 0 Affect Rows 2 Length(byte) of type of deflection of date of 0A server version Be in only just the Server on join when effective,Server can return the information 0 VersionString 8 end Of '\0' of section of a data immediately 8 Session ID 4 32bits 12 UnKnown 11 FF should appear mistake when return Length(byte) of information deflection type 0 ErrCode 2 2 ErrMsg END FE is multistage the end that information transmits is empty
The form that Client submits data to Server:
Length(byte) 0 HEAD Data Length 3 1 of deflection area type 2 3 Compressed 1 4 DATA Command ID 1 5 Command Data Data Length - 1
The specification of Command ID and Command Data:
0 COM_SLEEP of format of data of ID type 1 COM_QUIT NULL 2 COM_INIT_DB Database Name 3 COM_QUERY stand Query String 4 COM_FIELD_LIST table Name [128] Wildcard[128] 5 COM_CREATE_DB Database Name 6 COM_DROP_DB Database Name 7 COM_REFRESH options(bits) 8 COM_SHUT 12 COM_PROCESS_KILL sid[4] 13 COM_DEBUG NULL 14 COM_PING NULL 15 COM_TIME 16 COM_DELAYED_INSERT 17 COM_CHANGE_USER [user][passwd][db] 18 COM_BINLOG_DUMP 19 COM_TABLE_DUMP 20 COM_CONNECT_OUT
[1] [2] issues one page