Обсуждение: Persistence problem

Hello.

I have a problem. I've created several types and functions in C language and implemented them successfully. Or at least I thought so... When I insert, select, update data, everything works fine, but in that session only. As soon as I close psql and start it agan, the data is corrupted. Is there some common mistake I might have made? Code is pretty big, so I don't think it would do any good if I put it here...

Thanks.
Ivan

---

Hotmail: Powerful Free email with security by Microsoft. Get it now.

On 12 May 2010, at 18:08, I. B. wrote:

> Hello.
>
> I have a problem. I've created several types and functions in C language and implemented them successfully. Or at
leastI thought so... When I insert, select, update data, everything works fine, but in that session only. As soon as I
closepsql and start it agan, the data is corrupted. Is there some common mistake I might have made? Code is pretty big,
soI don't think it would do any good if I put it here... 

That sounds like what the documentation warns you about if you modify a structure directly from postgres without using
palloc.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4beadfeb10411880534263!

That was my first guess. I used palloc everywhere.. But to be sure, after I made the type, I tried to do the something like:

mytype * result;
mytype * realResult;
result = createType(...);
realResult = (mytype *)palloc(mytype->length);
mempcy (realResult, result, result->length);

It didn't help. Is that enough?

> Subject: Re: [GENERAL] Persistence problem
> From: dalroi@solfertje.student.utwente.nl
> Date: Wed, 12 May 2010 19:05:36 +0200
> CC: pgsql-general@postgresql.org
> To: i.bre@live.com
>
> On 12 May 2010, at 18:08, I. B. wrote:
>
> > Hello.
> >
> > I have a problem. I've created several types and functions in C language and implemented them successfully. Or at least I thought so... When I insert, select, update data, everything works fine, but in that session only. As soon as I close psql and start it agan, the data is corrupted. Is there some common mistake I might have made? Code is pretty big, so I don't think it would do any good if I put it here...
>
> That sounds like what the documentation warns you about if you modify a structure directly from postgres without using palloc.
>
> Alban Hertroys
>
> --
> If you can't see the forest for the trees,
> cut the trees and you'll see there is no forest.
>
>
> !DSPAM:1077,4beadfe710418634015713!
>
>

---

Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. Sign up now.

On Wed, May 12, 2010 at 07:12:10PM +0200, I. B. wrote:
>
> That was my first guess. I used palloc everywhere.. But to be sure, after I made the type, I tried to do the
somethinglike: 
>
> mytype * result;
> mytype * realResult;
> result = createType(...);
> realResult = (mytype *)palloc(mytype->length);
> mempcy (realResult, result, result->length);

Did you define the type properly at SQL level? Is it a varlena type or
fixed length? Did you return it properly (as Datum)?

You're going to need to post more information before we can help you
usefully.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first.
>                                       - Charles de Gaulle

On Thu, May 13, 2010 at 12:04:56PM +0200, I. B. wrote:
>
>
> I'll try to explain with as less code as possible.
> One of the types I wanted to create is called mpoint. This is a part of code:

<snip>

> typedef struct {
>     int4 length;
>     int noOfUnits;
>     void *units; // this is later casted to uPoint *
> } mapping_t;
>

This is not the correct way to handle varlena types. You can create the
datum that way, but if PostgreSQL decides to compress it (as it may
when writing to disk) you won't be able to read it back. Notably, the
"length" part of a varlena type is not always 4 bytes.

Make sure you have fully understood this page:
http://www.postgresql.org/docs/8.4/static/xfunc-c.html
it has a number of examples dealing with variable length types. You
MUST use the VARDATA/VARATT/etc macros to construct and read your data.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first.
>                                       - Charles de Gaulle

Thanks for the reply. However, I don't really understand how it works... Can you help? How should this function look like? Can I still create the type the same way I did for now and add something like this:

Datum mpoint_in(PG_FUNCTION_ARGS)
{
    char *str = PG_GETARG_CSTRING(0);
    mPoint *result;
    mPoint *finalResult;

    result = (mPoint *) create_mPoint(str);

    if (result == NULL) {
        Log ("mpoint_in: reporting error for invalid input syntax for mPoint");
        ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("invalid input syntax for mPoint: \"%s\"", str)));
    }
   
    finalResult = (mPoint *)palloc(VARSIZE(result));
    memset (finalResult, 0, VARSIZE(result));
    SET_VARSIZE(finalResult, VARSIZE(result));
    memcpy((void *) VARDATA(finalResult),
           (void *) VARDATA(result),
           VARSIZE(result) - VARHDRSZ);
   
    PG_RETURN_POINTER(finalResult);
}

I should still return Datum, right? At least version 1 calling convetion says that. And should I change something on the SQL side?

I would appreciate any help.
Thanks.
Ivan

> Date: Thu, 13 May 2010 12:42:09 +0200
> From: kleptog@svana.org
> To: i.bre@live.com
> CC: dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Persistence problem
>
> On Thu, May 13, 2010 at 12:04:56PM +0200, I. B. wrote:
> >
> >
> > I'll try to explain with as less code as possible.
> > One of the types I wanted to create is called mpoint. This is a part of code:
>
> <snip>
>
> > typedef struct {
> > int4 length;
> > int noOfUnits;
> > void *units; // this is later casted to uPoint *
> > } mapping_t;
> >
>
> This is not the correct way to handle varlena types. You can create the
> datum that way, but if PostgreSQL decides to compress it (as it may
> when writing to disk) you won't be able to read it back. Notably, the
> "length" part of a varlena type is not always 4 bytes.
>
> Make sure you have fully understood this page:
> http://www.postgresql.org/docs/8.4/static/xfunc-c.html
> it has a number of examples dealing with variable length types. You
> MUST use the VARDATA/VARATT/etc macros to construct and read your data.
>
> Hope this helps,
> --
> Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> > Patriotism is when love of your own people comes first; nationalism,
> > when hate for people other than your own comes first.
> > - Charles de Gaulle

---

Hotmail: Free, trusted and rich email service. Get it now.

UPDATE:

When I do this:

    realResult = (mPoint *)palloc(result->length);
    memcpy(realResult, result, result->length);

I get a right result in the same session, but corrupted in the next one.
I've also found one place in create_mPoint where I used realloc. But why would it matter if I copied the memory into a new variable?

Ivan

---

From: i.bre@live.com
To: kleptog@svana.org
CC: dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Persistence problem
Date: Thu, 13 May 2010 12:04:56 +0200

.ExternalClass .ecxhmmessage P {padding:0px;} .ExternalClass body.ecxhmmessage {font-size:10pt;font-family:Verdana;}
I'll try to explain with as less code as possible.
One of the types I wanted to create is called mpoint. This is a part of code:

Datum mpoint_in(PG_FUNCTION_ARGS)
{
    char *str = PG_GETARG_CSTRING(0);
    mPoint *result;

    result = (mPoint *) create_mPoint(str);

    if (result == NULL) {
        Log ("mpoint_in: reporting error for invalid input syntax for mPoint");
        ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("invalid input syntax for mPoint: \"%s\"", str)));
    }
   
    PG_RETURN_POINTER(result);
}

In SQL the internallength for the type is set to VARIABLE. The type mpoint is complex. It is a structure containing other structures.

typedef struct {
    int4 length;
    int noOfUnits;
    void *units; // this is later casted to uPoint *
} mapping_t;

typedef mapping_t    mPoint;

typedef struct {
    timeint_t interval;
    double x1, x0, y1, y0;
} uPoint;

typedef upoint_t    uPoint;

typedef struct {
    time_T start, end;
    short int LC, RC;
} timeint_t;

typedef struct {
    double time;
    short int infinity;
} time_T;

The function create_mPoint (char *str) creates the type. It is pretty complex and I don't think it's smart to post it here. Anyway, it creates dinamycally array of uPoints for units. During that period, I've used palloc. At the end I set the variable length:

result->length = sizeof(int4) + sizeof(int) + result->noOfUnits * sizeof(uPoint);

My first guess was that I don't have all the data directly next to each other in the memory. Then I've tried to add this in the function mpoint_in, after I get the result from create_mPoint:

    mPoint *finalResult;
    finalResult = (mPoint *)palloc(sizeof(mPoint));
    finalResult->units = (uPoint *)palloc(result->noOfUnits * sizeof(uPoint));
    memcpy(finalResult->units, result->units, result->noOfUnits * sizeof(uPoint));
    finalResult->noOfUnits = result->noOfUnits;
    finalResult->length = result->length;
    PG_RETURN_POINTER(finalResult);

When I call ToString (I've made it myself), I get the same content for both result and finalResult. However, unlike the first case when I return result and get normal data in that session which dissapears in next one, this time I get corrupted data even in the first session.

More code? More explanation? Or someone has an idea?

Thank you very much.
Ivan


> Date: Wed, 12 May 2010 19:45:26 +0200
> From: kleptog@svana.org
> To: i.bre@live.com
> CC: dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Persistence problem
>
> On Wed, May 12, 2010 at 07:12:10PM +0200, I. B. wrote:
> >
> > That was my first guess. I used palloc everywhere.. But to be sure, after I made the type, I tried to do the something like:
> >
> > mytype * result;
> > mytype * realResult;
> > result = createType(...);
> > realResult = (mytype *)palloc(mytype->length);
> > mempcy (realResult, result, result->length);
>
> Did you define the type properly at SQL level? Is it a varlena type or
> fixed length? Did you return it properly (as Datum)?
>
> You're going to need to post more information before we can help you
> usefully.
>
> Have a nice day,
> --
> Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> > Patriotism is when love of your own people comes first; nationalism,
> > when hate for people other than your own comes first.
> > - Charles de Gaulle

---

Hotmail: Trusted email with powerful SPAM protection. Sign up now.

---

Hotmail: Trusted email with powerful SPAM protection. Sign up now.

I'll try to explain with as less code as possible.
One of the types I wanted to create is called mpoint. This is a part of code:

Datum mpoint_in(PG_FUNCTION_ARGS)
{
    char *str = PG_GETARG_CSTRING(0);
    mPoint *result;

    result = (mPoint *) create_mPoint(str);

    if (result == NULL) {
        Log ("mpoint_in: reporting error for invalid input syntax for mPoint");
        ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("invalid input syntax for mPoint: \"%s\"", str)));
    }
   
    PG_RETURN_POINTER(result);
}

In SQL the internallength for the type is set to VARIABLE. The type mpoint is complex. It is a structure containing other structures.

typedef struct {
    int4 length;
    int noOfUnits;
    void *units; // this is later casted to uPoint *
} mapping_t;

typedef mapping_t    mPoint;

typedef struct {
    timeint_t interval;
    double x1, x0, y1, y0;
} uPoint;

typedef upoint_t    uPoint;

typedef struct {
    time_T start, end;
    short int LC, RC;
} timeint_t;

typedef struct {
    double time;
    short int infinity;
} time_T;

The function create_mPoint (char *str) creates the type. It is pretty complex and I don't think it's smart to post it here. Anyway, it creates dinamycally array of uPoints for units. During that period, I've used palloc. At the end I set the variable length:

result->length = sizeof(int4) + sizeof(int) + result->noOfUnits * sizeof(uPoint);

My first guess was that I don't have all the data directly next to each other in the memory. Then I've tried to add this in the function mpoint_in, after I get the result from create_mPoint:

    mPoint *finalResult;
    finalResult = (mPoint *)palloc(sizeof(mPoint));
    finalResult->units = (uPoint *)palloc(result->noOfUnits * sizeof(uPoint));
    memcpy(finalResult->units, result->units, result->noOfUnits * sizeof(uPoint));
    finalResult->noOfUnits = result->noOfUnits;
    finalResult->length = result->length;
    PG_RETURN_POINTER(finalResult);

When I call ToString (I've made it myself), I get the same content for both result and finalResult. However, unlike the first case when I return result and get normal data in that session which dissapears in next one, this time I get corrupted data even in the first session.

More code? More explanation? Or someone has an idea?

Thank you very much.
Ivan


> Date: Wed, 12 May 2010 19:45:26 +0200
> From: kleptog@svana.org
> To: i.bre@live.com
> CC: dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Persistence problem
>
> On Wed, May 12, 2010 at 07:12:10PM +0200, I. B. wrote:
> >
> > That was my first guess. I used palloc everywhere.. But to be sure, after I made the type, I tried to do the something like:
> >
> > mytype * result;
> > mytype * realResult;
> > result = createType(...);
> > realResult = (mytype *)palloc(mytype->length);
> > mempcy (realResult, result, result->length);
>
> Did you define the type properly at SQL level? Is it a varlena type or
> fixed length? Did you return it properly (as Datum)?
>
> You're going to need to post more information before we can help you
> usefully.
>
> Have a nice day,
> --
> Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> > Patriotism is when love of your own people comes first; nationalism,
> > when hate for people other than your own comes first.
> > - Charles de Gaulle

---

Hotmail: Trusted email with powerful SPAM protection. Sign up now.

"I. B." <i.bre@live.com> writes:
> When I do this:
>     realResult = (mPoint *)palloc(result->length);
>     memcpy(realResult, result, result->length);
> I get a right result in the same session, but corrupted in the next
> one.

I'm guessing a bit here, but I think what is happening is this:

> typedef struct {
>     int4 length;
>     int noOfUnits;
>     void *units; // this is later casted to uPoint *
> } mapping_t;

You're storing the above-named struct on disk, right?  And the "units"
pointer is pointing to an array that's somewhere else in memory?  As
long as the somewhere-else array survives, it will seem like everything
is okay.  But in a new session, that data in memory will certainly not
be there anymore.

You can't use pointers in data structures that are to be stored on disk.
The array data needs to be "in line" in the data structure, and
accounted for in the length word.

Martin's advice about using VARSIZE/VARDATA is good too.  Depending on
which PG version you're using, you might be able to get along without
that so long as you haven't marked the data type toastable (by using
a non-PLAIN storage option in CREATE TYPE).  But unless that array is
always pretty darn small, you're going to want to allow this type to
be toasted.

            regards, tom lane

Hello all,

I have a hard situation in hands. my autovacuum does not seem to be able
to get his job done;

database is under active INSERTs/UPDATEs;
CPU is in aprox 50% iowait for the past 5 hours;

I've tried turning off autovacuum and the effect goes away; I turn it back
on and it goes back to 50% iowait; my IO system is nothing special at all;

besides turning autovacuum off and running vacuum by hand once in a while,
what else can I do to get out of this situation ?

bellow some logs

I'm seriously considering turning off autovacuum for good; but I'dd like
to get input concerning other approaches... I mean... if I don't turn it
of, how can I be sure this will not happen again... we ship products with
PG inside... I must be absolutelly sure this will not ever happen in any of
our costumers. I'm a bit confuse... sorry :) !

Joao

============================================
2010-05-13 20:55:00 WEST   DEBUG:  server process (PID 6197) exited with
exit code 0
2010-05-13 20:55:00 WEST   DEBUG:  forked new backend, pid=6573 socket=6
2010-05-13 20:57:41 WEST   ERROR:  failed to re-find parent key in
"timeslots_strs_var_ts_key"
2010-05-13 20:58:42 WEST   LOG:  autovacuum: processing database
"postgres"
2010-05-13 20:59:35 WEST   DEBUG:  checkpoint starting
2010-05-13 20:59:35 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 20:59:42 WEST   LOG:  autovacuum: processing database
"template1"
2010-05-13 21:00:00 WEST   DEBUG:  server process (PID 6573) exited with
exit code 0
2010-05-13 21:00:00 WEST   DEBUG:  forked new backend, pid=6846 socket=6
2010-05-13 21:01:00 WEST   LOG:  autovacuum: processing database "egbert"
2010-05-13 21:01:00 WEST   DEBUG:  autovac: will VACUUM ANALYZE timeslots
2010-05-13 21:01:00 WEST   DEBUG:  vacuuming "public.timeslots"
2010-05-13 21:02:39 WEST   DEBUG:  index "timeslots_timestamp_index" now
contains 3971156 row versions in 15338 pages
2010-05-13 21:02:39 WEST   DETAIL:  351 index row versions were removed.
    0 index pages have been deleted, 0 are currently reusable.
    CPU 0.67s/1.28u sec elapsed 71.06 sec.
2010-05-13 21:03:51 WEST   DEBUG:  index "timeslots_timeslot_index" now
contains 3971159 row versions in 18724 pages
2010-05-13 21:03:51 WEST   DETAIL:  351 index row versions were removed.
    0 index pages have been deleted, 0 are currently reusable.
    CPU 0.86s/1.31u sec elapsed 71.65 sec.
2010-05-13 21:04:35 WEST   DEBUG:  checkpoint starting
2010-05-13 21:04:37 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:04:58 WEST   DEBUG:  index "timeslots_var_index" now
contains 3971167 row versions in 23857 pages
2010-05-13 21:04:58 WEST   DETAIL:  351 index row versions were removed.
    0 index pages have been deleted, 0 are currently reusable.
    CPU 1.34s/1.36u sec elapsed 67.49 sec.
2010-05-13 21:05:00 WEST   DEBUG:  server process (PID 6846) exited with
exit code 0
2010-05-13 21:05:01 WEST   DEBUG:  forked new backend, pid=7319 socket=6
2010-05-13 21:09:35 WEST   DEBUG:  checkpoint starting
2010-05-13 21:09:37 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:10:08 WEST   DEBUG:  server process (PID 7319) exited with
exit code 0
2010-05-13 21:10:08 WEST   DEBUG:  forked new backend, pid=7845 socket=6
2010-05-13 21:14:35 WEST   DEBUG:  checkpoint starting
2010-05-13 21:14:37 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:15:00 WEST   DEBUG:  server process (PID 7845) exited with
exit code 0
2010-05-13 21:15:00 WEST   DEBUG:  forked new backend, pid=8214 socket=6
2010-05-13 21:19:35 WEST   DEBUG:  checkpoint starting
2010-05-13 21:19:41 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:20:00 WEST   DEBUG:  server process (PID 8214) exited with
exit code 0
2010-05-13 21:20:00 WEST   DEBUG:  forked new backend, pid=8521 socket=6
2010-05-13 21:24:35 WEST   DEBUG:  checkpoint starting
2010-05-13 21:24:38 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:25:00 WEST   DEBUG:  server process (PID 8521) exited with
exit code 0
2010-05-13 21:25:00 WEST   DEBUG:  forked new backend, pid=8919 socket=6
2010-05-13 21:29:37 WEST   DEBUG:  checkpoint starting
2010-05-13 21:29:40 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:30:00 WEST   DEBUG:  server process (PID 8919) exited with
exit code 0
2010-05-13 21:30:00 WEST   DEBUG:  forked new backend, pid=9239 socket=6
2010-05-13 21:34:35 WEST   DEBUG:  checkpoint starting
2010-05-13 21:34:38 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:35:00 WEST   DEBUG:  server process (PID 9239) exited with
exit code 0
2010-05-13 21:35:00 WEST   DEBUG:  forked new backend, pid=9672 socket=6
2010-05-13 21:39:35 WEST   DEBUG:  checkpoint starting
2010-05-13 21:39:36 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:40:00 WEST   DEBUG:  server process (PID 9672) exited with
exit code 0
2010-05-13 21:40:00 WEST   DEBUG:  forked new backend, pid=9979 socket=6
2010-05-13 21:44:35 WEST   DEBUG:  checkpoint starting
2010-05-13 21:44:36 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:44:59 WEST   ERROR:  failed to re-find parent key in
"timeslots_strs_var_ts_key"
2010-05-13 21:45:00 WEST   DEBUG:  server process (PID 9979) exited with
exit code 0
2010-05-13 21:45:00 WEST   DEBUG:  forked new backend, pid=10340 socket=6
2010-05-13 21:46:00 WEST   LOG:  autovacuum: processing database
"postgres"
2010-05-13 21:47:00 WEST   LOG:  autovacuum: processing database
"template1"
2010-05-13 21:48:01 WEST   LOG:  autovacuum: processing database "egbert"
2010-05-13 21:48:01 WEST   DEBUG:  autovac: will VACUUM ANALYZE timeslots
2010-05-13 21:48:01 WEST   DEBUG:  vacuuming "public.timeslots"
2010-05-13 21:49:30 WEST   DEBUG:  index "timeslots_timestamp_index" now
contains 3971435 row versions in 15340 pages
2010-05-13 21:49:30 WEST   DETAIL:  405 index row versions were removed.
    0 index pages have been deleted, 0 are currently reusable.
    CPU 0.78s/1.23u sec elapsed 63.81 sec.
2010-05-13 21:49:35 WEST   DEBUG:  checkpoint starting
2010-05-13 21:49:37 WEST   DEBUG:  removing file "pg_subtrans/00AE"
2010-05-13 21:49:37 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:50:00 WEST   DEBUG:  server process (PID 10340) exited with
exit code 0
2010-05-13 21:50:01 WEST   DEBUG:  forked new backend, pid=10642 socket=6
2010-05-13 21:50:47 WEST   DEBUG:  index "timeslots_timeslot_index" now
contains 3971451 row versions in 18726 pages
2010-05-13 21:50:47 WEST   DETAIL:  405 index row versions were removed.
    0 index pages have been deleted, 0 are currently reusable.
    CPU 0.88s/1.32u sec elapsed 76.99 sec.
2010-05-13 21:51:56 WEST   DEBUG:  index "timeslots_var_index" now
contains 3971471 row versions in 23861 pages
2010-05-13 21:51:56 WEST   DETAIL:  405 index row versions were removed.
    0 index pages have been deleted, 0 are currently reusable.
    CPU 1.10s/1.40u sec elapsed 69.38 sec.
2010-05-13 21:54:35 WEST   DEBUG:  checkpoint starting
2010-05-13 21:54:36 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 21:55:00 WEST   DEBUG:  server process (PID 10642) exited with
exit code 0
2010-05-13 21:55:00 WEST   DEBUG:  forked new backend, pid=11061 socket=6
2010-05-13 21:59:35 WEST   DEBUG:  checkpoint starting
2010-05-13 21:59:37 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:00:00 WEST   DEBUG:  server process (PID 11061) exited with
exit code 0
2010-05-13 22:00:01 WEST   DEBUG:  forked new backend, pid=11398 socket=6
2010-05-13 22:04:35 WEST   DEBUG:  checkpoint starting
2010-05-13 22:04:36 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:05:00 WEST   DEBUG:  forked new backend, pid=11725 socket=6
2010-05-13 22:05:00 WEST   DEBUG:  server process (PID 11398) exited with
exit code 0
2010-05-13 22:09:35 WEST   DEBUG:  checkpoint starting
2010-05-13 22:09:39 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:10:00 WEST   DEBUG:  server process (PID 11725) exited with
exit code 0
2010-05-13 22:10:00 WEST   DEBUG:  forked new backend, pid=12073 socket=6
2010-05-13 22:14:35 WEST   DEBUG:  checkpoint starting
2010-05-13 22:14:39 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:15:00 WEST   DEBUG:  forked new backend, pid=12328 socket=6
2010-05-13 22:15:00 WEST   DEBUG:  server process (PID 12073) exited with
exit code 0
2010-05-13 22:19:35 WEST   DEBUG:  checkpoint starting
2010-05-13 22:19:38 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:20:00 WEST   DEBUG:  server process (PID 12328) exited with
exit code 0
2010-05-13 22:20:00 WEST   DEBUG:  forked new backend, pid=12601 socket=6
2010-05-13 22:24:35 WEST   DEBUG:  checkpoint starting
2010-05-13 22:24:37 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:25:02 WEST   DEBUG:  server process (PID 12601) exited with
exit code 0
2010-05-13 22:25:02 WEST   DEBUG:  forked new backend, pid=12864 socket=6
2010-05-13 22:29:35 WEST   DEBUG:  checkpoint starting
2010-05-13 22:29:36 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:30:00 WEST   DEBUG:  server process (PID 12864) exited with
exit code 0
2010-05-13 22:30:00 WEST   DEBUG:  forked new backend, pid=13109 socket=6
2010-05-13 22:31:29 WEST   ERROR:  failed to re-find parent key in
"timeslots_strs_var_ts_key"
2010-05-13 22:32:29 WEST   LOG:  autovacuum: processing database
"postgres"
2010-05-13 22:33:29 WEST   LOG:  autovacuum: processing database
"template1"
2010-05-13 22:34:29 WEST   LOG:  autovacuum: processing database "egbert"
2010-05-13 22:34:29 WEST   DEBUG:  autovac: will VACUUM ANALYZE timeslots
2010-05-13 22:34:29 WEST   DEBUG:  vacuuming "public.timeslots"
2010-05-13 22:34:35 WEST   DEBUG:  checkpoint starting
2010-05-13 22:34:36 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:35:00 WEST   DEBUG:  server process (PID 13109) exited with
exit code 0
2010-05-13 22:35:00 WEST   DEBUG:  forked new backend, pid=13329 socket=6
2010-05-13 22:36:06 WEST   DEBUG:  index "timeslots_timestamp_index" now
contains 3971652 row versions in 15341 pages
2010-05-13 22:36:06 WEST   DETAIL:  321 index row versions were removed.
    0 index pages have been deleted, 0 are currently reusable.
    CPU 0.81s/1.24u sec elapsed 72.89 sec.
2010-05-13 22:37:16 WEST   DEBUG:  index "timeslots_timeslot_index" now
contains 3971652 row versions in 18727 pages
2010-05-13 22:37:16 WEST   DETAIL:  321 index row versions were removed.
    0 index pages have been deleted, 0 are currently reusable.
    CPU 0.97s/1.35u sec elapsed 69.51 sec.
2010-05-13 22:38:27 WEST   DEBUG:  index "timeslots_var_index" now
contains 3971656 row versions in 23861 pages
2010-05-13 22:38:27 WEST   DETAIL:  321 index row versions were removed.
    0 index pages have been deleted, 0 are currently reusable.
    CPU 1.39s/1.43u sec elapsed 70.76 sec.
2010-05-13 22:39:35 WEST   DEBUG:  checkpoint starting
2010-05-13 22:39:38 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:40:00 WEST   DEBUG:  server process (PID 13329) exited with
exit code 0
2010-05-13 22:40:00 WEST   DEBUG:  forked new backend, pid=13549 socket=6
2010-05-13 22:44:36 WEST   DEBUG:  checkpoint starting
2010-05-13 22:44:45 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:45:00 WEST   DEBUG:  server process (PID 13549) exited with
exit code 0
2010-05-13 22:45:00 WEST   DEBUG:  forked new backend, pid=13918 socket=6
2010-05-13 22:49:35 WEST   DEBUG:  checkpoint starting
2010-05-13 22:49:38 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:50:01 WEST   DEBUG:  server process (PID 13918) exited with
exit code 0
2010-05-13 22:50:01 WEST   DEBUG:  forked new backend, pid=14218 socket=6
2010-05-13 22:50:22 WEST   DEBUG:  forked new backend, pid=14278 socket=6
2010-05-13 22:51:11 WEST   DEBUG:  server process (PID 14278) exited with
exit code 0
2010-05-13 22:51:20 WEST   DEBUG:  forked new backend, pid=14368 socket=6
2010-05-13 22:51:22 WEST   DEBUG:  server process (PID 14368) exited with
exit code 0
2010-05-13 22:52:52 WEST   DEBUG:  forked new backend, pid=14516 socket=6
2010-05-13 22:52:52 WEST   DEBUG:  server process (PID 14516) exited with
exit code 0
2010-05-13 22:54:35 WEST   DEBUG:  checkpoint starting
2010-05-13 22:54:37 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 22:55:00 WEST   DEBUG:  server process (PID 14218) exited with
exit code 0
2010-05-13 22:55:00 WEST   DEBUG:  forked new backend, pid=14775 socket=6
2010-05-13 22:59:35 WEST   DEBUG:  checkpoint starting
2010-05-13 22:59:40 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 23:00:01 WEST   DEBUG:  server process (PID 14775) exited with
exit code 0
2010-05-13 23:00:01 WEST   DEBUG:  forked new backend, pid=15443 socket=6
2010-05-13 23:04:35 WEST   DEBUG:  checkpoint starting
2010-05-13 23:04:43 WEST   DEBUG:  checkpoint complete; 0 transaction log
file(s) added, 0 removed, 0 recycled
2010-05-13 23:05:00 WEST   DEBUG:  forked new backend, pid=16028 socket=6
2010-05-13 23:05:00 WEST   DEBUG:  server process (PID 15443) exited with
exit code 0

Joao Ferreira <joao.miguel.c.ferreira@gmail.com> writes:
> I have a hard situation in hands. my autovacuum does not seem to be able
> to get his job done;

> database is under active INSERTs/UPDATEs;
> CPU is in aprox 50% iowait for the past 5 hours;

> I've tried turning off autovacuum and the effect goes away; I turn it back
> on and it goes back to 50% iowait; my IO system is nothing special at all;

Indeed, it sounds like you don't have enough I/O capacity for the size
of database you are running.

Turning off autovacuum is extremely unlikely to be a good solution;
it might reduce the bandwidth demand but it will just hasten the
database's bloating into unusability.

> 2010-05-13 21:44:59 WEST   ERROR:  failed to re-find parent key in
> "timeslots_strs_var_ts_key"

You seem to have some worse problems than disk bandwidth, though :-(.
The above is not good at all.  What PG version is this?  If not recent,
update, then reindex that index.

            regards, tom lane

On Thu, May 13, 2010 at 4:05 PM, Joao Ferreira
<joao.miguel.c.ferreira@gmail.com> wrote:
>
> Hello all,
>
> I have a hard situation in hands. my autovacuum does not seem to be able
> to get his job done;
>
> database is under active INSERTs/UPDATEs;
> CPU is in aprox 50% iowait for the past 5 hours;
>
> I've tried turning off autovacuum and the effect goes away; I turn it back
> on and it goes back to 50% iowait; my IO system is nothing special at all;
>
> besides turning autovacuum off and running vacuum by hand once in a while,
> what else can I do to get out of this situation ?
>
> bellow some logs
>
> I'm seriously considering turning off autovacuum for good; but I'dd like
> to get input concerning other approaches... I mean... if I don't turn it
> of, how can I be sure this will not happen again... we ship products with
> PG inside... I must be absolutelly sure this will not ever happen in any of
> our costumers. I'm a bit confuse... sorry :) !

Have you considered tuning autovacuum to not use less IO so that it
has no serious impact on other running pg processes?  it's pretty easy
to do, just don't go crazy (i.e. move autovacuum_vacuum_cost_delay
from 10 to 20 or 30 ms, not 2000ms)

Thanks for the reply.
Why is that somewhere else in the memory if I reserve enough memory with palloc and copy the complete memory from the previously created type into that new object?

realResult = (mPoint *)palloc(result->length);
memcpy(realResult, result, result->length);

OK, I suppose I should use the VARDATA and VARSIZE. But I really can't make it to work. I don't think I understood well enough how it works. This is what I'd like to do. I would like to create the type as I already did, and when it's created I would like to copy its memory into a new object. So, if I have:

Datum mpoint_in(PG_FUNCTION_ARGS)
{
    char *str = PG_GETARG_CSTRING(0);
    mPoint *result = (mPoint *) create_mPoint(str);

    mPoint *final result = ... //copy the memory from result

    PG_RETURN_POINTER(result);
}

Datum mpoint_out(PG_FUNCTION_ARGS)
{
    mPoint * input = (mPoint *) PG_GETARG_POINTER(0);
    char * result = ToString (input, MPOINT);   
    PG_RETURN_CSTRING(result);
}

Can you please help me to make this? How to copy the memory in a good way? Should I somehow change the type mPoint? Should I also change the SQL side?

CREATE TYPE mpoint (
   internallength = VARIABLE,
   input = mpoint_in,
   output = mpoint_out
);

Please help, it would mean a lot.

Thanks,
Ivan


> To: i.bre@live.com
> CC: kleptog@svana.org; dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Persistence problem
> Date: Thu, 13 May 2010 15:08:58 -0400
> From: tgl@sss.pgh.pa.us
>
> "I. B." <i.bre@live.com> writes:
> > When I do this:
> > realResult = (mPoint *)palloc(result->length);
> > memcpy(realResult, result, result->length);
> > I get a right result in the same session, but corrupted in the next
> > one.
>
> I'm guessing a bit here, but I think what is happening is this:
>
> > typedef struct {
> > int4 length;
> > int noOfUnits;
> > void *units; // this is later casted to uPoint *
> > } mapping_t;
>
> You're storing the above-named struct on disk, right? And the "units"
> pointer is pointing to an array that's somewhere else in memory? As
> long as the somewhere-else array survives, it will seem like everything
> is okay. But in a new session, that data in memory will certainly not
> be there anymore.
>
> You can't use pointers in data structures that are to be stored on disk.
> The array data needs to be "in line" in the data structure, and
> accounted for in the length word.
>
> Martin's advice about using VARSIZE/VARDATA is good too. Depending on
> which PG version you're using, you might be able to get along without
> that so long as you haven't marked the data type toastable (by using
> a non-PLAIN storage option in CREATE TYPE). But unless that array is
> always pretty darn small, you're going to want to allow this type to
> be toasted.
>
> regards, tom lane

---

Hotmail: Trusted email with Microsoft’s powerful SPAM protection. Sign up now.

I still have the same problem. Whatever I've tried didn't work out.
It seems like VARSIZE is wrong. It's less than it should be. It seems like it's not counting on the size of units array, although it changes depending on the number of units.
This is one of the things I've tried:

Datum mbool_in(PG_FUNCTION_ARGS)
{
    char *str = PG_GETARG_CSTRING(0);
    mBool *result;
        mBool *finalResult;
        int size;

    result = (mBool *) create_mConst(str, MBOOL);

        size = sizeof(int4) + sizeof(int) + result->noOfUnits*sizeof(uBool);
        finalResult = (mBool *) palloc(size);
        memcpy(finalResult, result, size);
        SET_VARSIZE(finalResult, size);
   
    PG_RETURN_POINTER(finalResult);
}

Datum mbool_out(PG_FUNCTION_ARGS)
{
    mBool * input = (mBool *) PG_GETARG_POINTER(0);
        mBool * safeCopy;
    char * result;

        safeCopy = (mBool *) palloc(VARSIZE(input));
        SET_VARSIZE(safeCopy, VARSIZE(input));
        memcpy((void *) VARDATA(safeCopy),
               (void *) VARDATA(input),
                VARSIZE(input));
   
    result = ToString (safeCopy, MBOOL);
   
    PG_RETURN_CSTRING(result);
}

How to fix this? The one who helps can count on the best Croatian dark beer shipped directly to his place. ;)

Ivan

---

From: i.bre@live.com
To: tgl@sss.pgh.pa.us
CC: kleptog@svana.org; dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Persistence problem
Date: Fri, 14 May 2010 14:35:34 +0200

.ExternalClass .ecxhmmessage P {padding:0px;} .ExternalClass body.ecxhmmessage {font-size:10pt;font-family:Verdana;} Thanks for the reply.
Why is that somewhere else in the memory if I reserve enough memory with palloc and copy the complete memory from the previously created type into that new object?

realResult = (mPoint *)palloc(result->length);
memcpy(realResult, result, result->length);

OK, I suppose I should use the VARDATA and VARSIZE. But I really can't make it to work. I don't think I understood well enough how it works. This is what I'd like to do. I would like to create the type as I already did, and when it's created I would like to copy its memory into a new object. So, if I have:

Datum mpoint_in(PG_FUNCTION_ARGS)
{
    char *str = PG_GETARG_CSTRING(0);
    mPoint *result = (mPoint *) create_mPoint(str);

    mPoint *final result = ... //copy the memory from result

    PG_RETURN_POINTER(result);
}

Datum mpoint_out(PG_FUNCTION_ARGS)
{
    mPoint * input = (mPoint *) PG_GETARG_POINTER(0);
    char * result = ToString (input, MPOINT);   
    PG_RETURN_CSTRING(result);
}

Can you please help me to make this? How to copy the memory in a good way? Should I somehow change the type mPoint? Should I also change the SQL side?

CREATE TYPE mpoint (
   internallength = VARIABLE,
   input = mpoint_in,
   output = mpoint_out
);

Please help, it would mean a lot.

Thanks,
Ivan


> To: i.bre@live.com
> CC: kleptog@svana.org; dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Persistence problem
> Date: Thu, 13 May 2010 15:08:58 -0400
> From: tgl@sss.pgh.pa.us
>
> "I. B." <i.bre@live.com> writes:
> > When I do this:
> > realResult = (mPoint *)palloc(result->length);
> > memcpy(realResult, result, result->length);
> > I get a right result in the same session, but corrupted in the next
> > one.
>
> I'm guessing a bit here, but I think what is happening is this:
>
> > typedef struct {
> > int4 length;
> > int noOfUnits;
> > void *units; // this is later casted to uPoint *
> > } mapping_t;
>
> You're storing the above-named struct on disk, right? And the "units"
> pointer is pointing to an array that's somewhere else in memory? As
> long as the somewhere-else array survives, it will seem like everything
> is okay. But in a new session, that data in memory will certainly not
> be there anymore.
>
> You can't use pointers in data structures that are to be stored on disk.
> The array data needs to be "in line" in the data structure, and
> accounted for in the length word.
>
> Martin's advice about using VARSIZE/VARDATA is good too. Depending on
> which PG version you're using, you might be able to get along without
> that so long as you haven't marked the data type toastable (by using
> a non-PLAIN storage option in CREATE TYPE). But unless that array is
> always pretty darn small, you're going to want to allow this type to
> be toasted.
>
> regards, tom lane

---

Hotmail: Trusted email with Microsoft’s powerful SPAM protection. Sign up now.

---

Hotmail: Trusted email with powerful SPAM protection. Sign up now.

"I. B." <i.bre@live.com> writes:
> How to fix this?

As long as you keep on showing us wrappers, and not the code that
actually does the work, we're going to remain in the dark.  What
you have shown us just copies data from point A to point B, and
it looks like it would be fine if the source data conforms to
PG's expectations.  But the problem is presumably that the source
data *doesn't* conform to those expectations.

            regards, tom lane

OK, here is the part of the code.

typedef struct {
    int4 length;
    int noOfUnits;
    void *units;
} mapping_t;

typedef struct {
    timeint_t interval;
    double x1, x0, y1, y0;
    // fx(t) = x1*t+x0, fy(t) = y1*t+y0
} upoint_t;

typedef struct {
    time_T start, end;
    short int LC, RC; // left or right closed
} timeint_t;

typedef struct {
    double time;
    short int infinity;
} time_T;

typedef upoint_t    uPoint;
typedef mapping_t    mPoint;

mPoint * create_mPoint(char *str) {
    /*
     *
     * str: ((start end x1 x0 y1 y0),(start end x1 x0 y1 y0),...)
     *
     */

    char *unit = NULL;
    mPoint * result = NULL;
    uPoint * units = NULL;
    uPoint * singleUnit;
    int stringLength;
    char *strTemp;
    int i;

    if (str == NULL) {
        return NULL;
    }

    stringLength = strlen(str);

    if (stringLength < 2 || str[0] != '(' || str[stringLength-1] != ')') {
        return NULL;
    } else {
        str = &str[1];
        strTemp = (char *) palloc ((stringLength-1)*sizeof(char));
        strncpy(strTemp, str, stringLength-2);
        strTemp[stringLength-2] = '\0';
        str = strTemp;
    };

    // allocating memory
    result = (mPoint *) palloc(sizeof(mPoint));
   
    result->noOfUnits = 0;
    unit = strtok(str, ",");   
   
    while (unit != NULL) {
        Log ("create_mPoint: str units:");
        Log2 ("\tunit:", unit);

        result->noOfUnits++;

        singleUnit = create_uPoint(unit);
       
        if (singleUnit == NULL) {
            return NULL;
        }

        units = (uPoint *) realloc(units, result->noOfUnits * sizeof(uPoint)); // EXPLAINED AT THE END OF THE POST
        units[result->noOfUnits - 1] = * singleUnit;

        unit = strtok(NULL, ",");
    };

    result->units = units;                     // EXPLAINED AT THE END OF THE POST
   
    if (SortUnits(result, MPOINT) == NULL) {
        return NULL;
    };

    result->length = sizeof(int4) + sizeof(int) + result->noOfUnits * sizeof(uPoint);
   
    //pfree(singleUnit);
    //pfree(strTemp);
    //pfree(unit);

    Log ("create_mPoint: moving type created");

    return result;
};


uPoint * create_uPoint(char *str) {
    double startTime, endTime;
    double x0, x1, y0, y1;
    timeint_t * interval;

    uPoint *result;

    if (sscanf(str, " ( %lf %lf %lf %lf %lf %lf )", &startTime, &endTime, &x1, &x0, &y1, &y0) != 6) {
        return NULL;
    }

    // allocate memory
    result = (uPoint *) palloc(sizeof(uPoint));
   
    result->x0 = x0;
    result->x1 = x1;
    result->y0 = y0;
    result->y1 = y1;

    // insert interval
    interval = (timeint_t *) createTimeInterval(startTime, endTime);
    if (interval == NULL) {
        return NULL;
    };
   
    result->interval = *interval;

    Log ("create_uPoint: uPoint (unit type) created");

    return result;
};


timeint_t * createTimeInterval(double startTime, double endTime) {
    timeint_t * interval = (timeint_t *) palloc(sizeof(timeint_t));

    Log ("createTimeInterval: entering function");
   
    if (startTime > endTime) {
        return NULL;
    };
   
    interval->start.time = startTime;
    interval->end.time = endTime;
   
    if (startTime < 0) {
        interval->LC = 0;
        interval->start.infinity = 1;
    }
    else {
        interval->LC = 1;
        interval->start.infinity = 0;
    }
    if (endTime < 0) {
        interval->RC = 0;
        interval->end.infinity = 1;
    }
    else {
        interval->RC = 1;
        interval->end.infinity = 1;
    }

    return interval;
};

The part where I've used realloc and set result->units=units, I've tried to replace it with palloc in the way to create the array units and then do this:

result->units = (uPoint *) palloc(result->noOfUnits * sizeof(uPoint));
memcpy(result->units, units, result->noOfUnits * sizeof(uPoint));

I've also tried to reserve the whole memory with palloc:

result = (mPoint *)palloc(sizeof(int4) + sizeof(int) + noOfUnits*sizeof(uPoint));
result->noOfUnits = noOfUnits;
result->length = sizeof(int4) + sizeof(int) + noOfUnits*sizeof(uPoint);
memcpy(result->units, units, result->noOfUnits * sizeof(uPoint));

I've then tried to palloc both result and result->units... I just don't know how I should do it. :-/
I hope you understand the problem better now.

Thanks,
Ivan

> To: i.bre@live.com
> CC: kleptog@svana.org; dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Persistence problem
> Date: Fri, 14 May 2010 17:32:49 -0400
> From: tgl@sss.pgh.pa.us
>
> "I. B." <i.bre@live.com> writes:
> > How to fix this?
>
> As long as you keep on showing us wrappers, and not the code that
> actually does the work, we're going to remain in the dark. What
> you have shown us just copies data from point A to point B, and
> it looks like it would be fine if the source data conforms to
> PG's expectations. But the problem is presumably that the source
> data *doesn't* conform to those expectations.
>
> regards, tom lane

---

Hotmail: Free, trusted and rich email service. Get it now.

"I. B." <i.bre@live.com> writes:
> OK, here is the part of the code.

Well, as suspected, you're doing this

> typedef struct {
>     void *units;
> } mapping_t;

and this

>         units = (uPoint *) realloc(units, result->noOfUnits * sizeof(uPoint)); // EXPLAINED AT THE END OF THE POST

which means that the array isn't contiguous with the mPoint struct.
You can certainly do that and then rearrange things to make it so
afterwards, but you're not doing so now.  Personally though I'd avoid
having two different representations.  You'd be better off with

typedef struct {
    int4 length;
    int noOfUnits;
    uPoint units[1];    /* actually, a variable length array */
} mPoint;

and then allocating or reallocating the result struct with a size
calculation like this:

    offsetof(mPoint, units) + noOfUnits * sizeof(uPoint)

BTW, realloc (as opposed to repalloc) doesn't seem like a tremendously
good idea here.  You are leaking that memory for the life of the session.

            regards, tom lane

Hello guys,

thx for your inputs. I consider you suggestions valid.

We have hundreds or thousands of unreachable and unmaintained PG
instalations. I'm totally unable to experiment in each of them. Usage
profile can range from 100 rows per hour to 1000, 10.000, 50.000...
sustained... for several days... or even forever... CPU's and IO
subsystem also varies.. from cheap IO to fast enterprise grade hardware.

But I did try vacuum_cost_delay and the effect is as expected: IOWAIT
reduces :) but vacuum time increases even more :(  but it still does not
leave the processor alone; autovaccum is still not able to finish it's
job... and I can't wait forever.

We are using pg 8.1.4, and yes we a) reindex b) vacuum full and c)
reindex again  once a week up to once a month; this weekly/monthly
maintenance script has been quite effective as a workaround for the
"can't find parent index" bug... we can eliminate heavy bloats just by
running the script... but it also plays an important role in keeping
database correctly indexed (reindex) and responsive.

My experience with this scenario tells me (I may be wrong) that I don't
really need autovacuum as long as I keep those periodic maintenance
operations running (tipically Saturdays or Sundays during the night)

For several reasons I can _not_ upgrade pg. I must use 8.1.4 and just
live with it. And learn to work around the issues it might bring me.
Additionally I can not change the cheap storage we ship in some lower
end versions of our product.

So, I'm still considering turning off autovacuum.

any thoughts ?

I really appreciate the discussion. thx a lot.

Cheers
Joao

On Fri, 2010-05-14 at 06:47 -0400, Scott Mead wrote:
>
>
> On Thu, May 13, 2010 at 6:23 PM, Scott Marlowe
> <scott.marlowe@gmail.com> wrote:
>         On Thu, May 13, 2010 at 4:05 PM, Joao Ferreira
>         <joao.miguel.c.ferreira@gmail.com> wrote:
>         >
>         > Hello all,
>         >
>         > I have a hard situation in hands. my autovacuum does not
>         seem to be able
>         > to get his job done;
>         >
>         > database is under active INSERTs/UPDATEs;
>         > CPU is in aprox 50% iowait for the past 5 hours;
>         >
>         > I've tried turning off autovacuum and the effect goes away;
>         I turn it back
>         > on and it goes back to 50% iowait; my IO system is nothing
>         special at all;
>         >
>         > besides turning autovacuum off and running vacuum by hand
>         once in a while,
>         > what else can I do to get out of this situation ?
>         >
>         > bellow some logs
>         >
>         > I'm seriously considering turning off autovacuum for good;
>         but I'dd like
>         > to get input concerning other approaches... I mean... if I
>         don't turn it
>         > of, how can I be sure this will not happen again... we ship
>         products with
>         > PG inside... I must be absolutelly sure this will not ever
>         happen in any of
>         > our costumers. I'm a bit confuse... sorry :) !
>
>
>         Have you considered tuning autovacuum to not use less IO so
>         that it
>         has no serious impact on other running pg processes?  it's
>         pretty easy
>         to do, just don't go crazy (i.e. move
>         autovacuum_vacuum_cost_delay
>         from 10 to 20 or 30 ms, not 2000ms)
>
>
> + 1 here, start with a 20ms delay, your vacuums make take a bit longer
> to run, but they'll have less impact on I/O.
>
>
> Just curious, what is your log_min_messages setting? I notice that you
> had 'DEBUG' in your logs, I'm guessing that you've just cranked up to
> DEBUG for your testing.... make sure that you leave that 'warning' or
> 'notice' for production, leaving those logs at DEBUG will also chew up
> I/O and get in the way of things like autovacuum.
>
>
>
>
> What version of Postgres are you using?  The visibility map in 8.4
> should lower the amount of I/O that you're stuck with (inmost cases)
> with vacuum.  Although you'll still need a full table scan to avoid
> xid wrap, you should get away with only vacuuming changed blocks in
> the general case.
>
>
> --
> Scott Mead
> EnterpriseDB
> The Enterprise Postgres Company
> www.enterprisedb.com
>
>
>         --
>         Sent via pgsql-general mailing list
>         (pgsql-general@postgresql.org)
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/pgsql-general
>
>

Thanks a lot for the patience and help. It worked.

Ivan


> To: i.bre@live.com
> CC: kleptog@svana.org; dalroi@solfertje.student.utwente.nl; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Persistence problem
> Date: Fri, 14 May 2010 18:34:14 -0400
> From: tgl@sss.pgh.pa.us
>
> "I. B." <i.bre@live.com> writes:
> > OK, here is the part of the code.
>
> Well, as suspected, you're doing this
>
> > typedef struct {
> > void *units;
> > } mapping_t;
>
> and this
>
> > units = (uPoint *) realloc(units, result->noOfUnits * sizeof(uPoint)); // EXPLAINED AT THE END OF THE POST
>
> which means that the array isn't contiguous with the mPoint struct.
> You can certainly do that and then rearrange things to make it so
> afterwards, but you're not doing so now. Personally though I'd avoid
> having two different representations. You'd be better off with
>
> typedef struct {
> int4 length;
> int noOfUnits;
> uPoint units[1]; /* actually, a variable length array */
> } mPoint;
>
> and then allocating or reallocating the result struct with a size
> calculation like this:
>
> offsetof(mPoint, units) + noOfUnits * sizeof(uPoint)
>
> BTW, realloc (as opposed to repalloc) doesn't seem like a tremendously
> good idea here. You are leaking that memory for the life of the session.
>
> regards, tom lane

---

Hotmail: Trusted email with Microsoft’s powerful SPAM protection. Sign up now.

Joao Ferreira gmail wrote:
> We have hundreds or thousands of unreachable and unmaintained PG
> instalations. I'm totally unable to experiment in each of them. Usage
> profile can range from 100 rows per hour to 1000, 10.000, 50.000...
> sustained... for several days... or even forever... CPU's and IO
> subsystem also varies.. from cheap IO to fast enterprise grade hardware.
>

It's impossible to tune something as complicated as vacuuming for this
range of workloads with a single configuration of settings.  The best
you could do is write a script that monitored each individual system and
tweaked itself based on activity.  That is however a very hard problem
to solve.

> We are using pg 8.1.4, and yes we a) reindex b) vacuum full and c)
> reindex again  once a week up to once a month; this weekly/monthly
> maintenance script has been quite effective as a workaround for the
> "can't find parent index" bug... we can eliminate heavy bloats just by
> running the script... but it also plays an important role in keeping
> database correctly indexed (reindex) and responsive.
>

The autovacuum in 8.1 barely works, if you set it carefully to match the
system workload.  That means that in your case, it effectively doesn't
work, because you don't have enough information to do that.

If they're already working for you, I think that scheduled vacuums done
at known off-hours periods are as good as you're going to do here.

> For several reasons I can _not_ upgrade pg. I must use 8.1.4 and just
> live with it. And learn to work around the issues it might bring me.
> Additionally I can not change the cheap storage we ship in some lower
> end versions of our product.
>

Just make sure you're producing good backups of these systems then.  You
can expect to run into one of the data eating bugs in 8.1 eventually.
You'll probably have a corrupt database from the sort of fsync issues
cheap storage introduces too.  Not upgrading from 8.1.4 is effectively
saying "I don't care about the integrity of my database", so you really
need to make alternate plans to help there.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

On Sun, May 16, 2010 at 12:32 PM, Greg Smith <greg@2ndquadrant.com> wrote:

> Just make sure you're producing good backups of these systems then.  You can
> expect to run into one of the data eating bugs in 8.1 eventually.  You'll
> probably have a corrupt database from the sort of fsync issues cheap storage
> introduces too.  Not upgrading from 8.1.4 is effectively saying "I don't
> care about the integrity of my database", so you really need to make
> alternate plans to help there.

I don't say this often, but "this".

On Fri, May 14, 2010 at 5:59 PM, Joao Ferreira gmail
<joao.miguel.c.ferreira@gmail.com> wrote:
>
> For several reasons I can _not_ upgrade pg. I must use 8.1.4 and just
> live with it. And learn to work around the issues it might bring me.
> Additionally I can not change the cheap storage we ship in some lower
> end versions of our product.
>

why you can't upgrade to say: 8.1.20... it has the same behaviour as
8.1.4 and less bugs...

--
Jaime Casanova         www.2ndQuadrant.com
Soporte y capacitación de PostgreSQL

On Sun, May 16, 2010 at 1:19 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
> On Fri, May 14, 2010 at 5:59 PM, Joao Ferreira gmail
> <joao.miguel.c.ferreira@gmail.com> wrote:
>>
>> For several reasons I can _not_ upgrade pg. I must use 8.1.4 and just
>> live with it. And learn to work around the issues it might bring me.
>> Additionally I can not change the cheap storage we ship in some lower
>> end versions of our product.
>>
>
> why you can't upgrade to say: 8.1.20... it has the same behaviour as
> 8.1.4 and less bugs...

Sadly not everybody has efficient testing, unit or otherwise.  And
certification of new software can take days or weeks.

On Thu, May 13, 2010 at 7:05 PM, Joao Ferreira
<joao.miguel.c.ferreira@gmail.com> wrote:
>
> Hello all,
>
> I have a hard situation in hands. my autovacuum does not seem to be able
> to get his job done;
>
> database is under active INSERTs/UPDATEs;
> CPU is in aprox 50% iowait for the past 5 hours;
>

vmstat divides io percent (last column) per cpu core. Probabily you
have 100%, look iostat last column (util).


> I've tried turning off autovacuum and the effect goes away; I turn it back
> on and it goes back to 50% iowait; my IO system is nothing special at all;
>
> besides turning autovacuum off and running vacuum by hand once in a while,
> what else can I do to get out of this situation ?
>
> bellow some logs
>
> I'm seriously considering turning off autovacuum for good; but I'dd like
> to get input concerning other approaches... I mean... if I don't turn it
> of, how can I be sure this will not happen again... we ship products with
> PG inside... I must be absolutelly sure this will not ever happen in any of
> our costumers. I'm a bit confuse... sorry :) !
>
> Joao
>

schedule the vacum to midnight

--
Reinaldo de Carvalho
http://korreio.sf.net
http://python-cyrus.sf.net