Thread: Postgres & large tables on average machine

Postgres & large tables on average machine

From
"Nicholay P. Chuprynin"
Date:
Hello, All!
Resently I had to create and manage the (relatively) large table.
In the mean time it's about 8 million rows, and surely will grow above
this size.
The problem is that queries takes absolutely not acceptable time.
Database located on average Celeron 400 machine with 128 Mb of RAM and
UDMA 33 capable IDE drive.
I run PostgreSQL 7.1 on Debian Linux with 2.4.18 kernel.
My question is what could be done in order to improve the performance?
I mean, is that normal behavior for Postgres on such computer or I
encounter a misconfiguration?

Nicholay


Re: Postgres & large tables on average machine

From
Peter Eisentraut
Date:
Nicholay P. Chuprynin writes:

> Resently I had to create and manage the (relatively) large table.
> In the mean time it's about 8 million rows, and surely will grow above
> this size.
> The problem is that queries takes absolutely not acceptable time.
> Database located on average Celeron 400 machine with 128 Mb of RAM and
> UDMA 33 capable IDE drive.
> I run PostgreSQL 7.1 on Debian Linux with 2.4.18 kernel.
> My question is what could be done in order to improve the performance?
> I mean, is that normal behavior for Postgres on such computer or I
> encounter a misconfiguration?

It is perfectly normal for queries to take an absolutely unacceptable
amount of time, but without providing any sort of detail about the
involved schema and data it's impossible to figure out if the performance
could be improved.

--
Peter Eisentraut   peter_e@gmx.net


Re: Postgres & large tables on average machine

From
"Fred Moyer"
Date:
Your UDMA 33 bus will limit disk reads to 33 Mbytes/sec so there is your
first bottleneck.  Get a  66 mhz PCI ide adapter (Promise is cheap) and that
will increase your disk speed dramatically.
Also you won't be able to do much with 128 Mb of ram, put in as much as you
can.  That box is likely 66 mhz front side bus so that will be a bottleneck
once you max out the ram.

If you are planning on that table growing you will need a bigger box with
scsi/raid and lots of ram.  Biggest factor when the table gets above the
available ram though is how fast you can pipe data though the processor from
the disk.  Get something like a dual athlon motherboard that is a big front
side bus and use a 64 bit, 66 mhz scsi raid controller.

On the other hand, if you don't want to go big then at least get an PCI IDE
controller and put another 384M ram in - it will make a big difference.
Don't forget to (man) hdparm to maximize your I/O.

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Peter Eisentraut
Sent: Friday, March 29, 2002 10:43 AM
To: Nicholay P. Chuprynin
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgres & large tables on average machine


Nicholay P. Chuprynin writes:

> Resently I had to create and manage the (relatively) large table.
> In the mean time it's about 8 million rows, and surely will grow above
> this size.
> The problem is that queries takes absolutely not acceptable time.
> Database located on average Celeron 400 machine with 128 Mb of RAM and
> UDMA 33 capable IDE drive.
> I run PostgreSQL 7.1 on Debian Linux with 2.4.18 kernel.
> My question is what could be done in order to improve the performance?
> I mean, is that normal behavior for Postgres on such computer or I
> encounter a misconfiguration?

It is perfectly normal for queries to take an absolutely unacceptable
amount of time, but without providing any sort of detail about the
involved schema and data it's impossible to figure out if the performance
could be improved.

--
Peter Eisentraut   peter_e@gmx.net


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

http://archives.postgresql.org


Re: Postgres & large tables on average machine

From
Jeremy Buchmann
Date:
Fred Moyer wrote:
> Your UDMA 33 bus will limit disk reads to 33 Mbytes/sec so there is your
> first bottleneck.  Get a  66 mhz PCI ide adapter (Promise is cheap) and that
> will increase your disk speed dramatically.
> Also you won't be able to do much with 128 Mb of ram, put in as much as you
> can.  That box is likely 66 mhz front side bus so that will be a bottleneck
> once you max out the ram.
 >
[snip]
>
>>Resently I had to create and manage the (relatively) large table.
>>In the mean time it's about 8 million rows, and surely will grow above
>>this size.
>>The problem is that queries takes absolutely not acceptable time.
>>Database located on average Celeron 400 machine with 128 Mb of RAM and
>>UDMA 33 capable IDE drive.
>>I run PostgreSQL 7.1 on Debian Linux with 2.4.18 kernel.
>>My question is what could be done in order to improve the performance?
>>I mean, is that normal behavior for Postgres on such computer or I
>>encounter a misconfiguration?

Also, the Celeron processor is cache-starved.  If you can switch it out
for a P3 at even the same clock speed, it'd be worth it.  A while back,
someone posted benchmarks where he just changed the processor from a
Celeron to a P3 of the same clock speed and the P3 was twice as fast.

Databases are I/O bound.  Anything you can stuff into cache is worth it.
This is why lots of memory, big disk caches, disk controller caches, and
processor caches help so much.

--Jeremy


Re: Postgres & large tables on average machine

From
"Nicholay P. Chuprynin"
Date:
Thanks a lot!

Although I wasn't clear enough in my questions I got pretty informative
answers.
Now I see, that I need faster hardware or to rethink the whole problem,
which is somewhat cheaper and much more interesting.
Thanks again for your answers.

Nicholay



Troubles Starting Postgres

From
Derek Neighbors
Date:
Been running Postgres on many platforms for sometime and never had much issue starting it.  I recently installed on a
machineand get the following when trying to start it.  I searched the lists and google and didnt find much useful
information.

PGSTAT: bind(2): Cannot assign requested address
Failed to start the postmaster

aegagrus:/home/dneighbo# less /etc/host.conf
order hosts,bind
multi on

aegagrus:/home/dneighbo# less /etc/hosts
127.0.0.1       aegagrus        localhost

I am not sure why its addressing bind....  Any help greatly appreciated.

-derek

Re: Troubles Starting Postgres

From
Tom Lane
Date:
Derek Neighbors <derek@gnue.org> writes:
> PGSTAT: bind(2): Cannot assign requested address
> Failed to start the postmaster

PGSTAT tries to bind to 127.0.0.1 to create a loopback UDP path for
passing statistics data.  Not clear why this would fail unless you
have some network filtering software in there to prevent it.  Without
more info about your platform, network config, etc, there's little
more I can say...

            regards, tom lane

Re: Troubles Starting Postgres

From
Derek Neighbors
Date:
> PGSTAT tries to bind to 127.0.0.1 to create a loopback UDP path for
> passing statistics data.  Not clear why this would fail unless you
> have some network filtering software in there to prevent it.  Without
> more info about your platform, network config, etc, there's little
> more I can say...

Platform
--------
aegagrus:/home/dneighbo# less /proc/version
Linux version 2.4.17-k7 (herbert@gondolin) (gcc version 2.95.4 20011006 (Debian prerelease)) #2 Sat Dec 22 22:03:49 EST
2001

Network
--------
aegagrus:/home/dneighbo# ifconfig
eth0      Link encap:Ethernet  HWaddr 00:E0:06:F7:23:BD
          inet addr:192.168.100.40  Bcast:192.168.100.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:262314 errors:0 dropped:0 overruns:0 frame:0
          TX packets:295273 errors:0 dropped:0 overruns:0 carrier:0
          collisions:31208 txqueuelen:100
          RX bytes:282725926 (269.6 MiB)  TX bytes:29130298 (27.7 MiB)
          Interrupt:5 Base address:0xfe00

In prior mail posted my hosts file and hosts.conf

The configuration is I have an internal network 192.168.100.xx that this machine is on.  There is a server that has a
connectionto the internet and I use it also as a gateway for the internal network. 

To my knowledge I do not have not installed any fire wall on this machine, basically this is a stock Debian install
from'unstable'.  I will gladly give more information if you can tell me specifics you are looking for. 

-derek

Re: Troubles Starting Postgres

From
Derek Neighbors
Date:
> PGSTAT tries to bind to 127.0.0.1 to create a loopback UDP path for
> passing statistics data.  Not clear why this would fail unless you
> have some network filtering software in there to prevent it.  Without
> more info about your platform, network config, etc, there's little
> more I can say...

Bad luser no donut.

Seems when i configured this box in /etc/hosts I put

127.0.0.1    computername    localhost (instead of)
127.0.0.1    localhost    computername

Which made 'auto lo' fail in my interfaces file which during debugging got commented out.  Subsequently when the
machinewas rebooted a while back trouble shooting hardware issues lo never restarted.  Networking has been all well and
dandyto the net, but postgres actually wanted localhost.  So problem solved. 

aegagrus:/etc/network# ps ax |grep postgres
 8288 pts/0    S      0:00 /usr/lib/postgresql/bin/postmaster
 8289 pts/0    S      0:00 postgres: stats buffer process
 8292 pts/0    S      0:00 postgres: stats collector process
aegagrus:/etc/network#


Thank you very much for responding to what was a dumb question.

-Derek

Re: Postgres & large tables on average machine

From
"Nigel J. Andrews"
Date:
On Fri, 29 Mar 2002, Nicholay P. Chuprynin wrote:

>
> Hello, All!
> Resently I had to create and manage the (relatively) large table.
> In the mean time it's about 8 million rows, and surely will grow above
> this size.
> The problem is that queries takes absolutely not acceptable time.
> Database located on average Celeron 400 machine with 128 Mb of RAM and
> UDMA 33 capable IDE drive.
> I run PostgreSQL 7.1 on Debian Linux with 2.4.18 kernel.
> My question is what could be done in order to improve the performance?
> I mean, is that normal behavior for Postgres on such computer or I
> encounter a misconfiguration?
>
> Nicholay

It all depends. We'd need more information about the DB and the query before
being able to say anything. Have you tried EXPLAINing the query?

I have a 1 million row table in a DB hosted on a 333MHz Celeron, 96MB. A full
sequential scan takes something like 30 seconds (I think) whereas queries using
an index are sub second.


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants