TvolodArhat109Смотрите средний размер времянок и их одновременное количество.
Не подскажете, где это посмотреть? Или имеются ввиду временные таблицы, которые создаёт пользователь? Они микроскопические, и процесс точно не висит в момент их создания.

тут имеются ввиду неявные временные таблицы, создание которых обусловлено обработкой запросов - group by, union и тд.


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

Вот что тут странного ? Запросов много, а mysql у вас один. Ресурсы ограничены не только числом ядер (половина из которых еще и HT), но и памятью и диском.

Разве не очевидно, что нужно просто уменьшить объем работы выполняемой в каждом запросе ?
explain покажет что именно делает mysql в этих запросах и как изменится этот объем по мере переписывания запроса.

tanglir>Ресурсы ограничены не только числом ядер (половина из которых еще и HT)
В стартпосте речь идёт о трёх-четырёх запросах на 8 ядрах
>но и памятью
ну хз какие там должны быть запросы, чтобы четырём 24 гектар не хватило... особенно если по одному они всего секунду работают
>и диском
ну разве что чтение подводит, это может быть

ну вот я и перечитываю первый пост :
1 запрос за 2 секунды,
но 4 запроса за 20 секунд.

Да, в параллельных вычислениях у компьютеров линейный рост производительности частенько не наблюдается.
Почему это вдруг стало странностью? Это обычное явление, если программист не продумал масштабирование специально.
Кеш-то на все ядра ограничен. Ширина шины обмена ограничена и тд и тп.

авторshingle
во-первых, сеонизаторы должны страдать.
и конечно же sql-база не место для обработки текстов.

авторЯ точно так же рассуждал, но вот состояние сервера в момент этой работы. Где тут видно, что чего-то не хватает?
и я не вижу.
однако, это не означает что на картинке все возможные показатели учтены.

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

авторselect no_cache
чтобы использовать эту конструкцию в здравом уме в приложении нужны серьезные причины.
я сильно сомневаюсь что они были.

Tvolodnetwindпропущено...

во-первых, сеонизаторы должны страдать.
и конечно же sql-база не место для обработки текстов.
Во-первых, это поиск дублирующихся текстов (или фото) в рамках одного сайта.
Во-вторых, там нет ни одного текстового поля. Это индексы.
netwindмного вы эти говносайтов склепать успеете в день?
У Вас неприятности в личной жизни? Я не понимаю, о чем эти эмоции и слова, которые не понятно к чему относятся?

Это такая всем доступная аналогия о недостижимости линейного масштабирования при конкуренции за ресурсы.
А неприятности у вас, если вы на это оскорбляетесь.
По сути-то что ?
И с чем связан такой серьезный подход к поиску подобных текстов ? Если не занимаетесь сеонизаторством - выкиньте это, перепишите на подсчет хешей от нормализированного текста.

netwindпропущено...

чтобы использовать эту конструкцию в здравом уме в приложении нужны серьезные причины. я сильно сомневаюсь что они были.Если идёт циклическая обработка некого массива данных, и совершенно понятно, что запрос с такими же параметрами никогда не будет повторён, нормальный человек поставит no_cache, не нормальный будет умничать...
Я высказываю личные обобщения исходя из опыта наблюдений за разными сайтами.
Хорошо, что это циклическая одноразовая обработка. Это и есть серьезные причины.
Значительно чаще люди не понимают зачем это нужно.

авторПодход не серьёзный, а нормальный. Если на сайте куча текстов и фотографий, публикуемых пользователями, то дубли и того и другого, а так же изменённые дубли, являются проблемой. Народ хочет видеть чистые сайты, а не говносайты. :)
В любом случае проблема проявляется на любом классе задач (у меня их много) которые связаны с обработкой больших массивов данных.

А кто эти дубли создает ? Да всех забаньте на этапе регистрации.
Если не пытаться искусственно и экспоненциально увеличивать число фактов в базе, не разбивать на шинглы, то и проблемы не будут экспоненциально расти.

Это применение нетипично для субд. Неудивительно что проблемы есть.
Во всех субд есть специальные типы полнотекстовых индексов, какие-то мало кому полезные функции типа soundex() и чтобы хоть как-то иметь с этим дело.

Может все данные в какой-нибудь движок типа sphinx выгружать ? их много.

TvolodnetwindА кто эти дубли создает ? Да всех забаньте на этапе регистрации.
Пользователи и создают. Умышленно и не умышленно. Есть, например, сайт объявлений. Человеку нужно забить своими текстами первую страницу раздела - он и постит их немного модифицированные.
То же самое с фотографиями. Есть сайт куда можно выгружать фото. Есть такое на сайте или нет - понять можно только достаточно сложными вычислениями. Здесь не обойтись хешем файла, если хотим искать не точное совпадение, а находить даже немного модифицированные (обрезанные, отресайзенные или отфотошопленные) фотографии.
Непосредственно при загрузке фото и текста есть только быстрая проверка на полное или почти полное совпадение. А чтобы определить похожесть с какой-то вероятностью, приходится на отдельном серваке по расписанию лопатить гигабайтные таблицы. Это работает хорошо. Есть только проблема в том, что подобных задач несколько, и хочется их параллельно выполнять.

Два сервака. Три сервака.

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

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

Не пробовал. Сильно сомневаюсь, что через soundex() можно определить похожесть именно текстов, а не "кошка - ложка".

Дак и нельзя. Это был пример инородной и практически бесполезной функции. Она есть, но никто ее не использует.

Будет время, побалуюсь. В любом случае к фото это не применимо.
А есть ещё задачи геолокации и взаимной привязки географических объектов, областей... А есть ещё куча других игрушек. И всё на той же машине. :)


netwindМожет все данные в какой-нибудь движок типа sphinx выгружать ? их много.
Ну, это решение понятно. Но придётся ещё данные туда-сюда перекачивать/конвертировать.
Но не хочется сразу капитулировать перед проблемой. По крайней мере пока она не понятна. Может всё-таки как-то мускул заставить работать.

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

Ну может быть в этой конфигурации негативные эффекты возникают из-за наличия NUMA или PAE, но скорее всего их нет.
У вас современный windows на какой-то массовой хостерской платформе ведь ?

TvolodnetwindКонечно, некоторые хипстеры, считают что сайт должен сам себя администрировать. Но тогда им не надо ожидать, что обычные базы будут хорошо работать в необычном применении.
По-вашему предел возможностей MySQL - это прайс-лист загрузить и гуэстбук замутить?
В чем необычность задач, я не очень понимаю. Не метафизически: "не предназначено, мол", а физически, т.е. имеются (объективно) следующие ограничения... и мы их можем увидеть/пощупать на приведённом примере там то и там то...

Дело не в пределе возможностей, а в том как обычно используются рсубд.
Используешь как все - гуглишь ответ на любую проблему. А в большинстве случаев они даже не возникают.
Хочешь необычного - ты один на один с проблемой.


netwindЗдесь нет никакой особенной проблемы. производительность не масштабируeтся линейно по числу ядер и это не новость.
Опять таки, это просто общее утверждение, пусть даже и верное, я же не знаю. Физика то в чём? Почему это так на данном конкретном приведённом мной весьма простом и измеримом примере.

Но вы с ними согласны ?
За конкретное это вам придется самолет и командировку оплачивать. Пивом не обойтись.

netwindЕсли план запроса предполагает генерирование большой временной таблицы из и так большого числа фактов, почему бы этим запросам выполняться быстро?
При параллельном выполнении нескольких запросов план меняется, относительно "сольного" выполнения? Вряд ли. Тогда почему ему исполняться на порядок медленнее?
Ещё раз

вот именно, еще раз повторюсь:
netwindесли вас лично вместо работы над алгоритмом каждые 15 минут заставлять отвлекаться бумагу в принтере менять, забывая каждый раз на чем вы остановились (аналогия с кешем внутри процессора), много вы эти говносайтов склепать успеете в день?


автор: ресурсов (процессорной мощности и оперативки), более чем достаточно. Другие не используются. Ну я же показал на картинках, что сервер вовсе не напрягается при выполнении этих трёх задач. Возможно, есть другие измерители, на которые нужно смотреть - тогда буду признателен за подсказку.
В моём идеальном мире запросы должны выполняться с той же скоростью, что и раньше пока не станут где-то друг с другом конкурировать (взаимные блокировки, чтение с диска, разделение процессорного времени и т.п.). А если нет конкуренции за ресурсы, то видимых причин снижать на порядок скорость выполнения я не вижу. Ведь если бы запросы шли не одновременно, а друг за другом (в теории по крайней мере), они бы выполнились за линейно возросшее время (в теории по крайней мере).

У вас задача поиска всего среди всего - то есть, интенсивный обмен с памятью, который осуществляется через кеш процессора. Вот этого ресурса при параллельной архитектуре и не хватает. И соседи постоянно забивают его другими данными.
Наверное, какой-то из профайлеров в состоянии снять метрики cache miss, но это собирать mysql специально надо.
Мне кажется это и так достаточно здравое объяснение.


netwindНу может быть в этой конфигурации негативные эффекты возникают из-за наличия NUMA или PAE, но скорее всего их нет.
Погуглил эти термины... NUMA точно нет, т.к. обычная стандартная машинка.
http://www.nix.ru/autocatalog/nix_computers/S5000B_S5365LNi_Core_i74790_16_SATA_RAID_190793.html

netwindУ вас современный windows на какой-то массовой хостерской платформе ведь ?
Не очень массовая. В антресоли над туалетом пылится. :)

Я бы все же убедился как-нибудь конкретнее. Формально в википедии написано что Haswell EP таки поддерживает NUMA.
но раз уж это "серийный" сервер из nix, наверное не стоит.

Tvolod,

я так думаю надо вот с этой штукой разобраться https://perf.wiki.kernel.org/index.php/Tutorial, в линуксе запустить mysqld, налить тестовых данных и как-то снять счетчики cache miss

автор It provides a list of events to measure micro-architectural events such as the number of cycles, instructions retired, L1 cache misses and so on

Не говоря о том, что линуксе может просто внезапно отпустить.

ну вот как-то вроде считает у меня, где 13357 - pid процесса mysqld :
автор perf stat -e L1-dcache-load,L1-dcache-load-misses,L1-dcache-stores,L1-dcache-store-misses -p 13357
^C
Performance counter stats for process id '13357':

1,109,824 L1-dcache-load
201,929 L1-dcache-load-misses # 18.19% of all L1-dcache hits
372,766 L1-dcache-stores
23,616 L1-dcache-store-misses

9.512196166 seconds time elapsed

шо бы это значило ответ где-то здесь http://www.intel.com/content/www/us/en/architecture-and-technology/64-ia-32-architectures-software-developer-vol-3b-part-2-manual.html

Кстати, после перезагрузки сервера эффект существенно уменьшился. Он по-прежнему есть, но стало в разы лучше. До этого менял параметры мускула и только рестартовал его.
Будем посмотреть, может быть это софтверный глюк. Кто его разберёт... :(
Может быть память была как-то занята другими таблицами и задачами. А при перезагрузке еще не успела.

Если у вас myisam, то попробуйте еще myisam_use_mmap=1.
Переход на innodb в данной ситуации может улучшить работу с данными в памяти, потому что данные точно залягут в пул innodb.

Ну и подумать над планами запросов всегда полезно.

TvolodnetwindЕсли у вас myisam, то попробуйте еще myisam_use_mmap=1.
Попробовал. Несколько дней покрутилось с этой опцией. Стало или хуже, или по крайней мере не лучше. Во всяком случае монитор ресурсов показывал постоянные обращения к диску в течение 1.5 суток, пока система всё-таки не закешировала большие таблицы.
netwindПереход на innodb в данной ситуации может улучшить работу с данными в памяти, потому что данные точно залягут в пул innodb.
А можете пояснить, за счёт чего может улучшить?


По определению. Так работает mmap - один из интерфейсов в ОС , про который неплохо было бы знать. Как и про кеш в процессоре.
вместо того чтобы считывать блоки данных через системные вызовы постоянно, эти системные вызовы возникают лишь когда блоки не попадают в уже считанную память область проецируемого файла.
Может и не ускорить запросы, которые использовали только лишь key_buffer.
Во всяком случае не должно быть хуже. Но вместе вот с этим :
Если у меня MyISAM через пару часов работы и так все системой (не MySQL!) полностью загружаются в оперативку. Просто проблемно этот опыт поставить. Нужно конвертировать таблицы, а их каждые сутки бекап подменяет...

..ситуация уже менее прозрачна. что там на что подменяется ?

Другой механизм используется в innodb, но он похож. Блоки просто считываются в буфер и при достаточно большом объеме памяти работа происходит только с буфером без обращения к ОС.
К несчастью, данные в innodb будут тупо больший размер в байтах занимать и прогноз дать нельзя. Я так подозреваю, все даже ухудшится.


Ну вот, например, простейший вариант:
[src sql]
select M2.ImageID, count(*) as Cnt


не читал, но заранее подход осуждаю.

Tvolod, да что ж такое? не будет того результата, который вам нужен.
попробуйте у mysql tmpdir на ramdrive вынести. Это может оказаться непросто для винды и возможно сожрет память навсегда ( не как в линуксе), но ускорить может.

Tvolodnetwindпопробуйте у mysql tmpdir на ramdrive вынести.
Зачем?

Ну просто попробуйте.
Например, в linux на практике накладные расходы на поддержку файлов в ramdrive меньше чем создание файлов на диске, даже если запись этих файлов буферизирована и идет без явного запроса на синхронизацию.

netwindTvolod, да что ж такое? не будет того результата, который вам нужен.
Ну мне б хотя б научно обоснованную трактовку, что происходит. Я б успокоился. :)
А то хрен знает что... Отсюда и решения предлагаются хрен знает какие. :)
Тут как раз все понятно. Перечитывайте.

TvolodВиноват. На картинке не видно, что не пишет она ни разу на диск в tmpdir
Ну раз
Created_tmp_disk_tables 44
Created_tmp_files 0
то это так!
так в документации написано же
Created_tmp_disk_tables - The number of internal on-disk temporary tables created by the server while executing statements.

может быть не пишет так интенсивно, чтобы это было заметно в мониторе ресурсов windows ?