Wott

WP очень любит обращаться в базу по любому пустяку, так что вынесение ее в далекие дали черевато.
Правильнее было бы сделать репликацию базы, но если у вас там какие-то изменения типа комментариев, то надо делать master-master репликацию, которая для 2 серверов имеет проблемы, лучше поставить 3й.
Если же изменения централизованы ( админ или api ) то можно сделать master-slave реплику.

Но проблема мне видится в другом — наплывы посетителей решаются через кеширование. Если у вас там нет юзерозависимых блоков, то достаточно поставить nginx с 1 минутным кэшем перед wp и нагрузка стабилизируется

Судя по акту №4 в базе данные в CP1251, хотя акт №2 не сопрягается с актом №4 — видимо вы все таки где-то ошиблись.
Как вариант сконвертируйте поля с русским текстом в blob и посмотрите что получиться

Странно то что удаление с 250К до 80К помогло разгрузить. Возможно что на 80К у вас есть попадание в кэш а на 250К нет, чего при правильных запросах быть не должно.
Посмотрите есть лимит у запросов SELECT к этой таблице и все ли одинаковые запросы одинаковы как строки?

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

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

1. select f1.friend from friends f1 where f1.user=:user_id
2. select f1.user from friends f1 where f1.friend=:user_id

пересечение будет давать взаимных френдов, вычитания — невзимных с одной и с другой стороны.
синтаксис SQL говорит что это легко можно сделать полным join, который обычно не имплементируется но эмулируется обьединением left и right. То бишь:

select * from friends f1 LEFT join friends f2 on f1.user=f2.friend and f2.user=f1.friend where f1.user=:user_id
UNION
select * from friends f1 RIGHT join friends f2 on f1.user=f2.friend and f2.user=f1.friend where f2.friend=:user_id

и будет 3 варианта — обе f1 и f2 не null — взаимные друзья или один из них null

Но это не самый эффективный способ, я бы согласился с serso и посоветовал иметь доп колонку с типом ( заодно можно их иметь несколько — друзья, супруги/любовники, коллеги ) и при добавлении проверять обратное отношение и сразу заполнять колонку. На нагруженной системе это можно делать в отложенном режиме.

посмотри кодовую страницу сессии — если установлена другая, то все успешно перекодируется

Вы неэффективно используете базу. Каждый раз вы делаете запрос мимо кэша да еще через кучу прокладок. Если для кэша установлено достаточно большое значение то он будет расти, пока не упрется в лимит.

По хорошему запрос должен быть один — «взять всех пользователей вместе с именем».
Хотя мне сложно представить зачем вдруг вам понадобились сразу все пользователи.

Надеюсь с индексами в базе все нормально.

на этом уровне это исключительно школьная задача и практической ценности у нее нет — стоит только копнуть чуть глубже простого селекта и выясняется что движки сильно отличаются и банальное id последней вставки может вызвать головную боль при портировании.

абстрагироваться надо выше — на уровне обьектов и операций работы с ними. собственно оптимизация делается уже под движек и может вызвать изменения на уровне порядка, вида запросов или отдельных операций. Тут обойтись оберткой над вызовами к базе не обойтись.

А вообще задачи доступа к разным движкам достаточно характерны для интеграции ( срастить документы в разных системах оборота, сопрячь VCS, треккер, тест менеджер и системы бизнес процессов ), для разного рода разделения по уровням ( sq-nosql, внешняя и локальная, серверная и клиентская )

вообще-то есть два способа
1. сделать DDL sql, который из схему первоначальной базы сделает нужную. Обычно это делается сразу и все модификации атомарны в виде скриптов DDL. Автоматом можно поискать тулзы которые делают миграцию структуры или сравнивают базы. Самый тупой способ — сделать экспорт структуры со старой базы ( без данных ) и тупо по тексту diff из которого сделать уже нужный скрипт для изменения DDL

2. Взять даные из старой и перенести в новую. То есть экспортируем данные из старой и заливаем в новую.

В обоих случая структуру можно поменять так что будут проблемы, обычно уникальные или внешние индексы не встают ( данные не заливаются ). Тут есть два пути — отключить все проверки и триггеры и сделать insert ignore, а потом сравнивать и искать что не залилось и почему, потом включать и проверять консистентность. Либо заливать как есть и смотреть почему вылетело. Со скриптами DDL вариант только второй.

Я в такой же ситуации обычно делают экспорт/импорт данных, потому что перестраиваются индексы, дополнительная проверка и вообще старую базу в качестве бекапа использую, хотя скрипты DDL есть всегда ( они синхронизируют базы на локальном и внешнем дев-серверах ).
Если есть проблемы, то приходиться писать редактирование данных в слитом дампе или отключать фичи в новом, менять данные и включать их обратно.

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

заведите юзера в mysql у которого минимум прав на эту таблицу и сделайте функцию, которая делает что нужно. из скрипта вызывай ее
mysql -u user -ppass -e 'select the_function();'

2. с датой просто:
delete from log_table where created<date_sub(now(), interval 2 month);

1. с размером чуть сложнее — в information_schema.tables есть data_length в байтах — можно взять разницу между 2*1024*1024 и data_length и поделить на data_length/table_rows — получим сколько строк, примерно, нужно удалить. что то типа
select if(data_length>2*1024*1024,floor((data_length-2*1024*1024)/(data_length/table_rows)),0) from information_schema.tables where table_schema='db_name' and table_name='log_table' into @row_cnt; delete from log_table order by created limit @row_cnt;

при этом конечно же надо завести индекс на created — поле где дата создания

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

Далее надо думать сколько живет кеш и как его инвалидировать. Есть простые схемы типа короткого внешнего кеша ( например nginx ), которые разгружают движек и базу. Есть более сложные — движек сам создает файловый кеш и удалает/пересоздает его по изменению контента ( самый простой — скидывать результаты запросов в файлы, которые лежат в соответвии с запрошенным урлом и сделать правила для apache, которые будут отдавать файлы вместо запуска движка, если они есть. )

Использовать еще базу типа memcached для кеширования запросов в безу или даже в движек — это явный фейл архитектуры :) Только с целью кеширования лучше отдать память в кеш io. Разумное использование начинается с хранения отдельных данных, типа сессий пользователя, которые подмешиваются в закешированную страницу включениями — типа разделение долгоживущих данных и короткоживущие сессии.

не понятно зачем иметь 4 таблицы для похожих сущностей — post,news,special, special_page. Если ихъ обьеденить, то сразу вопрос с комментами отвалится.

имхо очень плохая практика иметь безликие id, name — лучше иметь blog_id, post_name и сразу все понятно без разглядывания схем или ключей

Дурацкая мысль — настроить репликацию и включать лог вручную только для DDL команд SET SQL_LOG_BIN=1

Но вообще имхо как-то неправильно. Я делаю так что тестовое окружение восстанавливаемо, то есть есть где-то заливка базы, которую использую для инициализации перед прогоном тестов. Соответственно ее можно разделить на схему и данные и схему брать из продакшена экспортом. Либо схема лежит в VCS целиком для данного билда или в виде патчей.

вообще стандартное средство в таких случаях — поднять slave и с ним делать все что надо

правильно, с точки зрения нормализации вариант №1, когда нет дублирующей и зависимой информации. Если это не критичное для производительности место, то лучше не создавать лишних сущностей.

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

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

Советую регистрировать имя отдельно — хостинги меняются просто, если не надо переносить домен, а дешёвый хостинг менять скорее всего придется. Доменное имя на год можно зарегистрировать в районе 1$ по скидке — надо просто прошвырнуться по регистраторам и пошукать текущие скидки.

Очень странно — должен ругаться на все соединения с данного хоста.
И если есть проблемы — сначала их надо решать, а то приложение будет блокироваться снова и снова. Как вариант — кто-то перебирает пароли на доступный снаружи mysqld — отфильтруйте соединения снаружи нафиг.
И еще — есть глобальная переменная, которая определяет сколько ошибок до блокировки — можно ее поднять временно.

SELECT new_column, (CASE WHEN new_column < 1000 THEN new_column ELSE new_column + 1000 END) as new_column2 FROM (SELECT (CASE WHEN a.id > 500 THEN a.id ELSE a.id + 500 END) as new_column FROM table as a) as b