From: | Dave Cramer <pg(at)fastcrypt(dot)com> |
---|---|
To: | "Reuven M(dot) Lerner" <reuven(at)lerner(dot)co(dot)il> |
Cc: | List <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: Bound parameters on Linux are extremely slow (compared to unbound and/or Windows) |
Date: | 2013-05-25 23:28:13 |
Message-ID: | CADK3HH+A4RiLOhSRwaEFP1gTBRSEHeCxMsYG-cgDokigJ6wUgA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)lerner(dot)co(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Reuven M. Lerner | 2013-05-25 23:47:24 | Re: Bound parameters on Linux are extremely slow (compared to unbound and/or Windows) |
Previous Message | Reuven M. Lerner | 2013-05-25 23:21:13 | Re: Bound parameters on Linux are extremely slow (compared to unbound and/or Windows) |