Tmp sql что это

Управление временным табличным пространством (temporary tablespace) в Oracle MySQL

На первый взгляд может показаться, что тема не стоит и выеденного яйца, но при детальном рассмотрении вопросом управления временным табличным пространством (temporary tablespace) в Oracle MySQL нужно вовремя озаботиться — это позволит избежать появления различных проблем в будущем.

Давайте рассмотрим что такое временное табличное пространство (temporary tablespace) в Oracle MySQL, как им управлять и какие проблемы нас могут ждать если мы вовремя не настроим некоторые параметры.

Исходные данные: Oracle MySQL 5.7.25 на Debian Linux

Для начала как всегда ссылка на первоисточник информации — официальная документация по Oracle MySQL

А теперь небольшая предыстория, почему нужно знать и уметь управлять временным табличным пространством (temporary tablespace). В один прекрасный зимний вечер ко мне в скайп постучался клиент с криком: «Михаил, помоги, только что на сервере с MySQL на диске почти моментально закончилось место, база еле работает, мы не можем понять куда исчезли 200GB свободного места». Я подключаюсь к серверу и довольно быстро нахожу источник поедания места, вот так это выглядело:

Все место на диске занял файл /var/lib/mysql/ibtmp1

В данном файле MySQL содержит временное табличное пространство (temporary tablespace). Данные во временном табличном пространстве имеют временный характер и существуют только на протяжении существования сеанса пользователя. Как правило MySQL использует временное табличное пространство для хранения временных данных во время выполнении операции сортировки при выполнении запросов пользователей или операции сортировки при создании индексов и т.п. При нормальном завершении работы MySQL файл временного ТП удаляется, а при запуске создается новый файл с новым идентификатором.

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

Итак, давайте вначале посмотрим лог mysql на предмет упоминания в нем создания файла временного ТП:

Вот те самые строки в который MySQL при запуске создает файл ibtmp1, все довольно просто, быстро и лаконично.

Теперь посмотрим настройки временного ТП:

Тут мы видим, что у нас создается 1 файл для временного ТП размером 12 МБ, с авторасширением и без ограничения размера.

Более детальную информация о временном ТП можно посмотреть таким запросом:

Результаты запросов SELECT @@datadir; и SELECT @@innodb_data_home_dir; нам тоже важны и нужны, но о них мы поговорим чуть ниже.

Как Вы уже поняли, для управления настройками временного ТП используется настройка innodb_temp_data_file_path (по ссылке Вы можете прочитать детальную информацию)

Давайте ограничим размер роста временного ТП на уровне 10 GB, для этого в файле конфигурации /etc/mysql/mysql.conf.d/mysqld.cnf в секции [mysqld] пропишем:

Максимальный размер временного ТП указывается последним параметром с добавлением буквы K, M или G (KB, MB или GB (1024MB)).

Для применения настроек нужно перезапустить MySQL, после этого проверим состояние и параметры временного ТП с помощью моего запроса, результат:

Мы видим, что FILE_ID стал уже 38 и MAXIMUM_SIZE_MB стал теперь 10240 МБ.

Таким образом мы ограничили рост временного ТП до разумного предела и оно уже не заполнит нам весь диск.
Конечно помимо ограничения размера нужно еще постоянно вести мониторинг временного ТП и если оно заполниться, то необходимо отстреливать сессии которые потребляют все временное ТП.

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

Давайте создадим 2 файла для временного ТП: 1 файл фиксированного размером 512 MB и разместим его на отдельном разделе смонтированном к каталогу /var/lib/mysql_tmp, а второй как обычно начальным размером 12 MB и с авторасширением до 10 GB, второй файл оставим в каталоге /var/lib/mysql:

1. Смонтируем новый диск и установим владельца на каталог /var/lib/mysql_tmp:

2. Внесем настройки в файл конфигурации /etc/mysql/mysql.conf.d/mysqld.cnf в секцию [mysqld]:

Я не спроста смонтировал новый диск к каталогу /var/lib/mysql_tmp, т.к. в настройке innodb_temp_data_file_path путь до файла ibtmp1 нужно указывать относительно каталога datadir (по умолчанию /var/lib/mysql).

P.S. Если мы используем настройку innodb_data_home_dir, то тогда путь к файлам временного ТП будет указываться относительно пути из innodb_data_home_dir. По умолчанию innodb_data_home_dir = NULL (в самом начале статьи мы выводили эту настройку), то есть все системные табличные пространства хранятся в datadir (по умолчанию в /var/lib/mysql).

Для применения настроек нужно перезапустить MySQL, после этого проверим состояние и параметры временного ТП:

У нас все получилось, создалось 2 файла временного ТП, один из них /var/lib/mysql_tmp/ibtmp1 фиксированного размера 512 MB без возможности дальнейшего роста, а второй /var/lib/mysql/ibtmp2 размером 12 MB с авторасширением до 10 GB.

Хочу предупредить желающих сделать размер временного ТП сразу большого размера, т.к. временные ТП создаются при старте MySQL и удаляются при остановке, то создание большого файла может занимать продолжительное время, особенно если у вас не самые быстрые диски.

Еще одна немаловажная настройка — это инкремент увеличения размера временного ТП (колонка AUTOEXTEND_SIZE_MB в моем запросе), по умолчанию он равен 64 МБ, если Вам нужно увеличить это значение, то воспользуйтесь настройкой innodb_autoextend_increment

Для применения настроек нужно перезапустить MySQL, после этого проверим состояние и параметры временного ТП с помощью моего запроса, результат:

Мы видим, что AUTOEXTEND_SIZE_MB стал 128 МБ.

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

Источник

Временные таблицы sql @tmp vs #tmp

В чем разница между двумя типами временных таблиц @tmp vs #tmp в SQL 2005? и их другие типы, о которых я не знаю?

5 ответов

#tmp является временной таблицей и в основном действует как настоящая таблица. Может иметь индексы, может иметь статистику, участвовать в транзакциях, оптимизатор вырабатывает правильные оценки строк

@tmp переменная таблицы Без индексов, без статистики, без учета транзакций, оптимизатор всегда предполагает ровно 1 строку

О ключах на табличных переменных. Они не имеют значения. Там нет статистики и предполагается один ряд. Это изменит сканирование таблицы на сканирование кластерного индекса, которое будет таким же. Проверьте любой план запроса и предполагаемые строки.

Первым делом я поместил первичный ключ в табличную переменную @ComputersToProcess. Это превратило сканирование таблицы в сканирование кластерного индекса, но ничего не сделало для производительности.

Табличные переменные имеют следующие преимущества перед временными таблицами:

Вот некоторые недостатки по сравнению с временными таблицами:

Являются ли табличные переменные структурами только для памяти, которые гарантируют лучшую производительность по сравнению с временными или постоянными таблицами, потому что они хранятся в базе данных, которая находится на физическом диске?

Для практического сравнения производительности, см. Также:

Просто добавляю на существующие ответы. Там на самом деле 3 типа временных таблиц. В дополнение к другим ответам вы можете создавать глобальные временные таблицы, например ##globalTempTable

Они видимы для всех подключений к серверу sql и используются редко, однако полезно отметить, что они действительно существуют.

Вот хорошее прочтение о разнице между стандартными и глобальными временными таблицами http://www.codeproject.com/KB/database/TempTable.aspx

@tmp относится к переменной таблицы типов, хранящейся в памяти, тогда как #tmp относится к таблице в TEMP база данных.

Источник

tempdb, база данных

Системная база данных tempdb — это глобальный ресурс, доступный всем пользователям, подключенным к экземпляру SQL Server или Базе данных SQL Azure. tempdb содержит:

Временные пользовательские объекты, созданные явно. К ним относятся глобальные или локальные временные таблицы и индексы, временные хранимые процедуры, табличные переменные, возвращаемые функциями с табличными значениями таблицы и курсоры.

Внутренние объекты, создаваемые ядром СУБД. К ним относятся следующие:

Каждый внутренний объект использует минимум девять страниц: страницу IAM и восьмистраничный экстент. Дополнительные сведения см. в разделе Страницы и экстенты.

Отдельные базы данных и эластичные пулы Базы данных SQL Azure поддерживают глобальные временные таблицы и глобальные временные хранимые процедуры, которые хранятся в tempdb и имеют область действия на уровне базы данных.

Глобальные временные таблицы и глобальные временные хранимые процедуры являются общими для всех сеансов пользователей в рамках одной базы данных SQL. Сеансы пользователей, связанные с другими базами данных SQL, не имеют доступа к глобальным временным таблицам. Дополнительные сведения см. в разделе Глобальные временные таблицы (база данных SQL Azure) в области базы данных. Управляемый экземпляр SQL Azure поддерживает те же временные объекты, что и SQL Server.

Для Управляемого экземпляра SQL Azure применяются все системные базы данных.

Хранилища версий. Это коллекции страниц данных со строками данных, которые поддерживают функции управления версиями строк. Существует два типа хранилищ: общее хранилище версий и хранилище версий для построения индекса в подключенном режиме. Хранилища версий содержат следующее:

Операции в tempdb в минимальном объеме записываются в журнал, что позволяет откатывать транзакции. tempdb создается заново при каждом запуске SQL Server, чтобы система всегда запускалась с чистой копией базы данных. Временные таблицы и хранимые процедуры удаляются автоматически при отключении, и при выключении системы нет активных соединений.

tempdb не требует сохранения каких-либо данных между сеансами SQL Server. Операции резервного копирования и восстановления для tempdb недопустимы.

Физические свойства tempdb в SQL Server

Количество вторичных файлов данных зависит от числа логических процессоров на компьютере. Как правило, если число логических процессоров меньше или равно восьми, используйте равное ему число файлов данных. Если число логических процессоров больше восьми, используйте восемь файлов данных. Если состязание сохраняется, увеличьте число файлов данных на значение, кратное четырем, пока состязание не снизится до приемлемого уровня, или внесите изменения в рабочую нагрузку или код.

Количество файлов данных по умолчанию основано на общих рекомендациях, приведенных в статье KB 2154845.

Перемещение данных и файлов журналов базы данных tempdb в SQL Server

Сведения о перемещении файлов журналов и данных tempdb см. в статье Перемещение системных баз данных.

Параметры базы данных для tempdb в SQL Server

База данных tempdb в Базе данных SQL

Размеры базы данных tempdb для уровней служб на основе DTU

Размеры базы данных tempdb для уровней служб на основе виртуальных ядер

Ограничения

С базой данных tempdb нельзя выполнять следующие операции:

Разрешения

Оптимизация производительности базы данных tempdb в SQL Server

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

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

Файлы данных в каждой файловой группе должны иметь одинаковый размер, так как SQL Server использует алгоритм пропорционального заполнения, который повышает вероятность выделения памяти в файлах с большим объемом свободного пространства. Разделение tempdb на множество файлов данных равного размера обеспечивает эффективное выполнение использующих tempdb операций с высокой степенью параллелизма.

Поместите базу данных tempdb в быструю подсистему ввода-вывода. Если имеется много непосредственно присоединенных дисков, то используйте чередование дисков. Отдельные файлы данных tempdb или их группы не обязательно должны располагаться на разных дисках или шпинделях, если только у вы не наблюдаете узкие места в подсистеме ввода-вывода.

Расположите базу данных tempdb на дисках, отличающихся от используемых пользовательскими базами данных.

Увеличение производительности базы данных tempdb в SQL Server

Начиная с версии SQL Server 2016 (13.x);, производительность tempdb дополнительно оптимизирована следующим образом:

Дополнительные сведения об улучшениях производительности в tempdb см. в статье блога TEMPDB — Files and Trace Flags and Updates, Oh My! (TEMPDB — файлы, флаги трассировки и обновления).

Оптимизированные для памяти метаданные tempdb

Состязание метаданных tempdb всегда было узким местом для масштабируемости многих рабочих нагрузок, выполняющихся в SQL Server. В SQL Server 2019 (15.x) появилась новая функция оптимизированных для памяти метаданных tempdb, входящая в семейство функций выполняющейся в памяти базы данных.

Она эффективно устраняет существующее узкое место и открывает новый уровень масштабируемости для рабочих нагрузок, активно использующих tempdb. В SQL Server 2019 (15.x) системные таблицы, связанные с управлением метаданными временных таблиц, можно переместить в неустойчивые таблицы без кратковременной блокировки, оптимизированные для памяти.

Сейчас функция оптимизированных для памяти метаданных tempdb недоступна для Базы данных SQL Azure и Управляемых экземпляров SQL Azure.

Просмотрите это 7-минутное видео, чтобы узнать, как и когда следует использовать метаданные tempdb, оптимизированные для памяти:

Настройка и использование метаданных оптимизированной для памяти базы данных tempdb

Чтобы согласиться на применение этой новой функции, используйте следующий скрипт:

Чтобы это изменение конфигурации вступило в силу, нужно перезапустить службу.

Вы можете проверить, является ли tempdb оптимизированной для памяти, используя следующую команду T-SQL:

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

Ограничения оптимизированной для памяти базы данных tempdb

Отдельная транзакция не может обратиться к таблицам, оптимизированным для памяти, в более чем одной базе данных. Все транзакции, связанные с таблицей, оптимизированной для памяти, в пользовательской базе данных, не смогут обратиться к системным представлениям tempdb в той же транзакции. Если вы попытаетесь обратиться к системным представлениям tempdb в транзакции с участием таблицы, оптимизированной для памяти, в пользовательской базе данных, возникнет следующая ошибка:

Запросы к таблицам, оптимизированным для памяти, не поддерживают указания блокировки и изоляции, поэтому запросы к представлениям каталога оптимизированной для памяти tempdb не будут учитывать указания блокировки и изоляции. Как и в случае с другими системными представлениями каталога в SQL Server, все транзакции для системных представлений будут находиться в изоляции READ COMMITTED (или READ COMMITTED SNAPSHOT в нашем случае).

Если оптимизированные для памяти метаданные tempdb включены, индексы columnstore нельзя создавать во временных таблицах.

Планирование ресурсов для tempdb в SQL Server

Определение требуемого размера tempdb в рабочей среде SQL Server зависит от многих факторов. Как описано выше, эти факторы включают текущую рабочую нагрузку и используемые функции SQL Server. Рекомендуется проанализировать текущую рабочую нагрузку, выполнив следующие задачи в среде тестирования SQL Server:

Источник

Темпоральные таблицы

В SQL Server 2016 добавлена поддержка темпоральных таблиц (темпоральных таблиц с системным управлением версиями). Являясь встроенным компонентом базы данных, эти таблицы предоставляют сведения о хранящихся в них данных на любой, а не только на текущий момент времени. Темпоральные функции базы данных впервые появились в ANSI SQL 2011.

Краткое руководство

Начало работы

Примеры:

Синтаксис

Что такое темпоральная таблица с системным управлением версиями

Темпоральная таблица с системным управлением версиями — это тип пользовательской таблицы. Она ведет журнал всех изменений данных, что позволяет легко выполнять анализ для определенной точки во времени. Этот тип темпоральной таблицы называется темпоральной таблицей с системным управлением версиями, так как периодом действия каждой строки управляет система (т. е. ядро СУБД).

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

Зачем нужны темпоральные таблицы

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

Как работают темпоральные таблицы

Системное управление версиями для таблицы реализовано в виде пары таблиц — текущей таблицы и таблицы журнала. В каждой из этих таблиц используются следующие два дополнительных столбца datetime2 для определения периода действия для каждой записи.

В текущей таблице содержится текущее значение для каждой строки. В таблице журнала содержатся все предыдущие значения для каждой строки и время начала и время окончания периода, в котором действовали эти значения (если они заданы).

В следующем простом примере показан сценарий с информацией в таблице Employee в гипотетической базе данных HR.

Как выполнить запрос для темпоральных данных

Инструкция SELECT для предложения FROM имеет новое предложение FOR SYSTEM_TIME с пятью вложенными предложениями для темпоральных таблиц, которое позволяет запрашивать данные в текущей таблице и в таблице журнала. Этот новый синтаксис инструкции SELECT поддерживается непосредственно для одной таблицы, распространяется через несколько соединений и представлений на основе нескольких темпоральных таблиц.

Приведенный ниже запрос ищет версии строки о сотруднике с EmployeeID = 1000, которые были активны по крайней мере часть времени между 1 января 2014 г. и 1 января 2015 г. (включая верхнюю границу периода).

FOR SYSTEM_TIME отфильтровывает строки, которые имеют срок действия с нулевой продолжительностью (SysStartTime = SysEndTime). Эти строки будут созданы, если выполнить несколько изменений одного и того же первичного ключа в одной и той же транзакции. В этом случае темпоральные запросы возвращают только версии строк до выполнения транзакций и версии строк, которые становятся действительными после выполнения транзакций. Если необходимо включить эти строки в анализ, выполните запрос к таблице журнала напрямую.

В таблице ниже SysStartTime в столбце «Подходящие строки» представляет значение в столбце SysStartTime опрашиваемой таблицы, а SysEndTime представляет значение в столбце SysEndTime опрашиваемой таблицы. Полный синтаксис и примеры см. в разделах FROM (Transact-SQL) и Запрос данных в темпоральной таблице с системным управлением версиями.

Источник