DjangoBB LoFi version

Полная версия: Выборка из большой таблицы mysql

Начало » Базы данных » Выборка из большой таблицы mysql

1 2

Rkt

Ноя. 28, 2012 15:04:08

Здравствуйте.
У меня есть база в которую пишется статистика по трафику. Трафика много, базы большие - часовая табличка от 2 до 40 миллионов записей. Хранить это все в базе не очень выгодно, поэтому пишу скрипт который должен распарсить базу, сложить данные в текстовик и заархивировать.
Скрипт по выборке не сложный как мне казалось, получилось примерно так:

import MySQLdb
db = MySQLdb.connect(host="localhost", user="", passwd="", db="detailed", charset='utf8')
cursor = db.cursor()
...
sql1 = "SELECT `agent`, `src_ip`, `src_class`, `src_mask`, `src_as`, `dst_ip`, `dst_class`, `dst_mask`, `dst_as`, `in_if`, `out_if`, `pkts`, `bytes` FROM `{0}`".format(i[0])
cursor.execute(sql1)
data =  cursor.fetchall()
...

На этапе cursor.execute(sql1) начинаются проблемы. При выборке из базы с 23 миллиона записей весом 2 гига на сервере заканчиваются все 16 гиг оперативки и скрипт киляется операционкой.
Как правильно работать с такой базой?

helm2004

Ноя. 28, 2012 17:19:31

М-да, вот я уж этого инженера, который это всё придумал в Мускуле держать, за горло бы подержал.
Вам надо срочно на Редис переходить! А то сначала в файлы текстовые, а потом как парсить то их будете?

adray

Ноя. 28, 2012 17:55:41

Вариантов много, но идея одна - не запрашивать слишком много данных за раз

использовать where
использовать orm, позволяющую получать результаты по частям, н-р в sqlalchemy есть yield_per, но не факт, что оно заработает с mysql
использовать window functions, не уверен,что в mysql они есть
другое

FishHook

Ноя. 28, 2012 18:03:12

adray
другое

использовать LIMIT

Lexander

Ноя. 28, 2012 18:53:26

FishHook
использовать LIMIT

+1 за постраничную выборку вместо выборки всей таблицы с сотнями миллионов записей.

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

А вообще, прав helm2004, зачем записывать в базу.
Сразу в файл не?

doza_and

Ноя. 28, 2012 20:27:43

cursor.fetchall()

Просветите неграмотного зачем все тащить в память?

for rec in cursor:
    .....

не работает?
Конечно тоже не понял зачем лог писать в базу - это просто нехорошо.

adray

Ноя. 28, 2012 21:31:08

doza_and
Насколько я понял, дефолтное mysql-connection возвращает cursor, который хранит результаты на стороне клиента, так что итератор будет возвращать данные из уже созданного списка.

for rec in cursor:
    .....

для такого варианта нужен SSCursor
mysqldb/cursors.py
pep249

PooH

Ноя. 29, 2012 04:41:34

А я бы для хранения статистики пользовал что нибудь типа RRD

Lexander

Ноя. 29, 2012 11:24:34

Кстати, а чем не устраивает лог-файл веб-сервера?
Для их обработки и формирования статистики полно специализированных утилит.

Rkt

Ноя. 29, 2012 17:56:28

Спасибо всем за ответы.
Отдельная благодарность doza_and и adray. Переписал как в топике http://stackoverflow.com/questions/1808150/how-to-efficiently-use-mysqldb-sscursor и теперь скрипт память не кушает и прекрасно работает.

Почему не в файл? Так сложилось исторически. Эта база хранит статистику по трафику, какой IP c каким IP общался. Это нужно для клиентов и всяких там правоохранительных органов. Сейчас эта база живет два месяца в mysql для того что бы клиентам можно было быстро и удобно предоставить статистику, а затем парситься и складывается в тхт и архивируется. И делается это все перловым скриптом без проблем

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

PooH, RRD, конечно же, используется, но это же что бы графички рисовать, а в этом случае нужно все и навсегда сохранить.
Lexander, у нас не веб сервер, а Netflow.