Shulou(Shulou.com)05/31 Report--

This article mainly explains the "tuning ideas and practical operation of MySQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the tuning ideas and practical operation of MySQL".

Tuning ideas:

1. Design and planning-it is troublesome to fix it later, estimate the amount of data, what storage engine to use

two。 The application of data-- how to get data and the optimization of sql statement

3. Service optimization-memory usage, disk usage

4. Optimization of operating system-- number of kernel and tcp connections

5. Upgrade hardware equipment

Disk io planning

Raid technology: raid0 [XFS]

Swap partition: it is best to use raid0

Disk partition: a library is placed on a partition or on a disk

Physical partition

Create table T1 (id int,name char (20)) data directory='/data/' index directory='/data'

Mkdir / data

Chown mysql.mysql / data

Mysql > show variables like'% part%'

4. Optimization of operating system

Bonding Technology of Network Card

Limit the number of tcp connections

Optimize the maximum limit for the system to open files

Turn off unnecessary services of the operating system

5.mysql service optimization

Show status looks at the system's resources

Show variables looks at variables, defined in the my.cnf configuration file

Show warnings looks at the error warning generated by the most recent sql statement, and the rest needs to see the .err log.

Show processlist displays all processes running on the system.

Show errors

Enable mysql slow query:-analyze sql statements to find the SQL that affects efficiency

The path log-slow-queries=/var/lib/mysql/slow.log has writeable access to mysql users.

Record the statements of more than 2 seconds of long_query_time=2 query

The above 2 is the time of the query, that is, it is recorded when a SQL takes more than 5 seconds to execute, and / var/lib/mysql/slow.log is the location of the log record.

Then restart the MySQL service

Cache queries

How much memory does query_cache_size use to cache query statements [+ 8m]

Mysql > show variables like'%query%'

Query_cache_size=8M

[root@st mysql] # vim / etc/my.cnf

Mysql > show status like'% Qcache%'

Qcache_free_blocks: indicates that the cache is too large. The number of adjacent memories in the cache. A large number means there may be fragments. FLUSH QUERY CACHE will defragment the cache to get a free block. [+ 8m]

Free memory in Qcache_free_memory cache

Qcache_hits grows every time a query hits in the cache

Qcache_inserts grows every time a query is inserted. The number of hits divided by the number of inserts is the hit rate.

The number of times that the Qcache_lowmen_prunes cache is out of memory and must be cleaned to provide space for more queries. This number is best seen for a long time; if it is growing, it means that the fragmentation may be very serious, or there may be very little memory.

Qcache_hits/Qcache_inserts hit rate

Keyword buffer

Mysql > show status like'% key%'

Mysql > show variables like'key_buffer_size'

Key_buffer_size specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reads. [+ 8m]

Total number of key_read_requests requests

Key_reads represents the number of requests that hit the disk

(key_read_requests-key_read) / key_read_requests: hit ratio

Key_buffer_size only works on MyISAM tables. Use this value even if you do not use the MyISAM table, but the internal temporary disk table is the MyISAM table. You can use the check status value created_tmp_disk_tables to learn more.

For machines with 1G memory, if the MyISAM table is not used, the recommended value is 16m (8-64m).

Temporary tablespace size: order by and group by put data in temporary tables.

Tmp_table_size occupies the size of memory. If it is too small, it will make an error when sorting.

Number of temporary tables created by created_tmp_tables

Max_tmp_tables=32

Location of the temporary table on the tmpdir=/tmp hard drive

~

Innodb table:

Create a tablespace file

[mysqld]

Innodb_data_file_path=ibdata1:10M:autoextend

This setting configures a separate file with an expandable size of 10MB called ibdata1. The location of the file is not given, so the default is in the data directory of MySQL.

If you specify the autoextend option for the last data file. If the data file runs out of free space in the table space, InnoDB extends the data file. The extent of expansion is each 8MB.

To specify the maximum size for an auto-extended data file, use the max property. The following configurations allow ibdata1 to rise to the limit of 500MB:

[mysqld]

Innodb_data_file_path=ibdata1:10M:autoextend:max:500M

InnoDB creates tablespace files in the MySQL data directory by default. To specify a location explicitly, use the innodb_data_home_dir option. For example, to use two files named ibdata1 and ibdata2, but to create them to / ibdata, configure InnoDB as follows:

[mysqld]

Innodb_data_home_dir = / ibdata

Innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

Mysql > show variables like 'innodb_buffer_pool_size'

Innodb_buffer_pool_size

For InnoDB tables, innodb_buffer_pool_size is the same as key_buffer_size is for MyISAM tables. InnoDB uses this parameter to specify the size of memory to buffer data and indexes. For a separate MySQL database server, you can set this value to up to 80% of physical memory.

According to the MySQL manual, the recommended value for machines with 2G memory is 1G (50%).

Mysql > show variables like 'innodb_%per%'; [recommended]

Innodb_file_per_table = 1 creates a separate tablespace file for each table.

Other parameters

Skip-locking

Remove the external lock of the file system to reduce the probability of error and enhance stability

Skip-name-resolve

Turn off the dns reverse check function of mysql. It's faster this way!

Option to disable DNS parsing, which makes the connection much faster. However, in this case, the hostname cannot be used in the authorization table of MySQL, but only in ip format.

Wait_timeout=10 terminates links that have been idle for more than 10 seconds and avoids persistent connections [default 8 hours]

Max_connect_errors=10 / / Lock after 10 failed connections, unlock using flush hosts

Or mysqladmin flush-hosts unlock

~

SQL statement tuning:

Explain command: query select.

.type

This column is important to show which category is used by the connection and whether or not indexes are used.

The best to worst connection types are const, eq_reg, ref, range, indexhe, and ALL

Thank you for your reading, the above is the content of "tuning ideas and practical operation of MySQL". After the study of this article, I believe you have a deeper understanding of the tuning ideas and actual operation of MySQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.