Re: [GENERAL] max_fsm_pages increase
On Sun, Oct 31, 2010 at 4:43 AM, AI Rumman wrote: > I using Postgresql 8.1 and during vacuum at night time, I am getting the > following log: > number of page slots needed (2520048) exceeds max_fsm_pages (356656) > Do I need to increase max_fsm_pages to 2520048? Does it have any bad affect? You don't *have* to do it. The consequences of not doing it are: 1) your server will not know all of the pages in the files holding your database that there are empty slots available for use. 2) because of that lack of knowledge, it may then allocate new pages to hold your data, causing potentially more bloat, and the need for even more FSM pages. 3) Allocating new pages usually costs more than just filling in space on existing page, so your system slows down. If I were you, I'd set the FSM pages to double what your current need is, run vacuum again, and you should be good for a while. It will unfortunately, require a restart of your postgres server. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] max_fsm_pages increase
On Sun, Oct 31, 2010 at 2:43 AM, AI Rumman wrote: > I using Postgresql 8.1 and during vacuum at night time, I am getting the > following log: > number of page slots needed (2520048) exceeds max_fsm_pages (356656) > Do I need to increase max_fsm_pages to 2520048? Does it have any bad affect? No, you should set it to something higher most times. My production dbs, which are in the 100G range of size, and have a lot of updates, and very aggressive autovac setup, floats at around 2.5Million, and has max fsm set to 10M. It'll use a bit of shared mem (6 bytes per I think) so for me that's 60Meg of shared memory. Considering this machine has shared_buffers set to 8 Gig, that's not a whole lot of extra memory being used for me. Note two things: If your database is not getting vacuumed aggressively enough to keep up then you need to adjust autovacuum to keep up (more threads, less sleep, higher cost limits). Also, moving to 8.4 will get you out of this hole, as the free space map was moved from shared_memory to files on the hard drive. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] max_fsm_pages increase
Hello 2010/10/31 AI Rumman : > I using Postgresql 8.1 and during vacuum at night time, I am getting the > following log: > number of page slots needed (2520048) exceeds max_fsm_pages (356656) > Do I need to increase max_fsm_pages to 2520048? Does it have any bad affect? I takes a little more memory, but if you can run VACUUM FULL, then do it. It reduce a free space inside data files and it reduce a requests on max_fsm_pages. Regards Pavel Stehule -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] max_fsm_pages
Naz Gassiep <[EMAIL PROTECTED]> schrieb: > NOTICE: number of page slots needed (27056) exceeds max_fsm_pages (2) > HINT: Consider increasing the configuration parameter "max_fsm_pages" to a > value over 27056. > VACUUM > conwatchlive=# > > What does this mean? I assume it has nothing to do with the Flying Read again ;-) You should increase 'max_fsm_pages' ;-) > More generally, I am a novice at administering a PostgreSQL database, is > there a list of tips somewhere that I can read to improve performance? Yes, for instance, read this: http://www.powerpostgresql.com/Downloads/annotated_conf_80.html Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] max_fsm_pages
> "TL" == Tom Lane <[EMAIL PROTECTED]> writes: TL> VACUUM will fill whatever FSM space is available. You do have to TL> restart the postmaster to get the increased FSM size to be allocated, TL> but you don't have to do any magic pushups beyond that. Do you need a full restart or will a simple reload configuration suffice? Downtime is hard for me ;-) -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] max_fsm_pages
On Thu, 2004-07-01 at 11:45, [EMAIL PROTECTED] wrote: > If max_fsm_pages is too small and I have space not reclaimed by vacuum, if I > increase max_fsm_pages and restart postmaster, will the next VACUUM ANALYZE > relcaim all overlooked pages or must I do a VACUUM FULL? Let's say you have a table with 1,000 rows, but you've deleted 1,000,000 over the past year, and most of those are unclaimed. Regular vacuum will put those 900,000 odd pages into the FSM, and the database can use them. However, scans on this table will still be mostly reading empty space. So, to collapse the table back down to something reasonable, you'll need to do a vacuum full, then regular vacuums should keep things tight from then on. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] max_fsm_pages
If you increase max_fsm_pages to a large enough number then the next vacuum analyze will make all the pages with free space available to be reused. A normal VACUUM does not actually reclaim space (unless it's at the end of the table I think), it only marks the space as reuseable. VACUUM FULL will reclaim space immediately. Matthew [EMAIL PROTECTED] wrote: If max_fsm_pages is too small and I have space not reclaimed by vacuum, if I increase max_fsm_pages and restart postmaster, will the next VACUUM ANALYZE relcaim all overlooked pages or must I do a VACUUM FULL? Wes ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] max_fsm_pages
I've read this message, and tryed to apply this changes to my own database. sapiens=# vacuum verbose; ... INFO: free space map: 1 relations, 39 pages stored; 48 total pages needed DETAIL: Allocated FSM size: 1000 relations + 3 pages = 237 kB shared memory. 1 relation and 48 pages seems too little, considering the defaults in the configuration. The database is not too big, just 198 mb (contrib/dbsize tells me so), and I have 161 relations on pg_class (not counting the catalog). I'm doing something wrong or 1 relation/48 pages would be just fine? Thanks. On Fri, 2003-12-05 at 13:17, Tom Lane wrote: Ryan Mahoney <[EMAIL PROTECTED]> writes: > When interactively calculating the ideal value for max_fsm_pages by > summarizing the output of VACUUM VERBOSE, which statistic from vacuum am > I concerned with? 7.4 will tell you exactly how many FSM slots you need: foo=# vacuum verbose; ... INFO: free space map: 246 relations, 464 pages stored; 4160 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 178 kB shared memory. VACUUM In this example, the minimum FSM settings to not discard any data would be max_fsm_relations = 246, max_fsm_pages = 4160. Note that you need to have vacuumed all databases fairly recently for the totals to be really trustworthy. In previous versions you're kind of on your own :-( regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly signature.asc Description: This is a digitally signed message part
