URL: https://www.opennet.me/cgi-bin/openforum/vsluhboard.cgi
Форум: vsluhforumID1
Нить номер: 89993
[ Назад ]
Исходное сообщение
"Почему mysql вылезает в swap"
Отправлено small_linux_admin , 09-Окт-10 18:30 
Есть mysql сервер (на нем ничего больше не крутится). На нем 4 базы. 3 из них в myisam (общий объем 400МБ) и одна целиком innodb (10 гб). Базы myisam практически не используются, может 3-4 запроса в час, основная информация в innodb базе.
Запросы естественно к innodb тяжелые. То выполняются достаточно быстро. Обнаружил 2 проблемы.

1. Почему когда снимаю дамп с базы innodb резко падает скорость выполнения запросов? Как это лечить?

2. На сервере установлено 8 Гб памяти. После суток работы я обнаруживаю, что сервер mysql залез на 6гб в swap. Почему? Вроде нет ошибок в конфиге и память посчитана правильно под буфера и прочее.

root@db:~# cat /proc/meminfo
MemTotal:      8179108 kB
MemFree:         46896 kB
Buffers:         34368 kB
Cached:          23688 kB
SwapCached:    4488240 kB
Active:        7031740 kB
Inactive:      1008060 kB
SwapTotal:    11855928 kB
SwapFree:      5927964 kB
Dirty:              24 kB
Writeback:          16 kB
AnonPages:     7746516 kB
Mapped:           8196 kB
Slab:            49632 kB
SReclaimable:    15632 kB
SUnreclaim:      34000 kB
PageTables:      20140 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:  15945480 kB
Committed_AS:  9688980 kB
VmallocTotal: 34359738367 kB
VmallocUsed:      6076 kB
VmallocChunk: 34359732247 kB

Настройки такие:
root@db:~# cat /etc/mysql/my.cnf
[client]
port        = 3306
socket        = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket        = /var/run/mysqld/mysqld.sock
nice        = 0
[mysqld]
user        = mysql
character-set-server    = utf8
default-character-set    = utf8
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
log-error       = /var/log/mysql/mysqld.err
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
language    = /usr/share/mysql/english
skip-external-locking
bind-address        = 192.168.117.2
max_connections        = 2000
wait_timeout        = 60
key_buffer        = 128M
sort_buffer_size    = 4M
read_buffer_size    = 2M
join_buffer_size    = 2M
max_heap_table_size    = 128M
tmp_table_size        = 512M
thread_cache_size    = 20
table_cache        = 256
query_cache_size    = 64M
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time        = 7
log_bin_trust_function_creators = 1
myisam_sort_buffer_size    = 4M
myisam-recover         = FORCE

innodb_buffer_pool_size = 6700M
innodb_additional_mem_pool_size = 16M
innodb_log_buffer_size = 32M
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT

[mysqldump]
quick
quote-names
max_allowed_packet    = 16M
[isamchk]
key_buffer        = 1M
sort_buffer_size    = 1M
read_buffer        = 1M
write_buffer        = 1M
[myisamchk]
key_buffer        = 1M
sort_buffer_size    = 1M
read_buffer        = 1M
write_buffer        = 1M


Как я понял из чтения документации, после чего и писал конфиг файл, мне надо максимум памяти отвести под  innodb_buffer_pool_size. Что я и сделал под остальные структуры отводил по остаточному принципу, но при этом не забывая смотреть на соответствующие показатели базы.

Где у меня ошибка? Почему mysql свапится? Что я делаю не так? куда копать итд. Может у меня неправильный подход к расчетам?

Содержание
Сообщения в этом обсуждении
"Почему mysql вылезает в swap"
Отправлено universite , 09-Окт-10 18:36 

> Где у меня ошибка? Почему mysql свапится? Что я делаю не так?
> куда копать итд. Может у меня неправильный подход к расчетам?

Для начала запустите http://mysqltuner.pl/
он вам покажет узкие места в работающем Mysql-сервере

"Почему mysql вылезает в swap"
Отправлено small_linux_admin , 10-Окт-10 00:11 
>> Где у меня ошибка? Почему mysql свапится? Что я делаю не так?
>> куда копать итд. Может у меня неправильный подход к расчетам?
> Для начала запустите http://mysqltuner.pl/
> он вам покажет узкие места в работающем Mysql-сервере

Уже делал. И не только это. Спасибо за ответ. Он на 90 процентов решает все проблемы ( сам его всем советую).

"Почему mysql вылезает в swap"
Отправлено universite , 10-Окт-10 00:27 

> Уже делал. И не только это. Спасибо за ответ. Он на 90
> процентов решает все проблемы ( сам его всем советую).

так покажите вывод сюда.

"Почему mysql вылезает в swap"
Отправлено small_linux_admin , 10-Окт-10 13:08 
>>> Где у меня ошибка? Почему mysql свапится? Что я делаю не так?
>>> куда копать итд. Может у меня неправильный подход к расчетам?
>> Для начала запустите http://mysqltuner.pl/
>> он вам покажет узкие места в работающем Mysql-сервере
> Уже делал. И не только это. Спасибо за ответ. Он на 90
> процентов решает все проблемы ( сам его всем советую).

root@db:~# ./mysqltuner.pl

>>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering

[!!] Successfully authenticated with no password - SECURITY RISK!

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.38-Ubuntu_0ubuntu1.4-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 361M (Tables: 175)
[--] Data in InnoDB tables: 9G (Tables: 123)
[!!] Total fragmented tables: 4

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12h 18m 15s (4M q [110.595 qps], 454K conn, TX: 82B, RX: 1B)
[--] Reads / Writes: 56% / 44%
[--] Total buffers: 7.2G global + 8.5M per thread (300 max threads)
[!!] Maximum possible memory usage: 9.7G (124% of installed RAM)
[OK] Slow queries: 0% (7K/4M)
[OK] Highest usage of available connections: 66% (198/300)
[OK] Key buffer size / total MyISAM indexes: 128.0M/155.3M
[OK] Key buffer hit rate: 95.2% (11K cached / 535 reads)
[OK] Query cache efficiency: 36.4% (948K cached / 2M selects)
[!!] Query cache prunes per day: 38230
[OK] Sorts requiring temporary tables: 0% (5K temp sorts / 522K sorts)
[OK] Temporary tables created on disk: 3% (14K on disk / 488K total)
[OK] Thread cache hit rate: 98% (5K created / 454K connections)
[!!] Table cache hit rate: 0% (256 open / 75K opened)
[OK] Open file limit used: 0% (3/1K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
[!!] InnoDB data size / buffer pool: 9.1G/6.8G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 64M)
    table_cache (> 256)
    innodb_buffer_pool_size (>= 9G)

"Почему mysql вылезает в swap"
Отправлено universite , 10-Окт-10 16:42 
для начала, приподнять Key buffer size
потом что-то думать с размерами  InnoDB, попробовать сделать дамп, почистить мускул и заново залить

да и Open file limit подправить

"Почему mysql вылезает в swap"
Отправлено PavelR , 10-Окт-10 17:51 
Прокомментируй пожалуйста, на основании каких значений делаются твои выводы ?

Мое ИМХО говорит мне:

> для начала, приподнять Key buffer size

[OK] Key buffer size / total MyISAM indexes: 128.0M/155.3M
[OK] Key buffer hit rate: 95.2% (11K cached / 535 reads)

типа юзается хорошо, место есть?

> потом что-то думать с размерами  InnoDB, попробовать сделать дамп, почистить мускул
> и заново залить

а что, помогает ?

> да и Open file limit подправить

опять же, разве не ок всё ?

[OK] Open file limit used: 0% (3/1K)


Вот этот момент для меня спорный и не понятный, 36% вроде не мало:

[OK] Query cache efficiency: 36.4% (948K cached / 2M selects)
[!!] Query cache prunes per day: 38230

что такое 75К ?

[!!] Table cache hit rate: 0% (256 open / 75K opened)

"Почему mysql вылезает в swap"
Отправлено universite , 11-Окт-10 00:35 
> Прокомментируй пожалуйста, на основании каких значений делаются твои выводы ?
> Мое ИМХО говорит мне:

ок - это сферический конь в вакууме
по остальным вопросам гугл прекрасно разжевывает

"Почему mysql вылезает в swap"
Отправлено PavelR , 11-Окт-10 08:34 
>> Прокомментируй пожалуйста, на основании каких значений делаются твои выводы ?
>> Мое ИМХО говорит мне:
> ок - это сферический конь в вакууме
> по остальным вопросам гугл прекрасно разжевывает

да я собст-на, на цифры больше смотрю, чем на [OK]

"Почему mysql вылезает в swap"
Отправлено PavelR , 09-Окт-10 19:41 
> 1. Почему когда снимаю дамп с базы innodb резко падает скорость выполнения
> запросов? Как это лечить?

Так устроен мускул. Опять же, дамп - это доп нагрузка на сервер, на диски, на процессор, она вымывает кэши и т п - потому и падают скорости. Выход - дампить реплику, т.е. второй экземпляр БД иметь для целей бэкапа (второй сервер).

> 2. На сервере установлено 8 Гб памяти. После суток работы я обнаруживаю,
> что сервер mysql залез на 6гб в swap. Почему? Вроде нет
> ошибок в конфиге и память посчитана правильно под буфера и прочее.

Соединений к серверу сколько ?

лимит у вас достаточно высокий:
> max_connections        = 2000

а многое в расходе оперативки завязано на число коннектов.

6-7 Гб "сверху" это всего ~4Мб оперативки на коннект на ваши лимиты.

также есть и то, что на коннекты не завязано =)

---
у меня на сервере 8Г рамы, при innodb_buffer_pool_size = 6G сервер занимает в памяти 7Гб
т.е. 1Гб идет на прочие буфера, не связанные с иннодб.


"Почему mysql вылезает в swap"
Отправлено small_linux_admin , 10-Окт-10 13:08 
>[оверквотинг удален]
> потому и падают скорости. Выход - дампить реплику, т.е. второй экземпляр
> БД иметь для целей бэкапа (второй сервер).
>> 2. На сервере установлено 8 Гб памяти. После суток работы я обнаруживаю,
>> что сервер mysql залез на 6гб в swap. Почему? Вроде нет
>> ошибок в конфиге и память посчитана правильно под буфера и прочее.
> Соединений к серверу сколько ?
> лимит у вас достаточно высокий:
>> max_connections        = 2000
> а многое в расходе оперативки завязано на число коннектов.
> 6-7 Гб "сверху" это всего ~4Мб оперативки на коннект на ваши лимиты.

Уменьшил количество коннектов до 300 с 2000 - не помогло.


> также есть и то, что на коннекты не завязано =)
> ---
> у меня на сервере 8Г рамы, при innodb_buffer_pool_size = 6G сервер занимает
> в памяти 7Гб
> т.е. 1Гб идет на прочие буфера, не связанные с иннодб.