亚洲人妻精品在线,熟女丝袜视频在线

processlist命令的輸出結(jié)果顯示了有哪些線程在運(yùn)行

show full processlist;

1.查參數(shù)配置

SELECT @@query_cache_size,
     @@key_buffer_size,
     @@innodb_buffer_pool_size ,
     @@innodb_log_buffer_size ,
     @@tmp_table_size ,
     @@read_buffer_size,
     @@sort_buffer_size,
     @@join_buffer_size ,
     @@read_rnd_buffer_size,
     @@binlog_cache_size,
     @@thread_stack,
     (SELECT COUNT(host) FROM  information_schema.processlist where command<>'Sleep')\G;

*************************** 1. row ***************************
   @@query_cache_size: 67108864
   @@key_buffer_size: 402653184
   @@innodb_buffer_pool_size: 6442450944
   @@innodb_log_buffer_size: 16777216
   @@tmp_table_size: 268435456
   @@read_buffer_size: 4194304
   @@sort_buffer_size: 6291456
   @@join_buffer_size: 8388608
   @@read_rnd_buffer_size: 16777216
   @@binlog_cache_size: 4194304
   @@thread_stack: 262144
(SELECT COUNT(host) FROM  information_schema.processlist where command<>'Sleep'): 
                      4

目前積累的使用經(jīng)驗(yàn)中，存儲(chǔ)過程&函數(shù)&觸發(fā)器&視圖在MySQL場(chǎng)景下是不適合的。性能不好，又容易發(fā)現(xiàn)內(nèi)存不釋放的問題，所以建議盡量避免.

2.存儲(chǔ)過程&函數(shù)

#5.7
SELECT db,type,count(*) FROM mysql.proc
WHERE db not in ('mysql','information_schema','performance_schema','sys')
GROUP BY db, type;

#8.0
SELECT Routine_schema, Routine_type FROM information_schema.Routines
WHERE Routine_schema not in ('mysql','information_schema','performance_schema','sys')
GROUP BY Routine_schema, Routine_type;

3.視圖

SELECT  TABLE_SCHEMA , COUNT(TABLE_NAME) FROM information_schema.VIEWS
WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys')
GROUP BY TABLE_SCHEMA;

4.觸發(fā)器

SELECT TRIGGER_SCHEMA, count(*) FROM information_schema.triggers 
WHERE  TRIGGER_SCHEMA not in ('mysql','information_schema','performance_schema','sys')
GROUP BY TRIGGER_SCHEMA;

5.1 總內(nèi)存使用

SELECT SUM( CAST(replace(current_alloc,'MiB','')  as DECIMAL(10, 2)) ) 
FROM sys.memory_global_by_current_bytes
WHERE current_alloc like '%MiB%';

5.2 分事件統(tǒng)計(jì)內(nèi)存

#1
SELECT event_name,SUM( CAST(replace(current_alloc,'MiB','')  as DECIMAL(10, 2)) )    
FROM sys.memory_global_by_current_bytes    
WHERE current_alloc like '%MiB%' GROUP BY event_name  
ORDER BY SUM(CAST(replace(current_alloc,'MiB','')  as DECIMAL(10, 2))  ) DESC;

#2
SELECT event_name,sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED)
FROM performance_schema.memory_summary_global_by_event_name
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 10;

5.3 賬號(hào)級(jí)別統(tǒng)計(jì)

SELECT user,event_name,current_number_of_bytes_used/1024/1024 as MB_CURRENTLY_USED
FROM performance_schema.memory_summary_by_account_by_event_name
WHERE host<>"localhost"
ORDER BY  current_number_of_bytes_used DESC LIMIT 10;

5.4 線程對(duì)應(yīng)sql語句，內(nèi)存使用統(tǒng)計(jì)

#1
SELECT thread_id,event_name,sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED) 
FROM performance_schema.memory_summary_by_thread_by_event_name
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 20;

#2
SELECT m.thread_id tid,m.user,esc.DIGEST_TEXT,m.current_allocated,m.total_allocated
FROM sys.memory_by_thread_by_current_bytes m,performance_schema.events_statements_current esc
WHERE m.thread_id=esc.THREAD_ID

5.5 打開所有內(nèi)存性能監(jiān)控，會(huì)影響性能，需注意

#打開
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

#關(guān)閉
UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE 'memory/%';

#查看使用
SELECT * FROM performance_schema.memory_summary_global_by_event_name        
WHERE EVENT_NAME LIKE 'memory/%' 
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC;

5.6 系統(tǒng)表內(nèi)存監(jiān)控信息

select * from sys.x$memory_by_host_by_current_bytes;
select * from sys.x$memory_by_thread_by_current_bytes;
select * from sys.x$memory_by_user_by_current_bytes;
select * from sys.x$memory_global_by_current_bytes;
select * from sys.x$memory_global_total;
select * from performance_schema.memory_summary_by_account_by_event_name;
select * from performance_schema.memory_summary_by_host_by_event_name;
select * from performance_schema.memory_summary_by_thread_by_event_name;
select * from performance_schema.memory_summary_by_user_by_event_name;
select * from performance_schema.memory_summary_global_by_event_name;

6.top 命令

PID   USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                          
69265 mysql     20   0   11.5g   7.9g   6172 S   1.3 68.1  69:27.64  mysqld

free -h命令

[root@mpb91 ~]# free -h
              total        used        free      shared  buff/cache   available
Mem:            11G        8.2G         75M        7.9M        3.3G        3.3G
Swap:          8.0G         28M        8.0G

8.ps命令

[root@mpb91 ~]# ps eo user,pid,vsz,rss $(pgrep -f 'mysqld')
USER      PID     VSZ        RSS
mysql     69265   12017232   8296068

9.pmap 命令
pmap是Linux調(diào)試及運(yùn)維一個(gè)很好的工具，查看進(jìn)程的內(nèi)存映像信息

用法1：執(zhí)行一段時(shí)間記錄數(shù)據(jù)變化，最少20個(gè)記錄，下面69265是MySQL pid

[root@mpb91 ~]# 
while true; do pmap -d  69265  | tail -1; sleep 2; done

mapped: 12017236K    writeable/private: 9644980K    shared: 128K
mapped: 12017236K    writeable/private: 9644980K    shared: 128K
mapped: 12017236K    writeable/private: 9644980K    shared: 128K
mapped: 12017236K    writeable/private: 9644980K    shared: 128K
mapped: 12017236K    writeable/private: 9644980K    shared: 128K
mapped: 12017236K    writeable/private: 9644980K    shared: 128K
mapped: 12017236K    writeable/private: 9644980K    shared: 128K
mapped: 12017236K    writeable/private: 9644980K    shared: 128K

用法2：linux 命令pmap MySQL pid導(dǎo)出內(nèi)存，下面69265是MySQL pid

[root@mpb91 ~]# pmap -X -p 69265 > /tmp/memmysql.txt

[root@mpb91 ~]# cat /tmp/memmysql.txt

69265:   /usr/local/servers/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/data/mysql.pid
         Address Perm   Offset Device     Inode     Size     Rss     Pss Referenced Anonymous Swap Locked Mapping
        00400000 r-xp 00000000  fd:00 603980265    23160    5936    5936       5920         0    0      0 /usr/local/servers/mysql/bin/mysqld
        01c9e000 rw-p 0169e000  fd:00 603980265     1668     908     908        772       556    0      0 /usr/local/servers/mysql/bin/mysqld
        01e3f000 rw-p 00000000  00:00         0      760     612     612        384       612    0      0 
        03ce9000 rw-p 00000000  00:00         0      132     132     132         76       132    0      0 [heap]
        03d0a000 rw-p 00000000  00:00         0   207144  207144  207144     206776    207144    0      0 [heap]
...
    7ffd55555000 rw-p 00000000  00:00         0      132      84      84         12        84    0      0 [stack]
    7ffd555bd000 r-xp 00000000  00:00         0        8       4       0          4         0    0      0 [vdso]
ffffffffff600000 r-xp 00000000  00:00         0        4       0       0          0         0    0      0 [vsyscall]
                                                ======== ======= ======= ========== ========= ==== ====== 
                                                12017236 8307772 8306994    8146552   8300092    0      0 KB

RSS就是這個(gè)process實(shí)際占用的物理內(nèi)存。
Dirty: 臟頁的字節(jié)數(shù)（包括共享和私有的）。
Mapping: 占用內(nèi)存的文件、或[anon]（分配的內(nèi)存）、或[stack]（堆棧）。
writeable/private：進(jìn)程所占用的私有地址空間大小，也就是該進(jìn)程實(shí)際使用的內(nèi)存大小。

1.首先使用/top/free/ps在系統(tǒng)級(jí)確定是否有內(nèi)存泄露。如有，可以從top輸出確定哪一個(gè)process。

2.pmap工具是能幫助確定process是否有memory leak。確定memory leak的原則：writeable/private (‘pmap –d’輸出）如果在做重復(fù)的操作過程中一直保持穩(wěn)定增長(zhǎng)，那么一定有內(nèi)存泄露

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

MYSQL 內(nèi)存排查

MYSQL 內(nèi)存排查

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

MYSQL 內(nèi)存排查

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av