>
> d'oh, just realized what you're telling me here. /me smacks forehead.
> Let's try effective_cache of 183105... (75%). Starting both servers,
> waiting for big fetch to start, and...
>
> procs memory swap io
> system cpu
> r b w swpd free buff cache si so bi bo in cs us
> sy id
> 0 0 0 2800 11920 40532 1906516 0 0 0 0 521 8
> 0 0 100
> 0 1 0 2800 11920 40532 1906440 0 0 356 52 611 113
> 1 3 97
> 0 1 0 2800 11920 40532 1906424 0 0 20604 0 897 808
> 1 18 81
> 0 1 0 2800 11920 40532 1906400 0 0 26112 0 927 820
> 1 13 87
> 0 1 0 2800 11920 40532 1906384 0 0 26112 0 923 812
> 1 12 87
> 0 1 0 2800 11920 40532 1906372 0 0 24592 0 921 805
> 1 13 87
> 0 1 0 2800 11920 40532 1906368 0 0 3248 48 961 1209
> 0 4 96
> 0 1 0 2800 11920 40532 1906368 0 0 2600 0 845 1631
> 0 2 98
> 0 1 0 2800 11920 40532 1906364 0 0 2728 0 871 1714
> 0 2 98
>
> better in vmstat... but the query doesn't work any better
> unfortunately.
Your io now looks like you're getting a few seconds of continuous read,
and then you're getting into maxing out random reads. These look about
right for a single ide drive.
> The frustrating thing is, we also have a UP P3-500 with 512M RAM and
> two
> IDE drives with the same PG install which is doing okay with this load
> -- still half the speed of MS-SQL2K, but usable. I'm at a loss.
I wonder if you're doing table scans. From the earlier trace, it looked
like you have a few parallel select/process/insert processes going.
If that's the case, you might be getting a big sequential scan at
first, then at some point you have enough selects going that it wtarts
looking more like random access.
Can you run one of the selects from the psql console and see how fast
it runs? Do your inserts have any foreign key relations?
One thing you might try is to shut down the postmaster and move the
pg_clog and pg_xlog directories to the other drive, and leave symlinks
pointing back. That should help your insert performance by putting the
wal on a seperate drive from the table data. It will really help if you
wind up having uncached read and write access at the same time. You
also might gain by using software raid 0 (with large stripe size, 512k
or so) across both drives, but if you don't have the appropriate
paritions in there now it's going to be a bunch of work.
eric
