consumo non controllato della swap
RedFoxy Darrest <[email protected]> Wed, 17 Feb 2016 12:42:53 +0100
| Newsgroups | gmane.os.freebsd.italian.esperti |
|---|---|
| Message-ID | <[email protected]> |
Ciao ragazzi! Sono due giorni che mi crasha mysql (mysql Ver 14.14 Distrib 5.6.27-75.0 Percona su di un server 10.2-RELEASE-p7 con 16Gb di ram), ogni volta ricevo come errori "mysql has gone" e un più preoccupante "mysqli_real_connect(): (HY000/2002): No such file or directory”, verificando scopro che la swap (4gb) è stata riempita e quindi il servizio è stato riavviato: Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 last message repeated 9 times Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(8): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(12): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(12): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(12): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(12): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(12): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(6): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(12): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(9): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(12): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(9): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(12): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(9): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(12): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(9): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(12): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(9): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(12): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(9): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(12): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(9): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(12): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(9): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(9): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(16): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(9): failed Feb 17 10:20:44 samweb1 kernel: swap_pager_getswapspace(3): failed Feb 17 10:20:44 samweb1 last message repeated 2 times Feb 17 10:20:46 samweb1 kernel: pid 57086 (mysqld), uid 88, was killed: out of swap space Feb 17 10:20:56 samweb1 kernel: sonewconn: pcb 0xfffff800982b53c0: Listen queue overflow: 151 already in queue awaiting acceptance (34 occurrences) Verificando i log di mysql non vi è alcun errore o segnalazione, solo il fatto che il servizio è stato riavviato, ho verificato lo stato della swap, ma ormai era inutile visto che servizio è stato riavviato: # swapinfo -h Device 1K-blocks Used Avail Capacity /dev/mfid0p3 4194304 485M 3.5G 12% Trovo strano questo comportamento, è vero che da poco sono passato da mysql standard a percona, inoltre ho fatto un po’ di “ottimizzazione” della configurazione, basandomi molto su mysqltuner e tuning-primer, il server sembra funzionare perfettamente tranne questi momenti di collasso. # mysqltuner >> MySQLTuner 1.6.0 - Major Hayden <[email protected]> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.6.27-75.0-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in InnoDB tables: 5G (Tables: 1220) [--] Data in MyISAM tables: 517M (Tables: 961) [--] Data in MEMORY tables: 7M (Tables: 6) [!!] Total fragmented tables: 116 -------- Security Recommendations ------------------------------------------- [OK] There is no anonymous account in all database users [OK] All database users have passwords assigned [!!] There is not basic password file list ! -------- Performance Metrics ------------------------------------------------- [--] Up for: 9m 53s (28K q [47.862 qps], 1K conn, TX: 316M, RX: 8M) [--] Reads / Writes: 86% / 14% [--] Binary logging is enabled (GTID MODE: OFF) [--] Total buffers: 8.5G global + 3.5M per thread (1000 max threads) [OK] Maximum reached memory usage: 8.6G (53.58% of installed RAM) [OK] Maximum possible memory usage: 11.9G (74.52% of installed RAM) [OK] Slow queries: 0% (1/28K) [OK] Highest usage of available connections: 2% (22/1000) [OK] Aborted connections: 0.00% (0/1676) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (10 temp sorts / 2K sorts) [!!] Joins performed without indexes: 797 [!!] Temporary tables created on disk: 52% (897 on disk / 1K total) [OK] Thread cache hit rate: 98% (22 created / 1K connections) [OK] Table cache hit rate: 98% (465 open / 472 opened) [OK] Open file limit used: 1% (431/40K) [OK] Table locks acquired immediately: 99% (34K immediate / 34K locks) [OK] Binlog cache memory access: 99.25% ( 928 Memory / 935 Total) -------- MyISAM Metrics ----------------------------------------------------- [!!] Key buffer used: 18.9% (297M used / 1B cache) [OK] Key buffer size / total MyISAM indexes: 1.5G/204.1M [OK] Read Key buffer hit rate: 97.8% (98K cached / 2K reads) [!!] Write Key buffer hit rate: 44.8% (858 cached / 474 writes) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 6.0G/5.7G [OK] InnoDB buffer pool instances: 6 [!!] InnoDB Used buffer: 15.09% (59337 used/ 393210 total) [OK] InnoDB Read buffer efficiency: 99.16% (6571034 hits/ 6626518 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 1382 writes) -------- AriaDB Metrics ----------------------------------------------------- [--] AriaDB is disabled. -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] This is a standalone server.. -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Adjust your join queries to always utilize indexes Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: query_cache_size (>= 8M) join_buffer_size (> 1.0M, or always use indexes with joins) teoricamente mysql dovrebbe occupare solo 12Gb su 16Gb di ram, lasciando 4Gb di ram e 4Gb di swap al sistema e agli altri software (php-fpm, ngnix e poca altra roba), di seguito un elenco dei 100 processi che usano più ram sul mio server: # top -b -o res 100 last pid: 25873; load averages: 1.08, 1.11, 1.02 up 68+01:36:36 12:40:03 140 processes: 2 running, 138 sleeping Mem: 3618M Active, 5167M Inact, 2328M Wired, 20M Cache, 1666M Buf, 4780M Free Swap: 4096M Total, 485M Used, 3611M Free, 11% Inuse PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND 22932 mysql 53 24 0 9134M 3007M uwait 3 1:20 9.33% mysqld 1401 clamav 2 20 0 432M 330M select 2 85:13 0.00% clamd 5667 vscan 1 20 0 426M 286M select 2 0:06 0.00% perl 5668 vscan 1 28 0 422M 285M lockf 1 0:05 0.00% perl 5568 vscan 1 20 0 416M 281M select 7 0:19 0.00% perl 23092 web 1 20 -19 360M 92244K accept 3 0:42 2.10% php-fpm 24135 web 1 20 -19 360M 91248K accept 7 0:30 4.49% php-fpm 23303 web 1 22 -19 348M 79688K accept 5 0:24 0.00% php-fpm 24134 web 1 20 -19 348M 79408K accept 0 0:19 3.37% php-fpm 23205 web 1 20 -19 344M 75844K accept 1 0:35 1.95% php-fpm 23094 web 1 20 -19 344M 75824K accept 0 0:29 0.39% php-fpm 24566 web 1 20 -19 344M 75692K accept 7 0:18 3.86% php-fpm 24859 web 1 20 -19 344M 75508K accept 3 0:18 0.59% php-fpm 23762 web 1 20 -19 340M 71548K accept 2 0:33 3.56% php-fpm 23089 web 1 20 -19 340M 71460K accept 0 0:45 0.39% php-fpm 23495 web 1 20 -19 336M 67684K accept 0 0:34 1.07% php-fpm 23091 web 1 20 -19 336M 67536K accept 7 0:43 3.37% php-fpm 23302 web 1 20 -19 336M 67516K accept 3 0:28 3.76% php-fpm 24807 web 1 20 -19 335M 67280K accept 2 0:17 1.17% php-fpm 23084 web 1 20 -19 335M 67248K accept 0 0:40 4.98% php-fpm 24567 web 1 52 -19 331M 67008K accept 2 0:07 0.00% php-fpm 23093 web 1 20 -19 336M 66892K accept 4 0:27 0.10% php-fpm 24806 web 1 20 -19 332M 63580K accept 2 0:21 1.86% php-fpm 23072 web 1 21 -19 332M 63504K accept 7 0:43 0.00% php-fpm 23095 web 1 20 -19 332M 63364K accept 2 0:35 2.69% php-fpm 23494 web 1 20 -19 332M 63356K accept 3 0:28 1.76% php-fpm 23496 web 1 20 -19 332M 63344K accept 6 0:24 0.10% php-fpm 23075 web 1 27 -19 331M 63136K accept 2 0:24 0.00% php-fpm 23497 web 1 20 -19 332M 60352K select 4 0:28 7.47% php-fpm 23096 web 1 20 -19 328M 59452K accept 7 0:37 6.30% php-fpm 23090 web 1 20 -19 328M 59260K accept 7 0:40 0.49% php-fpm 25174 web 1 20 -19 328M 59204K accept 7 0:05 0.49% php-fpm 23087 web 1 52 -19 323M 59076K accept 1 0:25 0.00% php-fpm 23292 web 1 21 -19 327M 58924K accept 7 0:04 0.00% php-fpm 25175 web 1 20 -19 319M 54888K accept 1 0:09 0.98% php-fpm 25637 web 1 20 -19 315M 50620K accept 3 0:02 0.98% php-fpm 23073 web 1 20 -19 315M 50168K accept 5 0:01 0.00% php-fpm 23391 web 1 33 -19 311M 46640K accept 2 0:08 0.00% php-fpm 25638 web 1 20 -19 311M 46620K CPU0 0 0:05 3.86% php-fpm 25179 web 1 27 -19 311M 46248K accept 7 0:00 0.00% php-fpm 24565 web 1 21 -19 311M 46072K accept 0 0:00 0.00% php-fpm 23064 root 1 20 -19 305M 37576K kqread 0 0:00 0.00% php-fpm 23082 web 1 30 -19 303M 36156K accept 2 0:00 0.00% php-fpm 23085 web 1 34 -19 303M 36156K accept 0 0:00 0.00% php-fpm 23076 web 1 24 -19 303M 36156K accept 7 0:00 0.00% php-fpm 23088 web 1 36 -19 303M 36156K accept 3 0:00 0.00% php-fpm 25419 nobody 1 20 0 72964K 22488K sbwait 4 0:00 0.00% perl 5287 vmail 1 20 0 84000K 19836K kqread 1 0:02 0.00% imap 83308 web 3 20 0 213M 19676K kqread 3 64:36 0.00% rtorrent 24842 web 1 20 0 60708K 19064K kqread 2 0:01 0.00% nginx 24849 web 1 20 0 60708K 18740K kqread 5 0:01 0.00% nginx 24847 web 1 20 0 60708K 18712K kqread 2 0:00 0.00% nginx 24844 web 1 20 0 60708K 18676K kqread 5 0:00 0.00% nginx 24846 web 1 20 0 60708K 18616K kqread 7 0:00 0.00% nginx 24848 web 1 20 0 60708K 18608K kqread 5 0:00 0.00% nginx 24843 web 1 20 0 60708K 18600K kqread 7 0:00 0.00% nginx 24845 web 1 20 0 60708K 18584K kqread 7 0:00 0.00% nginx 57504 root 1 20 0 26164K 18084K select 5 0:54 0.00% ntpd 1564 nobody 6 52 0 73040K 13704K uwait 4 610:32 0.00% memcached 94832 web 1 52 0 111M 13596K select 2 21:58 0.00% python2.7 23343 root 1 20 0 353M 10876K select 6 0:32 0.00% perl 23431 root 1 20 0 353M 10392K select 5 0:00 0.00% perl 24841 root 1 52 0 48420K 10380K pause 7 0:00 0.00% nginx 23430 root 1 20 0 353M 10356K select 7 0:00 0.00% perl 61061 opendkim 4 20 0 73452K 10068K select 0 0:10 0.00% opendkim 1573 root 11 20 0 134M 8892K select 5 145:15 0.00% python2.7 25416 postfix 1 20 0 48008K 8024K kqread 6 0:00 0.00% smtpd 25418 postfix 1 20 0 47748K 7308K kqread 7 0:00 0.00% trivial-rewrite 25417 postfix 1 20 0 47724K 7304K kqread 0 0:00 0.00% proxymap 24717 postfix 1 20 0 47740K 7128K kqread 7 0:00 0.00% pickup 25163 postfix 1 20 0 47732K 7124K kqread 5 0:00 0.00% anvil 25177 root 1 20 0 47732K 7112K wait 7 0:00 0.00% spawn 5407 root 1 21 0 86504K 7068K select 0 0:00 0.00% sshd 5410 web 1 20 0 86504K 7064K select 7 0:00 0.00% sshd 5292 vmail 1 20 0 38168K 5980K kqread 0 0:00 0.00% imap 5304 dovenull 1 20 0 24144K 5640K kqread 6 0:00 0.00% imap-login 5277 dovenull 1 20 0 24144K 5636K kqread 7 0:00 0.00% imap-login 5279 dovenull 1 20 0 24144K 5636K kqread 6 0:00 0.00% imap-login 5288 dovenull 1 20 0 24144K 5636K kqread 0 0:00 0.00% imap-login 5302 dovenull 1 20 0 24144K 5636K kqread 6 0:00 0.00% imap-login 5300 dovenull 1 20 0 24144K 5636K kqread 4 0:00 0.00% imap-login 5286 dovenull 1 20 0 24144K 5636K kqread 7 0:00 0.00% imap-login 5303 dovenull 1 20 0 24144K 5636K kqread 6 0:00 0.00% imap-login 5296 dovenull 1 20 0 24144K 5636K kqread 7 0:00 0.00% imap-login 5297 dovenull 1 20 0 24144K 5636K kqread 6 0:00 0.00% imap-login 5291 dovenull 1 20 0 24144K 5636K kqread 7 0:00 0.00% imap-login 19429 dovenull 1 20 0 24144K 5636K kqread 4 0:00 0.00% imap-login 5280 dovenull 1 20 0 24144K 5636K kqread 6 0:00 0.00% imap-login 4968 dovenull 1 20 0 24144K 5636K kqread 6 0:00 0.00% imap-login 24248 dovenull 1 20 0 24144K 5636K kqread 5 0:00 0.00% imap-login 4975 dovenull 1 20 0 24144K 5636K kqread 7 0:00 0.00% imap-login 5289 vmail 1 20 0 36772K 5536K kqread 0 0:00 0.00% imap 5308 vmail 1 20 0 35696K 5208K kqread 3 0:00 0.00% imap 5309 vmail 1 20 0 35692K 5204K kqread 1 0:00 0.00% imap 25633 dovecot 1 20 0 39592K 5096K kqread 7 0:00 0.00% auth 5293 vmail 1 20 0 35736K 5064K kqread 5 0:00 0.00% imap 4974 vmail 1 20 0 35700K 4996K kqread 3 0:00 0.00% imap 19430 vmail 1 20 0 35700K 4928K kqread 1 0:00 0.00% imap 5306 vmail 1 20 0 35700K 4924K kqread 0 0:00 0.00% imap 5305 vmail 1 20 0 35688K 4892K kqread 0 0:00 0.00% imap La configurazione di mysql è: [client] socket = /tmp/mysql.sock [mysqld] bind_address = 127.0.0.1 socket = /tmp/mysql.sock server_id = 1 #skip_name_resolve query_cache_type = 1 query_cache_size = 0 query_cache_min_res_unit = 1k query_cache_limit = 32M table_open_cache = 2500 table_definition_cache = 2500 wait_timeout = 150 interactive_timeout = 150 max_connections = 1000 sort_buffer_size = 1M join_buffer_size = 1M read_buffer_size = 1M innodb_buffer_pool_size = 6G innodb_buffer_pool_instances = 6 innodb_log_buffer_size = 8M tmp_table_size = 1G max_heap_table_size = 1G key_buffer_size = 1500M open_files_limit = 30000 slow_query_log = 0 # 0 = disattiva 1 = attiva long_query_time = 3 # In secondi #log_queries_not_using_indexes = TRUE explicit_defaults_for_timestamp tmpdir = /tmp thread_cache_size = 34 ft_min_word_len = 3 default_storage_engine = InnoDB max_allowed_packet = 128M #16M #05/10/2015 max_connect_errors = 10 log-bin binlog_format = mixed #row expire_logs_days = 5 innodb_data_file_path = ibdata1:100M:autoextend innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_thread_concurrency = 8 transaction_isolation = READ-COMMITTED [mysqldump] quick max_allowed_packet = 128M [mysqlhotcopy] [mysql] no_auto_rehash [mysqld-safe] Qualche suggerimento? _______________________________________________ Esperti mailing list [email protected] http://mailman.gufi.org/mailman/listinfo/esperti