Day8-mysql Tuning

Dba

Database server???? Responding to client connection requests is particularly slow

1. Hardware configuration is too low--cpu, memory, hard disk
2. Network transfer speed
3. Excessive client access, busy database server
4. Software problem--version performance of data Service software No
5. A problem with the SQL statements written by the programmer

?

Hard Tuning

Soft tuning--operating parameters of the service runtime

?

1. The client connects to the database server?

2. The database server responds to client connection requests?

3. After successful connection, execute SQL operation (view Select, write)

4, if the execution is the query operation, first in the query cache to find data, find and return directly to the client, if not in the query cache, to the table corresponding to the file to find-the default will be the query results saved to the query cache, and then return the query results to the client

?

MySQL Tuning

To set run parameters for the database server runtime

View the maximum number of concurrent connections--depending on the hardware

Mysql> Show variables like "max_connections";

To view the number of connected concurrency

Mysql> Show global status Like "Max_used_connections";

Re-statistical data

Mysql>flush status;

1. Set the number of concurrent connections for the database server

Temporary settings

Mysql>set GLOBAL max_connections = value

Permanent settings

Write the max_connectinos= value in the my.cnf file [mysqld] below

?

Max_used_connectinos???? /max_connectinos= Value * 100%=85% (ideal value 85%)

Max_used_connectinos???? /max_connectinos=0.85 * 100%=85%

?

2, the client connection time-out setting

Mysql>sql command

Connection time-out period

connect_timeout= value???? Unit is seconds

Timeout to wait for sending instructions after connection

wait_timeout= value???? Unit is seconds

These two items are not generally modified by default

?

3. Setting of Cache space

Index cache Space???? The default is 8M???? Unit is byte

Mysql> Show variables like "Key_buffer_size"

Mysql> show global status like ' key_read% ';

Key_read_requests???? Total number of index read requests,

Key_reads not found in memory directly reading index data from hard disk

?

Query Cache Space

Each thread that needs to be sorted allocates a buffer of that size (increase this value to accelerate order by or group by)--when grouped and sorted more can be resized below

Mysql> Show variables like "sort_buffer_size%";

The length of the buffer reserved for read operations that read data from the data table order

Mysql> Show variables like "read_buffer_size%";

Query results output in a particular order (how to query with the ORDER BY clause)

Mysql> Show variables like "read_rnd_buffer_size%";

Number of threads that can be reused for saving in the cache--default value is 0

Mysql> Show variables like "thread_cache_size";

Viewing process usage

Mysql> show global status like ' thread% ';

threads_cached???? Threads_cached_size

threads_connected???? Existing links

threads_created???? Number of threads created

Threads_running???? A link that is working

The number of open tables for all threads

Mysql> Show variables like "table%_cache%";

Mysql> Show global status Like "open%table%";

Open_table_definitions????

Open_tables???????? Number of open tables

Opened_table_definitions????

Opened_tables???????? Number of open tables

?

Open_tables/table_cache * 100% =100% (ideal value <=95%)

?

4 Types of logs

Binlog log-The SQL statement that recorded the change

Slow query log--records SQL statements that display query results beyond a specified time

Query log--records all SQL statements executed by client after connecting to itself

???????? General-log

???????? general-log-file= Path

Error log--logs errors generated by the database service during startup and operation--default on--file name/var/lib/mysql/hostname. err--in the configuration file log-error= path

?

Enable slow query log for MySQL database server

Slow-query-log

slow-query-log-file= Path

Long-query-time= time-out (in seconds)-when the query time exceeds x seconds, it is logged

Log-queries-not-using-indexes???? Log queries that do not use indexes

?

Restart MySQL service after logging on

To view the contents of a slow query log file record

Using Mysqldumpslow???? Slow query log file name

?

Displays the settings of the current database server for query cache parameters

Mysql> Show variables like "query_cache%";

Query_cache_limit???? The query results are not saved to the query cache if the result exceeds the value inside

Query_cache_min_res_unit???? Cache block Size (equivalent to 4K of disk)

Query_cache_size???? Size of query cache

Query_cache_type???? Cache type (0 corresponds to OFF, close the query results into the cache, 1 corresponding on, open the query results into the cache, 2, open the query results into the cache, but the query need to add keywords)

Query_cache_wlock_invalidate???? When there are other clients writing on the MyISAM table, if the query is returned to cache results or wait for the write operation to complete before reading the table to get the result

?

Querying the value of a cache variable

Mysql> Show global status Like "qcache%";

Qcache_free_blocks???? Number of blocks in free blocks (more values, fragments)

Qcache_free_memory???? Free memory in the cache

Qcache_hits???? Number of times the query cache is logged (every time you lose everything, you add 1 when you hit the cache)

Qcache_inserts???????? Record the total number of queries (each time you insert a query, you add 1, the number of hits divided by the number of insertions is not the middle ratio)

Qcache_lowmem_prunes???? Number of records deleted when query cache space is low (cache is out of memory, and process cleanup is required to provide space for more queries)

qcache_not_cached???? Number of times that the query results are not allowed to be placed in cache space (number of queries that are not appropriate for caching)

Qcache_queries_in_cache???? The number of queries (and responses) currently cached

Qcache_total_blocks???? Number of blocks in cache

Day8-mysql Tuning