Personal Experience: MySQL database optimization techniques LIBRARY
10 Jul
Posted by jianfu0226 as edu
11
First, we can and should optimize what »
Hardware
OS / software library
SQL Server (set up and enquiries)
Application programming interface (API)
Application
Second, optimizing the hardware
If you need a huge database table (> 2 G), you should consider the use of 64 of the hardware, such as Alpha, Sparc or upcoming IA64. MySQL internal use because a large number of integer 64, 64-bit CPU will provide better performance.
Large databases, optimizing the order is generally RAM, fast hard drive, CPU capacity.
More memory will be the most commonly used by the key code pages stored in memory can speed up the key code updates.
If the non-use of security services (transaction-safe) the table or tables and want to avoid a long document checks, a UPS will be able to let in a power failure, the system is safe to shut down.
The database stored in a dedicated server systems, should consider 1 G Ethernet. Delay and throughput is equally important.
Third, optimizing disk
For the systems, procedures and temporary files with a dedicated disk, if it is a lot of modification, will be updated logs and transaction log on dedicated disk.
Seek low on disk database is very important. With the large table, you can estimate you will need to log (rows) / log (index block length / 3 * 2 / (Key length data pointer length)) 1, to find a line can be found. For 500,000 to the table, index Mediun int type out the need to log (500000) / log (1024 / 3 * 2 / (3 2)) 1 = 4, seeking. The index needs 500000 * 7 * 3 / 2 = 5.2 M space. In fact, the majority block will be cached, so probably only need 1-2 seeking.
But for writing (as above), you will need four times a request seeking to find where to store the new key code, and generally to the 2nd seeking to update the index and write and his party.
For very large database, your application will be seeking disk speed limit, as data showed an increase of N log N-level incremental data.
The database and table in different points on the disk. In MySQL, you can use this symbolic link.
Out of the disk (RAID 0) will increase the throughput of reading and writing.
With the mirror out of the (RAID 0 1) will be more security and increase the throughput of reading. Write the throughput will be lower.
Not on the provisional document, or can easily rebuild the data of the use of disk mirroring or RAID (in addition to RAID 0).
On Linux, in guiding the disk when using a command hdparm-m16-d1 to enable reading and writing at the same time a number of sectors and DMA function. This will improve the response time of 5 to 50 percent.
On Linux, using async (default) and noatime mount disk (mount).
For some specific applications, the use of certain forms of memory disk, but usually do not need.
Fourth, optimize the operating system
Do not exchange zone. If memory, adding more memory or configuration of your system uses less memory.
Do not use NFS disk (NFS will be locked).
MySQL server system and increase the number of open files. (Safe_mysqld script by adding ulimit-n #).
The process and increase the number of threads.
If you have a relatively small number of large table, told the paper file system should not be damaged in different tracks on the (Solaris).
Support the use of large file system (Solaris).
Choose to use either file system. Reiserfs on Linux for the open, read and write very fast. Within seconds, the document inspection.
5, the choice of application programming interface
PERL
Can be on different operating systems and databases between the transplant.
Suitable for rapid prototyping.
Should use DBI / DBD interface.
PHP
Than PERL to learn.
PERL less than the use of the resources.
PHP4 available through the upgrade to a faster pace.
C
MySQL’s native interface.
Faster and give more control.
Low-level, we must pay more.
C
A higher level, give you more time to prepare applications.
Is still in development
ODBC
Run on Windows and Unix.
Almost in a different SQL server transplantation.
Slower. MyODBC simply through drivers, than the primary interface with the slow 19 percent.
There are many ways to do the same thing. It is very difficult, as many as ODBC driver running in different areas have different mistakes.
Problems piled up. Microsoft will change the interface accidental.
An uncertain future. (Microsoft OLE more respected rather than ODBC)
ODBC
Run on Windows and Unix.
Almost in a different SQL server transplantation.
Slower. MyODBC simply through drivers, than the primary interface with the slow 19 percent.
There are many ways to do the same thing. It is very difficult, as many as ODBC driver running in different areas have different mistakes.
Problems piled up. Microsoft will change the interface accidental.
An uncertain future. (Microsoft OLE more respected rather than ODBC)
JDBC
In theory can be on different operating systems when according to the Inter-transplantation.
Can run in the web client.
Python and other
May be good, we do not have them.
6, and optimize applications
Should focus on solving the problem.
In the preparation of application, should decide what is most important:
Speed
The operating system portability
SQL server portability
Continued use of the connection. .
Application of the data in the cache to reduce the load SQL Server.
Not for use in the columns do not need.
Do not use SELECT * FROM table_name …
All parts of the test applications, but will most likely focus on the worst of a reasonable load test the overall application. Through a modular way, you should use a fast-track “dumb module” to find alternative bottlenecks, and then easily identified under a bottleneck.
If a batch of substantial changes to the use of LOCK TABLES. For example, or multiple UPDATES DELETES together.
7, should the use of portable applications
Perl DBI / DBD
ODBC
JDBC
Python (or other common SQL interface language)
You should use only exists in all or in the purpose of SQL Server can easily use other structural simulation of SQL structure. [url] www.mysql.com Crash-me on the page can help you. [/ url]
For the operating system / SQL server package prepared by the lack of procedures to provide the function.
8, if you need a faster pace, you should:
Identify bottlenecks (CPU, disk, memory, SQL server, operating system, API, or application) and concentrate on solving.
Used to give you faster speed / flexibility of the expansion.
SQL server in order to gradually understand your question for possible use of the fastest SQL structure and to avoid bottlenecks.
Table optimize the layout and enquiries.
The use of copy to get a faster option (select) speed.
If you have a slow Internet connection database, using compressed client / server agreement.
Do not be afraid of when the first version of transplantation can not be perfect, you solve the problem, you can always optimize it in the future.
9, optimize MySQL
The selection of compilers and compiler options.
In your system to find the best activate the option.
Reading the MySQL reference manual and read Paul DuBios the “MySQL” a book. (Chinese version has been - Yi Zhu)
More use of EXPLAIN SELECT, SHOW VARIABLES, SHOW STATUS and SHOW PROCESSLIST.
Optimization of understanding for the working principle.
Optimization table format.
Protect your table (myisamchk, CHECK TABLE, OPTIMIZE TABLE)
MySQL use the extension to allow completion of all fast.
If you pay attention to the many occasions you will need certain functions, the preparation of MySQL UDF function.
Do not use table-or out-GRANT, unless you really need.
Buy MySQL technical support to help you solve the problem:)
10, compile and install MySQL
In your system by selecting the best possible compiler, you usually get 10-30 percent of the performance improvement.
In Linux / Intel platform, with pgcc (gcc optimized version of the Pentium chip) compile MySQL. However, the binary code will only run on the Intel Pentium CPU.
For a specific platform, the use of MySQL reference manual on the recommendation of the optimization options.
In general, to specific CPU the original compiler (such as the Sparc Sun Workshop) than gcc should provide a better performance, but not always the case.
You will use the character set compile MySQL.
Mysqld generate static compiler of the implementation of paper (used - with-mysqld-ldflags = all-static) and used strip sql / mysqld finishing the implementation of the final document.
Pay attention to, since the MySQL extension of the non-use of C, non-extended support compile MySQL will win huge performance improvement.
If the original operating system support thread, the use of original thread (rather than mit-pthreads).
MySQL benchmark used to test the final binary code.
11, and safeguarding
If possible, occasionally running the OPTIMIZE table, this update of a large number of longer lines is very important.
Myisamchk-a chance to update the code in the table of key statistics. Remember to do before switching off MySQL.
If there is debris documents, may be worth all the documents will be copied to another disk, remove the disk and copy the original document back.
If having problems with myisamchk or CHECK table checklist.
With mysqladmin-i10 precesslist extended-status monitoring the status of MySQL.
Using MySQL GUI client, you can in different window monitoring process and the status of the list.
Use mysqladmin debug access to locked and performance information.
[#2: Edit Options>MightyAdsense>Adsense Code]
Mony people thinks me to be good students,but in fact i am a extreme asian lady boys。Do you believed?
Long long ago,I really a good student at school in asian。But one day,my deskmate violented me。I very abhor he,even plan to kill he.
From this events,I begin to be extreme。
[#3: Edit Options>MightyAdsense>Adsense Code]
Bachelor presidentIn is respectely in the world!But why?
Craig Broadbear - President Bachelor of Applied Science in Comp. Sci.RMIT Australia.
15 years software development experience.
Jenny Ballard - Vice President Operations.
Bachelor of Science Monash University Australia.
18 years software development and project management experience.
Paul Jones - Vice President Research and Development.
Bachelor of Applied Science in Comp. Sci.
RMIT Australia.
15 years software development experience.
Above the people is respectely,it is the reason.
I like the song(kevin michael if i ain’t got you),so the lyrics is under:
All I ever wanted was to love you
Girl I never meant to cause you pain
And I only have myself to blame
Now you’re gone and I can’t stop thinking of you
Every time I hear your name
I think of what I’ve done again and again
Chorus
See I’m all alone tonight with just a memory
And baby you’re a million miles away
I can’t take another day
If I ain’t got you, I ain’t got anything
It just don’t feel the same, no
If I ain’t got you, oh
And if I ain’t got you, I ain’t got anything
Can’t even feel the pain
No, if I ain’t got you
(You, you, you, you,) x4
I remember when we were together
I remember how you used to say
There was nothing I could do to turn you away
I know, I know that things have changed forever
When I look into your eyes
All I see now is the tears in your eyes
Chorus
I’m all alone tonight with just a memory
And baby you’re a million miles away
I can’t take another day
If I ain’t got you, I ain’t got anything
It just don’t feel the same, no
If I ain’t got you, oh
And if I ain’t got you, I ain’t got anything
Can’t even feel the pain
No, if I ain’t got you, you
If I could I would pick up the pieces of your broken heart
And put it back together
And I would, I would hold you in my arms until the hurting stops
And stay right here forever
All I ever wanted was to love you
Girl I never meant to cause you pain
And I only have myself to blame
Chorus
If I ain’t got you, I ain’t got anything
It just don’t feel the same, no
If I ain’t got you, oh
And if I ain’t got you, I ain’t got anything
Can’t even feel the pain (I need ya baby, want you baby)
No, if I aint got you (I Gotta have you baby)
If I ain’t got you, I ain’t got anything
It just don’t feel the same, no (It doesn’t feel the same without you)
If I ain’t got you, oh
And if I ain’t got you, I ain’t got anything
Can’t even feel the pain
No, if I aint got you
Walther PPS is very dangers!I don’t know how to slide resease problems.But the problems is very agony.If you is me,what do you do?
So we all like walther pps,it is escellent.Slide release problems to let us worried.
My gold!Walther pps slide release problems,please you to remember it!
panasonic portable cd player with mp3 cd mortal kombat theme mp3 download sound byte mp3 reveille look at me now mp3 morning musume mp3s creative 420gb zen nano plus mp3 player apple 1 gb ipod shuffle mp3 player review rupee tempted to touch mp3 latest free hindi mp3 songs
Myspace comments is my best likest.But much friends’s myspce comments is few.Special about new year myspqce comments is uncommon。
So,I want to provide free new year myspace comments to all friends。If you download this myspace comments,please reserve the copyright，thanks!
Candy: 50 Cent Candy Shop, Candy Samples,Free M&M Clipart
coloring in candy divinity candy cotton candy history. 50 cent candy shop mp3, i want candy candy cane. candy stripers, candy paint. milky way candy bar. twix candy bar twix candy bar scripture candy, candy wedding favors for cheap prices eye candy women. wholesale candy distributors wedding bridal baby shower candy wrappers favors cute. marcy playground- sex and candy chocolate candy skulls candy man aqua wholesale bulk candy, traditional mexican candy, free candy wrappers - candy and sweets daily candy i smell sex and candy. bill murray and john candy, see’s candy candy kids, chocolate candy bars candy star. wedding candy favors, candy maker harry kid in a M&M candy store, snickers candy. sees candy ebony candy candy blonde. candy samples tits. peppermint candy. mars candy, s wedding chocolate favors wrapped custom candy bars - bratz candy wrapper bratz candy wrapper rock candy candy wrapper purse chocolate for candy - candy lollipop. candy and candy interior design candy shop - 50 cent, candy clipart, the freshmaker candy - free m&m’s clipart. john candy. candy coated teens. dots candy. candy flowers. claddagh chocolate candy molds wedding favors Get here for your cheapest and fastest way to order Candy right from your seat, deep fried candy bars. candy boxes candy morrison gourmet candy stores the big rock candy mountains candy panties candy grams, list of candy names. candy bouquets, candy chocolate wedding favors, wedding candy bar wrappers favors candy direct, life savers candy - the candy shop, candy wrappers mexican candy importer hard to find candy italian candy, sex and candy lyrics. vero mexican candy, mexican spicy candy, sour candy’s clipart, billiards candy pool stand. candy bar wrappers, hard candy cameo candy, maple sugar candy.
Getting married in the Cayman Islands is fast and easy with Heart of Cayman Weddings. Couples can marry the same day they arrive in the Cayman Islands, including those arriving by cruise ship. There is no longer a 72 hour waiting period, unlike the other popular Caribbean Wedding Destinations. All packages can be easily customized to include transportation to and from the cruise ship pier.
[marriage requirements in grand cayman islands]
massanutten waterpark jonathan taylor thomas nude l.o.v.e music video gay marriage in vermont jefferson community college class schedule buyers associations pornstars wrestle senior citizen sex condos in massachusetts 8 button dress shirt
massanutten waterpark jonathan taylor thomas nude l.o.v.e music video gay marriage in vermont jefferson community college class schedule buyers associations pornstars wrestle senior citizen sex condos in massachusetts 8 button dress shirt!
So please getting married in grand cayman islands!
free pictures of tax forms, north carolina tax forms forms for filing irs tax returns tennessee tax forms. federal tax forms. w-4 tax forms louisiana state tax forms. blank new jersey tax forms - ontario tax forms public libraries tax forms availability. You even can order Tax Forms on line, pennsylvania estate tax forms 2005 deductions tax forms irs, irs tax forms information 2006 individual tax forms dor colorado tax forms - ky state tax forms, order tax forms, downloadable tax forms nc tax forms, north carolina state tax id forms irs 2005 tax forms. sales tax exemption forms free tax forms montana income tax forms 2005 income tax forms irs - corporate income tax return forms, tax payer forms, tax forms 2007 schedule c. nebraska government colorado income tax forms, virginia state tax forms irs business tax forms, free downloadable tax forms. kentucky motor carrier tax forms. new mexico tax forms maryland tax forms, state of connecticut tax forms, virginia tax forms - texas sales tax forms. az tax forms, dc tax forms 2006 irs tax forms
california state tax forms
501 c 3 irs tax forms employers quarterly tax witholding forms, free 2006 california state tax forms. nc state department revenue tax forms nc tax forms - sick pay - download 2006 md state income tax forms new york state tax forms. 2007 california tax forms. old tax return forms, ct tax forms. old irs tax forms - nys sales tax forms - employee tax forms melbourne tax forms 4652 sap minnesota tax forms. new york state sales tax forms. irs tax forms w4 us tax forms texas state comp public accounts assistance tax forms. federal estate tax and forms. This site is full of information of Tax Forms irs tax forms california intangible tax forms. cwv state corporate tax forms, tax forms needed for 1031 exchange 2007 us tax forms japanese tax forms, pa local tax forms. state of indiana tax forms 2007 retired income tax forms new jersey sales tax forms. utah state tax forms how to file1099 tax forms free printable tax forms, va tax forms fillable tax forms tax forms for a business virginia state income tax forms, irs california state tax forms sap tax depreciation forms. new jersey state tax forms, massachusetts income tax forms, Useful notes, links, advises, online help and many other about Tax Forms.
pp is proudly powered by PPMM
