Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Проблема с удалением записей из большой таблицы  [new]
yvmster
Member

Откуда:
Сообщений: 29
MS SQL Server 2005.
Делаю удаление из большой таблицы (30 млн записей) по одной записи. В цикле из внешнего приложения запускается хранимка, которая удаляет очередную запись. Каждый раз делаю отдельный коннект к базе.

Имеется Таблица Entry
поля:
EntryID int (кластеризованный индекс)
+ полей 20 разных типов (int, varchar(256), datetime, XML и тд )
из них 5 имеют некластеризованные индексы (date, int)

Код процедуры:
ALTER PROCEDURE [dbo].[deleteEntry]
(
@entryID int
)
AS
BEGIN
		delete from Entry where EntryID=@entryID;
END

Проблема в том, что скорость удаления записей постепенно замедляется. Вначале удаляется около 100 записей в секунду. Минут через 5 удаление зависает. Каждая запись удаляется по 5 минут и больше.

никакие другие запросы в базе не выполняются.

С чем это может быть связано? Можно ли добиться стабильного удаления по одной записи?
1 июн 09, 17:45    [7252854]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3264
эта такое извращение, да ? по одной записи из клиентского приложения.
а причин до фига, от full режима лога, до деланья, а ещё хуже множественно открытых транзакциях,до перестройки индексов и т.д в c# сказалиб что это индусокод.
1 июн 09, 17:55    [7252924]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
а зачем их вообще удалять?
пусть себе лежат в базе...
1 июн 09, 17:56    [7252938]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5181
Ken@t
эта такое извращение, да ? по одной записи из клиентского приложения.

+1! ;))


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

пример со вставкой, но можно переписать и на удаление
1 июн 09, 18:18    [7253037]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
yvmster
Member

Откуда:
Сообщений: 29
komrad

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

пример со вставкой, но можно переписать и на удаление


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

при удалении больше 100 записей возникает Lock:Escalation, который блокирует таблицу. А это недопустимо.
Удаление одной записи создает блокировки ключей. + 10 записей в системной таблице блокировок.
удаление 100 записей создает 50000 записей в таблице блокировок. Следовательно, такой запрос отрабатывает медленно.

Новые записией 150000 в сутки.. почти равномерно распределены

Пришел к выводу что лучше удалять по одной записи.. и вот столкнулся еще вот с такой проблемой.

в postgres такой проблемы не было...

по 1000 записей не катит.. блокируется таблица..
4 июн 09, 15:37    [7265848]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
yvmster
Member

Откуда:
Сообщений: 29
удаление по одной записи провожу последовательно.

как узнать что именно держит удаление одной записи больше 5 минут?
4 июн 09, 15:40    [7265873]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
хорошо, задам свой вопрос по-другому

что будет, если Вы вообще не будете удалять данные?
4 июн 09, 16:13    [7266090]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
yvmster
Member

Откуда:
Сообщений: 29
1. не хватит места на диске.
2. производительность падает.. так как таблица сильно разрастается
4 июн 09, 16:15    [7266102]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
yvmster
Новые записией 150000 в сутки
То есть около 55 млн записей в год
Допустим, одна запись 100 байт
Итого в год от силы 6 гиг
Пусть еще столько же на индексы

Это что, объемы, да?
4 июн 09, 16:19    [7266131]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
производительность чего, простите, падает?
имхо наоборот, если Вы не будете тратить ресурсы сервера на удаление данных, то может у него будет больше ресурсов на обслуживание выборок

ну и кроме того, есть индексирование (в т.ч. представлений), есть секционирование,
и вообще производительность - отдельная тема

кроме того, падение производительности на 10% и потеря 10% данных (которые гипотетически могут понадобиться) - какое из зол больше?
4 июн 09, 16:22    [7266151]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
yvmster
Member

Откуда:
Сообщений: 29
Паганель
yvmster
Новые записией 150000 в сутки
То есть около 55 млн записей в год
Допустим, одна запись 100 байт
Итого в год от силы 6 гиг
Пусть еще столько же на индексы

Это что, объемы, да?


база уже весит 50 г. за 6 мес. и кол-во новых записей каждый месяц растет. Ориентировочно будет 500 тыс в сутки.
основной момент - это производительность. А она уже ощутимо упала из-за размера базы.
4 июн 09, 16:25    [7266166]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
yvmster
база уже весит 50 г
То есть за год будет 100 гиг
При том, что почти любой студент может себе позволить полтерабайтный винчестер много ли это?

и какую часть из этих 50г занимает таблица Entry?
А индексы на ней?
А сколько в ней сейчас записей?
И самое главное - откуда Вы узнали, что производительность связана с объемом этой таблицы?
На основании планов каких-то запросов?
4 июн 09, 16:32    [7266209]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
yvmster
Member

Откуда:
Сообщений: 29
вообще есть еще и бекапирование. с ней проблем нет.
удалять все равно нужно.
Секционирование не подходит для нашей задачи. уже её рассмотрели.

производительность на формирование отчетов и тп. Необходимо чтобы в базе оставались записи только 2х месяцев.

может все таки как нибудь можно оптимизировать удаление не блокируя таблицу?
по крайней мере удаление можно выполнять когда сервер разгружен (ночью).
4 июн 09, 16:32    [7266210]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
yvmster
Секционирование не подходит для нашей задачи
yvmster
Необходимо чтобы в базе оставались записи только 2х месяцев.
я не спец по секционированию именно в MS SQL
(довелось с этим только под Oracle столкнуться)
но по-моему эти две фразы друг другу противоречат
4 июн 09, 16:35    [7266229]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
yvmster
Member

Откуда:
Сообщений: 29
Паганель

То есть за год будет 100 гиг
При том, что почти любой студент может себе позволить полтерабайтный винчестер много ли это?

и какую часть из этих 50г занимает таблица Entry?
А индексы на ней?
А сколько в ней сейчас записей?
И самое главное - откуда Вы узнали, что производительность связана с объемом этой таблицы?
На основании планов каких-то запросов?


50г и занимает только таблица Entry с индексами ))
таблица 70% индексы 30%

Вообще это задача любой платежной системы) очистка ненужных платежей.. причем ненужные записи уже лежат в бекапе..
4 июн 09, 16:36    [7266237]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
yvmster
Member

Откуда:
Сообщений: 29
Паганель
yvmster
Секционирование не подходит для нашей задачи
yvmster
Необходимо чтобы в базе оставались записи только 2х месяцев.
я не спец по секционированию именно в MS SQL
(довелось с этим только под Oracle столкнуться)
но по-моему эти две фразы друг другу противоречат


да.. малость противоречат) но бекапируем мы базу польностью со всеми справочниками и тп. поэтому в секционирование нам не нужно, так как старые данные сохранены...
4 июн 09, 16:40    [7266265]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
35 гиг за 6 месяцев - это около 200 мегабайт в сутки
делим на 500 тыс - получаем около 400 байт на запись
многовато

таблица денормализована?
4 июн 09, 16:43    [7266295]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
yvmster
Member

Откуда:
Сообщений: 29
Паганель
35 гиг за 6 месяцев - это около 200 мегабайт в сутки
делим на 500 тыс - получаем около 400 байт на запись
многовато

таблица денормализована?


есть xml поля которые содержат данные..
4 июн 09, 16:46    [7266313]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36800
Так я и не понял, почему вам не нужно секционироание.
4 июн 09, 16:49    [7266339]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
yvmster
секционирование нам не нужно
уф, нашел: Работа с большим оемом данных

Ну и конечно Секционированные таблицы и индексы SQL Server 2005
4 июн 09, 16:51    [7266365]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 887
Если у тебя поле EntryID оформлен как кластеризованный индекс, то по одному значению большой объем сразу удалять -это зло, он же потом идет перестраиваться.Удаляй промежутками EntryID.
4 июн 09, 21:35    [7267557]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
Что-то мне подсказывает, что у автора какая-то ERP-система :)
Если не секрет, какая ?
5 июн 09, 12:03    [7269155]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
Kew
Member

Откуда:
Сообщений: 377
Да, вроде, платежная система-то? Кастомная, наверное?
И в чем проблема с секционированием -- каким местом оно не вписывается, непонятно?
5 июн 09, 12:10    [7269192]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
в таблице sys.processes есть колонка blocked, в ней чтонибудь написано, в то время, пока выполняется команда delete (в течение 5 минут которая)?

для спящего время бодрствования равносильно сну
5 июн 09, 12:45    [7269383]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с удалением записей из большой таблицы  [new]
yvmster
Member

Откуда:
Сообщений: 29
вобщем sp_lock показал:
что при зависании удаления появляются блокировки DB (на всю базу) с типом NULL
BULKOP_BACKUP_DB
BULKOP_BACKUP_LOG
все запросы ждут этого процесса. Он отмирает минут через 5 и дальше опять можно заняться удалением по одной записи.

DATABASE.BULKOP_BACKUP_DB - Создание резервных копий базы данных с помощью массовых операций.
DATABASE.BULKOP_BACKUP_LOG - Создание резервных копий журнала базы данных с помощью массовых операций.

Но почему они блокируют всю базу не понятно.
5 июн 09, 13:40    [7269719]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить