6

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.

11
  • What are your autovacuum settings? Or how frequently do you VACUUM your tables? And what does 'initially' mean?
    – dezso
    Oct 24, 2012 at 11:02
  • @dezso Thanks for the edit. The autovacuum 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, 2012 at 11:07
  • Could you show tha autovacuum specifics? If your system is really write-heavy, those can count.
    – dezso
    Oct 24, 2012 at 11:17
  • @dezso sure please see the UPDATE. They set to their default values. I suspect as well that this might be the reason, though I have not played with these settings just yet. First will get the exact db copy to be sure this is not table related problem. Do you need any other setting details, I am happy to share them?
    – Boro
    Oct 24, 2012 at 11:29
  • Could you show the results from EXPLAIN ANALYZE ? Without this information it's hard to understand why queries are slow. Oct 24, 2012 at 11:38

2 Answers 2

5

Primary and unique keys in all(?) RDBMSes use indexes in order to quickly be able to determine whether a newly inserted value is indeed unique.

The side effect of this is that queries via primary and unique keys are usually "fast".

Now if you haven't defined primary or unique keys on your tables,

  1. You don't have a relational table but you have junk (OK, this is a contentious opinion, but a relational model needs keys on all tables).
  2. queries on this table (in the absence of any other indexes) will become slower as more data is inserted into the table.

So yes, the absence of primary keys will cause this!

6
  • Yea in all there were no keys. I know that there should be keys present in the first place. Though someone entered the data structure manually rather than from a dump thus forgetting to get the keys. But the indexes were still present. Thus I was wondering if the lack of any sort of keys would in fact impact it if each single query (select/insert/update) are executed on a single table, thus any relation between tables simply is not present in the used queries?
    – Boro
    Oct 24, 2012 at 13:23
  • Aha, so you do have the indexes: Can you post explain analyze outputs and compare them between the two systems? Hopefully the (amount of) data in the two systems is comparable so that other factors won't cause differences in execution plans. Oct 24, 2012 at 13:26
  • Yea I am working on that at the moment. I am restoring the data as we speak but from what I see it could take a long while. But on Win though the largest table had 70m rows and was growing for about 5m a day it handled it with constant 3.5ms. Will get EXPLAINs to you guys as soon as I can, i.e. the restore is completed. Thanks for you involvement.
    – Boro
    Oct 24, 2012 at 13:33
  • Colin, @FrankHeikens and dezso please see UPDATE2 for explain results. Maybe you have some suggestions as to what could be done here to stabilise the update/select time?
    – Boro
    Oct 24, 2012 at 14:28
  • What's the problem? If you vacuum full now, does the time go back down to the same time as at the start? Oct 25, 2012 at 8:50
3

Thanks @Frank Heikens, @dezso, and @Colin 't Hart for suggestions and involvement, +1 to you guys.

The problem was with the way I was using MyBatis. I had used a 'special' :) singleton pattern which was able to spawn new instances if needed and had one shared connection for some general stuff. Though for the operations mentioned in my question I was using a new instance of connection.

Apparently, this is not a good idea to use a shared connection, as my experience shows and MyBatis manual suggests by reminding to close connection (session) after work is done. Anyway the problem was that I used a shared connection to load a static table from the db at start then after it done its single select the connection was not used. Though when I was making the queries i.e. select and update on the ais_track table the times of operations were increasing. I still wonder why exactly not closing the shared connection was making the queries executed on a different connection to slow down with each execution - any suggestion would be appreciated.

I couldn't replicate this problem when using plain JDBC. So I assume it must have something to do with MyBatis, or rather me not using it right?

My solution was to get rid off of the shared resource and once I do some operation and I no longer use a connection I close it. Also I have noticed that, if I was forcing a commit after a select using the shared connection even if it was still left open the issue was not appearing.

BTW: Please do comment if and most importantly for what reason it is bad to use singleton or in other words to have a hanging connection to db (if in fact what I am saying is true).

1
  • 1
    It's not exactly clear what was going on with this other connection, but if it was leaving a long running transaction open, that would certainly hurt performance in the long run, since it'd likely prevent autovacuum from cleaning up dead rows. I wouldn't expect this to be a problem with most db connection libraries, but it sounds possible.
    – xzilla
    Mar 24, 2013 at 4:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.