general PG network slowness (possible cure) (repost)
От | Peter T. Breuer |
---|---|
Тема | general PG network slowness (possible cure) (repost) |
Дата | |
Msg-id | 200705250850.l4P8owm02940@inv.it.uc3m.es обсуждение исходный текст |
Ответы |
Re: general PG network slowness (possible cure) (repost)
(Richard Huxton <dev@archonet.com>)
Re: general PG network slowness (possible cure) (repost) ("Steinar H. Gunderson" <sgunderson@bigfoot.com>) Re: general PG network slowness (possible cure) (repost) (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-performance |
I set up pg to replace a plain gdbm database for my application. But even running to the same machine, via a unix socket * the pg database ran 100 times slower Across the net it was * about 500 to 1000 times slower than local gdbm with no cpu use to speak of. I'd heard that networked databases are slow. I might have left it at that if curiosity hadn't led me to write a network server for gdbm databases, and talk to _that_ just to get a comparison. Lo and behold and smack me with a corncob if it wasn't _slower_ than pg. On a whim I mapped the network bandwidth per packet size with the NPtcp suite, and got surprising answers .. at 1500B, naturally, the bandwidth was the full 10Mb/s (minus overheads, say 8.5Mb/s) of my pathetic little local net. At 100B the bandwidth available was only 25Kb/s. At 10B, you might as well use tin cans and taut string instead. I also mapped the network flows using ntop, and yes, the average packet size for both gdbm and pg in one direction was only about 100B or so. That's it! Clearly there are a lot of short queries going out and the answers were none too big either ( I had a LIMIT 1 in all my PG queries). About 75% of traffic was in the 64-128B range while my application was running, with the peak bandwidth in that range being about 75-125Kb/s (and I do mean bits, not bytes). Soooo ... I took a look at my implementation of remote gdbm, and did a very little work to aggregate outgoing transmissions together into lumps. Three lines added in two places. At the level of the protocol where I could tell how long the immediate conversation segment would be, I "corked" the tcp socket before starting the segment and "uncorked" it after the segment (for "cork", see tcp(7), setsockopt(2) and TCP_CORK in linux). Surprise, ... I got a speed up of hundreds of times. The same application that crawled under my original rgdbm implementation and under PG now maxed out the network bandwidth at close to a full 10Mb/s and 1200 pkts/s, at 10% CPU on my 700MHz client, and a bit less on the 1GHz server. So * Is that what is holding up postgres over the net too? Lots of tiny packets? And if so * can one fix it the way I fixed it for remote gdbm? The speedup was hundreds of times. Can someone point me at the relevant bits of pg code? A quick look seems to say that fe-*.c is interesting. I need to find where the actual read and write on the conn->sock is done. Very illuminating gnuplot outputs available on request. Peter
В списке pgsql-performance по дате отправления:
Следующее
От: Bastian VoigtДата:
Сообщение: Performance Problem with Vacuum of bytea table (PG 8.0.13)