Re: [PERFORM] Hardware/OS recommendations for large databases (
Mark Kirkwood wrote: The test is SELECT 1 FROM table That should read "The test is SELECT count(1) FROM table" ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Hyperthreading slows processes?
Yeah, it's pretty much a known issue for postgres Dave On 20-Nov-05, at 4:46 PM, Craig A. James wrote: This article on ZDNet claims that hyperthreading can *hurt* performance, due to contention in the L1/L2 cache by a second process: http://news.zdnet.co.uk/0,39020330,39237341,00.htm Has anyone tested this on Postgres yet? (And based on a recent somewhat caustic thread about performance on this forum, I'm going to avoid speculation, and let those who actually *know* answer! ;-) Craig ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Hardware/OS recommendations for large databases (
Alan Stange wrote: Another data point. We had some down time on our system today to complete some maintenance work. It took the opportunity to rebuild the 700GB file system using XFS instead of Reiser. One iostat output for 30 seconds is avg-cpu: %user %nice%sys %iowait %idle 1.580.00 19.69 31.94 46.78 Device:tpskB_read/skB_wrtn/skB_readkB_wrtn sdd 343.73175035.73 277.555251072 8326 while doing a select count(1) on the same large table as before. Subsequent iostat output all showed that this data rate was being maintained. The system is otherwise mostly idle during this measurement. The sequential read rate is 175MB/s. The system is the same as earlier, one cpu is idle and the second is ~40% busy doing the scan and ~60% idle. This is postgresql 8.1rc1, 32KB block size. No tuning except for using a 1024KB read ahead. The peak speed of the attached storage is 200MB/s (a 2Gb/s fiber channel controller). I see no reason why this configuration wouldn't generate higher IO rates if a faster IO connection were available. Can you explain again why you think there's an IO ceiling of 120MB/s because I really don't understand? I think what is going on here is that Luke's observation of the 120 Mb/s rate is taken from data using 8K block size - it looks like we can get higher rates with 32K. A quick test on my P3 system seems to support this (the numbers are a bit feeble, but the difference is interesting): The test is SELECT 1 FROM table, stopping Pg and unmounting the file system after each test. 8K blocksize: 25 s elapsed 48 % idle from vmstat (dual cpu system) 70 % busy from gstat (Freebsd GEOM io monitor) 181819 pages in relation 56 Mb/s effective IO throughput 32K blocksize: 23 s elapsed 44 % idle from vmstat 80 % busy from gstat 45249 pages in relation 60 Mb/s effective IO throughput I re-ran these several times - very repeatable (+/- 0.25 seconds). This is Freebsd 6.0 with the readahead set to 16 blocks, UFS2 filesystem created with 32K blocksize (both cases). It might be interesting to see the effect of using 16K (the default) with the 8K Pg block size, I would expect this to widen the gap. Cheers Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Hyperthreading slows processes?
This article on ZDNet claims that hyperthreading can *hurt* performance, due to contention in the L1/L2 cache by a second process: http://news.zdnet.co.uk/0,39020330,39237341,00.htm Has anyone tested this on Postgres yet? (And based on a recent somewhat caustic thread about performance on this forum, I'm going to avoid speculation, and let those who actually *know* answer! ;-) Craig ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Hardware/OS recommendations for large databases (
Greg Stark wrote:
Alan Stange <[EMAIL PROTECTED]> writes:
Iowait is time spent waiting on blocking io calls. As another poster
pointed out, you have a two CPU system, and during your scan, as predicted,
one CPU went 100% busy on the seq scan. During iowait periods, the CPU can
be context switched to other users, but as I pointed out earlier, that's not
useful for getting response on decision support queries.
I don't think that's true. If the syscall was preemptable then it wouldn't
show up under "iowait", but rather "idle". The time spent in iowait is time in
uninterruptable sleeps where no other process can be scheduled.
That would be wrong. The time spent in iowait is idle time. The
iowait stat would be 0 on a machine with a compute bound runnable
process available for each cpu.
Come on people, read the man page or look at the source code. Just
stop making stuff up.
iowait time is idle time. Period. This point has been debated endlessly for
Solaris and other OS's as well.
Here's the man page:
%iowait
Show the percentage of time that the CPU or CPUs were
idle during which the system had an outstanding disk I/O
request.
If the system had some other cpu bound work to perform you wouldn't ever see
any iowait time. Anyone claiming the cpu was 100% busy on the sequential scan
using the one set of numbers I posted is misunderstanding the actual metrics.
That's easy to test. rerun the test with another process running a simple C
program like "main() {while(1);}" (or two invocations of that on your system
because of the extra processor). I bet you'll see about half the percentage of
iowait because postres will get half as much opportunity to schedule i/o. If
what you are saying were true then you should get 0% iowait.
Yes, I did this once about 10 years ago. But instead of saying "I bet"
and guessing at the result, you should try it yourself. Without
guessing, I can tell you that the iowait time will go to 0%. You can do
this loop in the shell, so there's no code to write. Also, it helps to
do this with the shell running at a lower priority.
-- Alan
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PERFORM] Hardware/OS recommendations for large databases (
On Sun, Nov 20, 2005 at 09:22:41AM -0500, Greg Stark wrote: > I don't think that's true. If the syscall was preemptable then it wouldn't > show up under "iowait", but rather "idle". The time spent in iowait is time in > uninterruptable sleeps where no other process can be scheduled. You are confusing userspace with kernel space. When a process is stuck in uninterruptable sleep, it means _that process_ can't be interrupted (say, by a signal). The kernel can preempt it without problems. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Hardware/OS recommendations for large databases (
Alan Stange <[EMAIL PROTECTED]> writes:
> > Iowait is time spent waiting on blocking io calls. As another poster
> > pointed out, you have a two CPU system, and during your scan, as predicted,
> > one CPU went 100% busy on the seq scan. During iowait periods, the CPU can
> > be context switched to other users, but as I pointed out earlier, that's not
> > useful for getting response on decision support queries.
I don't think that's true. If the syscall was preemptable then it wouldn't
show up under "iowait", but rather "idle". The time spent in iowait is time in
uninterruptable sleeps where no other process can be scheduled.
> iowait time is idle time. Period. This point has been debated endlessly for
> Solaris and other OS's as well.
>
> Here's the man page:
> %iowait
> Show the percentage of time that the CPU or CPUs were
> idle during which the system had an outstanding disk I/O
> request.
>
> If the system had some other cpu bound work to perform you wouldn't ever see
> any iowait time. Anyone claiming the cpu was 100% busy on the sequential scan
> using the one set of numbers I posted is misunderstanding the actual metrics.
That's easy to test. rerun the test with another process running a simple C
program like "main() {while(1);}" (or two invocations of that on your system
because of the extra processor). I bet you'll see about half the percentage of
iowait because postres will get half as much opportunity to schedule i/o. If
what you are saying were true then you should get 0% iowait.
--
greg
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PERFORM] Hardware/OS recommendations for large databases (
William Yu wrote: Alan Stange wrote: Luke Lonergan wrote: The "aka iowait" is the problem here - iowait is not idle (otherwise it would be in the "idle" column). Iowait is time spent waiting on blocking io calls. As another poster pointed out, you have a two CPU system, and during your scan, as iowait time is idle time. Period. This point has been debated endlessly for Solaris and other OS's as well. I'm sure the the theory is nice but here's my experience with iowait just a minute ago. I run Linux/XFce as my desktop -- decided I wanted to lookup some stuff in Wikipedia under Mozilla and my computer system became completely unusable for nearly a minute while who knows what Mozilla was doing. (Probably loading all the language packs.) I could not even switch to IRC (already loaded) to chat with other people while Mozilla was chewing up all my disk I/O. So I went to another computer, connected to mine remotely (slow...) and checked top. 90% in the "wa" column which I assume is the iowait column. It may be idle in theory but it's not a very useful idle -- wasn't able to switch to any programs already running, couldn't click on the XFce launchbar to run any new programs. So, you have a sucky computer.I'm sorry, but iowait is still idle time, whether you believe it or not. -- Alan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Hardware/OS recommendations for large databases (
On Sat, Nov 19, 2005 at 08:13:09AM -0800, Luke Lonergan wrote: > Iowait is time spent waiting on blocking io calls. To be picky, iowait is time spent in the idle task while the I/O queue is not empty. It does not matter if the I/O is blocking or not (from userspace's point of view), and if the I/O was blocking (say, PIO) from the kernel's point of view, it would be counted in system. /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Hardware/OS recommendations for large databases (
Alan Stange wrote: Luke Lonergan wrote: The "aka iowait" is the problem here - iowait is not idle (otherwise it would be in the "idle" column). Iowait is time spent waiting on blocking io calls. As another poster pointed out, you have a two CPU system, and during your scan, as iowait time is idle time. Period. This point has been debated endlessly for Solaris and other OS's as well. I'm sure the the theory is nice but here's my experience with iowait just a minute ago. I run Linux/XFce as my desktop -- decided I wanted to lookup some stuff in Wikipedia under Mozilla and my computer system became completely unusable for nearly a minute while who knows what Mozilla was doing. (Probably loading all the language packs.) I could not even switch to IRC (already loaded) to chat with other people while Mozilla was chewing up all my disk I/O. So I went to another computer, connected to mine remotely (slow...) and checked top. 90% in the "wa" column which I assume is the iowait column. It may be idle in theory but it's not a very useful idle -- wasn't able to switch to any programs already running, couldn't click on the XFce launchbar to run any new programs. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Hardware/OS recommendations for large databases (
Mark Kirkwood wrote: - I am happy that seqscan is cpu bound after ~110M/s (It's cpu bound on my old P3 system even earlier than that) Ahem - after reading Alan's postings I am not so sure, ISTM that there is some more investigation required here too :-). ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
