3 вопроса по бд для 5к проекта

чето запутался уже, перестраиваю боевой проект где много count и с 5к хитов сильно тормозит, портал + доска объявлений
собственно

1
Выбрать записи из т1 и присоединить значение из т2 если они существуют или '0'

в t1 ключ автоинкремент , в t2 ключ varchar (в реале1, 1-125-3, 16-5243-128 итд)

t1
1 значениеAAA
2 значениеDDD
3 значениеXXX

t2
1 2

пишу

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

По моему поиск по varchar не лучший вариант.
Тем более с LEFT JOIN , и тем более с разными типами данных в запрашиваемых таблицах.

Проект очевидно старый.
Нужно найти эти "твердолобые" места с запросами.
EXPLAIN в том же phpmyadmin может с этим помочь, выполнив некоторые запросы в ручную, может не хватает банально ключа для поля.
5к юзеров не так и много.

может вы выразите (и еще кто нибудь) мысли более конкретно в контексте вопросов и указанных цифр по пунктам?

(Отредактировано автором: 19 Сентября, 2015 - 17:51:26)

Что это за величина? Укажите нормальные человеческие qps (раздельно r/w) и tps для субд, а так же rps в целом для бекэнда средние и пиковые.


Если в t2 нет такого значения, выдаётся 0.
А вот если есть - то выводится столько строк, сколько их есть в t2. Потому что join и никакой группировки.


Один из двух возможных вариантов как разгрузить субд от постоянных count. Или кэшировать или дёргать триггером.


Хорошо работающий кэш не использует вытеснение по таймауту, только по LRU. Обновляет при этом кеш тот, кто собственно и пишет.
Хорошо или плохо писать html в memcache - вопрос спорный. Главное, не мешайте в одном инстансе мемкеша мелкие и крупные куски, иначе начинаются проблемы с фрагментацией и неэффективным использованием памяти.

-----
PostgreSQL DBA

мелкий

дело в том, что в t2 может быть только одна соответствующая строка с двумя полями (ключ варчар и значение)
реально ключ для связи формируется динамически от пользовательских запросов, это условный пример ключ "1" => в т2
1-1
1
1-1256-12
выбираем ключ 1

?вроде как пробела не должно быть IFNULLтут(...


?я кеширую - можно подробней пример или как "дёргать триггером"



ставлю по таймауту тк за это время данные могут (99%)изменятся (в том то и сложность чтоб отдавать как можно более свежие данные , разгрузив бд).
:
добавили объявление - изменилась их сумма в регионе, городе, разделе, категории, рубрике.
это ключи в т2 изменяем значения +1 для ид: (регион, регион-город, регион-город-раздел,...)

в кеш ложу выборки - html 1500-3500 символов, в итоге получаю, пример:
запрос пользователя на главн стр, те для данной страны
отдаю html + html из мемкеша, разобрав в блоки шаблона по разделителю:
из кеша
сумму объявлений по регионам (из т2)
сумму объявлений по разделам (из т2)
итог расчета count сумму объявлений всего/покука/продажа/обмен/беспл атно в данном примере по стране
из скрипта
последние 10 объявлений

если перехожу в раздел => все тоже только для категории
если перехожу в регион => все тоже только для городов региона
если перехожу в город и раздел=> все тоже только для категорий этого раздела по этому городу

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

? может видите какой другой алгоритм

спасибо

(Отредактировано автором: 19 Сентября, 2015 - 19:30:37)

Ваша СУБД с вами однозначно не согласна.


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


То что сами описали в начальном сообщении.


Вы не объяснили, зачем вам время жизни записей в мемкеше. Данными вы и управляете, значит и кэшировать можете нормально событийно.
Создали объявление - отправили запрос в базу, проинкрементили соответствующие значения. Удалили - сделали декремент.

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

-----
PostgreSQL DBA

немного проясняется ситуация...

получается что табл. с агрегированными данными (счетчиками) будет всегда консистентной при обновлении ее триггером после создания/удаления объявления, что позволит отдавать всегда свежие данные при чтении с неё и на начальном этапе не использовать мемкеш

в итоге склоняюсь к такому алгоритму:
после создания/удаления объявления триггером на ней выполняем +1/-1 к соответствующим строкам (сейчас строк 6) таблице со счетчиками
+ постоянное чтение по ид без условия и сортировок
в принципе должно работать быстро

+прихожу к выводу, что возможно нужно выкинуть сумму по регионам, которая дергается всегда, что чуть разгрузит бд на 1/6 транзакции обновления как я понимаю

...опять встает вопрос в скорости работы:
1 но какие могут быть скрыты проблемы, ведь в итоге тут будет строк многократно больше чем самих данных (объявлений) ?
2 стоит триггер insert on duplicate, тк какие то рубрики в каких то городах вовсе не задействованы, таблица меньше, возрастает по мере вовлечения рубрик в городах - или стоит изначально забить все возможные варианты, но сразу неимоверно увеличить количество строк
3 в итоге уберем count, но как со скоростью инкрементирования и чтения?

Вы издеваетесь, да?
Там что, на столько мелкая и незначительная табличка, что смехотворные 3 ляма записей - это многократно больше?

По IFNULL догадываюсь, что речь о mysql?
На запросах insert on duplicate key update можно время от времени ловить дедлоки. Поэтому лучше заполнить таблицу счётчиков целиком и делать только update. А, кстати, да, обновляйте записи всегда в одном и том же порядке. Дедлоки и просто апдейтами хватать можно запросто.

В чём проблема инкрементов? Конкурентность записи, конечно, неважная получится, быстро упрётся в блокировки строк.

И терзают нехорошие предчувствия, лучше уточню: там не сделана какая-нибудь великая глупость вроде myisam вместо storage engine?

-----
PostgreSQL DBA

так, mysql

Ну, совершенно очевидно так джойн и работает.


Для таблицы счётчиков? Фигня вопрос даже для тупого mysql.
Хотя у вас ведь профиль нагрузки другой. Дедлоки на on duplicate update хорошо ловятся на аггрегации событий по дням, а у вас insert - штука редкая, так что on duplicate должен работать удовлетворительно, да.
Правда, всё равно не догоняю, почему ключ символьный, а не пара интов. Рубрики явно отдельная таблица со своим сиквенсом, нас.пункты - очевидно, тоже.

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

-----
PostgreSQL DBA

мыж пмшем

Сделайте пример на каком-нибудь sqlfiddle


Населённый пункт для конкретики с id 10546:
в случае инта: 4 байта, 1 команда процессора на сравнение. Вы ведь не на AVR какой-нибудь работаете, а как минимум на 32-битной системе, а то и на 64? К тому же можно FK повесить.
в случае varchar: 5 символов, это уже от 5 до 20 байт в зависимости от кодировки плюс один байт на длину строки. Поинтересуйтесь, сколько ассемблерных команд надо, чтобы сравнить две строки на равенство. Затем, в памяти varchar занимает всегда максимальный разрешённый для этого столбца объём. А поскольку в mysql primary key - это всегда b-tree, varchar будет вести себя как char и всегда занимать максимальный объём и на диске тоже.

-----
PostgreSQL DBA

спасибо, строку много дольше проверять.
в итоге переделал ближе к вашему совету и вообще удалил ид автоинкремент, чтоб on duplicate иды не жрал на всякий случй
для диска выгода получилаь не очевидна - 6 полей инт *4 байта =24, а в стлучае варчар максимум 12-100-1000-1000-6-25000 +1 =25 но 1-1-1-1-1-1 +1=12 даже чуть экономичней.
в where однако теперь 5 раз AND? чтоб получить счетчик для региона (ключи по столбцам 0|0|0|0|1ид_региона|0)
в итоге так сделал - ввезде инт + составной индекс уникальный
раздел|категория|рубрика|типто_вара|регион|город|сч_всего|сч_частных|сч_бизнсе|сч_бу|сч_новы|сч_покупка|сч_продаж|сч_обмен|сч_бесплптно
однако триггер получился не кислый и с условиями - чтобы реально задействовать все варианты - дергает 15 строк

хм... это допустимо / на сколько может быть плохо или критично 15 записей?
может и кеш пока отрезать, ведь выбор счетчика по полю с where должен очень быстро + ещё пару индексов сделать составных под запросы

чтото не очень нравится правка 15 строк + чтение, чтение...

Как-то вы неправильно считаете.
Каждый инт в символьном представлении - это до 10 символов текста, итого 65 символов (от 66 до 196 байт в зависимости от кодировки).
А если 4млрд значений слишком много для задачи - то надо сравнивать с mediumint или даже smallint вместо инта.


В этом случае эта калькуляция не работает, как уже писал:


Я не уловил, как изначальные изменения 6 строк превратились в 15.
Нормально, плохо или критично - зависит от соотношения затраченных ресурсов к сэкономленным. Т.е. соотношения записи новых значений к чтению из таблицы счётчиков.

-----
PostgreSQL DBA

да я уже перечитал мануал...
10 раз переделывал уже - никак не приду к требуемому результату, кроме как через count, щас скрины прилеплю чтоб видно было, что надо