# MySQL Swap issue

**URL:** <https://forums.percona.com/t/mysql-swap-issue/2025>\
**Category:** Other MySQL® Questions\
**Created:** [March 25, 2013, 2:26am UTC](https://forums.percona.com/t/mysql-swap-issue/2025 "2013-03-25T02:26:11Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![schery](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@schery](https://forums.percona.com/u/schery)\
**Post date:** [March 25, 2013, 2:26am UTC](https://forums.percona.com/t/mysql-swap-issue/2025/1 "2013-03-25T02:26:11Z")

</div>

Hi everyone.

I need your help with a swap problem.  
First of all, sorry for my English ☹

Here is my problem, i’ve got a MySQL database in production.  
There are 4 schemas and global database size is around 80GB  
There are some tables (10 tables) with around 20 000 000 lines.

All tables are in innodb.  
This database is running on Ubuntu 10.04 and is on a dedicated server. MySQL version is 5.0.51

The problem is that, every week, a get an alert on Swap which is growing up everyday.

I tried severals things (playing with my.cnf) but the problem problem still occur.  
I tried decrease the innodb\_buffer\_pool\_size, the swap came later but still came after 1 week.

I really don’t know where is the problem so if someone can help !

Here is my memory use and my my.cnf :

free -m total used free shared buffers cachedMem: 12040 11617 423 0 61 459-/+ buffers/cache: 11096 944Swap: 11615 3099 8516My.cnf :[client]port = 3306socket = /var/run/mysqld/mysqld.sock[mysqld\_safe]socket = /var/run/mysqld/mysqld.socknice = 0[mysqld]# DEFINITION DU DEFAULT STORAGE ENGINE AND CHARACTERSdefault-storage-engine=InnoDBdefault-character-set=utf8default-collation=utf8collation\_server=utf8\_general\_cicharacter\_set\_server=utf8user = mysqlpid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockport = 3306basedir = /usrdatadir = /var/lib/mysqltmpdir = /tmplanguage = /usr/share/mysql/englishskip-external-locking############################################# Gestion des Logs############################################# Definition des fichiers de logs#log = /var/log/mysql/mysql.loglog\_error = /var/log/mysql/error.log# Definition des log query time=temps a partir duquel une requete et long (en sec) / fichier de loglong\_query\_time = 2000log-slow-queries = /var/log/mysql/lquery.loglog-queries-not-using-indexes# Gestion des fichiers bin-loglog\_bin = /var/log/mysql/mysql-bin.logexpire\_logs\_days = 3max\_binlog\_size = 150Mlog\_bin\_trust\_function\_creators = 1#sync\_binlog = 1############################################# Fine Tuning############################################# Enlever le respect de la casselower\_case\_table\_names = 1# Taille des tables temporairestmp\_table\_size = 32Mmax\_heap\_table\_size = 32M# Definition du nombre max de connexionsmax\_connections = 1200max\_user\_connections = 1200max\_connect\_errors = 50# Definition des timeoutwait\_timeout = 31536000connect\_timeout = 31536000interactive\_timeout = 31536000# Nombre max de fichiers ouverts en simultaneopen-files-limit = 32000key\_buffer = 20Mmax\_allowed\_packet = 16Mthread\_stack = 256Kthread\_cache\_size = 64table\_cache = 2048thread\_concurrency = 12bulk\_insert\_buffer\_size = 1024read\_buffer\_size = 512Ksort\_buffer\_size = 1Mjoin\_buffer\_size = 1M############################################# Query Cache Configuration############################################query\_cache\_limit = 16Mquery\_cache\_size = 24Mquery\_cache\_type = 1# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.skip-bdb############################################# Optimisations InnoDB#############################################low\_priority\_updates = 1# 1 ficher tablespace par tableinnodb\_file\_per\_table# utiliser un buffer de 50 à 80 % de la RAM du SRVinnodb\_buffer\_pool\_size =6Ginnodb\_additional\_mem\_pool\_size =20M # Fichier de log de 25% du buffer RAMinnodb\_log\_file\_size =256Minnodb\_log\_buffer\_size =16M# La valeur 2 optimise fortement les requetesinnodb\_flush\_log\_at\_trx\_commit =2innodb\_lock\_wait\_timeout =100#innodb\_thread\_concurrency =12innodb\_flush\_method =O\_DIRECTinnodb\_fast\_shutdown = 0####################################[myisamchk]key\_buffer = 64Msort\_buffer = 64Mread\_buffer = 16Mwrite\_buffer = 16M[mysqldump]quickquote-namesmax\_allowed\_packet = 32M[mysql]#no-auto-rehash # faster start of mysql but no tab completition[isamchk]key\_buffer = 512M!includedir /etc/mysql/conf.d/

Here is innodb status (at this time, i get 3Gb swapped) :

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*_**Status:=====================================130325 8:19:35 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 5 seconds----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 46808695, signal count 39134530Mutex spin waits 0, rounds 6801755261, OS waits 20892489RW-shared spins 108332994, OS waits 13238686; RW-excl spins 317936348, OS waits 8465958------------------------LATEST DETECTED DEADLOCK------------------------130323 14:47:37 (1) TRANSACTION:TRANSACTION 2 3377082957, ACTIVE 1 sec, process no 1075, OS thread id 140454537946880 setting auto-inc lockmysql tables in use 2, locked 2LOCK WAIT 4 lock struct(s), heap size 1216, undo log entries 1MySQL thread id 18093863, query id 586539650 [lx-webfid.alinea-1.fr](http://lx-webfid.alinea-1.fr) 10.13.9.28 root updateinsert into FID\_ZZMODIF (DTMOD, TABLENAME, KEYSTRING, KEYVAL,OPER) values (sysdate(), ‘FID\_TIERS’, ‘IDSOC;IDINSTTIE;IDTIERS’, concat(new.IDSOC,‘;’,new.IDINSTTIE,‘;’,new.IDTIERS),‘U’) (1) WAITING FOR THIS LOCK TO BE GRANTED:TABLE LOCK table `fidelite/fid_zzmodif` trx id 2 3377082957 lock mode AUTO-INC waiting (2) TRANSACTION:TRANSACTION 2 3377082828, ACTIVE 2 sec, process no 1075, OS thread id 140454519576320 fetching rows, thread declared inside InnoDB 327mysql tables in use 3, locked 310799 lock struct(s), heap size 1062896, undo log entries 2MySQL thread id 18093812, query id 586539095 [lx-webfid.alinea-1.fr](http://lx-webfid.alinea-1.fr) 10.13.9.28 rootINSERT INTO fidelite.fid\_tiers\_poubelle (select \* from fidelite.fid\_tiers where numfid = 2000001746103) (2) HOLDS THE LOCK(S):TABLE LOCK table `fidelite/fid_zzmodif` trx id 2 3377082828 lock mode AUTO-INC (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 292176 page no 28423 n bits 168 index `PRIMARY` of table `fidelite/fid_tiers` trx id 2 3377082828 lock mode S waitingRecord lock, heap no 91 PHYSICAL RECORD: n\_fields 34; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 4; hex 800003e7; asc ;; 2: len 4; hex 801fda89; asc ;; 3: len 6; hex 0002c94a324d; asc J2M;; 4: len 7; hex 000000002d3001; asc -0 ;; 5: len 13; hex 32303030303035363531373030; asc 2000005651700;; 6: SQL NULL; 7: SQL NULL; 8: SQL NULL; 9: len 2; hex 8000; asc ;; 10: len 2; hex 8000; asc ;; 11: len 2; hex 7fff; asc ;; 12: len 2; hex 7fff; asc ;; 13: len 8; hex 8000124ef4c0c75a; asc N Z;; 14: len 2; hex 8000; asc ;; 15: SQL NULL; 16: len 2; hex 7fff; asc ;; 17: len 2; hex 8000; asc ;; 18: len 4; hex 80000005; asc ;; 19: len 4; hex 80000005; asc ;; 20: SQL NULL; 21: len 2; hex 8000; asc ;; 22: SQL NULL; 23: SQL NULL; 24: len 2; hex 8001; asc ;; 25: len 2; hex 8000; asc ;; 26: len 2; hex 7fff; asc ;; 27: len 2; hex 8000; asc ;; 28: len 2; hex 8002; asc ;; 29: SQL NULL; 30: SQL NULL; 31: len 0; hex ; asc ;; 32: SQL NULL; 33: SQL NULL;**_ WE ROLL BACK TRANSACTION (1)------------TRANSACTIONS------------Trx id counter 2 3416049958Purge done for trx’s n:o \< 2 3416049843 undo n:o \< 0 0History list length 5Total number of lock structs in row lock hash table 4LIST OF TRANSACTIONS FOR EACH SESSION:—TRANSACTION 0 0, not started, process no 1075, OS thread id 140454397339392MySQL thread id 21019432, query id 670552927 localhost root--------FILE I/O--------I/O thread 0 state: waiting for i/o request (insert buffer thread)I/O thread 1 state: waiting for i/o request (log thread)I/O thread 2 state: waiting for i/o request (read thread)I/O thread 3 state: waiting for i/o request (write thread)Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0Pending flushes (fsync) log: 0; buffer pool: 017048692 OS file reads, 330282862 OS file writes, 2294038 OS fsyncs34.59 reads/s, 36745 avg bytes/read, 14.00 writes/s, 2.00 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 4551, free list len 14617, seg size 19169,2792396 inserts, 2085808 merged recs, 499111 mergesHash table size 12750011, used cells 10698803, node heap has 34368 buffer(s)92432.31 hash searches/s, 18140.77 non-hash searches/s—LOG—Log sequence number 1989 4173680409Log flushed up to 1989 4173664867Last checkpoint at 1989 41410715140 pending log writes, 0 pending chkp writes325256386 log i/o’s done, 13.60 log i/o’s/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 7184845082; in additional pool allocated 20971008Buffer pool size 393216Free buffers 0Database pages 358848Modified db pages 1072Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages read 65279381, created 6355298, written 1852880777.98 reads/s, 0.00 creates/s, 12.80 writes/sBuffer pool hit rate 1000 / 1000--------------ROW OPERATIONS--------------1 queries inside InnoDB, 0 queries in queue10 read views open inside InnoDBMain thread process no. 1075, id 140454934210304, state: sleepingNumber of rows inserted 745961691, updated 16208910, deleted 172999698, read 42391275177016.40 inserts/s, 3.20 updates/s, 0.00 deletes/s, 148335.93 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================

And here is tunning-primer result (currently 3Gb swapped):

– MYSQL PERFORMANCE TUNING PRIMER – - By: Matthew Montgomery -MySQL Version 5.0.51a-3ubuntu5.8-log x86\_64Uptime = 12 days 10 hrs 29 min 53 secAvg. qps = 623Total Questions = 670512598Threads Connected = 149Server has been running for over 48hrs.It should be safe to follow these recommendationsSLOW QUERIESThe slow query log is enabled.Current long\_query\_time = 2000 sec.You have 6610653 out of 670512686 that take longer than 2000 sec. to completeYour long\_query\_time seems to be fineBINARY UPDATE LOGThe binary update log is enabledBinlog sync is not enabled, you could loose binlog records during a server crashWORKER THREADSCurrent thread\_cache\_size = 64Current threads\_cached = 3Current threads\_per\_sec = 0Historic threads\_per\_sec = 0Your thread\_cache\_size is fineMAX CONNECTIONSCurrent max\_connections = 1200Current threads\_connected = 149Historic max\_used\_connections = 906The number of used connections is 75% of the configured maximum.Your max\_connections variable seems to be fine.INNODB STATUSCurrent InnoDB index space = 18.38 GCurrent InnoDB data space = 18.84 GCurrent InnoDB buffer pool free = 0 %Current innodb\_buffer\_pool\_size = 6.00 GDepending on how much space your innodb indexes take up it may be safeto increase this value to up to 2 / 3 of total system memoryMEMORY USAGEMax Memory Ever Allocated : 8.75 GConfigured Max Per-thread Buffers : 3.54 GConfigured Max Global Buffers : 6.07 GConfigured Max Memory Limit : 9.62 GPhysical Memory : 11.75 GMax memory limit seem to be within acceptable normsKEY BUFFERCurrent MyISAM index space = 233 MCurrent key\_buffer\_size = 20 MKey cache miss rate is 1 : 147Key buffer free ratio = 0 %You could increase key\_buffer\_sizeIt is safe to raise this up to 1/4 of total system memory;assuming this is a dedicated database server.QUERY CACHEQuery cache is enabledCurrent query\_cache\_size = 24 MCurrent query\_cache\_used = 7 MCurrent query\_cache\_limit = 16 MCurrent Query cache Memory fill ratio = 32.01 %Current query\_cache\_min\_res\_unit = 4 KQuery Cache is 24 % fragmentedRun “FLUSH QUERY CACHE” periodically to defragment the query cache memoryIf you have many small queries lower ‘query\_cache\_min\_res\_unit’ to reduce fragmentation.MySQL won’t cache query results that are larger than query\_cache\_limit in sizeSORT OPERATIONSCurrent sort\_buffer\_size = 1 MCurrent read\_rnd\_buffer\_size = 256 KSort buffer seems to be fineJOINSCurrent join\_buffer\_size = 1.00 MYou have had 11824 queries where a join could not use an index properlyYou should enable "log-queries-not-using-indexes"Then look for non indexed joins in the slow query log.If you are unable to optimize your queries you may want to increase yourjoin\_buffer\_size to accommodate larger joins in one pass.Note! This script will still suggest raising the join\_buffer\_size whenANY joins not using indexes are found.OPEN FILES LIMITCurrent open\_files\_limit = 32000 filesThe open\_files\_limit should typically be set to at least 2x-3xthat of table\_cache if you have heavy MyISAM usage.Your open\_files\_limit value seems to be fineTABLE CACHECurrent table\_cache value = 2048 tablesYou have a total of 286 tablesYou have 425 open tables.The table\_cache value seems to be fineTEMP TABLESCurrent max\_heap\_table\_size = 32 MCurrent tmp\_table\_size = 32 MOf 66866944 temp tables, 0% were created on diskCreated disk tmp tables ratio seems fineTABLE SCANSCurrent read\_buffer\_size = 508 KCurrent table scan ratio = 302 : 1read\_buffer\_size seems to be fineTABLE LOCKINGCurrent Lock Wait ratio = 1 : 923255Your table locking seems to be fine

Many thanks for your help.

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [March 25, 2013, 7:09am UTC](https://forums.percona.com/t/mysql-swap-issue/2025/2 "2013-03-25T07:09:11Z")

</div>

What other processes run on the server?

---

<div class="post-metadata">

**Author:** ![schery](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@schery](https://forums.percona.com/u/schery)\
**Post date:** [March 25, 2013, 12:10pm UTC](https://forums.percona.com/t/mysql-swap-issue/2025/3 "2013-03-25T12:10:54Z")

</div>

Hi and thanks for your reply.  
Only MySQL is running on this server.

Regards