Re: Postgresql 9.1 on VMWare ESXi 5.0

Em 24/08/2012 00:14, Craig Ringer escreveu:
> On 08/24/2012 10:18 AM, Edson Richter wrote:
>> Dear friends,
>>
>> Anyone has experienced extreme slowness running PostgreSQL 9.1.4 on
>> virtualized CentOS 5.8 on VMware ESXi 5.0 (with all Paravirtualized
>> drivers)?
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
> Examine `EXPLAIN ANALYZE` for virtual and physical, compare. If it's
> complicated, post both to explain.depesz.com .
> Examine `iostat`, `vmstat` and `top` to see where the bottlenecks lie.
> etc.
> --
> Craig Ringer
Your directions pointed that there are no simple answer for the problem.
I've checked the execution plans, and they were just equal to the one in
my development server. So I started from the principle that the database
is working as expected (since it worked in my development server and not
in the production server). The problem must be the O.S. or Tomcat.

I got a Thread Dump in Tomcat, and discovered that I had biased locks
blocking my app on HTTP threads (a tip for checking O.S. networking,
that I did later). So in Tomcat I've disable biased locks. I decided
also to enable Large Pages, that is recommended by VMWare when running
J2EE apps in ESXi. Anyone looking how to do that, check how to in his
JVM docs, because every vendor and every version has different settings
for that.

After, I have started in a trial and error cycle with /etc/sysctl.conf,
changing parameters for Swap and Networking.
There was few wrong settings in /etc/sysctl.conf (specially IPV4 tcp
queue, reordering, and memory settings).

After adjustments, my system returned to acceptable performance again.

Thanks for your support, without your toughs I would never started to
check what would be wrong.

Regards,