Personal originality, permission to reprint, please indicate the source, the author, otherwise investigate legal responsibility. MySQL optimization

MySQL optimization

Personal originality, permission to reprint, please indicate the source, the author, otherwise investigate legal responsibility.
Number of MySQL connections
QPS number of queries per second

PV Page view

Peak Qps= Peak PV number * PV to connect to the database% of total PV * Average number of requests per PV
185 * 10% * 20 = 370

1 thread reuse number of threads
Show variables like "%thread%";

Thread_cache_size the maximum number of connection threads stored in the thread cache.
1G---8
2G---16
3G---32
>3G---64

Ways to modify values:
Temporary entry into force:
Set global thread_cache_size=16;

Methods for permanent entry:
Thread_cache_size=16 Write MY.CNF

2 connection limit number of connections
Set global max_connections=300;
Complement: 32G memory machine can be set to 2000

3 Check Memory-cache query cache to optimize query speed.
Mysql> show variable like "%cache_size%"
Query_cache_size 0-----Default is 0

Temporary settings:
Mysql> set global query_cache_siz=10000000 (10m)

Permanent entry into force:
Vim MY.CNF
Add below [mysqld]
Query_cache_size=128m (not necessarily the bigger the better, once opened, will each query to go cache, too big but slow)

Thread_cache_size=16
max_connections=1000
query_cache_size=128m

SQL Interface SQL interface to accept SQL request commands.
Parser:mysql The interpreter, interprets the SQL command, and then gives the command to the optimizer.
The optimizer of Optimizer:mysql is responsible for optimizing the execution of SQL and choosing the optimal way for data retrieval.
Caches and Buffers:mysql own cache buffers, different database engines use different caches.

InnoDB Buffering
Innodb_buffer_pool_size InnoDB buffer pool size. The larger the value the better.
If the MySQL dedicated server, the operating system memory is 70%-80%.
Set global innodb_buffer_pool_size=800000000;
ERROR 1238 (HY000): Variable ' Innodb_buffer_pool_size ' is a read only Variable
Error, read-only variable, can only be modified in the configuration file.

MyISAM Buffering
Key_buffer_size the buffer pool size of the MyISAM. Can be modified in a dynamic manner.
Set global key_buffer_size=134217728;

Slow log:
Slow log

To open a method:
Vim my.cnf (add two lines below mysqld)
Log_slow_queries=/tmp/slow.log (defines the location of the slow log)
Long_query_time=4 (record time in slow log, logged here when query time exceeds 4 seconds)

The Table_open_cache is used to limit the maximum number of cache tables that are opened. (How many tables are cached)
Mysql> Show variables like "%table%";
There's A: Table_open_cache 64 (default cache of 64 tables)
Setting: Set Global table_open_cache=128

Empty the number of cached tables:
Flush tables;

Temporary tables: Only the user can see them. Effect: Increase speed. Put in memory.
Tmp_table_size 16777216 Default 16M, no more than this value, the temporary table in memory, if exceeded, will be put into the hard disk.

Mysql>show variables like "%timeout%" (Units are seconds)

Connect_timeout 10 (Default time-out, more than 10 seconds to automatically disconnect if not connected)
The wait_timeout default is 28,800 seconds, 8 hours, and the connection is disconnected.
Net_read_timeout Network Read timeout time
Net_write_timeout Network Write timeout time

Log level for InnoDB:
How the cached data in the Innodb_flush_log_at_trx_commit:innodb log is written to the hard disk.
0 The least secure, every second to write data to the hard disk, power outages or database corruption data will be lost. Most efficient.
1 The most secure, real-time data written to the hard drive.
2 writes the system's cache first, writes the disk, the server loses power data. This is generally used.

Mysqlreport

./mysqlreport--user Root--password 123

Personal originality, permission to reprint, please indicate the source, the author, otherwise investigate legal responsibility. MySQL optimization