Обсуждение: WAL Internals question
Hello, I am currently working on a heavily stressed system and i am having some difficulties to make the background writer work properly. But before going any further, i would like to be sure that i understood how transaction processing, and management are handled by postgreSQL. Correct me if i'm wrong : 1. A Transaction is sent from the postgreSQL backend 2. If the result of the transaction is within the shared buffers, we get our result instantly. Else some searching is done within the database datafiles to get the result which is copied to the shared buffers memory zone. The transaction is stocked in a WAL buffer. Now, when the result is copied to the shared buffer, if the transaction was an update or a delete the line is flagged to be updated/deleted in the datafiles. transactions go on and on this way. At some points, the WAL buffers are written in the checkpoint segments. I don't know when, if you could just precise this point. 3. Then periodically, there are checkpoints, those will make the changes into the datafiles from the shared buffers (meaning shared buffers are flushed into the datafiles). The last written record in the datafiles is flagged into the checkpoint segments that way REDOs are possible. Now i tried to set the bgwriter_lru_percent to 100% and bgwriter_lru_maxpages to 1000 and i did not spot any difference with the disk activities, cpu occupation or anything else from the default set up which is 1% and 5 so i was wondering if commiting after every transaction would prevent me from seeing any difference ? or is there another explanation ? Thank you for your help Regards, Fabrice Franquenk.
Fabrice Franquenk <Fabrice.Franquenk@bull.net> writes: > Now i tried to set the bgwriter_lru_percent to 100% and > bgwriter_lru_maxpages to 1000 and > i did not spot any difference with the disk activities, cpu occupation > or anything else from Hmm, are you sure your changes took effect? To get the bgwriter's attention you need to make the change in postgresql.conf and then SIGHUP or restart the postmaster. I'd have thought you could easily measure a difference given that much change ... regards, tom lane
Tom Lane a écrit : > Fabrice Franquenk <Fabrice.Franquenk@bull.net> writes: > >> Now i tried to set the bgwriter_lru_percent to 100% and >> bgwriter_lru_maxpages to 1000 and >> i did not spot any difference with the disk activities, cpu occupation >> or anything else from >> > > Hmm, are you sure your changes took effect? To get the bgwriter's > attention you need to make the change in postgresql.conf and then > SIGHUP or restart the postmaster. I'd have thought you could easily > measure a difference given that much change ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > I made sure that all changes in postgresql.conf took effect. I restarted the postmaster and i could not spot any difference. The values of bgwriter_all_percent and bgwriter_all_maxpages are all default (and i set checkpoints segments value to 250). Could there be some kind of connection between these parameters and the bgwriter_lru_* parameters? by the way, was i correct describing the transaction handling mechanism ? thanks for your help regards, Fabrice Franquenk