[Moscow.pm] Написал интерфейс к базе от ipgeobase.ru

Пн Ноя 30 23:58:13 PST 2009

2009/12/1 Ruslan Zakirov <ruslan.zakirov на gmail.com>:
> Посмотрел EXPLAINы в Pg, стало более понятно. При двух условиях, когда
> каждое отдельно условие не ограничивает диапазон, оценки количества
> строк сильно зашкаливают. Это понятно. Не учитывается связь между
> значениями начала и конца.
>
> По этому PG и скорее всего mysql отказываются от index based range
> scan и переходят к full sequential index scan. Если знать максимальный
> размер блока ( MAX(iend-istart) ), то, добавив дополнительные условия,
> можно помочь БД получить более точную оценку и выбрать оптимальный
> план.

Для ipgeobase это действительно должно сильно помочь.

У нас используется другая база для определения региона по ip,
и там самая большая сеть 15.0.0.0 - 22.255.255.255 (США)
Соответственно в нашем случае такая оптимизация не выстрелит ;-)
Хотя, конечно, можно большие сети разбивать на куски, скажем /16

> Получение этого значения в 5 раз медленнее выполнения оптимизированого
> запроса. Так что значение нужно кешировать и соответственно кеш
> проверять как-то. Итого дополнительные накладные расходы на стороне
> клиента.
>
> 2009/12/1 Ruslan Zakirov <ruslan.zakirov на gmail.com>:
>> Понятно,
>>
>> Странные цифры. Больше похоже на баг mysql. Можете прислать EXPLAINы
>> для обоих запросов?
>>
>> Вот вы подумайте. Да, это range scan, но он ограничен с двух сторон.
>> Индекс отсортирован по возрастанию по обоим колонкам. По этому mysql
>> находит точку по istart и начинает сканирование вверх (к уменьшению) и
>> должна остановиться как только iend станет меньше нужного нам
>> значения. В итоге должно быть просмотрено записей максимум на две
>> больше чем реально будет возвращено.
>>
>> Ваш запрос вообще должен быть моментальным, ведь он не возвращает
>> результатов. Странно все это.
>>
>> Можете сравнить запросы совсем без сортировок?
>>
>> Я просто сейчас тестирую на Pg это дело. Потом конечно смогу
>> посмотреть на mysql, но от помощи не откажусь.
>>
>> 2009/12/1 Sergey Zhuravlev <sergey.zhuravlev на gmail.com>:
>>> Мне кажется, оптимальным решением было бы избавиться от пересечений
>>> сетей при загрузке базы в mysql.
>>> После этого можно было бы делать
>>> select * from ipgeobase where istart <= ? order by istart desc limit 1
>>> и проверять iend >= $ip на стороне перла
>>>
>>> Проверил на не на реальной базе (лень загружать), но на подобных
>>> тестовых данных:
>>>
>>> zhur на ppcdev% time perl -ME -e 'for (1..1000) {get_sql("select
>>> SQL_NO_CACHE * from ipgeobase where istart <= 62432 and iend >= 62432
>>> order by iend-istart asc limit 1")}'
>>> real    0m27.686s
>>>
>>> zhur на ppcdev% time perl -ME -e 'for (1..1000) {get_sql("select
>>> SQL_NO_CACHE * from ipgeobase where istart <= 62432 order by istart
>>> desc limit 1")}'
>>> real    0m0.256s
>>>
>>> Но для простого решения модуль конечно подходит и сейчас.
>>> И он точно несравненно лучше того, что раздаётся с сайта ;-))
>>>
>>>
>>> 2009/11/29 Ruslan Zakirov <ruslan.zakirov на gmail.com>:
>>>> 2009/11/29 Sergey Zhuravlev <sergey.zhuravlev на gmail.com>:
>>>>> Привет.
>>>>>
>>>>> Модуль клёвый.
>>>>> Только запрос из intersections - это index range scan, в среднем на
>>>>> половину таблички.
>>>>
>>>> Не уверен, что это будет половина таблички. Нужно посмотреть логи.
>>>> Конечно для нагруженного пректа я бы сделал по другому. Разбил бы все
>>>> на три структуры и индекс в памяти:
>>>>
>>>> my $index_length = 8;
>>>> # индекс - массив из 2^$index_lenght элементов
>>>> my @index = (
>>>>   <индекс первого блока, где $index_lenght первых бит начала блока == 0>
>>>>   <индекс первого блока, где $index_lenght первых бит начала блока == 1>
>>>>   <индекс первого блока, где $index_lenght первых бит начала блока == 2>
>>>>   ....
>>>> );
>>>>
>>>> В зависимости от размера маски меняется объем используемой памяти.
>>>>
>>>> Уже с этого момента можно посылать в БД, то есть указатели у нас
>>>> просто дополнят условия поиска, а можно в память смотреть:
>>>>
>>>> my @blocks = (
>>>>  [start, end, coordinates],
>>>> ...
>>>> );
>>>>
>>>> my @coordinates = (
>>>>  [long, lat, city],
>>>>  ...
>>>> );
>>>>
>>>> my @cities = (
>>>>  [name, region, federal_district],
>>>> );
>>>>
>>>>
>>>> Есть лучше идеи?
>>>>
>>>>> На нагруженных проектах это может вызвать проблемы, хотя, в случае
>>>>> mysql и 128'000 записей это достаточно быстро.
>>>>
>>>> Мне нужно было простое решение. Десяток поисков в день в окружении,
>>>> где и так памяти не очень много.
>>>>
>>>>> 2009/11/29 Ruslan Zakirov <ruz на bestpractical.com>:
>>>>>> Всем привет,
>>>>>>
>>>>>> Недавно мы обсуждали определение положения по IP адресу. Мне
>>>>>> посоветовали БД от http://ipgeobase.ru.
>>>>>>
>>>>>> Я рад, что существует такой ресурс. Был немного удивлен примерами кода
>>>>>> и очень удивлен структурой БД. Код на perl страшен, достаточно сделать
>>>>>> для него perltidy и он уже становится читабельный. Алгоритм тоже не
>>>>>> сахар, но это издержки структуры файла. Переключение на указание
>>>>>> смещения вместо номеров строк, позволит сделать быстрый поиск прямо из
>>>>>> файлов без полного сканирования или загрузки их в память.
>>>>>>
>>>>>> В итоге решил отказаться от работы с файлами и заливать все в табличку
>>>>>> БД. Написал скрипт обновления БД и модуль для работы. Пока не на
>>>>>> CPANе, но если не будет конструктивной критики и успешно уйдет в
>>>>>> продакшн, то залью на цпан и в гитхаб.
>>>>>>
>>>>>> --
>>>>>> Веселой гео локации, Руслан.
>>>>>>
>>>>>> --
>>>>>> Moscow.pm mailing list
>>>>>> moscow-pm на pm.org | http://moscow.pm.org
>>>>>>
>>>>>>
>>>>> --
>>>>> Moscow.pm mailing list
>>>>> moscow-pm на pm.org | http://moscow.pm.org
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Best regards, Ruslan.
>>>> --
>>>> Moscow.pm mailing list
>>>> moscow-pm на pm.org | http://moscow.pm.org
>>>>
>>> --
>>> Moscow.pm mailing list
>>> moscow-pm на pm.org | http://moscow.pm.org
>>>
>>
>>
>>
>> --
>> Best regards, Ruslan.
>>
>
>
>
> --
> Best regards, Ruslan.
> --
> Moscow.pm mailing list
> moscow-pm на pm.org | http://moscow.pm.org
>