Response time increases over time

From: Havasvölgyi Ottó <havasvolgyi(dot)otto(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Response time increases over time
Date: 2011-12-06 21:30:11
Message-ID: CAOryeA1Wbi3p51tsJQKd79twV0bK2D+6pKv13eHEWdJnYth+bA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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: default

I 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Mario Splivalo 2011-12-07 00:23:57 Re: Different query plans on same servers
Previous Message Kevin Grittner 2011-12-06 20:29:03 Re: Different query plans on same servers