pupseg, насчет анализа лога, понимаю, но расскажет он не совсем мне, а расскажет клиентам что у них где-то нет индексов, а где-то их программист сделал какашку, я так понимаю :D Первоначальная задача оградить сервер от зависаний по желанию mysql ;)))) А уже конечно позже будем анализировать данные вместе с клиентами и их программистами если будет надо.. но пока рано. :)
Спасибо за рекомендации и напутствия, если у кого-то еще есть что сказать - я внимательно выслушаю.
pupseg: интересная тема, но для начала - выяснили - какой движек формирует #sql_XXX ? и какого они размера ? просто имейте ввиду - что если временно-формированные таблицы формируются большого размера (я встречал до 16гб!!!) то вы потеряете всю оперативную память, поэтому увлекаться сильно не надо.
Создаются файлы размером от 50 до 500 MB. А кто именно их создает .... не ясно.... Как кстати посмотреть от каких запросов эти временные таблицы?
---------- Добавлено 06.02.2013 в 13:26 ----------
netwind: Почему, кстати, они разные ? они не в идентичном raid ?
Кстати тоже это интересно, можт один из драйвов шабашит? Но как бы SMART молчит, все ок!
---------- Добавлено 06.02.2013 в 13:36 ----------
После 7 часов работы:
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| Qcache_free_blocks | 3774 |
| Qcache_free_memory | 1008126648 |
| Qcache_hits | 16367635 |
| Qcache_inserts | 4043140 |
| Qcache_lowmem_prunes | 1022268 |
| Qcache_not_cached | 159970 |
| Qcache_queries_in_cache | 61687 |
| Qcache_total_blocks | 153160 |
+-------------------------+------------+
[!!] Total fragmented tables: 34
[!!] Query cache prunes per day: 3,541,660
[!!] Joins performed without indexes: 2083
[!!] Temporary tables created on disk: 29% (77K on disk / 259K total)
Интересно откуда берутся фрагментированные таблицы, после optimize они начали появляться почти что моментально, при перезапуске sql последнем их было всего 2 (предлагает их пересоздать, так как они не могут быть оптимизированы), а сейчас уже 34..... Как часто надо делать optimize и от чего зависит фрагментация в какие моменты она появляется ?
Evas: Если не ошибаюсь, кеш этот лишь хранит, причём хранит результаты одинаковых запросов, очищается в том случае, если его размера не хватает. Чем больше памяти под него выделено, тем больше запросов там помещается и соответственно тем легче серверу. У вас я гляжу их ну очень много, т.е как бы мы не старались очистка будет иметь место. Но, чем больше запросов там задержится, тем лучше.
Запросов много , спору нет , но и сервер не P4 / 512 ram :)
В общем "Query cache prunes per day" это экстраполяция результата на сутки при текущем положении дел с Qcache_lowmem_prunes..... т.е значение весьма относительное..... Но при этом всплывают новые вводные. В моем первом посте, планируемое значение было 46 миллионов, сейчас это значение 1 миллион, соответственно понизился рейт Qcache_lowmem_prunes в работе кеша.
В общем судя по тому, что я вижу, 1 GB при интенсивности и "качестве" моих запросов хватает на 60-70 тысяч записей в кеше, попробую поднять до 2 GB как вы и писали, посмотрим, возможно 120-140 тысяч записей будут давать меньший рейт Qcache_lowmem_prunes.
// Небольшой промах в данных, при 80 MB свободного кеша, в кеше находится ~110k записей. Ниже 90-70 мб не падает, уже появляются Qcache_lowmem_prunes и Qcache_queries_in_cache не ростет , видимо еще резерв какой-то :D 10% :)
При этом всем возник еще вот такой вот момент интересный, если в 900 MB влезло 110k записей, это получается около 8 килобайт на запрос, среднее тут конечно не совсем уместно :D но все равно значение query_cache_limit=64MB выглядит монстром по сравнению с 8 килобайтами :) что-то мне подсказывает, что даже 1 MB для этого лимита - много :D
[OK] Maximum possible memory usage: 23.4G (74% of installed RAM)
Думаю 75% вполне нормально... рекомендованное значение 80 (где-то в доках или на очередном сайте с вариацией настройки)
Поднял пока до :
| query_cache_size | 1073741824 |
посмотрю через часок на количество prune.... замеряю среднее в секунду... станет понятно , влияет или не влияет..
---------- Добавлено 06.02.2013 в 05:51 ----------
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 10 |
| Qcache_free_memory | 726589992 |
| Qcache_hits | 87370 |
| Qcache_inserts | 63245 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 580 |
| Qcache_queries_in_cache | 61096 |
| Qcache_total_blocks | 122654 |
+-------------------------+-----------+
Это после 5 минут работы, от 1Г осталось ~700 метров, думаю что минут через 15-20 пойдут четко Qcache_lowmem_prunes, возможно следует "меньше хранить" (в плане времени).. ?
/// Я только что перестал полагать, что max connections достигает 500 эффективно, ведь по сути это могут быть и WAIT сессии которые в силу нагрузки fs висят длительное время.....
/// В общем сутки - двое посмотрю в этом положении. Qcache_free_memory осталось 550 mb :) Верно ли я понимаю, что значение надо выверять таким образом, что бы не достигалось состояние окончание памяти в кеше и при этом уже начинали удаляться те запросы, которые должны выпасть из кеша, освобождая в нем память?
//// Так же хочу понять, достаточно ли будет собрать файл всех запросов за какой-то период времени, найти в нем все JOIN и следом просмотреть в каких из этих таблиц нет индексов?
//// Для того что бы свести к минимуму: "Joins performed without indexes"
//// Память в кеше все еще стремится к нулю.... ~350MB осталось ;) (Должна ли она вообще высвобождаться там? Или принцип то кеша в сборе информации в памяти.... как бы... просто уже получается около 60k запросов в кеш помещено, должно быть разумное количество перед тем как они начнут повторяться? :) ) при этом Qcache_not_cached ~1k. Т.е процент довольно не плох, попаданий в кеш уже ~250k.
Но я таки полагаю, судя по графикам, что основной проблемой была запись tmp таблиц в тот же раздел диска куда и вся нагрузка сваливается... По крайней мере понимаю откуда могли браться i/o wa, а как следствие и тупо торможение обычных процессов под которые база вполне оптимизирована...... Есть здравый смысл в этом?
// Спад на графиках после перехода tmp с "/" в shm.
Evas: Нет, 300 мб это не излишек. Проверяйте опытным путём, только не спешите запускать тюнер, дайте пройти времени после применения настроек, хотябы несколько часов.
Это я прекрасно понимаю.
Evas:
Те запросы, в которых идёт NO_CACHE всегда пройдут мимо кеша, но проблема у вас не в этом, а в том, что туда не помещаются запросы, которые как раз на этот кеш расчитывают.
Об этом нам и говорит отличное от нуля значение строки Query cache prunes per day.
Вы знаете, я например не могу себе представить ситуацию в которой 100% запросов будут кешированы, это просто не реально !!!! По этому отличная от нуля query prunes это нормально !!!! или я не прав? Как и в какой кеш можно положить все запросы которые прошли через SQL сервер например за 10 дней, да у вас и 10000 GB памяти не хватит я полагаю :) Это что бы пруны = 0 :) Т.е все запросы прошедшие сквозь SQL были закешированы и ни один из них не был выкинут из кеша .......
Возможно конечно значение в 18 мильенов это перебор, так это другой разговор, получается, что кеш очень часто очищается.... и ваши праметры обретают какой-то осознанный смысл, попробую увеличить размер кеша.
Evas:
Кроме выпадов в свою сторону я ничего не увидел, елси вы всё знаете, почему же у вас возникают проблемы подобного рода?
Выпады не выпады, понимаете ли, я на форуме как и вы не первый день, вижу многое и разное, по этому если вы не разобрались что я хостер и предлагаете мне нанимать администратора, это я тоже могу считать как выпад с вашей стороны, но я молчу :D Ровно как и советы из серии "мне нравятся эти цифры", я привык к конструктивному диалогу на повышенных тонах, именно в такой атмосфере и принимаются наиболее верные решения, нужны споры, обсуждения, дебаты, как хотите это называйте. Если вы говорите, надо 32 мегабайта, будьте готовы пояснить почему, в противном случае вы с первого сообщения флудите в теме.
Общаться или нет со мной - ваше личное дело. Мы свободные люди. Принуждать вас общаться с собой )))) не буду :)))
---------- Добавлено 06.02.2013 в 05:01 ----------
Evas: P.S. - на будущее, чтобы не возникало подобных ситуаций, изначально максимально описывайте ситуацию. Человек со стороны не может знать всех аспектов работы вашего проекта.
Это ваше "будущее" прямо вообще максимализм какой-то :) Что там в будущем интересного то ? Старость? :) А специально из будушего в ваше прошлое, я написал в первом посте, что предоставлю любую информацию которая вам будет требоваться для анализа за исключением рутового пароля :D :D :D Если читать внимательно настоящее , то можно понять , что именно оно и формирует будущее.
---------- Добавлено 06.02.2013 в 05:05 ----------
И вот опять же "Ставят max connections как попало"..... а вы предлагаете не обращать внимание на то, что при максимальной нагрузке на SQL будет скушано 200% доступной памяти? (Не ясно что хуже.... :D ) Фигня получается, вы не в будущее смотрите а вообще не ясно куда... Допустим, что у меня действительно 10 коннектов это потолок, и при ваших настройках у меня 600 мегабайт уйдет на их обслуживание, разве вы считаете верным оставлять возможность использовать 60 GB ? Я лично считаю это непрофессиональным подходом.
// Я вижу для людей форум с двух сторон, одна сторона для тех кто хочет зайти и постебаться, а вторая для тех кто хочет обменяться информацией, т.е получить её или передать кому-то, так вот в моем случае первая сторона закончилась лет в 20 наверное, хотя период "постебаться" бесспорно был. Не воспринимайте сказанное на личный счет пока это не адресовано вам лично, а так же определитесь, с какой стороны вы видите форумы.... Я например готов делиться опытом что и делаю, а вы говорите что не хотите общаться, видимо по тому, что обмен информацией не интересен?
spbrf, обращайтесь, настроим, подскажем.
pupseg, насчет анализа лога, понимаю, но расскажет он не совсем мне, а расскажет клиентам что у них где-то нет индексов, а где-то их программист сделал какашку, я так понимаю :D Первоначальная задача оградить сервер от зависаний по желанию mysql ;)))) А уже конечно позже будем анализировать данные вместе с клиентами и их программистами если будет надо.. но пока рано. :)
Спасибо за рекомендации и напутствия, если у кого-то еще есть что сказать - я внимательно выслушаю.
Все увидел,СПАСИБО!
Не за что, будут вопросы - обращайтесь.
Всем остальным напоминаем, о том что:
Подключившись к услугам в период с 01.02.2013 по 28.02.2013 каждый клиент получает 30% скидки на услуги Виртуального Хостинга а так же Вип Хостинга.
Скидка является постоянной !!!!
интересная тема, но для начала - выяснили - какой движек формирует #sql_XXX ? и какого они размера ? просто имейте ввиду - что если временно-формированные таблицы формируются большого размера (я встречал до 16гб!!!) то вы потеряете всю оперативную память, поэтому увлекаться сильно не надо.
Создаются файлы размером от 50 до 500 MB. А кто именно их создает .... не ясно.... Как кстати посмотреть от каких запросов эти временные таблицы?
---------- Добавлено 06.02.2013 в 13:26 ----------
Почему, кстати, они разные ? они не в идентичном raid ?
Кстати тоже это интересно, можт один из драйвов шабашит? Но как бы SMART молчит, все ок!
---------- Добавлено 06.02.2013 в 13:36 ----------
После 7 часов работы:
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| Qcache_free_blocks | 3774 |
| Qcache_free_memory | 1008126648 |
| Qcache_hits | 16367635 |
| Qcache_inserts | 4043140 |
| Qcache_lowmem_prunes | 1022268 |
| Qcache_not_cached | 159970 |
| Qcache_queries_in_cache | 61687 |
| Qcache_total_blocks | 153160 |
+-------------------------+------------+
[!!] Total fragmented tables: 34
[!!] Query cache prunes per day: 3,541,660
[!!] Joins performed without indexes: 2083
[!!] Temporary tables created on disk: 29% (77K on disk / 259K total)
Интересно откуда берутся фрагментированные таблицы, после optimize они начали появляться почти что моментально, при перезапуске sql последнем их было всего 2 (предлагает их пересоздать, так как они не могут быть оптимизированы), а сейчас уже 34..... Как часто надо делать optimize и от чего зависит фрагментация в какие моменты она появляется ?
Если не ошибаюсь, кеш этот лишь хранит, причём хранит результаты одинаковых запросов, очищается в том случае, если его размера не хватает.
Чем больше памяти под него выделено, тем больше запросов там помещается и соответственно тем легче серверу.
У вас я гляжу их ну очень много, т.е как бы мы не старались очистка будет иметь место. Но, чем больше запросов там задержится, тем лучше.
Запросов много , спору нет , но и сервер не P4 / 512 ram :)
В общем "Query cache prunes per day" это экстраполяция результата на сутки при текущем положении дел с Qcache_lowmem_prunes..... т.е значение весьма относительное..... Но при этом всплывают новые вводные. В моем первом посте, планируемое значение было 46 миллионов, сейчас это значение 1 миллион, соответственно понизился рейт Qcache_lowmem_prunes в работе кеша.
В общем судя по тому, что я вижу, 1 GB при интенсивности и "качестве" моих запросов хватает на 60-70 тысяч записей в кеше, попробую поднять до 2 GB как вы и писали, посмотрим, возможно 120-140 тысяч записей будут давать меньший рейт Qcache_lowmem_prunes.
// Небольшой промах в данных, при 80 MB свободного кеша, в кеше находится ~110k записей. Ниже 90-70 мб не падает, уже появляются Qcache_lowmem_prunes и Qcache_queries_in_cache не ростет , видимо еще резерв какой-то :D 10% :)
При этом всем возник еще вот такой вот момент интересный, если в 900 MB влезло 110k записей, это получается около 8 килобайт на запрос, среднее тут конечно не совсем уместно :D но все равно значение query_cache_limit=64MB выглядит монстром по сравнению с 8 килобайтами :) что-то мне подсказывает, что даже 1 MB для этого лимита - много :D
Спасибо.
Пока что привел буферы к такому положению дел:
[OK] Maximum possible memory usage: 23.4G (74% of installed RAM)
Думаю 75% вполне нормально... рекомендованное значение 80 (где-то в доках или на очередном сайте с вариацией настройки)
Поднял пока до :
| query_cache_size | 1073741824 |
посмотрю через часок на количество prune.... замеряю среднее в секунду... станет понятно , влияет или не влияет..
---------- Добавлено 06.02.2013 в 05:51 ----------
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 10 |
| Qcache_free_memory | 726589992 |
| Qcache_hits | 87370 |
| Qcache_inserts | 63245 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 580 |
| Qcache_queries_in_cache | 61096 |
| Qcache_total_blocks | 122654 |
+-------------------------+-----------+
Это после 5 минут работы, от 1Г осталось ~700 метров, думаю что минут через 15-20 пойдут четко Qcache_lowmem_prunes, возможно следует "меньше хранить" (в плане времени).. ?
/// Я только что перестал полагать, что max connections достигает 500 эффективно, ведь по сути это могут быть и WAIT сессии которые в силу нагрузки fs висят длительное время.....
/// В общем сутки - двое посмотрю в этом положении. Qcache_free_memory осталось 550 mb :) Верно ли я понимаю, что значение надо выверять таким образом, что бы не достигалось состояние окончание памяти в кеше и при этом уже начинали удаляться те запросы, которые должны выпасть из кеша, освобождая в нем память?
//// Так же хочу понять, достаточно ли будет собрать файл всех запросов за какой-то период времени, найти в нем все JOIN и следом просмотреть в каких из этих таблиц нет индексов?
//// Для того что бы свести к минимуму: "Joins performed without indexes"
//// Память в кеше все еще стремится к нулю.... ~350MB осталось ;) (Должна ли она вообще высвобождаться там? Или принцип то кеша в сборе информации в памяти.... как бы... просто уже получается около 60k запросов в кеш помещено, должно быть разумное количество перед тем как они начнут повторяться? :) ) при этом Qcache_not_cached ~1k. Т.е процент довольно не плох, попаданий в кеш уже ~250k.
Не влез 1 график :)
Но я таки полагаю, судя по графикам, что основной проблемой была запись tmp таблиц в тот же раздел диска куда и вся нагрузка сваливается... По крайней мере понимаю откуда могли браться i/o wa, а как следствие и тупо торможение обычных процессов под которые база вполне оптимизирована...... Есть здравый смысл в этом?
// Спад на графиках после перехода tmp с "/" в shm.
[--] Up for: 46m 23s (1M q [393.487 qps], 12K conn, TX: 12B, RX: 90M)
[!!] Query cache prunes per day: 6026764
Вот это действительно интересно..... 6 mln за 46 минут.... :(
При предложенных вами настройках, увеличивать еще кеш для запросов?
| query_cache_limit | 67108864 |
| query_cache_size | 536870912 |
Нет, 300 мб это не излишек. Проверяйте опытным путём, только не спешите запускать тюнер, дайте пройти времени после применения настроек, хотябы несколько часов.
Это я прекрасно понимаю.
Те запросы, в которых идёт NO_CACHE всегда пройдут мимо кеша, но проблема у вас не в этом, а в том, что туда не помещаются запросы, которые как раз на этот кеш расчитывают.
Об этом нам и говорит отличное от нуля значение строки Query cache prunes per day.
Вы знаете, я например не могу себе представить ситуацию в которой 100% запросов будут кешированы, это просто не реально !!!! По этому отличная от нуля query prunes это нормально !!!! или я не прав? Как и в какой кеш можно положить все запросы которые прошли через SQL сервер например за 10 дней, да у вас и 10000 GB памяти не хватит я полагаю :) Это что бы пруны = 0 :) Т.е все запросы прошедшие сквозь SQL были закешированы и ни один из них не был выкинут из кеша .......
Возможно конечно значение в 18 мильенов это перебор, так это другой разговор, получается, что кеш очень часто очищается.... и ваши праметры обретают какой-то осознанный смысл, попробую увеличить размер кеша.
Кроме выпадов в свою сторону я ничего не увидел, елси вы всё знаете, почему же у вас возникают проблемы подобного рода?
Выпады не выпады, понимаете ли, я на форуме как и вы не первый день, вижу многое и разное, по этому если вы не разобрались что я хостер и предлагаете мне нанимать администратора, это я тоже могу считать как выпад с вашей стороны, но я молчу :D Ровно как и советы из серии "мне нравятся эти цифры", я привык к конструктивному диалогу на повышенных тонах, именно в такой атмосфере и принимаются наиболее верные решения, нужны споры, обсуждения, дебаты, как хотите это называйте. Если вы говорите, надо 32 мегабайта, будьте готовы пояснить почему, в противном случае вы с первого сообщения флудите в теме.
Общаться или нет со мной - ваше личное дело. Мы свободные люди. Принуждать вас общаться с собой )))) не буду :)))
---------- Добавлено 06.02.2013 в 05:01 ----------
P.S. - на будущее, чтобы не возникало подобных ситуаций, изначально максимально описывайте ситуацию. Человек со стороны не может знать всех аспектов работы вашего проекта.
Это ваше "будущее" прямо вообще максимализм какой-то :) Что там в будущем интересного то ? Старость? :) А специально из будушего в ваше прошлое, я написал в первом посте, что предоставлю любую информацию которая вам будет требоваться для анализа за исключением рутового пароля :D :D :D Если читать внимательно настоящее , то можно понять , что именно оно и формирует будущее.
---------- Добавлено 06.02.2013 в 05:05 ----------
И вот опять же "Ставят max connections как попало"..... а вы предлагаете не обращать внимание на то, что при максимальной нагрузке на SQL будет скушано 200% доступной памяти? (Не ясно что хуже.... :D ) Фигня получается, вы не в будущее смотрите а вообще не ясно куда... Допустим, что у меня действительно 10 коннектов это потолок, и при ваших настройках у меня 600 мегабайт уйдет на их обслуживание, разве вы считаете верным оставлять возможность использовать 60 GB ? Я лично считаю это непрофессиональным подходом.
// Я вижу для людей форум с двух сторон, одна сторона для тех кто хочет зайти и постебаться, а вторая для тех кто хочет обменяться информацией, т.е получить её или передать кому-то, так вот в моем случае первая сторона закончилась лет в 20 наверное, хотя период "постебаться" бесспорно был. Не воспринимайте сказанное на личный счет пока это не адресовано вам лично, а так же определитесь, с какой стороны вы видите форумы.... Я например готов делиться опытом что и делаю, а вы говорите что не хотите общаться, видимо по тому, что обмен информацией не интересен?