Re: [GENERAL] Maximum size of database

[Jeff Davis]

On Wed, 2006-10-18 at 00:41 -0700, roopa perumalraja wrote:
> Hi
>  
> Thanks for your reply.
>  
> explain select * from tk_20060403;
> QUERY
> PLAN
> --
>  Seq Scan on tk_20060403  (cost=0.00..95561.30 rows=3609530 width=407)
> (1 row)
> 
> will this help?
>  

Is that a real query that your application is executing? Is that the
only table in your database? Why do you need all of that data at once?

Also send results of EXPLAIN ANALYZE.

Regards,
Jeff Davis


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

   http://archives.postgresql.org/

Re: [GENERAL] Maximum size of database

[Gregory S. Williamson]

Roopa,

I think that by defintion a "SELECT * FROM ;" will always take 
longer as the table increases in size (but if anyone who is more versed in 
theory of searches, sorts, etc. contradicts me I'll happily listen!). Note that 
the database could increase enormously with no direct effect on speed; but if 
one necessary table keeps growing in size that will effect speed.

Possible solutions are limited, since such a query *has* to check every row to 
see if it is live and retrieve the data.

Might be helped by:
 (a) reducing the core table to a minimum of columns, with esp. large ones 
placed in an another table where you will only get them when you need them -- 
reduces total amount of data being moved, but may be minimal if you still have 
lots of data [but if can reduce each row from say 1k bytes to 200 bytes, as 
long as you don't always need the data in the new table, you gain some in 
efficiency]. Heavily dependant on use of the data and the time you have to 
de-normalize the table.

 (b) more RAM -- if everything is in RAM and the server does not have to hit 
disk it's faster, but is obviously limited by $ and capacity. Our runtime 
servers have 4 gigs each and I'm worried about needing 8 sooner rather than 
later (we have lots of connections as well as lots of data).

 (c) faster/different disks -- RAIDED with battery backed cache as others have 
suggested. We have had poor experience with some Dell disks (I don't recall 
which off hand) as database servers -- I've seen similar posts by other users, 
probably in the performance list archives.

 (d) migrate to *nix (yeah, I know, probably not possible, but Windows has 
issues, and the postgres port is relatively newer on that platform) -- untested 
assumption which may warrant a flame, but I think in general the *Nix OS 
versions may have better disk I/O.

Greg W.


-Original Message-
From:   [EMAIL PROTECTED] on behalf of roopa perumalraja
Sent:   Wed 10/18/2006 12:41 AM
To: pgsql-general@postgresql.org
Cc: Michael Fuhr; louis gonzales
Subject:    Re: [GENERAL] Maximum size of database

Hi
   
  Thanks for your reply.
   
  explain select * from tk_20060403;
QUERY PLAN
--
 Seq Scan on tk_20060403  (cost=0.00..95561.30 rows=3609530 width=407)
(1 row)

  will this help?
   
  louis gonzales <[EMAIL PROTECTED]> wrote:
  also, run
EXPLAIN
on any command, show the results of this. In particular, if you have 
some commands that are taking 'even longer?'



roopa perumalraja wrote:

> Thanks for your reply.
> 
> I have answered your questions below.
> 
> 1 & 2) System: Microsoft Windows XP Professional
> Version 2002
> Computer: Intel Pentium CPU 3.40GHz, 960MB of RAM
> 
> 3) shared_buffers = 2
> autovaccum = on
> 
> 4) Yes, I am vacuuming & analyzing the database once every day.
> 
> 5) No concurrent activities, means I run one command at a time.
> 
> 6) Nothing else running on the box other than Postgres.
> 
> I hope these answers will try to solve my problem. Thanks again.
> 
> Roopa
> */Michael Fuhr /* wrote:
>
> On Tue, Oct 17, 2006 at 07:26:25PM -0700, roopa perumalraja wrote:
> > I would like to know that what can be the maximum size of
> database in
> > postgres 8.1.4.
>
> http://www.postgresql.org/docs/faqs.FAQ.html#item4.4
>
> > Currently my database size is 37GB & its pretty slow. I wonder if
> > its b'cos of huge amount of data in it.
>
> 37GB isn't all that huge; as the FAQ mentions, much larger databases
> exist. Without more information we'll have to ask some of the
> standard questions:
>
> What's your hardware configuration?
> What operating system and version are you using?
> What are your non-default postgresql.conf settings?
> Are you vacuuming and analyzing the database regularly?
> How much concurrent activity do you have?
> Does anything other than PostgreSQL run on the box?
>
> If you have a specific query that's slow then please post the EXPLAIN
> ANALYZE output. Also, you might get more help on the pgsql-performance
> list.
>
> -- 
> Michael Fuhr
>
>
>
>
> signature
>
> 
> Get your own web address for just $1.99/1st yr 
> 

-
Do you Yahoo!?
 Everyone is raving about the  all-new Yahoo! Mail.


---
Click link below if it is SPAM [EMAIL PROTECTED]
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4535d8c8103071076418835&[EMAIL
 PROTECTED]&retrain=spam&template=history&history_page=1"
!DSPAM:4535d8c8103071076418835!
---




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

   http://archives.postgresql.org/

Re: [GENERAL] Maximum size of database

[Matthias . Pitzl]

Title: Nachricht



What 
disk subsystem do you have? Single disks? Raid? Raid with battery buffered write 
cache?
Last 
one can improve your performance massively.
 
-- 
Matthias

  
  -Original Message-From: 
  [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
  On Behalf Of roopa perumalrajaSent: Wednesday, October 18, 
  2006 8:10 AMTo: pgsql-general@postgresql.orgCc: Michael 
  FuhrSubject: Re: [GENERAL] Maximum size of 
  database
  Thanks for your reply.
   
  I have answered your questions below. 
   
  1 & 2) System: Microsoft Windows XP 
  Professional  Version 
  2002
    Computer: Intel 
  Pentium CPU 3.40GHz, 960MB of RAM
   
  3) shared_buffers = 2    autovaccum = on
   
  4) Yes, I am vacuuming & analyzing the database once every day.
   
  5) No concurrent activities, means I run one command at a time.
   
  6) Nothing else running on the box other than Postgres.
   
  I hope these answers will try to solve my problem. Thanks again.
   
  Roopa

Re: [GENERAL] Maximum size of database

[louis gonzales]

explain analyze verbose select * from tk_  ;

roopa perumalraja wrote:


Hi
 
Thanks for your reply.
 
explain select * from tk_20060403;
QUERY PLAN   
--

 Seq Scan on tk_20060403  (cost=0.00..95561.30 rows=3609530 width=407)
(1 row)
will this help?
 
*/louis gonzales <[EMAIL PROTECTED]>/* wrote:


also, run
EXPLAIN
on any command, show the results of this. In particular, if you have
some commands that are taking 'even longer?'



roopa perumalraja wrote:

> Thanks for your reply.
>
> I have answered your questions below.
>
> 1 & 2) System: Microsoft Windows XP Professional
> Version 2002
> Computer: Intel Pentium CPU 3.40GHz, 960MB of RAM
>
> 3) shared_buffers = 2
> autovaccum = on
>
> 4) Yes, I am vacuuming & analyzing the database once every day.
>
> 5) No concurrent activities, means I run one command at a time.
>
> 6) Nothing else running on the box other than Postgres.
>
> I hope these answers will try to solve my problem. Thanks again.
>
> Roopa
> */Michael Fuhr /* wrote:
>
> On Tue, Oct 17, 2006 at 07:26:25PM -0700, roopa perumalraja wrote:
> > I would like to know that what can be the maximum size of
> database in
> > postgres 8.1.4.
>
> http://www.postgresql.org/docs/faqs.FAQ.html#item4.4
>
> > Currently my database size is 37GB & its pretty slow. I wonder if
> > its b'cos of huge amount of data in it.
>
> 37GB isn't all that huge; as the FAQ mentions, much larger databases
> exist. Without more information we'll have to ask some of the
> standard questions:
>
> What's your hardware configuration?
> What operating system and version are you using?
> What are your non-default postgresql.conf settings?
> Are you vacuuming and analyzing the database regularly?
> How much concurrent activity do you have?
> Does anything other than PostgreSQL run on the box?
>
> If you have a specific query that's slow then please post the
EXPLAIN
> ANALYZE output. Also, you might get more help on the
pgsql-performance
> list.
>
> --
> Michael Fuhr
>
>
>
>
> signature
>
>

> Get your own web address for just $1.99/1st yr
> 



Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail. 
 





--
Email:[EMAIL PROTECTED]
WebSite:  http://www.linuxlouis.net
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka


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

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

Re: [GENERAL] Maximum size of database

[roopa perumalraja]

Hi     Thanks for your reply.     explain select * from tk_20060403;    QUERY PLAN    -- Seq Scan on tk_20060403  (cost=0.00..95561.30 rows=3609530 width=407)(1 row)  will this help?     louis gonzales <[EMAIL PROTECTED]> wrote:  also, runEXPLAINon any command, show the results of
 this. In particular, if you have some commands that are taking 'even longer?'roopa perumalraja wrote:> Thanks for your reply.> > I have answered your questions below.> > 1 & 2) System: Microsoft Windows XP Professional> Version 2002> Computer: Intel Pentium CPU 3.40GHz, 960MB of RAM> > 3) shared_buffers = 2> autovaccum = on> > 4) Yes, I am vacuuming & analyzing the database once every day.> > 5) No concurrent activities, means I run one command at a time.> > 6) Nothing else running on the box other than Postgres.> > I hope these answers will try to solve my problem. Thanks again.> > Roopa> */Michael Fuhr <[EMAIL PROTECTED]>/* wrote:>> On Tue, Oct 17, 2006 at 07:26:25PM -0700, roopa perumalraja wrote:> > I would like to know that what can be the maximum size of>
 database in> > postgres 8.1.4.>> http://www.postgresql.org/docs/faqs.FAQ.html#item4.4>> > Currently my database size is 37GB & its pretty slow. I wonder if> > its b'cos of huge amount of data in it.>> 37GB isn't all that huge; as the FAQ mentions, much larger databases> exist. Without more information we'll have to ask some of the> standard questions:>> What's your hardware configuration?> What operating system and version are you using?> What are your non-default postgresql.conf settings?> Are you vacuuming and analyzing the database regularly?> How much concurrent activity do you have?> Does anything other than PostgreSQL run on the box?>> If you have a specific query that's slow then please post the EXPLAIN> ANALYZE output. Also, you might get more help on the pgsql-performance> list.>> -- >
 Michael Fuhr> signature>> > Get your own web address for just $1.99/1st yr >  
		Do you Yahoo!? Everyone is raving about the  all-new Yahoo! Mail.

Re: [GENERAL] Maximum size of database

[louis gonzales]

also, run
EXPLAIN
on any command, show the results of this.  In particular, if you have 
some commands that are taking 'even longer?'




roopa perumalraja wrote:


Thanks for your reply.
 
I have answered your questions below.
 
1 & 2) System: Microsoft Windows XP Professional

  Version 2002
  Computer: Intel Pentium CPU 3.40GHz, 960MB of RAM
 
3) shared_buffers = 2

autovaccum = on
 
4) Yes, I am vacuuming & analyzing the database once every day.
 
5) No concurrent activities, means I run one command at a time.
 
6) Nothing else running on the box other than Postgres.
 
I hope these answers will try to solve my problem. Thanks again.
 
Roopa

*/Michael Fuhr <[EMAIL PROTECTED]>/* wrote:

On Tue, Oct 17, 2006 at 07:26:25PM -0700, roopa perumalraja wrote:
> I would like to know that what can be the maximum size of
database in
> postgres 8.1.4.

http://www.postgresql.org/docs/faqs.FAQ.html#item4.4

> Currently my database size is 37GB & its pretty slow. I wonder if
> its b'cos of huge amount of data in it.

37GB isn't all that huge; as the FAQ mentions, much larger databases
exist. Without more information we'll have to ask some of the
standard questions:

What's your hardware configuration?
What operating system and version are you using?
What are your non-default postgresql.conf settings?
Are you vacuuming and analyzing the database regularly?
How much concurrent activity do you have?
Does anything other than PostgreSQL run on the box?

If you have a specific query that's slow then please post the EXPLAIN
ANALYZE output. Also, you might get more help on the pgsql-performance
list.

-- 
Michael Fuhr





signature


Get your own web address for just $1.99/1st yr 
<%20http://us.rd.yahoo.com/evt=43290/*http://smallbusiness.yahoo.com/domains>. 
We'll help. Yahoo! Small Business 
. 




--
Email:[EMAIL PROTECTED]
WebSite:  http://www.linuxlouis.net
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka


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

Re: [GENERAL] Maximum size of database

[roopa perumalraja]

Thanks for your reply.     I have answered your questions below.      1 & 2) System: Microsoft Windows XP Professional  Version 2002    Computer: Intel Pentium CPU 3.40GHz, 960MB of RAM     3) shared_buffers = 2    autovaccum = on     4) Yes, I am vacuuming & analyzing the database once every day.     5) No concurrent activities, means I run one command at a time.     6) Nothing else running on the box other than Postgres.     I hope these answers will try to solve my problem. Thanks again.     Roopa  Michael Fuhr <[EMAIL PROTECTED]> wrote:  On Tue, Oct 17, 2006 at 07:26:25PM -0700, roopa perumalraja wrote:> I would like to know that what can be the maximum size of database in> postgres 8.1.4.http://www.postgresql.org/docs/faqs.FAQ.html#item4.4> Currently my database size is 37GB & its pretty slow. I wonder if> its b'cos of huge amount of data in it.37GB isn't all that huge; as the FAQ mentions, much larger databasesexist. Without more information we'll have to ask some of thestandard questions:What's your hardware configuration?What operating system and version are you using?What are your non-default postgresql.conf settings?Are you vacuuming and analyzing the database regularly?How much concurrent activity do you have?Does anything other than PostgreSQL run on the box?If you have a specific query that's slow then please post the
 EXPLAINANALYZE output. Also, you might get more help on the pgsql-performancelist.-- Michael Fuhrsignature 
	

	
		Get your own web address for just $1.99/1st yr. We'll help. Yahoo! Small Business.

Re: [GENERAL] Maximum size of database

[Edward Macnaghten]

roopa perumalraja wrote

 
I would like to know that what can be the maximum size of database in 
postgres 8.1.4. Currently my database size is 37GB & its pretty slow. 
I wonder if its b'cos of huge amount of data in it.
 



http://www.postgresql.org/docs/faqs.FAQ.html#item4.4

Slowness reason cannot be really investigated without more information 
of the specifics, but could well be indexing issues.


Eddy

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

Re: [GENERAL] Maximum size of database

[Michael Fuhr]

On Tue, Oct 17, 2006 at 07:26:25PM -0700, roopa perumalraja wrote:
> I would like to know that what can be the maximum size of database in
> postgres 8.1.4.

http://www.postgresql.org/docs/faqs.FAQ.html#item4.4

> Currently my database size is 37GB & its pretty slow.  I wonder if
> its b'cos of huge amount of data in it.

37GB isn't all that huge; as the FAQ mentions, much larger databases
exist.  Without more information we'll have to ask some of the
standard questions:

What's your hardware configuration?
What operating system and version are you using?
What are your non-default postgresql.conf settings?
Are you vacuuming and analyzing the database regularly?
How much concurrent activity do you have?
Does anything other than PostgreSQL run on the box?

If you have a specific query that's slow then please post the EXPLAIN
ANALYZE output.  Also, you might get more help on the pgsql-performance
list.

-- 
Michael Fuhr

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