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

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;

 

См. также

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

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

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

11.03.2024    3625    dsdred    48    

66

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

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

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

15.02.2024    2276    1CUnlimited    14    

27

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

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

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

01.02.2024    1310    Sergey1CSpb    20    

15

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

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

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

25.01.2024    1546    doctor_it    15    

17

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

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

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

16.01.2024    5518    Филин    11    

44

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

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

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

04.12.2023    5711    n_mezentsev    15    

23

Дефрагментация индексов MS SQL для платформы 8.3.22

Инструменты администратора БД Администрирование СУБД Абонемент ($m)

Начиная с 8.3.22 фирма "1С" убрала блокировки БД на уровне страниц; если БД расположена в MS SQL, то стала возникать проблема при дефрагментации индексов с использованием команды ALTER INDEX REORGANIZE. Предлагаю модификацию известного скрипта, который позволяет обойти эту проблему.

1 стартмани

15.11.2023    2085    17    baturo    6    

12

Мигрируем с MS SQL на PostgreSQL

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

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

13.11.2023    10406    ivanov660    31    

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