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 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 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.