Optimization of the MySQL: Yan Zi (2001-05-14 12:20:05)
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 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 the disk with a dedicated disk for the system, procedures, and temporary files, if not Many modifications work, place the update log and transaction log on a dedicated disk. 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. -------------------------------------------------- ----------------------------4, optimize the operating system not to exchange the area. 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 Application 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. The optimization application should focus on 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, you should use portable Application Perl DBI / DBD ODBC JDBC Python (or other universal The language of the SQL interface) You should only use the SQL configuration that exists in all the SQL servers 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 faster speed, you should: find a bottleneck (CPU, disk , Memory, SQL servers, operating systems, APIs, or applications are concentrated. 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 the Mysql picking 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 :) ------------------------------------- ----------------------------------------- 10, compile and install MySQL through you The system selects the best compiler, you usually get 10-30% performance improvement. 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. -------------------------------------------------- ----------------------------- 11. Maintenance If possible, occasionally run Optimize Table, this is a large number of updates It is very important. 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. -------------------------------------------------- ----------------------------- 12, optimize SQL Yang SQL long, other things are done. Use the SQL server to do: find out the rows based on the WHERE clause. JOIN Table Group by Order by Distin Do not use SQL to do: Inspection data (such as date) becomes a calculator skill: use key code 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. -------------------------------------------------- ----------------------------- 13, the speed difference (in seconds) is read through the key code 2000000 OK: 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 insert 350768 lines: 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 In the test, MySQL configuration 8M cache runs, 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, optimized table MySQL has a rich type. 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, MySQL how to store data databases in directory storage. 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. -------------------------------------------------- ------------------------------ 17, MySQL Table Type Heap Table: Fixed President Table, only stored in memory And use the Hash index to 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 Format (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, disposable 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.
-------------------------------------------------- ------------------------------------------------------------------------------------------------------------------- GROUP BY RECORD_BUFFER: Scanning 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 Each Myisam Table Opening Instance (Instance) 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. -------------------------------------------------- ------------------------------22, MySQL Extension / Optimization - Provide faster speed using optimized table types ( 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 --------------------------------------------- -------------------------------- 22, mysql When 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 used LIKE SELECT no wildcard character begins * FROM table_name WHERE key_part1 LIKE The 'JANI%' is extracted from another table when connecting to another table. Select * from T1, T2 WHERE T1.col = t2.key_part identifies the max () or min () value Select Min (Key_Part2), Max, MAX prefix (key_part2) FROM table_name where key_part1 = 10 a key code using ORDER bY or GROUP bY SELECT * FROM foo ORDER bY key_part1, key_part2, key_part3 at all used in the query column is part of the time the key code SELECT key_part3 FROM table_name WHERE Key_PART1 = 1 ----------------------------------------------- -------------------------------- 23, when Mysql does not use the index If Mysql can estimate it You may not use an index when you are faster than the scanning tab. 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 using the key code first section Select * from table_name where key_part2 = 1 If you use a wildcard start, Like Select * from table_name where key_part1 like '% jani%' search on another index, you can do select * from From Table_name where key_part1 = # ORDER by key2 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------- 24, learn to use explain for every one 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 Temporary; Using FileSort | | T2 | REF | ID | ID | 4 | T1 .TransactionID | 13 | | | T3 | EQ_REF | PRIMARY | PRIMARY | 4 | T2.GroupID | 1 | | ------- -------- -------- ------- -------- --------- ---------------- --- - --------------------------------- all and range types Tips Tips.
-------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - ------- ---------- -- ------- ---- ----- --------- ------------------------------------ | 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 | - ------- --------- -- --------- - ---- -------------- ---------------------------------------------------------------------------------------- ------ In mysql or mysqladmin with kill to kill the thread. -------------------------------------------------- ------------------------------ twenty-six, how to know the mysql to solve a query run item column command and try to understand its output : Show Variables; Show Column from ... / g Explain Select ... / g flush status; select ...; show status; ------------------------------------------------------------------------------ -------------------------------------------------- ---------- Twenty-seven, MySQL is very nice logs when making a lot of connections, very fast. 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). -------------------------------------------------- ------------------------------28, Mysql should be avoided with deleted rows to update or insert tables, It takes time to consume a 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.
-------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Lock () / release lock () page lock (for BDB table) ALTER TABLE is also 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; --------------------------------------- ---------------------------------------- 30, give mesql more information To better solve problems, pay attention to you can remove (add) 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, the MYIASM table How to 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; how BDB table transaction: 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 the transaction by the following statement: UPDATE customer SET value = value new_value WHERE customer_id = some_id; -------------------------------------------------- ----------------------------- 32, using the example of the example Replace is imaging in Insert, except if an old record In a single index having the same value as a new record, the old record is deleted before the new record is inserted. Do not use SELECT 1 from t1 where key = # ify far-row lock tables T1 delete from t1 where key1 = # Insert INTO T1 VALUES (...) Unlock Tables T1; Endif with Replace Into T1 VALUES (...) -------------------------------------------------- ----------------------------- 33, general skills use short marks. 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 value rather than long key code: select * from table_name where has = md5 (Concat (color (color (color (color (color (color ") and col_1 = 'constant' and col_2 = 'constant' Save in the file needs to be accessed in file form BLOB, only save the file name 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.
