Re: [HACKERS] Pre-allocated free space for row updating (like PCTFREE)
Simon Riggs wrote: >>4. Allow to repair fragmentation in each page. >> >>Because updates cause fragmentation in the page. >> >>So we need to keep large continuous free space in each page, >>if we want to get more effective on PCTFREE feature. > > > ...doesn't VACUUM already do that? VACUUM generates a huge load because it repaires all pages on the table file. I think (more light-weight) repairing on a single page is needed to maintain free space in the specific page. -- NAGAYASU Satoshi <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Pre-allocated free space for row updating (like PCTFREE)
Simon Riggs wrote: > The summary was: > > 1. Have a PCTFREE column added on a table by table basis I think a good place to keep PCTFREE value is a new column in the pg_class, and ALTER TABLE should be able to change this value. > 2. Apply PCTFREE for Inserts only > 3. Allow Updates to use the full space in the block. 4. Allow to repair fragmentation in each page. Because updates cause fragmentation in the page. So we need to keep large continuous free space in each page, if we want to get more effective on PCTFREE feature. -- NAGAYASU Satoshi <[EMAIL PROTECTED]> ---(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: [HACKERS] Pre-allocated free space for row updating (like PCTFREE)
Satoshi, > I've created a new patch which can be applied to the current cvs tree. > > http://dpsql.sourceforge.net/pctfree.cvs.diff Hmmm ... I don't see where I set the GUC. How am I supposed to vary the PCTFREE amount? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Pre-allocated free space for row updating (like PCTFREE)
Satoshi Nagayasu wrote: > Josh Berkus wrote: > >>Satoshi, if you can package up a patch on current CVS, I'll throw it at DBT2. > > Ok. I'll do it. I've created a new patch which can be applied to the current cvs tree. http://dpsql.sourceforge.net/pctfree.cvs.diff -- NAGAYASU Satoshi <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Pre-allocated free space for row updating (like PCTFREE)
Josh Berkus wrote: > Satoshi, if you can package up a patch on current CVS, I'll throw it at DBT2. Ok. I'll do it. -- NAGAYASU Satoshi <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: 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
Re: [HACKERS] Pre-allocated free space for row updating (like PCTFREE)
On Mon, Aug 22, 2005 at 10:18:25PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > ... But I agree with Satoshi; if there are > > people who will benefit from this option (which doesn't hurt those who > > choose not to use it), why not put it in? > > Because there's no such thing as a free lunch. Every option we support > costs us in initial implementation time, documentation effort, and > ongoing maintenance. Plus it confuses users who don't know what to do > with it. (Note Josh's nearby lobbying to remove some GUC parameters. > While I opposed him on that particular item, I sympathize with his > point in general.) > > Oracle's approach of "offer every knob you can think of" is not one > that I care to emulate. We have to strike a balance between flexibility > and not having a database that's too complex to administer for anyone > except an expert. The problem is that unless you're going to put a lot of AI in the database[1] (something Oracle is now doing...), you're going to end up limiting yourself. As the PostgreSQL code continues to improve performance-wise, we're going to run into more and more situations where the way to get more performance means adding more tunables. Look at the knobs that have been added for bgwriter and delayed vacuum for example. These were added because the code had gotten to a point where the problems they solve had become bigger and bigger bottlenecks. I know there's hope that eventually these can be turned into simple 1-10 knobs or something, but I'm doubtful that something that simple will suffice for all situations. I do understand the issue of having 100s of knobs, though. I don't think we should go adding knobs willy-nilly (Josh made the good point that there's currently no testing to validate the usefullness of this free space knob, for example). But I also think that the way to control 'knob-bloat' isn't to do everything possible not to add knobs, but to look at how to limit their exposure to people who don't need to know about them. For example, there's less than a half dozen knobs that people always ask about when people post performance questions: shared_buffers, work_mem, effective_cache_size, etc. These are knobs that almost every user needs to know about. Call them 'level 1' knobs. Level 2 might be things like vacuum_cost_delay, maintenance_work_mem, max_fsm_pages, and max_connections. And so on. By grouping in this fashion we can limit exposure to things that most users won't need to mess with, but give users who have need to change these things the ability to do so. [1]: I'm all in favor of making things self-tuning wherever possible, but that's generally a lot more work than just exposing a GUC, so I suspect it will be some time before we get to that point. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Pre-allocated free space for row updating (like PCTFREE)
Jim, Satoshi, > It should be possible to see what the crossover point is in terms of > benefit using dbt2 and tweaking the transactions that are run, something > I can do if there's interest. But I agree with Satoshi; if there are > people who will benefit from this option (which doesn't hurt those who > choose not to use it), why not put it in? Because your predicate is still disputed? That is, we don't know that people will benefit yet -- pgbench is a pretty useless benchmark for real performance comparisons. Satoshi, if you can package up a patch on current CVS, I'll throw it at DBT2. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Pre-allocated free space for row updating (like PCTFREE)
Jim C. Nasby wrote: It should be possible to see what the crossover point is in terms of benefit using dbt2 and tweaking the transactions that are run, something I can do if there's interest. But I agree with Satoshi; if there are people who will benefit from this option (which doesn't hurt those who choose not to use it), why not put it in? ISTM that this patch could be beneficial for the 'web session table' type workload (i.e. huge number of updates on relatively few rows), that is (well - last time I tried anyway) a bit of a challenge to reign in. There was a thread about this a while ago (late 2004), so in some sense it is a 'real world' scenario: http://archives.postgresql.org/pgsql-hackers/2004-06/msg00282.php regards Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Pre-allocated free space for row updating (like PCTFREE)
Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > >>... But I agree with Satoshi; if there are >>people who will benefit from this option (which doesn't hurt those who >>choose not to use it), why not put it in? > > > Because there's no such thing as a free lunch. Every option we support > costs us in initial implementation time, documentation effort, and > ongoing maintenance. Plus it confuses users who don't know what to do > with it. (Note Josh's nearby lobbying to remove some GUC parameters. > While I opposed him on that particular item, I sympathize with his > point in general.) > > Oracle's approach of "offer every knob you can think of" is not one > that I care to emulate. We have to strike a balance between flexibility > and not having a database that's too complex to administer for anyone > except an expert. I understand what you mean, but I think we have to provide more flexibility or options for PostgreSQL to be used wider area in the real-world. In my case, if many updates reduce the system performance and there is no option, our customer will change their DBMS from PostgreSQL to MySQL or Oracle. If the DBAs can choose fewer options, the system performance management(monitoring) cost gets higher, because sometimes simple architecture causes complex operations (or tricks) in the real applications (like performance v.s. vacuum). It is also a part of user's TCO. I know there is no free lunch. However, it also means if we can pay more costs, we can get more great lunch. Just my thought... -- NAGAYASU Satoshi <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Pre-allocated free space for row updating (like PCTFREE)
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > ... But I agree with Satoshi; if there are > people who will benefit from this option (which doesn't hurt those who > choose not to use it), why not put it in? Because there's no such thing as a free lunch. Every option we support costs us in initial implementation time, documentation effort, and ongoing maintenance. Plus it confuses users who don't know what to do with it. (Note Josh's nearby lobbying to remove some GUC parameters. While I opposed him on that particular item, I sympathize with his point in general.) Oracle's approach of "offer every knob you can think of" is not one that I care to emulate. We have to strike a balance between flexibility and not having a database that's too complex to administer for anyone except an expert. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Pre-allocated free space for row updating (like PCTFREE)
On Sun, Aug 21, 2005 at 09:50:10PM -0400, Tom Lane wrote: > Satoshi Nagayasu <[EMAIL PROTECTED]> writes: > > I've done a quick hack to implement PCTFREE on PostgreSQL. > > ... > > According to my experiments, pgbench score was improved 10% or more > > with 1024 bytes free space. > > I'm not very enthused about this. Enforcing 12.5% PCTFREE means that > you pay 12.5% extra I/O costs across the board for INSERT and SELECT > and then hope you can make it back (plus some more) on UPDATEs. > pgbench is a completely UPDATE-dominated benchmark and thus it makes > such a patch look much better than it would on other workloads. > > I think the reason Oracle offers this has to do with their > overwrite-based storage management; it's not obvious that the tradeoff > is as useful for us. There are some relevant threads in our archives > here, here, and here: > http://archives.postgresql.org/pgsql-patches/2005-04/msg00078.php > http://archives.postgresql.org/pgsql-performance/2004-08/msg00402.php > http://archives.postgresql.org/pgsql-performance/2003-10/msg00618.php It should be possible to see what the crossover point is in terms of benefit using dbt2 and tweaking the transactions that are run, something I can do if there's interest. But I agree with Satoshi; if there are people who will benefit from this option (which doesn't hurt those who choose not to use it), why not put it in? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(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: [HACKERS] Pre-allocated free space for row updating (like PCTFREE)
Tom Lane wrote: > I'm not very enthused about this. Enforcing 12.5% PCTFREE means that > you pay 12.5% extra I/O costs across the board for INSERT and SELECT > and then hope you can make it back (plus some more) on UPDATEs. > pgbench is a completely UPDATE-dominated benchmark and thus it makes > such a patch look much better than it would on other workloads. Yes. I'm thinking about update-intensive workload or batch jobs which generate huge amounts of updates. I know pgbench is just a update-intensive benchmark, however I don't like updates cause many smgrextend() and performance down, because there are many workload types in the real-world. I believe some of us need more options for these types of workloads. (And I also know we need more tricks on page repair.) > I think the reason Oracle offers this has to do with their > overwrite-based storage management; it's not obvious that the tradeoff > is as useful for us. There are some relevant threads in our archives > here, here, and here: I think the reason why this topic is raised many times is some people need this. The important point is that we need several options for own workloads (or access patterns). -- NAGAYASU Satoshi <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: 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
Re: [HACKERS] Pre-allocated free space for row updating (like PCTFREE)
Satoshi Nagayasu <[EMAIL PROTECTED]> writes: > I've done a quick hack to implement PCTFREE on PostgreSQL. > ... > According to my experiments, pgbench score was improved 10% or more > with 1024 bytes free space. I'm not very enthused about this. Enforcing 12.5% PCTFREE means that you pay 12.5% extra I/O costs across the board for INSERT and SELECT and then hope you can make it back (plus some more) on UPDATEs. pgbench is a completely UPDATE-dominated benchmark and thus it makes such a patch look much better than it would on other workloads. I think the reason Oracle offers this has to do with their overwrite-based storage management; it's not obvious that the tradeoff is as useful for us. There are some relevant threads in our archives here, here, and here: http://archives.postgresql.org/pgsql-patches/2005-04/msg00078.php http://archives.postgresql.org/pgsql-performance/2004-08/msg00402.php http://archives.postgresql.org/pgsql-performance/2003-10/msg00618.php regards, tom lane ---(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
[HACKERS] Pre-allocated free space for row updating (like PCTFREE)
Hi all,
I've done a quick hack to implement PCTFREE on PostgreSQL.
As you know, it's inspired by Oracle's PCTFREE.
http://www.csee.umbc.edu/help/oracle8/server.815/a67772/schema.htm#990
http://www.comp.hkbu.edu.hk/docs/o/oracle10g/server.101/b10743/cncpt031.gif
Pre-allocated space for each block(page) can improve heap_update() performance,
because heap_update() looks for the free space in same block
to insert new row.
According to my experiments, pgbench score was improved 10% or more
with 1024 bytes free space.
Any comments? Is this idea good, or not?
Thanks.
--
NAGAYASU Satoshi <[EMAIL PROTECTED]>
diff -rc postgresql-8.0.0.orig/src/backend/access/heap/heapam.c
postgresql-8.0.0.pctfree/src/backend/access/heap/heapam.c
*** postgresql-8.0.0.orig/src/backend/access/heap/heapam.c 2005-01-01
06:59:16.0 +0900
--- postgresql-8.0.0.pctfree/src/backend/access/heap/heapam.c 2005-08-20
23:20:45.017901208 +0900
***
*** 1151,1157
heap_tuple_toast_attrs(relation, tup, NULL);
/* Find buffer to insert this tuple into */
! buffer = RelationGetBufferForTuple(relation, tup->t_len, InvalidBuffer);
/* NO EREPORT(ERROR) from here till changes are logged */
START_CRIT_SECTION();
--- 1151,1160
heap_tuple_toast_attrs(relation, tup, NULL);
/* Find buffer to insert this tuple into */
! buffer = RelationGetBufferForTuple(relation,
!
tup->t_len,
!
InvalidBuffer,
!
true);
/* NO EREPORT(ERROR) from here till changes are logged */
START_CRIT_SECTION();
***
*** 1671,1678
if (newtupsize > pagefree)
{
/* Assume there's no chance to put newtup on same page.
*/
! newbuf = RelationGetBufferForTuple(relation,
newtup->t_len,
!
buffer);
}
else
{
--- 1674,1683
if (newtupsize > pagefree)
{
/* Assume there's no chance to put newtup on same page.
*/
! newbuf = RelationGetBufferForTuple(relation,
!
newtup->t_len,
!
buffer,
!
false);
}
else
{
***
*** 1688,1695
* should seldom be taken.
*/
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
! newbuf = RelationGetBufferForTuple(relation,
newtup->t_len,
!
buffer);
}
else
{
--- 1693,1702
* should seldom be taken.
*/
LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
! newbuf = RelationGetBufferForTuple(relation,
!
newtup->t_len,
!
buffer,
!
false);
}
else
{
diff -rc postgresql-8.0.0.orig/src/backend/access/heap/hio.c
postgresql-8.0.0.pctfree/src/backend/access/heap/hio.c
*** postgresql-8.0.0.orig/src/backend/access/heap/hio.c 2005-01-01
06:59:16.0 +0900
--- postgresql-8.0.0.pctfree/src/backend/access/heap/hio.c 2005-08-20
23:35:44.986085248 +0900
***
*** 89,95
*/
Buffer
RelationGetBufferForTuple(Relation relation, Size len,
! Buffer otherBuffer)
{
Buffer buffer = InvalidBuffer;
PagepageHeader;
--- 89,95
*/
Buffer
RelationGetBufferForTuple(Relation relation, Size len,
! Buffer otherBuffer, bool
forInsert)
{
Buffer buffer = InvalidBuffer;
PagepageHeader;
***
*** 136,142
* We have no cached target page, so ask the FSM for an initial
* target.
*/
!
