Mysql optimization collection

(Monty gave a speech at OReillyOpenSourceConvention2000.) [compile and organize Yan Zi] I. what can and should we optimize? Hardware operating system software database SQL Server (setup and query) application programming interface (API) Application ------------------------------------------------------------

(This article is Monty's speech at the o'reilly Open Source Convention 2000 Conference.) [Yan Zi compilation] I. what can and should we optimize? Hardware OS/software database SQL Server (setup and query) application programming interface (API) Application ------------------------------------------------------------

(This is a Monty speech at the o'reilly Open Source Convention 2000 Conference)

[Compile and organize Yan Zi]

1. What can and should we optimize?

Hardware

Operating System/software library

SQL Server (set and query)

Application Programming Interface (API)

Applications

--------------------------------------------------------------------------------

Ii. Optimize hardware

If you need a huge database table (> 2 GB), you should consider using a 64-bit hardware structure, such as Alpha, or the upcoming IA64. Because MySQL uses a large number of 64-bit integers internally, the 64-bit CPU will provide better performance.

For large databases, the optimized order is generally RAM, fast hard disk, and CPU capacity.

More memory by storing the most commonly used keycode pages in the memory, you can speed up key code updates.

If you do not use a transaction-safe table or a large table and want to avoid long file check, a UPS can safely shut down the system when a power failure occurs.

1G Ethernet should be considered for systems with data inventory placed on a dedicated server. Latency and throughput are equally important.

--------------------------------------------------------------------------------

Iii. Disk Optimization

A dedicated disk is provided for the system, program, and temporary files. If a lot of modifications are made, update logs and transaction logs are placed on the dedicated disk.
The low tracing time is very important to the database disk. For large tables, You can estimate that you will need log (number of rows)/log (index block length/3*2/(key code length + Data Pointer length )) + a row can be found only once. For a table with 500000 rows, You need to search for columns of the Mediun int type by log (500000)/log (1024/3*2/(3 + 2) + 1 = 4. The above index requires 500000*7*3/2 = Mb space. In fact, most of the blocks will be cached, so we only need to find them one or two times.
However, for writing (as shown above), you will need four seek requests to find where to store the new key code. Generally, you need two seek requests to update the index and write a row.
For a very large database, your application will be limited by the disk seek speed. As the data volume increases, your application will increase at the N log N data level.
Divide databases and tables on different disks. In MySQL, you can use Symbolic Links for this purpose.
Disk (RAID 0) increases the Read and Write throughput.
A column with an image (RAID 0 + 1) is safer and increases the read throughput. The write throughput is reduced.
Do not use an image or RAID (except RAID 0) on the disk where temporary files or data that can be easily recreated are located ).
In Linux, run the hdparm-m16-d1 command on the disk during boot to enable simultaneous read/write of multiple sectors and DMA functions. This increases the response time by 5 ~ 50%.
In Linux, use async (default) and noatime to mount a disk ).
For some specific applications, memory disks can be used for some specific tables, but this is usually not required.

--------------------------------------------------------------------------------

4. Optimize the Operating System

Do not swap areas. If the memory is insufficient, add more memory or configure your system to use less memory.
Do not use an NFS disk ).
Increase the number of files opened by the system and MySQL server. (Add ulimit-n # To the safe_mysqld script #).
Increase the number of processes and threads in the system.
If you have a relatively small number of large tables, tell the file system not to break files into different channels (Solaris ).
Use a file system (Solaris) that supports large files ).
Select the file system to use. On Linux, Reiserfs is very fast for opening, reading and writing. File Check takes only a few seconds.

--------------------------------------------------------------------------------

5. Select the Application Programming Interface

PERL
It can be transplanted between different operating systems and databases.
Suitable for rapid prototyping.
You should use the DBI/DBD interface.
PHP
Easier to learn than PERL.
Use fewer resources than PERL.
You can get a faster speed by upgrading to PHP4.
C
Native Interface of MySQL.
Quickly and give more control.
Lower Layer, so you must pay more.
C ++
Higher level, giving you more time to write applications.
Still under development
ODBC
Run on Windows and Unix.
It can be transplanted between almost different SQL servers.
Slow. MyODBC is just a simple straight-through driver, which is 19% slower than native interfaces.
There are many ways to do the same thing. It is difficult to run as many ODBC drivers, and there are still different errors in different fields.
Problems. Microsoft occasionally changes the interface.
An unclear future. (Microsoft prefers OLE instead of ODBC)
ODBC
Run on Windows and Unix.
It can be transplanted between almost different SQL servers.
Slow. MyODBC is just a simple straight-through driver, which is 19% slower than native interfaces.
There are many ways to do the same thing. It is difficult to run as many ODBC drivers, and there are still different errors in different fields.
Problems. Microsoft occasionally changes the interface.
An unclear future. (Microsoft prefers OLE instead of ODBC)
JDBC
Theoretically, it is possible to port data between databases in different operating systems.
It can run on the web Client.
Python and others
It may be good, but we don't need them.

--------------------------------------------------------------------------------

Vi. Optimize applications

Focus on solving the problem.
When writing an application, you should decide what is the most important:
Speed
Portability between operating systems
Portability between SQL servers
Use Continuous connections ..
Cache data in the application to reduce the load on the SQL server.
Do not query unnecessary columns in the application.
Do not use SELECT * FROM table_name...
Test all parts of an application, but focus most of the effort on testing the overall application under the potentially worst-case load. By using a modular approach, you should replace the bottleneck with a fast "dumb module", and easily mark the next bottleneck.
If you make a large number of modifications in a batch, use lock tables. For example, you can combine multiple UPDATES or DELETES.

--------------------------------------------------------------------------------

7. Use portable applications

Perl DBI/DBD
ODBC
JDBC
Python (or other languages with common SQL interfaces)
You should only use SQL structures that exist in all target SQL servers or can easily use other constructor to simulate SQL structures. The Crash-me page on www.mysql.com can help you.
Write a package program for the operating system or SQL server to provide missing functions.

--------------------------------------------------------------------------------

8. If you need a faster speed, you should:

Identify bottlenecks (CPU, disk, memory, SQL Server, operating system, API, or application) and focus on solving them.
It provides you with faster speed/flexibility scaling.
Gradually understand the SQL server so that you can use the fastest possible SQL structure for your problems and avoid bottlenecks.
Optimize table layout and query.
Use replication for faster select speeds.
If you have a slow network connection to the database, use the compressed Client/Server protocol.
Don't be afraid that the first version of the application cannot be transplanted perfectly. When you solve the problem, you can always optimize it later.

--------------------------------------------------------------------------------

9. Optimize MySQL

Select the compiler and compilation options.
Find the best startup options for your system.
Read MySQL reference manual and Paul DuBios's MySQL book. (The Chinese version already exists)
Use explain select, show variables, show status, and show processlist.
Understand how the query optimizer works.
Optimize the table format.
Maintain your TABLE (myisamchk, check table, optimize table)
Use the MySQL extension function to complete everything quickly.
If you notice that you will need some functions in many cases, write the MySQL UDF function.
Do not use table-level or column-level GRANT unless you do need it.
Purchase MySQL technical support to help you solve the problem :)

--------------------------------------------------------------------------------

10. Compile and install MySQL

By selecting the best possible compiler for your system, you can generally achieve a performance improvement of 10-30%.
On Linux/Intel Platform, use pgcc (gcc Pentium chip optimized version) to compile MySQL. However, binary code can only run on Intel Pentium CPU.
For a specific platform, use the optimization options recommended in the MySQL reference manual.
Generally, native compilers for a specific CPU (such as the Sun Workshop of the iSCSI) should provide better performance than gcc, but this is not always the case.
Compile MySQL with the character set you will use.
Static compilation generates the mysqld execution file (use -- with-mysqld-ldflags = all-static) and uses strip SQL/mysqld to organize the final execution file.
Note that since MySQL does not use C ++ extensions, compilation of MySQL without extensions won huge performance improvements.
If the operating system supports native threads, use native threads (instead of mit-pthreads ).
Use the MySQL benchmark to test the final binary code.

--------------------------------------------------------------------------------

11. Maintenance

If possible, run OPTIMIZE table occasionally, which is very important for changing the length of rows in a large number of updates.
Occasionally, use myisamchk-a to update the key code distribution statistics in the table. Remember to turn MySQL off before doing so.
If there are fragment files, it may be worthwhile to copy all the files to another disk, clear the original disk and copy back the files.
If any problem occurs, use myisamchk or CHECK table to CHECK the table.
Use mysqladmin-i10 precesslist extended-status to monitor the MySQL status.
With the MySQL GUI client program, you can monitor the process list and status in different windows.
Use mysqladmin debug to obtain information about locking and performance.

--------------------------------------------------------------------------------

12. Optimize SQL

Take advantage of SQL and hand over other tasks to applications. Use the SQL Server:

Find the rows based on the WHERE clause.
JOIN table
GROUP
ORDER
DISTINCT
Do not use SQL:

Test data (such as date)
Become a calculator
TIPS:

Use the key code wisely.
The key code is suitable for searching, but not for inserting or updating index columns.
Keep data as the third paradigm of the database, but do not worry about redundant information or create a summary table if you need to speed up.
Do not use group by on a large table. Instead, create a summary table for a large table and query it.
UPDATE table set count = count + 1 where key_column = constant very fast.
For large tables, it may be better to generate a summary table occasionally instead of keeping the summary table all the time.
Make full use of the default values of INSERT.

--------------------------------------------------------------------------------

13. Speed differences between different SQL servers (in seconds)

　

Read 2000000 lines by keycode: 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 row 350768: 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 an 8 m high-speed cache to run, and other databases are installed and run by default.

--------------------------------------------------------------------------------

14. Important MySQL startup options

Modify back_log if a large number of new connections are required.
Thread_cache_size if a large number of new connections are required, modify it.
Key_buffer_size can be set to a large index page pool.
Bdb_cache_size the records and keys used by the BDB table are cached at high speed.
If table_cache has many tables and concurrent connections, modify it.
Delay_key_write If You Need To Cache all key code writes, set it.
Log_slow_queries.
Max_heap_table_size is used for GROUP
Sort_buffer is used for order by and GROUP
Myisam_sort_buffer_size is used for REPAIR TABLE
Join_buffer_size is used for join without keys.

--------------------------------------------------------------------------------

15. Optimization table

MySQL has a set of rich types. You should try to use the most effective type for each column.
The ANALYSE process helps you find the optimal table Type: SELECT * FROM table_name procedure analyse ().
Not null is used for columns that do NOT store NULL values. This is especially important for the columns you want to index.
Change the ISAM type table to MyISAM.
If possible, use a fixed table to create a table.
Don't index what you don't want to use.
The fact that MySQL can query by the prefix of an index. If you have an INDEX (a, B), you do not need an INDEX on.
Instead of creating an index on long CHAR/VARCHAR columns, you can only create a prefix of the index column to save storage space. Create table table_name (hostname CHAR (255) not null, index (hostname (10 )))
Use the most effective table style for each table.
Columns that store the same information in different tables must have the same definitions and names.

--------------------------------------------------------------------------------

16. How to store data in MySQL

The database is stored in a directory.
Tables are stored as files.
Columns are stored in files in a variable-length or fixed-length format. For BDB tables, data is stored as pages.
Supports memory-based tables.
Databases and tables can be connected by symbols on different disks.
On Windows, MySQL supports using internal symbols of the. sym file to connect to the database.

--------------------------------------------------------------------------------

17. MySQL table Type

HEAP table: fixed-length table, which is only stored in memory and indexed using HASH indexes.
ISAM table: Early B-tree table in MySQL 3.22.
New version of MyIASM: IASM table, which has the following extensions:
Binary hierarchy portability.
NULL column index.
There are fewer fragments for Long-varying rows than the ISAM table.
Supports large 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 ).

--------------------------------------------------------------------------------

18. MySQL row type (for IASM/MyIASM tables)

If all columns are in a fixed-length format (no VARCHAR, BLOB, or TEXT), MySQL creates a table in a fixed-length table format. Otherwise, the table is created in a dynamic-length format.
The Fixed Length format is much faster and safer than the dynamic length format.
The dynamic Length Row format usually occupies less storage space. However, if the table is updated frequently, fragments are generated.
In some cases, it is not worthwhile to transfer all VARCHAR, BLOB, and TEXT columns to another table, but to get a faster speed for the primary table.
Using myiasmchk (for ISAM, pack_iasm), you can create a read-only compression table, which minimizes disk usage, but this is very good when using a slow disk. The compressed table makes full use of the log table that will not be updated

--------------------------------------------------------------------------------

19. MySQL high-speed cache (All threads are shared and allocated at one time)

Key code cache: key_buffer_size. The default value is 8 Mb.
Table cache: table_cache. The default value is 64.
Thread cache: thread_cache_size. The default value is 0.
Host name cache: it can be modified during compilation. The default value is 128.
Memory ing table: currently only used to compress tables.
Note: The operating system can handle the problem because MySQL does not have a high-speed cache.

--------------------------------------------------------------------------------

20. MySQL cache zone variables (non-shared, on-demand allocation)

Sort_buffer: order by/GROUP
Record_buffer: scans tables.
Join_buffer_size: No Key Connection
Myisam_sort_buffer_size: REPAIR TABLE
Net_buffer_length: reads SQL statements and caches results.
Tmp_table_size: size of the temporary HEAP table.
　

--------------------------------------------------------------------------------

21. Working Principle of MySQL table high-speed cache

Each MyISAM table's open instance uses an index file and a data file. If the table is used by two threads or twice in the same query, MyIASM will share the index file but open another instance of the data file.
If all the tables in the cache are in use, the cache will be temporarily increased to a greater size than the table cache. In this case, the next released table will be closed.
You can check whether the cache is too small by checking the Opened_tables variable of mysqld. If the value is too high, you should increase the table cache speed.
　

--------------------------------------------------------------------------------

22. MySQL extension/Optimization-faster speed

Use the optimized table type (HEAP, MyIASM, or BDB table ).
Use optimized columns for data.
Use fixed-length rows if possible.
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 ()
INSERT multiple rows at a time using multiple rows.
SELECT INTO OUTFILE
Left join, STRAIGHT JOIN
Left join, combined with IS NULL
Order by can use the key code in some cases.
If you only query the columns in one index, you can use only the index tree to solve the query.
Joins are generally faster than subqueries (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) is highly optimized.
GET_LOCK ()/RELEASE_LOCK ()
LOCK TABLES
INSERT and SELECT can run simultaneously.
UDF functions can be loaded into a running server.
Compress read-only tables.
CREATE TEMPORARY TABLE
Create table... SELECT
The MyIASM table with the RAID option splits files into many files to break through the 2G limitation of some file systems.
Delay_keys
Copy Function

--------------------------------------------------------------------------------

22. When does MySQL use indexes?

Use >,>=,=, 1 and key_part1 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 | 11 | Using temporary; Using filesort |
| T2 | ref | ID | 4 | t1.TransactionID | 13 |
| T3 | eq_ref | PRIMARY | 4 | t2.GroupID | 1 |
+ ------- + -------- + --------------- + --------- + ------------------ + ------ + --------------------------------- +
The ALL and range types indicate a potential problem.
　

--------------------------------------------------------------------------------

25. Learn to use SHOW PROCESSLIST

Use SHOW processlist to find out what is being done:
+ ---- + ------- + ----------- + ---- + --------- + ------ + -------------- + --------------------------------------- +
| 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 |
+ ---- + ------- + ----------- + ---- + --------- + ------ + -------------- + --------------------------------------- +
KILL is used in mysql or mysqladmin to KILL the thread.
--------------------------------------------------------------------------------

26. How to know MySQL solves 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;

--------------------------------------------------------------------------------

27. MySQL is very good.

Logs
During many connections, the connection is very fast.
When both SELECT and INSERT are used.
When you choose not to combine updates with the time-consuming options.
When most selections/updates use a unique key code.
When multiple tables are not locked for a long time.
When using large tables (MySQL uses a very compact table format ).

--------------------------------------------------------------------------------

Things that MySQL should avoid

Use the deleted rows to update or insert tables, combined with the SELECT statement that takes a long time.
Use HAVING in columns that can be placed in the WHERE clause.
JOIN is performed without the use of the key code or the key code is not unique enough.
JOIN a column of different column types.
Use the HEAP table if the = parameter is not used to match the entire key code.
I forgot to use a WHERE clause in UPDATE or DELETE in the MySQL monitoring program. To do this, use the -- I-am-a-dummy option of the mysql client program.

--------------------------------------------------------------------------------

29. Various MySQL locks

Internal table lock
Lock tables (applicable to all table types)
Get lock ()/release lock ()
Page locking (for BDB tables)
Alter table also locks the TABLE on the bdb table.
Lock tables allows a table to have multiple readers and a writer.
Generally, the WHERE lock has a higher priority than the READ lock to prevent the writer from doing so. For unimportant writers, you can use the LOW_PRIORITY keyword to make the lock processor preferred reader.
UPDATE LOW_PRIORITY SET value = 10 WHERE id = 10;

--------------------------------------------------------------------------------

Thirty. Tips for giving MySQL more information to better solve the problem. Note that you can always remove (add a comment) the MySQL function to make the query Portable:

SELECT /*! SQL _BUFFER_RESULTS */...
SELECT SQL _BUFFER_RESULTS...
MySQL is forced to generate a temporary result set. After all temporary result sets are generated, all tables are locked. This is helpful when the table is locked or the result is sent to the client for a long time.
SELECT SQL _SMALL_RESULT... GROUP...
Tells the optimizer that the result set contains only a few rows.
SELECT SQL _BIG_RESULT... GROUP...
Tells the optimizer that the result set will contain many rows.
SELECT STRAIGHT_JOIN...
Force optimizer to join the table in the order that appears in the FROM clause.
SELECT... FROM table_name [use index (index_list) | ignore index (index_list)] table_name2
Force MySQL to use/ignore the listed indexes.

--------------------------------------------------------------------------------

. Transaction example

How to process transactions in the MyIASM table:
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_previus_statement
Where customer_id = some_id;
Mysql> unlock tables;
How to perform transactions in a BDB table:
Mysql> begin work;
Mysql> select sum (value) from trans where customer_id = some_id;
Mysql> update customer set total_value = sum_from_previus_statement
Where customer_id = some_id;
Mysql> COMMIT;
Note that you can use the following statements to avoid transactions:
UPDATE customer SET value = value + new_value WHERE customer_id = some_id;

--------------------------------------------------------------------------------

Example of using REPLACE

REPLACE functions like INSERT. Except for an old record with the same value as the new record on a unique index, the old record is deleted before the new record is inserted. SELECT 1 FROM t1 WHERE key = # is not used #
IF found-row
Lock tables t1
Delete from t1 WHERE key1 = #
Insert into t1 VALUES (...)
Unlock tables t1;
ENDIF
Use
Replace into t1 VALUES (...)

--------------------------------------------------------------------------------

33. general skills

Use a short primary key. Use numbers instead of strings to join a table.
When using multiple key codes, the first part should be the most commonly used part.
If you have any questions, use more repeated columns for better key code compression.
If you run MySQL clients and servers on the same machine, use a socket instead of a TCP/IP address when connecting to MySQL (this improves performance by 7.5% ). You can do this by not specifying the host name or host name as localhost when connecting to the MySQL server.
If possible, use -- skip-locking (default on some operating systems), which will disable External locks and improve performance.
Use the hash value of the application layer instead of the long key code:
SELECT * FROM table_name WHERE hash = MD5 (concat (col1, col2) AND
Col_1 = 'constant' AND col_2 = 'constant'
Save the BLOB that needs to be accessed as a file in the file, and save only the file name in the database.
Deleting all rows is faster than deleting a majority of rows.
If SQL is not fast enough, study the underlying interfaces for accessing data.

--------------------------------------------------------------------------------

34. Benefits of using MySQL 3.23

MyISAM: Portable big table format
HEAP: tables in memory
Berkeley DB: a table that supports transactions.
Many restrictions
Dynamic Character Set
More STATUS variables
CHECK and REPAIR tables
Faster group by and DISTINCT
Optimization of left join... IF NULL
Create table... SELECT
Create temporary table_name (...)
Automatic conversion from temporary HEAP table to MyISAM table
Copy
Mysqlhotcopy script

--------------------------------------------------------------------------------

35. Important functions under active development

Improve Transaction Processing
Failed and secure Replication
Body Search
Delete multiple tables (then update multiple tables)
Better key code caching
Atomic RENAME (rename table foo as foo_old, foo_new as foo)
Query high-speed cache
MERGE TABLES
A better GUI client program