Как правильно построить взаимодействие между php на одном сервере и mysql на другом?
Здравствуйте.
Стоит задача быстро и в большом объеме выбирать данные из БД на удаленном сервере.
Запросы очень тяжелые, с большим количеством условий.
Если подключаться к удаленной базе и выбирать данные напрямую, то происходит ситуация, когда коннекты висят по несколько минут, забивая все доступные подключения и пропускная способность получается очень низкой.
Для увеличения пропускной способности я сделал "карту данных" - кроном, раз в несколько минут на удаленном сервере выбираются все id соответвующие глобальным условиям и пишутся в файл.
На целевом сервере из этой карты выбирается несколько id и происходит обращение удаленной БД, но уже с упрощенным запросом, по первичному ключу id и локальными условиями для конкретного подключения. В такой ситуации, из за первичного ключа и локальных условий, часто нет подходящих данных и необходимо делать запросы рекурсией в несколько итераций.
Выбор данных происходит несколько дольше, но это так сильно не забивает подключения.
Несмотря на эти меры, и первичный ключ в запросе, часто происходит ситуация, когда удаленные коннекты просто висят по несколько минут со статусом Sleep и, как и в первом случае, забивают все доступные подключения (хотя все лучше, чем в первом варианте). Это при том, что подключение к удаленной БД происходит прямо перед запросом, а закрытие подключения сразу после. Непонятно почему подключения не закрываются. Есть ощущение что удаленные запросы выполняются медленнее сами по себе, т.к. если делать все тоже самое на том сервере, на котором хранится БД, такой проблемы нет.
Возможно в mysql есть специальные настройки, для оптимизации удаленных подключений?
Как лучше/правильнее организовать данную логику, для увеличения скорости и пропускной способности?
я вам больше скажу: нагрузка на канал минимальна, т.к. на слейв дублируются только запросы меняющие данные, т.е. INSERT, UPDATE, DELETE (причем только в том случае, если данные при запросе были изменены), также по опыту на слейв дублируются запросы CREATE, ALTER, DROP
Если связь пропадает - она восстановится как будет возможность. Единственным критичным сбоем может быть только разрушение на мастере данных (особенно с таблицами MyISAM) - тогда репликацию придется восстанавливать.
В общем случае время выполнения запроса никак не зависит от того локальный он или удаленный.
Могут быть микро потери времени на передачу самого запроса (если он реально длинный) и заметные потери времени на передачу ответа (опять же если он огромный).
По пунктам того что Вы пишите:
Если подключаться к удаленной базе и выбирать данные напрямую, то происходит ситуация, когда коннекты висят по несколько минут, забивая все доступные подключения и пропускная способность получается очень низкой.
Запросы реально исполняются несколько минут? Какой объем данных передается в ответе? Какой канал между серверами, не забивается ли он? Потребление памяти/процессора на SQL сервере в момент выполнения запроса?
Есть тупая идея увеличить max_connections, но я сомневаюсь что в Вашем случае это поможет, скорее всего проблема не в этом.
Это при том, что подключение к удаленной БД происходит прямо перед запросом, а закрытие подключения сразу после.
Можно так же попробовать использовать persistent connection, не будет оверхеда на постоянные подключения/отключения.
Возможно надо посмотреть в сторону оптимизации самих запросов, дробления их на части итд.
>>Запросы реально исполняются несколько минут?
>>Потребление памяти/процессора на SQL сервере в момент выполнения запроса?
По одиночке, нет, но если их сразу много, то подлетает использование процессорного времени для mysql и сервер очень напрягается, поднимается load average и waiting. Собственно этот момент удалось решить "картой данных".
>>Какой объем данных передается в ответе?
Совсем немного, несколько сотен байт.
>>Какой канал между серверами, не забивается ли он?
Интересный вопрос, как это можно проверить?
theded: ну если сервер не тянет то уже никак не решишь кроме оптимизации кода и индексов, если же просто не хватает коннектов то я всегда увеличиваю в сто раз и сервер у меня не падает.
Несмотря на эти меры, и первичный ключ в запросе, часто происходит ситуация, когда удаленные коннекты просто висят по несколько минут со статусом Sleep
