* fkater@xxxxxxxxxxxxxx <fkater@xxxxxxxxxxxxxx> [100114 09:29]: > This takes about 50s, so, 800MB/50s = 16MB/s. > > However the harddisk (sata) could write 43 MB/s in the worst > case! Why is write performance limited to 16 MB/s? > I altered the binary column to STORAGE EXTERNAL. > > Some experiments with postgresql.conf (fsync off, > shared_buffers=1000MB, checkpoint_segments=256) did not > change the 50s- much (somtimes 60s sometimes a little less). > > 4 Core CPU 3 Ghz, WinXP, 1 TB SATA disk. > > > Do you have any further idea why 16MB/s seems to be the > limit here? So, your SATA disk can do 43MB/s of sequential writes, but you're example is doing: 1) Sequential writes to WAL 2) Random writes to your index 3) Sequential writes to table heap 4) Sequential writes to table' toast heap 5) Any other OS-based FS overhead Now, writes #2,3 and 4 don't happen completely concurrently with your WAL, some of them are still in postgres buffers, but easily enough to interrupt the stream of WAL enough to certainly make it believable that with everything going on on the disk, you can only write WAL at a *sustained* 16 MB/s If you're running a whole system on a single SATA which can stream 43MB/s, remember that for *every* other read/write sent do the disk, you lose up to 1MB/s (12ms seek time, read/write, and back). And in that "every other", you have FS metadata updates, any other file writes the FS flushes, etc... 20 aditional blocks being that are either read or written to disk are going to completely chop your 43MB/s rate... a. -- Aidan Van Dyk Create like a god, aidan@xxxxxxxxxxx command like a king, http://www.highrise.ca/ work like a slave.
Attachment:
signature.asc
Description: Digital signature
