空LINUX 系统
- <p>[root@localhost ~]# free -m</p><p> total used free shared buff/cache available</p><p>Mem: 7821 96 7577 8 147 7304</p><p>Swap: 8063 0 8063</p>
MYSQL 参数文件
- <div>#Global Memon Set###### innodb buf 2048MB, binlog_cache=16MB;logbuf=16MB
- innodb_buffer_pool_size = 2048M
- innodb_log_buffer_size= 16M
- key_buffer_size = 8M
- max_heap_table_size =16M
- ###innodb buffer subordinate
- innodb_sort_buffer_size = 4M #插入排序 1M
- innodb_ddl_buffer_size = 4M
- innodb_adaptive_hash_index = OFF #来禁用
- innodb_buffer_pool_dump_at_shutdown = ON
- innodb_buffer_pool_load_at_startup = ON
- #### Thread Memon Set 8+8+8+8+16+16+8=72+1=73MB
- join_buffer_size=8388608 #8M
- sort_buffer_size=8388608 #8M
- read_buffer_size=8388608 #8M
- read_rnd_buffer_size=8388608 #8M
- net_buffer_length = 16384 #16K max 1M
- tmp_table_size =16777216 #16M
- binlog_cache_size =16777216 #16M
- bulk_insert_buffer_size=8388608 #8M
- thread_cache_size = 32
- thread_stack = 256K
- #limit
- max_allowed_packet = 16M
- interactive_timeout = 600
- lock_wait_timeout = 300
- max_execution_time = 100000
- max_connections=500</div>
启动MYSQL
- <div>root@localhost ~]# free -m
- total used free shared buff/cache available
- Mem: 7821 1590 5988 8 242 5762
- Swap: 8063 0 8063
- [root@localhost ~]# ps -o rss,command -C mysqld
- RSS COMMAND
- 1523928 /usr/sbin/mysqld
- [root@localhost ~]# top -p 4322
- PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
- 4322 root 20 0 5522936 1.5g 17748 S 0.0 19.0 0:09.15 mysqld </div>
物理内存使用了 1590 对比MYSQL使用1494MB
2.1 从MYSQL层统计内存使用
- <div><font color="#000000">mysql> SELECT * FROM sys.memory_global_total;</font>
- <font color="#000000">+-----------------+</font>
- <font color="#000000">| total_allocated |</font>
- <font color="#000000">+-----------------+</font>
- <font color="#000000">| 2.79 GiB |</font>
- <font color="#000000">+-----------------+</font>
- <font color="#000000">1 row in set (0.02 sec)</font></div>
mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, FORMAT_BYTES(SUM(current_alloc)) AS current_alloc
FROM sys.x$memory_global_by_current_bytes
GROUP BY SUBSTRING_INDEX(event_name,'/',2)
ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area | current_alloc |
+---------------------------+---------------+
| memory/innodb | 2.15 GiB |
| memory/performance_schema | 606.30 MiB |
| memory/mysys | 40.88 MiB |
| memory/sql | 12.20 MiB |
| memory/temptable | 1.00 MiB |
| memory/mysqld_openssl | 134.76 KiB |
| memory/mysqlx | 2.67 KiB |
| memory/vio | 2.12 KiB |
| memory/myisam | 696 bytes |
| memory/csv | 88 bytes |
| memory/blackhole | 88 bytes |
+---------------------------+---------------+
11 rows in set (0.00 sec)
- <div><b><font color="#000000">mysql> show engine innodb status;</font></b>
- <b><font color="#000000"> ----------------------
- BUFFER POOL AND MEMORY
- ---------------------- </font></b>
- <b><font color="#000000">Total large memory allocated 0 </font></b>
- <b><font color="#000000">Dictionary memory allocated 489415</font></b>
- <b><font color="#000000">Buffer pool size 131056 </font></b>
- <b><font color="#000000">Buffer pool size, bytes 2147221504 </font></b>
- <b><font color="#000000">Free buffers 99486 </font></b>
- <b><font color="#000000">Database pages 31570 </font></b>
- <b><font color="#000000">Old database pages 11810 </font></b>
- <b><font color="#000000">Modified db pages 0 </font></b>
- <b><font color="#000000">Pending reads 0 </font></b></div>
|
