ФорумПрограммированиеPHP для идиотов → PDO prepared statement тормозит

PDO prepared statement тормозит

  • Nyaah

    Сообщения: 574 Репутация: N Группа: Джедаи

    Spritz 27 июня 2012 г. 7:44

    Есть таблица с 500к записей, и тестовый код:
    <?php

    $db = new PDO('pgsql:dbname=db3;host=sandbox3', 'pguser_ro', 'mnDjsi3oqwpEfs9');

    echo "test exec: ";
    $s = microtime(true);
    $stmt = $db->prepare("SELECT * FROM period_38.project_1 WHERE ns_left>=127382 AND ns_right<=430109 AND rank_1=8 ORDER BY depth ASC OFFSET 40 LIMIT 20");
    $result = $stmt->execute();
    $e = microtime(true);
    echo (($e - $s) * 1000.0), PHP_EOL;

    echo "test prepared: ";
    $s = microtime(true);
    $stmt = $db->prepare("SELECT * FROM period_38.project_1 WHERE ns_left>=:nsLeft AND ns_right<=:nsRight AND rank_1=:rank ORDER BY depth ASC OFFSET :offset LIMIT :limit");
    $stmt->bindValue(':nsLeft', 127382, PDO::PARAM_INT);
    $stmt->bindValue(':nsRight', 430109, PDO::PARAM_INT);
    $stmt->bindValue(':rank', 8, PDO::PARAM_INT);
    $stmt->bindValue(':offset', 40, PDO::PARAM_INT);
    $stmt->bindValue(':limit', 20, PDO::PARAM_INT);
    $result = $stmt->execute();
    $e = microtime(true);
    echo (($e - $s) * 1000.0), PHP_EOL;
    И получаем весьма странный результат:
    test exec: 6.2339305877686
    test prepared: 1377.2549629211
    Перемена мест слагаемых на сумму не влияет никак, разница в скорости на два порядка сохраняется…
    Кто-нить сталкивался с таким странным поведением PDO драйвера?
    Work, buy, consume, die
  • vasa_c

    Сообщения: 3127 Репутация: N Группа: в ухо

    Spritz 27 июня 2012 г. 7:48, спустя 4 минуты 24 секунды

    замерь отдельно
    $stmt = $db->prepare();

    и
    $result = $stmt->execute();
  • Nyaah

    Сообщения: 574 Репутация: N Группа: Джедаи

    Spritz 27 июня 2012 г. 8:20, спустя 31 минуту 39 секунд

    Измерял, проблема именно в execute(), там разниче порядка 16 мс была между экзеком и подготовкой, то есть что 1.3 секунды, что 1.28.
    Тестил отдельно из жавы и си, как обычный запрос, так и подготоваленный, везде результат для обычного запроса меньше 15мс, для подготовленного меньше 25.
    Какая-то шляпа с PDOStatement::execute(), причем именно с параметрами.
    Сейчас пришёл домой и подумал, что нужно было ещё на фряхе проверить, может это у меня на рабочей машине какая-то локальная проблема.
    Work, buy, consume, die
  • kostyl

    Сообщения: 5203 Репутация: N Группа: Джедаи

    Spritz 27 июня 2012 г. 10:21, спустя 2 часа 1 минуту 4 секунды

    Nyaah, попробуй не биндить это
    $stmt->bindValue(':offset', 40, PDO::PARAM_INT);
    $stmt->bindValue(':limit', 20, PDO::PARAM_INT);
  • Nyaah

    Сообщения: 574 Репутация: N Группа: Джедаи

    Spritz 28 июня 2012 г. 0:14, спустя 13 часов 53 минуты 42 секунды

    Все норм, я мудак, PDO тут не причем.
    PDO драйвер юзает возможности самой базы данных для работы с подготовленными запросами, соответственно для моего запроса будет сделано нечто типа:
    PREPARE test_select_prepared(int, int, smallint, int, int) AS SELECT * FROM period_38.project_1 WHERE ns_left>=$1 AND ns_right<=$2 AND rank_1=$3 ORDER BY depth ASC OFFSET $4 LIMIT $5;
    EXECUTE test_select_prepared(127382, 430109, 8, 40, 20);

    Смотрим результат анализа запроса:
    Limit (cost=5333.44..10666.89 rows=519 width=183) (actual time=163.653..1351.766 rows=13 loops=1)
    -> Index Scan using project_1_idx_depth on project_1 (cost=0.00..53293.32 rows=5186 width=183) (actual time=43.749..1351.507 rows=53 loops=1)
    Filter: ((ns_left >= $1) AND (ns_right <= $2) AND (rank_1 = $3))
    Total runtime: 1352.029 ms
    В свою очередь для готового запроса:
    EXPLAIN ANALYZE SELECT * FROM period_38.project_1 WHERE ns_left>=127382 AND ns_right<=430109 AND rank_1=8 ORDER BY depth ASC OFFSET 40 LIMIT 20;
    Limit (cost=474.47..474.52 rows=20 width=183) (actual time=1.605..1.715 rows=13 loops=1)
    -> Sort (cost=474.37..474.59 rows=89 width=183) (actual time=1.369..1.524 rows=53 loops=1)
    Sort Key: depth
    Sort Method: quicksort Memory: 35kB
    -> Index Scan using project_1_idx_rank_1 on project_1 (cost=0.00..471.49 rows=89 width=183) (actual time=0.281..1.053 rows=53 loops=1)
    Index Cond: (rank_1 = 8)
    Filter: ((ns_left >= 127382) AND (ns_right <= 430109))
    Total runtime: 1.995 ms
    Спустя 275 сек.
    зы. в си и жаве у нас по умолчанию препейред стейтменты эмулируются на клиентской стороне, потому получилось как получилось
    Work, buy, consume, die
  • kostyl

    Сообщения: 5203 Репутация: N Группа: Джедаи

    Spritz 28 июня 2012 г. 7:04, спустя 6 часов 49 минут 43 секунды

    Nyaah, то есть подготовка запроса на сервере БД замедлили общее время в 200 раз? Че за фигня…
  • Абырвалг

    Сообщения: 6476 Репутация: N Группа: Джедаи

    Spritz 28 июня 2012 г. 7:15, спустя 11 минут 24 секунды

    +1, не понимаю как это возможно
  • Nyaah

    Сообщения: 574 Репутация: N Группа: Джедаи

    Spritz 28 июня 2012 г. 9:05, спустя 1 час 49 минут 23 секунды

    что вы как дети, результата эксплейна не видите чтоли? =)
    Имеею: приличных размеров таблицу и кучу индексов (там по чему только поиск не осуществляется).
    Селективность индекса project_1_idx_rank_1 в общем случае хреновая, он имеет значения от 0 до 8, при чем 0 - порядка 380к записей, 1 ~ 60к, 2 ~ 9к и тд, 8 - 53 строки.
    При использовании подготовленного запроса, который этот rank_1 юзает в WHERE выражении планировщик запросов ещё не знает какое значение я передам, а для общего случая получается, что стоимость сортировки по project_1_idx_depth и последующего скана полученных записей ниже, чем выборка по project_1_idx_rank_1 (и это правда, если rank_1 задать равным нулю, то при использовании индекса project_1_idx_rank_1 выборка осуществляется за ~1500мс, а при использовании project_1_idx_depth те же 1.3 секунды).
    Если передавать уже готовый запрос, планировщик смотрит статистику по индексам и видит, ага там всего примерно 89 строк с rank_1=8, значит быстрее эти записи взять и только потом отсортировать по глубине.
    Сегодня добавил составной индекс (rank_id, depth) в таблицу и все шуршит, 6мс непосредственно на сервере для подготовленного запроса.
    Work, buy, consume, die
  • kostyl

    Сообщения: 5203 Репутация: N Группа: Джедаи

    Spritz 28 июня 2012 г. 9:42, спустя 36 минут 41 секунду

    Nyaah, ага…. а на мускуле пробовал?
  • Nyaah

    Сообщения: 574 Репутация: N Группа: Джедаи

    Spritz 28 июня 2012 г. 11:46, спустя 2 часа 4 минуты 9 секунд

    не, мне лень базу устанавливать, настраивать =)
    Work, buy, consume, die
  • Nyaah

    Сообщения: 574 Репутация: N Группа: Джедаи

    Spritz 29 июня 2012 г. 0:08, спустя 12 часов 22 минуты 43 секунды

    kostyl, глянул исходники, pdo_mysql_statement использует mysql_stmt_* то есть также как и для пг используются возможности самой бд для работы с подготовленными запросами.
    Поискал инфу о планировке подготовленного запроса в MySQL, единственное что нашёл, более менее адекватное: тут, ну и на стековерфлоу поискал, там тоже пишут, что на каждый execute строится свой план запроса.
    Тесты ручные (юзал SELECT SQL_NO_CACHE везде, база не настроена на большие объёмы информации, потому скорость низкая):
    rank_1=6 (судя по эксплейну готового запроса используется idx_rank_1): готовый запрос: 0.05c, подготовленный 0.05c
    rank_1=0 (судя по эксплейну готового запроса используется опять idx_rank_1, хз почему, теперь уже точно лень разбираться =)): готовый запрос: 3.86c, подготовленный 3.93c
    EXPLAIN подготовленного запроса в майэскьюэле сделать нельзя к сожалению, потому полагаюсь на время исполнения: для MySQL безразлично как ты делаешь запрос
    Work, buy, consume, die
  • kostyl

    Сообщения: 5203 Репутация: N Группа: Джедаи

    Spritz 29 июня 2012 г. 8:04, спустя 7 часов 55 минут 39 секунд

    Nyaah, т.е. prep-statements в pg сосет у mysql? ))
  • Nyaah

    Сообщения: 574 Репутация: N Группа: Джедаи

    Spritz 29 июня 2012 г. 10:27, спустя 2 часа 23 минуты 5 секунд

    Зависит от радиуса кривизны рук:
    а) если без индексов, то пг подготовленный запрос работает чуть быстрее, так как план запроса уже составлен, нужно только переменные подставить и вытащить данные, а в майэскуэль запускается оптимизатор и только после вытаскиваются данные.
    б) с кривыми индексами (как у меня было): пг подставит значения переменных и при неудачном стечении обстоятельств для поиска будет заюзан не оптимальный индекс, в том же случае в маэскуэль оптимизатор сработает, и данные будут получены быстрее
    в) индексы нормально проставлены: и там и там будет заюзан оптимальный путь поиска нужных строк
    В моём случае сработал вариант б =)
    Вообще, по большому счёту, их некорректно сравнивать, все таки они совсем разные
    Work, buy, consume, die
  • kostyl

    Сообщения: 5203 Репутация: N Группа: Джедаи

    Spritz 30 июня 2012 г. 1:25, спустя 14 часов 57 минут 44 секунды

    да

Пожалуйста, авторизуйтесь, чтобы написать комментарий!