MySQL Performance Tuning Overview

1. hardware optimization
A> memory ?? Large Memory, large memory Bit Width, do not use swap;
B> hard disk ?? 15000 rpm, RAID5, raid10
C> CPU ?? 64-bit, high clock speed, high cache, and high parallel processing capability
D> network ?? The standard Gigabit Nic is sufficient. Try to avoid unnecessary overhead such as firewall policies in the same LAN.

2. Architecture Optimization
A> vertical Disassembly
The simplest Service is responsible for multiple web, middleware, and database roles at the same time. After Vertical dismantling, It is dedicated to database servers to avoid performance degradation and instability caused by additional services; if the dedicated database server plane still cannot meet the requirements, you can consider adding memcached between the database and the application server.
B> horizontal dismantling
Master-slave synchronization, Server Load balancer, and high-availability clusters. When a single MySQL database cannot meet the increasing demand, you can consider adding multiple servers at the logic layer of the database, to achieve stable and efficient results.

Http://bbs.linuxtone.org/thread-5152-1-1.html
3. Operating System-level optimization
A> A 64-bit system can allocate more memory and service tuning to a single process, disable unnecessary services, modify file descriptor restrictions, and reserve more resources for MySQL;
B> File System Optimization: XFS is recommended for a separate file system for the data warehouse, which is generally more efficient and reliable.
C> enable the noatime option when mounting a partition.

4. Database Service Optimization
A> use the Linux/BSD operating system for compilation and installation, optimize the performance of compilation parameters, and streamline unnecessary functions.
B> suitable application interfaces.
C> make sure that each table is not too large. You can perform cross-cutting and vertical cutting on large tables. For example, if you want to obtain the lastlogin of an ID, you can create a small table with only "ID" and "lastlog", instead of a large table with dozens or hundreds of columns of data; in addition, updating a table with 10 million records is generally slow compared to updating 10 tables with 1 million records.
D> MyISAM engine, table-Level Lock, small single lock overhead, but large impact scope, suitable for reading tables with fewer writes, does not support transaction logs; Table lock does not have a deadlock
E> the InnoDB engine, row-level locks, and row locking costs are much higher than locking the entire table, but the impact scope is small. It is suitable for data tables with frequent write operations. Row-level locks may have deadlocks.

5. Optimization of parameters in my. CNF;
General principle of optimization: if there are too few MySQL resources, MySQL will not be able to use them; if there are too many MySQL resources, the entire OS may be dragged down.
A> optimize the overall resource usage;
Open_files_limit ?? The number of files that mysqld can open;
Max_connections ?? Number of concurrent client connections allowed;
Max_connect_errors ?? The number of wrong connections allowed for the host;
Table_cache ?? The number of tables that can be opened at each link;
Max_allowed_packet ?? Size of packages received from the server;
Thread_cache_size ?? Number of waiting threads cached;
B> specific Buffer Optimization
Sort_buffer_size ?? The buffer size that each thread can allocate;
Join_buffer_size ?? The size of the buffer that can be allocated for join operations without indexing;
Query_cache_size ?? Cache allocated for query;
Query_cache_limit ?? Do not cache query results larger than this limit;
Query_cache_min_res_unit ?? Do not cache query results smaller than the limit;
Tmp_table_size ?? If the temporary table in the memory exceeds this limit, it is written to the hard disk;
Binlog_cache_size ?? Cache size of binary log files;
Key_buffer_size ?? The index blocks of the MyISAM engine share the buffer zone;
Read_buffer_size ?? The length of the cache reserved for read operations that read data from data tables sequentially;
Innodb_additional_mem_pool_size ?? InnoDB is used to store data directory information and the size of the memory pool of other internal data structures. The more tables you have in your application, the more memory you need to allocate here.
Innodb_buffer_pool_size ?? The size of the memory buffer used by InnoDB to cache its data and indexes. In theory, the larger the memory, the better, but be careful not to set it too large. Otherwise, the competition in physical memory may lead to page change bumps in the operating system.
Innodb_data_file_path ?? Specify the size and size of the InnoDB tablespace. The initial tablespace is larger, which can reduce the system overhead of the Self-increased tablespace in the future.
Innodb_thread_concurrency ?? Number of threads allowed in the InnoDB core;
Innodb_log_buffer_size ?? The size of the buffer used by InnoDB to write log files to and from the disk. When the log size exceeds this limit, the log will be written to the disk, which is more overhead than the memory I/O.
Innodb_log_file_size ?? The size of each log file.
Max_allowed_packet ?? The maximum size of the request packet that the package service can process and the maximum size of the request that the service can process.
Http://bbs.linuxtone.org/thread-5152-1-1.html

6. query optimization,
A> when creating a table, the table structure should be reasonable. Each table should not be too large. The most precise type should be used in any case. For example, it is a good idea to use the int type for the ID column, but the text type is a stupid way; the time column uses date or datetime as appropriate.
B> index. All queries adopt scientific indexes. When the hit rate of a single index is low, the composite index is used;
C> perform logical operations (and, or, or operations greater than or less than a certain value) as much as possible during query );
D> reduce improper query statements and do not query columns that are not required by applications, such as select * from.
E> reduce the size of the transaction package;
F> Merge multiple small queries into a large one to reduce the overhead of each query creation/closure;
G> split some overly complex queries into multiple small queries, which is the opposite of the previous one.
H> Create and optimize stored procedures to replace a large number of external program interactions.
Http://bbs.linuxtone.org/thread-5152-1-1.html

7. debug tool:
A> vmstat ?? The vmstat Command reports statistics on Kernel threads, virtual memory, disks, traps, and CPU activity,
B> iostat ?? The iostat Command reports the input and output of CPU, hard disk, and other devices. You may need to install the sysstat RPM package.
C> top ?? Dynamically displays the resource usage of the current system. Compared with the preceding two commands, top focuses more on processes.
D> free ?? Displays memory and swap usage.
E> show processlist ?? Displays the currently running or waiting threads and determines which query statements are always waiting.
F> explain ?? "Explain + SQL statement" to view index usage.
G> show create table + "table_name "?? View the table structure of a specified table
H> select count (distinct "row_name") from "table_name"; check the uniqueness of the data in the column and determine which column to create an index.
I> Create index: Create an index and use show processlist and top to check the effect of the created index.