Re: [PERFORM] PostgreSQL and Linux 2.6 kernel.
Josh Berkus Treating the optimizer as a black box is something I'm very used to from other RDBMS. My question is, how can you explicitly re-write a query now to improve it? If there's no way of manipulating queries without actually re-writing the optimizer, we're now in a position where we aren't able to diagnose when the optimizer isn't working effectively. Well, there is ... all of the various query cost parameters. They are very blunt instruments for such a delicate task. Surely someone of your experience might have benefit from something more? My feeling is, I would, though I want those tools as *a developer* rather than for tuning specific queries for people, which is always so sensitive to upgrades etc. But, ultimately, improvements on the planner are still bottlenecked by having only one developer actually hacking the changes. Do we have a clear list of optimizations we'd like to be working on? The TODO items aren't very related to specific optimizations... The only ones I was aware of was deferred subselect evaluation for DBT-3. ...sounds like there's more to discuss here, so I'll duck out now and get back to my current project... Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: 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] [ SOLVED ] select count(*) very slow on an already
I am running an update on the same table update rfis set inquiry_status='APPROVED' where inquiry_status='a'; Its running for past 20 mins. and top output is below. The PID which is executing the query above is 6712. Can anyone tell me why it is in an uninterruptable sleep and does it relate to the apparent poor performance? Is it problem with the disk hardware. I know at nite this query will run reasonably fast. I am running on a decent hardware . Regds mallah. 1:41pm up 348 days, 21:10, 1 user, load average: 11.59, 13.69, 11.49 85 processes: 83 sleeping, 1 running, 0 zombie, 1 stopped CPU0 states: 8.1% user, 2.3% system, 0.0% nice, 89.0% idle CPU1 states: 3.3% user, 2.3% system, 0.0% nice, 93.2% idle CPU2 states: 7.4% user, 1.4% system, 0.0% nice, 90.0% idle CPU3 states: 9.3% user, 7.4% system, 0.0% nice, 82.2% idle Mem: 2064796K av, 2053964K used, 10832K free, 0K shrd, 22288K buff Swap: 2048244K av, 88660K used, 1959584K free 1801532K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 6712 postgres 16 0 86592 84M 83920 D 11.1 4.1 1:36 postmaster 13103 postgres 15 0 54584 53M 52556 S 3.5 2.6 0:01 postmaster 13034 root 16 0 1072 1072 848 R 2.1 0.0 0:02 top 13064 postgres 15 0 67256 65M 64516 D 2.1 3.2 0:01 postmaster 13088 postgres 16 0 43324 42M 40812 D 2.1 2.0 0:00 postmaster 13076 postgres 15 0 49016 47M 46628 S 1.9 2.3 0:00 postmaster 26931 postgres 15 0 84880 82M 83888 S 1.7 4.1 3:52 postmaster 13107 postgres 15 0 18400 17M 16488 S 1.5 0.8 0:00 postmaster 13068 postgres 15 0 44632 43M 42324 D 1.3 2.1 0:00 postmaster 13074 postgres 15 0 68852 67M 66508 D 1.3 3.3 0:00 postmaster 13108 postgres 15 0 11692 11M 10496 S 1.3 0.5 0:00 postmaster 13075 postgres 15 0 50860 49M 47680 S 1.1 2.4 0:04 postmaster 13066 postgres 15 0 56112 54M 53724 S 0.9 2.7 0:01 postmaster 13109 postgres 15 0 14528 14M 13272 S 0.9 0.7 0:00 postmaster 24454 postgres 15 0 2532 2380 1372 S 0.7 0.1 11:58 postmaster 12 root 15 0 0 0 0 SW 0.5 0.0 816:30 bdflush 24455 postgres 15 0 1600 1476 1380 S 0.5 0.0 9:11 postmaster 12528 postgres 15 0 84676 82M 79920 S 0.3 4.0 0:02 postmaster 12575 postgres 15 0 76660 74M 75796 D 0.3 3.7 0:09 postmaster 13038 postgres 15 0 48952 47M 46436 D 0.3 2.3 0:00 postmaster 13069 postgres 15 0 57464 56M 54852 S 0.3 2.7 0:00 postmaster 13102 postgres 15 0 17864 17M 16504 D 0.3 0.8 0:00 postmaster Richard Huxton wrote: On Thursday 15 April 2004 17:19, Rajesh Kumar Mallah wrote: Bill Moran wrote: Rajesh Kumar Mallah wrote: Hi, The problem was solved by reloading the Table. the query now takes only 3 seconds. But that is not a solution. If dropping/recreating the table improves things, then we can reasonably assume that the table is pretty active with updates/inserts. Correct? Yes the table results from an import process and under goes lots of inserts and updates , but thats before the vacuum full operation. the table is not accessed during vacuum. What i want to know is is there any wat to automate the dumping and reload of a table individually. will the below be safe and effective: Shouldn't be necessary assuming you vacuum (not full) regularly. However, looking back at your original posting, the vacuum output doesn't seem to show any rows that need removing. # VACUUM full verbose eyp_rfi; INFO: vacuuming "public.eyp_rfi" INFO: "eyp_rfi": found 0 removable, 505960 nonremovable row versions in 71987 pages DETAIL: 0 dead row versions cannot be removed yet. Since your select count(*) showed 505960 rows, I can't see how dropping/replacing could make a difference on a sequential scan. Since we're not using any indexes I don't see how it could be related to that. begin work; create table new_tab AS select * from tab; truncate table tab; insert into tab select * from new_tab; drop table new_tab; commit; analyze tab; i havenot tried it but plan to do so. but i feel insert would take ages to update the indexes if any. It will have to update them, which will take time. BTW is there any way to disable checks and triggers on a table temporarily while loading data (is updating reltriggers in pg_class safe?) You can take a look at pg_restore and copy how it does it.
Re: [PERFORM] query slows down with more accurate stats
On Thu, 15 Apr 2004 20:18:49 -0400, Tom Lane [EMAIL PROTECTED] wrote:
getting several tuples from the same page is more likely
than with the old method.
Hm, are you sure?
Almost sure. Let's look at a corner case: What is the probability of
getting a sample with no two tuples from the same page? To simplify the
problem assume that each page contains the same number of tuples c.
If the number of pages is B and the sample size is n, a perfect sampling
method collects a sample where all tuples come from different pages with
probability (in OpenOffice.org syntax):
p = prod from{i = 0} to{n - 1} {{c(B - i)} over {cB - i}}
or in C:
p = 1.0;
for (i = 0; i n; ++i)
p *= c*(B - i) / (c*B - i)
This probability grows with increasing B.
Also, I'm not at all sure that the old method satisfies that constraint
completely in the presence of nonuniform numbers of tuples per page,
so we'd not necessarily be going backwards anyhow ...
Yes, it boils down to a decision whether we want to replace one not
quite perfect sampling method with another not quite perfect method.
I'm still working on putting together the pros and cons ...
Servus
Manfred
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
RESOLVED: Re: [PERFORM] Wierd context-switching issue on Xeon
Tom, Josh, I think we have the problem resolved after I found the following note from Tom: A large number of semops may mean that you have excessive contention on some lockable resource, but I don't have enough info to guess what resource. This was the key to look at: we were missing all indices on table which is used heavily and does lots of locking. After recreating the missing indices the production system performed normal. No, more excessive semop() calls, load way below 1.0, CS over 20.000 very rare, more in thousands realm and less. This is quite a relief but I am sorry that the problem was so stupid and you wasted some time although Tom said he had also seem excessive semop() calls on another Dual XEON system. Hyperthreading was turned off so far but will be turned on again the next days. I don't expect any problems then. I'm not sure if this semop() problem is still an issue but the database behaves a bit out of bounds in this situation, i.e. consuming system resources with semop() calls 95% while tables are locked very often and longer. Thanks for your help, Dirk At last here is the current vmstat 1 excerpt where the problem has been resolved: procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 1 0 2308 232508 201924 697653200 136 464 628 812 5 1 94 0 0 0 2308 232500 201928 69766280096 296 495 484 4 0 95 0 0 1 2308 232492 201928 697662800 0 176 347 278 1 0 99 0 0 0 2308 233484 201928 69765960040 580 443 351 8 2 90 0 1 0 2308 233484 201928 69766960076 692 792 651 9 2 88 0 0 0 2308 233484 201928 697669600 020 13234 0 0 100 0 0 0 2308 233484 201928 697669600 076 17790 0 0 100 0 0 1 2308 233484 201928 697669600 0 216 321 250 4 0 96 0 0 0 2308 233484 201928 697669600 0 116 417 240 8 0 92 0 0 0 2308 233484 201928 69767840048 600 403 270 8 0 92 0 0 0 2308 233464 201928 69768600076 452 1064 2611 14 1 84 0 0 0 2308 233460 201932 69769000032 256 587 587 12 1 87 0 0 0 2308 233460 201932 69769320032 188 379 287 5 0 94 0 0 0 2308 233460 201932 697693200 0 0 103 8 0 0 100 0 0 0 2308 233460 201932 697693200 0 0 10214 0 0 100 0 0 1 2308 233444 201948 697693200 0 348 300 180 1 0 99 0 1 0 2308 233424 201948 69769480016 380 739 906 4 2 93 0 0 0 2308 233424 201948 69770320068 260 724 987 7 0 92 0 0 0 2308 231924 201948 69771280096 344 1130 753 11 1 88 0 1 0 2308 231924 201948 697724800 112 324 687 628 3 0 97 0 0 0 2308 231924 201948 697724800 0 192 575 430 5 0 95 0 1 0 2308 231924 201948 697724800 0 264 208 124 0 0 100 0 0 0 2308 231924 201948 69772640016 272 380 230 3 2 95 0 0 0 2308 231924 201948 697726400 0 0 104 8 0 0 100 0 0 0 2308 231924 201948 697726400 048 25892 1 0 99 0 0 0 2308 231816 201948 697748400 212 268 456 384 2 0 98 0 0 0 2308 231816 201948 697748400 088 453 770 0 0 99 0 0 0 2308 231452 201948 697768000 196 476 615 676 5 0 94 0 0 0 2308 231452 201948 697768000 0 228 431 400 2 0 98 0 0 0 2308 231452 201948 697768000 0 0 23758 3 0 97 0 0 0 2308 231448 201952 697768000 0 0 36584 2 0 97 0 0 0 2308 231448 201952 697768000 040 246 108 1 0 99 0 0 0 2308 231448 201952 6960096 352 606 1026 4 2 94 0 0 0 2308 231448 201952 69600 0 240 295 266 5 0 95 0 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: RESOLVED: Re: [PERFORM] Wierd context-switching issue on Xeon
=?ISO-8859-1?Q?Dirk_Lutzeb=E4ck?= [EMAIL PROTECTED] writes: This was the key to look at: we were missing all indices on table which is used heavily and does lots of locking. After recreating the missing indices the production system performed normal. No, more excessive semop() calls, load way below 1.0, CS over 20.000 very rare, more in thousands realm and less. Hmm ... that's darn interesting. AFAICT the test case I am looking at for Josh's client has no such SQL-level problem ... but I will go back and double check ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] [ SOLVED ] select count(*) very slow on an already
Just a note, I was trying the cluster command and was short on space. I figured I had enough space for the new table and index. It failed on me twice. The reason is that I noticed for the command to complete, it needed the space of the new table and 2x the space of the new index. It looks like it creates the new table, then a new index. Afterwards it looked like it creates another index in the DB pgsql_tmp. So for me this is an important consideration, since the new index size was about 7GB. I had not anticipated the second index size so that is why it failed. I ended up creating a link of pgsql_tmp to another parttion to successfully complete. Dan. -Original Message- From: Bill Moran [mailto:[EMAIL PROTECTED] Sent: Thursday, April 15, 2004 4:14 PM To: Shea,Dan [CIS] Cc: Postgres Performance Subject: Re: [PERFORM] [ SOLVED ] select count(*) very slow on an already Shea,Dan [CIS] wrote: Bill, if you had alot of updates and deletions and wanted to optimize your table, can you just issue the cluster command. Will the cluster command rewrite the table without the obsolete data that a vacuum flags or do you need to issue a vacuum first? From the reference docs: During the cluster operation, a temporary copy of the table is created that contains the table data in the index order. Temporary copies of each index on the table are created as well. Therefore, you need free space on disk at least equal to the sum of the table size and the index sizes. CLUSTER preserves GRANT, inheritance, index, foreign key, and other ancillary information about the table. Because the optimizer records statistics about the ordering of tables, it is advisable to run ANALYZE on the newly clustered table. Otherwise, the optimizer may make poor choices of query plans. The primary reason CLUSTER exists is to allow you to physically reorder a table based on a key. This should provide a performance improvement if data with the same key is accessed all at once. (i.e. if you do SELECT * FROM table WHERE key=5 and it returns 100 rows, those 100 rows are guaranteed to be all on the same part of the disk after CLUSTER, thus a performance improvement should result.) Updates and inserts will add data in the next available space in a table with no regard for any keys, and _may_ require running all over the disk to retrieve the data in the previous example query. I doubt if CLUSTER is an end-all optimization tool. The specific reason I suggested it was because the original poster was asking for an easier way to drop/recreate a table (as prior experimentation had shown this to improve performance) I can't think of anything easier than CLUSTER tablename ON keyname Since CLUSTER recreates the table, it implicitly removes the dead tuples. However, it's going to be a LOT slower than vacuum, so if dead tuples are the main problem, vacuum is still the way to go. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] query slows down with more accurate stats
Manfred Koizar [EMAIL PROTECTED] writes:
If the number of pages is B and the sample size is n, a perfect sampling
method collects a sample where all tuples come from different pages with
probability (in OpenOffice.org syntax):
p = prod from{i = 0} to{n - 1} {{c(B - i)} over {cB - i}}
So? You haven't proven that either sampling method fails to do the
same.
The desired property can also be phrased as every tuple should be
equally likely to be included in the final sample. What we actually
have in the case of your revised algorithm is every page is equally
likely to be sampled, and of the pages included in the sample, every
tuple is equally likely to be chosen. Given that there are B total
pages of which we sample b pages that happen to contain T tuples (in any
distribution), the probability that a particular tuple gets chosen is
(b/B) * (n/T)
assuming that the two selection steps are independent and unbiased.
Now b, B, and n are not dependent on which tuple we are talking about.
You could argue that a tuple on a heavily populated page is
statistically likely to see a higher T when it's part of the page sample
pool than a tuple on a near-empty page is likely to see, and therefore
there is some bias against selection of the former tuple. But given a
sample over a reasonably large number of pages, the contribution of any
one page to T should be fairly small and so this effect ought to be
small. In fact, because T directly determines our estimate of the total
number of tuples in the relation, your experiments showing that the new
method gives a reliable tuple count estimate directly prove that T is
pretty stable regardless of exactly which pages get included in the
sample. So I think this method is effectively unbiased at the tuple
level. The variation in probability of selection of individual tuples
can be no worse than the variation in the overall tuple count estimate.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
[PERFORM] Long running queries degrade performance
Hello all, My apologies if this is not the right mailing list to ask this question, but we are wondering about general performance tuning principles for our main db server. We have a web app with a postgres backend. Most queries have subsecond response times through the web even with high usage. Every once in awhile someone will run either an ad-hoc query or some other long running db process. For some reason, it seems that a small number 3-4 of these jobs running in parallel absolutely floors our server. In monitoring the jobs, linux (Kernel 2.4) drops the long running jobs priority, but even so they seem to hog the system resources making subsequent requests for everyone else very slow. Our database at this point is almost entirely processor and memory bound because it isn't too large to fit most of the working data into memory yet. There is generally little disk activity when this occurs. These long running processes are almost always complex select statements, not generally inserts or updates. We continue to monitor and rework the bottlenecks, but what is a little scary to us is how easily the database becomes almost completely unresponsive with several large jobs running, especially since we have a large number of users. And it only takes one user trying to view a page with one of these selects clicking multiple times because it doesn't come back quickly to bring our system to it's knees for hours. We are looking to move to Kernel 2.6 and possibly a dedicated multiprocessor machine for postgres towards the end of this year. But, I am wondering if there is anything we can do now to increase the interactive performance while there are long running selects running as well. Are there ways to adjust the priority of backend processes, or things to tweak to maximize interactive throughput for the quick jobs while the long running ones run in the background? Or if worse comes to worse to actually kill long running processes without taking down the whole db as we have had to do on occasion. Our server is a modest 2.4Ghz P4 with mirrored UW SCSI drives and 1G of memory. The db on disk is around 800M and this machine also hosts our web app, so there is some contention for the processor. Does anyone have any suggestions or thoughts on things we could look at? Is a multiprocessor box the only answer, or are there other things we should be looking at hardware wise. Thank you for your time. -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] Poor performance of group by query
Anyone have any ideas why this query would be so slow? stats=# explain analyze SELECT work_units, min(raw_rank) AS rank FROM Trank_work_overall GROUP BY work_units; QUERY PLAN --- HashAggregate (cost=1050.12..1085.98 rows=14347 width=16) (actual time=163149.981..163227.758 rows=17849 loops=1) - Seq Scan on trank_work_overall (cost=0.00..804.41 rows=49141 width=16) (actual time=0.071..328.682 rows=49091 loops=1) Total runtime: 163296.212 ms (3 rows) stats=# \d Trank_work_overall Table pg_temp_1.trank_work_overall Column | Type | Modifiers ++--- raw_rank | bigint | work_units | bigint | stats=# FreeBSD fritz.distributed.net 5.2.1-RELEASE FreeBSD 5.2.1-RELEASE #1: Wed Apr 7 18:42:52 CDT 2004 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/FRITZ amd64 The machine is a dual opteron with 4G of memory. The query in question was not hitting the disk at all. PostgreSQL 7.4.2 compiled with -O3. Also, if I set enable_hashagg = false, it runs in less than a second. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] query slows down with more accurate stats
On Tue, 2004-04-13 at 15:18, Tom Lane wrote:
Robert Treat [EMAIL PROTECTED] writes:
Well, the first problem is why is ANALYZE's estimate of the total row
count so bad :-( ? I suspect you are running into the situation where
the initial pages of the table are thinly populated and ANALYZE
mistakenly assumes the rest are too.
That was my thinking, which is somewhat confirmed after a vacuum full on
the table; now analyze gives pretty accurate states. Of course the
downside is that now the query is consistently slower.
so i guess i am wondering if there is something I should be doing to
help get the better plan at the more accurate stats levels and/or why it
doesn't stick with the original plan (I noticed disabling merge joins
does seem to push it back to the original plan).
With the larger number of estimated rows it's figuring the nestloop will
be too expensive. The row estimate for the cl scan went up from 1248
to 10546, so the estimated cost for the nestloop plan would go to about
24 units vs 8 for the mergejoin plan. This is obviously off
rather badly when the true runtimes are 1.7 vs 8.1 seconds :-(.
I think this is an example of a case where we really need better
estimation of nestloop costs --- it's drastically overestimating the
relative cost of the nestloop because it's not accounting for the cache
benefits of the repeated index searches. You could probably force the
nestloop to be chosen by lowering random_page_cost, but that's just a
kluge solution ... the real problem is the model is wrong.
Unfortunately playing with random_page_cost doesn't seem to be enough to
get it to favor the nested loop... though setting it down to 2 does help
overall. played with index_cpu_tuple_cost a bit but that seemed even
less useful. aggravating when you know there is a better plan it could
pick but no (clean) way to get it to do so...
I have a to-do item to work on this, and will try to bump up its
priority a bit.
I'll keep an eye out, thanks Tom.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[PERFORM] Horribly slow hash join
Note the time for the hash join step: -- Hash Join (cost=357.62..26677.99 rows=93668 width=62) (actual time=741.159..443381.011 rows=49091 loops=1) Hash Cond: (outer.work_today = inner.work_units) - Hash Join (cost=337.11..24784.11 rows=93668 width=54) (actual time=731.374..417188.519 rows=49091 loops=1) Hash Cond: (outer.work_total = inner.work_units) - Seq Scan on email_rank (cost=0.00..22240.04 rows=254056 width=46) (actual time=582.145..1627.759 rows=49091 loops=1) Filter: (project_id = 8) - Hash (cost=292.49..292.49 rows=17849 width=16) (actual time=148.944..148.944 rows=0 loops=1) - Seq Scan on rank_tie_overall o (cost=0.00..292.49 rows=17849 width=16) (actual time=0.059..75.984 rows=17849 loops=1) - Hash (cost=17.81..17.81 rows=1081 width=16) (actual time=8.996..8.996 rows=0 loops=1) - Seq Scan on rank_tie_today d (cost=0.00..17.81 rows=1081 width=16) (actual time=0.080..4.635 rows=1081 loops=1) Total runtime: 619047.032 ms By comparison: stats=# set enable_hashjoin=false; SET stats=# explain analyze select * from email_rank, rank_tie_overall o, rank_tie_today d WHERE email_rank.work_today = d.work_units AND email_rank.work_total = o.work_units AND email_rank.project_id = :ProjectID; QUERY PLAN - Merge Join (cost=55391.69..56823.23 rows=93668 width=80) (actual time=2705.344..3349.318 rows=49091 loops=1) Merge Cond: (outer.work_units = inner.work_today) - Index Scan using work_units_today on rank_tie_today d (cost=0.00..23.89 rows=1081 width=16) (actual time=0.150..4.874 rows=1081 loops=1) - Sort (cost=55391.69..55625.86 rows=93668 width=64) (actual time=2705.153..2888.039 rows=49091 loops=1) Sort Key: email_rank.work_today - Merge Join (cost=45047.64..47656.93 rows=93668 width=64) (actual time=1685.414..2494.342 rows=49091 loops=1) Merge Cond: (outer.work_units = inner.work_total) - Index Scan using work_units_overall on rank_tie_overall o (cost=0.00..361.34 rows=17849 width=16) (actual time=0.122..79.383 rows=17849 loops=1) - Sort (cost=45047.64..45682.78 rows=254056 width=48) (actual time=1685.228..1866.215 rows=49091 loops=1) Sort Key: email_rank.work_total - Seq Scan on email_rank (cost=0.00..22240.04 rows=254056 width=48) (actual time=786.515..1289.101 rows=49091 loops=1) Filter: (project_id = 8) Total runtime: 3548.087 ms Even though the second case is only a select, it seems clear that something's wrong... -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Long running queries degrade performance
We have a web app with a postgres backend. Most queries have subsecond response times through the web even with high usage. Every once in awhile someone will run either an ad-hoc query or some other long running db process. Are you sure it is postgres where the delay is occurring? I ask this because I also have a web-based front end to postgres, and while most of the time the queries respond in about a second every now and then I see one that takes much longer, sometimes 10-15 seconds. I've seen this behavior on both my development system and on the production server. The same query a while later might respond quickly again. I'm not sure where to look for the delay, either, and it is intermittent enough that I'm not even sure what monitoring techniques to use. -- Mike Nolan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Horribly slow hash join
Jim C. Nasby [EMAIL PROTECTED] writes: Note the time for the hash join step: Have you ANALYZEd these tables lately? It looks to me like it's hashing on some column that has only a small number of distinct values, so that the hash doesn't actually help to avoid comparisons. The planner should know better than to choose such a plan, but if it's working with obsolete stats ... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Poor performance of group by query
Jim C. Nasby [EMAIL PROTECTED] writes: Anyone have any ideas why this query would be so slow? That seems very bizarre. Would you be willing to send me a dump of the table off-list? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] Index Problem?
I am using postgres 7.4.1 and have a problem with a plpgsql function. When I run the function on the production server it takes approx 33 minutes to run. I dumped the DB and copied it to a similarly configured box and ran the function and it ran in about 10 minutes. Can anyone offer advice on tuning the function or my database? Here are the lengthy, gory details. F u n c t i o n It updates seven columns of a table 1 to 4 times daily. Current data = 42,000 rows, new data = 30,000 rows. CREATE TYPE employeeType AS (empID INTEGER, updateDate DATE, bDate INTEGER, val1 NUMERIC, val2 NUMERIC, val3 NUMERIC, val4 NUMERIC, favNum NUMERIC); CREATE OR REPLACE FUNCTION updateEmployeeData() RETURNS SETOF employeeType AS ' DECLARE rec RECORD; BEGIN FOR rec IN SELECT empID, updateDate, bDate, val1, val2 , val3, val4, favNum FROM newData LOOP RETURN NEXT rec; UPDATE currentData SET val1=rec.val1, val2=rec.val2, val3=rec.val2, val4=rec.val4, favNum=rec.favNum, updateDate=rec.updateDate WHERE empID=rec.empID; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; The emp table has 60 columns, all indexed, about two-thirds are numeric, but they are not affected by this update. The other 50+ columns are updated in the middle of the night and the amount of time that update takes isn't a concern. Late last night I dumped the table, dropped it and re-created it from the dump (on the production server - when no one was looking). When I re-ran the function it took almost 11 minutes, which was pretty much in line with my results from the dev server. D e t a i l s v 7.4.1 Debian stable 1 GB ram shared_buffers = 2048 sort_mem = 1024 SHMMAX 36000 (360,000,000) VACUUM FULL ANALYZE is run every night, and I ran it yesterday between running the function and it made no difference in running time. top shows the postmaster using minimal cpu (0-40%) and miniscule memory. vmstat shows a fair amount of IO (bo=1000-4000). Yesterday on the dev server we upgraded to the 2.6 kernel and unfortunately only noticed a small increase in update time (about one minute). So does anyone have any suggestions for me on speeding this up? Is it the index? The function is run daily during the mid afternoon to early evening and really drags the performance of the server down (it also hosts a web site). Thanks Ron ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Index Problem?
Ron, The emp table has 60 columns, all indexed, about two-thirds are numeric, but they are not affected by this update. The other 50+ columns are updated in the middle of the night and the amount of time that update takes isn't a concern. Well, I'd say that you have an application design problem, but that's not what you asked for help with ;-) Late last night I dumped the table, dropped it and re-created it from the dump (on the production server - when no one was looking). When I re-ran the function it took almost 11 minutes, which was pretty much in line with my results from the dev server. Sounds like you need to run a REINDEX on the table -- and after that, dramatically increase your max_fsm_pages, and run lazy VACUUM immediately after the batch update to clean up. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Index Problem?
Josh Berkus [EMAIL PROTECTED] writes: A better way to set this would be to run VACUUM VERBOSE ANALYZE right after doing one of your update batches, and see how many dead pages are being reclaimed, and then set max_fsm_pages to that # + 50% (or more). Actually, since he's running 7.4, there's an even better way. Do a VACUUM VERBOSE (full-database vacuum --- doesn't matter whether you ANALYZE or not). At the end of the very voluminous output, you'll see something like INFO: free space map: 240 relations, 490 pages stored; 4080 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 178 kB shared memory. Here, I would need max_fsm_relations = 240 and max_fsm_pages = 4080 to exactly cover the present freespace needs of my system. I concur with the suggestion to bump that up a good deal, of course, but that gives you a real number to start from. The DETAIL part of the message shows my current settings (which are the defaults) and what the FSM is costing me in shared memory space. If you have multiple active databases, the best approach to getting these numbers is to VACUUM in each one, adding VERBOSE when you do the last one. The FSM report is cluster-wide but you want to be sure the underlying info is up to date for all databases. Also the docs on Varlena show the max_fsm_pages default to be 10,000 but my default was 20,000, looks like that needs updating. I don't think the default has been changed. Anyone? Yes, I kicked it up for 7.4 because FSM covers indexes too now. Both the 7.3 and 7.4 defaults are pretty arbitrary of course... regards, tom lane ---(end of broadcast)--- TIP 3: 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] Long running queries degrade performance
Fairly sure, when it is happening, postgres usually is taking up the top slots for cpu usage as reported by top. Perhaps there is a better way to monitor this? Given the intermittent nature of the problem and its relative brevity (5-10 seconds), I don't know whether top offers the granularity needed to locate the bottleneck. likely you have a situation where something else is happening which blocks the current thread. It happens on my development system, and I'm the only one on it. I know I've seen it on the production server, but I think it is a bit more common on the development server, though that may be a case of which system I spend the most time on. (Also, the production server is 1300 miles away with a DSL connection, so I may just be seeing network delays some of the time there.) Both of these were triggered by users double clicking links in our web app and were fixed by a better implementation. Perhaps something like that is causing what you are seeing. My web app traps double-clicks in javascript and ignores all but the first one. That's because some of the users have mice that give double-clicks even when they only want one click. -- Mike Nolan ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Long running queries degrade performance
On Friday 16 April 2004 4:25 pm, Mike Nolan wrote: Given the intermittent nature of the problem and its relative brevity (5-10 seconds), I don't know whether top offers the granularity needed to locate the bottleneck. Our long running processes run on the order of multiple minutes (sometimes for over an hour) and it's expected because the sql can be quite complex over somewhat large datasets. But it's the bringing the server to it's knees, that I'm trying to figure out how to address if we can. In other words, let those long running processes run, but somehow still get decent performance for quick requests. Yours reminds me of what used to happen in our apps back when I worked in java and the garbage collector kicked in. Suddenly everything would stop for 10-15s and then continue on. Sort of makes you think the app froze for some reason. It happens on my development system, and I'm the only one on it. I know I've seen it on the production server, but I think it is a bit more common on the development server, though that may be a case of which system I spend the most time on. (Also, the production server is 1300 miles away with a DSL connection, so I may just be seeing network delays some of the time there.) Interesting. Have you tried running a processor monitor and seeing if you are getting a cpu or disk spike when you get the blips? Postgres has been pretty constant for us in it's average runtime for any particular query. We do get some fluctuation, but I've always attributed that to other things happening in the background. I sometimes run gkrellm off the server just to see what's happening on a macro scale. It's a great early indicator when we are getting slammed one way or another (network, memory, processor, disk, etc). Plus it shows a couple of seconds of history so you can see blips pretty easily. My web app traps double-clicks in javascript and ignores all but the first one. That's because some of the users have mice that give double-clicks even when they only want one click. Hmmm, never thought of doing that. Might be interesting to do something like that in a few key places where we have problems. -- Mike Nolan -- Chris Kratz Systems Analyst/Programmer VistaShare LLC www.vistashare.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Index Problem?
Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: A better way to set this would be to run VACUUM VERBOSE ANALYZE right after doing one of your update batches, and see how many dead pages are being reclaimed, and then set max_fsm_pages to that # + 50% (or more). Actually, since he's running 7.4, there's an even better way. Do a VACUUM VERBOSE (full-database vacuum --- doesn't matter whether you ANALYZE or not). At the end of the very voluminous output, you'll see something like INFO: free space map: 240 relations, 490 pages stored; 4080 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 178 kB shared memory. Here, I would need max_fsm_relations = 240 and max_fsm_pages = 4080 to exactly cover the present freespace needs of my system. I concur with the suggestion to bump that up a good deal, of course, but that gives you a real number to start from. The DETAIL part of the message shows my current settings (which are the defaults) and what the FSM is costing me in shared memory space. Okay, after running the function VACUUM VERBOSE is telling me: INFO: free space map: 136 relations, 25014 pages stored; 22608 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 178 kB shared memory. My max_fsm_pages was set to 20,000 and I reset it to 40,000 on the dev server and the function ran about 20-30% faster, so I'll try the same on the production server. Thanks for the analysis of the VACUUM info. Ron ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] sunquery and estimated rows
Hi, When I included a subquery, the estimated rows (1240) is way too high as shown in the following example. Can someone explain why? Because of this behavior, some of our queries use hash join instead of nested loop. Thanks, select version(); version - PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) \d test Table public.test Column | Type | Modifiers -+--+--- id | integer | name| character varying(255) | d_id| integer | c_id| integer | r_id| integer | u_id| integer | scope | integer | active | integer | created | timestamp with time zone | typ | integer | Indexes: test_scope_idx btree (scope) reindex table test; vacuum full analyze test; select count(*) from test; count --- 4959 (1 row) select count(*) from test where scope=10; count --- 10 (1 row) explain analyze select * from test where scope=10; -- so far so good, estimate 12 rows, actual 10 rows QUERY PLAN -- Index Scan using test_scope_idx on test (cost=0.00..4.35 rows=12 width=59) (actual time=0.04..0.11 rows=10 loops=1) Index Cond: (scope = 10) Total runtime: 0.23 msec (3 rows) explain analyze select * from test where scope=(select 10); -- estimate rows is way too high, do not why QUERY PLAN - Index Scan using test_scope_idx on test (cost=0.00..40.74 rows=1240 width=59) (actual time=0.06..0.13 rows=10 loops=1) Index Cond: (scope = $0) InitPlan - Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1) Total runtime: 0.22 msec (5 rows) __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] query slows down with more accurate stats
On Fri, 16 Apr 2004 10:34:49 -0400, Tom Lane [EMAIL PROTECTED] wrote:
p = prod from{i = 0} to{n - 1} {{c(B - i)} over {cB - i}}
So? You haven't proven that either sampling method fails to do the
same.
On the contrary, I believe that above formula is more or less valid for
both methods. The point is in what I said next:
| This probability grows with increasing B.
For the one-stage sampling method B is the number of pages of the whole
table. With two-stage sampling we have to use n instead of B and get a
smaller probability (for n B, of course). So this merely shows that
the two sampling methods are not equivalent.
The desired property can also be phrased as every tuple should be
equally likely to be included in the final sample.
Only at first sight. You really expect more from random sampling.
Otherwise I'd just put one random tuple and its n - 1 successors (modulo
N) into the sample. This satisfies your condition but you wouldn't call
it a random sample.
Random sampling is more like every possible sample is equally likely to
be collected, and two-stage sampling doesn't satisfy this condition.
But if in your opinion the difference is not significant, I'll stop
complaining against my own idea. Is there anybody else who cares?
You could argue that a tuple on a heavily populated page is
statistically likely to see a higher T when it's part of the page sample
pool than a tuple on a near-empty page is likely to see, and therefore
there is some bias against selection of the former tuple. But given a
sample over a reasonably large number of pages, the contribution of any
one page to T should be fairly small and so this effect ought to be
small.
It is even better: Storing a certain number of tuples on heavily
populated pages takes less pages than to store them on sparsely
populated pages (due to tuple size or to dead tuples). So heavily
populated pages are less likely to be selected in stage one, and this
exactly offsets the effect of increasing T.
So I think this method is effectively unbiased at the tuple level.
Servus
Manfred
---(end of broadcast)---
TIP 3: 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] Poor performance of group by query
stats=# explain analyze SELECT work_units, min(raw_rank) AS rank FROM Trank_work_overall GROUP BY work_units; ... raw_rank | bigint | work_units | bigint | If you create a copy of the same table using regular integers does that run fast? And a copy of the table using bigints is still slow like the original? I know bigints are less efficient than integers because they're handled using dynamically allocated memory. This especially bites aggregate functions. But I don't see why it would be any slower for a hash aggregate than a regular aggregate. It's a pretty gross amount of time for 18k records. There was a thought a while back about making 64-bit machines handle 64-bit datatypes like bigints without pointers. That would help on your Opteron. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] sunquery and estimated rows
Litao Wu [EMAIL PROTECTED] writes: When I included a subquery, the estimated rows (1240) is way too high as shown in the following example. select * from test where scope=(select 10); The planner sees that as where scope = some complicated expression and falls back to a default estimate. It won't simplify a sub-select to a constant. (Some people consider that a feature ;-).) The estimate should still be derived from the statistics for the scope column, but it will just depend on the number of distinct values for the column and not on the specific comparison constant. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
