Postgresql slowing down HELP

Anon

(What could be wrong with my system)
Good Afternoon,

I'm in the process of developing a personal database that stores stock transactions for analytical use.

Currently we have two almost identical servers running.

The server specs are:

RedHat 7.1 with kernel 2.4.7-2.9enterprise
(i've recompiled the kernel removing unnescary drivers)

The computer actually performed better with
my custom version of the RedHat kernel 2.2.14-5.0smp, and performed similar to
RedHat's Version of kernel-2.4.7-2.9smp

Postgresql 7.1.2. I've gone through phpbuilder.com and made some changes to buffer size, fsync, sort mem.

SuperMicro S2DG2 motherboard-Intel 440GX, 100MHz, dual PIII Xeon  550 MHZ w/512k cache

processors, 512MB PC100 ECC REGISTERED SDRAM

AIC-7896 dual channel Ultra II SCSI controller. 
Drives-EIDE-7200 rpm- 
Server 1 -27.2 GB
-75.3 GB (IMB DESKTAR)
Server 2 -10.0 GB
-18.0 GB
-27.2 GB
-73.3 GB 
ATA 100 controller (Currently, the controller and drive are not configured for ATA 100 transfer rates,
but we will be working on it soon.)

We have a programer in canda that has written a program using sql queries to sort through
every stock's (each stock has its own table) transaction and find the high and low and time of the high and low for each stock for each day. I refer to this as the high low program

Server1 has approximately 90 million records in the PostgreSQL database. Currently, approximately, 800,000
new records are added each trading day. Eventually, that number will increase to approximately 4,000,000 per
trading day

When we were running the HiLo program, we found that the number of records it was creating was almost the same as the number of records that were created when our Canadian friend ran the same program on a dual PIII 233 MHz server with 256 KB of RAM.

Also the first hour that it ran on our server in Houston and on our friend's server in Canada, approximately 7,500 records were created. By the 24th hour, the count was down
to approximately 2,000 per hour, and by the 48th hour, it was down to 1,175. By the 72nd hour, it was down to approximately 1,050.

My questions are:

1) Are these good numbers?
2) If not what could be slowing my system
down
3) Why does postgres get slower as the query
runs longer
4) What do i need to look at to speed things
up?

[deleted]

-1) your optimized version of the kernel is still SMP right? (just checking)

1) Hard to tell without knowing the queries and logic around them

2) For one thing, that 512MB memory is not nearly enough. Databases get most of their speed through caching pages in memory. At 512MB memory, it can hold approx 64Million pages, yet you are already using 90Million rows. More memory sounds like a good idea to me 🙂

3) I don't understand that question. A query runs as fast as the system will let it, and how long it takes depends on how much work it has to do and how much CPU power it gets.

4) - System load. Run TOP and see what it's doing. If your system is using lots of swapspace, add more memory. Databases love memory.
- The queries. Use EXPLAIN to see just what postgres needs to do to run each query. It may be that a some query is forced to do a sequencial scan (which is hell on 90M rows)

Another thing that may be usefull:
Just how much of the data do you really need?
If you are only reporting for each day or the last week, then it may speed things up quite a bit if you move the 'unused' rows to a different table, or even dump them to an archive file.

smarlowe

What do

cat /proc/sys/kernel/shmmax
cat /proc/sys/kernel/shmall

say? The default is pretty small for a database server (32 megs) and can be upped to about 50% to 75% of the total server memory.

Next, tune the following settings in your postgresql.conf file:

shared_buffers = 2000 # or higher, 32768 - 256 megs of ram, a lot. Note that managing a lot of shared buffers can slow down postgresql a lot.

Next, play with these lines:

#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.001
#cpu_operator_cost = 0.0025

By lowering index tuple cost and raising cpu operator cost, you can usually force postgresql to use index scans when it mistakenly seems to pick sequential scans a lot right now with the default settings. my machine at work flys on 1.5Million records joined against 350k records, and my settings are:

cpu_index_tuple_cost = 0.00001
cpu_operator_cost = 0.25

If none of those get you fast queries, you can change the following:

#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true

Generally turning off hashjoin and mergejoin will force postgreqsl to use nestloop, which is usually much faster than either of the others. Also, turning off seqscan will usually speed .

also, you can set these in a query, for examples, see:

http://www.postgresql.org/idocs/index.php?performance-tips.html

You may want to program a vacuumdb -z -a in a cron job to keep the database "clean". I can't wait until 7.2 or later gets rid of vacuuming forever.