Thread: Postgres & large tables on average machine
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
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
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
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
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
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
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
> 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
> 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
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