Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have some very simple queries that occasionally run very slowly.

The table viewed_sites has about 10 - 20 rows.

Running EXPLAIN ANALYZE always gives a runtime of less than 3 milliseconds.

When the query is run automatically (every 10 seconds) it occasionally takes over a second to run.

The query:

INSERT INTO ga.viewed_sites (site_id) VALUES ('gop2')

The table:

CREATE TABLE viewed_sites (
    site_id character varying(4) NOT NULL,
    last_viewed timestamp with time zone DEFAULT now() NOT NULL
);

The (occasional) log result:

2010-05-24 15:47:55 UTC LOG: duration: 1044.632 ms statement: INSERT INTO ga.viewed_sites (site_id) VALUES ('gop2')

It's a horribly vague question, but what could be causing this? I suppose it comes down to CPU, RAM, HDD or some combination of the above.

Postgresql 8.3, Ubuntu 8.04

Intel(R) Core(TM)2 Duo CPU E6750 @ 2.66GHz

2 GiB RAM

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

Or, the table could be locked. Which seems all things given, more likely. Postgresql is very conservative about locks, but it can happen. A one second wait for HD write, or CPU seems extremely unlikely. And, it doesn't take much ram to do an insert. Check the table pg_locks (and pg_stat_activity to see what their doing), and the function pg_lock_status(), or some of the other tables and functions pertaining to lock status.

share|improve this answer
    
Is there a way to log table locks? The chances of the lock being active when I look at those tables is slim. –  Johan May 26 '10 at 10:59
add comment

Does the table have a foreign key? If it has to check the foreign key constraint, then it has to go do a table lookup. Maybe that's slowing it down.

http://wiki.postgresql.org has a ton of tips.

share|improve this answer
    
It does have a foreign key, and a trigger. I disabled both of these and it had no effect. Thanks for the wiki link though. –  Johan May 26 '10 at 8:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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