MySQL Learning (2)-mysql Server optimization

Tuning Ideas:

1. Database design and planning-it's a hassle to fix it later, estimate the amount of data, what storage engine to use

2. Application of data--how to fetch data, optimization of SQL statements

3.mysql Service Optimization--memory usage, disk usage

4. Operating system optimization--the number of cores, TCP connections

5. Upgrading hardware devices

Disk IO Planning

RAID Technology: RAID0[XFS]

Swap partition: Best to use RAID0

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

Physical partitions

CREATE TABLE T1 (ID int,name char) data directory= '/data/' index directory = '/data ';

Mkdir/data

Chown Mysql.mysql/data

Mysql> Show variables like '%part% ';

4. Optimization of the operating system

Nic Bonding Technology,

Number of TCP connections limit

Maximum limit for optimizing system open files

Turn off unnecessary services for the operating system

5.mysql Service Optimization

Show status See resources for the system

Show variables see variables, defined in the MY.CNF configuration file

Show Warnings View the most recent SQL statement generated by the error warning, see the other needs to see. err log

Show Processlist shows all the processes that are running on the system.

Show errors

Enable MySQL slow query:---Parse SQL statements to find SQL that affects efficiency

Log-slow-queries=/var/lib/mysql/slow.log This path has writable permissions for MySQL users

long_query_time=2 query more than 2 seconds to record the statement

The above 2 is the time of the query, that is, when a SQL execution time exceeds 5 seconds to record,/var/lib/mysql/slow.log is the location of the log records.

Then restart the MySQL service

To cache a query

Query_cache_size How much memory is used to cache query statements [+8m]

Mysql> Show variables like '%query% '

Query_cache_size=8m

[Email protected] mysql]# VIM/ETC/MY.CNF

Mysql> Show status like '%qcache% ';

Qcache_free_blocks: Indicates that the cache is too large. The number of contiguous memory in the cache. A large number indicates that there may be fragmentation. FLUSH QUERY Cache organizes the fragments in the cache to get a free block. [+8m]

Qcache_free_memory free memory in the cache

Qcache_hits increases each time a query is hit in the cache

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

The Qcache_lowmen_prunes cache is running out of memory and must be cleaned up to provide space for more queries. This number is best seen over a long period of time, and if the number is growing, it can be very fragmented, or memory is scarce.

Qcache_hits/qcache_inserts hit rate

Keyword buffers

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 to hit a disk

(Key_read_requests-key_read)/key_read_requests: Hit rate

Key_buffer_size only works on MyISAM tables. Even if you do not use the MyISAM table, the internal temporary disk table is the MyISAM table and this value is used. You can use the Check status value created_tmp_disk_tables to learn more.

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

Temporal tablespace Size: The order by and group by when the data is placed in a temporary table.

Tmp_table_size is the size of the memory, and if it is too small, an error occurs when sorting

Created_tmp_tables the number of temporary tables created

Max_tmp_tables=32

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

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

InnoDB table:

Create a tablespace file

[Mysqld]

Innodb_data_file_path=ibdata1:10m:autoextend

This setting configures a single file with a scalable size of 10MB, named Ibdata1. The location of the file is not given, so the default is within the MySQL data directory.

If you specify the Autoextend option for the final data file. If the data file runs out of free space in the tablespace, InnoDB expands the data file. The amplitude of the extension is 8MB each time.

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

[Mysqld]

innodb_data_file_path=ibdata1:10m:autoextend:max:500m

InnoDB creates a tablespace file by default in the MySQL data directory. To explicitly specify a 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 the 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 the InnoDB table, the role of innodb_buffer_pool_size is equivalent to key_buffer_size for the MyISAM table. InnoDB uses this parameter to specify the size of memory to buffer data and indexes. For a separate MySQL database server, the maximum value can be set to 80% of physical memory.

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

Mysql> Show variables like ' innodb_%per% '; [Suggest open]

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

Other parameters

Skip-locking

Remove external lock on file system, increase stability by reducing the chance of error

Skip-name-resolve

Turn off the DNS counter-check feature for MySQL. It's going to be fast!

Option to disable DNS resolution, the connection speed will be much faster. However, it is not possible to use the hostname in the MySQL authorization table and only use the IP format.

wait_timeout=10 terminate the link for more than 10 seconds of idle time, avoid long connections [default 8 hours]

MAX_CONNECT_ERRORS=10//10 connection failed to lock, use flush hosts to unlock,

or mysqladmin flush-hosts unlock

MySQL Learning (2)-mysql Server optimization