pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
postgresql performance with multimedia
8+ messages / 4 participants
[nested] [flat]

* postgresql performance with multimedia
@ 2004-08-18 00:44 my thi ho <mthoatbanjo@yahoo.com>
  2004-08-18 13:10 ` Re: postgresql performance with multimedia Jan Wieck <JanWieck@Yahoo.com>
  0 siblings, 1 reply; 8+ messages in thread

From: my thi ho @ 2004-08-18 00:44 UTC (permalink / raw)
  To: pgsql-performance

Hi,
I am working on a project which explore postgresql to
store multimedia data.
In details,  i am trying to work with the buffer
management part of postgres source code. And try to
improve the performance. I had search on the web but
could not find much usefull information. 
It would be great if anyone knows any developer groups
that working on similar things ? or where can i find
more information on this issue?
Thank you very much for your help
regards,
MT Ho


		
__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: postgresql performance with multimedia
  2004-08-18 00:44 postgresql performance with multimedia my thi ho <mthoatbanjo@yahoo.com>
@ 2004-08-18 13:10 ` Jan Wieck <JanWieck@Yahoo.com>
  2004-08-24 05:08   ` Re: postgresql performance with multimedia my ho <mthoatbanjo@yahoo.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Jan Wieck @ 2004-08-18 13:10 UTC (permalink / raw)
  To: my thi ho <mthoatbanjo@Yahoo.com>; +Cc: pgsql-performance

On 8/17/2004 8:44 PM, my thi ho wrote:

> Hi,
> I am working on a project which explore postgresql to
> store multimedia data.
> In details,  i am trying to work with the buffer
> management part of postgres source code. And try to
> improve the performance. I had search on the web but
> could not find much usefull information. 

What version of PostgreSQL are you looking at? Note that the buffer 
cache replacement strategy was completely changed for version 8.0, which 
is currently in BETA test. A description of the algorithm can be found 
in the README file in src/backend/storage/bufmgr.


Jan

> It would be great if anyone knows any developer groups
> that working on similar things ? or where can i find
> more information on this issue?
> Thank you very much for your help
> regards,
> MT Ho
> 
> 
> 		
> __________________________________
> Do you Yahoo!?
> Yahoo! Mail - 50x more storage than other providers!
> http://promotions.yahoo.com/new_mail
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: postgresql performance with multimedia
  2004-08-18 00:44 postgresql performance with multimedia my thi ho <mthoatbanjo@yahoo.com>
  2004-08-18 13:10 ` Re: postgresql performance with multimedia Jan Wieck <JanWieck@Yahoo.com>
@ 2004-08-24 05:08   ` my ho <mthoatbanjo@yahoo.com>
  2004-08-24 12:00     ` Re: postgresql performance with multimedia Jan Wieck <JanWieck@Yahoo.com>
  0 siblings, 1 reply; 8+ messages in thread

From: my ho @ 2004-08-24 05:08 UTC (permalink / raw)
  To: Jan Wieck <JanWieck@Yahoo.com>; my thi ho <mthoatbanjo@Yahoo.com>; +Cc: pgsql-performance


--- Jan Wieck <JanWieck@Yahoo.com> wrote:

> On 8/17/2004 8:44 PM, my thi ho wrote:
> 
> > Hi,
> > I am working on a project which explore postgresql
> to
> > store multimedia data.
> > In details,  i am trying to work with the buffer
> > management part of postgres source code. And try
> to
> > improve the performance. I had search on the web
> but
> > could not find much usefull information. 
> 
> What version of PostgreSQL are you looking at? Note
> that the buffer 
> cache replacement strategy was completely changed
> for version 8.0, which 
> is currently in BETA test. A description of the
> algorithm can be found 
> in the README file in src/backend/storage/bufmgr.

oki, Thanks for the information. I have a look at 8.0
beta, but cannot start the statistic collector. (I had
post this err message before for help, but havent
really got any clue to fix it)
> LOG:  could not create IPv6 socket: Address family
not
> supported by protocol
> LOG:  could not bind socket for statistics
collector:
> Cannot assign requested address
> LOG:  disabling statistics collector for lack of
> working socket

btw, what i want to ask here is does postgreSQL have
any kind of read-ahead buffer implemented? 'cos it
would be useful in multimedia case when we always scan
the large table for continous data.
Thanks 
Ho




	
		
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: postgresql performance with multimedia
  2004-08-18 00:44 postgresql performance with multimedia my thi ho <mthoatbanjo@yahoo.com>
  2004-08-18 13:10 ` Re: postgresql performance with multimedia Jan Wieck <JanWieck@Yahoo.com>
  2004-08-24 05:08   ` Re: postgresql performance with multimedia my ho <mthoatbanjo@yahoo.com>
@ 2004-08-24 12:00     ` Jan Wieck <JanWieck@Yahoo.com>
  2004-08-25 06:54       ` Re: postgresql performance with multimedia my ho <mthoatbanjo@yahoo.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Jan Wieck @ 2004-08-24 12:00 UTC (permalink / raw)
  To: my ho <mthoatbanjo@Yahoo.com>; +Cc: pgsql-performance

On 8/24/2004 1:08 AM, my ho wrote:

> --- Jan Wieck <JanWieck@Yahoo.com> wrote:
> 
>> On 8/17/2004 8:44 PM, my thi ho wrote:
>> 
>> > Hi,
>> > I am working on a project which explore postgresql
>> to
>> > store multimedia data.
>> > In details,  i am trying to work with the buffer
>> > management part of postgres source code. And try
>> to
>> > improve the performance. I had search on the web
>> but
>> > could not find much usefull information. 
>> 
>> What version of PostgreSQL are you looking at? Note
>> that the buffer 
>> cache replacement strategy was completely changed
>> for version 8.0, which 
>> is currently in BETA test. A description of the
>> algorithm can be found 
>> in the README file in src/backend/storage/bufmgr.
> 
> oki, Thanks for the information. I have a look at 8.0
> beta, but cannot start the statistic collector. (I had
> post this err message before for help, but havent
> really got any clue to fix it)
>> LOG:  could not create IPv6 socket: Address family
> not
>> supported by protocol
>> LOG:  could not bind socket for statistics
> collector:
>> Cannot assign requested address
>> LOG:  disabling statistics collector for lack of
>> working socket

Tom Lane answered to that question. The code in question does resolve 
"localhost" with getaddrinfo() and then tries to create and bind a UDP 
socket to all returned addresses. For some reason "localhost" on your 
system resolves to an address that is not available for bind(2).

> 
> btw, what i want to ask here is does postgreSQL have
> any kind of read-ahead buffer implemented? 'cos it
> would be useful in multimedia case when we always scan
> the large table for continous data.

Since there is no mechanism to control that data is stored contiguously 
in the tables, what would that be good for?


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: postgresql performance with multimedia
  2004-08-18 00:44 postgresql performance with multimedia my thi ho <mthoatbanjo@yahoo.com>
  2004-08-18 13:10 ` Re: postgresql performance with multimedia Jan Wieck <JanWieck@Yahoo.com>
  2004-08-24 05:08   ` Re: postgresql performance with multimedia my ho <mthoatbanjo@yahoo.com>
  2004-08-24 12:00     ` Re: postgresql performance with multimedia Jan Wieck <JanWieck@Yahoo.com>
@ 2004-08-25 06:54       ` my ho <mthoatbanjo@yahoo.com>
  2004-08-25 11:03         ` Re: postgresql performance with multimedia Jan Wieck <JanWieck@Yahoo.com>
  0 siblings, 1 reply; 8+ messages in thread

From: my ho @ 2004-08-25 06:54 UTC (permalink / raw)
  To: pgsql-performance; +Cc: Jan Wieck <JanWieck@Yahoo.com>

> Tom Lane answered to that question. The code in
> question does resolve 
> "localhost" with getaddrinfo() and then tries to
> create and bind a UDP 
> socket to all returned addresses. For some reason
> "localhost" on your 
> system resolves to an address that is not available
> for bind(2).

I tried to put my_ip instead of "localhost" in
bufmng.c and it seems to work (no more complaining).
However i check the pg_statio_all_tables and dont see
any recorded statistic at all. (all the columns are
'0')
some time postmaster shut down with this err msg: 
LOG:  statistics collector process (<process_id>)
exited with exit code 1
i starts postmaster with this command:
postmaster -i -p $PORT -D $PGDATA -k $PGDATA -N 32 -B
64 -o -s

> > btw, what i want to ask here is does postgreSQL
> have
> > any kind of read-ahead buffer implemented? 'cos it
> > would be useful in multimedia case when we always
> scan
> > the large table for continous data.
> 
> Since there is no mechanism to control that data is
> stored contiguously 
> in the tables, what would that be good for?

i thought that rows in the table will be stored
contiguously? in that case, if the user is requesting
1 row, we make sure that the continue rows are ready
in the buffer pool so that when they next requested,
they wont be asked to read from disk. For multimedia
data, this is important 'cos data needs to be
presented continuously without any waiting.

thanks again for your help
MT Ho



__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: postgresql performance with multimedia
  2004-08-18 00:44 postgresql performance with multimedia my thi ho <mthoatbanjo@yahoo.com>
  2004-08-18 13:10 ` Re: postgresql performance with multimedia Jan Wieck <JanWieck@Yahoo.com>
  2004-08-24 05:08   ` Re: postgresql performance with multimedia my ho <mthoatbanjo@yahoo.com>
  2004-08-24 12:00     ` Re: postgresql performance with multimedia Jan Wieck <JanWieck@Yahoo.com>
  2004-08-25 06:54       ` Re: postgresql performance with multimedia my ho <mthoatbanjo@yahoo.com>
@ 2004-08-25 11:03         ` Jan Wieck <JanWieck@Yahoo.com>
  2004-08-27 06:27           ` Re: postgresql performance with multimedia my ho <mthoatbanjo@yahoo.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Jan Wieck @ 2004-08-25 11:03 UTC (permalink / raw)
  To: my ho <mthoatbanjo@Yahoo.com>; +Cc: pgsql-performance

On 8/25/2004 2:54 AM, my ho wrote:
>> Tom Lane answered to that question. The code in
>> question does resolve 
>> "localhost" with getaddrinfo() and then tries to
>> create and bind a UDP 
>> socket to all returned addresses. For some reason
>> "localhost" on your 
>> system resolves to an address that is not available
>> for bind(2).
> 
> I tried to put my_ip instead of "localhost" in
> bufmng.c and it seems to work (no more complaining).
> However i check the pg_statio_all_tables and dont see
> any recorded statistic at all. (all the columns are
> '0')

The block level statistics are disabled by default in the 
postgresql.conf file.

> some time postmaster shut down with this err msg: 
> LOG:  statistics collector process (<process_id>)
> exited with exit code 1

Fix your operating systems network settings instead of curing the 
symptoms by breaking PostgreSQL.

> i starts postmaster with this command:
> postmaster -i -p $PORT -D $PGDATA -k $PGDATA -N 32 -B
> 64 -o -s
> 
>> > btw, what i want to ask here is does postgreSQL
>> have
>> > any kind of read-ahead buffer implemented? 'cos it
>> > would be useful in multimedia case when we always
>> scan
>> > the large table for continous data.
>> 
>> Since there is no mechanism to control that data is
>> stored contiguously 
>> in the tables, what would that be good for?
> 
> i thought that rows in the table will be stored
> contiguously? in that case, if the user is requesting
> 1 row, we make sure that the continue rows are ready
> in the buffer pool so that when they next requested,
> they wont be asked to read from disk. For multimedia
> data, this is important 'cos data needs to be
> presented continuously without any waiting.

They are only stored in that way on initial load and if the load is done 
with a single process. And don't you rely on this for the future. Right 
now, if you ever update or delete tuples, that order changes already.

Also keep in mind that large values are not stored inline, but in an 
extra "TOAST" relation.

For your "streaming" purposes I strongly recommend you do it in your 
application with the appropriate thread model. A relational database 
management system is not a multimedia cache.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: postgresql performance with multimedia
  2004-08-18 00:44 postgresql performance with multimedia my thi ho <mthoatbanjo@yahoo.com>
  2004-08-18 13:10 ` Re: postgresql performance with multimedia Jan Wieck <JanWieck@Yahoo.com>
  2004-08-24 05:08   ` Re: postgresql performance with multimedia my ho <mthoatbanjo@yahoo.com>
  2004-08-24 12:00     ` Re: postgresql performance with multimedia Jan Wieck <JanWieck@Yahoo.com>
  2004-08-25 06:54       ` Re: postgresql performance with multimedia my ho <mthoatbanjo@yahoo.com>
  2004-08-25 11:03         ` Re: postgresql performance with multimedia Jan Wieck <JanWieck@Yahoo.com>
@ 2004-08-27 06:27           ` my ho <mthoatbanjo@yahoo.com>
  0 siblings, 0 replies; 8+ messages in thread

From: my ho @ 2004-08-27 06:27 UTC (permalink / raw)
  To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: pgsql-performance

Hi,
> For your "streaming" purposes I strongly recommend
> you do it in your 
> application with the appropriate thread model. A
> relational database 
> management system is not a multimedia cache.

That's actually what i plan to do with postgreSQL,
maybe tailor it to suit with a multimedia streaming
database. Well, i could do it in the application level
but i think it's also worth a try with the database
itself.

> They are only stored in that way on initial load and
> if the load is done 
> with a single process. And don't you rely on this
> for the future. Right 
> now, if you ever update or delete tuples, that order
> changes already.

does the buffer manager have any idea what table that
buf belongs to? (can we add 'rel' variable to sbufdesc
in buf_internals.h and update it everytime we add new
entry to the buffer cahe?) And then we take in to
account which relation the data in the buffer belongs
to in the buf replacement algorithm or in the
read-ahead policy.

> Also keep in mind that large values are not stored
> inline, but in an 
> extra "TOAST" relation.
This is how i store my video file: break them in to
small chunks and store each part in a row of a table. 

regards,
MThi 



	
		
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: postgresql performance with multimedia
@ 2004-08-25 07:14 Gregory S. Williamson <gsw@globexplorer.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Gregory S. Williamson @ 2004-08-25 07:14 UTC (permalink / raw)
  To: my ho <mthoatbanjo@yahoo.com>; pgsql-performance; +Cc: Jan Wieck <JanWieck@Yahoo.com>


Not sure about the overall performance, etc. but I think that in order to collect statistics you need to set some values in the postgresql.conf config file, to wit:
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------

# - Statistics Monitoring -

#log_parser_stats = false
#log_planner_stats = false
#log_executor_stats = false
#log_statement_stats = false

# - Query/Index Statistics Collector -

stats_start_collector = true
stats_command_string = true
stats_block_level = true
stats_row_level = true
#stats_reset_on_server_start = true

If the appropriate values aren't set this could account for why no entries are found in the pg_stat tables. The manual has details on these; you'll need to reload postgres to make any changes effective.

Greg


-----Original Message-----
From:	my ho [mailto:mthoatbanjo@yahoo.com]
Sent:	Tue 8/24/2004 11:54 PM
To:	pgsql-performance@postgresql.org
Cc:	Jan Wieck
Subject:	Re: [PERFORM] postgresql performance with multimedia
> Tom Lane answered to that question. The code in
> question does resolve 
> "localhost" with getaddrinfo() and then tries to
> create and bind a UDP 
> socket to all returned addresses. For some reason
> "localhost" on your 
> system resolves to an address that is not available
> for bind(2).

I tried to put my_ip instead of "localhost" in
bufmng.c and it seems to work (no more complaining).
However i check the pg_statio_all_tables and dont see
any recorded statistic at all. (all the columns are
'0')
some time postmaster shut down with this err msg: 
LOG:  statistics collector process (<process_id>)
exited with exit code 1
i starts postmaster with this command:
postmaster -i -p $PORT -D $PGDATA -k $PGDATA -N 32 -B
64 -o -s

> > btw, what i want to ask here is does postgreSQL
> have
> > any kind of read-ahead buffer implemented? 'cos it
> > would be useful in multimedia case when we always
> scan
> > the large table for continous data.
> 
> Since there is no mechanism to control that data is
> stored contiguously 
> in the tables, what would that be good for?

i thought that rows in the table will be stored
contiguously? in that case, if the user is requesting
1 row, we make sure that the continue rows are ready
in the buffer pool so that when they next requested,
they wont be asked to read from disk. For multimedia
data, this is important 'cos data needs to be
presented continuously without any waiting.

thanks again for your help
MT Ho



__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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






^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2004-08-27 06:27 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-18 00:44 postgresql performance with multimedia my thi ho <mthoatbanjo@yahoo.com>
2004-08-18 13:10 ` Jan Wieck <JanWieck@Yahoo.com>
2004-08-24 05:08   ` my ho <mthoatbanjo@yahoo.com>
2004-08-24 12:00     ` Jan Wieck <JanWieck@Yahoo.com>
2004-08-25 06:54       ` my ho <mthoatbanjo@yahoo.com>
2004-08-25 11:03         ` Jan Wieck <JanWieck@Yahoo.com>
2004-08-27 06:27           ` my ho <mthoatbanjo@yahoo.com>
2004-08-25 07:14 Re: postgresql performance with multimedia Gregory S. Williamson <gsw@globexplorer.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox