The documentation is in need of it and it is hard to tackle it alongside all of the other things that I am doing since returning to the project. Even if it was, it might have only benefited the WAL for example.įinally, I am really happy to see someone volunteer to give this some attention. That had been consciously done since it was not a hard rule that disabling ARC's data caching on PostgreSQL was beneficial. That said, I did omit ARC tuning from the section on PostgreSQL specific advice. Of course, the section was so poorly written that even if I had confirmed it, it would still have been problematic in hindsight. It seemed obvious that PostgreSQL was in a similar situation, so a mention seemed merited, but unlike VMs, I had not tested the idea on PostgreSQL, which was a mistake. I had previously used PostgreSQL in a project and had some minor familiarity with it.PostgreSQL supposedly had its own caching algorithm that was similar to ARC, and superior to what the OS typically did from a mix of what I already knew about the page cache and what I had read in write-ups on it that the PostgreSQL developers had published.Far more experienced people had previously informed me that databases used O_DIRECT because they did their own caches and explicitly wanted to avoid having the filesystem do caching because it was pointless.The mention of PostgreSQL there had been more intended to be a possibility than a hard rule, but that was not clearly explained. That should have been explained, but it was not. ARC is relatively self tuning, but if you were to use the omission from ARC to increase a VM's memory so that it could include its entire working set in its own LRU cache, that would make any caching by ARC pointless for anything other than VM reboots. That said, double caching in general, is less than ideal, since the data being duplicated would be better omitted from the further away cache so that things that actually benefit would be included in it. When I wrote it down, I sought to record everything I knew, but my knowledge in the area of ARC was somewhat immature, which lead to it being written poorly. In hindsight, the section on ARC was not well written, which is my fault.Īt the time, there was no central write-up on performance tips and I had been helping people do tuning in IRC often enough that I had felt it was beneficial to write it down so that I would not be repeating myself so much. PG expects the majority of caching to be done by the OS file cache and is designed around that assumption. The advice to not cache data in ARC is simply wrong-PG does not work that way, shared buffers are more special purpose. Start with the documentation, which makes a brief reference to it: (see comments about eviction algo) Nearly any source regarding PostgreSQL tuning. But LZ4 + record size larger than PG block size offers even better performance. It is of course still better than 128K record size without compression. The advice to use 8K record size was correct prior to LZ4 and provided a significant performance improvement. There are a number of blog posts on ZFS tuning for PostgreSQL, and every one that has been written since LZ4 was available comes to the same conclusion.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |