Сравнение архитектуры двух СУБД.

12.03.24

База данных - Администрирование СУБД

Избранные административные представления.

Предисло вие.

Если человек еще в состоянии обучаться, то это нужно делать систематически, результаты обучения должны быть измеримы, например - подтверждены сертификатом или дипломом. Поэтому в своих публикациях я стараюсь придерживаться тематики экзамена 1С:Эксперт по технологическим вопросам. Поводом для статьи послужил вебинар http://edu.1c.ru/expert/ который произвел на меня превосходное впечатление. Ниже используются материалы вебинара, спасибо Виктору Богачёву.

Рядом с каждым утверждением содержится ссылка на документацию - буквы MS, PG.

 

Избранные административные представления и функции.

Список баз данных.

[ Наименование, идентификатор…]

[ MS Sys.databases (состояние базы данных, доступность, режим RCSI, модель восстановления (full, simple), настройки создания и обновления статистики, отметка о включении Query Stories, …) ]

[ PG pg_database ]

Список индексов.

[ Реквизиты: идентификатор индекса, идентификатор таблицы, уникальность, доступность к использованию, кластеризованность…]

[ MS sys.indexes ]

[ PG pg_index ]

Список статистик.

[Содержит гистограммы…]

[ MS sys.dm_db_stats_histogram ]

[ PG pg_stats ]

Информация о таблицах

[ MS sys.tables ]

[ PG pg_class (содержит информацию по таблицам, индексам, статистикам…)

Пример определения размера таблицы (отношения) и базы данных по списку и спец. функцией:

SELECT
relname, 
relpages, 
reltuples 
FROM pg_class 
ORDER BY relpages DESC
LIMIT 9;
--SELECT pg_database_size(current_database());
--SELECT pg_relation_size('pg_statistic')

]

 

Избранные динамические административные представления и функции.

[ MS Для просмотра DMV требуются права VIEW SERVER STATE, VIEW DATABASE STATE. Данные статистики накапливаются с момента запуска сервера SQL, но некоторые DMV могут быть очищены.

Тексты запросов MS SQL заимствованы из статей (Ian Stirk) Uncover Hidden Data to Optimize Application Performance и Troubleshooting Performance Problems in SQL Server 2005 ]

[ PG для сброса статистики pg_stat_reset(). При перезапуске сервера статистика сохраняется, если нет восстановления БД после запуска. Параметры postgresql.conf, влияющие на сбор статистики:

  • track_activities мониторинг текущих команд

  • track_counts сбора статистики по обращениям к таблицам и индексам.

  • track_functions отслеживание использования пользовательских функций.

  • track_io_timing мониторинг времени чтения и записи блоков.]

Выполнение запросов (Exec)

[ MS

  • sys.dm_exec_query_plan (параметр вызова plan_handle, поле query_plan cодержит представление Showplan…)

  • sys.dm_exec_query_stats (количество выполнений, статистика (total, last, min, max) по времени ЦП, физическому чтению, логическому чтению/записи, времени выполнения, количеству строк, степени параллелизма, объему памяти…)

  • sys.dm_exec_requests (информация о выполняющихся запросах…)

  • sys.dm_exec_sql_text (параметр вызова sql_ handle или plan_handle, содержит текст запроса…)

Пример:

SELECT
[Query plan] = qp.query_plan,
[Query text] = qt.text,
QueryState.[Total Reads],
QueryState.[Execution count]
FROM (SELECT TOP 10
[Total Reads] = (total_logical_reads),
[Execution count] = (qs.execution_count),
[sql_handle] = qs.sql_handle,
[plan_handle] = qs.plan_handle
FROM sys.dm_exec_query_stats AS qs
ORDER BY [Total Reads] DESC) AS QueryState
CROSS APPLY sys.dm_exec_sql_text(QueryState.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(QueryState.plan_handle) AS qp

]

 

[ PG

  • pg_stat_statements (статистика по выполнению запросов: число выполнений, длительность, количество строк, процент использования буферного кеша, количество прочитанных/записанных блоков, время чтения/записи… Чтобы начать собирать статистику, нужно добавить модуль pg_stat_statements  в файле postgresql.conf, строка pg_stat_statements и выполнить CREATE EXTENSION pg_stat_statements. Для очистки используйте SELECT pg_stat_statements_reset();)

Пример:

SELECT
query,
calls,
total_time,
rows, 
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 5;

]

 

Индексы (Index)

[ MS

  • sys.dm_db_index_usage_stats (статистика: количество использований…)

  • sys.dm_db_missing_index_group_stats (возможный выигрыш…)

  • sys.dm_db_missing_index_details (родительская таблица, условия полей…)

  • sys.dm_db_missing_index_groups (содержит связь между dm_db_missing_index_group_stats и dm_db_missing_index_details.)

Пример:

SELECT TOP 10
[Total Cost] = ROUND(Stat.avg_total_user_cost * Stat.avg_user_impact * (Stat.user_seeks + Stat.user_scans),0),
Stat.avg_user_impact,
TableName = Detail.statement,
[EqualityUsage] = Detail.equality_columns,
[InequalityUsage] = Detail.inequality_columns,
[Include Cloumns] = Detail.included_columns
FROM sys.dm_db_missing_index_groups Groups
INNER JOIN sys.dm_db_missing_index_group_stats Stat
ON Stat.group_handle = Groups.index_group_handle
INNER JOIN sys.dm_db_missing_index_details Detail
ON Detail.index_handle = Groups.index_handle
ORDER BY [Total Cost] DESC;

 

Результат работы - список недостающих индексов. Создавать индексы нужно средствами 1С. Соответствие между именами 1С и СУБД - функция "ПолучитьСтруктуруДанных". Информация "Include Cloumns" показывает, что как использовать включенные столбцы индекса в СУБД, но в 1С такой возможности нет - поэтому для полей "Include Cloumns" также создаем индексы.

 
 первая версия
SELECT CURRENT_TIMESTAMP;
use MyDataBaseName;
SELECT top 20
[IndexName] = IndexTable.name,
[user_updates] = StatIndex.user_updates
FROM(SELECT top 50
[object_id] = SizeIndx.[object_id],
[index_id] = SizeIndx.index_id
FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TableName'), NULL, NULL, 'LIMITED') AS SizeIndx
order by SizeIndx.page_count desc
) AS GroupSize
INNER JOIN sys.indexes AS IndexTable
ON GroupSize.[object_id] = IndexTable.[object_id]
AND GroupSize.index_id = IndexTable.index_id
INNER JOIN sys.dm_db_index_usage_stats as StatIndex
ON GroupSize.[object_id] = StatIndex.[object_id]
AND GroupSize.index_id = StatIndex.index_id
WHERE ( StatIndex.user_seeks + StatIndex.user_scans + StatIndex.user_lookups) = 0
order by StatIndex.user_updates desc
SELECT CURRENT_TIMESTAMP;

Запрос работал медленно (дольше 5 минут), поэтому сделал вторую версию. Используется режим Limited, который быстрее Detailed. Выбираются бОльшие по количеству страниц таблицы, затем находятся неиспользуемые индексы. Имена индексов имеют вид _InfoRg18313_ByProperty18332, что несложно расшифровать в 1С. Замеряется время до и после запроса.

 

Запрос использует функцию db_id(), поэтому перед его использованием следует установить текущую базу данных, команда use MyWorkDataBase. Выберите подходящую вам базу. Результат работы - список неиспользуемых индексов больших таблиц, которые могут замедлять запись в таблицу.

]

 

[ PG

  • pg_stat_all_indexes (статистика: количество использований…)

  • pg_statio_all_indexes (statio - данные о чтении страниц с диска и из буфера…)

]

Ввод-вывод (IO)

[ MS

  • sys.dm_io_virtual_file_stats

Пример:

SELECT
[ReadLatency] = CASE WHEN [num_of_reads] > 0
THEN ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] = CASE WHEN [num_of_writes] > 0
THEN ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] = CASE WHEN ([num_of_reads] + [num_of_writes] > 0)
THEN ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
[AvgBPerRead] = CASE WHEN [num_of_reads] > 0
THEN ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] = CASE WHEN [num_of_writes] > 0
THEN ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] = CASE WHEN ([num_of_reads] + [num_of_writes] > 0)
THEN (([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes])) END,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
[mf].[physical_name]
FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
--ORDER BY [WriteLatency] DESC;

]

 

[ PG

  • pg_stat_all_tables

  • pg_statio_all_tables (statio - данные о чтении страниц с диска и из буфера…)

  • pg_stat_database

Пример:

SELECT
datname,
temp_bytes,
deadlocks,
blk_read_time,
blk_write_time,
stats_reset
FROM
pg_stat_database;

Результат работы - список имя базы, объем данных записанный во временные таблицы, количество дедлок, время затраченное на чтение, время затраченное на запись, время начала статистики.

]

Транзакции (Tran)

[ MS

  • sys.dm_tran_locks (Активные блокировки. Описание ресурса, описание блокировки.)

Пример: //infostart.ru/1c/articles/707333/

]

[ PG

  • pg_locks (Активные блокировки. Описание ресурса, описание блокировки.)

Пример (блог разработчика):

SELECT 
locktype, 
mode, 
granted, 
pid, 
pg_blocking_pids(pid) AS wait_for
FROM pg_locks

Результат запроса - тип блокировки, режим, информация об установке, id сессии, id блокирующей сессии.

]

Системные

[ MS

  • sys.dm_os_wait_stats ( Накопленные данные ожидания, длительность. Очистка – при перезапуске или DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)… )

  • sys.dm_os_sys_info ( Физическая память, доступно в буферном кеше… )

  • sys.dm_os_performance_counters ( Счетчики PerfMon. Имя счетчика, текущее значение… )

  • sys.allocation_units (ИД таблицы, количество страниц...)

Пример:

SELECT TOP 10
[Wait type] = wait_type,
[Wait time (s)] = wait_time_ms / 1000,
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;

результат работы: тип ожидания, продолжительность, процент

SELECT TOP 9
  t.Name AS TableName,
  p.Rows AS RowCounts,
  SUM(a.total_pages) * 8 / 1000000 AS TotalSpaceGB,
  SUM(a.used_pages) * 8 / 1000000 AS UsedSpaceGB
FROM
  sys.tables t
  INNER JOIN sys.indexes i ON t.object_id = i.object_id
  INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
  INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE
  t.Name NOT LIKE 'dt%'
  AND t.is_ms_shipped = 0
  AND i.object_id > 255
GROUP BY t.Name, p.Rows
ORDER BY TotalSpaceGB DESC

результат работы: название таблиц, количество строк, всего страниц, используется страниц.

]

 

[ PG

  • pg_stat_activity (Текущие данные, без накопления. База данных, пользователь, тип ожидания, текст запроса…)

  • pg_stat_progress_vacuum (база данных, таблица, фаза очистки, число обработанных кортежей…)

Пример:

SELECT
pid,
wait_event_type,
wait_event
FROM pg_stat_activity
WHERE wait_event is NOT NULL;

список текущих пользователей

SELECT
pid,
datname,
usename,
application_name
FROM pg_stat_activity

чтобы завершить процесс пользователя

SELECT pg_cancel_backend(procpid)

]

 

Обновление, март 2024

SELECT DatabaseName = DB_NAME(),
    TableName = OBJECT_NAME(s.[object_id]),
    IndexName = i.name,
    user_updates,
    system_updates,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
	'drop index ['+i.name+'] ON ['+OBJECT_NAME(s.[object_id])+']' as [Drop]
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id and
                            s.index_id  = i.index_id
WHERE s.database_id = DB_ID() and
    OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 and
    i.is_disabled  = 0 and
    i.is_unique = 0 and
    i.is_primary_key = 0 and
    i.type_desc <> 'HEAP' and
--	i.name like 'missing%'
order by user_seeks desc

 

SELECT TOP 20
    CONVERT (decimal (28, 1), 
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) 
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' + 
        CONVERT (varchar, mig.index_group_handle) + '_' + 
        CONVERT (varchar, mid.index_handle) + ' ON ' + 
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') + 
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' + 
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON 
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON 
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;

 

См. также

Порционный шринк базы

Администрирование СУБД Бесплатно (free)

Скрипт позволяет высвобождать место в операционную систему, занятое файлом базы MS SQL в итерациях с заданным количеством мегабайт

вчера в 12:00    486    Garilia    0    

7

Создаем сценарии обслуживания SQL в Центре Контроля Качества 1С (Центр Администрирования)

Администрирование СУБД Платформа 1С v8.3 Бесплатно (free)

Данная статья научит вас, как создавать скрипты обслуживания MS SQL для Центра Контроля Качества (ЦКК) или Центра Администрирования (ЦА).

20.03.2024    539    Silenser    0    

4

Поинтегрируем: сервисы интеграции – новый стандарт или просто коннектор?

Обмен между базами 1C Администрирование СУБД Механизмы платформы 1С Платформа 1С v8.3 Бесплатно (free)

В платформе 8.3.17 появился замечательный механизм «Сервисы интеграции». Многие считают, что это просто коннектор 1С:Шины. Так ли это?

11.03.2024    4624    dsdred    53    

73

Инструкция по установке Postgres для OLTP приложений и 1С. Часть 1. Базовая конфигурация

Администрирование СУБД Платформа 1С v8.3 Бесплатно (free)

В Postgres достаточно подробная документация, и, видимо, поэтому при инсталляции Postgres для 1С большинство параметров приходится выставлять самим. Параметров в Postgres много, а составить эффективную комбинацию не так просто. Все упрощается, если рассмотреть профиль нагрузки, например, 1С это прежде всего профиль OLTP нагрузки – так устроены его метаданные (объекты). Если сосредоточиться на оптимизации профиля OLTP, понимание Postgres сразу упростится.

15.02.2024    2342    1CUnlimited    14    

27

Очистка устаревших патчей в конфигурациях на базе БСП

Администрирование СУБД Бесплатно (free)

В этой короткой заметке хочется рассказать о быстром и удобном способе удаления расширений-патчей как в типовых конфигурациях, так и любых других, использующих БСП.

01.02.2024    1456    Sergey1CSpb    20    

16

Как запустить сервер лицензирования 1С на примере облачной платформы

Администрирование СУБД Россия Бесплатно (free)

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

25.01.2024    1668    doctor_it    15    

17

Обслуживание индексов MS SQL Server: как, когда и, главное, зачем?

Администрирование СУБД Бесплатно (free)

Казалось бы, базовое знание: «индексы надо обслуживать, чтобы запросы выполнялись быстро». Но обслуживание индексов выполняется долго и может мешать работе пользователей. Кроме того, в последнее время популярны разговоры о том, что индексы можно вообще не обслуживать – насколько это оправданно? Рассмотрим: на что влияет обслуживание индексов, когда надо и когда не надо его выполнять, и если надо – как это сделать так, чтобы никому не помешать?

16.01.2024    5832    Филин    13    

45

Резервное копирование журнала транзакций, наконец-то!

Архивирование (backup) Администрирование СУБД Россия Бесплатно (free)

Постараюсь объяснить, зачем нужно резервное копирование именно журнала транзакций, а не только базы данных, и почему я словно сбросил груз, настроив его - как, покажу, естественно. Кстати, будут скрипты T-SQL (с подробными комментариями) - отличный способ сделать администрирование базы более уютным.

04.12.2023    5886    n_mezentsev    15    

25
Вознаграждение за ответ
Показать полностью
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Hatson 528 09.09.20 13:03 Сейчас в теме
Схожу ка я лучше свечку поставлю... чтобы уберегла меня от этих Postgre и других гомункулов
2. Sedaiko 572 14.09.20 12:20 Сейчас в теме
(1) Как то пришел монтажник кабель второго провайдера тянуть.
Увидев, что в качестве маршрутизатора стоит Mikrotik, сказал:
- Не люблю Mikrotik, мне больше Zyxel нравится.
- Может Вы просто не умеете его настраивать?, - спросил я.
- Да.
3. kiset 14.09.20 12:47 Сейчас в теме
Отрадно видеть, что ты продолжаешь развиваться и углублять свои познания в этой тематике. :)
4. vasilev2015 2687 24.08.21 09:45 Сейчас в теме +5 $m
Тема оказалась узко-специализированной, обсуждение не произошло.
Поэтому вознаграждение отзываю.
Оставьте свое сообщение