Hello there,

This is my first post in the list. I have a deep low-level background on
computer programming, but I am totally newbie to sql databases. I am using
postgres because of its commercial license.

My problem is with storing large values. I have a database that stores large
ammounts of data (each row consisting of up to 5MB). After carefully reading
the Postgres 8.0 manual (the version I'm using), I was told that the best
option was to create a bytea field.

Large objects are out of the line here since we have lots of tables.

As I understand it, the client needs to put the data into the server using a
textual-based command. This makes the 5MB data grow up-to 5x, making it 25MB
in the worst case. (Example: 0x01 -> \\001).

My question is:

1) Is there any way for me to send the binary field directly without needing
escape codes?
2) Will this mean that the client actually wastes my network bandwidth
converting binary data to text? Or does the client transparently manage
this?

Thanks for any light on the subject,
Rodrigo

On Tue, Oct 18, 2005 at 06:07:12PM +0000, Rodrigo Madera wrote:
> 1) Is there any way for me to send the binary field directly without needing
> escape codes?

In 7.4 and later the client/server protocol supports binary data
transfer. If you're programming with libpq you can use PQexecParams()
to send and/or retrieve values in binary instead of text.

http://www.postgresql.org/docs/8.0/i...IBPQ-EXEC-MAIN

APIs built on top of libpq or that implement the protcol themselves
might provide hooks to this capability; check your documentation.
What language and API are you using?

See also COPY BINARY:

http://www.postgresql.org/docs/8.0/i.../sql-copy.html

> 2) Will this mean that the client actually wastes my network bandwidth
> converting binary data to text? Or does the client transparently manage
> this?

Binary transfer sends data in binary, not by automatically converting
to and from text.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Please copy the mailing list on replies so others can participate
in and learn from the discussion.]

On Tue, Oct 18, 2005 at 07:09:08PM +0000, Rodrigo Madera wrote:
> > What language and API are you using?
>
> I'm using libpqxx. A nice STL-style library for C++ (I am 101% C++).

I've only dabbled with libpqxx; I don't know if or how you can make
it send data in binary instead of text. See the documentation or
ask in a mailing list like libpqxx-general or pgsql-interfaces.

> > Binary transfer sends data in binary, not by automatically converting
> > to and from text.
>
> Uh, I'm sorry I didn't get that... If I send: insert into foo
> values('\\001\\002') will libpq send 0x01, 0x02 or "\\\\001\\\\002"??

If you do it that way libpq will send the string as text with escape
sequences; you can use a sniffer like tcpdump or ethereal to see this
for yourself. To send the data in binary you'd call PQexecParams()
with a query like "INSERT INTO foo VALUES ($1)". The $1 is a
placeholder; the other arguments to PQexecParams() provide the data
itself, the data type and length, and specify whether the data is in
text format or binary. See the libpq documentation for details.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On 18/10/05, Michael Fuhr <mike@fuhr.org> wrote:
> [Please copy the mailing list on replies so others can participate
> in and learn from the discussion.]
>
> On Tue, Oct 18, 2005 at 07:09:08PM +0000, Rodrigo Madera wrote:
> > > What language and API are you using?
> >
> > I'm using libpqxx. A nice STL-style library for C++ (I am 101% C++).
>
> I've only dabbled with libpqxx; I don't know if or how you can make
> it send data in binary instead of text. See the documentation or
> ask in a mailing list like libpqxx-general or pgsql-interfaces.
>
> > > Binary transfer sends data in binary, not by automatically converting
> > > to and from text.
> >
> > Uh, I'm sorry I didn't get that... If I send: insert into foo
> > values('\\001\\002') will libpq send 0x01, 0x02 or "\\\\001\\\\002"??
>
> If you do it that way libpq will send the string as text with escape
> sequences; you can use a sniffer like tcpdump or ethereal to see this
> for yourself. To send the data in binary you'd call PQexecParams()
> with a query like "INSERT INTO foo VALUES ($1)". The $1 is a
> placeholder; the other arguments to PQexecParams() provide the data
> itself, the data type and length, and specify whether the data is in
> text format or binary. See the libpq documentation for details.
>

You could base64 encode your data admitiaddly increasing it by 1/3 but
it does at least convert it to text which means that its more
unserstandable. base64 is also pritty standard being whats used in
EMails for mime attachments.

Peter

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

HI!



I am having a confusion to the memory handling of postgreSQL.



Here is the Scenario.

I rebooted my Server which is a PostgreSQL 8.0 Running on Redhat 9, which is
a Dual Xeon Server and 6 gig of memory.

Of course there is not much memory still used since it is just restarted.

But after a number of access to the tables the memory is being used and it
is not being free up. Actually after this access to the database and the
server is just idle

The memory is still used up. I am monitoring this using the "free" command
which gives me about 5.5 gig of used memory and the rest free.



Is there something that I should do to minimize and free up the used memory?



Thanks You.



I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html

--On Freitag, Oktober 21, 2005 03:40:47 +0000 "Christian Paul B. Cosinas"
<cpc@cybees.com> wrote:
> I am having a confusion to the memory handling of postgreSQL.
> I rebooted my Server which is a PostgreSQL 8.0 Running on Redhat 9, which
> is a Dual Xeon Server and 6 gig of memory.
>
> Of course there is not much memory still used since it is just restarted.
>
> But after a number of access to the tables the memory is being used and
> it is not being free up. Actually after this access to the database and
> the server is just idle
>
> The memory is still used up. I am monitoring this using the "free"
> command which gives me about 5.5 gig of used memory and the rest free.
I suppose you looked at the top row of the free output?

Because there the disk-cache is counted as "used"... Have a look at the
second row where buffers are counted as free, which they more or less are.

> Is there something that I should do to minimize and free up the used
> memory?
No, the buffers make your database faster because they reduce direct disk
access

> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
I don't

Mit freundlichem Gruß,
Jens Schicke
--
Jens Schicke j.schicke@asco.de
asco GmbH http://www.asco.de
Mittelweg 7 Tel 0531/3906-127
38106 Braunschweig Fax 0531/3906-400

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

But as long as the memory is in the cache my database became much slower.
What could probably be the cause of this? But When I restarted the database
is back to normal processing.
-----Original Message-----
From: Jens-Wolfhard Schicke [mailto:ml+pgsql-performance@asco.de]
Sent: Friday, October 21, 2005 7:23 AM
To: Christian Paul B. Cosinas; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Used Memory

--On Freitag, Oktober 21, 2005 03:40:47 +0000 "Christian Paul B. Cosinas"
<cpc@cybees.com> wrote:
> I am having a confusion to the memory handling of postgreSQL.
> I rebooted my Server which is a PostgreSQL 8.0 Running on Redhat 9,
> which is a Dual Xeon Server and 6 gig of memory.
>
> Of course there is not much memory still used since it is just restarted.
>
> But after a number of access to the tables the memory is being used
> and it is not being free up. Actually after this access to the
> database and the server is just idle
>
> The memory is still used up. I am monitoring this using the "free"
> command which gives me about 5.5 gig of used memory and the rest free.
I suppose you looked at the top row of the free output?

Because there the disk-cache is counted as "used"... Have a look at the
second row where buffers are counted as free, which they more or less are.

> Is there something that I should do to minimize and free up the used
> memory?
No, the buffers make your database faster because they reduce direct disk
access

> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
I don't

Mit freundlichem Gruß,
Jens Schicke
--
Jens Schicke j.schicke@asco.de
asco GmbH http://www.asco.de
Mittelweg 7 Tel 0531/3906-127
38106 Braunschweig Fax 0531/3906-400


I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Also Does Creating Temporary table in a function and not dropping them
affects the performance of the database?


-----Original Message-----
From: Jens-Wolfhard Schicke [mailto:ml+pgsql-performance@asco.de]
Sent: Friday, October 21, 2005 7:23 AM
To: Christian Paul B. Cosinas; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Used Memory

--On Freitag, Oktober 21, 2005 03:40:47 +0000 "Christian Paul B. Cosinas"
<cpc@cybees.com> wrote:
> I am having a confusion to the memory handling of postgreSQL.
> I rebooted my Server which is a PostgreSQL 8.0 Running on Redhat 9,
> which is a Dual Xeon Server and 6 gig of memory.
>
> Of course there is not much memory still used since it is just restarted.
>
> But after a number of access to the tables the memory is being used
> and it is not being free up. Actually after this access to the
> database and the server is just idle
>
> The memory is still used up. I am monitoring this using the "free"
> command which gives me about 5.5 gig of used memory and the rest free.
I suppose you looked at the top row of the free output?

Because there the disk-cache is counted as "used"... Have a look at the
second row where buffers are counted as free, which they more or less are.

> Is there something that I should do to minimize and free up the used
> memory?
No, the buffers make your database faster because they reduce direct disk
access

> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
I don't

Mit freundlichem Gruß,
Jens Schicke
--
Jens Schicke j.schicke@asco.de
asco GmbH http://www.asco.de
Mittelweg 7 Tel 0531/3906-127
38106 Braunschweig Fax 0531/3906-400


I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html


---------------------------(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

On Fri, 21 Oct 2005 03:40:47 -0000
"Christian Paul B. Cosinas" <cpc@cybees.com> wrote:

>
> But after a number of access to the tables the memory is being used
> and it is not being free up. Actually after this access to the
> database and the server is just idle

I noticed this behavior on my SUSE linux box as well. I thought it was
a memory leak in something (I think there was an actual memory leak in
the kernel shared memory stuff, which I fixed by upgrading my kernel
to 2.6.13-ck8). It turns out that some file systems are better than
others when it comes to increasing the performance of I/O on Linux.
ReiserFS was what I put on originally and by the end of the day, the
box would be using all of it's available memory in caching inodes.

I kept rebooting and trying to get the memory usage to go down, but it
never did. All but 500MB of it was disk cache. I let my apps just run
and when the application server needed more memory, it reclaimed it from
the disk cache, so there weren't side effects to the fact that top and
free always reported full memory usage.

They tell me that this is a good thing, as it reduces disk I/O and
increases performance. That's all well and good, but it's entirely
unnecessary in our situation. Despite that, I can't turn it off because
my research into the issue has shown that kernel developers don't want
users to be able to turn off disk caching. There is a value
in /proc/sys/vm/vfs_cache_pressure that can be changed, which will
affect the propensity of the kernel to cache files in RAM (google it
to find the suggestions on what value to set it to), but there isn't a
setting to turn that off on purpose.

After rolling my own CK-based kernel, switching to XFS, and tweaking
the nice and CPU affinity of my database process (I use schedtool in my
CK kernel to run it at SCHED_FIFO, nice -15, and CPU affinity confined
to the second processor in my dual Xeon eServer) has got me to the
point that the perpetually high memory usage doesn't affect my
application server.

Hope any of this helps.

Jon Brisbin
Webmaster
NPC International, Inc.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[snip]
>
> to the second processor in my dual Xeon eServer) has got me to the
> point that the perpetually high memory usage doesn't affect my
> application server.


I'm curious - how does the high memory usage affect your application server?

Alex