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

Откуда: Киев
Сообщений: 40
Подскажите пожалуйста как лучше организовать структуру базы и инсерт.
1 таблица будет иметь 200-500 млн. id(int primary key):pass(varchar(40))
другие 5-10 таблиц будут иметь формат hash:pass_id

INSERT первых 30 млн записей в 1-ую таблицу были без проблем, но потом скорость инсерта начала падать

с самого начала была такая скорость:
автор
C:\passwords\ResultFiles>php -d memory_limit=2000M inserter_plaintext.php
Connection established.
Loaded ./0...9999999/xaa (3000000 lines)...
File: ./0...9999999/xaa Rows: 3000000 Errors:0 Time: 1221.7357051373
Loaded ./0...9999999/xab (3000000 lines)...
File: ./0...9999999/xab Rows: 3000000 Errors:0 Time: 1161.3827161789
Loaded ./0...9999999/xac (3000000 lines)...
File: ./0...9999999/xac Rows: 3000000 Errors:0 Time: 1145.4444630146
Loaded ./0...9999999/xad (2111110 lines)...
File: ./0...9999999/xad Rows: 2111110 Errors:0 Time: 827.44488310814
Loaded ./0...ffffff/xaa (3000000 lines)...
File: ./0...ffffff/xaa Rows: 3000000 Errors:333333 Time: 1344.5513880253
Loaded ./0...ffffff/xab (3000000 lines)...
File: ./0...ffffff/xab Rows: 3000000 Errors:288889 Time: 1327.0465590954
Loaded ./0...ffffff/xac (3000000 lines)...
File: ./0...ffffff/xac Rows: 3000000 Errors:277778 Time: 1315.9778511524
Loaded ./0...ffffff/xad (3000000 lines)...
File: ./0...ffffff/xad Rows: 3000000 Errors:211110 Time: 1291.5061349869



потом после 40млн:
автор
C:\passwords\ResultFiles>php -d memory_limit=3000M inserter_plaintext.php
Connection established.
Loaded ./decrypt.fr/xap (3000000 lines)...
File: ./decrypt.fr/xap Rows: 3000000 Errors:969026 Time: 7270.1742930412
Loaded ./decrypt.fr/xaq (3000000 lines)...
File: ./decrypt.fr/xaq Rows: 3000000 Errors:927607 Time: 13973.113543987




сначала был 1 файл базы в 1 файловой группе PRIMARY.
когда инсерт начал зависать(на тот момент размер mdf был 5.95гб) я создал ещё один файл в этой же файловой группе.
но скорость все равно осталась ужасной...

посоветуйте что сделать? а то купил 2 книги, и ничего не нашел по теме :(
2 мар 12, 00:57    [12180542]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Может вы скрипты таблиц с индексами покажете? И запросы, которыми вы данные вставляете?
2 мар 12, 01:09    [12180556]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
alexboesky
когда инсерт начал зависать(на тот момент размер mdf был 5.95гб) я создал ещё один файл в этой же файловой группе.
Автоприращение файла 10%, да?
2 мар 12, 01:09    [12180557]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
автоприращение 1гб для mdf, а для log 10%...

я в SSMS указал уникальным индекcом поле plain_text.pass, а id primary key int1
2 мар 12, 02:32    [12180659]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
alexboesky
я в SSMS указал уникальным индекcом поле plain_text.pass, а id primary key int1
Партизан?
2 мар 12, 02:36    [12180664]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
и идеале было бы не плохо чтоб таблица plain_text имела хотя бы 1млрд записей, размер на диске не принципиален, щас hdd 3Tb...
может можно как-то индексы вынести в отдельный файл? или подключить для индексов дополнительный SSD винчестер?
2 мар 12, 02:37    [12180666]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
Гавриленко Сергей Алексеевич,

что значит партизан? :D
2 мар 12, 02:39    [12180667]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
alexboesky
Гавриленко Сергей Алексеевич,

что значит партизан? :D
Скрипты из вас клещами не вытянуть. Стране нужны такие партизаны.
2 мар 12, 02:49    [12180678]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
Sack
Member

Откуда:
Сообщений: 163
alexboesky,
Создание скриптов для таблиц в SSMS
2 мар 12, 04:13    [12180721]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
автор
посоветуйте что сделать?


Руководство по производительности загрузки данных
2 мар 12, 09:28    [12181090]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
USE [hashdb]
GO

/****** Object:  Table [dbo].[plain_text]    Script Date: 03/02/2012 11:14:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[plain_text](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[pass] [nvarchar](32) NOT NULL,
 CONSTRAINT [PK_plain_text] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_plain_text] UNIQUE NONCLUSTERED 
(
	[pass] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
2 мар 12, 14:15    [12183408]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
alexboesky,

записи как вставляете?
2 мар 12, 14:18    [12183431]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
INSERT INTO plain_text([pass]) VALUES('..........')
2 мар 12, 14:41    [12183643]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
Aleksey V.P.
Member

Откуда: Москва
Сообщений: 575
alexboesky
и идеале было бы не плохо чтоб таблица plain_text имела хотя бы 1млрд записей, размер на диске не принципиален, щас hdd 3Tb...
может можно как-то индексы вынести в отдельный файл? или подключить для индексов дополнительный SSD винчестер?


Да. Создай дополнительные файловые группы, выноси туда индексы. Ещё, какими объёмами вставляются новые записи, иными словами по сколько штук за один раз? Ещё, убери нафик CLUSTERED на primary key.
2 мар 12, 14:53    [12183761]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
alexboesky
INSERT INTO plain_text([pass]) VALUES('..........')
Такими запросами сервер будет 500 милионов раз принимать команду, выполнять синтаксический разбор, искать планы в кеше, блокировать ресурсы, писать данные для отката в лог транзакций и т.д.

Как минимум объедините пачки инструкций в блоки begin tran... commit tran

А лучьше вставляйте данные в временную таблицу (тоже пакетами) и оттуда в основную. При этом такие действия можно сделать в нескольких потоках.

Пакеты подбирайте экспериментально, допустим 10000 для начала.

Вставки всё равно будут тяжёлые, т.к. будет случайная вставка для индекса на pass, но всё равно скорость можно будет увеличить раз в 10-100

Ну и хороший вариант - вставка bulk insert, но это зависит от логики приложения.
2 мар 12, 15:16    [12184021]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
Aleksey V.P.
Ещё, убери нафик CLUSTERED на primary key.
С чего это? Чтобы ещё замедлить построение инджекса для pass?
2 мар 12, 15:17    [12184030]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
Aleksey V.P
Ещё, убери нафик CLUSTERED на primary key.

С какой радости?
2 мар 12, 15:19    [12184044]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
foreach($f as $k=>$v)
{
	$pass=trim($v);
	if(strlen($pass)>32)
	 continue;

	$pass=trim($pass);
	$pass=preg_replace("#\s#","",$pass);
	$pass=ms_escape_string($pass);
	$sql = "insert into plain_text([pass]) values('$pass')";
	$stmt = sqlsrv_query( $conn, $sql );
	if( $stmt === false)
	{
		print_r( sqlsrv_errors(), true);
		$error_count++;
	}
}


инсерт сделал по одной строке, т.к. хочу избежать повторов строк. если я буду вставлять по 2 и больше строки за 1 запрос, то в случае повтора одной из строк весь запрос не обработается...
я слышал что можно быстро загружать большие обьемы данных через c# класс SqlBulkCopy, но как решить ошибки совпадения строк?
2 мар 12, 15:31    [12184147]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
Aleksey V.P.
Member

Откуда: Москва
Сообщений: 575
alexeyvg
Aleksey V.P.
Ещё, убери нафик CLUSTERED на primary key.
С чего это? Чтобы ещё замедлить построение инджекса для pass?


А при чём здесь поле Pass ???
2 мар 12, 15:33    [12184166]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
Aleksey V.P.
Member

Откуда: Москва
Сообщений: 575
gds
Aleksey V.P
Ещё, убери нафик CLUSTERED на primary key.

С какой радости?


Мильпардон, пропустил инфу про 5-10 таблиц со ссылками на Id пароля. Тогда, конечно, вариант с накоплением в промежуточной таблице с переносом insert from select более приемлем.
2 мар 12, 15:40    [12184231]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
edyaN
Member

Откуда: Berlin
Сообщений: 185
alexboesky,

Сформируйте в пхп файл с данными и потом скормите его bcp. Будет намного быстрее.
2 мар 12, 15:44    [12184279]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
edyaN
Member

Откуда: Berlin
Сообщений: 185
+ offtop
alexboesky
foreach($f as $k=>$v)
{
	$pass=trim($v);
	if(strlen($pass)>32)
	 continue;

	$pass=trim($pass);
	$pass=preg_replace("#\s#","",$pass);
	$pass=ms_escape_string($pass);
	$sql = "insert into plain_text([pass]) values('$pass')";
	$stmt = sqlsrv_query( $conn, $sql );
	if( $stmt === false)
	{
		print_r( sqlsrv_errors(), true);
		$error_count++;
	}
}

если в print_r передать второй параметр true, тогда будет возвращена строка, но у Вас она никак не сохраняется, поэтому вы не видите ошибок

2 мар 12, 15:50    [12184349]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
bcp схавает все запросы даже если будет много дублей?
2 мар 12, 17:21    [12185267]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexboesky
Member

Откуда: Киев
Сообщений: 40
по поводу print_r это я немного тупанул) у меня сначало стояло echo print_r... а потом я убрал echo...
2 мар 12, 17:24    [12185286]     Ответить | Цитировать Сообщить модератору
 Re: Очень большая база и таблицы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
alexboesky
инсерт сделал по одной строке, т.к. хочу избежать повторов строк. если я буду вставлять по 2 и больше строки за 1 запрос, то в случае повтора одной из строк весь запрос не обработается...
Можно использовать при создании индекса параметр IGNORE_DUP_KEY
Aleksey V.P.
alexeyvg
пропущено...
С чего это? Чтобы ещё замедлить построение инджекса для pass?

А при чём здесь поле Pass ???
Ну так если нет уникального кластерного ПК, то в индексы будут добавляться ИД строки, которые больше. Хотя это мелочь, конечно. По любому убирать кластерный индекс смысла нету. не тот это случай.
2 мар 12, 18:25    [12185651]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить