MySQL tuning _ MySQL

MySQL tuning bitsCN.com

Optimization and optimization:

1. database design and planning-it will be difficult to fix it later, estimate the data volume and what storage engine to use

2. data application-how to retrieve data and optimize SQL statements

3. mysql service optimization-memory usage and disk usage

4. operating system optimization-Number of kernel and tcp connections

5. upgrade the hardware.

Disk io planning

Raid technology: raid0 [xfs]

Swap partition: raid0 is recommended.

Disk Partition: A database is placed in one partition or 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. operating system optimization

Nic bonding technology,

Maximum number of tcp connections

Optimize the maximum limit on opening files in the system

Shut down unnecessary services of the operating system

5. mysql service optimization

Show status: View system resources

Show variables, which is defined in the my. cnf configuration file.

Show warnings

Show processlist displays all processes running in the system.

Show errors

Enable mysql slow query: --- analyze SQL statements and find SQL statements that affect efficiency

Log-slow-queries =/var/lib/mysql/slow. log is writable to mysql users.

Long_query_time = 2 query statements over 2 seconds are recorded

The above 2 is the query time, that is, it is recorded when the execution time of an SQL statement exceeds 5 seconds, and/var/lib/mysql/slow. log is the location of the log record.

Then restart the MySQL service.

Cache queries

Query_cache_size memory used to cache query statements [+ 8 M]

Mysql> show variables like '% query %'

Query_cache_size = 8 M

[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 of fragments may exist. Flush query cache sorts the fragments in the CACHE to obtain an idle block. [+ 8 M]

Qcache_free_memory idle memory in the cache

Qcache_hits increases when the query hits the cache.

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

Qcache_lowmen_prunes the cache is out of memory and must be cleaned up to provide more space for queries. It is better to look at this number for a long time; if this number continues to grow, it may indicate that the fragmentation is very serious, or the memory is very small.

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 index processing speed, especially the index reading speed. [+ 8 M]

Total number of key_read_requests requests

Key_reads indicates the number of requests that hit the disk.

(Key_read_requests-key_read)/key_read_requests: Hit rate

Key_buffer_size only applies to the MyISAM table. This value is used even if you do not use the MyISAM table, but the internal temporary disk table is a MyISAM table. You can use the check status value created_tmp_disk_tables to learn the details.

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

Temporary tablespace size: when order by and group by are used, the data is placed in the temporary table.

Tmp_table_size occupies the memory size. if it is too small, an error occurs during sorting.

Created_tmp_tables: number of temporary tables created

Max_tmp_tables = 32

Tmpdir =/tmp location of the temporary table on the hard disk

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~

Innodb table:

Create a tablespace file

[Mysqld]

Innodb_data_file_path = ibdata1: 10 M: autoextend

This configuration configures a single file with an expandable size of 10 Mb, named ibdata1. No file location is provided, so it is in the MySQL data directory by default.

If you specify the autoextend option for the final data file. If the data file consumes free space in the tablespace, InnoDB expands the data file. The extended range is 8 MB each time.

To specify the maximum size for an automatically scalable data file, use the max attribute. The following configuration allows ibdata1 to reach the limit of 500 MB:

[Mysqld]

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

InnoDB creates a tablespace file in the MySQL data directory by default. To specify a specific location, 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: 50 M; ibdata2: 50 M: autoextend

Mysql> show variables like 'innodb _ buffer_pool_size ';

Innodb_buffer_pool_size

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

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

Mysql> show variables like 'innodb _ % per % '; [open]

Innodb_file_per_table = 1 creates a tablespace file for each form.

Other parameters

Skip-locking

Cancels the external lock of the file system to reduce the chance of errors and enhance stability.

Skip-name-resolve

Disable the dns lookup function of mysql. This is faster!

To disable DNS resolution, and the connection speed is much faster. However, in this case, the host name cannot be used in the MySQL authorization table, but only in ip format.

Wait_timeout = 10 terminate connections with idle time more than 10 seconds to avoid persistent connections [8 hours by default]

Max_connect_errors = 10 // if the connection fails for 10 times, it will be locked. use flush hosts to unlock it,

Or unlock mysqladmin flush-hosts

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~

SQL statement optimization:

Explain command: query select.

. Type

This column is very important and shows the category used for the connection and whether or not the index is used.

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

This article is from the "technology" blog

BitsCN.com