Hi all,
I am running a load simulation on Debian with PostgreSQL 8.4.9 (standard Debian package).
Certain number of clients do the following stepsin a transaction (read commited level) periodically (about 1.1 transaction per second / client) and concurrently:
-reads a record of table Machine and State (they each have about 300 records, read size is about 1.4 KB)
-reads a record of table Card (it has about 1200 records)
-reads some other records from other tables, all these are straightforward, single line queries (here there are even less records in the tables)
-updates Machine (1 record, updates 2-3 integer values)
-updates State (1 record, updates a bytea field, about 1,3KB)
-updates Card (1 record, updates an integer)
-inserts 1-1 record into 2 log tables
Its important, that each client updates different records, so there is no update conflict. There are no triggers or rules. Tables have simple indexes, 2 at most.
As I run the simulation with more and more clients, I can observe, that at the beginning of the simulation the transaction times are quite acceptable (20-30 ms) and quite uniform/smooth, but as the simultion progresses, it becomes higher (30-40-50-60 ms) and more and more non-uniform, but the tps interestingly remains the same during the simulation. With 100 clients this kind of behaviour can be seen very well. The simulation's duration is 500 sec.
I wonder why this happens on this server, and how I can keep the response time as low as at the beginning.
Just for comparison, I ran the same simulation on a Windows 7 notebook machine but with PostgreSQL 9.1.2 (downloaded from EnterpriseDB's site, not PostgreSQL Plus), and it did not show this problem even with 120 clients. It's transaction times were surprisingly smooth and consistent. The client code was the same in the 2 cases.
Actually I ran first the test on the Windows machine, and after that on the better Debian. I expected that it would be even better there. Network latency is quite minimal, because the clients and the database server run on VMs on a server machine in the Linux case.
Here is some important config variables from the 8.4 (9.1.2 is configured similarly):
ssl=false
shared_buffers=24MB (OS max currently, but should not be a problem because 9.1.2 performed quite well on Windows with 24 MB)
work_mem=1MB
maintainance_work_mem=16MB
fsync=on
sync_commit=on
wal_sync_method=fsync
full_page_writes=on
wal_buffers=1MB
commit_delay=0
checkpoint segments=8
effective_cache_size=256MB
vacuum: default
bgwriter: defaultI suspected that due to the lot of update, the tables get bloated with dead rows, but vacuum analyze verbose did not show that.
It seems that something cannot keep up with the load, but tps does not change, just the response time gets higher.
Could you please help me with what can cause this kind of behaviour on Linux?
What setting should I change perhaps?
Is there so much difference between 8.4 and 9.1, or is this something else?
Please tell me if any other info is needed.
Thanks in advance,
Otto
