Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Получение данных из большой таблицы / 25 сообщений из 37, страница 1 из 2
  1  все
16.10.2008, 21:22
    #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, 22:17
    #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
| Ответить | Цитировать | Написать  
17.10.2008, 08:03
    #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:27
    #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, 09:46
    #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:50
    #35600013
Kruchinin Pahan
Kruchinin Pahan
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Получение данных из большой таблицы
Скрябин ДмитрийKruchinin Pahan,
Даже если одно поле выбрать - все равно индекс не используется. Да и не много толку будет тут от индекса - все равно все данные загружать.

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

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

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

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

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

Использование индекса даст возможность не сортировать таблицу.
Она у тебя большая. А если ты заставишь сканировать индекс,
то, поскольку индекс упорядочен, серверу не придётся сортировать
таблицу. Оптимизатор вполне оправдано не берёт индекс, потому что
сканировать без индекса конечно же быстрее. Но сортировать дольше.
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2008, 11:59
    #35600427
Kruchinin Pahan
Kruchinin Pahan
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Получение данных из большой таблицы
MasterZiv
Использование индекса даст возможность не сортировать таблицу.
Она у тебя большая. А если ты заставишь сканировать индекс,
то, поскольку индекс упорядочен, серверу не придётся сортировать
таблицу. Оптимизатор вполне оправдано не берёт индекс, потому что
сканировать без индекса конечно же быстрее. Но сортировать дольше.
Posted via ActualForum NNTP Server 1.4
Согласен. Туплю.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2008, 12:12
    #35600469
MySQLCraft
MySQLCraft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Получение данных из большой таблицы
А еще быстрее, не использовать индекс и не сортировать данные, выполнять full scan, обрабатывать данные кусками. Файлы открывать по необходимости, а закрывать после завершения всех операций. При поступлении очередной записи записывать ее в нужный файловый поток.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2008, 12:36
    #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, 13:20
    #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:40
    #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, 14:24
    #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:36
    #35601068
MySQLCraft
MySQLCraft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Получение данных из большой таблицы
Но лучше, открывать только один файл, чтобы не было конкуренции между потоками при записи.
Короче. Второй поток, получает порции данных от SQL сервера и сортирует их по 16000 массивам. Второй поток обходит по очереди не пустые массивы, забирает оттуда данные и обнуляет текущий массив, открывает соответствующий файл, пишет, закрывает файл. Периодичность обхода зависит от ограничений оперативки и субъективных критериев.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2008, 14:38
    #35601076
MySQLCraft
MySQLCraft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Получение данных из большой таблицы
очепятка "Короче. Первый поток"... ))))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.10.2008, 15:26
    #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:27
    #35601267
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Получение данных из большой таблицы
MySQLCraft wrote:

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

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

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

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

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

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

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

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


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

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

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


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