Интересное поведение

12 3
Romka_Kharkov
На сайте с 08.04.2009
Offline
485
3106

День добрый,

iotop показывает интересные штуки (centos 5.x)


939 be/3 root 0.00 B/s 315.50 K/s 0.00 % 31.74 % [kjournald]

Понятно что так не постоянно, иногда показывает 99% i/o на несколько секунд, обычный хостинг сервер , слегка нагружен, apache / sql / cpanel все как обычно вроде бы....

Уже тюнил журнал и.т.п как-то все равно ...... , обычно процесс подвисает на несколько секунд совместно с mysql+php+apache процессами от клиентов, изучал запросы в базу - х3 , вроде как все нормально, конечно не все там проиндексировано, но не на столько объемы там сложные .... что бы умирать..... при таких аномалиях LA может добегать до 600 ... при этом еще несколько гиг рамы свободно (не всегда, иногда своп задействован), и при этом в /tmp создается довольно приличное количество временных таблиц, которые собственно я так понимаю и сносят i/o винтам напрочь.... растолкуйте, крутил ввертед параметры по разным рекомендациям, никак не вышло стабилизировать.

доп. информацию выдам, скажите какую :) (кроме рутового пароля :D )

Спасибо,

Есть около 15.000 ipv4 !!! (http://onyx.net.ua/price.php#ipv4) Качественный хостинг с 2005 года - лучшее клиентам! (http://onyx.net.ua/)
Evas EvaSystems
На сайте с 31.05.2012
Offline
88
#1

Временные таблички перенесите на tmpfs. Также проверьте логи вебсервера и самой Mysql, если их много и они большего размера - отсюда может быть нагрузка. А так, покажите весь My.cnf

Системный администратор Linux. Настройка, сопровождение и оптимизация серверов. Отзывы - searchengines.guru/ru/forum/1017473
Romka_Kharkov
На сайте с 08.04.2009
Offline
485
#2

my.cnf выглядит вот так:



[mysqld]
max_connections=600
safe-show-database
local-infile=0

## Cache
query_cache_size=32M
query_cache_limit=8M
thread_cache_size=128
thread_concurrency=4
table_cache=4096

## Buffers
join_buffer_size=4M
key_buffer_size=512M
key_cache_division_limit=70
myisam_sort_buffer_size=64M
key_buffer=16M
sort_buffer=3M

tmp_table_size=200M
max_heap_table_size=200M

interactive_timeout=600
wait_timeout=30
connect_timeout=20

innodb_buffer_pool_size=50M

###
max_join_size=5000000000
max_sort_length=512
key_buffer_size=512M
myisam_sort_buffer_size=32M
read_buffer_size=1M
read_rnd_buffer_size=1M

При этом логи веб сервера занимают около 500 MB , это те которые актуальны , естественно они ротируются.

MySQL ведет только error log, который весит около 2х метров :D

Вопрос про tmp таблицы актуален, машинка оснащена 32 гигами памяти.... почему временные таблицы выпадают на диск?

---------- Добавлено 06.02.2013 в 01:59 ----------

Перенес в shm временные таблицы, сервер конечно отпустило но понятно как бы почему, вопрос остается открытый, мне не ясно почему SQL пишет на диск если есть свободная память.... (с точки зрения временных таблиц). Винт у меня разбит на "/" и "/tmp", раньше писало в /tmp , отдельный раздел, меньше вопросов было, но со временем отрезанного (не LVM) под /tmp стало не хватать, перенесли в "/" этим собственно и добили. Сейчас в shm с меньшим i/o пишет конечно, но *****, почему не сразу в память? Какие буфера крутить ? куда? :)

Evas EvaSystems
На сайте с 31.05.2012
Offline
88
#3
почему не сразу в память?

Наиболее распространённые причины этого:

а) не хватает места отведенного директивами tmp_table_size и max_heap_table_size

б) в ваших основных таблицах имеются поля типа TEXT или BLOB, в результате все временные даные, которые являются результатом запросов на основные таблички пишутся на диск, а не в ОЗУ.

MySQL ведет только error log, который весит около 2х метров

Перепроверьте, не включен ли часом binlog или что-то иное...

Какие буфера крутить ? куда?

Судя по кол-ву памяти - размеры буферов у вас маловаты, да и вообще... Прежде чем что либо настраивать необходимо выяснить ряд моментов.

Прогоните Mysqltuner'ом, он подскажет основные моменты. Можете приложить сюда результат.

А так больше ничем помочь не могу, я не телепат :) Надо глядеть подробно...

Romka_Kharkov
На сайте с 08.04.2009
Offline
485
#4

Жестоки однако временные таблицы, смотрим график после переноса их на shm.

png diskstats_iops-day.png
Romka_Kharkov
На сайте с 08.04.2009
Offline
485
#5
Evas:
Наиболее распространённые причины этого:
а) не хватает места отведенного директивами tmp_table_size и max_heap_table_size

Тюнил и тут, причем от 100M до 3 GB и heap и table_size, пофик, результат тот же :D

Evas:

б) в ваших основных таблицах имеются поля типа TEXT или BLOB, в результате все временные даные, которые являются результатом запросов на основные таблички пишутся на диск, а не в ОЗУ.

А как же в таких случаях выживают хостеры? :) Таблички то ясное дело что не мои :D

Evas:

Перепроверьте, не включен ли часом binlog или что-то иное...

Нету.

Evas:

Судя по кол-ву памяти - размеры буферов у вас маловаты, да и вообще... Прежде чем что либо настраивать необходимо выяснить ряд моментов.

Какие моменты? Какие буферы маловаты на ваш взгляд?

Evas:

Прогоните Mysqltuner'ом, он подскажет основные моменты. Можете приложить сюда результат.

Треш это а не софт, гоняю им, результата нет , показывает почти одно и то же, по буферам я сейчас специально урезал, что бы не более 10GB памяти кушало, ибо в моменты когда оно все заедает с сервером работать весьма сложно, но обращая внимание на график который я привел выше, возможно станет полегче из за переноса таблиц в shm.

Evas EvaSystems
На сайте с 31.05.2012
Offline
88
#6
Треш это а не софт, гоняю им, результата нет , показывает почти одно и то же

Для большей ясности надо было написать "для начала, прогоните тюнером". Разумеется этого недостаточно, он показывает лишь базовые моменты.

Какие моменты? Какие буферы маловаты на ваш взгляд?

Все... и не только буферы. Я полагаю сайтов у вас много, баз и табличек в них тоже. Их объём, объём индексов, тип движка и т.п... Чтобы точно сказать что либо необходимо знать эту информацию.


Тюнил и тут, причем от 100M до 3 GB и heap и table_size, пофик, результат тот же

Как я написал выше, если не смотря на увеличение этих параметров таблички всё же попадают на диск, виной являются поля вышеперечисленных типов.

Для примера вазьмём высоконагруженный блог на вордпрес...

[!!] Temporary tables created on disk: 37% (8M on disk / 23M total)

Хотя пишет, что объём временных таблиц уже слишком высок (256 мб), разместил на tmpfs и забыл об этом.

Обычно 256 мб хватает с головой, если же нет - тюнер это сообщит.

А как же в таких случаях выживают хостеры? Таблички то ясное дело что не мои

Без грамотной оптимизации не как.

Повторюсь, приложите пожалуйста сюда результат тюнера, поглядим чего у вас там, а то гадать можно до бесконечности.

Romka_Kharkov
На сайте с 08.04.2009
Offline
485
#7

Да без проблем, что он только даст , не совсем понимаю:


-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 5G (Tables: 2512)
[--] Data in InnoDB tables: 29M (Tables: 58)
[--] Data in MEMORY tables: 0B (Tables: 12)
[!!] Total fragmented tables: 141

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8h 49m 32s (31M q [981.693 qps], 202K conn, TX: 109B, RX: 2B)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 796.0M global + 9.2M per thread (600 max threads)
[OK] Maximum possible memory usage: 6.2G (19% of installed RAM)
[OK] Slow queries: 0% (682/31M)
[OK] Highest usage of available connections: 24% (148/600)
[OK] Key buffer size / total MyISAM indexes: 512.0M/1.7G
[OK] Key buffer hit rate: 100.0% (2B cached / 237K reads)
[OK] Query cache efficiency: 29.0% (8M cached / 29M selects)
[!!] Query cache prunes per day: 46333585
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 475K sorts)
[!!] Joins performed without indexes: 6944
[!!] Temporary tables created on disk: 38% (237K on disk / 623K total)
[OK] Thread cache hit rate: 99% (148 created / 202K connections)
[OK] Table cache hit rate: 24% (3K open / 12K opened)
[OK] Open file limit used: 62% (5K/8K)
[OK] Table locks acquired immediately: 99% (23M immediate / 23M locks)
[OK] InnoDB data size / buffer pool: 29.3M/50.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query_cache_size (> 32M)
join_buffer_size (> 4.0M, or always use indexes with joins)
tmp_table_size (> 200M)
max_heap_table_size (> 200M)

Фрагментация - фигова, согласен, проведу optimize. еще?

Uptime не велик , но и не полчаса... С кешом игрался, можно чуток увеличить, но все равно значение в районе нескольких миллионов. Так как вижу NO_CACHE или как-то там запросы про процессам. Общий объем баз 5 GB, сайтов не много, но они посещаемые... Я полагаю для такого сервера 200 сайтов это не много :D :D :D Понимаю что "считать сайтами" не верно, но реально из них есть 5 которые по 50k+ uniq в сутки, остальные блекло смотрятся на их фоне, типа 1-5k uniq и даже меньше есть.... Рядом есть такой же сервер с практически такими же настройками но там иначе, тяжеловесов нет, но зато сайтов 1000+ чувствует себя в 100 раз лучше )))) полагаю что таки проблемы с запросами (базой).....

Еще думаю, что может совокупность факторов играет роль, например пишутся временные таблицы, тут же рядом может бекап запустится.... а следом уже комом набираются процессы php которые попросту не завершаются. и так по кругу пока есть свободный ресурс..... сейчас буферы немного увеличу по этой формуле:


key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections

Что бы "Maximum possible memory usage:" было около 20 GB, я же понимаю это нормальным, для 32 гиг памяти.. ? остальные 13 останутся под все остальное, memory_limit и прочее в php есть, т.е выедать под ноль не должно.

Посмотрим как после этого пару суток вести себя будет ..... возможно перенос в shm решает вопрос в корне...хотя вы говорите что что-то настроили бы иначе... а точнее "Все..." , жду плотных комментариев :D

Evas EvaSystems
На сайте с 31.05.2012
Offline
88
#8
Да без проблем, что он только даст , не совсем понимаю

Ну как что, я вижу основные проблемы, используемые движки, а также размер прочих нужных вещей.

key_buffer_size = 1,7G

tmp_table_size = 256M

max_heap_table_size = 256M

query_cache_size = 512M

query_cache_limit = 64M

sort_buffer = 32M

join_buffer_size = 64M

innodb_flush_method = O_DIRECT

innodb_flush_log_at_trx_commit = 0

Опять же это лишь моменты касающиеся базовой оптимизации.

Пропишите сиё дело, а завтра днём опять дадите результат тюнера.

хотя вы говорите что что-то настроили бы иначе... а точнее "Все...",

Ну как всё, практически. Для максимальной производительности одних лишь тюнеров недостаточно.

жду плотных комментариев

Смотрю хотите, чтобы всё сделали за вас. Наглость второе счастье, да? :)

Я направил вас в нужное русло, изучайте вопрос самостоятельно, если хотите в будущем не создавать подобных тем.

Если же, по какой-то причине у вас это не получается, наймите администратора.

Romka_Kharkov
На сайте с 08.04.2009
Offline
485
#9
Evas:
Ну как что, я вижу основные проблемы, используемые движки, а также размер прочих нужных вещей.

key_buffer_size = 1,7G

Согласен, его подбираем по размеру общему индексов, это значение так же уже было в фазе 2 и 3 GB, т.е с небольшим запасом.


tmp_table_size = 256M
max_heap_table_size = 256M

Сейчас стоит 200 M, что это дает? Кстати, установка этих переменных не влияет на размер создаваемых временных таблиц.....


sort_buffer = 32M

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_sort_buffer_size

Вот тут Пишут что: "On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values."

Другими словами ставить "что попало" не имеет смысла, так как буфер будет выделяться даже в том случае если он не будет использован целиком. Почему взято значение 32 M ? поясните мне пожалуйста. Я понимаю, что документация не носит в себе настроек для всех и всяк, но хотелось бы кроме сухого ответа в виде 32M услышать ответ на вопрос "почему именно 32?"


query_cache_size = 512M
query_cache_limit = 64M

Допустим, но влияет ли это на формирование нагрузки? ;)


join_buffer_size = 64M

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html

Интересная наводка: "It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it."

Т.е по факту рекомендовано в глобальных значениях держать "некий минимум", а для сессий с большими join применять другое значение. Почему вы выбираете 64M ?


innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0

Это для : "Data in InnoDB tables: 29M (Tables: 58)" ???? 😮

---------- Добавлено 06.02.2013 в 04:18 ----------

Evas:

Смотрю хотите, чтобы всё сделали за вас. Наглость второе счастье, да?

Сделали за меня что? Вы о чем? Комменнтарии это когда человек рассказывает почему стоит выбрать данное значение , а не говорит что это "базовая оптимизация" :) Базовая для чего и для кого ? :)

Evas:

Я направил вас в нужное русло, изучайте вопрос самостоятельно, если хотите в будущем не создавать подобных тем.

Я понял ваше настроение, спасибо что уделили время, приму ваши советы к сведению.

Evas:

Если же, по какой-то причине у вас это не получается, наймите администратора.

Обязательно подумаю на эту тему.

---------- Добавлено 06.02.2013 в 04:25 ----------

Кстати, при применении ваших настроек, результат mysqltuner выглядит вот так:


[!!] Maximum possible memory usage: 60.4G (192% of installed RAM)
[OK] Slow queries: 0% (0/573)
[OK] Highest usage of available connections: 0% (3/600)
[OK] Key buffer size / total MyISAM indexes: 2.0G/1.7G
[!!] Key buffer hit rate: 93.0% (40K cached / 2K reads)
[OK] Query cache efficiency: 48.9% (258 cached / 528 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 32 sorts)
[!!] Temporary tables created on disk: 40% (14 on disk / 35 total)
[OK] Thread cache hit rate: 85% (3 created / 20 connections)
[OK] Table cache hit rate: 87% (51 open / 58 opened)
[OK] Open file limit used: 1% (101/8K)
[OK] Table locks acquired immediately: 100% (326 immediate / 326 locks)
[OK] InnoDB data size / buffer pool: 29.3M/50.0M

Полагаю сему виной как раз join_buffer_size 64M ?

Evas EvaSystems
На сайте с 31.05.2012
Offline
88
#10

Большого запаса между Key buffer size и total MyISAM indexes быть не должно иначе будут проблемы с Key buffer hit rate.

По поводу размера выделенного под кешер запросов. Внимательно посмотрите на вывод тюнера. Если просит их увеличить, значит текущих значений недостаточно.

Допустим, но влияет ли это на формирование нагрузки?

Эти параметры влияют на размеры кеша запросов. Как думаете, если результат запроса будет взять из кеша, вместо того, чтобы выполнять его каждый раз, нагрузка будет меньше?

Это для : "Data in InnoDB tables: 29M (Tables: 58)" ????
буфер будет выделяться даже в том случае если он не будет использован целиком.

Т.е в будущее вы не смотрите? Как я понял у вас общаковый хостинг и на нём распологаются разного рода клиенты с разныи сайтами, а следовательно и разными базами и типами используемых в них движков. Начнут расти таблички с типом InnoDB без базовой оптимизации получите медленную работу.

Спрашиваете почему я выбрал те или иные значения? Трудно сказать, нравится мне эти числа 😂 А если серьёзно - подобные значения подбираются опытным путём (одинаковыми для всех они не будут). Судя по размеру баз и результата тюнера я предложил их увеличить.

кстати, при применении ваших настроек, результат mysqltuner выглядит вот так:

И дальше чего? сколько прошло с момента перезапуска мускл? 5-10 минут. Эти результаты ничего не значат, подождите хотябы несколько часов.

И да, я не помню, чтобы укзывал key_buffer равным 2GB

Полагаю сему виной как раз join_buffer_size 64M ?

Чему именно? Тому, что макс. используемый объём озу увеличился?

Нет, не только из за этого. Вы увеличили глобальный Key_buffer + другие буфферы, которые выделяются на каждый поток.

Ключевым словом тут будет максимальный объём, именно столько будет использоватся при одновременных 600 подключений к вашему мускл, сейчас же у вас их меньше десятка...

12 3

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий