MySQL Memory Foot print.

MySQL & MariaDB Other MySQL® Questions
1

Hello all,

We are adding MySQL as a database offering here where I work. We currently support both DB2 and Oracle. One of the questions I am hearing allot is how do we see the overall memory foot print of our running MySQL servers?

Any advice or pointer would be great.

Thank you in advanced for your time.

Wayne

2

There are several nice documents covering this problem, for example:

http://www.mysqlperformanceblog.com/2012/03/21/troubleshooti ng-mysql-memory-usage/
http://www.mysqlperformanceblog.com/2006/05/30/innodb-memory -usage/
[URL]http://dev.mysql.com/doc/refman/5.5/en/memory-use.html[/URL]

In short: a lot depends on your schemas, mysql server settings, number of tables, workload, MySQL version used, etc.

Worth mentioning here that Percona version of MySQL offers you an extended view into memory usage details.
By doing

SHOW ENGINE INNODB STATUS\G

Percona Server with XtraDB’s memory related output is e.g.:

----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 137887744; in additional pool allocated 0Internal hash tables (constant factor + variable factor) Adaptive hash index 2217584 (2213368 + 4216) Page hash 139112 (buffer pool 0 only) Dictionary cache 592524 (554768 + 37756) File system 83552 (82672 + 880) Lock system 333248 (332872 + 376) Recovery system 0 (0 + 0)Dictionary memory allocated 37756Buffer pool size 8191Buffer pool size, bytes 134201344Free buffers 8048Database pages 143Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 143, created 0, written 05.30 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 689 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 143, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]

And for MySQL Community Version:

----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 137363456; in additional pool allocated 0Dictionary memory allocated 33650Buffer pool size 8192Free buffers 8050Database pages 142Old database pages 0Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 142, created 0, written 06.76 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 687 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 142, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]

(both are 5.5.27)

Also, it’s always recommended to have both alerts and trend graphs on memory usage for your servers, and for example Percona Monitoring Plugins offer a lot regarding MySQL specifics in that subject.