Percona 5.6 high memory usage

Question

I'm not sure if stack is the right place to ask this, but I recently upgraded to Percona 5.6 from 5.5 and my memory usage has skyrocketed!

This is from PS:

mysql     4598  0.0 29.5 1583356 465312 ?      Sl   Oct17   9:07 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib6

I'm on a dedicated VSS

My server only has a gig of ram...how is this only memory usage only 30% according to PS?

I have my ram set in the config to be less than this, and when I run MySQLTuner i get:

[OK] Maximum possible memory usage: 338.9M (22% of installed RAM)

So how am I using almost 500MB of physical memory and over a gig and a half of virtual?

Is this a bug in mySQL or something with my server?

Haggerty

using Pmap I found the culprits, there are 2 huge ANON memory allocations happening, and I don't know why 00007fb73b65c000 403088K rw--- [ anon ] 00007fb731c2a000 155748K rw--- [ anon ] anyway to figure out WHY this memory is being allocated? – Haggerty

Answer 1

found out that in mysql 5.6 performance_schema is on by default. It was disabled by default prior in 5.5 and before. It has been enabled by default since 5.6.6

performance_schema=off to my config file fixes the issue.

I'd imagine anyone who doesn't have the memory to be running performance_schema would not be using it anyway.

This might affect other distros of mysql 5.6.6 as well.

Haggerty

This is specific to MySQL 5.6. See here for MySQL 5.7 and beyond: mysqlserverteam.com/… – Adiathermancy

Answer 2

I had this problem and fixing a few (increased) cache values in MySQL.ini sorted the problem out.

table_definition_cache - set to 400

From "http://bugs.mysql.com/bug.php?id=68287" where this is discussed

Yes, there are thresholds based on table_open_cache and table_definition_cache and max_connections and crossing the thresholds produces a big increase in RAM used. The thresholds work by first deciding if the server size is small, medium or large.

Small: all three are same as or less than defaults (2000, 400, 151). Large: any of the three is more than twice the default. Medium: others.

From memory. mine was set to 2000+ and dropping it sorted the problem.

Blown

Answer 3

What helped me was on CentOS was changing the memory allocator:

yum install jemalloc-devel

and add to my.cnf:

[mysqld_safe]

malloc-lib = /usr/lib64/libjemalloc.so.1

Theomancy