On Tue, Feb 7, 2012 at 1:22 PM, Greg Smith <gsm...@gregsmith.com> wrote: > On 02/03/2012 11:41 PM, Jeff Janes wrote: >>> >>> -The steady stream of backend writes that happen between checkpoints have >>> filled up most of the OS write cache. A look at /proc/meminfo shows >>> around >>> 2.5GB "Dirty:" >> >> "backend writes" includes bgwriter writes, right? > > > Right. > > >> Has using a newer kernal with dirty_background_bytes been tried, so it >> could be set to a lower level? If so, how did it do? Or does it just >> refuse to obey below the 5% level, as well? > > > Trying to dip below 5% using dirty_background_bytes slows VACUUM down faster > than it improves checkpoint latency.
Does it cause VACUUM to create latency for other processes (like the checkpoint syncs do, by gumming up the IO for everyone) or does VACUUM just slow down without effecting other tasks? It seems to me that just lowering dirty_background_bytes (while not also lowering dirty_bytes) should not cause the latter to happen, but it seems like these kernel tunables never do exactly what they advertise. This may not be relevant to the current situation, but I wonder if we don't need a "vacuum_cost_page_dirty_seq" so that if the pages we are dirtying are consecutive (or at least closely spaced) they cost less, in anticipation that the eventual writes will be combined and thus consume less IO resources. I would think it would be common for some regions of table to be intensely dirtied, and some to be lightly dirtied (but still aggregating up to a considerable amount of random IO). But the vacuum process might also need to be made more "bursty", as even if it generates sequential dirty pages the IO system might write them randomly anyway if there are too many delays interspersed > Since the sort of servers that have > checkpoint issues are quite often ones that have VACUUM ones, too, that > whole path doesn't seem very productive. The one test I haven't tried yet > is whether increasing the size of the VACUUM ring buffer might improve how > well the server responds to a lower write cache. I wouldn't expect this to help. It seems like it would hurt, as it just leaves the data for even longer (however long it takes to circumnavigate the ring buffer) before there is any possibility of it getting written. I guess it does increase the chances that the dirty pages will "accidentally" get written by the bgwriter rather than the vacuum itself, but I doubt that that would be significant. ... >> >> Was the sorted checkpoint with an fsync after every file (real file, >> not VFD) one of the changes you tried? > > ... > > I haven't had very good luck with sorting checkpoints at the PostgreSQL > relation level on server-size systems. There is a lot of sorting already > happening at both the OS (~3GB) and BBWC (>=512MB) levels on this server. > My own tests on my smaller test server--with a scaled down OS (~750MB) and > BBWC (256MB) cache--haven't ever validated sorting as a useful technique on > top of that. It's never bubbled up to being considered a likely win on the > production one as a result. Without sorted checkpoints (or some other fancier method) you have to write out the entire pool before you can do any fsyncs. Or you have to do multiple fsyncs of the same file, with at least one occurring after the entire pool was written. With a sorted checkpoint, you can start issuing once-only fsyncs very early in the checkpoint process. I think that on large servers, that would be the main benefit, not the actually more efficient IO. (On small servers I've seen sorted checkpoints be much faster on shutdown checkpoints, but not on natural checkpoints, and presumably this improvement *is* due to better ordering). On your servers, you need big delays between fsyncs and not between writes (as they are buffered until the fsync). But in other situations, people need the delays between the writes. By using sorted checkpoints with fsyncs between each file, the delays between writes are naturally delays between fsyncs as well. So I think the benefit of using sorted checkpoints is that code to improve your situations is less likely to degrade someone else's situation, without having to introduce an extra layer of tunables. > >> What I/O are they trying to do? It seems like all your data is in RAM >> (if not, I'm surprised you can get queries to ran fast enough to >> create this much dirty data). So they probably aren't blocking on >> reads which are being interfered with by all the attempted writes. > > > Reads on infrequently read data. Long tail again; even though caching is > close to 100%, the occasional outlier client who wants some rarely accessed > page with their personal data on it shows up. Pollute the write caches > badly enough, and what happens to reads mixed into there gets very fuzzy. > Depends on the exact mechanics of the I/O scheduler used in the kernel > version deployed. OK, but I would still think it is a minority of transactions which need at least one of those infrequently read data and most do not. So a few clients would freeze, but the rest should keep going until they either try to execute a read themselves, or they run into a heavyweight lock held by someone else who is read-blocking. So if 1/1000 of all transactions need to make a disk read, but clients are running at 100s of TPS, then I guess after a few tens of seconds all clients will be blocked on reads and you will see total freeze up. But it seems more likely to me that they are in fact freezing on writes. Is there a way to directly observe what they are blocking on? I wish "top" would separate %wait into read and write. > > >> The current shared_buffer allocation method (or my misunderstanding of >> it) reminds me of the joke about the guy who walks into his kitchen >> with a cow-pie in his hand and tells his wife "Look what I almost >> stepped in". If you find a buffer that is usagecount=0 and unpinned, >> but dirty, then why is it dirty? It is likely to be dirty because the >> background writer can't keep up. And if the background writer can't >> keep up, it is probably having trouble with writes blocking. So, for >> Pete's sake, don't try to write it out yourself! If you can't find a >> clean, reusable buffer in a reasonable number of attempts, I guess at >> some point you need to punt and write one out. But currently it grabs >> the first unpinned usagecount=0 buffer it sees and writes it out if >> dirty, without even checking if the next one might be clean. > > > Don't forget that in the version deployed here, the background writer isn't > running during the sync phase. Oh, I had thought you had compiled your own custom work around to that. So much of the problem might go away upon a new release and an upgrade, as far as we know? > I think the direction you're talking about > here circles back to "why doesn't the BGW just put things it finds clean > onto the free list?", I wouldn't put it that way, because to me the freelist is the code located in freelist.c. The linked list is a freelist. But the clock sweep is also a freelist, just implemented in a different way. If the hypothetical BGW doesn't remove the entry from the buffer mapping table and invalidate it when it adds to the linked list, then we might pull a "free" buffer from the linked list and discover it is not actually free. If we want to make it so that it does remove the entry from the buffer mapping table (which doesn't seem like a good idea to me) we could implement that just as well with the clock-sweep as we could with the linked list. I think the linked list is a bit of a red herring. Many of the concepts people discuss implementing on the linked list could just as easily be implemented with the clock sweep. And I've seen no evidence at all that the clock sweep is the problem. The LWLock that protects can obviously be a problem, but that seems to be due to the overhead of acquiring a contended lock, not the work done under the lock. Reducing the lock-strength around this might be a good idea, but that reduction could be done just as easily (and as far as I can tell, more easily) with the clock sweep than the linked list. > a direction which would make "nothing on the free > list" a noteworthy event suggesting the BGW needs to run more often. Isn't seeing a dirty unpinned usage_count==0 buffer in the clocksweep just as noteworthy as seeing an empty linked list? From what I can tell, you can't dirty a buffer without pinning it, you can't pin a buffer without making usage_count>0, and we never decrement usage_count on a pinned buffer. So, the only way to see a dirty buffer that is unpinned and has zero usage_count is if another normal backend saw it unpinned and decremented the count, which would have to be a full clock sweep ago, and the bgwriter hasn't visited it since then. If our goal is to autotune the bgwriter_* parameters, then detecting either an empty linked list or dirty but usable buffer in the clock sweep would be a good way to do that. But, I think the bigger issue is to assume that the bgwriter is already tuned as well as it can be, and that beating on it further will not improve its moral. If the IO write caches are all full, there is nothing bgwriter can do about it by running more often. In that case, we can't really do anything about the dirty pages it is leaving around our yard. But what we can do is not pick up those little piles of toxic waste and bring them into our living rooms. That is, don't try to write out the dirty page in the foreground, instead go looking for a clean one. We can evict it without doing a write, and hopefully we can read in the replacement either from OS cache, or from disk if reads are not as gummed up as writes are. >> But I would think that pgbench can be configured to do that as well, >> and would probably offer a wider array of other testers. Of course,if >> they have to copy and specify 30 different -f files, maybe getting >> dbt-2 to install and run would be easier than that. My attempts at >> getting dbt-5 to work for me do not make me eager jump from pgbench to >> try more other things. > > > dbt-5 is a work in progress, known to be tricky to get going. dbt-2 is > mature enough that it was used for this sort of role in 8.3 development. > And it's even used by other database systems for similar testing. It's the > closest thing to an open-source standard for write-heavy workloads as we'll > find here. OK, thanks for the reassurance. I'll no longer be afraid to give it a try if I get an opportunity. Cheers, Jeff -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers