Mysql memory usage linux

Изучаем использование памяти MySQL с помощью Performance Schema

Понимание того, как MySQL использует память, является ключом к настройке системы для достижения оптимальной производительности, также как и для устранения неполадок в случаях ненормального использования памяти, например, когда у вас есть сервер MySQL, использующий намного больше памяти, чем вы ожидаете, исходя из параметров конфигурации.

На заре MySQL понимание всех тонкостей и нюансов использования памяти было трудным и включало очень много догадок. Быть может, для выполнения некоторых запросов требуется большая временная таблица или выделение больших объемов памяти для сохраненных пользовательских переменных? Может, какие-либо хранимые процедуры занимают неожиданно большой объем памяти? Причиной чрезмерного использования памяти MySQL могло оказаться что угодно, но у вас не было легких путей понять и быть уверенным, что именно.

Все это изменилось в MySQL 5.7, который добавил инструментарий для отслеживания памяти в Performance Schema, а в MySQL 8.0 этот инструментарий стал включен по умолчанию, поэтому вы можете получить эти данные практически с любого работающего инстанса.

Если вы ищете сведения об использовании текущей памяти, схема Sys предоставляет фантастический набор сведений:

mysql> select event_name,current_alloc from sys.memory_global_by_current_bytes limit 10; +---------------------------------------------------------------+---------------+ | event_name | current_alloc | +---------------------------------------------------------------+---------------+ | memory/innodb/buf_buf_pool | 262.12 MiB | | memory/temptable/physical_ram | 64.00 MiB | | memory/performance_schema/events_statements_summary_by_digest | 39.67 MiB | | memory/sql/TABLE | 33.32 MiB | | memory/innodb/ut0link_buf | 24.00 MiB | | memory/innodb/lock0lock | 20.51 MiB | | memory/innodb/memory | 17.79 MiB | | memory/innodb/buf0dblwr | 17.08 MiB | | memory/innodb/ut0new | 16.08 MiB | | memory/performance_schema/events_statements_history_long | 13.89 MiB | +---------------------------------------------------------------+---------------+ 10 rows in set (0.01 sec)

В этом представлении отображается текущий общий объем выделенной памяти. Вы также можете копнуть глубже, просмотрев память, выделенную соединениям с разных хостов:

mysql> select host,current_allocated from memory_by_host_by_current_bytes; +-------------------------------+-------------------+ | host | current_allocated | +-------------------------------+-------------------+ | localhost | 1.19 GiB | | background | 101.28 MiB | | li1317-164.members.linode.com | 49.61 MiB | | li1319-234.members.linode.com | 27.90 MiB | | li1316-24.members.linode.com | 27.00 MiB | +-------------------------------+-------------------+ 5 rows in set (0.02 sec)

Или даже проверить выделение памяти по заданному thread_id, что может быть очень полезно для диагностики транзакций или запросов, требующих большие объемы памяти:

mysql> select thread_id,user,current_allocated from memory_by_thread_by_current_bytes limit 5; +-----------+------------------------------------+-------------------+ | thread_id | user | current_allocated | +-----------+------------------------------------+-------------------+ | 44 | innodb/srv_worker_thread | 1.99 MiB | | 48 | innodb/srv_worker_thread | 1.16 MiB | | 54322 | root@localhost | 1.10 MiB | | 46 | innodb/srv_worker_thread | 777.29 KiB | | 43881 | app1@li1317-164.members.linode.com | 274.84 KiB | +-----------+------------------------------------+-------------------+ 5 rows in set (0.43 sec)

Изучение текущей статистики не очень полезно в рамках диагностики прошлых инцидентов или даже для ответа на вопрос, нормально ли, что конкретный объект занимает столько памяти, или это уже чрезмерные показатели. Для этого лучше подходит история, доступная для отслеживания… именно для этого и предназначена Percona Monitoring and Management (PMM).

К сожалению, начиная с PMM 2.11, мы не включаем в релиз инструментарий для работы с памятью Performance Schema. Однако его довольно легко добавить с помощью пользовательских запросов (Custom Queries).

Пользовательские запросы MySQL

Пользовательские запросы — отличный функционал, который позволяет вам получать статистику с локального инстанса MySQL с помощью стандартных SQL-запросов и делает их доступными вместе с другими метриками, собираемыми экспортером MySQL. Это можно использовать для таблиц Performance Schema, таблиц Information Schema или даже запросов к вашей собственной схеме, чтобы раскрыть данные, которые вы считаете актуальными для вашего приложения. Для получения более подробной информации читайте нашу статью Пользовательские запросы в Percona Monitoring and Management.

Вы можете установить пользовательские запросы для считывания статистики использования памяти из MySQL Performance Schema следующим образом…

cd /usr/local/percona/pmm2/collectors/custom-queries/mysql/medium-resolution wget https://raw.githubusercontent.com/Percona-Lab/pmm-custom-queries/master/mysql/ps-memory-summary.yml

Во-первых, у нас есть блок, который не имеет ничего общего с информацией, которую мы получаем из Performance Schema, но дает некоторые полезные сведения для интерпретации этой информации:

Мы можем увидеть, как долго система и процесс MySQL работали, сколько памяти у системы есть и сколько доступно, всякий раз, когда происходит какая-либо агрессивная операция подкачки и т.д., и все это помогает нам узнать, когда MySQL в настоящее время использует слишком много памяти… или слишком мало. Мы можем вывести количество текущих подключений, которое является известным фактором высокого использования памяти, наряду с парой других показателей, которые могут повлиять на потребление памяти.

Сводка по использованию памяти MySQL (MySQL Memory Usage Summary) показывает, сколько памяти содержат различные объекты, а также каков общий объем измеряемой памяти, выделенной процессом MySQL.

С сотнями разных мест, где может быть выделена память, даже не самые большие потребители памяти, могут аккумулировать достаточно большие накладные расходы:

Когда память выделяется хостом и пользователем, эти представления покрывают только память, которая может быть отнесена к конкретным соединениям. Но это отличный инструмент, чтобы узнать, существуют ли хосты, пользователи или приложения, которые являются значительными потребителями памяти.

Вы также можете просмотреть сведения об использовании памяти определенными учетными записями (какой-нибудь конкретный пользователь с конкретного хоста). Это все, что касается дашбордов, поскольку конкретные идентификаторы подключения (и идентификаторы потоков), как правило, меняются так быстро, что не особенно полезны в долгосрочном захвате.

Возможно, вы также заметили, что я отключил из сводки пользователя pmm@localhost, поскольку похоже, что рабочая нагрузка этого пользователя вызывает ошибку в учете памяти, и сообщаются нереальные цифры (это также то, что вам следует иметь в виду, чтобы не поплатиться за слепое доверие данным, которые вы видите).

Наконец, вы можете посмотреть распределение памяти по различным подсистемам, что может быть весьма полезно для расширенного анализа, когда только глобальные топ потребители не предоставляют достаточно информации.

А что думаете вы ? Это было бы для вас полезно, или вы хотели бы увидеть больше дополнительной визуализации, прежде чем мы рассмотрим возможность ее включения в Percona Monitoring and Management?

Источник

How do I tell what is using most memory in MySQL

My MySQL server is running out of physical memory and is beginning to use swap. How do I tell what is using the most memory? Ideally, it would be a specific table or group of indices and I could optimize just a small number of items. My DB is over 100GB and my current machine has 15GB memory. Thanks.

If you’re using InnoDB storage engine, check your innodb_buffer_pool_size variable. I’ll go out on a limb and assume you have assigned most of your memory for innodb_buffer_pool and the remainder of the processes are struggling to get some ram to work.

2 Answers 2

MySQL’s maximum memory usage very much depends on hardware, your settings and the database itself.

Hardware

The hardware is the obvious part. The more RAM the merrier, faster disks ftw. Don’t believe those monthly or weekly news letters though. MySQL doesn’t scale linear — not even on Oracle hardware. It’s a little trickier than that.

The bottom line is: there is no general rule of thumb for what is recommend for your MySQL setup. It all depends on the current usage or the projections.

Settings & database

Engines

Table engines are a pretty important decision, many people forget about those early on and then suddenly find themselves fighting with a 30 GB sized MyISAM table which locks up and blocks their entire application.

I don’t mean to say MyISAM sucks, but InnoDB can be tweaked to respond almost or nearly as fast as MyISAM and offers such thing as row-locking on UPDATE whereas MyISAM locks the entire table when it is written to.

If you’re at liberty to run MySQL on your own infrastructure, you might also want to check out the percona server because among including a lot of contributions from companies like Facebook and Google (they know fast), it also includes Percona’s own drop-in replacement for InnoDB , called XtraDB .

See my gist for percona-server (and -client) setup (on Ubuntu): http://gist.github.com/637669

Size

Database size is very, very important — believe it or not, most people on the Intarwebs have never handled a large and write intense MySQL setup but those do really exist. Some people will troll and say something like, «Use PostgreSQL. 111», but let’s ignore them for now.

The bottom line is: judging from the size, decision about the hardware are to be made. You can’t really make a 80 GB database run fast on 1 GB of RAM.

Indices

It’s not: the more, the merrier. Only indices needed are to be set and usage has to be checked with EXPLAIN . Add to that that MySQL’s EXPLAIN is really limited, but it’s a start.

Suggested configurations

About these my-large.cnf and my-medium.cnf files — I don’t even know who those were written for. Roll your own.

Tuning primer

A great start is the tuning primer. It’s a bash script (hint: you’ll need linux) which takes the output of SHOW VARIABLES and SHOW STATUS and wraps it into hopefully useful recommendation. If your server has ran some time, the recommendation will be better since there will be data to base them on.

The tuning primer is not a magic sauce though. You should still read up on all the variables it suggests to change.

Reading

I really like to recommend the mysqlperformanceblog. It’s a great resource for all kinds of MySQL-related tips. And it’s not just MySQL, they also know a lot about the right hardware or recommend setups for AWS, etc.. These guys have years and years of experience.

Another great resource is planet-mysql, of course.

This answer is referenced from a similar question on stackoverflow regarding Mysql Memory Usage: MySQL maximum memory usage

Источник