One, Mysql ? is optimized when compile
If you distribute installation Mysql from source code,Should notice,The effect with compile a process to have to function of the following object program main,Compile means differently to may obtain similar target document,But function may differ very big,Because of this,Compiling installation Mysql to suit to choose carefully to compile option well the most possibly according to your applied type.This kind of custom-built Mysql can offer optimum behavior for your application.?
Skill:It is better to choose compile implement it is better to mix compile implement option,Such application can raise function 10-30% .(Mysql documentation says so) ?
1.1, use Pgcc (Pentium ? Gcc) is compiled implement ?
Should compile implement (Http://www.goof.com/pcg/ ) in the light of move in gallop the program on processor system undertakes optimizing,Compile Mysql source code with Pgcc,Overall performance can rise 10% .If your server is not to use,gallop of course processor,Need not use it,Because it is to be only,gallop the system designs.?
1.2, the character set that uses you to want to use only compiles Mysql ?The character set that Mysql looks premise differs for amounting to 24 kinds,The data in be inserted with the language of themselves for global user or examining a watch.Save a circumstance to fall however,Mysql installation is possessory these character set,Hot however,Best choice is to point to those who choose a kind of your need.Like,Prohibit removing all other character set beyond Latin1 character set:?
------------------------------------------------------------------------------?
%%26gt; . / Configure ? - With-extra-charsets=none ? [- - Other-configuration-options] ?
------------------------------------------------------------------------------?
1.3, compile Mysqld the static state to carry out file ?
Compile Mysqld the performance with the static state carries out a file and need not share a library to also can be obtained better.Through following options is being appointed when configuration,But the static state compiles Mysqld.?
------------------------------------------------------------------------------?
%%26gt; . / Configure ? - With-mysqld-ldflags=-all-static ? [- - Other-configuration-options] ?
------------------------------------------------------------------------------?
1.4, configuration example ?
Following configuration command is commonly used at improving performance:?
------------------------------------------------------------------------------?
%%26gt;cflags=%26quot;-o6 ? - Mpentiumpro ? - ? of Cxxflags=%26quot;-o6 of ? of Fomit-frame-pointer%26quot; ? Cxx=gcc - Mpentiumpro ? - Fomit-frame-pointer ? - Felide-constructors ? - Fno-exceptions ? - Fno-rtti%26quot; ? . / Configure ?- - Prefix=/usr/local ?- - Enable-assembler ?- - With-mysqld-ldflags=-all-static ?- - Disable-shared ?
------------------------------------------------------------------------------?
Two, adjust server ?
Ensure apply it is admittedly important to be compiled correctly,But the first pace that this just succeeds,The Mysql variable with numerous configuration is likewise right of the server run a crucial function normally.You can assign these variably existence configures a file in,With ensuring they all act well when starting Mysql every time,This configuration file is My.cnf file.?The example that Mysql had provided document of a few My.cnf,Can be in / find below Usr/local/mysqld/share/mysql/ catalog.These files name respectively for My-medium.cnf of My-small.cnf, ? , My-large.cnf and My-huge.cnf,Dimensions specification can configure a file in the description find in applicable systematic type caption.If be on the system that has quite little memory only,run Mysql,And just now and then with,So My-small.cnf will be idealer,Because it commands Mysqld uses least resource only.Similar,If you plan compose to establish supermarket of electronic business affairs,And the system has 2g memory,So you should use Mysql-huge.cnf document possibly.?
To use one of these files,You need to duplicate a file that fits requirement most,Incognito for My.cnf.You can choose a kind of limits of three kinds of action to use configuration document:?Global:Duplicate My.cnf file of the server / below Etc catalog,The variable action that this makes configuration file medium at overall situation,The Mysql database server that goes up to all servers namely is effective.
? Local:Duplicate My.cnf file [below Mysql-install-dir]/var/ catalog,Make My.cnf action at specific server.[Mysql-install-dir] states Mysql installs catalog.
? User:You can restrict action again at specific user,Duplicate My.cnf to fall to the root catalog of the user.?
These variable in how installing My.cnf after all?Say further,You can install which variable.Although what use variable relatively general to Mysql server,Of each variable and Mysql certain component has more specific concern.If variable Max_connects returns,fall in Mysqld category.Carry out following command to be able to know:?
------------------------------------------------------------------------------?
%%26gt;/usr/local/mysql/libexec/mysqld ?- - Help ?
------------------------------------------------------------------------------?
It shows many options and the variable related to Mysqld.You can be easily under word of this style or manner of writing find out variable:?
------------------------------------------------------------------------------?Option ? of ? of For of ? of Possible ? Variables- - Set-variable ? (- ? of O) ? Are
------------------------------------------------------------------------------?
Next you can install those variable in My.cnf as follows:?
------------------------------------------------------------------------------?? of Max_connections=100 of ? of Set-variable ? =
------------------------------------------------------------------------------?
The number of the biggest intercurrent join that it sets Mysql server is 100.Should ensure in My.cnf file [variable setting is inserted below Mysqld] caption.?
Three, watch type ?
Likelihood of user of a lot of Mysql is very open-eyed,Mysql offers 5 kinds of different watch types for the user really,Call Dbd, Heap, Isam, Merge and Myiasm.Dbd returns for clerical safety kind,And other to be not clerical safety kind.?
3.1, clerical safety ?Dbd ?The watch that watch of Berkeley ? Db(dbd) is processing of supportive general affairs,By Sleepycat software company (Http://www.sleepycat.com) is developed.It offers Mysql user to expect long already function - general affairs is controlled.Clerical control is an extremely valuable function in any database systems,Because they ensure,a group of commands are carried out successfully.?
3.2, ? of safety of blame general affairsHeap ?Heap watch is the list with the sharpest data is accessed in Mysql.Because they use memory to breath out rare index in one of dynamic memory,this is.If Mysql or server break down,another point is,Data will be missing.?
Isam ?The default that Isam watch is inchoate Mysql version expresses a kind,Till Myiasm development comes out.The proposal does not want reuse it.?
Merge ?Merge is an interesting new type,In 3.23.25 later appear.Watch of a Merge is the gather that an identical Myisam expresses actually,Amalgamative into a watch,Basically be for efficiency reason.Can raise efficiency of speed, search, repair efficiency to save disk space so.?
Myiasm ?
The default that this is Mysql expresses a kind.It is based on Iasm code,But a lot of expand usefully.The reason with better Myiasm:?Myiasm watch is less than Iasm to express,Use less resource so.?Myiasm watch is on different platform binary layer can transplant.?
Bigger key piles up dimension,Bigger key piles up upper limit.?
3.3, appoint watch type ?
The type that the watch specifies when you are founding a watch.Next exemples found a Heap to express:?
------------------------------------------------------------------------------?? of Type=heap of ? of Email_addresses of ? of Mysql%26gt;create ? Table (?
- %26gt; Null of ? of Not of Email ? Char(55) ? , ?
- %26gt; Null of ? of Not of Name ? Char(30) ? , ?
- %26gt; ? of Primary ? Key(email) ) ; ?
------------------------------------------------------------------------------?Bdb watch needs a few configuration to work,Refer to Http://www.mysql.com/doc/b/d/bdb_overview.html.?
3.4, more watch type ?
To make Mysql management job more interesting,The Mysql ? that is about to release 4 will offer two kinds of new watch types,Call Innobase and Gemeni.?
Four, optimize tool ?Mysql server itself offerred a few inside buy order is used at the help to optimize.?
4.1, Show ?
You may have fun at know Mysql server after all more what,Following command gives out a summary:?
------------------------------------------------------------------------------?? of Mysql%26gt;show ? Status;
------------------------------------------------------------------------------?
It gave out a quite long condition variable and the list that its are worth.The the time that the amount that some variable contained abort client, abort connective amount, join tries, biggest intercurrent join is counted and a large number of other and useful information.These information have value extremely to finding out systematic issue and small effect.?Show still can do more businesses.It can show the valuable information in be being expressed about log file, specific database, watch, index, process and attributive.Detailed sees Mysql manual.?
4.2, Explain ?
When you face Select sentence,How is command of Explain explanation Select handled.This indexes to deciding to whether should increase not only,And it is helpful to deciding how a complex Join is handled by Mysql.?
4.3, Optimize ?Optimize statement allows you to restore space and fragment of amalgamative data file,Lengthen to including after the watch of travel undertook be updated in great quantities and be deletinged,It is particularly important to such doing.Optimize works to be expressed at Myiasm and Bdb only at present.
...
