Ühel kenal päeval, N, 2006-06-22 kell 10:20, kirjutas Jonah H. Harris: > On 6/22/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > > Hmm, OK, then the problem is more serious than I suspected. > > > This means that every index on a row has to be updated on every > > > transaction that modifies that row. Is that correct? > > > > Add an index entry, yes. > > Again, this is a case for update-in-place. No need to write an extra > index entry and incur the WAL associated with it.
I guess that MySQL on its original storage does that, but they allow only one concurrent update per table and no transactions. > Imagine a table > with 3 indexes on it... I would estimate that we perform at least 3 to > 6 times more overhead than any commercial database on such an update. One way to describe what "commercial databases" do to keep constant update rates is saying that they do either vacuuming as part of update, or they just use locks anf force some transactions to wait or fail/retry. Depending on exact details and optimisations done, this can be either slower or faster than postgresql's way, but they still need to do something to get transactional visibility rules implemented. > > > There has to be a more linear way of handling this scenario. > > > > So vacuum the table often. > > It's easy to say VACUUM often... but I'd bet that vacuuming is going > to lessen the throughput in his tests even more; no matter how it's > tuned. Running VACUUM often/continuously will likely keep his update rate fluctuatons within a corridor of maybe 5-10%, at the cost of 1-2% extra load. At least if vacuum is configured right and the server is not already running at 100% IO saturation, in which case it will be worse. The max throughput figure is not something you actually need very often in production. What is interesting is setting up the server so that you can service your loads comfortably. Running the server at 100% lead is not anything you want to do on production server. There will be things you need to do anyway and you need some headroom for that. -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org