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
---------------------------(end of broadcast)--------------------------- TIP 3: 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
