Skip site navigation (1) Skip section navigation (2)

Variable (degrading) perfomance

From: Vladimir Stankovic <V(dot)Stankovic(at)city(dot)ac(dot)uk>
To: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Variable (degrading) perfomance
Date: 2007-06-11 13:04:49
Message-ID: 466D4871.2090707@city.ac.uk (view raw or flat)
Thread:
Lists: pgsql-performance
Hi all,

It seems that I have an issue with the performance of a PostgreSQL server.

I'm running write-intensive, TPC-C like tests. The workload consist of 
150 to 200 thousand transactions. The performance varies dramatically, 
between 5 and more than 9 hours (I don't have the exact figure for the 
longest experiment). Initially the server is relatively fast. It 
finishes the first batch of 50k transactions in an hour. This is 
probably due to the fact that the database is RAM-resident during this 
interval. As soon as the database grows bigger than the RAM the 
performance, not surprisingly, degrades, because of the slow disks.
My problem is that the performance is rather variable, and to me 
non-deterministic. A 150k test can finish in approx. 3h30mins but 
conversely  it can take more than 5h to complete.
Preferably I would like to see *steady-state* performance (where my 
interpretation of the steady-state is that the average 
throughput/response time does not change over time). Is the steady-state 
achievable despite the MVCC and the inherent non-determinism between 
experiments? What could be the reasons for the variable performance?
- misconfiguration of the PG parameters (e.g. autovacuum does not cope 
with the dead tuples on the MVCC architecture)
- file fragmentation
- index bloat
- ???
The initial size of the database (actually the output of the 'du -h' 
command) is ~ 400 MB. The size increases dramatically, somewhere between 
600MB and 1.1GB

I have doubted the client application at some point too. However, other 
server combinations using different DBMS exhibit steady state 
performance.As a matter of fact when PG is paired with Firebird, through 
statement-based replication middleware, the performance of the pair is 
steady too.

The hardware configuration:
Client machine
- 1.5 GHz CPU Pentium 4
- 1GB Rambus RAM
- Seagate st340810a IDE disk (40GB), 5400 rpms

Server machine
- 1.5 GHz CPU Pentium 4
- 640 MB Rambus RAM
- Seagate Barracuda 7200.9 rpms
- Seagate st340810a IDE disk (40GB) - the WAL is stored on an ext2 partition

The Software configuration:
The client application is a multi-threaded Java client running on Win 
2000 Pro sp4
The database server version is  8.1.5 running on Fedora Core 6.
Please find attached:
1 - the output of vmstat  taken after  the first  60k transactions were 
executed
2 - the postgresql.conf file

Any help would be appreciated.

Best regards,
Vladimir
-- 

Vladimir Stankovic                    	T: +44 20 7040 0273
Research Student/Research Assistant   	F: +44 20 7040 8585
Centre for Software Reliability       	E: V(dot)Stankovic(at)city(dot)ac(dot)uk
City University				
Northampton Square, London EC1V 0HB 


Attachment: vmstat_output.txt
Description: text/plain (4.8 KB)
Attachment: postgresql.conf
Description: text/plain (13.8 KB)

Responses

pgsql-performance by date

Next:From: Michael FuhrDate: 2007-06-11 13:29:53
Subject: Re: pg_statistic doesnt contain details for specific table
Previous:From: Dave CramerDate: 2007-06-11 10:55:36
Subject: Re: How much ram is too much

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group