2012/3/13 François Beausoleil <franc...@teksol.info>: > > > Le mardi 13 mars 2012 à 10:48, Merlin Moncure a écrit : > >> 2012/3/12 François Beausoleil <franc...@teksol.info >> (mailto:franc...@teksol.info)>: >> > Currently, I can sustain 30-40 writes per second on a Rackspace VPS. I >> > know it's not the ideal solution, but that's what I'm working with. >> > Following vmstat, the server is spending 30 to 40% of it's time in iowait. >> > I don't have measurements as to what files are touched, and I'd welcome >> > suggestions to measure the time PostgreSQL actually spends writing indices >> > vs data. >> > > >> > >> > >> >> >> you're almost certainly blocking on fsync. A real quick'n'dirty way >> to confirm this (although it wont be as fast as COPY) would be to wrap >> your inserts in a transaction. VMs tend to have really horrible >> storage latency which can hurt postgres performance. Another option >> would be to relax your commit policy (for example by flipping >> synchronous_commit) if that fits within your safety requirements. >> > > > I already applied the tricks you have here: I have a transaction, and > synchronous_commit is off. I also have checkpoint_segments set to 96, and 10 > minutes. > > I'll go with the COPY, since I can live with the batched requirements just > fine.
30-40 'in transaction' i/o bound inserts is so slow as to not really be believable unless each record is around 1 megabyte because being in transaction removes storage latency from the equation. Even on a crappy VM. As a point of comparison my sata workstation drive can do in the 10s of thousands. How many records are you inserting per transaction? merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
