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

[Ruslan Zakirov]

Привет,

Посмотрел EXPLAINы в Pg, стало более понятно. При двух условиях, когда
каждое отдельно условие не ограничивает диапазон, оценки количества
строк сильно зашкаливают. Это понятно. Не учитывается связь между
значениями начала и конца.

По этому PG и скорее всего mysql отказываются от index based range
scan и переходят к full sequential index scan. Если знать максимальный
размер блока ( MAX(iend-istart) ), то, добавив дополнительные условия,
можно помочь БД получить более точную оценку и выбрать оптимальный
план.

Получение этого значения в 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.