Re: Bound parameters on Linux are extremely slow (compared to unbound and/or Windows)

Поиск
Список
Период
Сортировка
От Dave Cramer
Тема Re: Bound parameters on Linux are extremely slow (compared to unbound and/or Windows)
Дата
Msg-id CADK3HH+A4RiLOhSRwaEFP1gTBRSEHeCxMsYG-cgDokigJ6wUgA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Bound parameters on Linux are extremely slow (compared to unbound and/or Windows)  ("Reuven M. Lerner" <reuven@lerner.co.il>)
Ответы Re: Bound parameters on Linux are extremely slow (compared to unbound and/or Windows)
Список pgsql-jdbc
Дерево обсуждения


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Sat, May 25, 2013 at 7:21 PM, Reuven M. Lerner <reuven@lerner.co.il> wrote:
Hi, Dave.  Thanks for the response.  You wrote:

The problem lies in the fact that JDBC uses an unnamed statement to do
the query. The planner can't plan these very well as the parameters are
not available at plan time.

Is there any way to avoid this, and to give the planner more hints?

No, not really, pg doesn't have hints.
 
 Is there an option in JDBC that can give us a more direct connection?

Yes but you will have no protection from sql injection. You can use protocol 2  add protocolVersion=2 to the url

This does not explain the slowness on linux however. Is postgresql tuned
on linux, or windows ?

The tuning suggestions that I've given were pretty plain overall, and mostly involved changing the amounts given to shared_buffers and effective_cache_size.  I know that I gave them suggestions for the Linux box, so if any tuning was done, it was actually there.  I was quite surprised to hear that they were even trying it on Windows, let alone that it gave significantly better performance.

OK, so 25% memory to shared memory and 75%effective cache, you should also lower randam page cost as this is very conservative. 

On the Linux side, they've switched from ext3 to (non-journaled) ext2, to improve performance.  Execution is excellent on Linux, except via JDBC and bound parameters.

This is not advisable, the difference is minimal the risk is great. They would be better with ex4.

Can you get the result of explain analyze for prepare/execute and the non prepared statement from psql?


Any other suggestions regarding the differences between Windows and Linux?  As you can imagine, this is really puzzling me (er, us).

 Are the boxes really the same hardware wise ?

Dave


Reuven

--
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
http://lerner.co.il * Skype/AIM: reuvenlerner

В списке pgsql-jdbc по дате отправления:

Предыдущее
От: "Reuven M. Lerner"
Дата:
Сообщение: Re: Bound parameters on Linux are extremely slow (compared to unbound and/or Windows)
Следующее
От: "Reuven M. Lerner"
Дата:
Сообщение: Re: Bound parameters on Linux are extremely slow (compared to unbound and/or Windows)