SQL и куча пользователей.

← →
KilkennyCat © (2009-12-16 22:31) [0]

Имеем сайт, файловый архив. На сайте зарегистрировано куча пользователей, предположим пара миллионов. У каждого пользователя имеется куча собственных файлов, этак около десятка тысяч, каталогизированы по вкусу каждого пользователя.
Как организовать?
Мне сразу видится, что есть буквально одна табличка - пользователи, и у каждого пользователя своя собственная таблица. В этом случае, когда пользователь копается в своих файлах, его запрос крутится в пределах своей таблицы, правильно? Что должно быть быстрее и менее нагрузочно на сервер. Но пара мильенов таблиц...
Как правильно?

← →
Kerk © (2009-12-16 22:34) [1]

> Мне сразу видится, что есть буквально одна табличка - пользователи,
> и у каждого пользователя своя собственная таблица.

Ну, во-первых, не у каждого собственная.
Нужно всего 2 таблицы: пользователи и файлы, каждый файл будет ссылаться на владельца по ID. Если файл может принадлежать многим пользователям, то нужна третья таблица для связки юзер_ид и файл_ид.

← →
Eraser © (2009-12-16 22:35) [2]

> и у каждого пользователя своя собственная таблица

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

← →
Kerk © (2009-12-16 22:36) [3]

Если файлов совсем дофига, то это нужно разбивать таблицу каким-либо образом (по алфавиту или еще как) и разносить на разные сервера.

← →
KilkennyCat © (2009-12-16 22:39) [4]

А чем противоречит нормализации? общих данных у пользователей нет. один пользователь не может видеть данные другого пользователя. Фактически, каждый пользователь имеет свою БД.

> Нужно всего 2 таблицы: пользователи и файлы, каждый файл
> будет ссылаться на владельца по ID.

Да. Но не будет ли это медленнее? Или все равно?

← →
KilkennyCat © (2009-12-16 22:40) [5]

> Kerk © (16.12.09 22:36) [3]

скорее, разносить пользователей на разные сервера.

← →
xayam © (2009-12-16 22:41) [6]

таксономию в друпале посмотри, я по ней делал. Примерно так в упрощенном виде классификация выглядет:
CREATE TABLE `term_data` ( `ID_TERM` int(11) unsigned NOT NULL auto_increment, `F_VOC` int(11) unsigned NOT NULL, `F_PARENT` int(11) unsigned NOT NULL default "0", `NAME` varchar(255) NOT NULL, `DESCRIPTION` longtext, `WEIGHT` tinyint(4) NOT NULL, PRIMARY KEY (`ID_TERM`), KEY `F_VOC` (`F_VOC`,`NAME`), KEY `F_VOC_WEIGHT_NAME` (`F_VOC`,`WEIGHT`,`NAME`), KEY `F_PARENT` (`F_PARENT`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `term_node` ( `F_TERM` int(11) unsigned NOT NULL default "0", `F_NODE` int(11) unsigned NOT NULL default "0", PRIMARY KEY (`F_NODE`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `vocabulary` ( `ID_VOC` int(11) unsigned NOT NULL auto_increment, `TYPE` tinyint(4) unsigned NOT NULL, `NAME` varchar(255) NOT NULL, `DESCRIPTION` longtext, `WEIGHT` tinyint(4) NOT NULL default "0", PRIMARY KEY (`ID_VOC`), UNIQUE KEY `TYPE` (`TYPE`), UNIQUE KEY `NAME` (`NAME`) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
но тебе нужно еще к этому добавить поддержку мультиюсерности т.е. в таблицах term_data и vocabulary должна быть ссылка на id пользователя. А так много таблиц конечно не требуется.

← →
Anatoly Podgoretsky © (2009-12-16 22:45) [7]

> KilkennyCat (16.12.2009 22:31:00) [0]

Правильнее провести разработку. Создать нормализованые таблицы, ссылочную целостность.
Но не в коем случае два милиона таблиц.

← →
Anatoly Podgoretsky © (2009-12-16 22:47) [8]

> Kerk (16.12.2009 22:36:03) [3]

Файлов 20 миллиардов, тут потребуется много серверов.

← →
Eraser © (2009-12-16 22:47) [9]

> [4] KilkennyCat © (16.12.09 22:39)

тем что у файла, по сути, нет идентификатора вообще, т.е. противоречит всей концепции реляционных БД.

← →
Anatoly Podgoretsky © (2009-12-16 22:48) [10]

> KilkennyCat (16.12.2009 22:40:05) [5]

2 миллиона серверов, а что неплохо

← →
KilkennyCat © (2009-12-16 22:55) [11]

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

← →
Petr V. Abramov © (2009-12-16 22:59) [12]

> KilkennyCat © (16.12.09 22:31)

если задача - as is как написано, то БД - баловство.
каждый юзер - директория с файлами.

← →
Kerk © (2009-12-16 22:59) [13]

> KilkennyCat © (16.12.09 22:55) [11]

Вообще, не зря же придумали индексы.

← →
Anatoly Podgoretsky © (2009-12-16 23:00) [14]

> KilkennyCat (16.12.2009 22:55:11) [11]

Обычный INNER JOIN таблицы пользователей с таблицей файлов, по ИД - это будет почти мгновенно, при условии наличия индексов на ИД.

← →
KilkennyCat © (2009-12-16 23:01) [15]

> Eraser © (16.12.09 22:47) [9]

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

← →
KilkennyCat © (2009-12-16 23:03) [16]

> Kerk © (16.12.09 22:59) [13]
> Anatoly Podgoretsky © (16.12.09 23:00) [14]

Индексация практически даст требуемое? Или лишь приблизит?

> Petr V. Abramov © (16.12.09 22:59) [12]

да вот я изначально так и думал сделать, но ведь это не модно :)

← →
Petr V. Abramov © (2009-12-16 23:38) [17]

> KilkennyCat © (16.12.09 23:03) [16]

а ты на главной странице напиши "используется модная БД" и не парься.

← →
Petr V. Abramov © (2009-12-16 23:41) [18]

> KilkennyCat © (16.12.09 23:03) [16]

а если не совсем as is, то приведи, "чем не совсем"

← →
antonn © (2009-12-16 23:42) [19]

> Вы же предлагаете, что мы лазаем в одной папке, но постоянно
> смотрим, ага, это твое, а это мое...

можно по папкам, а можно все в одной. Но тогда в таблице "файлы" заносить имя файла залитого пользователем (мама.jpeg), и имя файла на винте (12452154212.jpeg) по которому будет обращение к файлу

← →
KilkennyCat © (2009-12-16 23:45) [20]

Да не, я всегда парюсь... я раньше всегда сайты делал спокойненько, на чистом нтмл, в блокнотике... А сейчас используют всякую мощнейшую хренотень. Сайт-визитка из трех страниц, с содержанием лэйбл, контакты, как проехать соответственно, делается на вордпрессе и с бд.
Фрэймы не модно. Даже таблицы не модно.
Я боюсь, что может, я просто чего-то не знаю? может, браузеры уже неумеют отображать файлы с расширением htm, обязательно должно быть минимум php?

← →
antonn © (2009-12-16 23:48) [21]

> может, браузеры уже неумеют отображать файлы с расширением
> htm, обязательно должно быть минимум php?

браузер отобразит то, что передается в хедере, какой там тип. Текс в строке запроса может быть совершенно любой, если вебсервер поддерживает - он отдаст запрошенный 11.gif как хтмл (htaccess, к примеру :) )

← →
KilkennyCat © (2009-12-16 23:48) [22]

> Но тогда в таблице "файлы" заносить имя файла залитого пользователем
> (мама.jpeg), и имя файла на винте (12452154212.jpeg) по
> которому будет обращение к файлу

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

← →
Petr V. Abramov © (2009-12-16 23:49) [23]

> Anatoly Podgoretsky © (16.12.09 23:00) [14]

> Обычный INNER JOIN таблицы пользователей с таблицей файлов,
> по ИД - это будет почти мгновенно, при условии наличия
> индексов на ИД.
>

фиг там, десятки тыщ scattered read`ом тащить или как там у вас на ms оно называется, ну ты понял.

← →
antonn © (2009-12-16 23:52) [24]

> Компенсирует ли индексация разницу между ползаньем в одной
> большой и куче мелких?

если там файлов от 2х миллионов я бы создавал по папкам (имя папки - id юзера).

2 миллиона записей в таблице с индексами - это много? насколько я понял это интранет сайт, значит юзеры спокойно смогут подождать и 2-3 секунды, за которые идут сложные запросы в 2/10 миллионную табличку

← →
KilkennyCat © (2009-12-17 00:04) [25]

> если там файлов от 2х миллионов я бы создавал по папкам
> (имя папки - id юзера).

это вопрос хранения файлов. разумеется, так и будет.
я веду речь о базе, где записано, где и что хранится (файл имеет несколько атрибутов, помимо имени).
Хотя, конечно, все этоможно интегрировать в файл или рядом...
кажется, я понял.
Petr V. Abramov прав. Будет ас ис :)
И остальные правы, будет всего несколько табличек.

Всем огромное спасибо!

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

← →
Petr V. Abramov © (2009-12-17 00:19) [26]

> KilkennyCat © (17.12.09 00:04) [25]

> Будет ас ис :)

противоречит

> файл имеет несколько атрибутов, помимо имени

:)
т.е (телепатирую :)) а вдруг нужен поиск по этим атрибутам.
ну тогда БД и файлы :)

могу только посоветовать Oracle с Partitionig Option :)

← →
Anatoly Podgoretsky © (2009-12-17 00:25) [27]

> Petr V. Abramov (16.12.2009 23:49:23) [23]

where t1.id=:id

Одно чтение из t1 и соединение по индексу. seek + scan пока равно. Максимум 10 000 чтений по одному на файл

← →
KilkennyCat © (2009-12-17 00:26) [28]

> Petr V. Abramov © (17.12.09 00:19) [26]

Такой поиск будет. Но это уже последний по вероятности поиск, потом решу, как его, реализовать, скорее всего совмещу собственно с поиском внутри файлов (текстовых). А можно и еще модный XML прикрутить. Каждому пользователю.

А вот еще нашел Hyperwave, тож какая-то база документов.

← →
Eraser © (2009-12-17 00:27) [29]

> [15] KilkennyCat © (16.12.09 23:01)

> это я не понял. причем здесь идентификатор у файла?

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

← →
KilkennyCat © (2009-12-17 00:28) [30]

Ща я найду техническое описание SQL, как оно все внутре него работает, и еще раз подумаю.

← →
KilkennyCat © (2009-12-17 00:29) [31]

> на запрос выборки всех файлов, размер которых больше 1 Мб
> по всем пользователям )

намек понял...

← →
Anatoly Podgoretsky © (2009-12-17 00:37) [32]

> Eraser (17.12.2009 00:27:29) [29]

select FileName, FileSize from files where FileSize > 1048576

← →
GDI+ (2009-12-17 00:39) [33]

> KilkennyCat © (16.12.09 22:31)
>
> Имеем сайт, файловый архив. На сайте зарегистрировано куча
> пользователей, предположим пара миллионов. У каждого пользователя
> имеется куча собственных файлов, этак около десятка тысяч,
> каталогизированы по вкусу каждого пользователя.
> Как организовать?
> Мне сразу видится, что есть буквально одна табличка - пользователи,
> и у каждого пользователя своя собственная таблица. В этом
> случае, когда пользователь копается в своих файлах, его
> запрос крутится в пределах своей таблицы, правильно? Что
> должно быть быстрее и менее нагрузочно на сервер. Но пара
> мильенов таблиц...
> Как правильно?

Тут без распределенной структуры не обойтись.

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

Тоесть для пользователя - это один сервер для коннекта, а ПО работает каждое со своим сервером.

← →
Petr V. Abramov © (2009-12-17 00:42) [34]

> Anatoly Podgoretsky © (17.12.09 00:25) [27]

> where t1.id=:id

ну в as is [27] - один поиск в индексе (кстати, нефиговом и не таком уж здоровском, хоть и не отвратном, с т.з. nv/ndv) + поиск записей по rowid (или как оно там у вас называется).

> Максимум 10 000 чтений по одному на файл

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

← →
KilkennyCat © (2009-12-17 00:55) [35]

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

← →
Anatoly Podgoretsky © (2009-12-17 00:56) [36]

> Petr V. Abramov (17.12.2009 00:42:34) [34]

Поиск по индексу работает практически мгновенно, время log(N), как при 20, так и при 20 000 000 файлов. и второй поиск еще столько же, дальше последовательная выборка. Больше времени займет передача 10 000 строк на клиента. Больше проблем будет с железом и оптимизацией файловых групп и баз. База то будет 20 000 000 000 * 100 примерно 2 ТБ и более. И железо под 20 000 000 000 файлов, потребуется много серверов или очень большое количество дисков. При среднем размере файла в 1 мб 20 000 000 000 * 1000000 байт

← →
KilkennyCat © (2009-12-17 01:00) [37]

Интересно, а как работает "вконтакте" ? 7 миллионов пользователей.

← →
antonn © (2009-12-17 01:25) [38]

они же не все одновременно онлайн :)
да и кто там пишет, что их 7 миллионов?

← →
GDI+ (2009-12-17 01:33) [39]

> KilkennyCat © (17.12.09 01:00) [37]
>
> Интересно, а как работает "вконтакте" ? 7 миллионов пользователей.

Я ж уже указал в [33]. Писал такую систему.

> GDI+ (17.12.09 01:33) [39]

как-то они быстро и мощно. это все-таки затратно.

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

Да и вообще, 20 миилиардов файлов на сервере разместить, это как бы тоже не хрен собакин.

> Игорь Шевченко © (17.12.09 01:59) [41]

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

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

> Игорь Шевченко © (17.12.09 02:17) [43]

я тоже так думал. но я куда не смотрел - везде бд, бд, бд... миллион леммингов не могут ошибаться.

← →
GDI+ (2009-12-17 02:26) [45]

> Игорь Шевченко © (17.12.09 02:17) [43]
>
> Kerk © (17.12.09 02:12) [42]
>
> Я к чему - файловая структура не проще ? NFS-ы там всякие

На БД проще вносить новые фичи, так как книверсальное хранилище уже готово. Главное, чтобы нормализация не заменила здравый смысл.

А по поводу чистых сетевых ФС. Есть DFS, но при серьезной нагрузке 500 одновременных коннектов к серверу она падает.
http://www.microsoft.com/windowsserver2003/technologies/storage/dfs/default.mspx

> Kerk © (17.12.09 02:12) [42]

Угу, это и есть распределенная система со справочником аккаунтов. Так и Skype работает и ICQ и Jabber.

> Игорь Шевченко (17.12.2009 01:59:41) [41]

На одном сервере, нужна хорошая сановская дисковая стойка и не одна.

Anatoly Podgoretsky © (17.12.09 09:14) [46]

> На одном сервере, нужна хорошая сановская дисковая стойка
> и не одна.

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

> Игорь Шевченко (17.12.2009 11:57:47) [47]

Я же привел объемы для размера файлов 1 мб в среднем, это 2*10^16 степени байт, не считая базы данных

← →
ANB (2009-12-17 12:09) [49]

Вариант 1 : - 2 толстые таблицы - юзеры + файлы юзеров.
Достоинства :
1.1 очень просто. Чтобы таблицы не джойнить - определяем ИД юзера при входе и запоминаем его, потом обращаемся только ко 2-й таблице.
1.2 - легко писать запросы для поиска файлов по всей БД (если это нужно)
Минусы :
1.1 - на небольшом количестве юзеров запросы по индексам будут летать. При увеличении количества подключенных юзеров может возникнуть затор на чтении.
1.2 - толстые таблицы тяжело распределять по разным дискам / серверам. Придется использовать фичи СУБД типа партиционирования.
1.3. - если юзеров будет выгребать не все файлы, а только нужные и использовать всякие нестандартные приемы поиска - то поиск нужных файлов пойдет перебором (индекс рэнж скан + фильтер)
1.4 - индекс по второй табличке будет не маленький
Вариант 2 : таблица юзеров + куча таблиц для каждого юзера.
Минус :
2.1 явно большая сложность разработки и поддержки
2.2 запросы всегда придется строить на лету (динамически)
2.3 структура БД будет противоречить всем канонам построения баз
Достоинства :
2.1 - легко распределять БД по разным устройствам / серверам. Можно вообще по разным БД раскидывать.
2.2 - все нестандартные поиски будут шустрее работать (всяческие фичи фулл-скана, т.к. таблицы будут маленькими)

← →
GDI+ (2009-12-17 22:44) [50]

> Игорь Шевченко © (17.12.09 11:57) [47]
> Да и сервер наверное не очень тривиальный. Я как-то по собственному
> опыту сужу, для средних баз объемом несколько терабайт и
> количеством записей под миллиард в таблицах уже пользуется
> нетривиальное железо для одновременной работы тысячи пользователей.

Поэтому, так как по ТЗ пользователи не должны пересекается, то лучше 1 база - 1 пользователь. На каждый компьютер по 1000 пользователей. 1 база для справочника аккаунтов для учета нагрузки и распределения баз (активно используемые по 500 на сервер, пассивные по 5000 на сервер).

> KilkennyCat ©

<offtop>
Я вот чего нашел, а то ты свое писал подобное
http://docs.jquery.com/Plugins/Validation
</offtop>

> Kerk © (18.12.09 01:37) [51]

ага, я свое писал после того как это тоже нашел. бьюсь за размер. я и jquery уменьшу по окончании проекта.

> Минус :
> 2.1 явно большая сложность разработки и поддержки

почему? все там очень просто.

> 2.2 запросы всегда придется строить на лету (динамически)

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

> 2.3 структура БД будет противоречить всем канонам построения баз

ну... если жить по уставу - свихнешься.

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

Но вот шустрость работы как все-таки оценить...
То, что допсервера придется по мере роста кол-ва юзверей подключать - это понятно. Но деньги тоже надо экономить. И если нарушение канонов позволяет разместить юзверей на порядок больше - каноны нарушать надо.

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

← →
ANB (2009-12-18 13:18) [53]

> ну... если жить по уставу - свихнешься.

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

> и проведу эксперимент,ибо в теории запутался совсем.

Не забудь сэмулировать тыщ 10 одновременных коннектов. Иначе разницы не заметишь.

> KilkennyCat © (18.12.09 09:42) [52]
>
> > Kerk © (18.12.09 01:37) [51]
>
> ага, я свое писал после того как это тоже нашел. бьюсь за
> размер. я и jquery уменьшу по окончании проекта.

А нафига?

SQL и куча пользователей. Найти похожие ветки