mysql optimize

由 datahunter 在三, 25/10/2017 - 15:57 發表

最後更新: 2018-05-04

Performance monitor - mytop

介紹

mytop (Perl)

periodically runs the "show processlist" and "show global status"

Install (Centos 7)

yum install mytop -y

Configuring (/root/.mytop)

touch /root/.mytop

chmod 600 /root/.mytop

# display refreshes
delay=3
# idle (sleeping) threads to appear in the list in mytop
# 1 => see idle processes
idle=0

Usage

# enter the MySQL root password at the prompt.
mytop --prompt

Help

Toubleshoot

Error in option spec: "long|!"

vim /usr/bin/mytop

Find

"long|!"

"#" 了那行

Hotkey

t thread view (default)
f To view the entire query
e to explain the query
c command view
o reverse the sort order by pressing

GUI 解釋

days+hours:minutes:seconds

qps: average queries per second,

Slow: average the number of slow queries,

Se/In/Up/De(%): average the percentage of Select, Insert, Update, and Delete queries.

Threads: 1 ( 1/ 1)

1 connected threads

1 are active ( => 0 sleeping)

1 threads in the thread cache

Bps in/out: shows that since startup bytes that MySQL has sent and received

Now in/out:

Time: number of seconds of idle time

Key Efficiency

It is an indication of how much value you are getting from the index caches held within MySQL's memory.

If your key efficiency is high, then most often MySQL is performing key lookups from within memory space,

which is much faster than having to retrieve the relevant index blocks from disk.

For MyISAM, increase the value of key-buffer-size
For InnoDB, increase the value of innodb-buffer-pool-size

good => Key Efficiency > 90

Setting

[mysqld]
key_buffer_size=8M   # default

Check

show variables like 'key_buffer_size';

mysqltuner.pl

# 介紹

MySQLTuner is a read only script.

perl script

Home Page: http://github.com/major/MySQLTuner-perl

# DL

wget http://mysqltuner.pl/ -O mysqltuner.pl

# usage

./mysqltuner.pl

# result in a file

./mysqltuner.pl --outputfile /tmp/result_mysqltuner.txt

./mysqltuner.pl --silent --outputfile /tmp/result_mysqltuner.txt

# remotely

./mysqltuner.pl --host targetDNS_IP --user admin_user --pass admin_password

# update

--checkversion Check for updates to MySQLTuner (default: don't check)

--updateversion Check for updates to MySQLTuner and update when newer version is available (default: don't check)

innodb_log_file_size

innodb_log_file_size

# The size in bytes of each log file in a log group.
# Total = innodb_log_file_size X innodb_log_files_in_group (Default: 2)
# InnoDB writes to the files in a circular fashion
# Recommand: 25% of buffer pool size
# The larger the value, the less checkpoint flush activity is required in the buffer pool
# Larger log files also make crash recovery slower

# When InnoDB has written the redo log files full,
# it must write the modified contents of the buffer pool to disk in a checkpoint.
# Small redo log files cause many unnecessary disk writes.

show variables like 'innodb_log_%';

+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_log_buffer_size    | 1048576 |
| innodb_log_file_size      | 5242880 |
| innodb_log_files_in_group | 2       |
| innodb_log_group_home_dir | ./      |
+---------------------------+---------+

ll -h /var/lib/mysql/ib_logfile*

-rw-rw---- 1 mysql mysql 5.0M Oct 25 16:19 /var/lib/mysql/ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Oct 25 16:19 /var/lib/mysql/ib_logfile1

ib_logfile <-- redo log

----------

innodb_fast_shutdown

To save time, certain flush operations are skipped.

0, InnoDB does a slow shutdown, a full purge and a change buffer merge before shutting down.
1 (the default), InnoDB skips these operations at shutdown, a process known as a fast shutdown
2, InnoDB flushes its logs and shuts down cold, as if MySQL had crashed;
(no committed transactions are lost, but the crash recovery operation makes the next startup take longer. )

slow shutdown => innodb_fast_shutdown=0

----------

Change log file size

Yes it is safe to delete the log file once mysqld has been shutdown

mysql -p -e"SET GLOBAL innodb_fast_shutdown = 0"

# 在另一個 session
show variables like 'innodb_fast_shutdown';

service mysqld stop

# 在另一個 check log

171025 16:45:31 [Note] Event Scheduler: Purging the queue. 0 events
171025 16:45:31  InnoDB: Starting shutdown...
171025 16:45:34  InnoDB: Shutdown completed; log sequence number 0 931357918
171025 16:45:34 [Note] /usr/libexec/mysqld: Shutdown complete

mv /var/lib/mysql/ib_logfile[01] /tmp

service mysqld start

innodb_log_buffer_size

# A large log buffer enables large transactions to run
# without the need to write the log to disk before the transactions commit.

tmp_tables

http://datahunter.org/tmp_tables

Cache Setting

Query Cache

Deprecated as of MySQL 5.7.20, and removed in MySQL 8.0 (query cache is useless if InnoDB is being used)

A large query cache size leads to significant performance degradation.

This is because of cache overhead and locking.

Cacheable queries take out an exclusive lock on MySQL's query cache.

In addition, any insert, update, delete, or other modifications to a table

causes any relevant entries in the query cache to be flushed.

To check if query cache is supported:

show variables like 'have_query_cache';

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Checking Setting

show variables like 'query_cache_%';

+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

Setting

/etc/my.cnf

query_cache_type=1
query_cache_size=128M
query_cache_limit=1M

query_cache_type

# Clients can set the SESSION value to affect their own use of the query cache.

0 = OFF
1 or ON (Default)
2 or DEMAND # Cache results only for cacheable queries that begin with SELECT SQL_CACHE.

query_cache_size (重要)

# memory allocated for caching query results, Default value is 0 (Disable), multiples of 1kB

# query cache excessively large, which increases the overhead required to maintain the cache

query_cache_limit

# maximum size of a single resultset in the cache.

Hitrate

SHOW STATUS LIKE 'Qcache%';

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 2         |
| Qcache_free_memory      | 126626896 |
| Qcache_hits             | 12882680  |
| Qcache_inserts          | 10032     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 13930     |
| Qcache_queries_in_cache | 4460      |
| Qcache_total_blocks     | 8945      |
+-------------------------+-----------+

Qcache_lowmem_prunes # 因為缺少內存而被從緩存中刪除的查詢數目

show global status like 'com_select';

Cache 的效率

Qcache_hits # Each time a query result is returned from cache
Qcache_inserts # Each time a query result is inserted into the query cache
Com_select # Each time a query result is returned without the cache

# Hit Rate Amongst All Queries

Qcache_hits / (QCache_hits + Com_select)

# Hit Rate Amongst Cacheable Queries

Qcache_hits / (Qcache_hits + Qcache_inserts)

SQL

Ver. => 5.5

Com_select: Number of SELECT commands executed. Also includes queries that make use of the query cache.

com_xxx: com_select, com_insert, com_delete, com_update ....

SELECT * FROM INFORMATION_SCHEMA.TABLES;

+----------------------+----------------+
| VARIABLE_NAME        | VARIABLE_VALUE |
+----------------------+----------------+

select (select VARIABLE_VALUE from INFORMATION_SCHEMA.GLOBAL_STATUS where VARIABLE_NAME='Qcache_hits')/
(select VARIABLE_VALUE from INFORMATION_SCHEMA.GLOBAL_STATUS where VARIABLE_NAME='Com_select') as Hitrate;

Poorly performing cache:

More queries have been added, and more queries have been dropped, than have actually been used.

FLUSH QUERY CACHE

You can defragment the query cache to better utilize its memory with the "FLUSH QUERY CACHE" statement.

The statement does not remove any queries from the cache.

The "RESET QUERY CACHE" statement removes all query results from the query cache.

The FLUSH TABLES statement also does this.

Every cached query requires a minimum of two blocks (one for the query text and one or more for the query results).

Also, every table that is used by a query requires one block.

However, if two or more queries use the same table, only one table block needs to be allocated.

The query cache allocates blocks with a minimum size given by the query_cache_min_res_unit system variable.

The default value of query_cache_min_res_unit is 4KB.

table_cache

# The number of open tables for all threads. (descriptor)
# increase the table cache by checking the
# "SHOW GLOBAL STATUS like 'Opened_tables';"
# cmd: "FLUSH TABLES" 會 close 返開左的 tables
# Default: 64

table_cache = 1024

memlock

# Lock the mysqld process in memory.
# 防止 mysqld to swap to disk

--memlock

low-priority-updates

# Give table-modifying operations (INSERT, REPLACE, DELETE, UPDATE) lower priority than selects.

--low-priority-updates

Buffer

key_buffer_size # MyISAM 才用此設定

Index blocks for MyISAM tables are buffered and are shared by all threads.

For index blocks, a special structure called the key cache (or key buffer) is maintained.

For data blocks, MySQL uses no special cache. (Instead it relies on the native OS file system cache.)

When the key cache is not operational, index files are accessed using only the native file system buffering provided by the operating system.

Usually the server follows an LRU (Least Recently Used) strategy:

When choosing a block for replacement, it selects the least recently used index block.

# buffer used for index blocks (MyISAM tables only)
# You can increase the value to get better index handling for all reads and multiple writes;
# 對 MyISAM 尤其重要
# Default: 8388608 <- 8 M

key_buffer_size = 256M

myisam_sort_buffer_size

# sorting MyISAM indexes during a REPAIR TABLE
# creating indexes with CREATE INDEX or ALTER TABLE.

myisam_sort_buffer_size = 128M

read_buffer_size

# Default: 128k
# does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans.

read_buffer_size = 4M

read_rnd_buffer_size

read_rnd_buffer_size = 12M

Sort & Join Buffer

* It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins / sort.

sort_buffer_size

# Default: 2M
# speed up ORDER BY or GROUP BY operations
# 當 SHOW GLOBAL STATUS like 'Sort_merge_passes'; 不斷上升才要調整它

sort_buffer_size = 4M

join_buffer_size

join_buffer_size = 512K

Memory Usage

# 不知為何, MySQL 吃了我 6 Gbyte

查看 mysql 最大的 memory 使用量

SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_additional_mem_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size )
) / (1024 * 1024) AS MAX_MEMORY_MB;

主的的用量在 max_connections 倍在出來

SELECT (
@@max_connections * ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size )
) / (1024 * 1024) AS MAX_MEMORY_MB;

會影響的 setting

read_buffer_size
read_rnd_buffer_size
sort_buffer_size
join_buffer_size
binlog_cache_size
thread_stack
tmp_table_size

mysqlcalculator

http://www.mysqlcalculator.com/

connections

# MAX

SHOW STATUS WHERE `variable_name` = 'Max_used_connections';

# The number of currently open connections

show status where `variable_name` = 'Threads_connected';

show processlist;

show session status;

tmp_table_size

# Maximum size for internal (in-memory) temporary tables.

# If a table grows larger than this value, it is automatically converted to disk based table

# This limitation is for a single table. There can be many of them.

SELECT @@tmp_table_size / (1024 * 1024) AS MAX_MEMORY_MB;

MySQL 8.0

key_buffer_size

cache index blocks for MyISAM tables

* only affects MyISAM tables # InnoDB 用 "innodb_buffer_pool_size"

internal_tmp_mem_storage_engine

MEMORY # Default

creates in-memory tables with a hash index.

It's very fast.

Limitations: MEMORY tables cannot contain BLOB or TEXT columns.

TempTable

Introduced in MySQL 8.0. Supports all column types
(avoid the limitations of the MEMORY engine)

It uses a combination of a hash index and a B-tree index
(which can handle larger tables more efficiently)

max_heap_table_size

sets the maximum size to which user-created MEMORY tables