Home
Reading
Searching
Subscribe
Sponsors
Statistics
Posting
Contact
Spam
Lists
Links
About
Hosting
Filtering
Features Download
Marketing
Archives
FAQ
Blog
 
Gmane
From: RedFoxy Darrest <redfoxy <at> redfoxy.it>
Subject: consumo non controllato della swap
Newsgroups: gmane.os.freebsd.italian.esperti
Date: Wednesday 17th February 2016 11:42:53 UTC (about 1 year ago)
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 
 >>  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
 
CD: 4ms