Mysql optimization 2001-02-06 5:55
Publisher: NetBull
(This article is a speech at the OREILLY OPEN SOURCE CONVENTION 2000 conference)
[子 编译 整]]
First, can we and should you optimize?
hardware
Operating system / software library
SQL server (settings and queries)
Application Programming Interface (API)
application
-------------------------------------------------- ------------
Second, optimize hardware
If you need a huge database table (> 2G), you should consider using a 64-bit hardware structure, like Alpha, SPARC, or the upcoming IA64. Because the internal 64-bit integers inside the MySQL, the 64-bit CPU will provide better performance.
For large databases, optimized order is generally RAM, fast hard drive, CPU capabilities.
More memory can accelerate the update of keywords by storing the most commonly used keycode page in memory.
If you do not use a table or a big table or a large table and you want to avoid long file checking, a UPS will be able to shut down the system security when the power is faulty.
For data stocks in a system of dedicated servers, 1G Ethernet should be considered. The delay is equally important to throughput.
-------------------------------------------------- ----------------
Third, optimize disk
To be equipped with a dedicated disk for the system, programs, and temporary files. Low find time is very important to database disks. For the big table, you can estimate that you will need log (number of lines) / log (index block length / 3 * 2 / (key code length data pointer length) 1 time to find a line. For columns with 500,000 rows, indexing Mediun int type columns require log (500000) / log (1024/3 * 2 / (3 2)) 1 = 4 times. The above index requires a space of 500000 * 7 * 3/2 = 5.2m. In fact, most blocks will be cached, so it is probably only 1-2 times. However, for write (as above), you will need 4 seek requests to find where to store new keys, and generally find two times to update the index and write a line. For very large databases, your application will be limited by the disk seeking speed, with the increase of the amount of data in N log n data level increments. Summary of databases and devices on different disks. In MySQL, you can use symbolic links for this. The list of disks (RAID 0) will increase the throughput of read and write. A mirroring (RAID 0 1) will be safer and increased throughput. The written throughput will be reduced. Do not use mirrored or raid (except RAID 0) where the data is located or the data that can be easily reconstructed. On Linux, use command hdparm -m16 -d1 to the disk when booted to enable both multiple sectors and DMA functions while reading simultaneously. This can increase the response time by 5 to 50%. On Linux, use Async (default) and noAAtime mount disks (MOUNT). For some specific applications, you can use memory disks for certain specific tables, but usually don't need it.
-------------------------------------------------- -------------
Fourth, optimize the operating system
Do not exchange zones. If memory is insufficient, add more memory or configure your system to use less memory. Do not use NFS disks (there will be a problem with NFS locks). Increase the number of open files for the system and the MySQL server. (Add ulimit -n # in the SAFE_MYSQLD script). Increase the process of the system and the number of threads. If you have a relatively few big table, tell the file system not to break the file on a different track (Solaris). Use the file system that supports large files (Solaris). Select which file system used. Reiserfs on Linux is open, and read and write is very fast. Document check is only a few seconds. -------------------------------------------------- ----------------
V. Select App Programming Interface
Perl can be transplanted between different operating systems and databases. Suitable rapid prototype. You should use the DBI / DBD interface. PHP is easy to learn than Perl. Use less resources than Perl. Fast speed can be obtained by upgrading to PHP4. C MySQL's native interface. Faster and give more control. Low-level, so there must be more. C is higher, give you more time to write applications. ODBC is still running on Windows and UNIX in the development. Almost migrate between different SQL servers. Slower. Myodbc is just a simple straight-through driver, which is 19% less than the native interface. There are many ways to do the same thing. It is difficult to run like a lot of ODBC drivers, and there are different errors in different areas. The problem is pile. Microsoft occasionally changes the interface. Unclear future. (Microsoft More Special OLNODBC) ODBC is running on Windows and UNIX. Almost migrate between different SQL servers. Slower. Myodbc is just a simple straight-through driver, which is 19% less than the native interface. There are many ways to do the same thing. It is difficult to run like a lot of ODBC drivers, and there are different errors in different areas. The problem is pile. Microsoft occasionally changes the interface. Unclear future. (Microsoft More Special OLN OLE, OLNC) JDBC is theoretically transplanted in different operating systems. You can run on the web client. Python and other may be good, we don't need them.
-------------------------------------------------- -------------
6. Optimize application
It should be concentrated to solve the problem. When writing applications, what is the most important: the portability of the portability SQL server between speed operating systems is continuously connected. Cache the data in the application to reduce the load of the SQL server. Do not query the columns in the application. Do not use the Select * from Table_name ... test all parts of the application, but places most energy into the test of test under the worst and reasonable load. By performing in a modular manner, you should be able to replace the bottleneck found in a fast "dummy module", and then easily log out of the next bottleneck. If you make a lot of modifications in a batch, use Lock Tables. For example, multiple Updates or deletes are set together.
-------------------------------------------------- -------------
Seven, it should be used in portable applications
Perl DBI / DBD ODBC JDBC Python (or other language with universal SQL interface) You should use only SQL configurations that exist in all purposes or can easily use other configurations. The Crash-ME page on www.mysql.com can help you. Write a packager for the operating system / SQL server to provide missing features.
-------------------------------------------------- -----------
8. If you need a faster speed, you should:
Find a bottleneck (CPU, disk, memory, SQL server, operating system, API, or application) and concentrate on resolution. Use it to give you faster / flexibility extensions. Gradually understand the SQL server so that you can use the possible SQL structure for your problem and avoid bottlenecks. Optimize table layout and query. Use replication to get a faster selection (SELECT) speed. If you have a slow network connection database, use the Compressed Customer / Server Protocol. Don't be afraid that the first version of the app cannot be perfectly transplanted. When you solve the problem, you can always optimize it later. -------------------------------------------------- -----------
Nine, optimize mysql
Select compiler and compile options. Bit Your System is looking for the best startup options. Read the mysql reference manual and read the book of "MySQL" in Paul Dubios. (Existing - Translation Note) Multi-use Explain Select, Show Variables, Show Status, and Show ProcessList. Understand the working principle of the query optimizer. Optimize the format of the table. Maintain your table (MyiSamchk, Check Table, Optimize Table) uses MySQL extension to make everything complete. If you noticed that you will need some functions in many cases, write the MySQL UDF function. Don't use a table-level or column grade, unless you really need it. Buy mysql technical support to help you solve the problem :)
-------------------------------------------------- ---------
Ten, compile and install MySQL
You can usually achieve 10-30% performance improvement through your system. On the Linux / Intel platform, compiled mysql with PGCC (GCC Pentium Chip Optimization). However, the binary code will only run on the Intel Pentium CPU. For a specific platform, use the recommended optimization options in the MySQL reference manual. Generally, the native compiler of a particular CPU (such as SUN Workshop) should provide better performance than GCC, but not always. Compile MYSQL with the character set you will use. Static compile to generate mysqld execution files (with -with-mysqld-ldflags = all-static) and use strip sql / mysqld to organize the final execution file. Note that since MySQL does not use C extensions, it will have a huge performance improvement without extension support compiling MYSQL. If the operating system supports the original thread, use the native thread (without mit-pthreads). Test the final binary code with MySQL benchmark.
-------------------------------------------------- ----------------
Eleven, maintenance
If possible, I occasionally run Optimize Table, which is very important to the large number of updates. Occasionally update the key code distribution statistics in the table with myisamchk -a. Remember to turn off MySQL before doing. If there is a debris, it may be worth copying all files to another disk, clear the original disk and copy the file. If you encounter problems, check the table with Myisamchk or Check Table. Use mysqladmin -i10 precessList Extended-status to monitor the status of MySQL. With the MySQL GUI client, you can monitor your processes and status in different windows. Use mysqladmin Debug to get information about locking and performance.
-------------------------------------------------- ---------------
Twelve, optimize SQL
Yang SQL is long, and other things will be done. Use SQL servers to do:
Find out the WHERE clauses. JOIN Table GROUP BY ORDER BY DISTINCT Do not use SQL to do: inspection data (such as date) becomes a calculator skill:
Use keywords wisely. Key code is suitable for search, but it is not suitable for the insert / update of the index column. Keep data as a database third paradigm, but don't worry about redundant information or this if you need faster speed, create a summary table. Don't make Group By on a big table, the contrary to create a summary table of a big table and query it. Update Table Set Count = Count 1 Where key_column = constant is very fast. For large tables, perhaps it is best to generate a summary table rather than keep the summary table. Take advantage of the default value of INSERT.
-------------------------------------------------- ----------------
Thirteen, the speed difference of different SQL servers (in seconds)
2,000,000 rows by reading the key code: NT Linux mysql 367 249 mysql_odbc 464 db2_odbc 1206 informix_odbc 121126 ms-sql_odbc 1634 oracle_odbc 20800 solid_odbc 877 sybase_odbc 17614 350768 insert rows: NT Linux mysql 381 206 mysql_odbc 619 db2_odbc 3460 informix_odbc 2692 ms-sql_odbc 4012 oracle_odbc 11291 Solid_odbc 1801 Sybase_ODBC 4802
In the above test, MySQL configures 8M cache operation, and other databases are run by default.
-------------------------------------------------- ---------------
14, important mysql startup options
BACK_LOG If you need a lot of new connections, modify it. Thread_cache_size If you need a lot of new connections, modify it. The key_buffer_size index page can be set to be large. BDB_CACHE_SIZE BDB Table Use Record and Keys Cache. Table_Cache If there are a lot of tables and concurrent connections, modify it. DELAY_KEY_WRITE If you need to cache all keysses, set it. Log_slow_queries find out the query that needs to spend a lot of time. MAX_HEAP_TABLE_SIZE Used for Group By Sort_Buffer for ORDER BY and Group BYIISAM_SORT_BUFFER_SIZE Used for REPAIR TABLE JOIN_BUFFER_SIZE When using a non-key connection.
-------------------------------------------------- ----------------
Fifteen, optimization table
MySQL has a wide range of types. You should try to use the most efficient type for each column. Analyse procedure can help you find the best type of table: select * from table_name procedure analyse (). For columns that do not save NULL values, use Not Null, this is especially important for you to index. Change the ISAM type table to Myisam. If possible, create a table with a fixed table. Don't index what you don't want to use. Use MySQL to inquire the facts of an indexed prefix. If you have index index (a, b), you don't need an index on A. Not creating an index on the long char / varchar column, and only one prefix ranked is saved to save storage. Create Table Table_Name (Hostname Char (255) Not Null, Index (HostName (10))) Use the most effective table format for each table. The columns saved in different tables should have the same definition and have the same column name. -------------------------------------------------- ----------------
16, how to store data times of mysql
The database is stored in a directory. Table is stored. Columns are stored in the file in a gear or fixed length format. For the BDB table, the data is stored in the form of a page. Support for memory-based tables. Databases and tables can be connected with symbols on different disks. On Windows, MySQL supports the connection database for the .sym file internal symbol.
-------------------------------------------------- ---------------
Seventeenth, MySQL table type
HEAP Table: The table of fixed lecture is only stored in memory and indexes with a Hash index. ISAM table: Early B-Tree table format in MySQL 3.22. Myiasm: The new version of the IASM table has the following extensions: portability of binary hierarchies. NULL column index. There are fewer debris than the ISAM table. Support big files. Better index compression. Better key statistical distribution. Better and faster auto_increment processing. Berkeley DB (BDB) table from Sleepcat: Transaction Security (with Begin Work / Commit | ROLLBACK).
-------------------------------------------------- -------------
Eighteen, MySQL line type (special refers to IASM / Myiasm table)
If all columns are fixed-length formats (no varchar, blob, or text), MySQL will create a table in a fixed-length table format, otherwise the table is created in dynamic length format. The fixed length format is much faster than the dynamic length format. The dynamic length line format generally takes up less storage space, but if the table is frequently updated, a debris is generated. In some cases, it is not worth transferring all varchar, blobs, and text columns to another table, just get the faster speed on the primary table. With MyiasMCHK (for ISAM, PACK_IASM), you can create a read-only compression table, which makes the disk usage, but when using a slow disk, this is very good. Compressed tables fully utilize log tables that will not be updated
-------------------------------------------------- ---------------
19, MySQL cache (all thread sharing, one-time assignment)
Key code cache: key_buffer_size, default 8M. Table Cache: Table_Cache, default 64. Thread cache: thread_cache_size, default 0. Host name cache: You can modify it at compile time, default 128. Memory mapping table: currently only for compression tables. Note: MySQL does not have a high speed cache and allows the operating system to process. -------------------------------------------------- ----------------
Twenty, mysql cache variable (non-shared, on-demand assignment)
Sort_buffer: ORDER BY / GROUP BY RECORD_BUFFER: Scan table. JOIN_BUFFER_SIZE: Non-bond Myisam_Sort_Buffer_Size: Repair Table Net_Buffer_Length: For read SQL statements and cache results. TMP_TABLE_SIZE: The HEAP table size for temporary results.
-------------------------------------------------- ---------
21, mysql table cache work principle
Open instances of each Myisam table use an index file and a data file. If the table is used by two threads or two times in the same query, MyiaSM will share the index file but open another instance of the data file. If all tables in the cache are in use, the cache will temporarily increase the size than the spectrum. If so, the next release will be closed. You can check if the MySQLD's OPENED_TABLES variable is checked whether the table cache is too small. If this value is too high, you should increase the table cache.
-------------------------------------------------- ----------------
Twenty-two, mysql extension / optimization - provide faster speed
Use an optimized table type (HEAP, MYIASM or BDB table). Use the optimized column for data. If you may use a long line. Use different lock types (select high_priority, insert low_priority) auto_increment replace (Replace Into Table_name Values (...) Insert Delayed Load Data Infile / Load_File () uses multi-line insert once insert multiplexes. Select Into Outfile Left Join, Straight Join Left Join, combined with is null order by use keycase in some cases. If you only query the column in an index, only the index tree is used to solve the query. The joint is generally faster than the child (this is also true for most SQL servers). Limit Select * from table1 WHERE A> 10 LIMIT 10, 20 DELETE * From Table1 Where A> 10 LIMIT 10 Foo In (constant list) Highly optimized. GET_LOCK () / release_lock () Lock Tables INSERT and SELECT can run at the same time. The UDF function can be loaded into a running server. Compress read only tables. Create Temporary Table Create Table .. SELECT MYIAASM table with RAID options splits files into a lot of files to break through 2G restrictions of certain file systems. DELAY_KEYS replication function
-------------------------------------------------- ----------------
Twenty-two, when mysql uses indexes to a key code>,> =, =, <, <=, if null and between
Select * from table_name where key_part1 = 1 and key_part2> 5;
Select * from table_name where key_part1 is null; when using the LIKE that does not start with wildcard
Select * from table_name where key_part1 Like Jani% is extracted from another table when connecting
Select * from T1, T2 where t1.col = t2.key_part identifies the max () or min () value of the specified index
Select min (key_part2), max (key_part2) from table_name where key_part1 = 10 A key code prefix using Order by or group by
Select * from fo Order by key_part1, key_part2, key_part3 is part of the key code in all columns in the query
SELECT key_part3 from table_name where key_part1 = 1
Twenty-three, when Mysql does not use indexes
If Mysql estimates that it will might be more fast than the scanning table, it is not used. For example, if Key_Part1 is evenly distributed between 1 and 100, the following queries are not very good: select * from table_name where key_part1> 1 and key_part1 <90 If you use the HEAP table and you don't have to search all keycort parts. Use the Order by on the HEAP table. If not the first part of the key code
Select * from table_name where key_part2 = 1 If you start with a wildcard, Like
Select * from table_name where key_part1 like% JANI% Search for an index and do ORDER BY in another index
Select * from table_name where key_part1 = # ORDER by Key2
-------------------------------------------------- -------------
Twenty-four, learn to use explain
For each, you think too slow query uses explain!
MySQL> Explain Select T3.DateOfaction, T1.TransactionID -> from T1 Join T2 Join T3 -> WHERE T2.ID = T1.TransactionID and T3.ID = T2.GroupID -> Order by T3.DateOfaction, T1.TransactionId; ----- ---- ------------- - ---------- -------- ------------ | Table | TYPE | POSSIBLE_KEYS | Key | Key_Len | Ref | Rows | Extra | ---- ---- ------- ---- - ------- -- ----- -------------- | T1 | All | Null | NULL | NULL | NULL | 11 | Using Mport | | T2 | Ref | ID | ID | 4 | T1.TRANSACTIONID | 13 | | | T3 | EQ_REF | PRIMARY | PRIMARY | 4 | T2.GROUPID | 1 | ------- -------- --------------- ------- ------ --- ----------- ALL and range type Tips prompts a potential problem.
-------------------------------------------------- ---------------
Twenty-five, learn to use Show ProcessList
Use Show ProcessList to find what is doing: -- ------- --------- -- --------- ------ -------------- ---------------------------------------------------------------------------------------------------- --------- | ID | User | Host | DB | Command | Time | State | INFO | ---- ------- ------- - ---- --------- ------ ---------------------- --------------------------- | 6 | MONTY | Localhost | BP | Query | 15 | Sending Data | Select * from station, station As S1 | | 8 | MONTY | Localhost | | Query | 0 | | Show ProcessList | ---- ------- --------- ---- --------- ------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------
Use Kill to kill the thread with kill in mysql or mysqladmin. -------------------------------------------------- ------------------------------
Twenty-six, how to know Mysql solve a query
Run the item column command and try to understand its output: show variables; show columns from ... / g explain select ... / g flush status; select ...; show status; ---------- -------------------------------------------------- --------------------
Twenty-seven, MySQL is very good
The log is very fast when logging a lot. At the same time, SELECT and INSERT are used. When the update is not combined with the time consumption too long. When most selection / updates use unique keys. When using a plurality of tables that do not lock for a long conflict. When using a big table (MySQL uses a very compact table format).
-------------------------------------------------- ------------------------------
Twenty eight, Mysql should avoid things
Update or insert the table with the deleted row, combine the time-consuming time-long SELECT. Use Having on the columns that can be placed in the WHERE clause. Not using key code or key code is not unique enough to perform JOIN. Join on the column of different column types. Use the HEAP table when not using = matching the entire key. I forgot to use a WHERE clause in Update or Delete in the MySQL monitor. If you want to do this, use the MySQL client -I-AM-A-Dummy option.
-------------------------------------------------- ------------------------------
Twenty-nine, mysql various locks
Internal table lock Lock Tables (All Table Types) Get Lock () / Release Lock () Page Lock (for BDB Table) ALTER TABLE is also available on the BDB table to lock Lock Tables allows a table to have multiple readers and a writer . General WHERE locks have a high priority than READ to avoid letting write parties. For unimportant write parties, you can use the low_priority keyword to allow the lock processor to read the part. Update low_priority set value = 10 where id = 10;
-------------------------------------------------- ------------------------------
Thirty, give MYSQL more information to better solve problems
Note that you can always remove (add this) MySQL function to make the query portable:
SELECT / *! SQL_Buffer_Results * / ... Select SQL_Buffer_Results ... will force Mysql to generate a temporary result set. As long as all temporary results are generated, the locks on all tables are released. This can help the results to the client when you encounter a table lock problem or take a long time. SELECT SQL_SMALL_RESULT ... Group By ... tells the optimizer result set will only contain few rows. SELECT SQL_BIG_RESULT ... Group By ... tells the optimizer result set will contain a lot of rows. Select Straight_JOIN ... Force Optimizer to appear in the order of the Sentences in the FROM clause. Select ... from table_name [use index (index_list) | ignore index (index_list)] Table_name2 Force MySQL Use / Ignore the index listed.
-------------------------------------------------- ------------------------------
Thirty-one, examples of affairs
How MyIASM table transaction: mysql> LOCK TABLES trans READ, customer WRITE; mysql> select sum (value) from trans where customer_id = some_id; mysql> update customer set total_value = sum_from_previous_statement where customer_id = some_id; mysql> UNLOCK TABLES; BDB how the transaction table: mysql> BEGIN WORK; mysql> select sum (value) from trans where customer_id = some_id; mysql> update customer set total_value = sum_from_previous_statement where customer_id = some_id; mysql> COMMIT;
Note that you can avoid transactions through the following statement: Update Customer Set Value = Value New_Value Where Customer_ID = Some_ID;
-------------------------------------------------- ------------------------------
Thirty-two, using replace
The Replace function is imagined in Insert, except that an old record has the same value as the new record on a single index, then the old record is deleted before the new record is inserted. Do not use
SELECT 1 from t1 where key = # ified-row lock tables T1 delete from t1 where key1 = # Insert Into T1 VALUES (...) Unlock Tables T1; Endif
And Replace Into T1 VALUES (...)
-------------------------------------------------- ------------------------------
Thirty-three, general skills
Use short markers. Use a digital than a string while the joint table. When using multiple key, the first part should be the most commonly used portion. In doubts, you first use more repeated columns to get better keycase compression. If you run the MySQL client and server on the same machine, use socket instead of TCP / IP when connecting MySQL (this can increase performance). You can do not specify whether the host name or host name is Localhost when connecting the MySQL server. If possible, use -skip-locking (default on some OS), which will turn off the external lock and will increase performance. Use the application layer hash rather rather than long key code: select * from table_name where hash = md5 (Concat (color (colq1, col2)) and col_1 = constant and col_2 = constant
Save the BLOB that needs to be accessed in the file, and only the file name is saved in the database. Deleting all rows is fast than deleting a lot. If SQL is not fast enough, study the next layer interface of the access data.
-------------------------------------------------- ------------------------------
Thirty-four, use MySQL 3.23 benefits
Myisam: Portable large surface format HEAP: Table Berkeley DB in memory: a table supporting transaction. Many increased limit dynamic character sets More status variables Check and Repair Tables faster group by and distinct left join ... if Null Optimized CREATE TABLE ... SELECT CREATE TEMPORY TABLE_NAME (...) Temporary HEAP Table Automatic conversion of Myisam tables Copy mysqlhotcopy script ------------------------------------------------------------------------------------------------------------------------------------------------ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Thirty-five, important features that are actively developing
Improve transaction failed, replication GUI client
