2012/3/14 François Beausoleil <francois@xxxxxxxxxxx>: > Le mardi 13 mars 2012 à 11:15, Merlin Moncure a écrit : > >> 2012/3/13 François Beausoleil <francois@xxxxxxxxxxx (mailto:francois@xxxxxxxxxxx)>: >> > >> > 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? >> > > > I took the time to gather statistics about the database server: https://gist.github.com/07bbf8a5b05b1c37a7f2 > > The files are a series of roughly 30 second samples, while the system is under production usage. When I quoted 30-40 transactions per second, I was actually referring to the number of messages processed from my message queue. Going by the PostgreSQL numbers, xact_commit tells me I manage 288 commits per second. It's much better than I anticipated. > > Anyways, if anybody has comments on how I could increase throughput, I'd appreciate. My message queues are almost always backed up by 1M messages, and it's at least partially related to PostgreSQL: if the DB can write faster, I can manage my backlog better. > > I'm still planning on going with batch processing, but I need to do something ASAP to give me just a bit more throughput. well your iowait numbers are through the roof which makes things pretty simple from a diagnosis point of view: your storage is overloaded. the only remedies are to try and make your queries more efficient so that you are doing less writing, better use of transactions, etc. but looking at the log it appears the low hanging fruit is already grabbed (synchronous_commit=off, etc). so you have to choose from a list of not very pleasant options: *) fsync=off *) tune the application *) bring more/faster storage online. a single ssd would probably make your problem disappear. in the vm world, hopefully you can at least bring another volume online and move your wal to that. *) HARDWARE. In the entirety of my career, I have never found anything more perplexing than the general reluctance to upgrade hardware to solve hardware related performance bottlenecks. Virtualization is great technology but is nowhere near good enough in my experience to handle high transaction rate database severs. A 5000$ server will solve your issue, and you'll spend that in two days scratching your head trying to figure out the issue (irritating your customers all the while). merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
