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

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

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 пишет конечно, но *****, почему не сразу в память? Какие буфера крутить ? куда? :)

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

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

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

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

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

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

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

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

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

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

Нету.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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?"

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

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 ?

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

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

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

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

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

---------- Добавлено 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 ?

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

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

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

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

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

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

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

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

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

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