Re: [PATCHES] [HACKERS] pg_freespacemap question
Patch applied. Thanks. --- Mark Kirkwood wrote: Mark Kirkwood wrote: Tom Lane wrote: I do notice a rather serious shortcoming of pg_freespacemap in its current incarnation, which is that it *only* shows you the per-page free space data, and not any of the information that would let you determine what the FSM is doing to filter the raw data. The per-relation avgRequest and lastPageCount fields would be interesting for instance. Perhaps there should be a second view with one row per relation to carry the appropriate data. Ok - I did wonder about 2 views, but was unsure if the per-relation stuff was interesting. Given that it looks like it is interesting, I'll see about getting a second view going. This patch implements the second view for FSM relations. I have renamed the functions and views to be: pg_freespacemap_relations pg_freespacemap_pages This patch depends on the previous one (which was called simply 'pg_freespacemap.patch'). Cheers Mark [ application/gzip is not supported, skipping... ] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] pg_freespacemap question
Mark Kirkwood wrote: Tom Lane wrote: I do notice a rather serious shortcoming of pg_freespacemap in its current incarnation, which is that it *only* shows you the per-page free space data, and not any of the information that would let you determine what the FSM is doing to filter the raw data. The per-relation avgRequest and lastPageCount fields would be interesting for instance. Perhaps there should be a second view with one row per relation to carry the appropriate data. Ok - I did wonder about 2 views, but was unsure if the per-relation stuff was interesting. Given that it looks like it is interesting, I'll see about getting a second view going. This patch implements the second view for FSM relations. I have renamed the functions and views to be: pg_freespacemap_relations pg_freespacemap_pages This patch depends on the previous one (which was called simply 'pg_freespacemap.patch'). Cheers Mark pg_freespacemap-1.patch.gz Description: application/gzip ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] pg_freespacemap question
Mark Kirkwood wrote: Christopher Kings-Lynne wrote: The point here is that if tuples require 50 bytes, and there are 20 bytes free on a page, pgstattuple counts 20 free bytes while FSM ignores the page. Recording that space in the FSM will not improve matters, it'll just risk pushing out FSM records for pages that do have useful amounts of free space. Maybe an overloaded pgstattuple function that allows you to request FSM behavior? That's a nice idea - could also do equivalently by adding an extra column usable_free_space or some such, and calculating this using FSM logic. The current pgstattuple function scans the whole table, so I don't think this is a good idea. Re: the overloaded function, I think the behaviors are different enough to merit a separate function, with a different name. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] pg_freespacemap question
Tatsuo Ishii wrote: BTW, I noticed difference of outputs from pg_freespacemap and pgstattuple. I ran pgbench and inspected accounts table by using these tools. pg_freespacemap: sum of bytes: 250712 pgstattuple: free_space: 354880 Shouldn't they be identical? I would have thought so - unless there are not enough pages left in the FSM... pg_freespacemap is reporting on what gets into the FSM - so provided I haven't put a bug in there somewhere (!) - we need to look at how VACUUM reports free space to the FSM cheers Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] pg_freespacemap question
Tatsuo Ishii wrote: BTW, I noticed difference of outputs from pg_freespacemap and pgstattuple. I ran pgbench and inspected accounts table by using these tools. pg_freespacemap: sum of bytes: 250712 pgstattuple: free_space: 354880 Shouldn't they be identical? No, because (a) pgbench vacuums at the start of the run not the end, and (b) vacuum/fsm disregard pages with uselessly small amounts of free space (less than the average tuple size, IIRC). I do notice a rather serious shortcoming of pg_freespacemap in its current incarnation, which is that it *only* shows you the per-page free space data, and not any of the information that would let you determine what the FSM is doing to filter the raw data. The per-relation avgRequest and lastPageCount fields would be interesting for instance. Perhaps there should be a second view with one row per relation to carry the appropriate data. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] pg_freespacemap question
Tom Lane wrote: Tatsuo Ishii wrote: BTW, I noticed difference of outputs from pg_freespacemap and pgstattuple. I ran pgbench and inspected accounts table by using these tools. pg_freespacemap: sum of bytes: 250712 pgstattuple: free_space: 354880 Shouldn't they be identical? vacuum/fsm disregard pages with uselessly small amounts of free space (less than the average tuple size, IIRC). Ah - that what I was seeing! Thanks. I do notice a rather serious shortcoming of pg_freespacemap in its current incarnation, which is that it *only* shows you the per-page free space data, and not any of the information that would let you determine what the FSM is doing to filter the raw data. The per-relation avgRequest and lastPageCount fields would be interesting for instance. Perhaps there should be a second view with one row per relation to carry the appropriate data. Ok - I did wonder about 2 views, but was unsure if the per-relation stuff was interesting. Given that it looks like it is interesting, I'll see about getting a second view going. Cheers Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] pg_freespacemap question
Tatsuo Ishii wrote: BTW, I noticed difference of outputs from pg_freespacemap and pgstattuple. I ran pgbench and inspected accounts table by using these tools. pg_freespacemap: sum of bytes: 250712 pgstattuple: free_space: 354880 Shouldn't they be identical? No, because (a) pgbench vacuums at the start of the run not the end, I ran VACUUM after pbench run and still got the differece. and (b) vacuum/fsm disregard pages with uselessly small amounts of free space (less than the average tuple size, IIRC). That sounds strange to me. Each record of accounts tables is actually exactly same, i.e fixed size. So it should be possible that UPDATE reuses any free spaces made by previous UPDATE. If FSM neglects those free spaces because they are uselessly small, then the unrecycled pages are getting grow even if they are regulary VACUUMed, no? I do notice a rather serious shortcoming of pg_freespacemap in its current incarnation, which is that it *only* shows you the per-page free space data, and not any of the information that would let you determine what the FSM is doing to filter the raw data. The per-relation avgRequest and lastPageCount fields would be interesting for instance. Perhaps there should be a second view with one row per relation to carry the appropriate data. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] pg_freespacemap question
Tatsuo Ishii [EMAIL PROTECTED] writes: That sounds strange to me. Each record of accounts tables is actually exactly same, i.e fixed size. So it should be possible that UPDATE reuses any free spaces made by previous UPDATE. If FSM neglects those free spaces because they are uselessly small, then the unrecycled pages are getting grow even if they are regulary VACUUMed, no? The point here is that if tuples require 50 bytes, and there are 20 bytes free on a page, pgstattuple counts 20 free bytes while FSM ignores the page. Recording that space in the FSM will not improve matters, it'll just risk pushing out FSM records for pages that do have useful amounts of free space. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] pg_freespacemap question
The point here is that if tuples require 50 bytes, and there are 20 bytes free on a page, pgstattuple counts 20 free bytes while FSM ignores the page. Recording that space in the FSM will not improve matters, it'll just risk pushing out FSM records for pages that do have useful amounts of free space. Maybe an overloaded pgstattuple function that allows you to request FSM behavior? Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] pg_freespacemap question
Christopher Kings-Lynne wrote: The point here is that if tuples require 50 bytes, and there are 20 bytes free on a page, pgstattuple counts 20 free bytes while FSM ignores the page. Recording that space in the FSM will not improve matters, it'll just risk pushing out FSM records for pages that do have useful amounts of free space. Maybe an overloaded pgstattuple function that allows you to request FSM behavior? That's a nice idea - could also do equivalently by adding an extra column usable_free_space or some such, and calculating this using FSM logic. Cheers Mark ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] pg_freespacemap question
BTW, I noticed difference of outputs from pg_freespacemap and pgstattuple. I ran pgbench and inspected accounts table by using these tools. pg_freespacemap: sum of bytes: 250712 pgstattuple: free_space: 354880 Shouldn't they be identical? -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
