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

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. We'll help. Yahoo! Small Business.

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&gt;.
We'll help. Yahoo! Small Business
<http://us.rd.yahoo.com/evt=41244/*http://smallbusiness.yahoo.com/&gt;.

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

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.

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&gt;

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

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: 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 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: 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&amp;user=gsw@globexplorer.com&amp;retrain=spam&amp;template=history&amp;history_page=1&quot;
!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