On 13/10/10 19:47, Neil Whelchel wrote:
Nope... So, possible conclusions are: 1. Even with VACUUM database table speed degrades as tables are updated. 2. Time testing on a freshly INSERTed table gives results that are not real- world. 3. Filesystem defragmentation helps (some). 4. Cache only makes a small difference once a table has been UPDATEd. I am going to leave this configuration running for the next day or so. This way I can try any suggestions and play with any more ideas that I have. I will try these same tests on ext4 later, along with any good suggested tests. I will try MySQL with the dame data with both XFS and ext4. -Neil-
I think that major effect you are seeing here is that the UPDATE has made the table twice as big on disk (even after VACUUM etc), and it has gone from fitting in ram to not fitting in ram - so cannot be effectively cached anymore.
This would not normally happen in real life (assuming UPDATEs only modify a small part of a table per transaction). However administration updates (e.g 'oh! - ref 1 should now be ref 2 please update everything') *will* cause the table size to double.
This is an artifact of Postgres's non overwriting storage manager - Mysql will update in place and you will not see this.
Try VACUUM FULL on the table and retest. regards Mark -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
