Подскажите, гуру, пожалуйста, не сталкивался ли кто-нибудь с таким странным явлением. MySQL начинает сильно тормозить при одновременном выполнении нескольких запросов, при чём чем больше потоков одновременно выполняют выборки данных, тем сильнее тормозят запросы.
Это было бы естественно, если бы было обращение к одним и тем же таблицам, или был бы дефицит ресурсов. Но у меня идут циклические запросы к разным таблицам и даже к разным базам данных. На сервере 8 ядер и 24 гига памяти, Win2008R2. При этом когда в единственном потоке запрос исполняется за 1-2 секунды, то при одновременном выполнении трех-четырёх потоков время выполнения тех же запросов вырастает до 20-30 и более секунд. Запросы висят в статусе Copying to tmp table.
Что бы это значило?

Спасибо за ответы!
tanglirсоздают временные таблицы на диске
а диск-то у вас, в отличие от ядер процессора, наверняка один...
Нет, если бы была работа с диском, то статус был бы: Copying to tmp table on disk
http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html
А так идёт работа с памятью.
Кроме того, если посмотреть show status, то видим:
авторCreated_tmp_disk_tables 0
Created_tmp_files 0
Created_tmp_tables 0
Arhat109Смотрите средний размер времянок и их одновременное количество.
Не подскажете, где это посмотреть? Или имеются ввиду временные таблицы, которые создаёт пользователь? Они микроскопические, и процесс точно не висит в момент их создания.
Arhat109Устанавливаете 2(две!) переменных в это или большее значение: tmp_table_size = max_heap_table_size = значение. Наименования переменных лучше уточнить в доках. Но они обе и вместе должны иметь это количество памяти. Иначе будет выделено по меньшей величине.
У меня сейчас такие значения:
авторmax_connections=10
tmp_table_size=16000M
max_heap_table_size=16000M
key_buffer_size=22000M
read_buffer_size=1024M
read_rnd_buffer_size=512M
sort_buffer_size=512M
join_buffer_size=16M
default-storage-engine=MYISAM
Не помогает. Кстати, при этом по монитору ресурсов использование памяти MySQL при этом 4-5 гигов. Редко до 6 увеличивается. Диски практически не работают на чтение, т.к. файлы таблиц закешировал Windows и вся работа идёт через системный кеш.

tanglirTvolodНа сервере 8 ядермусклю точно все 8 разрешено использовать? что-то ничего другого не придумывается.
А каким параметром это регулируется?
Судя по всему используется ровно столько ядер, сколько рабочих потоков MySQL в данный момент обслуживает. Уже не первый день экспериментирую - всё четко, запускаю новый скрипт с запросами - подключается новое ядро. правда не на 100% занято. На 80 где-то. Вот такая картина при трёх скриптах:
ScareCrowexplain?
Explain ничего не даст, ибо если запускать запрос один, или серию таких запросов в один поток, то всё в порядке. Время выполнения совершенно приемлемое (1-2 секунды), план нормальный (по индексам), на сколько может быть нормальный план по таблицам о 30-40 миллионов записей.
Проблема именно с тем, что при увеличении количества потоков время выполнения каждого запроса экспоненциально (от количества потоков) увеличивается.
Если интересно, вот примерно такие запросы идут:

Поверите, что Explain ничего не даст? :)
netwindВот что тут странного ? Запросов много, а mysql у вас один. Ресурсы ограничены не только числом ядер (половина из которых еще и HT), но и памятью и диском.
Я точно так же рассуждал, но вот состояние сервера в момент этой работы. Где тут видно, что чего-то не хватает?

netwindКеш-то на все ядра ограничен. Ширина шины обмена ограничена и тд и тп.
Имеется ввиду кеш процессора или кеш в оперативке?
Как видно по картинкам оперативки на всё про всё прекрасно хватает.
Про шины обмена с памятью тоже думал, но... там же другие порядки скоростей, нежели скорость работы дисков. По идее, если все необходимые данные уже загружены в память, то дальше должен лопатить их достаточно быстро, иначе зачем 8 ядер в процессоре, если скорости обмена данных хватает только на загрузку 1-2?
Так же не понятно экспоненциальное, а не линейное падение производительности. Если ты за 1-2 секунды обрабатываешь 1 запрос, то при трех одновременных (если подгрузка данных и их обработка идёт последовательно) должно быть 3-6 секунд.

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

netwindА неприятности у вас, если вы на это оскорбляетесь.
Я просто не понимаю, почему в технические вопросы нужно приплетать своё отношение к якобы (потому что Вы меня не знаете) автору и его якобы занятиям.
Собственно, да, описанная в топике проблема для меня неприятность :), ибо потрачена куча времени на поиск решения.
netwindИ с чем связан такой серьезный подход к поиску подобных текстов ? Если не занимаетесь сеонизаторством - выкиньте это, перепишите на подсчет хешей от нормализированного текста.
Я не очень понял, при чем тут сеонизаторство. Шинглы же и есть хеши от кусков нормализованного текста, как я это понимаю. Или, кстати, нормализованных и приведённых к шинглам фото.
Подход не серьёзный, а нормальный. Если на сайте куча текстов и фотографий, публикуемых пользователями, то дубли и того и другого, а так же изменённые дубли, являются проблемой. Народ хочет видеть чистые сайты, а не говносайты. :)
В любом случае проблема проявляется на любом классе задач (у меня их много) которые связаны с обработкой больших массивов данных.
netwindХорошо, что это циклическая одноразовая обработка. Это и есть серьезные причины. Значительно чаще люди не понимают зачем это нужно.
Спасибо что приняли аргумент! Извините за резкость - вынудили. :)

netwindА кто эти дубли создает ? Да всех забаньте на этапе регистрации.
Пользователи и создают. Умышленно и не умышленно. Есть, например, сайт объявлений. Человеку нужно забить своими текстами первую страницу раздела - он и постит их немного модифицированные.
То же самое с фотографиями. Есть сайт куда можно выгружать фото. Есть такое на сайте или нет - понять можно только достаточно сложными вычислениями. Здесь не обойтись хешем файла, если хотим искать не точное совпадение, а находить даже немного модифицированные (обрезанные, отресайзенные или отфотошопленные) фотографии.
Непосредственно при загрузке фото и текста есть только быстрая проверка на полное или почти полное совпадение. А чтобы определить похожесть с какой-то вероятностью, приходится на отдельном серваке по расписанию лопатить гигабайтные таблицы. Это работает хорошо. Есть только проблема в том, что подобных задач несколько, и хочется их параллельно выполнять.
netwindВо всех субд есть специальные типы полнотекстовых индексов, какие-то мало кому полезные функции типа soundex() и чтобы хоть как-то иметь с этим дело.
Не пробовал. Сильно сомневаюсь, что через soundex() можно определить похожесть именно текстов, а не "кошка - ложка". Будет время, побалуюсь. В любом случае к фото это не применимо.
А есть ещё задачи геолокации и взаимной привязки географических объектов, областей... А есть ещё куча других игрушек. И всё на той же машине. :)
netwindМожет все данные в какой-нибудь движок типа sphinx выгружать ? их много.
Ну, это решение понятно. Но придётся ещё данные туда-сюда перекачивать/конвертировать.
Но не хочется сразу капитулировать перед проблемой. По крайней мере пока она не понятна. Может всё-таки как-то мускул заставить работать.

netwindКонечно, некоторые хипстеры, считают что сайт должен сам себя администрировать. Но тогда им не надо ожидать, что обычные базы будут хорошо работать в необычном применении.
По-вашему предел возможностей MySQL - это прайс-лист загрузить и гуэстбук замутить?
В чем необычность задач, я не очень понимаю. Не метафизически: "не предназначено, мол", а физически, т.е. имеются (объективно) следующие ограничения... и мы их можем увидеть/пощупать на приведённом примере там то и там то...
netwindЗдесь нет никакой особенной проблемы. производительность не масштабируeтся линейно по числу ядер и это не новость.
Опять таки, это просто общее утверждение, пусть даже и верное, я же не знаю. Физика то в чём? Почему это так на данном конкретном приведённом мной весьма простом и измеримом примере.
netwindЕсли план запроса предполагает генерирование большой временной таблицы из и так большого числа фактов, почему бы этим запросам выполняться быстро?
При параллельном выполнении нескольких запросов план меняется, относительно "сольного" выполнения? Вряд ли. Тогда почему ему исполняться на порядок медленнее?
Ещё раз: ресурсов (процессорной мощности и оперативки), более чем достаточно. Другие не используются. Ну я же показал на картинках, что сервер вовсе не напрягается при выполнении этих трёх задач. Возможно, есть другие измерители, на которые нужно смотреть - тогда буду признателен за подсказку.
В моём идеальном мире запросы должны выполняться с той же скоростью, что и раньше пока не станут где-то друг с другом конкурировать (взаимные блокировки, чтение с диска, разделение процессорного времени и т.п.). А если нет конкуренции за ресурсы, то видимых причин снижать на порядок скорость выполнения я не вижу. Ведь если бы запросы шли не одновременно, а друг за другом (в теории по крайней мере), они бы выполнились за линейно возросшее время (в теории по крайней мере).
netwindМожно улучшить ситуацию если разобраться с этими запросами, но вообще-то ничего странного не происходит.
Да вопрос то не в конкретных запросах, а в том что происходит при параллельных вычислениях и почему. С конкретными запросами можно разобраться, хотя особых проблем в них я не вижу. Какой-то оптимизацией какие-то проценты можно выиграть, но в целом всё равно поиск по многомиллионным таблицам. Правда по индексам.
Я упёрся в эту проблему потому что мне удобнее её решить на уровне сервера (если получится), чем переписывать логику. Тем более что явление проявляется на любых циклических тяжёлых запросах, а они на этой машинке всегда будут тяжёлыми и циклическими – для того и покупалась.
netwindНу может быть в этой конфигурации негативные эффекты возникают из-за наличия NUMA или PAE, но скорее всего их нет.
Погуглил эти термины... NUMA точно нет, т.к. обычная стандартная машинка.
http://www.nix.ru/autocatalog/nix_computers/S5000B_S5365LNi_Core_i74790_16_SATA_RAID_190793.html
netwindУ вас современный windows на какой-то массовой хостерской платформе ведь ?
Не очень массовая. В антресоли над туалетом пылится. :)
Серверная Windows 2008R2 64 бит, родная, не ломанная. MySQL 5.5.23.

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