mysql 内存相关命令

原创已于 2023-05-10 14:44:42 修改 · 535 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #java

于 2023-05-09 12:19:39 首次发布

这篇博客介绍了如何使用MySQL的内置命令来监控和计算内存使用情况，包括全局状态、变量以及不同内存组件如InnoDB缓冲池的命中率。通过展示一系列SQL查询，博主展示了如何评估和优化MySQL内存配置，以确保高效运行。

show GLOBAL STATUS;

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

SELECT ( @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size
) / 1024 /1024 AS MAX_MEM_MB;

show VARIABLES;

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

show processlist ;

show full PROCESSLIST;

show table status;

Show global status ;

show global status like 'open%';

show variables like 'max_heap_table%';

show variables like 'innodb_buffer_pool%';

show status like 'innodb_buffer_pool_read%';

show variables like 'table_open_cache%';

show variables like 'flush_time%';

show variables like '%table%';

show open tables;

show global status like 'Max_used_connections'

show variables like 'max_tmp%'

show global status like '%tmp%table%'

show variables like 'query_cache_size';
show global status like 'Qcache%';
select * from information_schema.GLOBAL_STATUS where VARIABLE_NAME like 'Qcache%';

SELECT( ( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size+ @@join_buffer_size + @@binlog_cache_size + @@thread_stack +@@tmp_table_size + @@bulk_insert_buffer_size + @@max_allowed_packet +@@net_buffer_length ) ) / (1024*1024) AS MEMORY_MB;

select(@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@key_buffer_size)/ 1024 /1024 AS MEMORY_MB;

mysql使用的内存 = 全局共享内存+最大线程数×线程独享内存

把tmp_table_size放入全局共享内存,线程独享内存即连接数乘以的部分
SET @kilo_bytes=1024;
SET @mega_bytes=@kilo_bytes*1024;
SET @giga_bytes=@mega_bytes*1024;
SELECT (@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@binlog_cache_size+@@thread_cache_size+@@table_open_cache+@@table_definition_cache+@@key_buffer_size+@@tmp_table_size+@@max_connections*(@@thread_stack+@@sort_buffer_size+@@join_buffer_size+@@read_buffer_size+@@read_rnd_buffer_size))/@giga_bytes AS MAX_MEMORY_GB;

SET @kilo_bytes=1024;
SET @mega_bytes=@kilo_bytes*1024;
SET @giga_bytes=@mega_bytes*1024;
SELECT (@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@binlog_cache_size+@@thread_cache_size+@@table_open_cache+@@table_definition_cache+@@key_buffer_size+@@max_connections*(@@thread_stack+@@sort_buffer_size+@@join_buffer_size+@@read_buffer_size+@@read_rnd_buffer_size+@@tmp_table_size))/@giga_bytes AS MAX_MEMORY_GB;

修改连接数为10计算
SELECT (@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@binlog_cache_size+@@thread_cache_size+@@table_open_cache+@@table_definition_cache+@@key_buffer_size+10*(@@thread_stack+@@sort_buffer_size+@@join_buffer_size+@@read_buffer_size+@@read_rnd_buffer_size+@@tmp_table_size))/@giga_bytes AS MAX_MEMORY_GB;

SELECT (@@thread_stack+@@sort_buffer_size+@@join_buffer_size+@@read_buffer_size+@@read_rnd_buffer_size)/@giga_bytes AS MAX_MEMORY_GB;

show status like 'innodb_buffer_pool_read%';
InnoDB buffer pool 命中率：

InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100

此值低于99%，则可以考虑增加innodb_buffer_pool_size。

innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。

innodb_buffer_pool_read_requests：表示从内存中读取逻辑的请求数。
Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests*100
从磁盘完成读取的百分比如果小，则无需增加innodb_buffer_pool_size值。

缓冲池大小将自动调整为等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值

mysql> status
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 562
Current database: *** NONE ***

--------------
mysql Ver 8.0.33 for Win64 on x86_64 (MySQL Community Server - GPL)

Connection id: 562
Current database:
Current user: root@localhost
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Using delimiter: ;
Server version: 8.0.33 MySQL Community Server - GPL
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Binary data as: Hexadecimal
Uptime: 22 hours 19 min 27 sec

Threads: 46 Questions: 46564 Slow queries: 0 Opens: 3053 Flush tables: 3 Open tables: 2810 Queries per second avg: 0.579

mysql> SET @kilo_bytes=1024;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @mega_bytes=@kilo_bytes*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @giga_bytes=@mega_bytes*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT (@@thread_stack+@@sort_buffer_size+@@join_buffer_size+@@read_buffer_size+@@read_rnd_buffer_size)/@giga_bytes AS MAX_MEMORY_GB;
+---------------+
| MAX_MEMORY_GB |
+---------------+
| 0.0018 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT (@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@binlog_cache_size+@@thread_cache_size+@@table_open_cache+@@table_definition_cache+@@key_buffer_size+@@max_connections*(@@thread_stack+@@sort_buffer_size+@@join_buffer_size+@@read_buffer_size+@@read_rnd_buffer_size+@@tmp_table_size))/@giga_bytes AS MAX_MEMORY_GB;
+---------------+
| MAX_MEMORY_GB |
+---------------+
| 2.7843 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT (@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@binlog_cache_size+@@thread_cache_size+@@table_open_cache+@@table_definition_cache+@@key_buffer_size+@@tmp_table_size+@@max_connections*(@@thread_stack+@@sort_buffer_size+@@join_buffer_size+@@read_buffer_size+@@read_rnd_buffer_size))/@giga_bytes AS MAX_MEMORY_GB;
+---------------+
| MAX_MEMORY_GB |
+---------------+
| 0.4406 |
+---------------+
1 row in set (0.00 sec)

mysql>

--------------

[root@xxxxxxxxxxxx ~]# ps ax | grep mysql
409324 ? Ssl 61075:23 /usr/sbin/mysqld
4035391 pts/1 S+ 0:00 grep --color=auto mysql

[root@xxxx ~]# ps -aux | grep mysqld
mysql 409324 13.1 15.2 1959736 1188456 ? Ssl 2022 61090:30 /usr/sbin/mysqld
root 4035853 0.0 0.0 12136 1048 pts/2 S+ 14:24 0:00 grep --color=auto mysqld

[root@xxxx ~]# cat /proc/409324/status
Name: mysqld
Umask: 0026
State: S (sleeping)
Tgid: 409324
Ngid: 0
Pid: 409324
PPid: 1
TracerPid: 0
Uid: 27 27 27 27
Gid: 27 27 27 27
FDSize: 1024
Groups: 27
NStgid: 409324
NSpid: 409324
NSpgid: 409324
NSsid: 409324
VmPeak: 2129884 kB /////////////表示进程所占用最大虚拟内存大小
VmSize: 1959736 kB ///////表示进程当前虚拟内存大小
VmLck: 0 kB ///////表示被锁定的内存大小
VmPin: 0 kB
VmHWM: 1242872 kB ////////表示进程所占用物理内存的峰值
VmRSS: 1188456 kB //////////VmRSS对应的值就是物理内存占用
RssAnon: 1170140 kB
RssFile: 18316 kB
RssShmem: 0 kB
VmData: 1497652 kB ///////表示进程数据段的大小
VmStk: 132 kB ///////////////表示进程堆栈段的大小
VmExe: 54272 kB /////表示进程代码的大小
VmLib: 11256 kB ////// 表示进程所使用共享库的大小
VmPTE: 2836 kB ///// 表示进程页表项的大小
VmSwap: 0 kB
HugetlbPages: 0 kB
CoreDumping: 0
THP_enabled: 1
Threads: 60

[root@xxxxx~]# ps -e -o 'pid,comm,args,pcpu,rsz,vsz,stime,user,uid' | awk '$1 ~ /409324/'
409324 mysqld /usr/sbin/mysqld 13.1 1188444 1958708 2022 mysql 27
[root@xxxxx~]# cat /proc/409324/smaps | grep '^Rss:' | awk '{sum +=$2} END{print sum}'
1192064

[root@xxxxx~]# ps -e -o 'pid,comm,args,pcpu,rsz,vsz,stime,user,uid' | grep mysqld | sort -k5nr
409324 mysqld /usr/sbin/mysqld 13.1 1188444 1959736 2022 mysql 27
4036051 grep grep --color=auto mysqld 0.0 1048 12136 14:44 root 0
[root@xxxxx ~]#

[root@xxxxxxxx~]# pidstat -C mysqld -r
Linux 4.18.0-348.7.1.el8_5.x86_64 (XXXXX) 05/09/2023 _x86_64_ (2 CPU)

01:56:08 PM UID PID minflt/s majflt/s VSZ RSS %MEM Command
01:56:08 PM 27 409324 4.49 0.00 1959736 1188456 15.22 mysqld
[root@xxxxxxxx~]# pidstat -p 409324 -r 1 10
Linux 4.18.0-348.7.1.el8_5.x86_64 (XXXXX) 05/09/2023 _x86_64_ (2 CPU)

01:56:46 PM UID PID minflt/s majflt/s VSZ RSS %MEM Command
01:56:47 PM 27 409324 0.00 0.00 1959736 1188456 15.22 mysqld
01:56:48 PM 27 409324 0.00 0.00 1959736 1188456 15.22 mysqld
01:56:49 PM 27 409324 0.00 0.00 1959736 1188456 15.22 mysqld
01:56:50 PM 27 409324 0.00 0.00 1959736 1188456 15.22 mysqld
01:56:51 PM 27 409324 0.00 0.00 1959736 1188456 15.22 mysqld
01:56:52 PM 27 409324 0.00 0.00 1959736 1188456 15.22 mysqld
01:56:53 PM 27 409324 0.00 0.00 1959736 1188456 15.22 mysqld
01:56:54 PM 27 409324 0.00 0.00 1959736 1188456 15.22 mysqld
01:56:55 PM 27 409324 0.00 0.00 1959736 1188456 15.22 mysqld
01:56:56 PM 27 409324 0.00 0.00 1959736 1188456 15.22 mysqld
Average: 27 409324 0.00 0.00 1959736 1188456 15.22 mysqld
[root@xxxxxx~]# pmap 409324

show status like 'innodb_buffer_pool_read%';
InnoDB buffer pool 命中率：

InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100

此值低于99%，则可以考虑增加innodb_buffer_pool_size。

innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。

缓冲池大小将自动调整为等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值

show status like 'Table_open_cache_%';

If Table_open_cache_overflows / Uptime > 1 per second, then increase the setting.

If Table_open_cache_misses / Uptime > 1/sec, then increase table_open_cache

If Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) > 3%, then increase table_open_cacheand checktable_open_cache_instances`.

Consider increasing table_open_cache if

Opened_tables / Uptime > 2
Opened_table_definitions / Uptime > 1
Table_open_cache_misses / Uptime > 1
Table_open_cache_overflows / Uptime > 1
Open_tables = table_open_cache

SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES GROUP BY table_schema; ////////表数量