Memory Usage in MySQL

Jyoti_Rajai · November 12, 2024, 7:32am

There is no slow query, Ram allocated to MySQL is also low as you can see in the query, no MEMORY engine is used still the RAM consumed by MySQL is 50% of total RAM. How ? & how can we identified where does all RAM goes ?

mysql> system free -h
              total        used        free      shared  buff/cache   available
Mem:           15Gi       9.4Gi       4.0Gi        23Mi       2.3Gi       5.9Gi
Swap:         8.0Gi       123Mi       7.9Gi
mysql> select distinct engine , group_concat(distinct table_schema) from information_schema.tables group by engine ;
+--------------------+---------------------------------------------------------------------------------------------------+
| engine             | group_concat(distinct table_schema)                                                               |
+--------------------+---------------------------------------------------------------------------------------------------+
| NULL               | sys                                                                                               |
| CSV                | mysql                                                                                             |
| InnoDB             | clicrdv_authent_preprod,clicrdv_default_preprod,clicrdv_sncf_preprod,information_schema,mysql,sys |
| MEMORY             | information_schema                                                                                |
| MyISAM             | mysql                                                                                             |
| PERFORMANCE_SCHEMA | performance_schema                                                                                |
+--------------------+---------------------------------------------------------------------------------------------------+
6 rows in set (0.02 sec)

mysql>  SELECT     ( @@key_buffer_size    + @@query_cache_size    + @@innodb_buffer_pool_size    + @@innodb_log_buffer_size        + @@tmp_table_size    + @@max_heap_table_size    + @@max_connections * (        @@sort_buffer_size      + @@read_buffer_size      + @@read_rnd_buffer_size      + @@join_buffer_size      + @@thread_stack      + @@binlog_cache_size    ) ) / (1024 *
1024 * 1024) AS MAX_MEMORY_GB;
+---------------+
| MAX_MEMORY_GB |
+---------------+
|        0.3512 |
+---------------+
1 row in set, 1 warning (0.00 sec)
# Time: 2024-09-27T12:52:37.480347Z
# User@Host: prometheus_mysql_exporter[prometheus_mysql_exporter] @  [172.18.29.12]  Id: 12573
# Query_time: 145.464411  Lock_time: 0.051933 Rows_sent: 253  Rows_examined: 6238
SET timestamp=1727441557;
SELECT table_schema, table_name, column_name, auto_increment,
                  pow(2, case data_type
                    when 'tinyint'   then 7
                    when 'smallint'  then 15
                    when 'mediumint' then 23
                    when 'int'       then 31
                    when 'bigint'    then 63
                    end+(column_type like '% unsigned'))-1 as max_int
                  FROM information_schema.tables t
                  JOIN information_schema.columns c USING (table_schema,table_name)
                  WHERE c.extra = 'auto_increment' AND t.auto_increment IS NOT NULL;
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.7.42-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/r


root@slcmysql101:~#  ps -eo pid,ppid,cmd,%mem,%cpu --sort=-%mem | head
   PID   PPID CMD                         %MEM %CPU
121926      1 /usr/sbin/mysqld --daemoniz 53.7  0.5
  1054    899 s1-agent                     1.0  0.1
  1053    899 s1-scanner                   0.7  0.0
   649      1 /usr/share/filebeat/bin/fil  0.2  0.0
  1051    899 s1-network                   0.2  0.0
   322      1 /lib/systemd/systemd-journa  0.1  0.0
 34820      1 /lib/systemd/systemd --user  0.0  0.0
 34817    661 sshd: admclara [priv]        0.0  0.0
 36110    661 sshd: admclara [priv]        0.0  0.0
root@slcmysql101:~#

kedarpercona · November 12, 2024, 7:40am

Hi @Jyoti_Rajai,

What’s your bufferpool size?
What do you get here:

SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,
 sys.FORMAT_BYTES(SUM(current_alloc)) AS current_alloc,
 sys.FORMAT_BYTES(SUM(high_alloc)) AS high_alloc
 FROM sys.x$memory_global_by_current_bytes
 GROUP BY SUBSTRING_INDEX(event_name,'/',2)
 ORDER BY SUM(current_alloc) DESC;

Thanks,
K

Jyoti_Rajai · November 14, 2024, 6:46am

kedarpercona:

SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,
 sys.FORMAT_BYTES(SUM(current_alloc)) AS current_alloc,
 sys.FORMAT_BYTES(SUM(high_alloc)) AS high_alloc
 FROM sys.x$memory_global_by_current_bytes
 GROUP BY SUBSTRING_INDEX(event_name,'/',2)
 ORDER BY SUM(current_alloc) DESC;

mysql> SELECT SUBSTRING_INDEX(event_name,‘/’,2) AS code_area,
→ sys.FORMAT_BYTES(SUM(current_alloc)) AS current_alloc,
→ sys.FORMAT_BYTES(SUM(high_alloc)) AS high_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 | high_alloc |
±--------------------------±--------------±-----------+
| memory/performance_schema | 163.49 MiB | 163.49 MiB |
±--------------------------±--------------±-----------+
1 row in set (0.00 sec)

kedarpercona · November 15, 2024, 6:27am

Few more…

SELECT * FROM performance_schema.memory_summary_global_by_event_name ORDER BY total_allocated DESC;

SELECT * FROM performance_schema.memory_summary_by_thread_by_event_name;

SELECT  @@key_buffer_size/ 1024/1024  as  key_buffer_size
, @@innodb_buffer_pool_size / 1024/1024 as innodb_buffer_pool_size
, @@innodb_log_buffer_size / 1024/1024/1024 as innodb_log_buffer_size
, @@read_buffer_size / 1024/1024 as read_buffer_size
, @@read_rnd_buffer_size / 1024/1024 as read_rnd_buffer_size
, @@sort_buffer_size / 1024/1024 as sort_buffer_size
, @@join_buffer_size / 1024/1024 as join_buffer_size
, @@binlog_cache_size / 1024/1024 as binlog_cache_size
, @@thread_stack / 1024/1024 as thread_stack
, @@tmp_table_size / 1024/1024 as tmp_table_size \G

SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Connections';

show engine innodb status\G

SELECT 
    engine AS Storage_Engine,
    COUNT(*) AS Tables_Count,
    CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024), 2), 'G') AS Data_Size,
    CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2), 'G') AS Index_Size,
    CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2), 'G') AS Total_Size
FROM 
    information_schema.TABLES
WHERE 
    TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
GROUP BY engine;

mysqladmin extended-status | grep -i 'memory'

pt-summary

Jyoti_Rajai · November 15, 2024, 6:55am

mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name ORDE R BY total_allocated DESC;
ERROR 1054 (42S22): Unknown column ‘total_allocated’ in ‘order clause’

kedarpercona · November 15, 2024, 7:33am

I’d have tried to review the details on this table… let’s try with ORDER BY SUM_NUMBER_OF_BYTES_ALLOC DESC limit 20. Also review other commands.

Topic		Replies	Views
Where is my ram? Other MySQL® Questions	3	860	December 31, 2007
Query to fetch performance related details MySQL & MariaDB mysql	3	117	May 22, 2024
Mysqld process's memory usage is very high MySQL & MariaDB	1	848	November 16, 2021
How is MySQL using RAM Other MySQL® Questions	4	719	September 25, 2007
MySQL using Other MySQL® Questions	0	336	September 13, 2018

Memory Usage in MySQL

Related topics