ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Получение данных из большой таблицы
25 сообщений из 37, страница 1 из 2
  1  все
Получение данных из большой таблицы
    #35599400
Скрябин Дмитрий
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Скрябин Дмитрий
Гость
Здравствуйте!
Есть таблица, в которой хранится трафик.
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
                 Table "internet.trafic_20080901"
  Column   |            Type             |       Modifiers
-----------+-----------------------------+------------------------
 ip_from   | inet                        | not null
 port_from | character varying( 10 )       | not null
 ip_to     | inet                        | not null
 bytes     | bigint                      | not null
 t_t       | smallint                    | not null
 place     | smallint                    | not null
 tm        | timestamp without time zone | not null default now()
 client_id | integer                     | not null
Indexes:
    "trafic_20080901_client_idx" btree (client_id)
В таблице 80млн. записей, размер таблицы - 6Гб.
Из этой таблицы нужно взять трафик для каждого client_id и поместить его в свой файл.
client_id имеет около 16000 различных значений.
Подскажите, как лучше выбирать данные? Записывать в файл будет скрипт либо на php, либо на python (еще не определился).
Вариантов вижу несколько:
1. запрос для каждого client_id. Недостаток - очень много запросов. выполнение каждого запроса - около 3-4 секунд.
2. запрос для диапазонов - сначала от 1 до 100, затем от 101 до 200 и т.д. В скрипте можно будет открыть 100 файлов, и туда всё записать. Время выполнения одного запроса - 27секунд.
3. запрос всех данных. Не хочу загружать 6 гиг в оперативку. время выполнения не вычислял ;)
4. дамп таблицы и работа уже в текстовым файлом. время выполнения дампа - около 20 минут.
Пока других вариантов не вижу, а хочется придумать, ибо такая задача будет выполняться каждую ночь. После создания таких файлов таблица будет удаляться. Надеюсь на ваши советы :)
БД: PostgreSQL 8.3
Debian 4, 30Gb RAM, 4x2.8 CPU
...
Рейтинг: 0 / 0
16.10.2008, 21:22
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35599451
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Скрябин Дмитрий wrote:

> Table "internet.trafic_20080901"
> Column | Type | Modifiers
> -----------+-----------------------------+------------------------
> ip_from | inet | not null
> port_from | character varying(*10*) | not null
> ip_to | inet | not null
> bytes | bigint | not null
> t_t | smallint | not null
> place | smallint | not null
> tm | timestamp without time zone | not null default now()
> client_id | integer | not null
> Indexes:
> "trafic_20080901_client_idx" btree (client_id)
>
> В таблице 80млн. записей, размер таблицы - 6Гб.
> Из этой таблицы нужно взять трафик для каждого client_id и поместить его
> в свой файл.

select * from internet.trafic_20080901
order by client_id

бежать по запросу, и переписывать данные в куда надо.
Как только будет меняться client_id, закрывать предыдущий
файл клиента и открывать следующий.

Перед выполнением запроса проверьте его план, и убедитесь
в том, что сервер не будет сортировать данные, а будет
использовать индекс.
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
16.10.2008, 22:17
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35599857
Скрябин Дмитрий
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Скрябин Дмитрий
Гость
MasterZiv, в этом случае все данные будут в память загружаться всей кучей, все 6 гигабайт. Думаешь, это оптимальный вариант?

Код: plaintext
1.
2.
3.
4.
5.
6.
db3=>      explain select * from internet.trafic_20080901 order by client_id;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Sort  (cost= 23744033 . 47 .. 23944644 . 35  rows= 80244352  width= 43 )
   Sort Key: client_id
   ->  Seq Scan on trafic_20080901  (cost= 0 . 00 .. 1552201 . 52  rows= 80244352  width= 43 )
( 3  rows)

Планировщик решил, что правильнее будет индекс не использовать.
...
Рейтинг: 0 / 0
17.10.2008, 08:03
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35599875
Kruchinin Pahan
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kruchinin Pahan
Участник
Скрябин ДмитрийMasterZiv, в этом случае все данные будут в память загружаться всей кучей, все 6 гигабайт. Думаешь, это оптимальный вариант?

Код: plaintext
1.
2.
3.
4.
5.
6.
db3=>      explain select * from internet.trafic_20080901 order by client_id;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Sort  (cost= 23744033 . 47 .. 23944644 . 35  rows= 80244352  width= 43 )
   Sort Key: client_id
   ->  Seq Scan on trafic_20080901  (cost= 0 . 00 .. 1552201 . 52  rows= 80244352  width= 43 )
( 3  rows)

Планировщик решил, что правильнее будет индекс не использовать.
Возможно проблема в SELECT *
Перечислите только те поля, которые действительно вам необходимы для суммирования.
...
Рейтинг: 0 / 0
17.10.2008, 08:27
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35599999
Скрябин Дмитрий
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Скрябин Дмитрий
Гость
Kruchinin Pahan,
Даже если одно поле выбрать - все равно индекс не используется. Да и не много толку будет тут от индекса - все равно все данные загружать.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
explain select client_id from internet.trafic_20080901 order by client_id;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Sort  (cost= 15927262 . 97 .. 16127873 . 85  rows= 80244352  width= 4 )
   Sort Key: client_id
   ->  Seq Scan on trafic_20080901  (cost= 0 . 00 .. 1552201 . 52  rows= 80244352  width= 4 )
( 3  rows)
...
Рейтинг: 0 / 0
17.10.2008, 09:46
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35600013
Kruchinin Pahan
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kruchinin Pahan
Участник
Скрябин ДмитрийKruchinin Pahan,
Даже если одно поле выбрать - все равно индекс не используется. Да и не много толку будет тут от индекса - все равно все данные загружать.

Значит, действительно нет необходимости. В реальности, иногда сортировка всей таблы ускоряется, если по индексу. Но, в общем-то MasterZiv прав. Данный способ может оказаться самым быстрым.
Чтобы много памяти на клиенте не жрать, можно Fetch кусками делать (через курсор). И работать с небольшими блоками.
...
Рейтинг: 0 / 0
17.10.2008, 09:50
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35600326
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Скрябин Дмитрий wrote:
> MasterZiv, в этом случае все данные будут в память загружаться всей
> кучей, все 6 гигабайт.

А ты можешь как-то обработать данные, не читая их в память ?
Ну, давай, телепат ...

Думаешь, это оптимальный вариант?
Да, 100%.
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
17.10.2008, 11:27
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35600334
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Kruchinin Pahan wrote:

> Планировщик решил, что правильнее будет индекс не использовать.
>
>
> Возможно проблема в SELECT *
> Перечислите только те поля, которые действительно вам необходимы для
> суммирования.
А это на скорость не повлияет. Таблица всё равно будет читаться.
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
17.10.2008, 11:28
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35600337
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Скрябин Дмитрий wrote:

> db3=> explain select * from internet.trafic_20080901 order by client_id;
> QUERY PLAN
> -----------------------------------------------------------------------------------
> Sort (cost=*23744033*.*47*..*23944644*.*35* rows=*80244352* width=*43*)
> Sort Key: client_id
> -> Seq Scan on trafic_20080901 (cost=*0*.*00*..*1552201*.*52* rows=*80244352* width=*43*)
> (*3* rows)
>
>
> Планировщик решил, что правильнее будет индекс не использовать.

Ой, я это просмотрел.
Нет, надо его ЗАСТАВИТЬ использовать индекс и не сортировать данные.
Иначе - будет труба. Сортировать такой массив данных очень сложно.
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
17.10.2008, 11:29
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35600345
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Скрябин Дмитрий wrote:

> Даже если одно поле выбрать - все равно индекс не используется. Да и не
> много толку будет тут от индекса - все равно все данные загружать.

Использование индекса даст возможность не сортировать таблицу.
Она у тебя большая. А если ты заставишь сканировать индекс,
то, поскольку индекс упорядочен, серверу не придётся сортировать
таблицу. Оптимизатор вполне оправдано не берёт индекс, потому что
сканировать без индекса конечно же быстрее. Но сортировать дольше.
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
17.10.2008, 11:31
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35600427
Kruchinin Pahan
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kruchinin Pahan
Участник
MasterZiv
Использование индекса даст возможность не сортировать таблицу.
Она у тебя большая. А если ты заставишь сканировать индекс,
то, поскольку индекс упорядочен, серверу не придётся сортировать
таблицу. Оптимизатор вполне оправдано не берёт индекс, потому что
сканировать без индекса конечно же быстрее. Но сортировать дольше.
Posted via ActualForum NNTP Server 1.4
Согласен. Туплю.
...
Рейтинг: 0 / 0
17.10.2008, 11:59
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35600469
MySQLCraft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MySQLCraft
Участник
А еще быстрее, не использовать индекс и не сортировать данные, выполнять full scan, обрабатывать данные кусками. Файлы открывать по необходимости, а закрывать после завершения всех операций. При поступлении очередной записи записывать ее в нужный файловый поток.
...
Рейтинг: 0 / 0
17.10.2008, 12:12
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35600575
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
MySQLCraft wrote:

> А еще быстрее, не использовать индекс и не сортировать данные, выполнять
> full scan, обрабатывать данные кусками. Файлы открывать по
> необходимости, а закрывать после завершения всех операций. При
> поступлении очередной записи записывать ее в нужный файловый поток.

Я бы так и посоветовал бы, если бы не количество групп:

"Из этой таблицы нужно взять трафик для каждого client_id и поместить его в свой
файл.
client_id имеет около 16000 различных значений."

16000 - многовато. В некоторых ОС даже столько файловых дескрипторов не
открыть.
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
17.10.2008, 12:36
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35600734
Скрябин Дмитрий
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Скрябин Дмитрий
Гость
MasterZiv
Скрябин Дмитрий wrote:
> MasterZiv, в этом случае все данные будут в память загружаться всей
> кучей, все 6 гигабайт.

А ты можешь как-то обработать данные, не читая их в память ?

Posted via ActualForum NNTP Server 1.4

Вот, оказывается, могу :) Не читая их разом. Совсем не подумал про курсоры - спасибо Kruchinin Pahan! Так и сделаю. Была трудность как раз в том, чтобы по минимуму нагружать базу и использовать меньше оперативной памяти в процессе работы с этой таблицей. курсоры как раз подходят
Открыть 16000 файлов конечно не получится, думаю разбить все данные сначала на 100 файлов (по диапазонам client_id), потом каждый файл еще на 100, тогда в конечном итоге будет в каждом файле не более 100 разных client_id. Там уже каждый файл можно разложить на требуемые файлы. 100 дескрипторов система спокойно потянет :)
...
Рейтинг: 0 / 0
17.10.2008, 13:20
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35600811
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
Скрябин ДмитрийMasterZiv, в этом случае все данные будут в память загружаться всей кучей, все 6 гигабайт. Думаешь, это оптимальный вариант?

Код: plaintext
1.
2.
3.
4.
5.
6.
db3=>      explain select * from internet.trafic_20080901 order by client_id;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Sort  (cost= 23744033 . 47 .. 23944644 . 35  rows= 80244352  width= 43 )
   Sort Key: client_id
   ->  Seq Scan on trafic_20080901  (cost= 0 . 00 .. 1552201 . 52  rows= 80244352  width= 43 )
( 3  rows)

Планировщик решил, что правильнее будет индекс не использовать.сделайте set enable_seqscan to off;
...
Рейтинг: 0 / 0
17.10.2008, 13:40
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35601009
MySQLCraft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MySQLCraft
Участник
MasterZiv
MySQLCraft wrote:

> А еще быстрее, не использовать индекс и не сортировать данные, выполнять
> full scan, обрабатывать данные кусками. Файлы открывать по
> необходимости, а закрывать после завершения всех операций. При
> поступлении очередной записи записывать ее в нужный файловый поток.

Я бы так и посоветовал бы, если бы не количество групп:

"Из этой таблицы нужно взять трафик для каждого client_id и поместить его в свой
файл.
client_id имеет около 16000 различных значений."

16000 - многовато. В некоторых ОС даже столько файловых дескрипторов не
открыть.
Posted via ActualForum NNTP Server 1.4
Буферизовать массивы в оперативной памяти и периодически скидывать на диск, открывая столько файлов, сколько потянет ОС.
...
Рейтинг: 0 / 0
17.10.2008, 14:24
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35601068
MySQLCraft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MySQLCraft
Участник
Но лучше, открывать только один файл, чтобы не было конкуренции между потоками при записи.
Короче. Второй поток, получает порции данных от SQL сервера и сортирует их по 16000 массивам. Второй поток обходит по очереди не пустые массивы, забирает оттуда данные и обнуляет текущий массив, открывает соответствующий файл, пишет, закрывает файл. Периодичность обхода зависит от ограничений оперативки и субъективных критериев.
...
Рейтинг: 0 / 0
17.10.2008, 14:36
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35601076
MySQLCraft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MySQLCraft
Участник
очепятка "Короче. Первый поток"... ))))
...
Рейтинг: 0 / 0
17.10.2008, 14:38
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35601264
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Скрябин Дмитрий wrote:

> Вот, оказывается, могу :) Не читая их разом. Совсем не подумал про
> курсоры - спасибо Kruchinin Pahan! Так и сделаю.

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

> Открыть 16000 файлов конечно не получится, думаю разбить все данные
> сначала на 100 файлов (по диапазонам client_id), потом каждый файл еще
> на 100, тогда в конечном итоге будет в каждом файле не более 100 разных
> client_id. Там уже каждый файл можно разложить на требуемые файлы. 100
> дескрипторов система спокойно потянет :)


Увеличишь только свою работу внесколько раз.
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
17.10.2008, 15:26
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35601267
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
MySQLCraft wrote:

> Буферизовать массивы в оперативной памяти и периодически скидывать на
> диск, открывая столько файлов, сколько потянет ОС.
а если не влезет ?
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
17.10.2008, 15:27
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35601369
Скрябин Дмитрий
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Скрябин Дмитрий
Гость
авторТы думаешь, что курсоры не тянут куски таблицы в память ?
Ты ошибаешься, курсор - ровно такой же запрос, как и все остальное.
Конечно тянут - но не все 6 гиг сразу, а частями. поэтому в единицу времени скрипт требует не так много памяти.
MySQLCraft, спасибо, попробую оба варианта и сравню скорость :)
...
Рейтинг: 0 / 0
17.10.2008, 15:54
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35601578
Sishnikov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sishnikov
Гость
а SQL-комманду COPY вы не рассматривали?
...
Рейтинг: 0 / 0
17.10.2008, 17:10
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35601769
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Скрябин Дмитрий wrote:

> Конечно тянут - но не все 6 гиг сразу, а частями. поэтому в единицу
> времени скрипт требует не так много памяти.

Так и запрос тебе все 6 гигов сразу не вытащит. Точно так же
кусками и будет подтаскивать.

На самом деле курсоры сами используются внутри сервера, когда
вы запрос выполняете. Это ровно то же самое.
вы бежите fetch-ем по строкам, это и есть курсор.

> MySQLCraft, спасибо, попробую оба варианта и сравню скорость :)

Бессмысленно пробовать два варианта.
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
17.10.2008, 18:30
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35602067
Фотография Степан H.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Степан H.
Участник
как вариант
данные в таблицу подгружать и обрабатывать порциями
client_id разбить на группы group_id и партицировать эти группы .. или вовсе разнести в разные таблицы (если позволяет устройство загрузки трафика)

выгребать вашим 1-м вариантом несколькими потоками (по идее время select должно значительно уменьшиться, количество запросов в единицу времени возрасти)
...
Рейтинг: 0 / 0
18.10.2008, 00:12
| Ответить | Цитировать | Написать  
Получение данных из большой таблицы
    #35602249
Скрябин Дмитрий
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Скрябин Дмитрий
Гость
MasterZiv
Так и запрос тебе все 6 гигов сразу не вытащит. Точно так же
кусками и будет подтаскивать.

На самом деле курсоры сами используются внутри сервера, когда
вы запрос выполняете. Это ровно то же самое.
вы бежите fetch-ем по строкам, это и есть курсор.


На самом деле это не так. Точнее не совсем так, о чем нам и говорит первый абзац в разделе "38.7. Cursors" в документации. Я написал 2 тестовых скрипта, первый тянет данные одним запросом, второй - через курсоры. Второй использует в разы меньше памяти. Если интересует - могу выложить текст скриптов, сами убетитесь или опровергните мое утверждение.

Sishnikov, COPY TO рассматривал, но не остановился на нем. php копирует команду только в массив. А копирование в файл во-первых, требует прав postgres, во-вторых, это то же, что и дамп таблицы - проще наверно сделать дамп, если решу работать с текстовым файлом.

Степан Н., к сожалению, трафик сейчас можно загружать только в одну таблицу :(
...
Рейтинг: 0 / 0
18.10.2008, 11:34
| Ответить | Цитировать | Написать  
25 сообщений из 37, страница 1 из 2
  1  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Получение данных из большой таблицы
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]