Shulou(Shulou.com)06/01 Report--

Optimization of MySQL [@ more@]

Optimization of Mysql

2001-02-06 5:55

Publisher. NETbull

(this article is Monty's speech at OReilly open source Convention 2000.)

[Yanzi compiled and sorted out]

First, what can and should be optimized?

Hardware

Operating system / software library

SQL server (setup and query)

Application programming Interface (api)

Application program

Second, optimize the hardware

If you need large database tables (> 2G), you should consider using 64-bit hardware structures such as Alpha, Sparc, or the upcoming IA64. Because MYSQL uses a large number of 64-bit integers inside, 64-bit cpu will provide better performance.

For large databases, the order of optimization is generally RAM, fast hard disk, and CPU capabilities.

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

If you don't use transaction security (transaction-safe) tables or have large tables and want to avoid long file checks, a UPS can safely shut down the system in the event of a power failure.

For systems where the database is stored on a dedicated server, 1G Ethernet should be considered. Latency is as important as throughput.

Third, optimize disk

Provide a dedicated disk for systems, programs, and temporary files, and if you do make a lot of changes, put the update log and transaction log on the dedicated disk.

Low seek time is very important for database disks. For large tables, you can estimate that you will need log (rows) / log (index block length / 3x2 / (key length + data pointer length) + 1 find to find a row. For a table with 500000 rows, indexing columns of type Mediun int requires log (500000) / log (1024 + 2) + 1 seek 4 times. The above index requires 500000 square meters of space, 7cm, 3ax, 2m, 5.2m. In fact, most blocks will be cached, so it only takes about 1-2 seek.

However, for writes (as above), you will need 4 seek requests to find out where to store the new key code, and typically 2 seek requests to update the index and write a row.

For very large databases, your application will be limited by disk seek speed and increase at the N log N data level as the amount of data increases.

Divide the database and tables on different disks. In MySQL, you can use symbolic links for this.

Striped disks (RAID 0) will increase read and write throughput.

Columns with mirrors (RAID 0 columns 1) will be more secure and improve read throughput. Write throughput will be reduced.

Do not use mirroring or RAID on temporary files or disks where data can be easily rebuilt (except for RAID 0).

On Linux, use the command hdpaRM-M16-D1 to the disk at boot time to enable simultaneous read and write to multiple sectors and DMA. This increases response time by 5-50%.

On Linux, mount the disk (mount) with async (default) and noatime.

For certain applications, memory disks can be used for specific tables, but they are usually not required.

Fourth, optimize the operating system

No exchange area. If there is not enough memory, add more memory or configure your system to use less memory.

Do not use NFS disks (there will be problems with NFS locking).

Increase the number of open files on the system and MySQL server. (add ulimit-n # to the safe_mysqld script).

Increase the number of processes and threads on the system.

If you have relatively few large tables, tell the file system not to break files on different tracks (Solaris).

Use a file system (Solaris) that supports large files.

Choose which file system to use. Reiserfs on Linux is very fast for opening, reading and writing. It only takes a few seconds to check the file.

Choose the application programming interface

Perl

Portable between different operating systems and databases.

Suitable for rapid prototyping.

The dbI/DBD interface should be used.

PHP

Easier to learn than PERL.

Use fewer resources than PERL.

Faster speeds can be achieved by upgrading to php4.

C

Native interface to MySQL.

Faster and give more control.

Lower level, so you have to pay more.

C++

At a higher level, it gives you more time to write applications.

Still under development.

ODBC

Run on windows and unix.

It is almost portable between different SQL servers.

It's slower. MyODBC is a simple pass-through driver, which is 19% slower than using a native interface.

There are many ways to do the same thing. It's hard to run like many ODBC drivers, and there are different errors in different areas.

There are a lot of problems. Microsoft occasionally changes the interface.

An uncertain future. (Microsoft prefers OLE to ODBC)

ODBC

Run on Windows and Unix.

It is almost portable between different SQL servers.

It's slower. MyODBC is a simple pass-through driver, which is 19% slower than using a native interface.

There are many ways to do the same thing. It's hard to run like many ODBC drivers, and there are different errors in different areas.

There are a lot of problems. Microsoft occasionally changes the interface.

An uncertain future. (Microsoft prefers OLE to ODBC)

JDBC

In theory, it can be ported between different operating systems.

Can be run on the web client.

Python and others

It may be good, but we don't use them.

VI. Optimize the application

We should concentrate on solving the problem.

When writing an application, you should decide what is most important:

Speed

Portability between operating systems

Portability between SQL servers

Use a continuous connection. .

Cache the data in the application to reduce the load on the SQL server.

Do not query columns that are not needed in the application.

Do not use select * FROM table_name...

Test all parts of the application, but focus most of your energy on testing the overall application under the worst possible reasonable load. By doing this in a modular way, you should be able to replace the bottleneck found with a fast "dumb module" and then easily mark the next bottleneck.

If you make a lot of changes in a batch, use LOCK TABLES. For example, bring multiple UPDATES or DELETES together.

7. Portable applications should be used

Perl DBI/DBD

ODBC

JDBC

Python (or any other language with a universal SQL interface)

You should only use SQL constructs that exist in all destination SQL servers or can be easily simulated with other constructs. The Crash-me page on www.mysql.com can help you.

Write a wrapper for the operating system / SQL server to provide missing functionality.

If you need faster speed, you should:

Identify bottlenecks (CPU, disk, memory, SQL server, operating system, API, or application) and focus on solving them.

Use extensions that give you faster speed / flexibility.

Learn more about SQL servers so that you can use the fastest SQL constructs possible for your problems and avoid bottlenecks.

Optimize table layout and queries.

Use replication for faster select speeds.

If you have a slow network connection to the database, use a compressed client / server protocol.

Don't be afraid that the first version of the application is not perfectly portable, and when you solve a problem, you can always optimize it later.

IX. Optimize MySQL

Pick the compiler and compilation options.

Find the best startup option for your system.

Read through the MySQL reference manual and read Paul DuBIOS's "MySQL". (Chinese version is available-translation note)

Multi-use EXPLAIN SELECT, SHOW VARIABLES, SHOW STATUS and SHOW PROCESSLIST.

Understand how the query optimizer works.

Optimize the format of the table.

Maintain your watch (myisamchk, CHECK TABLE, OPTIMIZE TABLE)

Use the extensions of MySQL to get everything done quickly.

If you notice that you will need certain functions on many occasions, write MySQL UDF functions.

Do not use table-level or column-level GRANT unless you really need it.

Purchase MySQL technical support to help you solve the problem:)

Compile and install MySQL

By selecting the best compiler for your system, you can usually get a 10-30% performance improvement.

On the Linux/intel platform, compile MySQL with pgcc (the optimized version of gcc's Pentium chip). However, the binaries will only run on Intel Pentium CPU.

For a particular platform, use the optimization options recommended in the MySQL reference manual.

In general, native compilers for a particular CPU, such as Sparc's Sun Workshop, should provide better performance than gcc, but not always.

Compile MySQL with the character set you will use.

Statically compile to generate the execution file of mysqld (with-- with-mysqld-ldflags=all-static) and collate the final execution file with strip sql/mysqld.

Note that since MySQL does not use C++ extensions, compiling MySQL without extension support will gain a huge performance improvement.

If the operating system supports native threads, use native threads (instead of mit-pthreads).

Use the MySQL benchmark to test the final binary.

11. Maintenance

If possible, run OPTIMIZE table once in a while, which is important for a large number of updated variable-length rows.

Occasionally update the key code distribution statistics in the table with myisamchk-a. Remember to turn off MySQL before you do it.

If there are fragmented files, it may be worthwhile to copy all the files to another disk, erase the original disk, and copy the files back.

If you encounter problems, check the table with myisamchk or CHECK table.

Use mysqladmin-i10 precesslist extended-status to monitor the status of the MySQL.

With MySQL GUI clients, you can monitor the list and status of processes in different windows.

Use mysqladmin debug to get information about locking and performance.

-

12. Optimize SQL

Take advantage of SQL and leave everything else to the app. Use the SQL server to do:

Find the lines based on the WHERE clause.

JOIN table

GROUP BY

ORDER BY

DISTINCT

Do not use SQL to do this:

Inspection data (e. G. date)

Become a calculator

Tips:

Use the key code wisely.

Keycodes are suitable for searching, but not for inserting / updating index columns.

Keep the data as the third normal form of the database, but don't worry about redundant information or this if you need faster, create summary tables.

Instead of GROUP BY on a large table, create a summary table of the large table and query it.

UPDATE table set count=count+1 where key_column=constant is very fast.

For large tables, it may be best to generate summary tables occasionally rather than keep them all the time.

Make full use of the default values of INSERT.

13. Differences in speed among different SQL servers (in seconds)

Read line 2000000 through 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

Insert 350768 line: 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 tests, MySQL configured 8m cache to run, and other databases were installed to run by default.

-

XIV. Important MySQL startup options

If back_log requires a large number of new connections, modify it.

If thread_cache_size requires a large number of new connections, modify it.

Key_buffer_size index page pool, which can be set to very large.

The records and keys used by bdb_cache_size BDB tables are cached.

If table_cache has a lot of tables and concurrent joins, modify it.

Delay_key_write if you need to cache all keycode writes, set it.

Log_slow_queries finds queries that take a lot of time.

Max_heap_table_size is used for GROUP BY

Sort_buffer is used for ORDER BY and GROUP BY

Myisam_sort_buffer_size is used for REPAIR TABLE

Join_buffer_size is used when making keyless joins.

15. Optimization table

MySQL has a rich set of types. You should try to use the most efficient type for each column.

The ANALYSE process can help you find the optimal type of table: SELECT * FROM table_name PROCEDURE ANALYSE ().

Use NOT NULL for columns that do not hold null values, which is especially important for the columns you want to index.

Change the table of type ISAM to MyISAM.

If possible, create a table in a fixed table format.

Don't index things you don't want to use.

The fact that MySQL can query by the prefix of an index. If you have an index INDEX, you don't need an index on a.

Instead of creating an index on a long CHAR/VARCHAR column, only one prefix of the column is indexed to save storage space. CREATE TABLE table_name (hostname CHAR (255) not null, index (hostname (10)

Use the most efficient table format for each table.

Columns that hold the same information in different tables should have the same definition and the same column name.

16. How to store data in MySQL

The database is stored in a directory.

Tables are stored as files.

The column is stored in the file in a variable or fixed length format. For BDB tables, the data is stored as a page.

Supports memory-based tables.

Databases and tables can be symbolically connected on different disks.

On Windows, MySQL supports connecting to databases with internal symbols in .sym files.

-

Seventeen. MySQL table type

HEAP table: a fixed-length table that is stored only in memory and indexed with a HASH index.

ISAM table: the early B-tree table format in MySQL 3.22.

A new version of the MyIASM:IASM table with the following extensions:

Portability at the binary level.

NULL column index.

There is less fragmentation for variable-length rows than for ISAM tables.

Large files are supported.

Better index compression.

Better keys? statistical distribution.

Better and faster auto_increment processing.

Berkeley DB (BDB) table from Sleepcat: transaction security (with BEGIN WORK/COMMIT | ROLLBACK).

XVIII. MySQL row type (specifically refers to IASM/MyIASM table)

If all columns are in fixed-length format (no VARCHAR, BLOB, or TEXT), MySQL creates the table in fixed-length table format, otherwise the table is created in dynamic length format.

The fixed length format is much faster and safer than the dynamic length format.

Dynamic length row formats generally take up less storage space, but fragmentation can occur if the table is updated frequently.

In some cases, it is not worthwhile to move all the VARCHAR, BLOB, and TEXT columns to another table, just to get faster on the main table.

With myiasmchk (for ISAM,pack_iasm), you can create read-only compression tables, which minimizes disk usage, but this is great when using slow disks. Compressed tables make full use of log tables that will no longer be updated

-

19. MySQL cache (shared by all threads, allocated at one time)

Key cache: key_buffer_size, default is 8m.

Table cache: table_cache, default is 64.

Thread cache: thread_cache_size. Default is 0.

Hostname cache: can be modified at compile time, default 128.

Memory mapping table: currently used only for compression tables.

Note: MySQL does not have a row cache and lets the operating system handle it.

MySQL cache variables (non-shared, allocated on demand)

Sort_buffer:ORDER BY/GROUP BY

Record_buffer: scan the table.

Join_buffer_size: keyless join

Myisam_sort_buffer_size:REPAIR TABLE

Net_buffer_length: for reading SQL statements and caching the results.

Tmp_table_size: the HEAP table size of the temporary result.

21. The working principle of MySQL table cache

Each open instance of the MyISAM table (instance) uses an index file and a data file. If the table is used by two threads or twice in the same query, MyIASM shares the index file and opens another instance of the data file.

If all tables in the cache are in use, the cache will be temporarily increased to a larger size than the table cache. If so, the next freed table will be closed.

You can check whether the table cache is too small by checking the Opened_tables variable of mysqld. If the value is too high, you should increase the table cache.

22, MySQL expansion / optimization-provides faster speed

Use optimized table types (HEAP, MyIASM, or BDB tables).

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 ()

Use multiline INSERT to insert multiple rows at a time.

SELECT INTO OUTFILE

LEFT JOIN, STRAIGHT JOIN

LEFT JOIN, combined with IS NULL

ORDER BY can use keycodes in some cases.

If you query only columns in one index, only the index tree will be used to solve the query.

Joins are generally faster than subqueries (and for most SQL servers).

LIMIT

SELECT * from table1 WHERE a > 10 LIMIT 10pm 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 at the same time.

The UDF function can be loaded into a running server.

Compress the read-only table.

CREATE TEMPORARY TABLE

CREATE TABLE.. SELECT

The MyIASM table with the RAID option splits the file into many files to break the 2G limit of some file systems.

Delay_keys

Copy function

22. When does MySQL use the index

Use >, > =, 1 and key_part1 for a key code

< 90 如果使用HEAP表且不用=搜索所有键码部分。 在HEAP表上使用ORDER BY。 如果不是用键码第一部分 SELECT * FROM table_name WHERE key_part2=1 如果使用以一个通配符开始的LIKE SELECT * FROM table_name WHERE key_part1 LIKE %jani% 搜索一个索引而在另一个索引上做ORDER BY SELECT * from table_name WHERE key_part1 = # ORDER BY key2 --------------------------------------------------------------- 二十四、学会使用EXPLAIN 对于每一条你认为太慢的查询使用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 | 11 | Using mporary;Using filesort |

| | T2 | ref | ID | ID | 4 | t1.TransactionID | 13 |

| | T3 | eq_ref | PRIMARY | PRIMARY | 4 | t2.GroupID | 1 |

+-

The ALL and scope types suggest a potential problem.

-

25. Learn to use SHOW PROCESSLIST

Use SHOW processlist to discover what you are 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 escaped thread in mysql or mysqladmin.

26. How to know MySQL to solve a query

Run the itemline command and try to figure out its output:

SHOW VARIABLES

SHOW COLUMNS FROM... G

EXPLAIN SELECT... G

FLUSH STATUS

SELECT...

SHOW STATUS

27, MySQL is very good.

Journal

When making many connections, the connection is very fast.

Where SELECT and INSERT are used at the same time.

When not combining updates with choices that take too long.

When most selections / updates use unique keys.

When using multiple tables that do not have long-term conflict locks.

When using large tables (MySQL uses a very compact table format).

28, things that MySQL should avoid

Update or insert the table with deleted rows, combined with the time-consuming SELECT.

Use HAVING on columns that can be placed in the WHERE clause.

Do not use the key code or the key code is not unique enough to JOIN.

JOIN on columns of different column types.

Use the HEAP table when you do not use = to match the entire key code.

Forget to use a WHERE clause in UPDATE or DELETE in the MySQL monitor. If you want to do this, use the-- i-am-a-dummy option of the mysql client.

29, MySQL various locks

Internal table lock

LOCK TABLES (applicable to all table types)

GET LOCK () / RELEASE LOCK ()

Page locking (for BDB tables)

ALTER TABLE also performs table locking on the BDB table

LOCK TABLES allows multiple readers and one writer for a table.

In general, WHERE locks have higher priority than READ locks to avoid making writes dry, and so on. For unimportant writers, you can use the LOW_PRIORITY keyword to have the locking processor prefer the reader.

UPDATE LOW_PRIORITY SET value=10 WHERE id=10

Tips for giving MySQL more information to better solve problems

Note that you can always remove (annotate) the MySQL function to make queries portable:

SELECT / *! SQL_BUFFER_RESULTS * /...

SELECT SQL_BUFFER_RESULTS...

MySQL will be forced to generate a temporary result set. Locks on all tables are released as long as all temporary result sets are generated. This can be helpful when you encounter a table locking problem or when it takes a long time to pass the results to the client.

SELECT SQL_SMALL_RESULT... GROUP BY...

Tells the optimizer that the result set will contain only a few rows.

SELECT SQL_BIG_RESULT... GROUP BY...

Tell the optimizer that the result set will contain many rows.

SELECT STRAIGHT_JOIN...

Forces the optimizer to join tables in the order in which they appear in the FROM clause.

SELECT... FROM table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)] table_name2

Forces MySQL to use / ignore listed indexes.

31. Examples of transactions

How the MyIASM table is transacted:

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 the BDB table conducts transactions:

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 with the following statements:

UPDATE customer SET value=value+new_value WHERE customer_id=some_id

32. Examples of using REPLACE

REPLACE functions very much like INSERT, except that if an old record has the same value as the new record on a unique index, the old record is deleted before the new record is inserted. Do not use

SELECT 1 FROM t1 WHERE key=#

IF found-row

LOCK TABLES t1

DELETE FROM t1 WHERE key1=#

INSERT INTO T1 VALUES (...)

UNLOCK TABLES t1

ENDIF

And use

REPLACE INTO T1 VALUES (...)

33. General skills

Use a short primary key. Use numbers instead of strings when joining tables.

When using multipart keys, the first part should be the most commonly used part.

When in doubt, first use more repetitive columns to get better keycode compression.

If you are running MySQL clients and servers on the same machine, use sockets instead of TCP/IP when connecting to MySQL (which improves performance by 7.5%). This can be done without specifying a hostname or hostname of localhost when connecting to the MySQL server.

If possible, use-- skip-locking (default on some OS), which turns off external locking and improves performance.

Use the application layer hash 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 large number of rows.

If SQL is not fast enough, take a look at the lower-level interfaces that access data.

34. Benefits of using MySQL 3.23

MyISAM: portable large table format

HEAP: tables in memory

Berkeley DB: tables that support transactions.

Many improved restrictions

Dynamic character set

More STATUS variables

CHECK and REPAIR tables

Faster GROUP BY and DISTINCT

LEFT JOIN... Optimization of IF NULL

CREATE TABLE... SELECT

CREATE TEMPORARY table_name (...)

Automatic conversion from temporary HEAP table to MyISAM table

Copy

Mysqlhotcopy script

35. Important features under active development

Improve transaction processing

Failed secure replication

Text search

Delete multiple tables (update multiple tables later)

Better keycode caching

Atomic RENAME (RENAME TABLE foo as foo_old, foo_new as foo)

Query cache

MERGE TABLES

A better GUI client program

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.