MySQL Database tuning

MySQL Tuning
Hardware configuration
Network bandwidth
MySQL Run parameters
Slow query log
Network architecture
Multiple instances (multiple database services running on a single server)
Sub-Library
Sub-table

When a database server handles requests from clients that are slow,
What could be the cause?

Low hardware configuration: (Memory CPU hard disk I/O)
View Hardware usage
Free-m
Top

Network bandwidth:
Test bandwidth (extended bandwidth)

The

provides a version of the database services software is too low:
VIM/ETC/MY.CNF
[mysqld]
Option-value (not selected is default)
/etc/init.d/mysql restart
The database server processes query requests: Connection pool SQL Interface Analyzer optimization Storage engine file System administration Tool
When a query request is received, the query cache is now looking for records, if any, directly back to the client,
if not, go to the table to find, Once found, the results are stored in the query cache, and then the data is returned to the client.

View the parameter values for the service run
mysql> show variables like "% keyword%"; (view)
mysql> set global variable name = value; (modified)

Concurrent Connections
Mysql> Show variables like "%connect%"; (view link)
max_connections=151 (default 151)
Mysql> set global max_ (connections=200)
mysql> show global Status Like "%connect%"; max_used_connections; (view maximum number of connections)
Concurrency optimal formula: Max_used_connections/max_connections = 0.85*100%=85%

Link time-out
mysql> show variables like "% timeout% "; (View time-out time)
time-out when establishing a connection connect_timeout=10 default of 10 seconds
Wait for SQL command time-out after establishing a connection wait_timeout=28800 default 28,800 seconds

Query Cache size
mysql> show variables like "%cache%"; (view query)
mysql> show variables like "%query_cache%"; (view query size)
query_cache_size=1048576 is greater than 1048576 and is not allowed to be stored in

To view statistics for a query cache
Mysql> Show global status Like "%qcache%";
Qcache_hits (Find once in query cache +1)
Qcache_inserts (total number of queries)

Number of threads that can be reused
The number of threads that can be stored in memory and can be reused
Mysql> Show variables like "%thread%";
Thread_cache_size=9 default of 9

Total number of open tables for all threads
Mysql> mysql> Show variables like "%table%";
table_open_cache=2000 Default 2000
Key-buffer-size: Key index cache size for the MyISAM engine
Sort-buffer-size: Allocates a cache space of this size for each thread to be sorted
Read-buffer-size: Cache size reserved for sequential reading of table records
Thread-cache-size: Number of threads allowed to be reused in cache

The program Ape writes the SQL command to extract the data too complex:
View Records
Single-table multi-table nested joins
FIX: Enable slow query log, log after client connection
SQL command to display query results over a specified time
Log type for MySQL service:
Error log: Default enabled, logged service error messages during startup and run
BINLG log: Default is not enabled, after logging client Connection server, execute the SQL command other than the query
Slow query log: SQL command to display query results over a specified time after logging client connections is not enabled by default
Query log: All SQL commands that are executed after logging the client link are not enabled by default

Use of slow query logs:
Vim/etc/my.cnf
Slow-query-log (log storage location, default in database directory, host name-slow.log)
Long-query-time=5 (Time-out, default 10 seconds)
/etc/init.d/mysql Restart (restart service)
[[email protected] mysql]# mysqldumpslow/var/lib/mysql/158-slow.log >/opt/sql.txt (redirect the timeout query to the file)

Use of the query log:
Vim/etc/my.cnf
General-log (log storage location, default in database directory, host name. log)
[email protected] mysql]# cat 158.log

There is a problem with the network architecture:
Re-planning your network

——————————————————————————————————————————————————————————————————————————————————————————

MySQL Database tuning