[PERFORM] Vacum Analyze problem
Hello everyone: I wanted to ask you about how the VACUUM ANALYZE works. is it possible that something can happen in order to reset its effects forcing to execute the VACUUM ANALYZE comand again? i am asking this because i am struggling with a query which works ok after i run a VACUUM ANALYZE, however, sudennly, it starts to take forever (the execution of the query) until i make another VACUUM ANALYZE, and so on ... I'd like to point that i am a novice when it comes to non basic postgresql performance related stuff. Thank you all in advance Rafael ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Vacum Analyze problem
> On 9/4/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> > wrote: >> >> Hello everyone: >> >>I wanted to ask you about how the VACUUM ANALYZE works. is it >>possible >> that something can happen in order to reset its effects forcing to >> execute the VACUUM ANALYZE comand again? > > > > Yes, lots of modifications (INSERT,UPDATE,DELETE) to the table in > question. > > Regards > > MP I knew that in the long run the VACUUM ANALYZE comand has to be executed again. My question is if something can happen over night and cause the need of a new VACUUM ANALYZE (regenerating indexes or other thing related with performance). Thanks for your reply. Rafael ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Vacum Analyze problem
> On Tuesday 04 September 2007 11:27:07 [EMAIL PROTECTED] wrote: >> Hello everyone: >> >>I wanted to ask you about how the VACUUM ANALYZE works. is it >>possible >> that something can happen in order to reset its effects forcing to >> execute the VACUUM ANALYZE comand again? i am asking this because i am >> struggling with a query which works ok after i run a VACUUM ANALYZE, >> however, sudennly, it starts to take forever (the execution of the >> query) until i make another VACUUM ANALYZE, and so on ... >>I'd like to point that i am a novice when it comes to non basic >> postgresql performance related stuff. >> >> Thank you all in advance >> >> Rafael >> >> >> >> ---(end of >> broadcast)--- TIP 1: if posting/reading >> through Usenet, please send an appropriate >>subscribe-nomail command to [EMAIL PROTECTED] so that >>your message can get through to the mailing list cleanly > > Rafael; > > Vacuum Analyze performs 2 tasks at once. > > 1) Vacuum - this analyzes the table pages and sets appropriate dead row > space (those from old updates or deletes that are not possibly needed > by any existing transactions) as such that the db can re-use > (over-write) that space. > > 2) Analyze - Like an Oracle compute stats, updates the system catalogs > with current table stat data. > > The Vacuum will improve queries since the dead space can be re-used and > any dead space if the table you are having issues with is a high > volume table then the solution is generally to run vacuum more often - > I've seen tables that needed a vacuum every 5 minutes due to > significant sustained churn. > > The Analyze of course is key for the planner, if the table is growing > rapidly then running analyze more often will help, if however there is > lots of churn but little change in the data (i.e. lots of inserts > followed by delete's of the same rows) then a straight vacuum is > probably what you need. If the data is changing rapidly then bumping > up the default_statistics_target value may help - you can bump the > default_statistics_target for a single table in the pg_autovacuum > system catalog table. > > Hope this helps... > > /Kevin > > > -------(end of > broadcast)--- TIP 3: Have you checked our > extensive FAQ? > > http://www.postgresql.org/docs/faq Thank you all for the information. I'll get to work on it and see what happends. Thanks again Rafael ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Problems with an update-from statement and pg-8.1.4
Hello
We are having some problems with an UPDATE ... FROM sql-statement and
pg-8.1.4. It takes ages to finish. The problem is the Seq Scan of the
table 'mail', this table is over 6GB without indexes, and when we send
thousands of this type of statement, the server has a very high iowait
percent.
How can we get rid of this Seq Scan?
I send the output of an explain and table definitions:
-
mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail m,
mail_received mr where mr.mail_id = m.mail_id AND mr.queue_id =
'1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47';
QUERY PLAN
Nested Loop (cost=0.00..932360.78 rows=7184312 width=57)
-> Nested Loop (cost=0.00..6.54 rows=1 width=0)
-> Index Scan using received_queue_id_index on mail_received
mr (cost=0.00..3.20 rows=1 width=4)
Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text)
Filter: (mailhost = '129.240.10.47'::inet)
-> Index Scan using mail_pkey on mail m (cost=0.00..3.32
rows=1 width=4)
Index Cond: ("outer".mail_id = m.mail_id)
-> Seq Scan on mail (cost=0.00..860511.12 rows=7184312 width=57)
(8 rows)
mailstats=# \d mail
Table "public.mail"
Column | Type | Modifiers
+--+
mail_id| integer | not null default
nextval('mail_mail_id_seq'::regclass)
size | integer |
message_id | text | not null
spamscore | numeric(6,3) |
Indexes:
"mail_pkey" PRIMARY KEY, btree (mail_id)
"mail_message_id_key" UNIQUE, btree (message_id)
mailstats=# \d mail_received
Table "public.mail_received"
Column |Type |
Modifiers
---+-+--
reception_id | integer | not null default
nextval('mail_received_reception_id_seq'::regclass)
mail_id | integer | not null
envelope_from | text|
helohost | text|
from_host | inet|
protocol | text|
mailhost | inet|
received | timestamp without time zone | not null
completed | timestamp without time zone |
queue_id | character varying(16) | not null
Indexes:
"mail_received_pkey" PRIMARY KEY, btree (reception_id)
"mail_received_queue_id_key" UNIQUE, btree (queue_id, mailhost)
"mail_received_completed_idx" btree (completed)
"mail_received_mailhost_index" btree (mailhost)
"mail_received_received_index" btree (received)
"received_id_index" btree (mail_id)
"received_queue_id_index" btree (queue_id)
Foreign-key constraints:
"$1" FOREIGN KEY (mail_id) REFERENCES mail(mail_id)
-----
Thanks in advance.
regards,
--
Rafael Martinez, <[EMAIL PROTECTED]>
Center for Information Technology Services
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [PERFORM] Problems with an update-from statement and pg-8.1.4
On Wed, 2006-12-06 at 14:55 -0500, Ted Allen wrote:
> Stephan Szabo wrote:
> > On Wed, 6 Dec 2006, Rafael Martinez wrote:
> >>
> >> mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail m,
> >> mail_received mr where mr.mail_id = m.mail_id AND mr.queue_id =
> >> '1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47';
> >>
> >
> > I don't think this statement does what you expect. You're ending up with
> > two copies of mail in the above one as "mail" and one as "m". You probably
> > want to remove the mail m in FROM and use mail rather than m in the
> > where clause.
> >
> >
> Worse yet I think your setting "spamcore" for EVERY row in mail to
> '-5.026'. The above solution should fix it though.
>
> -- Ted
>
Thanks for the answers. I think the 'problem' is explain in the
documentation:
"fromlist
A list of table expressions, allowing columns from other tables to
appear in the WHERE condition and the update expressions. This is
similar to the list of tables that can be specified in the FROMClause of
a SELECT statement. Note that the target table must not appear in the
fromlist, unless you intend a self-join (in which case it must appear
with an alias in the fromlist)".
And as you said, we can not have 'mail m' in the FROM clause. I have
contacted the developers and they will change the statement. I gave then
these 2 examples:
---
mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM
mail_received mr where mr.mail_id = mail.mail_id AND mr.queue_id =
'1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47';
QUERY PLAN
--
Nested Loop (cost=0.00..6.54 rows=1 width=57)
-> Index Scan using received_queue_id_index on mail_received mr
(cost=0.00..3.20 rows=1 width=4)
Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text)
Filter: (mailhost = '129.240.10.47'::inet)
-> Index Scan using mail_pkey on mail (cost=0.00..3.32 rows=1
width=57)
Index Cond: ("outer".mail_id = mail.mail_id)
(6 rows)
mailstats=# explain update mail SET spamscore = '-5.026' where mail_id
= (select mail_id from mail_received where queue_id = '1GrxLs-0004N9-I1'
and mailhost = '129.240.10.47');
QUERY PLAN
-
Index Scan using mail_pkey on mail (cost=3.20..6.52 rows=1 width=57)
Index Cond: (mail_id = $0)
InitPlan
-> Index Scan using received_queue_id_index on mail_received
(cost=0.00..3.20 rows=1 width=4)
Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text)
Filter: (mailhost = '129.240.10.47'::inet)
(6 rows)
---
regards,
--
Rafael Martinez, <[EMAIL PROTECTED]>
Center for Information Technology Services
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Problems with an update-from statement and pg-8.1.4
On Wed, 2006-12-06 at 14:19 -0600, Erik Jones wrote:
> Rafael Martinez wrote:
> > On Wed, 2006-12-06 at 14:55 -0500, Ted Allen wrote:
> >
> >> Stephan Szabo wrote:
> >>
> >>> On Wed, 6 Dec 2006, Rafael Martinez wrote:
> >>>
> >>>> mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM mail m,
> >>>> mail_received mr where mr.mail_id = m.mail_id AND mr.queue_id =
> >>>> '1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47';
> >>>>
> >>>>
> >>> I don't think this statement does what you expect. You're ending up with
> >>> two copies of mail in the above one as "mail" and one as "m". You probably
> >>> want to remove the mail m in FROM and use mail rather than m in the
> >>> where clause.
> >>>
> >>>
> >>>
> >> Worse yet I think your setting "spamcore" for EVERY row in mail to
> >> '-5.026'. The above solution should fix it though.
> >>
> >> -- Ted
> >>
> >>
> >
> > Thanks for the answers. I think the 'problem' is explain in the
> > documentation:
> >
> > "fromlist
> >
> > A list of table expressions, allowing columns from other tables to
> > appear in the WHERE condition and the update expressions. This is
> > similar to the list of tables that can be specified in the FROMClause of
> > a SELECT statement. Note that the target table must not appear in the
> > fromlist, unless you intend a self-join (in which case it must appear
> > with an alias in the fromlist)".
> >
> > And as you said, we can not have 'mail m' in the FROM clause. I have
> > contacted the developers and they will change the statement. I gave then
> > these 2 examples:
> >
> > ---
> > mailstats=# EXPLAIN update mail SET spamscore = '-5.026' FROM
> > mail_received mr where mr.mail_id = mail.mail_id AND mr.queue_id =
> > '1GrxLs-0004N9-I1' and mr.mailhost = '129.240.10.47';
> > QUERY PLAN
> > --
> > Nested Loop (cost=0.00..6.54 rows=1 width=57)
> >-> Index Scan using received_queue_id_index on mail_received mr
> > (cost=0.00..3.20 rows=1 width=4)
> > Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text)
> > Filter: (mailhost = '129.240.10.47'::inet)
> >-> Index Scan using mail_pkey on mail (cost=0.00..3.32 rows=1
> > width=57)
> > Index Cond: ("outer".mail_id = mail.mail_id)
> > (6 rows)
> >
> > mailstats=# explain update mail SET spamscore = '-5.026' where mail_id
> > = (select mail_id from mail_received where queue_id = '1GrxLs-0004N9-I1'
> > and mailhost = '129.240.10.47');
> > QUERY PLAN
> > -
> > Index Scan using mail_pkey on mail (cost=3.20..6.52 rows=1 width=57)
> >Index Cond: (mail_id = $0)
> >InitPlan
> > -> Index Scan using received_queue_id_index on mail_received
> > (cost=0.00..3.20 rows=1 width=4)
> >Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text)
> >Filter: (mailhost = '129.240.10.47'::inet)
> > (6 rows)
> > ---
> >
> Look again at the estimated costs of those two query plans. You haven't
> gained anything there. Try this out:
>
> EXPLAIN UPDATE mail
> SET spamscore = '-5.026'
> FROM mail_received mr
> WHERE mail.mail_id = mr.mail_id AND mr.queue_id = '1GrxLs-0004N9-I1' ;
>
Haven't we?
* In the statement with problems we got this:
Nested Loop (cost=0.00..932360.78 rows=7184312 width=57)
* In the ones I sent:
Nested Loop (cost=0.00..6.54 rows=1 width=57)
Index Scan using mail_pkey on mail (cost=3.20..6.52 rows=1 width=57)
* And in the last one you sent me:
--
Nested Loop (cost=0.00..6.53 rows=1 width=57)
-> Index Scan using received_queue_id_index on mail_received mr
(cost=0.00..3.20 rows=1 width=4)
Index Cond: ((queue_id)::text = '1GrxLs-0004N9-I1'::text)
-> Index Scan using mail_pkey on mail (cost=0.00..3.32 rows=1
width=57)
Index Cond: (mail.mail_id = "outer".mail_id)
(5 rows)
--
I can not see the different.
regards,
--
Rafael Martinez, <[EMAIL PROTECTED]>
Center for Information Technology Services
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
Douglas J Hunley wrote: Hello The DB server in question does nothing else, is running CentOS 4.5, kernel 2.6.9-55.ELsmp. Hyperthreading is disabled in the BIOS and there are 2 Xeon 3.4Ghz cpus. There is 8Gb of RAM in the machine, and another 8Gb of swap. After a very quick read of your configuration files, I found some paramaters that need to be change if your server has 8GB of RAM. The values of these parameters depend a lot of how much RAM you have, what type of database you have (reading vs. writing) and how big the database is. I do not have experience with 8.2.x yet, but with 8.1.x we are using as defaults in out 8GB RAM servers these values in some of the paramaters (they are not the only ones, but they are the minimum to change): 25% of RAM for shared_buffers 2/3 of ram for effective_cache_size 256MB for maintenance_work_mem 32-64MB for work_mem 128 checkpoint_segments 2 random_page_cost And the most important of all: fsync should be ***ON*** if you appreciate your data. It looks like you are using default values #shared_buffers = 32MB # min 128kB or max_connections*16kB #work_mem = 1MB # min 64kB #maintenance_work_mem = 16MB# min 1MB fsync = off # turns forced synchronization on or off #effective_cache_size = 128MB [] -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/> ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Help with optimizing a sql statement
On Thu, 2006-02-09 at 13:46 -0600, Jim C. Nasby wrote: > I looked at the estimates for the table access methods and they all > looked ok, so I think the statistics are pretty up-to-date; there just > aren't enough of them for the planner to do a good job. > VACUUM ANALYZE runs 4 times every hour, so yes, statistics are up-to-date. I will increase default_statistics_target tomorrow at work and see what happens. Thanks for your help. -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Help with optimizing a sql statement
On Thu, 2006-02-09 at 18:22 -0500, Tom Lane wrote: > Rafael Martinez Guerrero <[EMAIL PROTECTED]> writes: > > WHERE ((ACL_2.RightName = 'OwnTicket')) > > AND ((CachedGroupMembers_4.MemberId = Principals_1.id)) > > AND ((Groups_3.id = CachedGroupMembers_4.GroupId)) > > AND ((Principals_1.Disabled = '0') or (Principals_1.Disabled = '0')) > > AND ((Principals_1.id != '1')) > > AND ((main.id = Principals_1.id)) > > AND ( (ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = > > 'Group' AND ( Groups_3.Domain = 'SystemInternal' OR Groups_3.Domain = > > 'UserDefined' OR Groups_3.Domain = 'ACLEquivalence')) OR ( ( > > (Groups_3.Domain = 'RT::Queue-Role' ) ) AND Groups_3.Type > > =ACL_2.PrincipalType) ) > > AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue') > > ) > > Are you sure this WHERE clause really expresses your intent? It seems > awfully oddly constructed. Removing the redundant parens and clarifying > the layout, I get > [] This is an application that we have not programmed, so I am not sure what they are trying to do here. I will contact the developers. Tomorrow I will try to test some of your suggestions. > BTW, what PG version is this? It looks to me like it's doing some > manipulations of the WHERE clause that we got rid of a couple years ago. > If this is 7.4 or older then you really ought to be thinking about an > update. > We are running 7.4.8 in this server and will upgrade to 8.0.6 in a few weeks. Thanks. -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Same SQL, 104296ms of difference between 7.4.12 and
On Fri, 2006-04-07 at 16:41 +0200, Gábriel Ákos wrote: > > > > Any ideas of what I can test/configurate to find out why this happens? > > Thanks in advance. > > Increase work_mem to 50% of memory, and don't care about > maintenance_work_mem and effective_cache_size, they don't matter in this > case. > The problem is not the amount of memory. It works much faster with only 16M and 7.4.12 than 8.0.7. -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Same SQL, 104296ms of difference between 7.4.12 and
On Fri, 2006-04-07 at 13:36 -0400, Tom Lane wrote: > I wrote: > > Rafael Martinez Guerrero <[EMAIL PROTECTED]> writes: > >> I have a sql statement that takes 108489.780 ms with 8.0.7 in a > >> RHEL4/amd64linux server with 2xAMD Opteron(tm) Processor 275 2.00GHz / > >> 8GB RAM and only 4193.588 ms with 7.4.12 in a RHEL3/386linux server with > >> 2xIntel(R) Xeon(TM) CPU 2.40GHz / 4GB RAM. > > > I think you've discovered a planner regression. > > Simplified test case using the regression database: > > > explain select * from tenk1 a, tenk1 b > > where (a.ten = b.ten and (a.unique1 = 100 or a.unique1 = 101)) > >or (a.hundred = b.hundred and a.unique1 = 42); > > I've repaired the assertion crash in 8.1/HEAD, but I don't think it's > practical to teach 8.0 to optimize queries like this nicely. The reason > 7.4 can do it is that 7.4 forces the WHERE condition into CNF, ie > [..] Tom, thank you very much for your help. As I suspected this was a more complicated problem than the configuration of some parameters :( . Good that we have found out this now and not after the upgrade. All our upgrade plans and testing for all our databases have been done for/with 8.0.x (yes, I know 8.1.x is much better, but I am working in a conservative place from the sysadm point of view). We will have to change our plans and go for 8.1 if we want this to work. > My recommendation is to update to 8.1.4 when it comes out. Any idea about when 8.1.4 will be released? Thanks again. -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] Need to run CLUSTER to keep performance
Hello
This is a question about something we have seen sometimes in the last
months. It happens with tables with a large amount of updates/selects
compared with the amount of inserts/deletes. The sizes of these tables
are small and the amount of rows too.
The 'problem' is that performance decrease during the day and the only
thing that helps is to run CLUSTER on the table with problems. VACUUM
ANALYZE does not help.
Some information that can help to find out why this happens:
- PostgreSQL version: 8.1.9
--
scanorama=# SELECT pg_size_pretty(pg_relation_size('hosts'));
pg_size_pretty
12 MB
--
scanorama=# SELECT count(*) FROM hosts ;
count
---
16402
--
scanorama=# EXPLAIN ANALYZE SELECT * FROM hosts;
Seq Scan on hosts (cost=0.00..2771.56 rows=66756 width=314) (actual
time=0.008..2013.415 rows=16402 loops=1)
Total runtime: 2048.486 ms
--
scanorama=# VACUUM ANALYZE ;
VACUUM
--
scanorama=# EXPLAIN ANALYZE SELECT * FROM hosts;
Seq Scan on hosts (cost=0.00..2718.57 rows=61357 width=314) (actual
time=0.008..1676.283 rows=16402 loops=1)
Total runtime: 1700.826 ms
--
scanorama=# CLUSTER hosts_pkey ON hosts ;
CLUSTER
--
scanorama=# EXPLAIN ANALYZE SELECT * FROM hosts;
Seq Scan on hosts (cost=0.00..680.02 rows=16402 width=314) (actual
time=0.008..31.205 rows=16402 loops=1)
Total runtime: 53.635 ms
--
scanorama=# SELECT * from pg_stat_all_tables WHERE relname LIKE 'hosts';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan |
idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del
++-+--+--+--+---+---+---+---
105805 | public | hosts | 1996430 | 32360280252 | 2736391 |
3301856 | 948 | 1403325 | 737
The information from pg_stat_all_tables is from the last 20 days.
--
INFO: analyzing "public.hosts"
INFO: "hosts": scanned 2536 of 2536 pages, containing 16410 live rows
and 57042 dead rows; 16410 rows in sample, 16410 estimated total rows
INFO: free space map contains 191299 pages in 786 relations
DETAIL: A total of 174560 page slots are in use (including overhead).
174560 page slots are required to track all free space.
Current limits are: 200 page slots, 4000 relations, using 12131 KB.
--
The tables with this 'problem' are not big, so CLUSTER finnish very fast
and it does not have an impact in the access because of locking. But we
wonder why this happens.
Do you need more information?
Thanks in advance.
regards
--
Rafael Martinez, <[EMAIL PROTECTED]>
Center for Information Technology Services
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Need to run CLUSTER to keep performance
Heikki Linnakangas wrote: > Rafael Martinez wrote: >> The tables with this 'problem' are not big, so CLUSTER finnish very fast >> and it does not have an impact in the access because of locking. But we >> wonder why this happens. > > 2 seconds for seq scanning 12 MB worth of data sounds like a lot. Have > you increased shared_buffers from the default? Which operating system > are you using? Shared memory access is known to be slower on Windows. > This is a server with 8GB of ram, we are using 25% as shared_buffers. Linux RHELAS4 with a 2.6.9-55.0.9.ELsmp kernel / x86_64. > On a small table like that you could run VACUUM every few minutes > without much impact on performance. That should keep the table size in > check. > Ok, we run VACUUM ANALYZE only one time a day, every night. But we would espect the performance to get ok again after running vacuum, and it doesn't. Only CLUSTER helps. I can not see we need to change the max_fsm_pages parameter and pg_class and analyze give us this information today (not long ago a CLUSTER was executed): -- scanorama=# VACUUM VERBOSE ANALYZE hosts; INFO: vacuuming "public.hosts" INFO: index "hosts_pkey" now contains 20230 row versions in 117 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "hosts": found 0 removable, 20230 nonremovable row versions in 651 pages DETAIL: 3790 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.01 sec. INFO: vacuuming "pg_toast.pg_toast_376127" INFO: index "pg_toast_376127_index" now contains 131 row versions in 2 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_376127": found 0 removable, 131 nonremovable row versions in 33 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.hosts" INFO: "hosts": scanned 651 of 651 pages, containing 16440 live rows and 3790 dead rows; 16440 rows in sample, 16440 estimated total rows VACUUM scanorama=# SELECT relname, relpages, reltuples from pg_class WHERE relname LIKE 'hosts'; relname | relpages | reltuples -+--+--- hosts | 651 | 20230 -- Anymore ideas? regards, -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Need to run CLUSTER to keep performance
Heikki Linnakangas wrote: > > If the table is already bloated, a VACUUM won't usually shrink it. It > only makes the space available for reuse, but a sequential scan still > needs to go through a lot of pages. > > CLUSTER on the other hand repacks the tuples and gets rid of all the > unused space on pages. You need to run CLUSTER or VACUUM FULL once to > shrink the relation, but after that frequent-enough VACUUMs should keep > the table size down. > Ok, thanks for the advice. We will try this and will come back with more information. -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Need to run CLUSTER to keep performance
Alvaro Herrera wrote: > Rafael Martinez wrote: >> The 'problem' is that performance decrease during the day and the only >> thing that helps is to run CLUSTER on the table with problems. VACUUM >> ANALYZE does not help. > > Probably because all the live tuples are clustered at the end of the > table, and the initial pages are polluted with dead tuples. Try > vacuuming the table much more often, say every few minutes. > > Your table is 2536 pages long, but it could probably be in the vicinity > of 700 ... > We run VACUUM ANALYZE every 10 minuttes during 2-3 days to see if it helped, but when it didn't we when back to the old configuration (1 time everyday) Yes, after a CLUSTER we are using 517 pages. But the table does not grow much, it is always around 12-20MB, it looks like vacuum works without problems. regards, -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Need to run CLUSTER to keep performance
Tom Lane wrote:
> Rafael Martinez <[EMAIL PROTECTED]> writes:
>> Heikki Linnakangas wrote:
>>> On a small table like that you could run VACUUM every few minutes
>>> without much impact on performance. That should keep the table size in
>>> check.
>
>> Ok, we run VACUUM ANALYZE only one time a day, every night.
>
> There's your problem.
>
> Reading between the lines I gather that you think an update is "free"
> in the sense of not creating a need for vacuum. It's not --- it's
> exactly equivalent to an insert + a delete, and it leaves behind a
> dead row that needs to be vacuumed. If you do a lot of updates, you
> need to vacuum.
>
Hello again
We have more information about this 'problem'.
Tom, we have many other tables which are much bigger and have larger
amount of updates/deletes and are working very well with our actual
vacuum configuration. We are aware of how important is to run vacuum
jobs and we think we have a good understanding of how/why vacuum works.
We think the problem we are seeing sometimes with these small tables is
another thing.
We increased the vacuum analyze jobs, as you all pointed, from one a day
to four every hour (we did not run cluster at all since we started with
this new configuration). We started with this after a fresh 'cluster' of
the table. This has been in production since last week and the
performance of this table only gets worst and worst.
After 4 days with the new maintenance jobs, it took more than 4 sec to
run a select on this table. After running a cluster we are down to
around 50ms. again.
I can not believe 4 vacuum jobs every hour is not enough for this table.
If we see the statistics, it has only ca.67000 updates/day, ca.43
deletes/day and ca.48 inserts/day. This is nothing compare with many of
the systems we are administrating.
What we see in common between these tables (we have seen this a couple
of times before) is:
- Small table size.
- Small amount of tuples in the table (almost constant).
- Large amount of updates compared to inserts/deletes and compared to
the amount of tuples in the table.
You that know the interns of postgres :), can you think of anything that
can be causing this behavior? Any more suggestions? do you need more data?
Thanks in advance :)
We are sending all data we had before the last cluster command and after
it.
--
BEFORE CLUSTER
--
INFO: vacuuming "public.hosts"
INFO: index "hosts_pkey" now contains 99933 row versions in 558 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "hosts": found 0 removable, 99933 nonremovable row versions in
3875 pages
DETAIL: 83623 dead row versions cannot be removed yet.
There were 12079 unused item pointers.
0 pages are entirely empty.
CPU 0.02s/0.03u sec elapsed 0.06 sec.
INFO: vacuuming "pg_toast.pg_toast_376272"
INFO: index "pg_toast_376272_index" now contains 133 row versions in 2
pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_376272": found 0 removable, 133 nonremovable row
versions in 65 pages
DETAIL: 2 dead row versions cannot be removed yet.
There were 127 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.hosts"
INFO: "hosts": scanned 3875 of 3875 pages, containing 16310 live rows
and 83623 dead rows; 16310 rows in sample, 16310 estimated total rows
scanorama=# SELECT age(now(), pg_postmaster_start_time());
age
-
25 days 22:40:01.241036
(1 row)
scanorama=# SELECT pg_size_pretty(pg_relation_size('hosts'));
pg_size_pretty
30 MB
(1 row)
scanorama=# SELECT count(*) from hosts;
count
---
16311
(1 row)
scanorama=# SELECT
relname,relpages,reltuples,reltoastrelid,reltoastidxid from pg_class
where relname = 'hosts';
relname | relpages | reltuples | reltoastrelid | reltoastidxid
-+--+---+---+---
hosts | 3875 |100386 |376276 | 0
(1 row)
scanorama=# SELECT * from pg_stat_all_tables where schemaname = 'public'
and relname = 'hosts';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan |
idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del
++-+--+--+--+---+---+---+---
105805 | public | hosts | 2412159 | 39109243131 | 3244406 |
9870886 | 1208 | 1685525 | 1088
(1 row)
scanorama=# EXPLAIN ANALYZE SELECT * from hosts;
Re: [PERFORM] Need to run CLUSTER to keep performance
Rafael Martinez wrote: > > We have more information about this 'problem'. > Sending this just in case it can help Checking all the log files from these vacuum jobs we have been running, we found one that looks difference from the rest, specially on the amount of removed pages. We are sending also the output before and after the one we are talking about: ### 2007-11-11_0245.log ### COMMAND: /local/opt/pgsql-8.1/bin/psql -h /tmp/pg_sockets/dbpg-meridien -p 5432 scanorama -c 'VACUUM VERBOSE ANALYZE hosts' CODE: 0 OUTPUT: INFO: vacuuming "public.hosts" INFO: index "hosts_pkey" now contains 110886 row versions in 554 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.02s/0.00u sec elapsed 0.87 sec. INFO: "hosts": found 0 removable, 110886 nonremovable row versions in 3848 pages DETAIL: 94563 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.05s/0.03u sec elapsed 0.94 sec. INFO: vacuuming "pg_toast.pg_toast_376272" INFO: index "pg_toast_376272_index" now contains 260 row versions in 2 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_376272": found 0 removable, 260 nonremovable row versions in 65 pages DETAIL: 129 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.02 sec. INFO: analyzing "public.hosts" INFO: "hosts": scanned 3848 of 3848 pages, containing 16323 live rows and 94563 dead rows; 16323 rows in sample, 16323 estimated total rows VACUUM ### 2007-11-11_0301.log ### COMMAND: /local/opt/pgsql-8.1/bin/psql -h /tmp/pg_sockets/dbpg-meridien -p 5432 scanorama -c 'VACUUM VERBOSE ANALYZE hosts' CODE: 0 OUTPUT: INFO: vacuuming "public.hosts" INFO: index "hosts_pkey" now contains 16782 row versions in 556 pages DETAIL: 94551 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.04s/0.09u sec elapsed 590.48 sec. INFO: "hosts": removed 94551 row versions in 3835 pages DETAIL: CPU 0.00s/0.03u sec elapsed 0.10 sec. INFO: "hosts": found 94551 removable, 16695 nonremovable row versions in 3865 pages DETAIL: 372 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.08s/0.16u sec elapsed 590.99 sec. INFO: vacuuming "pg_toast.pg_toast_376272" INFO: index "pg_toast_376272_index" now contains 131 row versions in 2 pages DETAIL: 129 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_376272": removed 129 row versions in 33 pages DETAIL: CPU 0.00s/0.00u sec elapsed 32.05 sec. INFO: "pg_toast_376272": found 129 removable, 131 nonremovable row versions in 65 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 51.96 sec. INFO: analyzing "public.hosts" INFO: "hosts": scanned 3875 of 3875 pages, containing 16323 live rows and 576 dead rows; 16323 rows in sample, 16323 estimated total rows VACUUM ### 2007-11-11_0315.log ### COMMAND: /local/opt/pgsql-8.1/bin/psql -h /tmp/pg_sockets/dbpg-meridien -p 5432 scanorama -c 'VACUUM VERBOSE ANALYZE hosts' CODE: 0 OUTPUT: INFO: vacuuming "public.hosts" INFO: index "hosts_pkey" now contains 17363 row versions in 556 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.00u sec elapsed 1.39 sec. INFO: "hosts": found 0 removable, 17362 nonremovable row versions in 3875 pages DETAIL: 1039 dead row versions cannot be removed yet. There were 94074 unused item pointers. 0 pages are entirely empty. CPU 0.02s/0.02u sec elapsed 1.43 sec. INFO: vacuuming "pg_toast.pg_toast_376272" INFO: index "pg_toast_376272_index" now contains 131 row versions in 2 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_376272": found 0 removable, 131 nonremovable row versions in 65 pages DETAIL: 0 dead row versions cannot be removed yet. There were 129 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.05 sec. INFO: analyzing "public.hosts" INFO: "hosts": scanned 3875 of 3875 pages, containing 16323 live rows and 1040
Re: [PERFORM] Need to run CLUSTER to keep performance
Heikki Linnakangas wrote: > Rafael Martinez wrote: >> DETAIL: 83623 dead row versions cannot be removed yet. > > Looks like you have a long-running transaction in the background, so > VACUUM can't remove all dead tuples. I didn't see that in the vacuum > verbose outputs you sent earlier. Is there any backends in "Idle in > transaction" state, if you run ps? > I don't see any long transaction in progress ( in transaction) and if we run the vacuum jobb manual just after checking this, it still cannot remove the dead rows. Any suggestions cause vacuum cannot remove these dead rows? > In 8.1, CLUSTER will remove those tuples anyway, but it's actually not > correct. With other words, we have to be very carefull to not run CLUSTER on a table been modified inside a transaction if we do not want to lose data? ... Does this mean that if we run a transaction which update/delete many rows, run cluster before the transaction is finnish, and then rollback the transaction after cluster has been executed, all dead rows updated/deleted by the transaction can not be rollbacked back because they are not there anymore? -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Need to run CLUSTER to keep performance
Rafael Martinez wrote: > Heikki Linnakangas wrote: > >> In 8.1, CLUSTER will remove those tuples anyway, but it's actually not >> correct. > > With other words, we have to be very carefull to not run CLUSTER on > a table been modified inside a transaction if we do not want to lose > data? ... > > Does this mean that if we run a transaction which update/delete many > rows, run cluster before the transaction is finnish, and then rollback > the transaction after cluster has been executed, all dead rows > updated/deleted by the transaction can not be rollbacked back because > they are not there anymore? > Stupid question, I could have checked this myself. CLUSTER will wait to be executed until the transaction is finish. I have just checked this. -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM] Performance problems deleting data
Hello
---
Postgresql version: 8.1.10
4GB RAM
2x HP 72GB 10K SAS RAID1/smartarray
---
I have a colleague that is having som performance problems from time to
time when deleting some rows from a table.
We found out that the database having this problem had a severe bloat
problem in many tables and indexes (they were running only autovacuum)
and some misconfiguration in postgresql.conf.
What we did to fix the situation was:
1) Stop the application accessing the database.
2) Change these parameters in postgresql.conf:
-
shared_buffers = 108157
work_mem = 16384
maintenance_work_mem = 262144
max_fsm_pages = 80
wal_buffers = 64
checkpoint_segments = 128
random_page_cost = 2.0
effective_cache_size = 255479
default_statistics_target = 400
-
3) Update /etc/sysctl.conf with new values for kernel.shmmax and
kernel.shmall
3) Run 'VACUUM FULL VERBOSE'
4) Run 'REINDEX DATABASE '
5) Run 'ANALYZE VERBOSE'
6) Define a 'VACUUM VERBOSE ANALYZE' in crontab
7) Start the application.
These changes helped a lot, the size of the database when down from 7GB
to 1GB and most of the deletes work as they are suppose to. But from
time to time a single deletion takes a lot of time to finish. The output
from explain analyze doesn't show anything wrong, as long as I can see.
The definition of the table 'module' is:
-
manage=# \d module
Table "public.module"
Column |Type | Modifiers
---+-+---
moduleid | integer | not null default
nextval('module_moduleid_seq'::regclass)
deviceid | integer | not null
netboxid | integer | not null
module| integer | not null
model | character varying |
descr | character varying |
up| character(1)| not null default 'y'::bpchar
downsince | timestamp without time zone |
Indexes:
"module_pkey" PRIMARY KEY, btree (moduleid)
"module_deviceid_key" UNIQUE, btree (deviceid)
"module_netboxid_key" UNIQUE, btree (netboxid, module)
Check constraints:
"module_up" CHECK (up = 'y'::bpchar OR up = 'n'::bpchar)
Foreign-key constraints:
"$1" FOREIGN KEY (deviceid) REFERENCES device(deviceid) ON UPDATE
CASCADE ON DELETE CASCADE
"$2" FOREIGN KEY (netboxid) REFERENCES netbox(netboxid) ON UPDATE
CASCADE ON DELETE CASCADE
Rules:
close_alerthist_modules AS
ON DELETE TO module DO UPDATE alerthist SET end_time = now()
WHERE (alerthist.eventtypeid::text = 'moduleState'::text OR
alerthist.eventtypeid::text = 'linkState'::text) AND alerthist.end_time
= 'infinity'::timestamp without time zone AND alerthist.deviceid =
old.deviceid
-
manage=# EXPLAIN ANALYZE DELETE FROM module WHERE deviceid='7298';
QUERY PLAN
-
Nested Loop (cost=0.00..14.63 rows=1 width=67) (actual
time=2.365..2.365 rows=0 loops=1)
-> Index Scan using alerthist_end_time_btree on alerthist
(cost=0.00..10.65 rows=1 width=67) (actual time=2.363..2.363 rows=0 loops=1)
Index Cond: (end_time = 'infinity'::timestamp without time zone)
Filter: eventtypeid)::text = 'moduleState'::text) OR
((eventtypeid)::text = 'linkState'::text)) AND (7298 = deviceid))
-> Index Scan using module_deviceid_key on module (cost=0.00..3.96
rows=1 width=4) (never executed)
Index Cond: (deviceid = 7298)
Total runtime: 2.546 ms
Index Scan using module_deviceid_key on module (cost=0.00..3.96 rows=1
width=6) (actual time=0.060..0.061 rows=1 loops=1)
Index Cond: (deviceid = 7298)
Trigger for constraint $1: time=3.422 calls=1
Trigger for constraint $1: time=0.603 calls=1
Total runtime: 2462558.813 ms
(13 rows)
-
Any ideas why it is taking 2462558.813 ms to finish when the total time
for the deletion is 2.546 ms + 3.422 ms + 0.603ms?
The deletion of a row in the 'module' table involves several
deletions/updates in many other tables in the database related by
foreign keys (with ON DELETE CASCADE) and triggers.
I suppose that an open transaction in one of these not directly releated
tables to 'module' could lock the deletion without showing in EXPLAIN
ANALYZE?. The two 'Trigger for constraint' in the EXPLAIN ANALYZE output
only show two ta
Re: [PERFORM] Performance problems deleting data
Tom Lane wrote: > Rafael Martinez <[EMAIL PROTECTED]> writes: > >> Any ideas why it is taking 2462558.813 ms to finish when the total time >> for the deletion is 2.546 ms + 3.422 ms + 0.603ms? > Hei Tom, I got this information from my colleague: > Is the problem repeatable? Repeatable as in about 30+ times every day, the deletion of a row takes more than 100 seconds. I have not found a way to provoke it though. > Is the delay consistent? No. I see frequently everything from below the 8 seconds log_min_duration_statement to about 4900 seconds. As for distribution, about half of the 30+ takes more than 500 seconds to complete, the rest (obviously) between 100 and 500 seconds. > What do you see in pg_locks while it's delaying? locktype| database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted ---+--+--+--+---+---+-+---+--+-+---+--+- relation |16393 |16784 | | | | | | |82179843 | 19890 | AccessShareLock | t relation |16393 |16784 | | | | | | |82179843 | 19890 | RowExclusiveLock | t relation |16393 |17176 | | | | | | |82179843 | 19890 | RowExclusiveLock | t relation |16393 |16794 | | | | | | |82180131 | 19907 | AccessShareLock | t relation |16393 |16794 | | | | | | |82180131 | 19907 | RowExclusiveLock | t relation |16393 |16977 | | | | | | |82179843 | 19890 | AccessShareLock | t relation |16393 |16977 | | | | | | |82179843 | 19890 | RowExclusiveLock | t relation |16393 |16800 | | | | | | |82179669 | 19906 | AccessShareLock | t relation |16393 |16800 | | | | | | |82179669 | 19906 | RowExclusiveLock | t relation |16393 |17174 | | | | | | |82179843 | 19890 | RowExclusiveLock | t transactionid | | | | | 80430155 | | | |80430155 | 29569 | ExclusiveLock| t relation |16393 |17164 | | | | | | |82179843 | 19890 | AccessShareLock | t relation |16393 |16816 | | | | | | |82179669 | 19906 | AccessShareLock | t relation |16393 |16816 | | | | | | |82179669 | 19906 | RowExclusiveLock | t relation |16393 |16812 | | | | | | |82179669 | 19906 | AccessShareLock | t relation |16393 |16812 | | | | | | |82179669 | 19906 | RowExclusiveLock | t relation |16393 |17174 | | | | | | |82180131 | 19907 | RowExclusiveLock | t relation |16393 |16977 | | | | | | |82180131 | 19907 | AccessShareLock | t relation |16393 |16977 | | | | | | |82180131 | 19907 | RowExclusiveLock | t relation |16393 |16784 | | | | | | |82180131 | 19907 | AccessShareLock | t relation |16393 |16784 | | | | | | |82180131 | 19907 | RowExclusiveLock | t relation |16393 |16766 | | | | | | |82179843 | 19890 | AccessShareLock | t relation |16393 |16766 | | | | | | |82179843 | 19890 | RowExclusiveLock | t relation |16393 |16977 | | | | | | |82179669 | 19906 | AccessShareLock | t relation |16393 |16977 | | | | | | |82179669 | 19906 | RowExclusiveLock | t relation |16393 |17164 | | | | | | |82179669 | 19906 | AccessShareLock | t relation |16393 |16766 | | | | | | |82180131 | 19907 | AccessShareLock | t relation |16393 |16766 | | | | | | |82180131 | 19907 | RowEx
[PERFORM] SQL With Dates
Hello People, I have initiated a work to review the sqls of our internal software. Lot of them he problem are about sql logic, or join with table unecessary, and so on. But software has lot of sql with date, doing thinks like: [..] date >= '2009-04-01' AND date <= '2009-04-15' [..] Redoing the SQL with fix date (date = '2009-04-01') o cost in explain always still about 200 or less. But with a period the cost is high, about 6000 or more. Select is using Index and the date is using index too. There is some way to use date period with less cost? Rafael Domiciano
Re: [PERFORM] SQL With Dates
Hello Grzegorz, Thnks for response, but lot of the selects is using BETWEEN and the cost is the same. 2009/4/20 Grzegorz Jaśkiewicz > BETWEEN X AND Y > > On Mon, Apr 20, 2009 at 2:55 PM, Rafael Domiciano > wrote: > > Hello People, > > > > I have initiated a work to review the sqls of our internal software. > > Lot of them he problem are about sql logic, or join with table > unecessary, > > and so on. > > But software has lot of sql with date, doing thinks like: > > [..] > > date >= '2009-04-01' AND > > date <= '2009-04-15' > > [..] > > > > Redoing the SQL with fix date (date = '2009-04-01') o cost in explain > always > > still about 200 or less. But with a period the cost is high, about 6000 > or > > more. > > > > Select is using Index and the date is using index too. > > > > There is some way to use date period with less cost? > > > > Rafael Domiciano > > > > > > -- > GJ >
Re: [PERFORM] Query planner making bad decisions
Cory Coager wrote: > I'm running version 8.1.11 on SLES 10 SP2. I'm trying to improve this > query and unfortunately I cannot change the application. For some > reason the planner is making a bad decision sometimes after an analyze > of table objectcustomfieldvalues. > > The query is: > SELECT DISTINCT main.* FROM Tickets main JOIN CustomFields > CustomFields_1 ON ( CustomFields_1.Name = 'QA Origin' ) JOIN > CustomFields CustomFields_3 ON (CustomFields_3.Name = 'QA Group Code' ) > JOIN ObjectCustomFieldValues ObjectCustomFieldValues_4 ON > (ObjectCustomFieldValues_4.ObjectId = main.id ) AND ( > ObjectCustomFieldValues_4.Disabled = '0' ) AND > (ObjectCustomFieldValues_4.ObjectType = 'RT::Ticket' ) AND ( > ObjectCustomFieldValues_4.CustomField = CustomFields_3.id ) JOIN > ObjectCustomFieldValues ObjectCustomFieldValues_2 ON ( > ObjectCustomFieldValues_2.Disabled = '0' ) AND > (ObjectCustomFieldValues_2.ObjectId = main.id ) AND ( > ObjectCustomFieldValues_2.CustomField = CustomFields_1.id ) AND > (ObjectCustomFieldValues_2.ObjectType = 'RT::Ticket' ) WHERE > (main.Status != 'deleted') AND (main.Queue = '60' AND > ObjectCustomFieldValues_2.Content LIKE '%Patient Sat Survey%' AND > ObjectCustomFieldValues_4.Content LIKE'%MOT%') AND (main.EffectiveId = > main.id) AND (main.Type = 'ticket') ORDER BY main.id ASC; > > Hello Just in case you want this information. Our RT installation running on 8.3.6 / RHEL4 and with default_statistics_target=100 gives us this query plan: Unique (cost=1360.05..1360.12 rows=1 width=161) (actual time=2141.834..2141.834 rows=0 loops=1) -> Sort (cost=1360.05..1360.06 rows=1 width=161) (actual time=2141.831..2141.831 rows=0 loops=1) Sort Key: main.effectiveid, main.issuestatement, main.resolution, main.owner, main.subject, main.initialpriority, main.finalpriority, main.priority, main.timeestimated, main.timeworked, main.status, main.timeleft, main.told, main.starts, main.started, main.due, main.resolved, main.lastupdatedby, main.lastupdated, main.creator, main.created, main.disabled Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=14.14..1360.04 rows=1 width=161) (actual time=2141.724..2141.724 rows=0 loops=1) -> Nested Loop (cost=14.14..1358.09 rows=1 width=165) (actual time=2141.717..2141.717 rows=0 loops=1) -> Nested Loop (cost=14.14..1356.14 rows=1 width=169) (actual time=2141.715..2141.715 rows=0 loops=1) -> Nested Loop (cost=14.14..1348.69 rows=1 width=169) (actual time=2141.711..2141.711 rows=0 loops=1) -> Bitmap Heap Scan on tickets main (cost=14.14..1333.78 rows=2 width=161) (actual time=0.906..26.413 rows=1046 loops=1) Recheck Cond: (queue = 60) Filter: (((status)::text <> 'deleted'::text) AND (effectiveid = id) AND ((type)::text = 'ticket'::text)) -> Bitmap Index Scan on tickets1 (cost=0.00..14.14 rows=781 width=0) (actual time=0.662..0.662 rows=1188 loops=1) Index Cond: (queue = 60) -> Index Scan using objectcustomfieldvalues3 on objectcustomfieldvalues objectcustomfieldvalues_2 (cost=0.00..7.44 rows=1 width=8) (actual time=2.017..2.017 rows=0 loops=1046) Index Cond: ((objectcustomfieldvalues_2.disabled = 0) AND (objectcustomfieldvalues_2.objectid = main.effectiveid) AND ((objectcustomfieldvalues_2.objecttype)::text = 'RT::Ticket'::text)) Filter: ((objectcustomfieldvalues_2.content)::text ~~ '%Patient Sat Survey%'::text) -> Index Scan using objectcustomfieldvalues3 on objectcustomfieldvalues objectcustomfieldvalues_4 (cost=0.00..7.44 rows=1 width=8) (never executed) Index Cond: ((objectcustomfieldvalues_4.disabled = 0) AND (objectcustomfieldvalues_4.objectid = main.effectiveid) AND ((objectcustomfieldvalues_4.objecttype)::text = 'RT::Ticket'::text)) Filter: ((objectcustomfieldvalues_4.content)::text ~~ '%MOT%'::text) -> Index Scan using customfields_pkey on customfields customfields_3 (cost=0.00..1.94 rows=1 width=4) (never executed) Index Cond: (customfields_3.id = objectcustomfieldvalues_4.customfield) Filter: ((customfields_3.name)::text = 'QA Group Code'::text) -> Index Scan using customfields_pkey on customfields customfields_1 (cost=0.00..1.94 rows=1
Re: [PERFORM] raid setup for db
Thomas Finneid wrote: > Hi > > I am wondering what stripe size, on a raid 0, is the most suitable for > postgres 8.2? > Hello Raid 0 for a database? This is a disaster waiting to happen. Are you sure you want to use raid0? regards -- Rafael Martinez, Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] WAL partition filling up after high WAL activity
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/09/2011 05:06 PM, Greg Smith wrote: > On 11/07/2011 05:18 PM, Richard Yen wrote: >> My biggest question is: we know from the docs that there should be no >> more than (2 + checkpoint_completion_target) * checkpoint_segments + 1 >> files. For us, that would mean no more than 48 files, which equates >> to 384MB--far lower than the 9.7GB partition size. **Why would WAL >> use up so much disk space?** >> > > That's only true if things are operating normally. There are at least > two ways this can fail to be a proper upper limit on space used: > > 1) You are archiving to a second system, and the archiving isn't keeping > up. Things that haven't been archived can't be re-used, so more disk > space is used. > > 2) Disk I/O is slow, and the checkpoint writes take a significant period > of time. The internal scheduling assumes each individual write will > happen without too much delay. That assumption can easily be untrue on > a busy system. The worst I've seen now are checkpoints that take 6 > hours to sync, where the time is supposed to be a few seconds. Disk > space in that case was a giant multiple of checkpoint_segments. (The > source of that problem is very much improved in PostgreSQL 9.1) > Hello We have a similar case in june but we did not find the cause of our problem. More details and information: http://archives.postgresql.org/pgsql-docs/2011-06/msg7.php Your explanation in 2) sounds like a good candidate for the problem we had. As I said in june, I think we need to improve the documentation in this area. A note in the documentation about what you have explained in 2) with maybe some hints about how to find out if this is happening will be a great improvement. We did not understand why we experienced this problem in june when creating a GIN index on a tsvector column. But we found out that a lot of the tsvector data was generated from "garbage" data (base64 encoding of huge attachments). When we generated the right tsvector data, the creation of the GIN index ran smoothly and the problem with extra WAL files disappeared. PS.- In our case, the disk space used by all the extra WAL files was almost the equivalent to the 17GB of our GIN index. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk688LoACgkQBhuKQurGihTbvQCfaSBdYNF2oOtErcx/e4u0Zw1J pLIAn2Ztdbuz33es2uw8ddSIjj8UXe3s =olkD -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Dramatic change in memory usage with version 9.1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello I am sending this email to ask if anyone has noticed a change in how a server running postgreSQL 9.1 uses and allocates memory compared to older versions. We upgraded all our systems from 8.3 to 9.1 a couple of weeks ago, and we have experienced a radical change in how our servers make use of memory. How memory is allocated has become more unstable and the swap usage has increased dramatically. The pattern that we have started seeing is: * Sudden decrease of swap when running backup/vacuum+analyze jobs * Full use of cached memory when running backup/vacuum+analyze jobs * Sudden increase of swap and unused memory when backup/vacuum+analyze jobs are finnished. * Progressive decrease of swap during the day. Here is a list of things about this upgrade to version 9.1 that can be interesting when analyzing this change of behavior: * The servers are running the samme OS version and linux kernel as with 8.3. * We are running the same values for parameters related to memory allocation as we used in 8.3. * We are running the same backups and maintenance jobs as with version 8.3. These jobs are running at the exactly same time as with 8.3. * Backups (PITR, pg_dumps) and maintenances (vacuum, analyze) jobs are executed between midnight and early morning. * We run several postgreSQL clusters per server, running in different IPs and disks. * We have not seen any significant change in how databases are used/accessed after the upgrade to 9.1. * We upgraded in the first time from 8.3.12 to 9.1.2, but because this bug: http://archives.postgresql.org/pgsql-bugs/2011-12/msg00068.php we had to downgrade to 9.1.1. We thought in the begynning that our memory problems were related to this bug, but everything is the same with 9.1.1. * A couple of days ago we decreased the values of maintenance_work_mem and work_mem over a 50% in relation to values used with 8.3. The only change we have seen is even more unused memory after backup/vacuum +analyze jobs are finnished. Here you have some graphs that can help to get a picture about what we are talking about: * Overview of how memory use changed in one of our servers after the upgrade in the begynning og week 49: http://folk.uio.no/rafael/upgrade_to_9.1/server-1/memory-month.png http://folk.uio.no/rafael/upgrade_to_9.1/server-1/memory-year.png * We could think that all this happens because we are running to much in one server. Here are some graphs from a server with 30GB+ running only one postgres cluster (shared_memory = 6GB, maintenance_work_memory = 512MB, work_mem = 32MB) for a couple of days: http://folk.uio.no/rafael/upgrade_to_9.1/server-2/memory-week.png The memory pattern is the same even when running only one postgres cluster in a server with enough memory. Any ideas about why this dramatic change in memory usage when the only thing apparently changed from our side is the postgres version? Thanks in advance for any help. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk7vUpYACgkQBhuKQurGihTvjACff5J08pNJuRDgkegYdtQ5zp52 GeoAnRaaU+F/C/udQ7lMl/TkvRKX2WnP =VcDk -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Dramatic change in memory usage with version 9.1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/20/2011 12:15 PM, Cédric Villemain wrote: > Le 19 décembre 2011 16:04, Rafael Martinez a écrit > : >> -BEGIN PGP SIGNED MESSAGE- >> Hash: SHA1 >> >> Hello >> >> I am sending this email to ask if anyone has noticed a change in how >> a server running postgreSQL 9.1 uses and allocates memory compared to >> older versions. >> >> We upgraded all our systems from 8.3 to 9.1 a couple of weeks ago, and >> we have experienced a radical change in how our servers make use of >> memory. How memory is allocated has become more unstable and the swap >> usage has increased dramatically. >> [...] > > Can you report what is filling the cache and the swap ? > Hello We are running RHEL4 with a 2.6.9 kernels and we do not know how to check how much swap a particular process is using. It looks like with kernels > 2.6.16 you can get this informaton via /proc/PID/smaps. We have been able to run some tests and we think we have found a reason for the change in memory usage with version 9.1 It looks like it is a combination of how pg_dump works now and how the operative system manages memory. What we have found out is that the server process attending to pg_dump uses much more memory with 9.1 than with 8.3 dumping the same database. This is the test we have done with 8.3 and 9.1: * Clean reboot of the server. * Clean start of postgres server * One unique process running against postgres: pgdump -c --verbose | gzip > dump_file.dump.gz * DBsize = 51GB+ * shared_buffers = 2GB * work_mem = 16MB * maintenance_work_mem = 256MB * Total server memory = 8GB * We have collected data via /proc of how the system has been using memory and VSIZE, RSS and SHARE memory values for all postgres processes. Some graphs showing what happens during the dump of the database with 9.1 and 8.3 can be consulted here: http://folk.uio.no/rafael/upgrade_to_9.1/test/ As you can see, the server process with 9.1 memory usage grows more than the dobbel of the value defined with shared_buffers. With 8.3 is half of this. What we have seen in these tests corresponds with what we have seen in production Ref:[1]. The 'cached' memory follows the 'inactive' memory when this one gets over a certain limit. And 'active' and 'inactive' memory cross their paths and exchange roles. We have not experienced the use of swap under these tests as we do in production probably because we are not running several jobs in parallel. So the drop in 'cached' memory we see in production is not related to the termination of a backup or maintenance job, it is related to how much 'inactive' memory the system has. It looks like some kernel limit is reached and the kernel starts to reallocate how the memory is used. What it's clear is that: * Running pg_dump needs/uses much more memory with 9.1 than with 8.3 (33% more). The same job takes 15min.(18%) more with 9.1 than 8.3 * With 9.1 the assignation the system does of wich memory is 'active' and wich one is 'inactive' has changed Ref:[2]. We still has some things to find out: * We are not sure why swap usage has increased dramatically. We have in theory a lot of memory 'cached' that could be used instead of swap. * We still do not understand why the assignation of which memory is 'active' and which one is 'inactive' has such an impact in how memory is managed. * We are trying to find out if the kernel has some memory parameters that can be tunned to change the behavior we are seeing. [1] http://folk.uio.no/rafael/upgrade_to_9.1/server-1/memory-week.png [2] http://folk.uio.no/rafael/upgrade_to_9.1/server-1/memory-month.png Thanks in advance to anyone trying to find an explanation. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.14 (GNU/Linux) iEYEARECAAYFAk7yGjYACgkQBhuKQurGihTeHwCggv0yjskln8OkW2g5Kj6T4YGR jekAn3FhUbCUR0RjXS+LLJpyzAGNQjys =lBqa -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Dramatic change in memory usage with version 9.1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/21/2011 12:48 AM, Craig Ringer wrote: > On 19/12/2011 11:04 PM, Rafael Martinez wrote: >> Any ideas about why this dramatic change in memory usage when the only >> thing apparently changed from our side is the postgres version? >> > It'd be interesting to know how much of your workload operates with > SERIALIZABLE transactions, as the behavior of those has changed > significantly in 9.1 and they _are_ more expensive in RAM terms now. > Hello As long as I know, all the databases are using the default, "read committed". We have almost 500 databases across all our servers, but we are only dbas. We provide the infrastructure necessary to run this and help users when they need it but we have not 100% control over how they are using the databases ;-) regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.14 (GNU/Linux) iEYEARECAAYFAk7yHHAACgkQBhuKQurGihQz1gCdGJY6vk89lHKMldkYlkxOeJYJ GSMAoKDRCRo1UpqlUgItzCm/XV9aCbb8 =7f6R -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Dramatic change in memory usage with version 9.1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/22/2011 12:29 AM, Havasvölgyi Ottó wrote: > Hello, > > Can you find some relation between the memory usage and insert > statements? 9.1.2 has memory problems with inserts (even the simplest > ones) on Linux and Windows too, I could produce it. Using pgbench also > shows it. Some memory is not reclaimed. > I could produce it also with 8.4.9 on Linux, I haven't tried 8.4.10 yet. > [...] Hello Are you thinking about this bug?: http://archives.postgresql.org/pgsql-bugs/2011-12/msg00068.php Our problem should not have anything to do with this bug (it was introduced in 9.1.2) We could not finish a full import of some of our databases with 9.1.2 because all ram+swap was used in a matter of minuttes. We are using 9.1.1 and we haven't seen the 9.1.2 behavior. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.14 (GNU/Linux) iEYEARECAAYFAk7y8aUACgkQBhuKQurGihTD8gCgk0Frrd/mEjQrIgG9K0dzhNxN HzcAnRiQKWBgwZaNSmY+zrGjYSJFva9o =zcv3 -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] DBD-Pg prepared statement versus plain execution
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello We are having some performance problems with an application that uses prepared statement heavily. We have found out that it creates-executes-destroys a prepared statement *per* statement it sends to the database (pg-9.1) via DBD-Pg. A normal log entry for a sql-statement looks e.g. like this: - -- [2012-03-15 14:49:12.484 CET] LOG: duration: 8.440 ms parse dbdpg_p32048_3: SELECT DISTINCT ACL.RightName FROM ACL, Principals, CachedGroupMembers WHERE Principals.id = ACL.PrincipalId AND Principals.PrincipalType = 'Group' AND Principals.Disabled = 0 AND CachedGroupMembers.GroupId = ACL.PrincipalId AND CachedGroupMembers.GroupId = Principals.id AND CachedGroupMembers.MemberId = 19312 AND CachedGroupMembers.Disabled = 0 AND ((ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1) OR (ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1)) [2012-03-15 14:49:12.485 CET] LOG: duration: 0.087 ms bind dbdpg_p32048_3: SELECT DISTINCT ACL.RightName FROM ACL, Principals, CachedGroupMembers WHERE Principals.id = ACL.PrincipalId AND Principals.PrincipalType = 'Group' AND Principals.Disabled = 0 AND CachedGroupMembers.GroupId = ACL.PrincipalId AND CachedGroupMembers.GroupId = Principals.id AND CachedGroupMembers.MemberId = 19312 AND CachedGroupMembers.Disabled = 0 AND ((ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1) OR (ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1)) [2012-03-15 14:49:12.487 CET] LOG: duration: 1.692 ms execute dbdpg_p32048_3: SELECT DISTINCT ACL.RightName FROM ACL, Principals, CachedGroupMembers WHERE Principals.id = ACL.PrincipalId AND Principals.PrincipalType = 'Group' AND Principals.Disabled = 0 AND CachedGroupMembers.GroupId = ACL.PrincipalId AND CachedGroupMembers.GroupId = Principals.id AND CachedGroupMembers.MemberId = 19312 AND CachedGroupMembers.Disabled = 0 AND ((ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1) OR (ACL.ObjectType = 'RT::System' AND ACL.ObjectId = 1)) [2012-03-15 14:49:12.488 CET] LOG: duration: 0.029 ms statement: DEALLOCATE dbdpg_p32048_3 - -- As you can see, the parse+bind+deallocate part uses much more time than the execution part. This is the same for many of the statements send to the database. My question is: Is the parse+bind time reported, a time (not reported) that the planer will use anyway when running a sql-statement in a normal way or the parse+bind+deallocate time is *extra* time needed by the prepared statement? Can we assume that running this application without using prepared statements will do that it runs faster the time used by parse+bind+deallocate? Thanks in advance. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk9pubAACgkQBhuKQurGihTYkwCcCFYQRDGWD0yaR+f2FFwKs7gN RfgAoJdPrAzUhfBfsXmst7/l7LVLisHy =l7Fl -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] timing != log duration
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello I am wondering why the time reported by \timing in psql is not the same as the time reported by duration in the log file when log_duration or log_min_duration_statement are on?. I can not find any information about this in the documentation. e.g. - --- ver=# SELECT * from version ; Time: 0.450 ms 2012-03-20 16:10:16 CET 29119 LOG: duration: 0.313 ms statement: SELECT * from version ; - --- ver=# PREPARE foo AS SELECT * from version ; PREPARE Time: 0.188 ms ver=# EXECUTE foo; Time: 0.434 ms ver=# DEALLOCATE foo; DEALLOCATE Time: 0.115 ms 2012-03-20 16:12:21 CET 29119 LOG: duration: 0.127 ms statement: PREPARE foo AS SELECT * from version ; 2012-03-20 16:12:37 CET 29119 LOG: duration: 0.303 ms statement: EXECUTE foo; 2012-03-20 16:13:24 CET 29119 LOG: duration: 0.055 ms statement: DEALLOCATE foo; - --- Thanks in advance regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk9pvoUACgkQBhuKQurGihRf3gCfRMv5dQnNA8f/gjcPMv6OPrGz qHoAn0PPgN1OYMBDQqJes3kRBxH//Y95 =rsAY -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 9.2.2 - semop hanging
Hello all you guys,
Since saturday I'm get stucked in a very strange situation: from time to
time (sometimes with intervals less than 10 minutes), the server get
"stucked"/"hang" (I dont know how to call it) and every connections on
postgres (dont matter if it's SELECT, UPDATE, DELETE, INSERT, startup,
authentication...) seems like get "paused"; after some seconds (say ~10 or
~15 sec, sometimes less) everything "goes OK".
So, my first trial was to check disks. Running "iostat" apparently showed
that disks was OK. It's a Raid10, 4 600GB SAS, IBM Storage DS3512, over FC.
IBM DS Storage Manager says that disks is OK.
Then, memory. Apparently no swap being used:
[###@### data]# free -m
total used free sharedbuffers cached
Mem:145182 130977 14204 0 43 121407
-/+ buffers/cache: 9526 135655
Swap: 6143 65 6078
No error on /var/log/messages.
Following, is some strace of one processes, and some others, maybe, useful
infos. Every processes I've straced bring the same scenario: seems it get
stucked on semop.
There's no modification in server since last monday, that I changed
pg_hba.conf to login in LDAP. The LDAP Server apparently is OK, and tcpdump
doesnt show any slow on response, neither big activity on this port.
Any help appreciate,
[###@### ~]# strace -ttp 5209
Process 5209 attached - interrupt to quit
09:01:54.122445 semop(2293765, {{15, -1, 0}}, 1) = 0
09:01:55.368785 semop(2293765, {{15, -1, 0}}, 1) = 0
09:01:55.368902 semop(2523148, {{11, 1, 0}}, 1) = 0
09:01:55.368978 semop(2293765, {{15, -1, 0}}, 1) = 0
09:01:55.369861 semop(2293765, {{15, -1, 0}}, 1) = 0
09:01:55.370648 semop(3047452, {{6, 1, 0}}, 1) = 0
09:01:55.370694 semop(2293765, {{15, -1, 0}}, 1) = 0
09:01:55.370762 semop(2785300, {{12, 1, 0}}, 1) = 0
09:01:55.370805 access("base/2048098929", F_OK) = 0
09:01:55.370953 open("base/2048098929/PG_VERSION", O_RDONLY) = 5
[###@### data]# ipcs -l
- Shared Memory Limits -
max number of segments = 4096
max seg size (kbytes) = 83886080
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1
-- Semaphore Limits
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 32
semaphore max value = 32767
-- Messages: Limits
max queues system wide = 32768
max size of message (bytes) = 65536
default max size of queue (bytes) = 65536
[###@### data]# ipcs -u
- Semaphore Status ---
used arrays: 34
allocated semaphores: 546
[###@### data]# uname -a
Linux ### 2.6.32-279.14.1.el6.x86_64 #1 SMP Tue Nov 6 23:43:09 UTC 2012
x86_64 x86_64 x86_64 GNU/Linux
postgres=# select version();
version
--
PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6
20120305 (Red Hat 4.4.6-4), 64-bit
(1 registro)
[###@### data]# cat /etc/redhat-release
CentOS release 6.3 (Final)
Re: [PERFORM] 9.2.2 - semop hanging
Hello guys,
I've been trying to "hunting down" my problem and reached the following:
1) Emre Hasegeli has suggested to reduce my shared buffers, but it's
already low:
total server memory: 141 GB
shared_buffers: 16 GB
Maybe it's too low? I've been thinking to increase to 32 GB.
max_connections = 500 and ~400 connections average
2) Being "hanging" on "semop" I tried the following, as suggested on some
"tuning page" over web.
echo "250 32000 100 128" > /proc/sys/kernel/sem
3) I think my problem could be something related to "LwLocks", as I did
some googling and found some related problems and slides. There is some way
I can confirm this?
4) Rebooting the server didn't make any difference.
Appreciate any help,
Rafael
On Tue, Jun 11, 2013 at 9:48 AM, Rafael Domiciano <
rafael.domici...@gmail.com> wrote:
> Hello all you guys,
>
> Since saturday I'm get stucked in a very strange situation: from time to
> time (sometimes with intervals less than 10 minutes), the server get
> "stucked"/"hang" (I dont know how to call it) and every connections on
> postgres (dont matter if it's SELECT, UPDATE, DELETE, INSERT, startup,
> authentication...) seems like get "paused"; after some seconds (say ~10 or
> ~15 sec, sometimes less) everything "goes OK".
>
> So, my first trial was to check disks. Running "iostat" apparently showed
> that disks was OK. It's a Raid10, 4 600GB SAS, IBM Storage DS3512, over FC.
> IBM DS Storage Manager says that disks is OK.
>
> Then, memory. Apparently no swap being used:
> [###@### data]# free -m
> total used free sharedbuffers cached
> Mem:145182 130977 14204 0 43 121407
> -/+ buffers/cache: 9526 135655
> Swap: 6143 65 6078
>
> No error on /var/log/messages.
>
> Following, is some strace of one processes, and some others, maybe, useful
> infos. Every processes I've straced bring the same scenario: seems it get
> stucked on semop.
>
> There's no modification in server since last monday, that I changed
> pg_hba.conf to login in LDAP. The LDAP Server apparently is OK, and tcpdump
> doesnt show any slow on response, neither big activity on this port.
>
> Any help appreciate,
>
> [###@### ~]# strace -ttp 5209
> Process 5209 attached - interrupt to quit
> 09:01:54.122445 semop(2293765, {{15, -1, 0}}, 1) = 0
> 09:01:55.368785 semop(2293765, {{15, -1, 0}}, 1) = 0
> 09:01:55.368902 semop(2523148, {{11, 1, 0}}, 1) = 0
> 09:01:55.368978 semop(2293765, {{15, -1, 0}}, 1) = 0
> 09:01:55.369861 semop(2293765, {{15, -1, 0}}, 1) = 0
> 09:01:55.370648 semop(3047452, {{6, 1, 0}}, 1) = 0
> 09:01:55.370694 semop(2293765, {{15, -1, 0}}, 1) = 0
> 09:01:55.370762 semop(2785300, {{12, 1, 0}}, 1) = 0
> 09:01:55.370805 access("base/2048098929", F_OK) = 0
> 09:01:55.370953 open("base/2048098929/PG_VERSION", O_RDONLY) = 5
>
> [###@### data]# ipcs -l
>
> - Shared Memory Limits -
> max number of segments = 4096
> max seg size (kbytes) = 83886080
> max total shared memory (kbytes) = 17179869184
> min seg size (bytes) = 1
>
> -- Semaphore Limits
> max number of arrays = 128
> max semaphores per array = 250
> max semaphores system wide = 32000
> max ops per semop call = 32
> semaphore max value = 32767
>
> -- Messages: Limits
> max queues system wide = 32768
> max size of message (bytes) = 65536
> default max size of queue (bytes) = 65536
>
> [###@### data]# ipcs -u
> - Semaphore Status ---
> used arrays: 34
> allocated semaphores: 546
>
> [###@### data]# uname -a
> Linux ### 2.6.32-279.14.1.el6.x86_64 #1 SMP Tue Nov 6 23:43:09 UTC 2012
> x86_64 x86_64 x86_64 GNU/Linux
>
> postgres=# select version();
>version
>
> --
> PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6
> 20120305 (Red Hat 4.4.6-4), 64-bit
> (1 registro)
>
> [###@### data]# cat /etc/redhat-release
> CentOS release 6.3 (Final)
>
[PERFORM] SQL statement over 500% slower with 9.2 compared with 9.1
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello
We have a SQL statement that with 9.1 takes ca 4000ms to finnish and
with 9.2 over 22000ms.
The explain analyze information is here:
With 9.1.:
http://explain.depesz.com/s/5ou
With 9.2
http://explain.depesz.com/s/d4vU
The SQL statement is:
SELECT firstname || ' ' || lastname AS Name
FROMPerson R
WHERE R.gender like 'F'
AND 19 < (SELECT COUNT(DISTINCT filmId)
FROM FilmParticipation F
WHERE F.partType = 'director' AND
F.personId = R.personId)
AND NOT EXISTS (
SELECT *
FROMFilmParticipation D
WHERE D.partType = 'director'
AND D.personId = R.personId
AND NOT EXISTS (
SELECT *
FROMFilmParticipation C
WHERE C.partType = 'cast'
AND C.filmId = D.filmId
AND C.personId = D.personId
)
)
;
The tables information:
# SELECT count(*) from filmparticipation;
count
- --
10835351
(1 row)
# SELECT pg_size_pretty(pg_table_size('filmparticipation'));
pg_size_pretty
-
540 MB
(1 row)
# SELECT count(*) from person;
count
- -
1709384
(1 row)
# SELECT pg_size_pretty(pg_table_size('person'));
pg_size_pretty
-
85 MB
(1 row)
We can see that the query plan is very different between versions and
that 9.2 is really wrong with the number of rows involved. Why is 9.2
taking so wrong about the number of rows involved in some parts of the
plan?
Some additional information:
* VACUUM ANALYZE has been run in both databases.
* Both databases are running on servers running RHEL6.3.
* The relevant parameters changed from the default configuration are:
9.1:
-
checkpoint_segments | 128
client_encoding | UTF8
effective_cache_size| 28892MB
maintenance_work_mem| 256MB
max_connections | 400
max_stack_depth | 4MB
random_page_cost| 2
server_encoding | UTF8
shared_buffers | 8026MB
ssl | on
ssl_renegotiation_limit | 0
wal_buffers | 16MB
wal_level | archive
wal_sync_method | fdatasync
work_mem| 16MB
9.2:
-
checkpoint_segments | 128
client_encoding | UTF8
effective_cache_size| 28892MB
maintenance_work_mem| 256MB
max_connections | 400
max_stack_depth | 4MB
random_page_cost| 2
server_encoding | UTF8
shared_buffers | 8026MB
ssl | on
ssl_renegotiation_limit | 0
wal_buffers | 16MB
wal_level | archive
wal_sync_method | fdatasync
work_mem| 16MB
Any ideas on why this is happening and how to fix it?
Thanks in advance for your time.
regards,
- --
Rafael Martinez Guerrero
Center for Information Technology
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.14 (GNU/Linux)
iEYEARECAAYFAlIbSyoACgkQBhuKQurGihTOYwCfWC/ptAuMQ1pxFcplq9bHfBi3
uekAnj+nll/Z2Lr8kFgPAB6Fx0Kop4/0
=3TPA
-END PGP SIGNATURE-
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] SQL statement over 500% slower with 9.2 compared with 9.1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/26/2013 02:33 PM, Rafael Martinez wrote: [] > The SQL statement is: > > SELECT firstname || ' ' || lastname AS Name FROMPerson R WHERE > R.gender like 'F' AND 19 < (SELECT COUNT(DISTINCT filmId) FROM > FilmParticipation F WHERE F.partType = 'director' AND F.personId = > R.personId) AND NOT EXISTS ( SELECT * FROM > FilmParticipation D WHERE D.partType = 'director' AND D.personId > = R.personId AND NOT EXISTS ( SELECT * FROMFilmParticipation > C WHERE C.partType = 'cast' AND C.filmId = D.filmId AND > C.personId = D.personId ) ) ; > > [.] > > We can see that the query plan is very different between versions > and that 9.2 is really wrong with the number of rows involved. Why > is 9.2 taking so wrong about the number of rows involved in some > parts of the plan? > Hei More information: If we turn off enable_indexscan the runtime gets more similar to the one we get with 9.1, we are down to 4200ms. The query plan with this configuration is here: http://explain.depesz.com/s/jVR The question remains the same, why is 9.2 using such a different and bad plan compared to 9.1, when the data and the configuration are the same? regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.14 (GNU/Linux) iEYEARECAAYFAlIcbx8ACgkQBhuKQurGihReJgCcCiEfGQ0rZHcazlN3Ihb2PeCn jOsAnjkh1M0j4r1DQJ4Xb1djZ+y4mji3 =Td8b -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] SQL statement over 500% slower with 9.2 compared with 9.1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/27/2013 11:27 PM, Tomas Vondra wrote: [] > I don't immediately see where's the problem - maybe some other > hacker on this list can. Can you prepare a testcase for this? I.e. > a structure of the tables + data so that we can reproduce it? > Hello Of course, you can download a SQL dump of the tables involved, here: http://folk.uio.no/rafael/filmdatabase_testcase.sql.gz This file is 357M gunzipped and 101M gzipped. When restored in a database it will use 1473MB. # \d+ List of relations Schema | Name| Type | Owner | Size | Description - +---+---+--++- public | filmitem | table | postgres | 41 MB | public | filmparticipation | table | postgres | 540 MB | public | person| table | postgres | 85 MB | (3 rows) [dbpg-hotel-utv:5432/postgres@fdb_testcase][]# \di+ List of relations Schema | Name | Type | Owner | Table | Size | Description - ++---+--+---++- public | filmitempkey | index | postgres | filmitem | 26 MB | public | filmparticipationfilmidindex | index | postgres | filmparticipation | 232 MB | public | filmparticipationpersonidindex | index | postgres | filmparticipation | 232 MB | public | filmparticipationpkey | index | postgres | filmparticipation | 232 MB | public | personlastnameindex| index | postgres | person | 41 MB | public | personpkey | index | postgres | person | 37 MB | regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.14 (GNU/Linux) iEYEARECAAYFAlIdvbkACgkQBhuKQurGihTZ0ACgk5ZpAvBFdhJs7A3xm3h80VhR AX4AoIp+tSeeQtmmQh7ShP5WFI3hS+gp =wK/M -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] SQL statement over 500% slower with 9.2 compared with 9.1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/28/2013 06:10 AM, Jeff Janes wrote: > On Monday, August 26, 2013, Rafael Martinez wrote: Hei > > Could you do explain (analyze, buffers) of these? > With 9.1: http://explain.depesz.com/s/FMe with 9.2: http://explain.depesz.com/s/Z1j > > What happens if you excise the "19 < (select ...)" clause? That > would greatly simplify the analysis, assuming the problem remains. > With 9.1: http://explain.depesz.com/s/DhuV With 9.2: I do not get a result in a reasonable time, after several minuttes I cancel the query. > How many distinct filmId are there? > count - 934752 > > Most directors are not also actors, so there is a strong negative > correlation that PostgreSQL is not aware of. However, I think if > you could get 9.1 to report the same path, it would be just as > wrong on that estimate. But since it doesn't report the same > path, you don't see how wrong it is. > > Try running: > > explain (analyze, buffers) SELECT D.personId FROM > FilmParticipation D WHERE D.partType = 'director' --AND > D.personId = R.personId AND NOT EXISTS ( SELECT * FROM > FilmParticipation C WHERE C.partType = 'cast' AND C.filmId = > D.filmId AND C.personId = D.personId ); > > On both 9.1 and 9.2. > Same result with both: with 9.1: http://explain.depesz.com/s/fdO With 9.2 http://explain.depesz.com/s/gHz regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.14 (GNU/Linux) iEYEARECAAYFAlIdzb4ACgkQBhuKQurGihSGEgCeP6frW7l65IphXFUjw80VMZun qO0An1++ZB7IGQ0MwR4wphWmlcYGXFDD =9fg4 -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] SQL statement over 500% slower with 9.2 compared with 9.1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/28/2013 09:08 PM, Tom Lane wrote: [..] > > If you don't want to do any major rewriting, you could probably > stick an OFFSET 0 into the outer EXISTS sub-select (and/or the > inner one) to get something similar to the 9.1 plan. > Thank you for your help. Using OFFSET 0 in SELECT * FROMFilmParticipation C WHERE C.partType = 'cast' AND C.filmId = D.filmId AND C.personId = D.personId OFFSET 0 give us a result similar to 9.1. This SQL is used as an example in one of the database courses at the University. I will send them this information and they can decide if they want to rewrite the statement or use the OFFSET trick. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.14 (GNU/Linux) iEYEARECAAYFAlIm1/8ACgkQBhuKQurGihRAogCePl6G51w8dfYMruj+qSm4Vsjl coMAn2sjyv6PcfsKhASC7ct0WI4YKRtJ =FdeD -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgresql in a Virtual Machine
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/25/2013 09:01 PM, Lee Nguyen wrote: > Hi, > > Having attended a few PGCons, I've always heard the remark from a > few presenters and attendees that Postgres shouldn't be run inside > a VM. That bare metal is the only way to go. > [] Hello This was true some years ago. In our experience, this is not true anymore if you are not running a very demanding system that will be a challenge even running on metal. It should work well for most use cases if your infrastructure is configured correctly. This year we have moved all our postgreSQL servers (45+) to a VMware cluster running vSphere 5.1. We are also almost finished moving all our oracle databases to this cluster too. More than 100 virtual servers and some thousands databases are running without problems in our VM environment. In our experience, VMware vSphere 5.1 makes a huge different in IO performance compared to older versions. Our tests against a storage solution connected to vm servers and metal servers last year, did not show any particular difference in performance between them. Some tips: * We use a SAN via Fibre Channel to storage our data. Be sure to have enough active FC channels for your load. Do not even think to use NFS to connect your physical nodes to your SAN. * We are using 10GigE to interconnect the physical nodes in our cluster. This helps a lot when moving VM servers between nodes. * Don't use in production the snapshot functionality in VM clusters. * Don't over provision resources, specially memory. * Use paravirtualized drivers. * As usual, your storage solution will define the limits in performance of your VM cluster. We have gained a lot in flexibility and manageability without losing performance, the benefits in these areas are many when you administrate many servers/databases. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.14 (GNU/Linux) iEYEARECAAYFAlKUbjcACgkQBhuKQurGihTpHQCeIDkjR/BFM61V2ft72BYd2SBr sowAnRrscNmByay3KL9iicpGUYcb2hv6 =Qvey -END PGP SIGNATURE- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1
Jean-David Beyer escribió: Gregory Stark wrote (in part): The extra spindles speed up sequential i/o too so the ratio between sequential and random with prefetch would still be about 4.0. But the ratio between sequential and random without prefetch would be even higher. I never figured out how extra spindles help sequential I-O because consecutive logical blocks are not necessarily written consecutively in a Linux or UNIX file system. They try to group a bunch (8 512-bit?) of blocks together, but that is about it. So even if you are reading sequentially, the head actuator may be seeking around anyway. I suppose you could fix this, if the database were reasonably static, by backing up the entire database, doing a mkfs on the file system, and restoring it. This might make the database more contiguous, at least for a while. When I was working on a home-brew UNIX dbms, I used raw IO on a separate disk drive so that the files could be contiguous, and this would work. Similarly, IBM's DB2 does that (optionally). But it is my understanding that postgreSQL does not. OTOH, the large (in my case) cache in the kernel can be helpful if I seek around back and forth to nearby records since they may be in the cache. On my 8 GByte RAM, I have the shared buffers set to 200,000 which should keep any busy stuff in memory, and there are about 6 GBytes of ram presently available for the system I-O cache. I have not optimized anything yet because I am still laundering the major input data to initialize the database so I do not have any real transactions going through it yet. I have 6 SCSI hard drives on two Ultra/320 SCSI controllers. Of the database partitions, sda8 has the write-ahead-log, sdb7 has a few tiny seldom-used tables and pg_log, and sdc1, sdd1, sde1, and sdf1 are just for the other tables. For the data on sd[c-f]1 (there is nothing else on these drives), I keep the index for a table on a different drive from the data. When populating the database initially, this seems to help since I tend to fill one table, or a very few tables, at a time, so the table itself and its index do not contend for the head actuator. Presumably, the SCSI controllers can do simultaneous seeks on the various drives and one transfer on each controller. When loading the database (using INSERTs mainly -- because the input data are gawdawful unnormalized spreadsheets obtained from elsewhere, growing once a week), the system is IO limited with seeks (and rotational latency time). IO transfers average about 1.7 Megabytes/second, although there are peaks that exceed 10 Megabytes/second. If I run pg_restore from a backup tape, I can see 90 Megabyte/second transfer rates for bursts of several seconds at a time, but that is pretty much of a record. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Help with optimizing a sql statement
null default
nextval('acl_id_seq'::text)
principaltype | character varying(25) | not null
principalid | integer | not null
rightname | character varying(25) | not null
objecttype| character varying(25) | not null
objectid | integer | not null default 0
delegatedby | integer | not null default 0
delegatedfrom | integer | not null default 0
Indexes:
"acl_pkey" primary key, btree (id)
"acl1" btree (rightname, objecttype, objectid, principaltype,
principalid)
rttest=# \d groups
Table "public.groups"
Column| Type |
Modifiers
-++-
id | integer| not null default
nextval('groups_id_seq'::text)
name| character varying(200) |
description | character varying(255) |
domain | character varying(64) |
type| character varying(64) |
instance| integer|
Indexes:
"groups_pkey" primary key, btree (id)
"groups1" unique, btree ("domain", instance, "type", id, name)
"groups2" btree ("type", instance, "domain")
********
rttest=# \d cachedgroupmembers"
Table "public.cachedgroupmembers"
Column | Type |
Modifiers
---+--+-
id| integer | not null default
nextval('cachedgroupmembers_id_seq'::text)
groupid | integer |
memberid | integer |
via | integer |
immediateparentid | integer |
disabled | smallint | not null default 0
Indexes:
"cachedgroupmembers_pkey" primary key, btree (id)
"cachedgroupmembers2" btree (memberid)
"cachedgroupmembers3" btree (groupid)
"disgroumem" btree (groupid, memberid, disabled)
--
Rafael Martinez, <[EMAIL PROTECTED]>
Center for Information Technology Services
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[PERFORM] Same SQL, 104296ms of difference between 7.4.12 and 8.0.7
uot;outer"."domain")::text = 'RT::Queue-Role'::text) AND
(("outer"."type")::text = ("inner".principaltype)::text)))
-> Hash Join (cost=4667.85..51078.88 rows=62852
width=727) (actual time=649.028..13602.451 rows=513264 loops=1)
Hash Cond: ("outer".groupid = "inner".id)
-> Merge Join (cost=0.00..32353.73 rows=62852
width=699) (actual time=0.809..6644.928 rows=513264 loops=1)
Merge Cond: ("outer".id = "inner".memberid)
-> Merge Join (cost=0.00..6379.54
rows=15877 width=699) (actual time=0.118..911.395 rows=15866 loops=1)
Merge Cond: ("outer".id = "inner".id)
-> Index Scan using users_pkey on
users main (cost=0.00..1361.01 rows=15880 width=695) (actual
time=0.016..49.141 rows=15880 loops=1)
-> Index Scan using principals_pkey on
principals principals_1 (cost=0.00..4399.08 rows=168394 width=4)
(actual time=0.026..412.688 rows=168409 loops=1)
Filter: ((disabled = 0::smallint)
AND (id <> 1))
-> Index Scan using cachedgroupmembers2 on
cachedgroupmembers cachedgroupmembers_4 (cost=0.00..18647.25
rows=666758 width=8) (actual time=0.008..1513.877 rows=666754 loops=1)
-> Hash (cost=3094.48..3094.48 rows=152548
width=32) (actual time=637.618..637.618 rows=0 loops=1)
-> Seq Scan on groups groups_3
(cost=0.00..3094.48 rows=152548 width=32) (actual time=0.017..333.669
rows=152548 loops=1)
-> Materialize (cost=34.72..35.20 rows=48 width=13)
(actual time=0.001..0.077 rows=54 loops=513264)
-> Seq Scan on acl acl_2 (cost=0.00..34.67
rows=48 width=13) (actual time=0.013..0.850 rows=54 loops=1)
Filter: (((rightname)::text =
'OwnTicket'::text) AND (((objecttype)::text = 'RT::System'::text) OR
((objecttype)::text = 'RT::Queue'::text)))
Total runtime: 108486.306 ms
(21 rows)
--
Rafael Martinez, <[EMAIL PROTECTED]>
Center for Information Technology Services
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [PERFORM] Same SQL, 104296ms of difference between 7.4.12 and
On Fri, 2006-04-07 at 15:31, Richard Huxton wrote: > Rafael Martinez Guerrero wrote: > > Hello > > > > I have a sql statement that takes 108489.780 ms with 8.0.7 in a > > RHEL4/amd64linux server with 2xAMD Opteron(tm) Processor 275 2.00GHz / > > 8GB RAM and only 4193.588 ms with 7.4.12 in a RHEL3/386linux server with > > 2xIntel(R) Xeon(TM) CPU 2.40GHz / 4GB RAM. > > > > Some information: > > > > - There is no IO when I am running the sql, but it uses 99% of the cpu. > > - I run VACUUM VERBOSE ANALYZE in both databases before the test. > > - The databases are identical. > > - No other jobs running when testing. > > - Some different parameters between 7.4.12 and 8.0.7 : > > > > 7.4.12: > > --- > > shared_buffers = 114966 #(15% of ram) > > sort_mem = 16384 > > vacuum_mem = 524288 > > wal_buffers = 64 > > checkpoint_segments = 16 > > effective_cache_size = 383220 #(50% ram) > > random_page_cost = 3 > > default_statistics_target = 100 > > > > 8.0.7: > > -- > > shared_buffers = 250160 #(25% ram) > > work_mem = 8192 > > maintenance_work_mem = 131072 > > wal_buffers = 128 > > checkpoint_segments = 64 > > effective_cache_size = 500321 #(50% ram) > > random_page_cost = 3 > > default_statistics_target = 100 > > > > Any ideas of what I can test/configurate to find out why this happens? > > Thanks in advance. > > I haven't looked in detail at the plans, but what stands out to me is > that you've got a sort with a lot of columns and you've halved sort_mem > (work_mem). Try increasing it (perhaps to 32000 even). > set work_mem = 32000; > > Give that a quick go and see what happens. If it doesn't work, we'll > look at the plans in more detail. I know that this SQL could be done in a much better way, but I can not change it at the moment. work_mem = 16384: - After restarting the database and running the explain two times: 107911.229 ms work_mem = 32768: - After restarting the database and running the explain two times: 103988.337 ms -- Rafael Martinez, <[EMAIL PROTECTED]> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Suspicious top output
Hello, i have a postgresql server running and from time to time it gets painfully slow. When this happens i usually connect to the server and run a "top" command, the output i get is filled with lines like the following 71872 pgsql1 40 48552K 42836K sbwait 1:41 4.79% postgres Are those connections that were not closed or something like that? should i worry? Thanks in advance, as always yours trully Rafael -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
