Почему mysql запрашивает, это требует копирования к tmp таблице, случайным образом иногда используйте 100% ЦП в течение нескольких секунд?
Что Вы обычно хотите произойти, вот то, что все Ваши операции записи всегда переходят к тому же (ведущее устройство), и Вы используете некоторую форму репликации для синхронизации новых данных от ведущего устройства к ведомым устройствам. Можно затем использовать подсистему балансировки нагрузки для распределения запросов чтения среди различных доступных физических серверов с пониманием, что для определенных вещей эти серверы могли бы немного устареть. Подсистема балансировки нагрузки должна также быть достаточно умной, чтобы знать, что определенным видам запросов нужно к выполненному от главного сервера, так, чтобы, например, когда я добавляю это сообщение к Переполнению стека, я не ожидал репликации для наверстывания для наблюдения моего нового сообщения, если я заканчиваю загрузка, сбалансированная к вторичному серверу. Короче говоря, приложение должно быть записано для знания об этом также. Вы не можете только включить опцию в базе данных и иметь все это, просто работают.
Я думаю, ваша проблема не столько в tmp_table_size, сколько в том, чтобы ваш "уродливый" запрос (набор результатов) кэшировался через query_cache. Если ваш запрос имеет тип SELECT, используйте SQL_NO_CACHE. При кэшировании он обслуживается быстро, но может перегружать кеш, и у mysql были известные проблемы с внутренней реорганизацией кеша. Кроме того, проверьте свой запрос с помощью EXPLAIN и при необходимости используйте принудительные индексы для объединений.
Это было бы более уместно в качестве комментария, но моя текущая репутация слишком мала.