# MySQL Eats Memory **URL:** \ **Category:** Reporting an Issue\ **Created:** [April 17, 2023, 9:45am UTC](https://forums.vitalpbx.org/t/mysql-eats-memory/2652 "2023-04-17T09:45:10Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![PitzKey](https://forums.vitalpbx.org/user_avatar/forums.vitalpbx.org/pitzkey/32/420_2.png) [@PitzKey](https://forums.vitalpbx.org/u/PitzKey)\ **Post date:** [April 17, 2023, 9:45am UTC](https://forums.vitalpbx.org/t/mysql-eats-memory/2652/1 "2023-04-17T09:45:10Z")
VitalPBX 3.2.3-9 It seems like MySQL is eating most of the ram. ![image](https://forums.vitalpbx.org/uploads/default/original/2X/e/e986a9bb870ae850c7fb901321a8c21bf05df931.png) ---
**Author:** ![miguel](https://forums.vitalpbx.org/user_avatar/forums.vitalpbx.org/miguel/32/415_2.png) [@miguel](https://forums.vitalpbx.org/u/miguel)\ **Post date:** [April 17, 2023, 12:21pm UTC](https://forums.vitalpbx.org/t/mysql-eats-memory/2652/2 "2023-04-17T12:21:12Z")
Try updating to the latest available version. It could be a Cron of a service executing multiple requests. ---
**Author:** ![PitzKey](https://forums.vitalpbx.org/user_avatar/forums.vitalpbx.org/pitzkey/32/420_2.png) [@PitzKey](https://forums.vitalpbx.org/u/PitzKey)\ **Post date:** [April 17, 2023, 12:29pm UTC](https://forums.vitalpbx.org/t/mysql-eats-memory/2652/3 "2023-04-17T12:29:17Z")
I’ll try and report back. ---
**Author:** ![PitzKey](https://forums.vitalpbx.org/user_avatar/forums.vitalpbx.org/pitzkey/32/420_2.png) [@PitzKey](https://forums.vitalpbx.org/u/PitzKey)\ **Post date:** [April 19, 2023, 12:15pm UTC](https://forums.vitalpbx.org/t/mysql-eats-memory/2652/4 "2023-04-19T12:15:49Z")
Updated. It still has this issue. It seems like it happens post maintenance. ---
**Author:** ![ontral](https://forums.vitalpbx.org/letter_avatar_proxy/v4/letter/o/d6d6ee/32.png) [@ontral](https://forums.vitalpbx.org/u/ontral)\ **Post date:** [April 28, 2023, 5:31pm UTC](https://forums.vitalpbx.org/t/mysql-eats-memory/2652/5 "2023-04-28T17:31:21Z")
The question is… why do You have so many MySQL processes running. can you log in to the MySQL server via the command line and run a “show processlist;” query it should look similar to this: ![image](https://forums.vitalpbx.org/uploads/default/original/2X/8/83fbd9dbcbe79f3d3c9425774904e20a0c29805c.png) Seeing the running queries will help you to understand what is being processed and who/what is executing it and for how long. ---
**Author:** ![PitzKey](https://forums.vitalpbx.org/user_avatar/forums.vitalpbx.org/pitzkey/32/420_2.png) [@PitzKey](https://forums.vitalpbx.org/u/PitzKey)\ **Post date:** [April 30, 2023, 1:23pm UTC](https://forums.vitalpbx.org/t/mysql-eats-memory/2652/6 "2023-04-30T13:23:12Z")
Here’s the list of all processlists ```auto MariaDB [(none)]> show processlist; +--------+-------------+-----------------+--------------+---------+------+--------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+-------------+-----------------+--------------+---------+------+--------------------------+------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 502414 | vitalpbx | localhost | ombutel | Sleep | 6 | | NULL | 0.000 | | 502426 | asterisk | localhost | asterisk | Sleep | 2 | | NULL | 0.000 | | 502427 | vitalpbx | localhost | ombutel | Sleep | 10 | | NULL | 0.000 | | 502428 | vitalpbx | localhost | ombutel | Sleep | 6 | | NULL | 0.000 | | 502429 | vitalpbx | localhost | ombutel | Sleep | 6 | | NULL | 0.000 | | 502434 | telerec | localhost | telerec | Sleep | 39 | | NULL | 0.000 | | 502436 | vitalpbx | localhost | ombutel | Sleep | 10 | | NULL | 0.000 | | 502446 | stats | localhost:40202 | sonata_stats | Sleep | 14 | | NULL | 0.000 | | 502447 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 | | 502452 | ombutel | localhost | ombutel | Sleep | 0 | | NULL | 0.000 | +--------+-------------+-----------------+--------------+---------+------+--------------------------+------------------+----------+ 15 rows in set (0.006 sec) ``` Also posting a screenshot in case the formatting is getting messed up: ![image](https://forums.vitalpbx.org/uploads/default/original/2X/0/023f53f7d4c9d73bfdd3a5db44ae57928bec048e.png) cc @miguel is these process list normal? ---
**Author:** ![PitzKey](https://forums.vitalpbx.org/user_avatar/forums.vitalpbx.org/pitzkey/32/420_2.png) [@PitzKey](https://forums.vitalpbx.org/u/PitzKey)\ **Post date:** [May 3, 2023, 6:41pm UTC](https://forums.vitalpbx.org/t/mysql-eats-memory/2652/7 "2023-05-03T18:41:57Z")
Following up on this ---
**Author:** ![ontral](https://forums.vitalpbx.org/letter_avatar_proxy/v4/letter/o/d6d6ee/32.png) [@ontral](https://forums.vitalpbx.org/u/ontral)\ **Post date:** [May 6, 2023, 3:34pm UTC](https://forums.vitalpbx.org/t/mysql-eats-memory/2652/8 "2023-05-06T15:34:23Z")
Hi PitzKey, sorry for delay The list of processes looks normal. There are extra connections open to the database by vitalpbx user but none of those “commands” would eat up memory or CPU. During heavy usage you would see queries getting executed in that list. What it tells me is that server memory is not consumed by the SQL Queries. The database was nearly idling at the time of those screenshots. Questions: Are you still having same memory issues? How long have you been using this system? Any recent configuration changes, updates or crashes? Can you run this query?: SHOW GLOBAL STATUS LIKE ‘open%’; ---
**Author:** ![PitzKey](https://forums.vitalpbx.org/user_avatar/forums.vitalpbx.org/pitzkey/32/420_2.png) [@PitzKey](https://forums.vitalpbx.org/u/PitzKey)\ **Post date:** [May 16, 2023, 7:11am UTC](https://forums.vitalpbx.org/t/mysql-eats-memory/2652/9 "2023-05-16T07:11:25Z")
> [@ontral](#): > > Can you run this query?: SHOW GLOBAL STATUS LIKE ‘open%’; ```auto MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'open%'; +--------------------------+---------+ | Variable_name | Value | +--------------------------+---------+ | Open_files | 28 | | Open_streams | 0 | | Open_table_definitions | 318 | | Open_tables | 622 | | Opened_files | 7171644 | | Opened_plugin_libraries | 0 | | Opened_table_definitions | 317 | | Opened_tables | 628 | | Opened_views | 0 | +--------------------------+---------+ 9 rows in set (0.024 sec) ``` > [@ontral](#): > > What it tells me is that server memory is not consumed by the SQL Queries. See screenshot above. **It is** consumed by MySQL. As mentioned, I suspect that this is related to the maintenance scripts that were created by VitalPBX. ---
**Author:** ![ontral](https://forums.vitalpbx.org/letter_avatar_proxy/v4/letter/o/d6d6ee/32.png) [@ontral](https://forums.vitalpbx.org/u/ontral)\ **Post date:** [May 16, 2023, 3:34pm UTC](https://forums.vitalpbx.org/t/mysql-eats-memory/2652/10 "2023-05-16T15:34:08Z")
Its just a piece of the puzzle but based on the info it seems that there is allot of Open\_tables and Opened\_tables which suggests that table\_open\_cache value might be too small. MySQL opens tables and keeps them open because continually opening and closing tables is computationally expensive. “table\_open\_cache” is the setting that defines how many tables the server can keep open at once. If you’re seeing that “Opened\_tables” is consistently increasing, it could mean your “table\_open\_cache” is too small and MySQL is continually opening and closing tables because it can’t keep all the needed ones open. This constant opening and closing is computationally expensive and could indirectly increase memory usage. Check the current value of table\_open\_cache with " SHOW VARIABLES LIKE ‘table\_open\_cache’; " . If the number of “Open\_tables” is close to the “table\_open\_cache” then you may want to increase “table\_open\_cache” Remember to make a copy of your original MySQL mysql.cnf config file before making changes to it. Make a backup of your database before restarting the mysql service There are other variables that can help to understand what can possibly cause high memory usage: innodb\_buffer\_pool\_size query\_cache\_size thread\_cache\_size sort\_buffer\_size join\_buffer\_size eg: SHOW VARIABLES LIKE ‘innodb\_buffer\_pool\_size’; ---
**Author:** ![services\_cdev](https://forums.vitalpbx.org/letter_avatar_proxy/v4/letter/s/ba9def/32.png) [@services\_cdev](https://forums.vitalpbx.org/u/services_cdev)\ **Post date:** [July 17, 2023, 7:29pm UTC](https://forums.vitalpbx.org/t/mysql-eats-memory/2652/11 "2023-07-17T19:29:04Z")
Update: This also shows on VitalPBX 4, perhaps @PitzKey or @miguel would chip in, I found the following: ![image](https://forums.vitalpbx.org/uploads/default/original/2X/9/9dc65affcf0615ba73306821934d59d88873c978.jpeg) Mariadb usage has been steadily increasing for the last 30 days. checking swappiness with: sysctl -a |grep swap Returns a value of vm.swappiness = 60 Which is apparently a default setting and should be set to 1 (or 10 max) instead of 60 as per best practices, on the other hand, the following file: /etc/mysql/conf.d/vitalpbx.cnf is set to have innodb\_buffer\_pool\_size=50G, meaning that will do 50G pool, combined with the vm.swappiness of 60 will cause the db to grow as large and dump it on swap space. Are these intended to be set like that? Will I break anything by setting vm.swappiness = 10? What happens when swap usage hits 100%? regards, ---
**Author:** ![services\_cdev](https://forums.vitalpbx.org/letter_avatar_proxy/v4/letter/s/ba9def/32.png) [@services\_cdev](https://forums.vitalpbx.org/u/services_cdev)\ **Post date:** [July 18, 2023, 7:06pm UTC](https://forums.vitalpbx.org/t/mysql-eats-memory/2652/12 "2023-07-18T19:06:40Z")
Hi again, update: Ran mariadbfix from the HA suite of tools and while that did a redo of the mariadb replication and recreated all dbs in slave, it also updated cdr and queues\_log tables, these tables are being updated rather frequently now (these had stopped being updated sometime about a month ago). Also updated vm.swappiness = 10, system appears to be working correctly. Would it be of use if someone performed the vm.swappiness = 10 on a standalone VitalPBX 4 and mysql restart to test if results match the ones described above? System status is now as follows: ![image](https://forums.vitalpbx.org/uploads/default/original/2X/2/232b5d127ec8619d2e947154ef465aad26fd5a02.png) ---
**Author:** ![miguel](https://forums.vitalpbx.org/user_avatar/forums.vitalpbx.org/miguel/32/415_2.png) [@miguel](https://forums.vitalpbx.org/u/miguel)\ **Post date:** [July 19, 2023, 3:03am UTC](https://forums.vitalpbx.org/t/mysql-eats-memory/2652/13 "2023-07-19T03:03:07Z")
> [@services\_cdev](#): > > vm.swappiness Here’s all the information about it: > **[Configuring Swappiness](https://mariadb.com/kb/en/configuring-swappiness/)** > > Setting Linux swappiness.