Кеширование результатов запросов в MySQL

MySQL, PostgreSQL, MSSQL, Oracle и т.д. Диалекты SQL, формирование запросов, хранимые процедуры, тригерры, транзакции и многое другое

Кеширование результатов запросов в MySQL

Сообщение xdsl 22 сен 2010, 12:20

Долгое время не испытывал нужды в кешировании результатов селектов. Однако время идет и на сегодняшний день у меня в вузовской базе более 20000 персоналий с немеряным количеством атрибутов (~10000 договоров, ~30000 фактов оплат за обучение, ~300000 рейтинговых оценок и т.п.). До сих пор, как истинный джедай ;), обходился оптимизацией запросов, полей таблиц, индексов и т.п, добиваясь мгновенного выполнения запросов, однако всему есть предел.
Например, фильтр, возвращающий сотню договоров, работает сейчас уже не мгновенно:
Код: Выделить всё
SELECT SQL_CALC_FOUND_ROWS
k.id as id,
concat(DATE_FORMAT(k.AGREEMENT_INDATE, '%y' ) , '.', f.num, '.') as AGREEMENT_PRENUM,
concat(DATE_FORMAT(k.AGREEMENT_INDATE, '%y' ) , '.', f.num, '.',k.AGREEMENT_NUM) as AGREEMENT_NUM_REAL,
AGREEMENT_NUM as AGREEMENT_NUM,
dkt.name as dktname,
AGREEMENT_CONCLUSION_DATE as AGREEMENT_CONCLUSION_DATE,
AGREEMENT_COMPLETE_DATE as AGREEMENT_COMPLETE_DATE,
AGREEMENT_INDATE as AGREEMENT_INDATE,
AGREEMENT_OUTDATE as AGREEMENT_OUTDATE,
concat(cust.surname,' ',cust.firname,' ',cust.patname) as customer,
concat(stud.surname,' ',stud.firname,' ',stud.patname) as student,
f.name as fname,
qualif.name as qname,
dp.code as code,
pt.name as totype,
k.privelege as privelege,
k.addinfo as addinfo,
PRICE as PRICE,
s as s,
COALESCE(cast(s/price*100 as decimal),0.0) as ss,
PRICE2 as PRICE2,
s2 as s2,
COALESCE(cast(s2/price2*100 as decimal),0.0) as ss2,
PRICE3 as PRICE3,
s3 as s3,
COALESCE(cast(s3/price3*100 as decimal),0.0) as ss3,
PRICE4 as PRICE4,
s4 as s4,
COALESCE(cast(s4/price4*100 as decimal),0.0) as ss4,
PRICE5 as PRICE5,
s5 as s5,
COALESCE(cast(s5/price5*100 as decimal),0.0) as ss5,
PRICE6 as PRICE6,
s6 as s6,
COALESCE(cast(s6/price6*100 as decimal),0.0) as ss6,
PAYMENT1 as PAYMENT1,
PAYMENT1_DATE as PAYMENT1_DATE,
k.creator as creator,
k.creatorTS as creatorTS,
k.updator as updator,
k.updatorTS as updatorTS
      FROM
dtpeople cust,
dicorgunit f,
dtpeople stud,
dicqualif qualif
inner join dicprof dp on dp.id=qualif.to1prof
inner join dicprof_type pt on pt.id=dp.totype,
dickontracttype dkt,
kontracts k,
(
SELECT
  k.id as kid,
COALESCE(convert(sum(pay.payment)/(count(*)/count(distinct pay.id)),decimal),0.0) as s,
COALESCE(convert(sum(pay2.payment)/(count(*)/count(distinct pay2.id)),decimal),0.0) as s2,
COALESCE(convert(sum(pay3.payment)/(count(*)/count(distinct pay3.id)),decimal),0.0) as s3,
COALESCE(convert(sum(pay4.payment)/(count(*)/count(distinct pay4.id)),decimal),0.0) as s4,
COALESCE(convert(sum(pay5.payment)/(count(*)/count(distinct pay5.id)),decimal),0.0) as s5,
COALESCE(convert(sum(pay6.payment)/(count(*)/count(distinct pay6.id)),decimal),0.0) as s6
       FROM
kontracts k
left join
payments pay
on pay.tokontract=k.id and pay.kontractYear=1
left join
payments pay2
on pay2.tokontract=k.id and pay2.kontractYear=2
left join
payments pay3
on pay3.tokontract=k.id and pay3.kontractYear=3
left join
payments pay4
on pay4.tokontract=k.id and pay4.kontractYear=4
left join
payments pay5
on pay5.tokontract=k.id and pay5.kontractYear=5
left join
payments pay6
on pay6.tokontract=k.id and pay6.kontractYear=6
      GROUP BY
k.id
) as pays

WHERE
cust.id=toPeopleCust and
stud.id=toPeopleStud and toDicQualif=qualif.id and qualif.toorgunit=f.id
and AGREEMENT_TYPE=dkt.id and pays.kid=k.id 

GROUP BY
k.id
 
ORDER BY
AGREEMENT_PRENUM ASC, AGREEMENT_NUM ASC
limit 0,100

На все про все уходит до 3.5 секунд, что начинает раздражать пользователей системы. Анализ и профилирование этого и многих других запросов результата уже не дает. Попытки использования временных таблиц вместо подзапросов, даже с учетом их индексирования, и уж тем более попытки свести все в один запрос проблему не решают - количество обрабатываемых данных слишком велико. Упрощение запросов для частных случаев - вполне возможно, но тогда уйдет в прошлое вся мощь системы, позволяющей фильтровать данные по любому из существующих полей и произвольной их комбинации без потери скорости. Можно пытаться решать проблему наращиванием мощности железа, но это лишь отодвинет проблему чуть дальше во времени. Можно добавить поддержку кэша в саму систему, но это - время и немалое, особенно на тестирование. А хочется - дешево и сердито.

Оказалось, решение есть. Не панацея, но в большинстве случаев проблема решаема. В MySQL имеется поддержка кэширования результатов запросов. Т.е. результат выполнения select сохраняется в кэше, индексируемый хешем (md5) строки-запроса. При повторном запросе сервер возвращает сохраненный результат, не выполняя сам запрос. При этом любой insert, delete, update и т.п. удаляет из кэша запросы, ссылающиеся на изменяемые таблицы. По умолчанию кэш выключен. Включить просто:
в my.cnf прописать
query_cache_size=сколько памяти под кэш
Лично у меня теперь query_cache_size=64M
За утро в кэше 940 результатов запросов, 5555 хитов и всего 2МБ задействованной RAM:
Код: Выделить всё
mysql> SHOW STATUS LIKE "Qcache%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 161      |
| Qcache_free_memory      | 65743968 |
| Qcache_hits             | 5555     |
| Qcache_inserts          | 3295     |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 21024    |
| Qcache_queries_in_cache | 940      |
| Qcache_total_blocks     | 2097     |
+-------------------------+----------+
8 rows in set (0.00 sec)

Более 20000 некэшированных запросов объясняется тем, что на этом-же сервере крутится realtime база данных журнала доступа портала shgpi.edu.ru и ее статистический обработчик.
xdsl
 
Сообщения: 1236
Зарегистрирован: 09 дек 2008, 05:16
Откуда: ВЦ ШГПИ
Полное имя: Слинкин Д.А.

Re: Кеширование результатов запросов в MySQL

Сообщение alekam 22 сен 2010, 16:10

есть скрипт mysqltuner, анализирующий статистику работы mysql и показывающий его узкие места
alekam
 
Сообщения: 46
Зарегистрирован: 23 дек 2008, 14:36
Полное имя: A.K.

Re: Кеширование результатов запросов в MySQL

Сообщение xdsl 22 сен 2010, 16:53

Смотрел и узкие места определил. Но их я и так знал, через explain и profiling=1 пропускал все. У меня проблемы не в поиске узких мест, а в поиске оптимальных способов расширения узких мест.
xdsl
 
Сообщения: 1236
Зарегистрирован: 09 дек 2008, 05:16
Откуда: ВЦ ШГПИ
Полное имя: Слинкин Д.А.


Вернуться в Базы данных

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 2

cron