# mysql memory consumption / crashes **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [March 13, 2009, 9:34am UTC](https://forums.percona.com/t/mysql-memory-consumption-crashes/1111 "2009-03-13T09:34:00Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![stephan2807](https://avatars.discourse-cdn.com/v4/letter/s/a5b964/32.png) [@stephan2807](https://forums.percona.com/u/stephan2807)\ **Post date:** [March 13, 2009, 9:34am UTC](https://forums.percona.com/t/mysql-memory-consumption-crashes/1111/1 "2009-03-13T09:34:00Z")
Hello, I have quite an annoying problem: after about one week running mysql-server (5.0.44) is terminated because of excessive memory usage (around 2.8GB, 32-bit system). I have allready reduced the memory settings several times, but the problem still persists. Mysql-tunig-primer calculates a max memory allocated of 447M and a configured limit of 1017M. top tells me the process uses 2239M at the moment, so time for a manual restart… Any ideas how to solve the issue?? other applications involved - mainly InnoDB-Tables, 2 in memory tables - apache/php (running on same server) - mysqldump - replication to a standby-server CONFIG (memory-related parts) key\_buffer = 8M max\_allowed\_packet = 32M table\_cache = 1350 sort\_buffer\_size = 2M net\_buffer\_length = 8K read\_buffer\_size = 256K read\_rnd\_buffer\_size = 512K myisam\_sort\_buffer\_size = 1M max\_connections = 150 long\_query\_time = 4 thread\_cache\_size = 20 join\_buffer\_size = 2M max\_heap\_table\_size = 16M tmp\_table\_size = 4M open\_files\_limit = 4000 max\_binlog\_cache\_size = 1073741824 innodb\_buffer\_pool\_size = 256M innodb\_additional\_mem\_pool\_size = 2M innodb\_log\_file\_size = 50M innodb\_log\_buffer\_size = 8M ERROR MESSAGE (Low Mem) 090306 17:54:53 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1094604 bytes) 090306 17:54:53 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use ‘ulimit’ to allow mysqld to use more memory or you can add more swap space ERROR MESSAGE (Crash) 081221 19:10:15 InnoDB: Error: cannot allocate 1064960 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 317687476 bytes. Operating system errno: 12 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: Note that in most 32-bit computers the process InnoDB: memory space is limited to 2 GB or 4 GB. InnoDB: We keep retrying the allocation for 60 seconds… 081221 19:40:02 InnoDB: Error: cannot allocate 1064960 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 317683032 bytes. Operating system errno: 12 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: Note that in most 32-bit computers the process InnoDB: memory space is limited to 2 GB or 4 GB. InnoDB: We keep retrying the allocation for 60 seconds… 081221 19:41:02 InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. 081221 19:41:02 - mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key\_buffer\_size=8388608 read\_buffer\_size=258048 max\_used\_connections=41 max\_connections=150 threads\_connected=4 It is possible that mysqld could use up to key\_buffer\_size + (read\_buffer\_size + sort\_buffer\_size)\*max\_connections = 353190 K bytes of memory Hope that’s ok; if not, decrease some variables in the equation. ---
**Author:** ![MarkRose](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@MarkRose](https://forums.percona.com/u/MarkRose)\ **Post date:** [March 13, 2009, 3:51pm UTC](https://forums.percona.com/t/mysql-memory-consumption-crashes/1111/2 "2009-03-13T15:51:27Z")
Have you seen this post? [http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-](http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-) memory-usage/ Just a hunch, it might be your max\_allowed\_packet and your max\_connections settings. 150 x 32MB = 4.6875 GB. As large packets get sent to each connection, it’s possible they allocate more memory to accomodate the large packet and never release it. Try setting your max packet to something smaller. Also look at reducing your number of connections. If you don’t need 150, reduce the number. For instance, on my busy forums (1M PHP hits daily), I have it limited to 48. I have 32 php-fastcgi processes, each which keeps a persistent connection to MySQL. I keep a few more around for things Debian maintenance scripts and running mytop or logging in myself. ---
**Author:** ![stephan2807](https://avatars.discourse-cdn.com/v4/letter/s/a5b964/32.png) [@stephan2807](https://forums.percona.com/u/stephan2807)\ **Post date:** [March 13, 2009, 5:04pm UTC](https://forums.percona.com/t/mysql-memory-consumption-crashes/1111/3 "2009-03-13T17:04:05Z")
Thanks for your hint regarding max\_allowed\_packet, haven’t considered this. I will check it out next week (don’t want to change during the weekend). There were in fact changes to blobs during the last year when the crashes started. Well, the number of blobs - esp. with large size - highliy decreased, but sometimes changes have strange effects. Possible that the frequent use of larger blobs helped to free unused memory… Further lowering the number of connections would be quite hard. The crash reports usually showed 110-401 max connections (the setting was 400 before my first changes). The report above was quite extraordinary regarding this fact. BTW: The server has 8GB of physical memory. Would have been better to set it up with a 64-bit-install, but I worried additional troubles. atm there is no way to change this. ---
**Author:** ![stephan2807](https://avatars.discourse-cdn.com/v4/letter/s/a5b964/32.png) [@stephan2807](https://forums.percona.com/u/stephan2807)\ **Post date:** [March 20, 2009, 2:23pm UTC](https://forums.percona.com/t/mysql-memory-consumption-crashes/1111/4 "2009-03-20T14:23:47Z")
No luck. max\_allowed\_packet\_size does not seem to change anything. I lowered the value from 32 to 4 mb and restarted on 16.3., 19:00. The rise seems to quite the same as before. I had calculated a max of 1617MB (1017MB + 150\*4MB) for the new setting, after 4 days i allready see 2000MB. The rise is usually slower during the weekend, but I expect the server to crash on monday or tuesday. Any other ideas/suggestions? I welcome any hint… Best regards, Stephan memory usage was as following: virt. res. 17.3. 10:30 585m 497m 17.3. 14:39 677m 588m 18.3. 09:43 970m 878m 18.3. 12:58 1044m 953m 18.3. 23:55 1280m 1.2g 19.3. 08:55 1343m 1.2g 19.3. 17:07 1540m 1.4g 19.3. 20:53 1621m 1.5g 20.3. 11:02 1778m 1.6g 20.3. 20:30 2085m 1.9g Max. connections used was 30 during the 4 days. ---
**Author:** ![stephan2807](https://avatars.discourse-cdn.com/v4/letter/s/a5b964/32.png) [@stephan2807](https://forums.percona.com/u/stephan2807)\ **Post date:** [March 30, 2009, 4:45pm UTC](https://forums.percona.com/t/mysql-memory-consumption-crashes/1111/5 "2009-03-30T16:45:55Z")
Looks like I have a lead. After the last set of changes the server seems to be jailed within 1.1-1.2g (virtual memory). Thanks Mark. Here is a short summary of the last changes: The lowered max\_heap\_table\_size does not look to have any relevance. I restarted after 4 days at 2.1g/1.9g (virt./res.) on 25.3. About the same rise as before and even before the changes in march (since I opened the topic). Those were the changes for the last restart (25.3. 21:00) with much better mem usage: - max\_connections 150 \> 100 - table\_cache 1350 \> 400 - open\_files\_limit 4000 \> 1200 - thread\_cache\_size 20 \> 10 I will try to isolate the responsible setting and report back. Will try to check performance impact as well, but this has only minor relevance. I also managed to nail down the set of changes that caused the issues. Well hidden in times of low usage and config changes so the problems appeared quite a bit later. Anyway, there is no way to revert. ---
**Author:** ![shane](https://avatars.discourse-cdn.com/v4/letter/s/dbc845/32.png) [@shane](https://forums.percona.com/u/shane)\ **Post date:** [June 2, 2009, 2:25am UTC](https://forums.percona.com/t/mysql-memory-consumption-crashes/1111/6 "2009-06-02T02:25:25Z")
if you have many tables with blobs and those blobs are large, then table\_cache can consume more memory than you think… see [URL][MySQL Bugs: #38002: table\_cache consumes too much memory with blobs](http://bugs.mysql.com/bug.php?id=38002%5B/URL%5D) for an example of this.