Обсуждение: Maximum size of database

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

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

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

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 = 20000
>     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 <mike@fuhr.org>/* 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
> <http://us.rd.yahoo.com/evt=41244/*http://smallbusiness.yahoo.com/>.



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

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 = 20000
> 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
>

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 <gonzales@linuxlouis.net>/* 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 = 20000
>     > 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.
> <http://us.rd.yahoo.com/evt=42297/*http://advision.webevents.yahoo.com/mailbeta>




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

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of roopa perumalraja
Sent: Wednesday, October 18, 2006 8:10 AM
To: pgsql-general@postgresql.org
Cc: Michael Fuhr
Subject: 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 = 20000
    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

Roopa,

I think that by defintion a "SELECT * FROM <sometable>;" will always take longer as the table increases in size (but if
anyonewho is more versed in theory of searches, sorts, etc. contradicts me I'll happily listen!). Note that the
databasecould increase enormously with no direct effect on speed; but if one necessary table keeps growing in size that
willeffect 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
onlyget them when you need them -- reduces total amount of data being moved, but may be minimal if you still have lots
ofdata [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
havelots 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
withsome Dell disks (I don't recall which off hand) as database servers -- I've seen similar posts by other users,
probablyin the performance list archives. 

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

Greg W.


-----Original Message-----
From:    pgsql-general-owner@postgresql.org 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 <gonzales@linuxlouis.net> 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 = 20000
> 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 gsw@globexplorer.com

"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4535d8c8103071076418835&user=gsw@globexplorer.com&retrain=spam&template=history&history_page=1"
!DSPAM:4535d8c8103071076418835!
-------------------------------------------------------

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