I have observed a weird situation that over time the performance of a query (a combination of queries explained below) degrades, meaning at the start of testing (for a few minutes) the time of the query is 2ms then next day it got to 15ms then day after 30ms.
By query I refer here to a combination of either:
- insert a row into table 2, select a row from table 2, select a row in table 3, update a row in table 3, commit
- insert a row into table 1, select a row in table 3, update a row in table 3, commit
I wonder what might be the reason of that or which settings from the configuration file should I consider setting and how? I observed the problem on Ubuntu machine where database was set and the primary keys were not added. On the other hand on Win which I develop on it was not observed (it was running constantly on average 3ms per query for 7 days).
I noticed that in the new database (on Ubuntu) there were no primary keys on any table, as oppose to the one I develop on. Could the lack of primary keys have the negative impact on this sort of query?
I thought I will ask this question in the mean time as I am moving my whole db from my development machine to the test one.
On development I used PostgreSQL 8.4 (CPU: Intel i7 740QM, RAM: 6GB), on test there is PostgreSQL 9.1 (CPU: Intel i3-2100, RAM: 3.8GB).
UPDATE: autovacuum
related parameters:
#autovacuum = on
#log_autovacuum_min_duration = -1
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
#autovacuum_vacuum_cost_delay = 20ms
#autovacuum_vacuum_cost_limit = -1
UPDATE2: It appears that the problem occurs on the development machine as well, but I remember it running fine before. Never the less I did some more testing and did run EXPLAIN ANALYZE on the query, which I takes the most time, and it is an update (I also seen selects take a while on the table as well) presented below:
EXPLAIN ANALYZE UPDATE ais_track SET latest_dynamic = '2012-09-10 22:22:22.222' WHERE mmsi = 123456789 AND ais_system = 1;
The below results are on Win as restore still is in progress on the Ubuntu, and I got this at first:
Index Scan using pk_track on ais_track (cost=0.00..4.46 rows=1 width=36) (actual time=1.090..2.460 rows=1 loops=1)
Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 8.681 ms
Then on 2nd repeat and further repeats of the same update query for a few times I get something of this form:
Index Scan using pk_track on ais_track (cost=0.00..4.46 rows=1 width=36) (actual time=0.699..1.797 rows=1 loops=1)
Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 1.850 ms
After a hundred repeats or so it got to over 2ms.
EXPLAIN ANALYZE for Select:
EXPLAIN ANALYZE SELECT * FROM ais_track WHERE mmsi = 123456789 AND ais_system = 1
1st run:
Index Scan using pk_track on ais_track (cost=0.00..4.46 rows=1 width=38) (actual time=1.283..2.522 rows=1 loops=1)
Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 2.560 ms
After a hundred or so runs:
Index Scan using pk_track on ais_track (cost=0.00..4.46 rows=1 width=38) (actual time=0.027..1.357 rows=1 loops=1)
Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 1.382 ms
The table used in query:
CREATE TABLE ais_track
(
ais_system integer NOT NULL,
mmsi integer NOT NULL,
ext_id integer,
latest_dynamic timestamp without time zone,
latest_static timestamp without time zone,
"name" character varying,
CONSTRAINT pk_track PRIMARY KEY (mmsi, ais_system)
)
And two indexes:
CREATE INDEX ais_track_mmsi
ON ais_track
USING btree
(mmsi);
CREATE INDEX ais_track_sys
ON ais_track
USING btree
(ais_system);
NOTE: The table size is 11000 and it doesn't change.
autovacuum
settings? Or how frequently do youVACUUM
your tables? And what does 'initially' mean? – dezso Oct 24 '12 at 11:02autovacuum
is on and all of vaccum related configuration is as it came with the database. I do not do VACUUM myself. The word 'initially' means that such time is observed on the start of the query execution, so on the very start of the test for a while and then the query after a few minutes starts to take longer and longer with each query. Like I said the changes are observable over hour period. – Boro Oct 24 '12 at 11:07