Wed May 16 16:47:13 PDT 2007
- Previous message: [Slony1-general] Slony Replication Slow
- Next message: [Slony1-general] Slony Replication Slow
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Jeff: Thanks for responding! I have edited out some bits to keep this as brief as possible. > Shared buffers on the origin or the subscriber? Shared buffers might not > matter in this case anyway. I upped the shared_buffers on both the origin and subscriber from 20,000 to 40,000 then did a reload (not a restart) in Postgres on both the origin and the subscriber. > A Slony subscriber reads from the log on the origin and then writes the > records on the subscriber in separate statements. For any bulk operation > on the origin (like a big UPDATE statement) that translates into a lot > of little statements on the subscriber. Usually, each of those little > statements uses an index, because that's fastest for an action on a > single tuple. My mass update was comprised of all individual, one record, updates per transaction. > If you don't have enough memory to hold the index, that translates into > bad performance. > > Tell us more about the situation. Is the index fitting in shared memory? > How big is the table? How much physical memory? Thanks for the pointed questions. I didn't think any of this was relevant until I checked. Two of my 12 indexes are bigger (as measured by 'relpages') than the table itself. These are 8k pages right? SELECT relname, reltuples, relpages FROM pg_class ORDER BY relpages DESC ; relname | reltuples | relpages ---------------------------------+-------------+---------- indx_tsearch2a | 72559 | 66324 indx_tsearch2c | 40761 | 44640 used_ad | 335542 | 38646 (used_ad is the table, the other two are partial indexes.) So, my shared_buffers is 40,000 and two of my indexes are bigger than that. The total size of all 12 indexes is some 324,000 'relpages'. *** Is shared_buffers comparable to 'relpages'? If so, is it practical to bump shared_buffers to 325,000? That's just over 2.5GB by my calculation - I have 3GB on the machine. > Try to hunt down which events are taking a long time to SYNC. Please, what would allow me to measure that? > What other types of bulk operations are you doing? Any big UPDATEs or DELETEs? Nothing else big during the day, just a steady constant stream. Thanks again, and in advance, for your sage advice. - Don Barthel
- Previous message: [Slony1-general] Slony Replication Slow
- Next message: [Slony1-general] Slony Replication Slow
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
More information about the Slony1-general mailing list