Вопрос о базах и кеше.

Dmitry67 · Post by **Dmitry67** » 29 Sep 2003 14:41

ВОт и у меня вопрос возник по SQL serverу. Впрочем есть вопросик и для остальных

Имею две квери, A и B.
Кверь A выполняется первый раз 50sec, второй раз 5 sec
Кверь B выполняется 40sec, второй раз 10 sec
Понятно, работает кеш

При запуске их последовательно A,B выполняется 50sec.
Ага, кеша мало
Пытаюсь дать SQL serverу больше памяти
Скорость увеличивается но больше 1.7Gb расти не хочет
Хотя памяти на сервере значительно больше
Почему ?

Кстати, на DB2 и Oracle - они читают все мимо кеша операционной системы ?

Dmitry67 · Post by **Dmitry67** » 29 Sep 2003 14:50

Кажется сам понял: надо включить AWE

vc · Post by vc » 29 Sep 2003 15:57

Dmitry67 wrote:Кстати, на DB2 и Oracle - они читают все мимо кеша операционной системы ?

It depends.

In Unix, one has to use either raw partitions or the 'forcedirectio' option (Solaris) when mounting a file system in order to avoid OS buffering.

In Windows, Oracle always bypasses the OS cache by using the FILE_FLAG_NO_BUFFERING/FILE_FLAG_WRITE_THROUGH options.

Rgds.

Dmitry67 · Post by **Dmitry67** » 29 Sep 2003 16:04

А на Linux есть forcedirectio ?

Кстати любопытно
С AWE sql server в Task Manager выглядит совсем крошечным

То есть AWE память 'не считается'...

zVlad · Post by **zVlad** » 29 Sep 2003 17:06

Dmitry67 wrote:ВОт и у меня вопрос возник по SQL serverу. Впрочем есть вопросик и для остальных
..........
Кстати, на DB2 и Oracle - они читают все мимо кеша операционной системы ?

Shortly saying DB2 reads pages from disks without using what you call “кеш операционной системы “ (which is actually not defined in MVS at all). Of course, DB2 has caches (called as Buffer Pools) to store pages in memory for better performance. For those BPs, DB2 co-operates with MVS very close. There are MVS’s Data Spaces (it could be considered as an analog of “кеш операционной системы “) and they could be used by DB2 for BPs (this is just one of few ways of how BPs could be implemented. With 64-bit addressing in place, ways of having BPs are totally reviewed. But all pervious cache related techniques are kept.)
In our shop we have few tables which are completely resided in CPU memory all time. It was made to meet response time requirements.

By the way, DB2 has a “cache” for dynamic SQL statements. That means when SQL comes in for first time, DB2 will compile it and store it in “dynamic SQL cache”. If the same SQL will come in again, DB2 will rather use already prepared and stored SQL than do it again.
Do Oracle and/or MS SQL have stuff like that?

YellowMan · Post by **YellowMan** » 29 Sep 2003 17:07

У меня сложилось впечатление что если выполнять более одного запроса последовательно сервер не использует кэш отдельно для каждого из запросов - можно бы проверить в Performance Manager.

Dmitry67 · Post by **Dmitry67** » 29 Sep 2003 17:17

zVlad wrote: By the way, DB2 has a “cache” for dynamic SQL statements. That means when SQL comes in for first time, DB2 will compile it and store it in “dynamic SQL cache”. If the same SQL will come in again, DB2 will rather use already prepared and stored SQL than do it again.
Do Oracle and/or MS SQL have stuff like that?

Разумеется
Более того, если выполнять запросы типа
select ... where A=45 and B=66
select ... where A=233 and B=66
select ... where A=9 and B=66

то вскоре заметишь что MS SQL создал план запроса для
select ... where A=@p1 and B=66

vc · Post by vc » 29 Sep 2003 18:32

Dmitry67 wrote:А на Linux есть forcedirectio ?

...

Last time I dealt with Oracle on Linux (about a year ago), there was no such option.

Rgds.

zVlad · Post by **zVlad** » 29 Sep 2003 18:45

Dmitry67, in DB2, it is the same, but queries:

'select c1 where A=? and B=?'

, and

'select c1 where A=? and B=? '

will be considered as two different queries.

verzlo · Post by **verzlo** » 29 Sep 2003 22:14

Dmitry67 wrote:Кажется сам понял: надо включить AWE

Абсолютно верно ,включите AWE и дополнительно включите 3G switches.
По умолчанию в Виндах мах размер процесса в памяти не может превысить 2 Гб (включая виртальную память для него - поэтому реальной памяти вы видите тольк 1.7 Гб примерно). Если включить 3G switch - то
этот лимит увеличится до 3 Гб. При включении AWE - можно неограниченно увеличивать размер процесса в пределах свободной памяти, но тут есть ньюанс - буфера памяти будут маппится в "верхней" памяти > 4 Гб, а в обычной будут сохраняться пойнтеры наних.. Это немного увеличивает CPU overhead - в нашем случае на 15-20 % .

Dmitry67 · Post by **Dmitry67** » 30 Sep 2003 08:43

verzlo wrote:
Dmitry67 wrote:Кажется сам понял: надо включить AWE

Абсолютно верно ,включите AWE и дополнительно включите 3G switches.
По умолчанию в Виндах мах размер процесса в памяти не может превысить 2 Гб (включая виртальную память для него - поэтому реальной памяти вы видите тольк 1.7 Гб примерно). Если включить 3G switch - то
этот лимит увеличится до 3 Гб. При включении AWE - можно неограниченно увеличивать размер процесса в пределах свободной памяти, но тут есть ньюанс - буфера памяти будут маппится в "верхней" памяти > 4 Гб, а в обычной будут сохраняться пойнтеры наних.. Это немного увеличивает CPU overhead - в нашем случае на 15-20 % .

Да, спасибо
А вообще deja vu - помните XMS, EMM386, оптимизация QEMM386

lozzy · Post by **lozzy** » 30 Sep 2003 08:54

Dmitry67 wrote:Да, спасибо
А вообще deja vu - помните XMS, EMM386, оптимизация QEMM386

64 бита не за горами

hren · Post by **hren** » 02 Oct 2003 09:09

vc wrote:Last time I dealt with Oracle on Linux (about a year ago), there was no such option.

Direct io для файловых систем поддерживается на уровне ядра в Linux RH Enterprise Linux AS (бывший Red Hat Linux Advanced Server). Возможно и где-то еще. В принципе, он доступен для файловых систем xfs (точно) и, возможно, ext3 (не уверен).

В большинстве случаев использование direct io в Oracle актуально для OLTP приложений, с высоким уровнем io, при большом размере блочных буферов СУБД, особенно при использовании высокоскоростных внешних дисковых массивов со своим большим кэшем и большой пропускной способностью контроллера. Может заметно уменьшить нагрузку на процессоры сервера.

hren · Post by **hren** » 02 Oct 2003 09:22

zVlad wrote: By the way, DB2 has a “cache” for dynamic SQL statements. That means when SQL comes in for first time, DB2 will compile it and store it in “dynamic SQL cache”. If the same SQL will come in again, DB2 will rather use already prepared and stored SQL than do it again.
Do Oracle and/or MS SQL have stuff like that?

Oracle имеет очень развитую систему кэширования запросов. Кэшируются курсоры на уровне сессии, кэшируются запросы (несколькими способами) в общих областях памяти. Это механизм настраивается большим количеством параметров и работает весьма эффективно, если у приложения достаточно велик коэффициент переиспользования запросов. В противном случае contention в областях буферизации запросов приводит иногда к необходимости уменьшать их размер.

zVlad · Post by **zVlad** » 02 Oct 2003 15:15

hren wrote:
zVlad wrote: By the way, DB2 has a “cache” for dynamic SQL statements. That means when SQL comes in for first time, DB2 will compile it and store it in “dynamic SQL cache”. If the same SQL will come in again, DB2 will rather use already prepared and stored SQL than do it again.
Do Oracle and/or MS SQL have stuff like that?
Oracle имеет очень развитую систему кэширования запросов. Кэшируются курсоры на уровне сессии, кэшируются запросы (несколькими способами) в общих областях памяти. Это механизм настраивается большим количеством параметров и работает весьма эффективно, если у приложения достаточно велик коэффициент переиспользования запросов. В противном случае contention в областях буферизации запросов приводит иногда к необходимости уменьшать их размер.

In DB2 I could find just three parameters to control Dyn SQL Cache, one of them is optional - Data Space Size (if you wish Data Space for Dyn Cache), Second one is to say if you really wish to use Dyn Cache (YES, or NO), and last one to limit number of dyn stmts be kept past commit point for each single application.
I have never heard about contentions related to usage of Dyn SQL Cache. :pain1:

zVlad · Post by **zVlad** » 02 Oct 2003 16:14

I could find something in IBM problem data base regarding contention and Dynamic SQL (I put here whole cover letter for not only to show problem itself but also how IBM problem cover letter looks like. With monospace font it looks better). It was fixed in 1998:

APAR Identifier ...... PQ14893 Last Changed..02/05/14
INTERNAL LATCH SUSPEND TIMES ARE HIGH WHEN USING VERY HIGH
THROUGHPUT OF DYNAMIC SQL AND USING DYNAMIC STATEMENT CACHING

Symptom ...... PF PERFM Status ........... CLOSED PER
Severity ................... 4 Date Closed ......... 98/08/28
Component .......... 5740XYR00 Duplicate of ........
Reported Release ......... 510 Fixed Release ............ 999
Component Name 5740 IBM DATABA Special Notice
Current Target Date ..98/07/07 Flags
SCP ...................
Platform ............

Status Detail: SHIPMENT - Packaged solution is available for
shipment.

PE PTF List:

PTF List:
Release 510 : UQ21098 available 98/09/18 (F809 )

Parent APAR:
Child APAR list:

ERROR DESCRIPTION:
When running many concurrent threads, each submitting large
numbers of PREPARE statements, DB2 throughput is reduced by
internal latch contention. Reports show a high percentage of
DB2 class 2 time being spent in latch suspension. This was
seen when using dynamic SQL caching, but can also occur when
that feature is not used.

Additional keywords: OEM PeopleSoft hung thread loop excessive
cpu cycles in DBP1MSTR DSNXEUF0 DSNIDALC DSNXEUFP DSNSPOWN +300
DSNXPDAL PIPE MANAGER

LOCAL FIX:

PROBLEM SUMMARY:
****************************************************************
* USERS AFFECTED: DB2 Subsystems running with the Dynamic *
* Statement Cache feature (CACHEDYN=Y) and *
* have a high throughput of concurrent *
* dynamic SQL statements. *
****************************************************************
* PROBLEM DESCRIPTION: Performance gets gradually worse *
* over time for dynamic PREPARE *
* statements while the DB2 subsystem *
* is active. Throughput of *
* concurrent dynamic SQL PREPARE *
* requests is also reduced over time. *
****************************************************************
* RECOMMENDATION: *
****************************************************************
Several customers have reported performance problems when
running with dynamic statement caching in an environment where
there is a large volume of concurrent dynamic SQL.
Some of these problems are more likely to occur when there are
lower hit ratios in the dynamic SQL cache. Investigation has
turned up the following bottlenecks that contributed to this
slowdown:

1. All DB2 threads that retrieved statements from the dynamic
SQL statement cache kept their working copies in a shared
storage pool. This was a source of latch contention as these
threads attempted to get and free storage from that pool.
This appeared as contention in latch class 32.

2. Another shared storage pool - the RDS OP POOL experienced
high rates of contention when many concurrent prepares were
executed. This appeared as contention in latch class 32.

3. DB2 hash tables for tracking cached SQL statements and their
object dependencies experienced severe latch contention when
many concurrent PREPARE requests are processed. This appeared
as contention in latch classes 25 and 26.

4. An error in the logic for managing object dependencies caused
some storage to be acquired and never freed or reused. In a
storage dump, these blocks can be identified by the eyecatcher
"DMHE"

5. Also in the object dependency recording logic, some of the
code was found to be very inefficient, causing many CPU cycles
to be used.

PROBLEM CONCLUSION:
To address the performance problems listed in the APAR summary,
the following changes have been made to DB2 code:

1.)The working copies of cached SQL statements are no longer
stored in a shared pool. They have been moved to a thread-
based storage pool, eliminating storage pool latch contention.

2.)When preparing a dynamic SQL statement for insertion into
the cache, the RDS OP pool is no longer used. That storage is
acquired in a thread-based pool that lasts only for the
duration of the prepare request.

3.)Several changes were made to alleviate latch contention on
the statement and object hash tables:
a.)Logic was changed so that the latch used on the object
dependency tables is more granular and acquired less
frequently.
b.)The size of the statement hash table was increased
c.)The key generation logic for the statement hash table was
changed for better distribution.

4.)Code was changed so that storage is always freed or reused
correctly for the object dependency tables.

5.)Object dependency recording code has been restructured to
eliminate inefficiencies.

hren · Post by **hren** » 03 Oct 2003 13:15

Я не совсем понимаю кое-что. Должен сразу сказать, что DB2 не знаю совсем, поэтому не удивляйтесь, если мои вопросы не совсем в тему.

Во-первых, мы говорим о кэшировании любых SQL или только динамических? Динамический SQL поддерживается Oracle несколькими способами, но мне кажется, что никаких специальных механизмов кэширования при разборе и исполнении именно динамических выражений не предусмотрено, они обрабатываются на общих основаниях. Впрочем, я могу ошибаться. Приведенная Вами цитата из документации IBM относится к довольно специфической для Oracle вещи, которая на практике используется довольно редко - именно к особенностям разбора выражений при выполнении prepare. Я никогда не видел вживую OLTP приложения на Oracle, которое всерьез занималось бы генерацией динамических запросов, как правило, используется статика. В DB2 ситуация иная?

Во-вторых, наличие contention в общий областях памяти, иcпользуемых для кэширования SQL, является прямым следствием конечности мира

. Я не могу себе представить систему, использующую буферную память фиксированного размера, которая была бы полностью лишена этой проблемы. Конечно, если приложение написано хорошо, коэффициент переиспользования SQL высок, соответственно hit ratio в shared pool правильного размера (как я понимаю, это понятие есть и в DB2) также высок и contention низок. Если переиспользование мало, а приложение велико, работает много пользователей и их активность разнообразна, то при любых разумных размеров буфера наступает момент, когда в нем не остается свободной памяти, а hit ratio по прежнему низок. В этот момент и начинается contention - для того, чтобы закэшировать новый запрос система выбрасывает из кэша старые. Чем больше размер пула, тем сложнее системе обслуживать конкурентные запросы при отсутствии свободных ресурсов - начинается latch contention и wait time тем больше, чем больше памяти выделено под буфер. Мне непонятно, как можно избежать подобных эффектов с "плохими приложениями" при включенном кэшировании полностью. Или в DB2 не бывает "плохих приложений"?

Конечно, можно отключить кэширование (я подозреваю, что сама возможность в DB2 предусмотрена именно для таких ситуаций). В Oracle, по моему, так просто его не отключишь, тем более, что некоторые запросы все-равно полезно кэшировать (например, стандартные системные). Приходится играться с размерами буферов и более тонкими настройками.

Еще замечу, что на эффективность работу SQL-кэша сильно влияют и другие факторы. Классический пример в Oracle - статические SQL statements не опознаются как одинаковые, если содержат разное количество пробелов, или символы в различном регистре (несмотря на то, что семантически разницы нет, поскольку case insensitive). Для борьбы с этой проблемой есть даже специальный параметр, который меняет режимы разбора, байндинга переменных, а также позволяет игнорировать некоторые различия (режим similar в Oracle 9). Как все эти вещи обходятся в DB2, если там такая благодать с кэшированием запросов?

Dmitry67 · Post by **Dmitry67** » 03 Oct 2003 13:43

hren wrote:Мне непонятно, как можно избежать подобных эффектов с "плохими приложениями" при включенном кэшировании полностью.

Можно бороться также как и MS SQL
То есть хотя запросов плохая аппликация может выдать огромное количество
но вследствие конечности мира

и аппликации количество ТИПОВ запросов которое аппликация выдает довольно ограничено
Поэтому надо просто пытаться параметризовать запросы

К сожалению этот механизм у Oracle сделан из рук вон плохо
Не говоря о пробелах, в случае серии запросов

select * from X where A=1 and B=44 and C='33
select * from X where A=2 and B=44 and C='33
select * from X where A=3 and B=44 and C='33
...
select * from X where A=1000 and B=44 and C='33

он в зависимости от настроек либо запишет в къш тупо все 1000 запросов, либо сразу параметризирует ВСЕ ТРИ параметра A= B= C= (а зря ! - поэтому этим параметром и не рекомендуют пользоваться)

MS SQL догадается что параметризировать надо только A

vc · Post by vc » 03 Oct 2003 14:28

Dmitry67 wrote:
hren wrote:Мне непонятно, как можно избежать подобных эффектов с "плохими приложениями" при включенном кэшировании полностью.

К сожалению этот механизм у Oracle сделан из рук вон плохо
Не говоря о пробелах, в случае серии запросов

select * from X where A=1 and B=44 and C='33
select * from X where A=2 and B=44 and C='33
select * from X where A=3 and B=44 and C='33
...
select * from X where A=1000 and B=44 and C='33

он в зависимости от настроек либо запишет в къш тупо все 1000 запросов, либо сразу параметризирует ВСЕ ТРИ параметра A= B= C= (а зря ! - поэтому этим параметром и не рекомендуют пользоваться) ...

Well, it's not true.

Oracle has _two_ modes to make SQL statements with literals shareable:

1. cursor_sharing=force will autobind every constant (as described above).
2. cursor_sharing=similar will detect when substituting a bind variable may change the plan substantially and will generate multiple plans based on the bind variable value.

Both (1) and (2) are, of course, a crutch for a poorly written application.

Rgds.

hren · Post by **hren** » 03 Oct 2003 14:33

Я же говорю о "плохих приложениях". Хорошее, конечно, будет использовать в нужных местах bind variables и придерживаться стандартного форматирования запросов (что, кстати, легко автоматизируется при разработке). Насчет того, как именно Oracle параметризует константы в приведенном Вами примере при разных значениях параметра cursor_sharing, я не буду высказываться не знаю, надо попробовать. Тем более, что поведение оптимизатора вообще и в зависимости от значенияэтого параметра в частности меняется и очень сильно от версии к версии.

Dmitry67 · Post by **Dmitry67** » 03 Oct 2003 14:33

Не знал
Видимо это новое
Я знал только про cursor_sharing=force и none

hren · Post by **hren** » 03 Oct 2003 14:35

vc wrote:Well, it's not true.

Oracle has _two_ modes to make SQL statements with literals shareable:

Справедливости ради, similar появился только в девятке.

zVlad · Post by **zVlad** » 03 Oct 2003 15:16

To Hren. I'am afraid I wouldn't be able to respond to all your questions in one shoot. I'll do it in few steps. OK?

By now, I want to clarify what Static and Dynamic SQL mean in Oracle and DB2. I wouldn't surprise they are different.

In DB2, Static SQL mean - when you compile your program, all SQLs are extracted from source and put into DBRM - Database Request Module. Then, with DB2 (non-SQL) command BIND we bind DBRM into package or plan (long time ago only plan was available). Package has access path for each single SQL, and until next BIND (or REBIND), DB2 never change generated? during binding, access path, just run it. This is Static. Staticaly bound applications never use PREPARE to run SQLs! There is EDM pool where static SQLs are cached.
In contrast, Dynamic SQL is preparing every time they come up. But, if you set up Dynamic SQL Cache, DB2 is tring to search if this SQL was already PREPARed? If yes DB2 will use access path from prepared before rather then do it again. DB2 has intelect like what Dmitry67 told about.
Of course, if we have small Cache DB2 will have to choose somebody to sweep out in order to accomodate new request. There is algorithm of doing that job.

What about Oracle?

Hren also wrote: "Я никогда не видел вживую OLTP приложения на Oracle, которое всерьез занималось бы генерацией динамических запросов, как правило, используется статика. В DB2 ситуация иная?"

PeopleSoft is an example of Dynamic Application. But usually, truly DB2 applications are static (in terms I mentioned above).

"Классический пример в Oracle - статические SQL statements не опознаются как одинаковые, если содержат разное количество пробелов, или символы в различном регистре (несмотря на то, что семантически разницы нет, поскольку case insensitive).... " you see, it is impossible for DB2 Static applications. I bet "Oracle static" and "DB2 static" are two different worlds. What about dynamic?

"... Для борьбы с этой проблемой есть даже специальный параметр, который меняет режимы разбора, байндинга переменных, а также позволяет игнорировать некоторые различия (режим similar в Oracle 9). Как все эти вещи обходятся в DB2, если там такая благодать с кэшированием запросов?"

Again, it is two different wolds (I guess). For static, DB2 has no such problem at all, and there is no special parametrs there. For Dynamic you might want or might not want to use cache - depence on nature of application you run. In my shop we used to use totally static application (with small amount of dynamic requests, mostly for maintanace and stuff like that), Few months ago, application was implemented to run Dynamic queries. This application has given number of SQLs, say 100. Monitor shows (see attached, "SQL Cache" - Dynamic SQL chace). Hit ration is very high 94.6% for static hit ration = 100%.

I was not able to attach. Privet says: "txt extansion is not allowed" :pain1:

I put it here. It must be seen in monospace font:

% FOUND
# PAGES % PAGES REQUESTS LOADS IN POOL
------- ------- -------- ------- -------
FREE PAGES 1,260 8.4
SKCT'S 13 0.1
SKPKG'S 13,209 88.1
CT'S 13 0.1 2,973K 36 100.0
DBD'S 409 2.7 5,409K 98 100.0
PKG'S 96 0.6 100M 21,017 100.0

TOTAL 15,000 109M 21,151 100.0

SQL CACHE 2,500 1,099K 56,025 94.9

DSPACE-USED 2,500
DSPACE-FREE 0 0.0

Dmitry67 · Post by **Dmitry67** » 03 Oct 2003 15:49

А вот как Вы объясните два отстойные вещи в Oracle

1. case insensitive. В век unicode... странное это
В MS SQL collation настраивается на уровне поля (!!!)

2. Вроде в Oracle пустая строка есть NULL

vc · Post by vc » 03 Oct 2003 16:21

Dmitry67 wrote:А вот как Вы объясните два отстойные вещи в Oracle

1. case insensitive. В век unicode... странное это
В MS SQL collation настраивается на уровне поля (!!!)

Please elaborate. It's not clear what you mean.

Dmitry67 wrote:2. Вроде в Oracle пустая строка есть NULL

This is true. Here (as elsewhere), Oracle does not conform to SQL-92.

Rgds.

Привет

Вопрос о базах и кеше.

Вопрос о базах и кеше.

Re: Вопрос о базах и кеше.

Re: Вопрос о базах и кеше.

Re: Вопрос о базах и кеше.

Re: Вопрос о базах и кеше.

Re: Вопрос о базах и кеше.