Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgres problems with large databases.

Postgres problems with large databases.

From
Dan Moschuk
Date:
Greetings,

I'm encounting some strange problems trying to use postgres on a table
with a fairly substantial amount of records (~1 million).

The problem comes when you try and select a record from the database.
Postmaster in debug mode complains whole heartedly about resource errors,
and after that, everything goes downhill (everything from postmaster crashes
to index corruption).  This occurs both with 6.5.3 and 7.0.2.

Any pointers/ideas?

-Dan
--
Man is a rational animal who always loses his temper when he is called
upon to act in accordance with the dictates of reason.
                -- Oscar Wilde

Re: Postgres problems with large databases.

From
Alfred Perlstein
Date:
* Dan Moschuk <dan@freebsd.org> [000810 14:55] wrote:
>
> Greetings,
>
> I'm encounting some strange problems trying to use postgres on a table
> with a fairly substantial amount of records (~1 million).
>
> The problem comes when you try and select a record from the database.
> Postmaster in debug mode complains whole heartedly about resource errors,
> and after that, everything goes downhill (everything from postmaster crashes
> to index corruption).  This occurs both with 6.5.3 and 7.0.2.
>
> Any pointers/ideas?

First off I'm sure the developers would like to know more than:
  "complains whole heartedly about resource errors"
can you name the errors specifically?

Second, when was the last time to vacuumed the table?  what about
vacuum analyze?

Third, do you have nice indexes on the tables to help the search?

What does explain say about your query?

Have you bumped up the size of the shared memory segment for
postgresql?

-Alfred

Re: Postgres problems with large databases.

From
Tom Lane
Date:
Alfred Perlstein <bright@wintelcom.net> writes:
> * Dan Moschuk <dan@freebsd.org> [000810 14:55] wrote:
>> I'm encounting some strange problems trying to use postgres on a table
>> with a fairly substantial amount of records (~1 million).

> First off I'm sure the developers would like to know more than:
>   "complains whole heartedly about resource errors"
> can you name the errors specifically?

Indeed.  1 million records is by no means an impressive amount ---
lots of people are using PG with multi-gigabyte databases.
We need more details about your database setup and the exact errors
you are seeing (as well as the exact queries that produce 'em).

            regards, tom lane

Re: Postgres problems with large databases.

From
Dan Moschuk
Date:
| > I'm encounting some strange problems trying to use postgres on a table
| > with a fairly substantial amount of records (~1 million).
| >
| > The problem comes when you try and select a record from the database.
| > Postmaster in debug mode complains whole heartedly about resource errors,
| > and after that, everything goes downhill (everything from postmaster crashes
| > to index corruption).  This occurs both with 6.5.3 and 7.0.2.
| >
| > Any pointers/ideas?
|
| First off I'm sure the developers would like to know more than:
|   "complains whole heartedly about resource errors"
| can you name the errors specifically?

Sorry, I'm reporting this second hand.

The resource errors are massive, and usually "Out of Swap" messages from
the query executed, which is just a simple..

SELECT url from table where com='n' limit 1;

| Second, when was the last time to vacuumed the table?  what about
| vacuum analyze?

The database is vacuumed every six hours.

| Third, do you have nice indexes on the tables to help the search?

Of course.

Below illustrates the issue of corruption..

BT=# select * from murl where com='y' limit 1;
<<THIS WOULD LOCATE THE RECORD>>
            url            | com | ban | qcon | words |          time
---------------------------+-----+-----+------+-------+------------------------
 http://www.altavista.com/ | y   |     |      |       | 2000-08-10 21:19:05-04
(1 row)

BT=# update murl set com='n' where url='http://www.altavista.com/';
<<THIS WOULD UPDATE 0>>

BT=# update murl set com='n' where url like 'http://www.altavista.com/';
<<THIS WOULD ALSO UPDATE 0>>

BT=# select * from murl where url='http://www.altavista.com/';
<<THIS WOULD RETURN>>
 url | com | ban | qcon | words | time
-----+-----+-----+------+-------+------
(0 rows)


Cheers,
Dan
--
Man is a rational animal who always loses his temper when he is called
upon to act in accordance with the dictates of reason.
                -- Oscar Wilde