simple update queries take a long time - postgres 8.3.1
is the query I am running , and it takes over 10 seconds to complete this
query...
update users set number_recieved=number_recieved+1 where uid=738889333;
table has about 1.7 million rows.. i have an index on column uid and also on
number_received. .. this is also slowing down the inserts that happen.
how to fix this?
i have totally 6 different database of similar size in the same machine..j
The hardware is dual quad core intel xeon5405 , and 32GB RAM, and following
settings changed..
max_connections = 2000 # (change requires restart)
shared_buffers = 4000MB # min 128kB or max_connections*16kB
effective_cache_size = 12000MB
CREATE INDEX idx_uid
ON users
USING btree
(uid);
CREATE INDEX number_rx
ON users
USING btree
(number_recieved);
table
CREATE TABLE users
(
id serial NOT NULL,
username text,
first_name text,
last_name text,
email text,
"password" text,
last_login timestamp without time zone,
profilepic text,
ip text,
dob timestamp without time zone,
created timestamp without time zone DEFAULT now(),
rawpassword character varying(128),
rating real DEFAULT 0,
zip text,
hash text,
gender character(1),
groups text,
aim text,
yahoo text,
skype text,
hotmail text,
vanity text,
number_comments integer DEFAULT 0,
number_friends integer DEFAULT 0,
number_posts integer DEFAULT 0,
number_polls integer DEFAULT 0,
city text,
site text,
number_pictures bigint DEFAULT 0,
email_subscribe boolean DEFAULT true,
number_userpics integer DEFAULT 0,
htmlcodes text,
pageviews integer DEFAULT 1,
number_uservideos integer DEFAULT 0,
number_useraudios integer DEFAULT 0,
number_usermessages integer DEFAULT 0,
number_usermessages_sent integer DEFAULT 0,
myrand double precision NOT NULL DEFAULT random(),
number_activities integer DEFAULT 0,
number_pages integer DEFAULT 0,
uid integer NOT NULL,
number_invites integer DEFAULT 0,
number_notifications integer DEFAULT 0,
number_emailnotifications integer DEFAULT 0,
number_pendingnotifications integer DEFAULT 0,
total_number_invites integer DEFAULT 0,
total_number_emailnotifications integer DEFAULT 0,
last_invitation timestamp without time zone,
last_emailnotification timestamp without time zone,
session_key text,
last_profilefbml timestamp without time zone,
"name" text,
number_sent integer DEFAULT 0,
number_recieved integer DEFAULT 0,
score integer DEFAULT 0,
number_votes integer DEFAULT 0,
CONSTRAINT users_pkey PRIMARY KEY (id),
CONSTRAINT unique_uid UNIQUE (uid),
CONSTRAINT uniquemail UNIQUE (email),
CONSTRAINT uniquuser UNIQUE (username)
)
WITH (OIDS=FALSE);
ALTER TABLE users OWNER TO postgres;
On 31/03/2008 20:16, mark wrote:
is the query I am running , and it takes over 10 seconds to complete
this query...update users set number_recieved=number_recieved+1 where uid=738889333;
table has about 1.7 million rows.. i have an index on column uid and
also on number_received. .. this is also slowing down the inserts that
happen.
Are you VACUUMing the table regularly?
Also, can you show us the EXPLAIN ANALYZE output from the query?
Ray.
---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------
On Mon, Mar 31, 2008 at 12:23 PM, Raymond O'Donnell <rod@iol.ie> wrote:
On 31/03/2008 20:16, mark wrote:
is the query I am running , and it takes over 10 seconds to complete
this query...update users set number_recieved=number_recieved+1 where uid=738889333;
table has about 1.7 million rows.. i have an index on column uid and
also on number_received. .. this is also slowing down the inserts that
happen.Are you VACUUMing the table regularly?
I have this setting on in postgresql.conf.. I dont manually do vaccum..
autovacuum = on # Enable autovacuum subprocess? 'on'
Also, can you show us the EXPLAIN ANALYZE output from the query?
EXPLAIN ANALYZE update users set number_recieved=number_recieved+1 where
uid=738889333;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Index Scan using idx_uid on users (cost=0.00..8.46 rows=1 width=1073)
(actual time=0.094..0.161 rows=1 loops=1)
Index Cond: (uid = 738889333)
Total runtime: 11479.053 ms
(3 rows)
On 31/03/2008 20:38, mark wrote:
EXPLAIN ANALYZE update users set number_recieved=number_recieved+1 where
uid=738889333;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Index Scan using idx_uid on users (cost=0.00..8.46 rows=1 width=1073)
(actual time=0.094..0.161 rows=1 loops=1)
Index Cond: (uid = 738889333)
Total runtime: 11479.053 ms
Is there anything going on that might affect the time taken? - triggers,
loads of indices to be updated, etc? From the docs[1]http://www.postgresql.org/docs/8.3/static/using-explain.html:
"For INSERT, UPDATE, and DELETE commands, the total run time might be
considerably larger, because it includes the time spent processing the
result rows. In these commands, the time for the top plan node
essentially is the time spent computing the new rows and/or locating the
old ones, but it doesn't include the time spent applying the changes.
Time spent firing triggers, if any, is also outside the top plan node,
and is shown separately for each trigger."
Ray.
[1]: http://www.postgresql.org/docs/8.3/static/using-explain.html
---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------
On 31/03/2008 20:38, mark wrote:
I dont manually do vaccum..
It might be worth doing one and seeing if it makes a difference.
Ray.
---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------
On Mon, Mar 31, 2008 at 12:48 PM, Raymond O'Donnell <rod@iol.ie> wrote:
On 31/03/2008 20:38, mark wrote:
EXPLAIN ANALYZE update users set number_recieved=number_recieved+1 where
uid=738889333;
QUERY PLAN------------------------------------------------------------------------------------------------------------------
Index Scan using idx_uid on users (cost=0.00..8.46 rows=1 width=1073)
(actual time=0.094..0.161 rows=1 loops=1)
Index Cond: (uid = 738889333)
Total runtime: 11479.053 msIs there anything going on that might affect the time taken? - triggers,
loads of indices to be updated, etc? From the docs[1]:
no, there are no triggers at all.. just the index on the column..
can you explain what the numbers mean in the EXPLAIN ANALYZE?
(cost=0.00..8.46 rows=1 width=1073) (actual time=0.094..0.161 rows=1
loops=1)
thanks
On 31/03/2008 20:51, mark wrote:
can you explain what the numbers mean in the EXPLAIN ANALYZE?
(cost=0.00..8.46 rows=1 width=1073) (actual time=0.094..0.161 rows=1
loops=1)
It's worth reading through the docs at that reference in my previous
email - it's well explained there.
As I understand it, "cost" signifies the planner's estimate of how long
the query will take, in terms of disk page fetches. The "actual time" is
in milliseconds.
Ray.
---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------
On Mon, Mar 31, 2008 at 12:59 PM, Raymond O'Donnell <rod@iol.ie> wrote:
On 31/03/2008 20:51, mark wrote:
can you explain what the numbers mean in the EXPLAIN ANALYZE?
(cost=0.00..8.46 rows=1 width=1073) (actual time=0.094..0.161 rows=1
loops=1)It's worth reading through the docs at that reference in my previous
email - it's well explained there.As I understand it, "cost" signifies the planner's estimate of how long
the query will take, in terms of disk page fetches. The "actual time" is
in milliseconds.
this is my RAID and HD configuration.. is this a good enough configuration?
is this slowing down my queries?
Raid Controller: 3Ware 9650SE-4LPML, 4x CHs Multi-Lane, LP PCI-e(4x) SATA2
Raid Controller, 256MB
HDD: 4x 500GB/7200 RPM, U300, SATA2 Drive, 16M (RAID 10)
the IO wait is most of the times 10-30%
top - 13:02:35 up 33 days, 4:05, 3 users, load average: 2.65, 2.82, 2.61
Tasks: 1040 total, 1 running, 1039 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.7%us, 0.6%sy, 0.0%ni, 85.3%id, 13.2%wa, 0.0%hi, 0.0%si,
0.0%st
On Mon, Mar 31, 2008 at 12:59 PM, Raymond O'Donnell <rod@iol.ie> wrote:
On 31/03/2008 20:51, mark wrote:
can you explain what the numbers mean in the EXPLAIN ANALYZE?
(cost=0.00..8.46 rows=1 width=1073) (actual time=0.094..0.161 rows=1
loops=1)As I understand it, "cost" signifies the planner's estimate of how long
the query will take, in terms of disk page fetches. The "actual time" is
in milliseconds.
it says actual time is 0.161 seconds or milliseconds.. but the total run
time is 11 seconds.. any ideas why this discrepancy?
Show quoted text
Index Scan using idx_uid on users (cost=0.00..8.46 rows=1 width=1073)
(actual time=0.094..0.161 rows=1 loops=1)
Index Cond: (uid = 738889333)
Total runtime: 11479.053 ms
On 31/03/2008 21:11, mark wrote:
it says actual time is 0.161 seconds or milliseconds.. but the total run
time is 11 seconds.. any ideas why this discrepancy?
Well, I interpret the docs as implying that the difference between the
time quoted in the top line of EXPLAIN ANALYZE's output and the time in
"Total runtime" if effectively the time taken to apply the update.
Beyond that, I'm guessing.....
Ray.
---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------
I am developing an application where I will have a default table and/or
view for a select statement for my application. If a client has a special
requirement I would like to have a alternate table of the same name in a
different schema with the change structure.
Schema A
Default table - coretable.foo
Schema B
Client specific table client.foo
How can I check if a table or alternate view exists in schema B.
ie
if client.foo exists // check for a view or table
// my code will use client.foo table
else
// my code will use coretable.foo
endif
Also is it possible to check via function/via in postgress and not have to
rely on my application to do the check. If so how.
Em Monday 31 March 2008 22:35:01 Jeff Williams escreveu:
I am developing an application where I will have a default table and/or
view for a select statement for my application. If a client has a special
requirement I would like to have a alternate table of the same name in a
different schema with the change structure.Schema A
Default table - coretable.fooSchema B
Client specific table client.fooHow can I check if a table or alternate view exists in schema B.
ie
if client.foo exists // check for a view or table
// my code will use client.foo table
else
// my code will use coretable.foo
endifAlso is it possible to check via function/via in postgress and not have to
rely on my application to do the check. If so how.
I'd give both the same name and make the client schema first in the search
path. Then, you should just call the function and it would follow the search
path order and give you what you want without having to check anything.
--
Jorge Godoy <jgodoy@gmail.com>
On 2008-03-31 21:16, mark wrote:
is the query I am running , and it takes over 10 seconds to complete
this query...
update users set number_recieved=number_recieved+1 where
uid=738889333;
Every time or only sometimes?
If it is sometimes then I think this query is waiting for a checkpoint.
If I'm right then you'll have to tune Postgres to do them more often, so
that there is less work to do each time. You use 4GB of shared buffers
so write-busy database can write gigabytes of data on each checkpoint,
which can take seconds.
Upgrade to 8.3 will help as checkpoint writing algorithms were optimized
there for lots of RAM.
Read this for more info and tuning tips:
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
On Mon, Mar 31, 2008 at 11:18 PM, Tomasz Ostrowski <tometzky@batory.org.pl>
wrote:
On 2008-03-31 21:16, mark wrote:
is the query I am running , and it takes over 10 seconds to complete
this query...
update users set number_recieved=number_recieved+1 where
uid=738889333;Every time or only sometimes?
i checked again, and it seems to be only sometimes.. and during these
times there are lot of other queries too running which I can see from
pg_stat_activity;
Upgrade to 8.3 will help as checkpoint writing algorithms were optimized
there for lots of RAM.
I already am running 8.3.1 [ i mentioned in subject]. I am having the
default settings for those. can you pl. share the settings for check points:
current settings all default
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB
each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 -
1.0
#checkpoint_warning = 30s # 0 is off
On Tue, Apr 1, 2008 at 12:44 AM, mark <markkicks@gmail.com> wrote:
On Mon, Mar 31, 2008 at 11:18 PM, Tomasz Ostrowski <tometzky@batory.org.pl>
wrote:On 2008-03-31 21:16, mark wrote:
is the query I am running , and it takes over 10 seconds to complete
this query...
update users set number_recieved=number_recieved+1 where
uid=738889333;Every time or only sometimes?
i checked again, and it seems to be only sometimes.. and during these
times there are lot of other queries too running which I can see from
pg_stat_activity;Upgrade to 8.3 will help as checkpoint writing algorithms were optimized
there for lots of RAM.
I already am running 8.3.1 [ i mentioned in subject]. I am having the
default settings for those. can you pl. share the settings for check points:current settings all default
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB
each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0-
1.0
#checkpoint_warning = 30s # 0 is off
this is what I have on pg_stat_bgwriter ; how much should I increase
checkpoint_segment & checkpoint_completion_target to? thanks a lot!
postgres=# select * from pg_stat_bgwriter;
checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean |
maxwritten_clean | buffers_backend | buffers_alloc
-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
42 | 1051 | 8035125 | 21954
| 42 | 241519 | 10394696
On 2008-04-01 09:44, mark wrote:
I already am running 8.3.1 [ i mentioned in subject].
Missed that, sorry.
I'd first try to set the following on a write-busy 8.3 server to smooth
checkpoint spikes:
checkpoint_segments = 16
checkpoint_timeout = 20min
checkpoint_completion_target = 0.8
I'd also set
log_checkpoints=on
to get an idea how it behaves.
But I have no experience on anything with more than 1GB of RAM...
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
On Tue, Apr 1, 2008 at 1:48 AM, Tomasz Ostrowski <tometzky@batory.org.pl>
wrote:
On 2008-04-01 09:44, mark wrote:
I already am running 8.3.1 [ i mentioned in subject].
But I have no experience on anything with more than 1GB of RAM...
Should I reduce shared_buffers to less than 1GB? If that is going to improve
performance, I certainly can do it. I somehow assumed allocating more
shared_buffers is always good..?
thanks
Tomasz Ostrowski <tometzky@batory.org.pl> writes:
I'd also set
log_checkpoints=on
to get an idea how it behaves.
Yeah, that's really the *first* thing to do. You need to determine
whether the episodes of slowness are correlated with checkpoints
or not; there's no point fooling with the checkpoint parameters if
not.
Another thing you could try in order to get a handle on what's
happening is to watch the output of "vmstat 1" or a similar tool,
and see if there are spikes in CPU or I/O load when things are
slow. If not, I'd next look into PG's pg_locks view to see if
queries are getting blocked on locks.
regards, tom lane
On Tue, Apr 1, 2008 at 7:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Tomasz Ostrowski <tometzky@batory.org.pl> writes:
I'd also set
log_checkpoints=on
to get an idea how it behaves.Yeah, that's really the *first* thing to do. You need to determine
I set this on,
log_checkpoints = on
and started postgres using this command
pg_ctl -D /mnt/work/database -l /mnt/work/logs/pgsql.log start
nothing is getting logged in the logfile. should I turn on some other
settings?
mark <markkicks@gmail.com> writes:
I set this on,
log_checkpoints = on
and started postgres using this command
pg_ctl -D /mnt/work/database -l /mnt/work/logs/pgsql.log start
nothing is getting logged in the logfile. should I turn on some other
settings?
You're probably looking in the wrong logfile. Depending on what your
log destination settings are, the file mentioned in pg_ctl -l is likely
to see only early-postmaster-startup messages (before it's read the
configuration file and adopted those settings).
regards, tom lane
