Skip site navigation (1) Skip section navigation (2)

Re: pg9.0.3 explain analyze running very slow compared to a different box with much less configuration

From: DM <dm(dot)aeqa(at)gmail(dot)com>
To: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: pg9.0.3 explain analyze running very slow compared to a different box with much less configuration
Date: 2011-03-25 01:12:11
Message-ID: AANLkTin2Lp+uMY_NLQkvjhyhVk+0qn1XgjKgfbHH3H-i@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Thank you for your research on and posting on it, when I first encountered
this issue I saw your posting/research on this issue, this gave me a great
insight.

gettimeofday() on my new box is slow, after further research we found that,
when we set ACPI=Off, we got a good clock performance even the explain
analyze gave approximately gave the right values, but the hyperthreading is
off.

could you guide me how to set, the parameter current_clocksource to TSC,


Thanks
Deepak

On Thu, Mar 24, 2011 at 5:07 AM, Achilleas Mantzios <
achill(at)matrix(dot)gatewaynet(dot)com> wrote:

> Στις Thursday 24 March 2011 13:39:19 ο/η Marti Raudsepp έγραψε:
> > On Thu, Mar 24, 2011 at 11:11, Achilleas Mantzios
> > <achill(at)matrix(dot)gatewaynet(dot)com> wrote:
> > > My problem had to do with the speed of gettimeofday. You might want to
> do some special setting regarding
> > > your box's way of reading time for the hw clock.
> >
> > Just for extra info, on x86, TSC is usually the "fast" timeofday
> > implementation. On recent CPUs in single-socket configurations, TSC
> > should always be available, regardless of any power management. I
> > don't know about multi-socket. If you want to know whether your kernel
> > is using tsc, run:
> >
>
> That's what i am experiencing as well, in two of my FreeBSD boxes
> (work/home) i get:
>
> phenom ii X4 :
> ==========
> % sysctl -a | grep -i timecounter
> kern.timecounter.tick: 1
> kern.timecounter.choice: TSC(-100) HPET(900) ACPI-fast(1000) i8254(0)
> dummy(-1000000)
> kern.timecounter.hardware: TSC
> kern.timecounter.stepwarnings: 0
> kern.timecounter.tc.i8254.mask: 65535
> kern.timecounter.tc.i8254.counter: 1960
> kern.timecounter.tc.i8254.frequency: 1193182
> kern.timecounter.tc.i8254.quality: 0
> kern.timecounter.tc.ACPI-fast.mask: 4294967295
> kern.timecounter.tc.ACPI-fast.counter: 3642319843
> kern.timecounter.tc.ACPI-fast.frequency: 3579545
> kern.timecounter.tc.ACPI-fast.quality: 1000
> kern.timecounter.tc.HPET.mask: 4294967295
> kern.timecounter.tc.HPET.counter: 1160619197
> kern.timecounter.tc.HPET.frequency: 14318180
> kern.timecounter.tc.HPET.quality: 900
> kern.timecounter.tc.TSC.mask: 4294967295
> kern.timecounter.tc.TSC.counter: 2788277817
> kern.timecounter.tc.TSC.frequency: 3400155810
> kern.timecounter.tc.TSC.quality: -100
> kern.timecounter.smp_tsc: 0
> kern.timecounter.invariant_tsc: 1
>
> Pentium 4
> ======
> % sysctl -a | grep -i timecounter
> kern.timecounter.tick: 1
> kern.timecounter.choice: TSC(800) ACPI-fast(1000) i8254(0) dummy(-1000000)
> kern.timecounter.hardware: ACPI-fast
> kern.timecounter.stepwarnings: 0
> kern.timecounter.tc.i8254.mask: 65535
> kern.timecounter.tc.i8254.counter: 13682
> kern.timecounter.tc.i8254.frequency: 1193182
> kern.timecounter.tc.i8254.quality: 0
> kern.timecounter.tc.ACPI-fast.mask: 16777215
> kern.timecounter.tc.ACPI-fast.counter: 6708142
> kern.timecounter.tc.ACPI-fast.frequency: 3579545
> kern.timecounter.tc.ACPI-fast.quality: 1000
> kern.timecounter.tc.TSC.mask: 4294967295
> kern.timecounter.tc.TSC.counter: 3109326068
> kern.timecounter.tc.TSC.frequency: 2663194296
> kern.timecounter.tc.TSC.quality: 800
> kern.timecounter.smp_tsc: 0
> kern.timecounter.invariant_tsc: 0
>
> TSC, it seems, outperform the rest of clocks in terms of frequency.
>
> > cat /sys/devices/system/clocksource/clocksource0/current_clocksource
> >
> > On older CPUs, you often had to disable some sort of power management
> > in order to get a stable TSC -- the "ondemand" scaling governor is the
> > top suspect. Disabling this is distro-specific. You have to reboot to
> > get the kernel to re-test TSC. Unfortunately disabling power
> > management later at boot doesn't help you, you have to prevent it from
> > activating at all.
> >
> > For debugging, grepping dmesg for tsc or clocksource is often helpful.
> > On machines with unstable TSC you'll see output like this:
> >
> > [    0.000000] Fast TSC calibration using PIT
> > [    0.164068] checking TSC synchronization [CPU#0 -> CPU#1]: passed.
> > [    0.196730] Switching to clocksource tsc
> > [    0.261347] Marking TSC unstable due to TSC halts in idle
> > [    0.261536] Switching to clocksource acpi_pm
> >
> > If you just want to get repeatable timings, you can force both
> > machines to use the hpet clocksource:
> > echo hpet >
> /sys/devices/system/clocksource/clocksource0/current_clocksource
> >
> > Marti
> >
>
>
>
> --
> Achilleas Mantzios
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

In response to

Responses

pgsql-performance by date

Next:From: Adarsh SharmaDate: 2011-03-25 06:35:54
Subject: Why Index is not used
Previous:From: Claudio FreireDate: 2011-03-24 22:23:15
Subject: Re: Shouldn't we have a way to avoid "risky" plans?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group