MySQL Memory / Buffer Pool
root@okprd5328:/usr/sbin#free
total(KB) used free shared buff/cache available
Mem: 7492252 1368184 386208 472708 5737860 5287860
Swap: 3145724 122624 3023100
7GB RAM, 1GB Used, 3.8GB Free
[root@col-lnx229 ~]# cat /proc/meminfo
MemTotal: 8010088 kB
MemFree: 201568 kB
MemAvailable: 5665884 kB
Buffers: 9624 kB
Cached: 5735120 kB
SwapCached: 31220 kB
Active: 3957396 kB
Inactive: 3518076 kB
Active(anon): 1139516 kB
Inactive(anon): 754600 kB
Active(file): 2817880 kB
Inactive(file): 2763476 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 2097148 kB
SwapFree: 1658620 kB
Dirty: 64 kB
Writeback: 0 kB
AnonPages: 1709480 kB
Mapped: 35884 kB
Shmem: 163480 kB
Slab: 217828 kB
SReclaimable: 188868 kB
SUnreclaim: 28960 kB
KernelStack: 4960 kB
PageTables: 12152 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 6102192 kB
Committed_AS: 1584896 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 164048 kB
VmallocChunk: 34359341052 kB
HardwareCorrupted: 0 kB
AnonHugePages: 602112 kB
CmaTotal: 0 kB
CmaFree: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
DirectMap4k: 90048 kB
DirectMap2M: 8298496 kB
*Note :
MemFree: The amount of physical RAM, in kilobytes, left unused by the system.
MemAvailable: An estimate of how much memory is available for starting new applications, without swapping. Calculated from MemFree, SReclaimable, the size of the file LRU lists, and the low watermarks in each zone. The estimate takes into account that the system needs some page cache to function well, and that not all reclaimable slab will be reclaimable, due to items being in use. The impact of those factors will vary from system to system.
Check top processes utilize system memory
[root@col-lnx229 ~]# ps -eo user,pcpu,pmem | tail -n +2 | awk '{num[$1]++; cpu[$1] += $2; mem[$1] += $3} END{printf("NPROC\tUSER\tCPU\tMEM\n"); for (user in cpu) printf("%d\t%s\t%.2f\t%.2f\n",num[user], user, cpu[user], mem[user]) }'
NPROC USER CPU MEM
1 polkitd 0.00 0.00
1 dbus 0.00 0.00
2 foongs 0.00 0.00
1 mysql 6.40 20.20
2 postfix 0.00 0.00
1 ntp 0.00 0.00
202 root 1.00 1.70
Check default InnoDb buffer pool in bytes.
mysql> show variables like 'innodb_buffer_pool_size'; (in bytes)
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | XXXXX |
+-------------------------+-----------+
1 row in set (0.01 sec)
mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1 |
+------------------------------+-------+
1 row in set (0.00 sec)
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 16777216 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
Update Innodb_buffer_pool
Activities:
1. Checking before the change :
mysql> show variables like 'innodb_buffer_pool_size';
| Variable_name | Value |
| innodb_buffer_pool_size | 134217728 |
2. Edit my.cnf file (default location at /etc)
[mysqld]
innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=2
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
3. Restart mysql via apps_control
4. Checking after change , Updated values as below:
mysql> show variables like 'innodb_buffer_pool_size';
| Variable_name | Value |
| innodb_buffer_pool_size | 2147483648 |
mysql> show variables like 'innodb_buffer_pool_instances';
| Variable_name | Value |
| innodb_buffer_pool_instances | 2 |