# MYSQL Swapping Issue / Memory Usage **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [September 28, 2007, 11:26pm UTC](https://forums.percona.com/t/mysql-swapping-issue-memory-usage/471 "2007-09-28T23:26:07Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![joeo](https://avatars.discourse-cdn.com/v4/letter/j/9fc29f/32.png) [@joeo](https://forums.percona.com/u/joeo)\ **Post date:** [September 28, 2007, 11:26pm UTC](https://forums.percona.com/t/mysql-swapping-issue-memory-usage/471/1 "2007-09-28T23:26:07Z")
I’m running MYSQL on a Linux box using basically the my-medium.cnf file. The machine has 1GB of RAM. After MYSQL has been running for a day or so, it starts to use very much memory. Here is top: top - 00:52:44 up 67 days, 57 min, 3 users, load average: 0.07, 1.19, 2.39 Tasks: 95 total, 1 running, 94 sleeping, 0 stopped, 0 zombie Cpu(s): 0.0% us, 0.0% sy, 0.0% ni, 99.7% id, 0.3% wa, 0.0% hi, 0.0% si Mem: 1034084k total, 666440k used, 367644k free, 29476k buffers Swap: 2096472k total, 463748k used, 1632724k free, 64872k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 24782 mysql 15 0 750m 401m 2476 S 0.0 39.7 37:53.80 mysqld 24820 joeo 24 0 600m 33m 1744 S 0.0 3.3 1:00.73 java 24558 root 16 0 8624 2688 1084 S 0.0 0.3 0:59.94 ddclient 30880 nobody 15 0 9920 1832 1272 S 0.0 0.2 0:00.87 httpd 30441 nobody 15 0 10072 1820 1256 S 0.0 0.2 0:01.09 httpd 24082 nobody 15 0 9920 1808 1256 S 0.0 0.2 0:01.06 httpd 30882 nobody 15 0 9832 1784 1228 S 0.0 0.2 0:01.00 httpd Earlier today, it was using 1.6GB of VIRT space. Sometimes, during usage, the machine will start to swap so much, that is is un-usable. Usually after a long period of swapping the memory drops back down to around 750MB as it is now. Any ideas? ## Here is my /etc/my.cnf file: [mysqld] datadir=/usr/mysql\_data port = 3306 socket = /tmp/mysql.sock skip-locking key\_buffer = 16M max\_allowed\_packet = 1M table\_cache = 64 sort\_buffer\_size = 512K net\_buffer\_length = 8K read\_buffer\_size = 256K read\_rnd\_buffer\_size = 512K myisam\_sort\_buffer\_size = 8M thread\_cache\_size = 8 thread\_concurrency = 2 query\_cache\_size = 8M [mysqldump] quick max\_allowed\_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key\_buffer = 20M sort\_buffer\_size = 20M read\_buffer = 2M write\_buffer = 2M [myisamchk] key\_buffer = 20M sort\_buffer\_size = 20M read\_buffer = 2M write\_buffer = 2M ## [mysqlhotcopy] interactive-timeout Thanks for any ideas! -Joe [http://www.lovehorsepower.com](http://www.lovehorsepower.com) ---
**Author:** ![joeo](https://avatars.discourse-cdn.com/v4/letter/j/9fc29f/32.png) [@joeo](https://forums.percona.com/u/joeo)\ **Post date:** [September 29, 2007, 8:29am UTC](https://forums.percona.com/t/mysql-swapping-issue-memory-usage/471/2 "2007-09-29T08:29:04Z")
The memory usage is back up now: 24782 mysql 15 0 1343m 389m 2288 S 0.0 38.5 54:25.98 mysqld According to my-medium.cnf: # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) Any ideas why the usage is so high? Thanks! -Joe [http://www.lovehorsepower.com](http://www.lovehorsepower.com) ---
**Author:** ![mikec](https://avatars.discourse-cdn.com/v4/letter/m/977dab/32.png) [@mikec](https://forums.percona.com/u/mikec)\ **Post date:** [September 30, 2007, 11:49pm UTC](https://forums.percona.com/t/mysql-swapping-issue-memory-usage/471/3 "2007-09-30T23:49:59Z")
Depending on how many connections you have… If you are using MyISAM tables…and doing selects requiring sorts (as in group by, order by, etc)… The myisam\_sort\_buffer\_size=8M for 100 connections that would be 800M. Try dropping this down to 1M and see if good performance doesn’t last longer. Also running MySQL by itself on its own server would be a better idea. ---
**Author:** ![joeo](https://avatars.discourse-cdn.com/v4/letter/j/9fc29f/32.png) [@joeo](https://forums.percona.com/u/joeo)\ **Post date:** [October 1, 2007, 8:23am UTC](https://forums.percona.com/t/mysql-swapping-issue-memory-usage/471/4 "2007-10-01T08:23:01Z")
Currently there are only about 3 connections as this is in development. There is a process running that is performing many inserts, but only through one connection. I will try this anyway and see what happens. Thanks! -JoeO ---
**Author:** ![mikec](https://avatars.discourse-cdn.com/v4/letter/m/977dab/32.png) [@mikec](https://forums.percona.com/u/mikec)\ **Post date:** [October 1, 2007, 10:06am UTC](https://forums.percona.com/t/mysql-swapping-issue-memory-usage/471/5 "2007-10-01T10:06:00Z")
Some ideas to try… When the problem occurs do show processlist there should be only a few processes running in MySQL or are there a lot of processes? How about when the problem isn’t occurring? Reboot MySQL and do a show status. Then do a show status when the problem is occurring and compare them. What is the code that is accessing MySQL? Maybe you have a memory leak. ---
**Author:** ![joeo](https://avatars.discourse-cdn.com/v4/letter/j/9fc29f/32.png) [@joeo](https://forums.percona.com/u/joeo)\ **Post date:** [October 1, 2007, 2:37pm UTC](https://forums.percona.com/t/mysql-swapping-issue-memory-usage/471/6 "2007-10-01T14:37:07Z")
Good idea. Here are the results after running for a long while. MySQLd is currently using 350M of RAM, and 1004M of VIRT space. top: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 24782 mysql 15 0 1004m 358m 2156 S 1.7 35.5 90:50.05 mysqld * * * show processlist Id User Host db Command Time State Info * * * 3454 joeo 192.168.0.2:4862 zanydb Sleep 21777 (null) 3455 joeo 192.168.0.2:4863 zanydb Query 0 (null) show processlist 3477 joeo cybrina.mine.nu:10042 zanydb Sleep 0 (null) 3705 joeo cybrina.mine.nu:24794 zanydb Sleep 38 (null) * * * show table status Name Engine Version Row\_format Rows Avg\_row\_length Data\_length Max\_data\_length Index\_length Data\_free Auto\_increment Create\_time Update\_time Check\_time Collation Checksum Create\_options Comment * * * CONCEPTS MyISAM 10 Dynamic 0 0 0 281474976710655 1024 0 (null) 8/31/2007 12:17:52 AM 8/31/2007 12:17:52 AM 8/31/2007 1:23:01 AM latin1\_swedish\_ci (null) ENTITY MyISAM 10 Dynamic 1304297 79 103582900 281474976710655 31250432 0 (null) 8/31/2007 12:17:56 AM 10/1/2007 4:03:27 PM 8/31/2007 12:17:56 AM latin1\_swedish\_ci (null) LINKS MyISAM 10 Dynamic 2250683 225 507099016 281474976710655 339712000 0 (null) 9/13/2007 1:08:30 AM 10/1/2007 4:03:27 PM 9/13/2007 1:10:39 AM latin1\_swedish\_ci (null) TOKEN MyISAM 10 Dynamic 11986918 29 349799444 281474976710655 266305536 0 (null) 8/31/2007 12:18:01 AM 10/1/2007 4:03:27 PM 8/31/2007 12:18:01 AM latin1\_swedish\_ci (null) * * * show status Variable\_name Value * * * Aborted\_clients 7398 Aborted\_connects 3 Binlog\_cache\_disk\_use 0 Binlog\_cache\_use 0 Bytes\_received 1125 Bytes\_sent 19158 Com\_admin\_commands 0 Com\_alter\_db 0 Com\_alter\_table 0 Com\_analyze 0 Com\_backup\_table 0 Com\_begin 0 Com\_change\_db 1 Com\_change\_master 0 Com\_check 0 Com\_checksum 0 Com\_commit 0 Com\_create\_db 0 Com\_create\_function 0 Com\_create\_index 0 Com\_create\_table 0 Com\_dealloc\_sql 0 Com\_delete 0 Com\_delete\_multi 0 Com\_do 0 Com\_drop\_db 0 Com\_drop\_function 0 Com\_drop\_index 0 Com\_drop\_table 0 Com\_drop\_user 0 Com\_execute\_sql 0 Com\_flush 0 Com\_grant 0 Com\_ha\_close 0 Com\_ha\_open 0 Com\_ha\_read 0 Com\_help 0 Com\_insert 0 Com\_insert\_select 0 Com\_kill 0 Com\_load 0 Com\_load\_master\_data 0 Com\_load\_master\_table 0 Com\_lock\_tables 0 Com\_optimize 0 Com\_preload\_keys 0 Com\_prepare\_sql 0 Com\_purge 0 Com\_purge\_before\_date 0 Com\_rename\_table 0 Com\_repair 0 Com\_replace 0 Com\_replace\_select 0 Com\_reset 0 Com\_restore\_table 0 Com\_revoke 0 Com\_revoke\_all 0 Com\_rollback 0 Com\_savepoint 0 Com\_select 14 Com\_set\_option 4 Com\_show\_binlog\_events 0 Com\_show\_binlogs 0 Com\_show\_charsets 1 Com\_show\_collations 1 Com\_show\_column\_types 0 Com\_show\_create\_db 0 Com\_show\_create\_table 0 Com\_show\_databases 1 Com\_show\_errors 0 Com\_show\_fields 0 Com\_show\_grants 0 Com\_show\_innodb\_status 0 Com\_show\_keys 0 Com\_show\_logs 0 Com\_show\_master\_status 0 Com\_show\_ndb\_status 0 Com\_show\_new\_master 0 Com\_show\_open\_tables 0 Com\_show\_privileges 0 Com\_show\_processlist 4 Com\_show\_slave\_hosts 0 Com\_show\_slave\_status 0 Com\_show\_status 1 Com\_show\_storage\_engines 0 Com\_show\_tables 0 Com\_show\_triggers 0 Com\_show\_variables 1 Com\_show\_warnings 11 Com\_slave\_start 0 Com\_slave\_stop 0 Com\_stmt\_close 0 Com\_stmt\_execute 0 Com\_stmt\_fetch 0 Com\_stmt\_prepare 0 Com\_stmt\_reset 0 Com\_stmt\_send\_long\_data 0 Com\_truncate 0 Com\_unlock\_tables 0 Com\_update 0 Com\_update\_multi 0 Com\_xa\_commit 0 Com\_xa\_end 0 Com\_xa\_prepare 0 Com\_xa\_recover 0 Com\_xa\_rollback 0 Com\_xa\_start 0 Compression OFF Connections 3724 Created\_tmp\_disk\_tables 0 Created\_tmp\_files 11 Created\_tmp\_tables 8 Delayed\_errors 0 Delayed\_insert\_threads 0 Delayed\_writes 0 Flush\_commands 3 Handler\_commit 0 Handler\_delete 0 Handler\_discover 0 Handler\_prepare 0 Handler\_read\_first 2 Handler\_read\_key 0 Handler\_read\_next 4464163 Handler\_read\_prev 0 Handler\_read\_rnd 8 Handler\_read\_rnd\_next 405 Handler\_rollback 0 Handler\_savepoint 0 Handler\_savepoint\_rollback 0 Handler\_update 0 Handler\_write 528 Innodb\_buffer\_pool\_pages\_data 19 Innodb\_buffer\_pool\_pages\_dirty 0 Innodb\_buffer\_pool\_pages\_flushed 0 Innodb\_buffer\_pool\_pages\_free 493 Innodb\_buffer\_pool\_pages\_latched 0 Innodb\_buffer\_pool\_pages\_misc 0 Innodb\_buffer\_pool\_pages\_total 512 Innodb\_buffer\_pool\_read\_ahead\_rnd 1 Innodb\_buffer\_pool\_read\_ahead\_seq 0 Innodb\_buffer\_pool\_read\_requests 77 Innodb\_buffer\_pool\_reads 12 Innodb\_buffer\_pool\_wait\_free 0 Innodb\_buffer\_pool\_write\_requests 0 Innodb\_data\_fsyncs 3 Innodb\_data\_pending\_fsyncs 0 Innodb\_data\_pending\_reads 0 Innodb\_data\_pending\_writes 0 Innodb\_data\_read 2494464 Innodb\_data\_reads 25 Innodb\_data\_writes 3 Innodb\_data\_written 1536 Innodb\_dblwr\_pages\_written 0 Innodb\_dblwr\_writes 0 Innodb\_log\_waits 0 Innodb\_log\_write\_requests 0 Innodb\_log\_writes 1 Innodb\_os\_log\_fsyncs 3 Innodb\_os\_log\_pending\_fsyncs 0 Innodb\_os\_log\_pending\_writes 0 Innodb\_os\_log\_written 512 Innodb\_page\_size 16384 Innodb\_pages\_created 0 Innodb\_pages\_read 19 Innodb\_pages\_written 0 Innodb\_row\_lock\_current\_waits 0 Innodb\_row\_lock\_time 0 Innodb\_row\_lock\_time\_avg 0 Innodb\_row\_lock\_time\_max 0 Innodb\_row\_lock\_waits 0 Innodb\_rows\_deleted 0 Innodb\_rows\_inserted 0 Innodb\_rows\_read 0 Innodb\_rows\_updated 0 Key\_blocks\_not\_flushed 0 Key\_blocks\_unused 0 Key\_blocks\_used 14497 Key\_read\_requests 145870080 Key\_reads 580288 Key\_write\_requests 31037715 Key\_writes 30653236 Last\_query\_cost 0.000000 Max\_used\_connections 24 Not\_flushed\_delayed\_rows 0 Open\_files 21 Open\_streams 0 Open\_tables 15 Opened\_tables 0 Qcache\_free\_blocks 1 Qcache\_free\_memory 8379904 Qcache\_hits 13 Qcache\_inserts 44 Qcache\_lowmem\_prunes 0 Qcache\_not\_cached 87207 Qcache\_queries\_in\_cache 0 Qcache\_total\_blocks 1 Questions 14963997 Rpl\_status NULL Select\_full\_join 0 Select\_full\_range\_join 0 Select\_range 0 Select\_range\_check 0 Select\_scan 8 Slave\_open\_temp\_tables 0 Slave\_retried\_transactions 0 Slave\_running OFF Slow\_launch\_threads 3 Slow\_queries 2 Sort\_merge\_passes 0 Sort\_range 0 Sort\_rows 8 Sort\_scan 2 Ssl\_accept\_renegotiates 0 Ssl\_accepts 0 Ssl\_callback\_cache\_hits 0 Ssl\_cipher Ssl\_cipher\_list Ssl\_client\_connects 0 Ssl\_connect\_renegotiates 0 Ssl\_ctx\_verify\_depth 0 Ssl\_ctx\_verify\_mode 0 Ssl\_default\_timeout 0 Ssl\_finished\_accepts 0 Ssl\_finished\_connects 0 Ssl\_session\_cache\_hits 0 Ssl\_session\_cache\_misses 0 Ssl\_session\_cache\_mode NONE Ssl\_session\_cache\_overflows 0 Ssl\_session\_cache\_size 0 Ssl\_session\_cache\_timeouts 0 Ssl\_sessions\_reused 0 Ssl\_used\_session\_cache\_entries 0 Ssl\_verify\_depth 0 Ssl\_verify\_mode 0 Ssl\_version Table\_locks\_immediate 14231643 Table\_locks\_waited 49584 Tc\_log\_max\_pages\_used 0 Tc\_log\_page\_size 0 Tc\_log\_page\_waits 0 Threads\_cached 8 Threads\_connected 4 Threads\_created 1981 Threads\_running 2 Uptime 326624 Does anything there look out of place? Thanks! -Joe [http://videos.lovehorsepower.com](http://videos.lovehorsepower.com) ---
**Author:** ![mikec](https://avatars.discourse-cdn.com/v4/letter/m/977dab/32.png) [@mikec](https://forums.percona.com/u/mikec)\ **Post date:** [October 1, 2007, 3:13pm UTC](https://forums.percona.com/t/mysql-swapping-issue-memory-usage/471/7 "2007-10-01T15:13:59Z")
aborted clients seems really hi. Try reboot of MySQL and rerun your app. Motor thru different parts of your app and keep an eye on how this variable goes up. This may be a memory leak of some kind. ---
**Author:** ![joeo](https://avatars.discourse-cdn.com/v4/letter/j/9fc29f/32.png) [@joeo](https://forums.percona.com/u/joeo)\ **Post date:** [October 1, 2007, 3:48pm UTC](https://forums.percona.com/t/mysql-swapping-issue-memory-usage/471/8 "2007-10-01T15:48:40Z")
Thank you! I will check this and get back to you! -JoeO [http://www.lovehorsepower.com](http://www.lovehorsepower.com)