[PERFORM] UPDATE 66k rows too slow
Hi! I'm testing an update on 66k rows on Postgresql, and it seems something is not right here. My server is a Quad-Xeon 3.2 Ghz with 2 GB RAM and a RAID 1 running FreeBSD 6.3 and PgSQL 8.3. My development machine is a PowerBook G4 1.67 Ghz with 2 GB RAM, OS X Leopard and PgSQL 8.3. I detected that an update in my application was runnning to slow. So, I'm testing an update query with no conditions, just: UPDATE text_answer_mapping_ebt SET f1 = false; f1 is a boolean column, so it can't get much simpler than this. I've analysed and vaccumed several times, yet the results I get on the Xeon are: EXPLAIN ANALYZE UPDATE text_answer_mapping_ebt SET f1 = false; QUERY PLAN --- Seq Scan on text_answer_mapping_ebt (cost=0.00..13945.72 rows=265072 width=92) (actual time=21.123..1049.054 rows=66268 loops=1) Total runtime: 63235.363 ms (2 rows) On my powerbook, this runs on about 25 seconds. Also, when I do the same operation on a very similar-structured table with less rows, I get *much* faster times: EXPLAIN ANALYZE UPDATE respondent_mapping_ebt SET f1 = false; QUERY PLAN - Seq Scan on respondent_mapping_ebt (cost=0.00..1779.03 rows=36003 width=68) (actual time=3.023..76.096 rows=12001 loops=1) Total runtime: 894.888 ms (2 rows) Of course that, less rows, less time, but how can 12k rows take less than one second, and 66k rows take more than one minute? I've read some stuff about PgSQL tuning, and played with the configuration files, but I keep getting the feeling that I'm doing this in a blind way. I'm trying to guess the problem and avoid it. I'm sure there's a better way, but I can't seem to find it. My question is, how can I "ask" PgSQL what's happening? How can I avoid guessing, and be sure of what is causing this slowdown? Is some buffer too small for this? Is this related to checkpoints? I would appreciate if someone could point me in the right direction. Of course I don't need to say I'm relatively new to this kind of problems. :) Yours Miguel Arroz Miguel Arroz http://www.terminalapp.net http://www.ipragma.com smime.p7s Description: S/MIME cryptographic signature
Re: [PERFORM] UPDATE 66k rows too slow
Miguel Arroz <[EMAIL PROTECTED]> wrote .. >I'm testing an update on 66k rows on Postgresql, and it seems > something is not right here. > >My server is a Quad-Xeon 3.2 Ghz with 2 GB RAM and a RAID 1 running > FreeBSD 6.3 and PgSQL 8.3. My development machine is a PowerBook G4 > 1.67 Ghz with 2 GB RAM, OS X Leopard and PgSQL 8.3. [62 seconds on server, 25 seconds on much weaker development machine] OK, my guess is that the server's tables are bloated beyond what regular VACUUM can fix. Try a VACUUM FULL VERBOSE or a re-CLUSTER if the tables are clustered. Hope this helps. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] UPDATE 66k rows too slow
Miguel Arroz <[EMAIL PROTECTED]> writes: > EXPLAIN ANALYZE UPDATE text_answer_mapping_ebt SET f1 = false; > QUERY PLAN > --- > Seq Scan on text_answer_mapping_ebt (cost=0.00..13945.72 > rows=265072 width=92) (actual time=21.123..1049.054 rows=66268 loops=1) > Total runtime: 63235.363 ms > (2 rows) Hm, only one second to do the scan ... I'm thinking the extra time must be going into index updating or CHECK-constraint checking or some such overhead. Can we see the full schema definition of the table? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] UPDATE 66k rows too slow
On Mon, 10 Mar 2008, Miguel Arroz wrote: My question is, how can I "ask" PgSQL what's happening? How can I avoid guessing, and be sure of what is causing this slowdown? There are many pieces involved here, and any one or multiple of them could be to blame. Someone may make a guess and get lucky about the cause, but the only generic way to solve this sort of thing is to have a systematic approach that goes through the likely possible causes one by one until you've discovered the source of the problem. Since as you say you're new to this, you've got the double task of learning that outline and then finding out how to run each of the tests. For your particular case, slow updates, I usually follow the following series of tests. I happen to have articles on most of these sitting around because they're common issues: -Confirm disks are working as expected: http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm -Look at differences between fsync commit behavior between the two systems. It's often the case that when servers appear slower than development systems it's because the server is doing fsync properly, while the development one is caching fsync in a way that is unsafe for database use but much faster. http://www.postgresql.org/docs/8.3/static/wal-reliability.html is a brief intro to this while http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm goes into extreme detail. The test_fsync section there is probably the most useful one for your comparision. -Setup basic buffer memory parameters: http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm -VACUUM VERBOSE ANALYZE and make sure that's working properly. This requires actually understanding the output from that command which is "fun" to figure out. A related topic is looking for index bloat which I haven't found a good tutorial on yet. -Investigate whether checkpoints are to blame. Since you're running 8.3 you can just turn on log_checkpoints and see how often they're showing up and get an idea how big the performance impact is. Increasing checkpoint_segments is the usual first thing to do if this is the case. -Collect data with vmstat, iostat, and top to figure out what's happening during the problem query -Look for application problems (not your issue here) -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
