Re: RESOLVED: Re: [PERFORM] Wierd context-switching issue on Xeon
Josh, I cannot reproduce the excessive semop() on a Dual XEON DP on a non-bigmem kernel, HT on. Interesting to know if the problem is related to XEON MP (as Tom wrote) or bigmem. Josh Berkus wrote: Dirk, 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. It would be helpful to us if you could test this with the indexes disabled on the non-Bigmem system. I'd like to eliminate Bigmem as a factor, if possible. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] very slow simple query - outer join makes it quicker
Hi Everyone I am new to this group and relatively new to Postgres, having used MSSQL 7 up until now. One table in my database in returning even the simplest of queries extremely slowly. The table is user table, and even the select userid from users takes over 20 seconds to run. There are about 2000 records in the table. The EXPLAIN ANALYZE on this table produces this output: Seq Scan on users (cost=0.00..89482.63 rows=1463 width=4) (actual time=68.836..40233.463 rows=1465 loops=1) Total runtime: 40234.965 ms SELECT USERID FROM USERS produces this: 1465 rows fetched (25.28 sec) The userid field is the primary key and has an index on it with this ddl: ALTER TABLE public.users ADD CONSTRAINT users_pkey PRIMARY KEY (userid); There are other tables, such as the messages table, that have 10s of thousands of rows and they return records much more quickly. There must be something seriously wrong for simple queries like this to take so long. I should say that we are using the OpenFTS text search on the users table. In many cases to make the queries run at reasonable speeds I do an outer join on another table, and surprisingly these results come back very quickly Can anybody help me in diagnosing this problem. Gerard Isdell * This e-mail and any attachments may contain confidential or privileged information. If you are not the intended recipient, please contact the sender immediately and do not use, store or disclose their contents. Any views expressed are those of the individual sender and not of Kinetic Information System Services Limited unless otherwise stated. www.kinetic.co.uk ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Horribly slow hash join
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: If the hash tables were made a power of two then it would be possible to mix the bits of the 32 bit value and just mask off the unneeded bits. I've found one page via google that mentions mixing bits in a hash function, but I would look for a more serious treatment somewhere. Modding by a *non* power of 2 (esp. a prime) mixes the bits quite well, and is likely faster than any multiple-instruction way to do the same. Well a) any number that has any factors of two fails to mix in some bits. That's a lot more common than non powers of two. b) The postgres code makes no attempt to make the number of buckets a prime and c) Even if the number of buckets were prime then it seems it would still be too easy to find real-world data where all the data have that prime as a factor. As it is they only need to have common factors to lose. The quoted article seems to be by someone who has spent a lot of time counting assembly cycles and none at all reading the last thirty years worth of CS literature. Yes, well I did note that. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] very slow simple query - outer join makes it quicker
There are other tables, such as the messages table, that have 10s of thousands of rows and they return records much more quickly. There must be something seriously wrong for simple queries like this to take so long. Have you run VACUUM recently? If not, run VACUUM FULL against the users table and see if that makes a difference. ---(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] Horribly slow hash join
Here's an interesting link that suggests that hyperthreading would be much worse. http://groups.google.com/groups?q=hyperthreading+dual+xeon+idlestart=10hl=enlr=ie=UTF-8c2coff=1selm=aukkonen-FE5275.21093624062003%40shawnews.gv.shawcable.netrnum=16 FWIW, I have anecdotal evidence that suggests that this is the case, on of my clients was seeing very large context switches with HTT turned on, and without it was much better. Dave On Mon, 2004-04-19 at 02:09, Tom Lane wrote: Greg Stark [EMAIL PROTECTED] writes: If the hash tables were made a power of two then it would be possible to mix the bits of the 32 bit value and just mask off the unneeded bits. I've found one page via google that mentions mixing bits in a hash function, but I would look for a more serious treatment somewhere. http://burtleburtle.net/bob/hash/doobs.html Incidentally, this text claims mod is extremely slow compared to bit manipulations. Modding by a *non* power of 2 (esp. a prime) mixes the bits quite well, and is likely faster than any multiple-instruction way to do the same. The quoted article seems to be by someone who has spent a lot of time counting assembly cycles and none at all reading the last thirty years worth of CS literature. Knuth's treatment of hashing has some actual math to it... 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 !DSPAM:40837183123741526418863! -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Horribly slow hash join
Dave Cramer [EMAIL PROTECTED] writes: Here's an interesting link that suggests that hyperthreading would be much worse. Uh, this is the wrong thread. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] very slow simple query - outer join makes it quicker
On Mon, 2004-04-19 at 08:26, Gerard Isdell wrote: Thank, that has worked. I've been running VACUUM regularly and thought that would have done it. Obviously the FULL makes a big difference It shouldn't. That FULL makes a significant difference says that you're not running regular VACUUM frequently enough and/or your fsm_* settings are too low. -Original Message- From: Rod Taylor [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Postgresql Performance [EMAIL PROTECTED] Date: Mon, 19 Apr 2004 08:01:15 -0400 Subject: Re: [PERFORM] very slow simple query - outer join makes it quicker There are other tables, such as the messages table, that have 10s of thousands of rows and they return records much more quickly. There must be something seriously wrong for simple queries like this to take so long. Have you run VACUUM recently? If not, run VACUUM FULL against the users table and see if that makes a difference. * This e-mail and any attachments may contain confidential or privileged information. If you are not the intended recipient, please contact the sender immediately and do not use, store or disclose their contents. Any views expressed are those of the individual sender and not of Kinetic Information System Services Limited unless otherwise stated. www.kinetic.co.uk ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Wierd context-switching issue on Xeon
What about quad-XEON setups? Could that be worse? (have dual, and quad setups both) Shall we re-consider XEON-MP CPU machines with high cache (4MB+)? Very generally, what number would be considered high, especially, if it coincides with expected heavy load? Not sure a specific chipset was mentioned... Thanks, Anjan -Original Message- From: Greg Stark [mailto:[EMAIL PROTECTED] Sent: Sun 4/18/2004 8:40 PM To: Tom Lane Cc: [EMAIL PROTECTED]; Josh Berkus; [EMAIL PROTECTED]; Neil Conway Subject: Re: [PERFORM] Wierd context-switching issue on Xeon Tom Lane [EMAIL PROTECTED] writes: So in the short term I think we have to tell people that Xeon MP is not the most desirable SMP platform to run Postgres on. (Josh thinks that the specific motherboard chipset being used in these machines might share some of the blame too. I don't have any evidence for or against that idea, but it's certainly possible.) In the long run, however, CPUs continue to get faster than main memory and the price of cache contention will continue to rise. So it seems that we need to give up the assumption that SpinLockAcquire is a cheap operation. In the presence of heavy contention it won't be. There's nothing about the way Postgres spinlocks are coded that affects this? Is it something the kernel could help with? I've been wondering whether there's any benefits postgres is missing out on by using its own hand-rolled locking instead of using the pthreads infrastructure that the kernel is often involved in. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] query slows down with more accurate stats
Manfred Koizar [EMAIL PROTECTED] writes: Random sampling is more like every possible sample is equally likely to be collected, and two-stage sampling doesn't satisfy this condition. Okay, I finally see the point here: in the limit as the number of pages B goes to infinity, you'd expect the probability that each tuple in your sample came from a different page to go to 1. But this doesn't happen in the two-stage sampling method: the probability doesn't increase beyond the value it would have for B=n. On the average each sample page would supply one tuple, but the odds that this holds *exactly* would be pretty low. However the existing sampling method has glaring flaws of its own, in particular having to do with the fact that a tuple whose slot is preceded by N empty slots is N times more likely to be picked than one that has no empty-slot predecessors. The fact that the two-stage method artificially constrains the sample to come from only n pages seems like a minor problem by comparison; I'd happily accept it to get rid of the empty-slot bias. A possible compromise is to limit the number of pages sampled to something a bit larger than n, perhaps 2n or 3n. I don't have a feeling for the shape of the different-pages probability function; would this make a significant difference, or would it just waste cycles? 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] Wierd context-switching issue on Xeon
I decided to check the context-switching behavior here for baseline since we have a rather diverse set of postgres server hardware, though nothing using Xeon MP that is also running a postgres instance, and everything looks normal under load. Some platforms are better than others, but nothing is outside of what I would consider normal bounds. Our biggest database servers are Opteron SMP systems, and these servers are particularly well-behaved under load with Postgres 7.4.2. If there is a problem with the locking code and context-switching, it sure isn't manifesting on our Opteron SMP systems. Under rare confluences of process interaction, we occasionally see short spikes in the 2-3,000 cs/sec range. It typically peaks at a couple hundred cs/sec under load. Obviously this is going to be a function of our load profile a certain extent. The Opterons have proven to be very good database hardware in general for us. j. andrew rogers ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Wierd context-switching issue on Xeon
Josh Berkus [EMAIL PROTECTED] writes: The other thing I'd like your comment on, Tom, is that Dirk appears to have reported that when he installed a non-bigmem kernel, the issue went away. Dirk, is this correct? I'd be really surprised if that had anything to do with it. AFAIR Dirk's test changed more than one variable and so didn't prove a connection. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Wierd context-switching issue on Xeon
scott.marlowe wrote: On Mon, 19 Apr 2004, Bruce Momjian wrote: I have BSD on a SuperMicro dual Xeon, so if folks want another hardware/OS combination to test, I can give out logins to my machine. I can probably do some nighttime testing on a dual 2800MHz non-MP Xeon machine as well. It's a Dell 2600 series machine and very fast. It has the moderately fast 533MHz FSB so may not have as many problems as the MP type CPUs seem to be having. I've got a quad 2.8Ghz MP Xeon (IBM x445) that I could test on. Does anyone have a test set that can reliably reproduce the problem? Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Wierd context-switching issue on Xeon
Joe, I've got a quad 2.8Ghz MP Xeon (IBM x445) that I could test on. Does anyone have a test set that can reliably reproduce the problem? Unfortunately we can't seem to come up with one.So far we have 2 machines that exhibit the issue, and their databases are highly confidential (State of WA education data). It does seem to require a database which is in the many GB ( 10GB), and a situation where a small subset of the data is getting hit repeatedly by multiple processes. So you could try your own data warehouse, making sure that you have at least 4 connections hitting one query after another. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Wierd context-switching issue on Xeon
Josh Berkus [EMAIL PROTECTED] writes: I've got a quad 2.8Ghz MP Xeon (IBM x445) that I could test on. Does anyone have a test set that can reliably reproduce the problem? Unfortunately we can't seem to come up with one. It does seem to require a database which is in the many GB ( 10GB), and a situation where a small subset of the data is getting hit repeatedly by multiple processes. I do not think a large database is actually necessary; the test case Josh's client has is only hitting a relatively small amount of data. The trick seems to be to cause lots and lots of ReadBuffer/ReleaseBuffer activity without much else happening, and to do this from multiple backends concurrently. I believe the best way to make this happen is a lot of relatively simple (but not short) indexscan queries that in aggregate touch just a bit less than shared_buffers worth of data. I have not tried to make a self-contained test case, but based on what I know now I think it should be possible. I'll give this a shot later tonight --- it does seem that trying to reproduce the problem on different kinds of hardware is the next useful step we can take. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Wierd context-switching issue on Xeon
Here is a test case. To set up, run the test_setup.sql script once; then launch two copies of the test_run.sql script. (For those of you with more than two CPUs, see whether you need one per CPU to make trouble, or whether two test_runs are enough.) Check that you get a nestloops-with-index-scans plan shown by the EXPLAIN in test_run. In isolation, test_run.sql should do essentially no syscalls at all once it's past the initial ramp-up. On a machine that's functioning per expectations, multiple copies of test_run show a relatively low rate of semop() calls --- a few per second, at most --- and maybe a delaying select() here and there. What I actually see on Josh's client's machine is a context swap storm: vmstat 1 shows CS rates around 170K/sec. strace'ing the backends shows a corresponding rate of semop() syscalls, with a few delaying select()s sprinkled in. top(1) shows system CPU percent of 25-30 and idle CPU percent of 16-20. I haven't bothered to check how long the test_run query takes, but if it ends while you're still examining the behavior, just start it again. Note the test case assumes you've got shared_buffers set to at least 1000; with smaller values, you may get some I/O syscalls, which will probably skew the results. regards, tom lane drop table test_data; create table test_data(f1 int); insert into test_data values (random() * 100); insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; create index test_index on test_data(f1); vacuum verbose analyze test_data; checkpoint; -- force nestloop indexscan plan set enable_seqscan to 0; set enable_mergejoin to 0; set enable_hashjoin to 0; explain select count(*) from test_data a, test_data b, test_data c where a.f1 = b.f1 and b.f1 = c.f1; select count(*) from test_data a, test_data b, test_data c where a.f1 = b.f1 and b.f1 = c.f1; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Wierd context-switching issue on Xeon
I wrote: Here is a test case. Hmmm ... I've been able to reproduce the CS storm on a dual Athlon, which seems to pretty much let the Xeon per se off the hook. Anybody got a multiple Opteron to try? Totally non-Intel CPUs? It would be interesting to see results with non-Linux kernels, too. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] sunquery and estimated rows
Well, the example shown is simplified version. Now, let's see a little 'real' example (still simplified version): Table test is same as before: \d test Table public.test Column | Type | Modifiers -+--+--- id | integer | ... scope | integer | ... Indexes: test_scope_idx btree (scope) select count(*) from test; count --- 4959 (1 row) select count(*) from test where scope=10; count --- 10 (1 row) create table scope_def (scope int primary key, name varchar(30) unique); insert into scope_def values (10, 'TEST_SCOPE'); -- This is not a trivial arithmetic expression explain analyze select * from test where scope=(select scope from scope_def where name = 'TEST_SCOPE'); -- estimated row is 1653, returned rows is 10 QUERY PLAN Index Scan using test_scope_idx on test (cost=0.00..49.91 rows=1653 width=59) (actual time=0.08..0.15 rows=10 loops=1) Index Cond: (scope = $0) InitPlan - Index Scan using scope_def_name_key on scope_def (cost=0.00..4.82 rows=1 width=4) (actual time=0.04..0.04 rows=1 loops=1) Index Cond: (name = 'TEST_SCOPE'::character varying) Total runtime: 0.22 msec (6 rows) -- trivial arithmetic expression -- estimated row is 1653, returned rows is 10 explain analyze select * from test where scope=(select 10); QUERY PLAN - Index Scan using test_scope_idx on test (cost=0.00..49.91 rows=1653 width=59) (actual time=0.06..0.14 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.20 msec (5 rows) -- This is the plan I expect to see: estimated rows is -- close the actual returned rows. -- Do I have to devide the sub-select into two -- queries? explain analyze select * from test where scope=10; QUERY PLAN -- Index Scan using test_scope_idx on test (cost=0.00..3.77 rows=10 width=59) (actual time=0.05..0.12 rows=10 loops=1) Index Cond: (scope = 10) Total runtime: 0.18 msec (3 rows) -- Rewritten query using join in this case explain analyze select test.* from test JOIN scope_def using (scope) where scope_def.name = 'TEST_SCOPE'; QUERY PLAN -- Nested Loop (cost=0.00..75.39 rows=5 width=63) (actual time=0.07..0.19 rows=10 loops=1) - Index Scan using scope_def_name_key on scope_def (cost=0.00..4.82 rows=1 width=4) (actual time=0.04..0.04 rows=1 loops=1) Index Cond: (name = 'TEST_SCOPE'::character varying) - Index Scan using test_scope_idx on test (cost=0.00..49.91 rows=1653 width=59) (actual time=0.02..0.09 rows=10 loops=1) Index Cond: (test.scope = outer.scope) Total runtime: 0.28 msec (6 rows) __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Wierd context-switching issue on Xeon
Tom Lane wrote: Here is a test case. To set up, run the test_setup.sql script once; then launch two copies of the test_run.sql script. (For those of you with more than two CPUs, see whether you need one per CPU to make trouble, or whether two test_runs are enough.) Check that you get a nestloops-with-index-scans plan shown by the EXPLAIN in test_run. Check. In isolation, test_run.sql should do essentially no syscalls at all once it's past the initial ramp-up. On a machine that's functioning per expectations, multiple copies of test_run show a relatively low rate of semop() calls --- a few per second, at most --- and maybe a delaying select() here and there. What I actually see on Josh's client's machine is a context swap storm: vmstat 1 shows CS rates around 170K/sec. strace'ing the backends shows a corresponding rate of semop() syscalls, with a few delaying select()s sprinkled in. top(1) shows system CPU percent of 25-30 and idle CPU percent of 16-20. Your test case works perfectly. I ran 4 concurrent psql sessions, on a quad Xeon (IBM x445, 2.8GHz, 4GB RAM), hyperthreaded. Heres what 'top' looks like: 177 processes: 173 sleeping, 3 running, 1 zombie, 0 stopped CPU states: cpuusernice systemirq softirq iowaitidle total 35.9%0.0%7.2% 0.0% 0.0%0.0% 56.8% cpu00 19.6%0.0%4.9% 0.0% 0.0%0.0% 75.4% cpu01 44.1%0.0%7.8% 0.0% 0.0%0.0% 48.0% cpu020.0%0.0%0.0% 0.0% 0.0%0.0% 100.0% cpu03 32.3%0.0% 13.7% 0.0% 0.0%0.0% 53.9% cpu04 21.5%0.0% 10.7% 0.0% 0.0%0.0% 67.6% cpu05 42.1%0.0%9.8% 0.0% 0.0%0.0% 48.0% cpu06 100.0%0.0%0.0% 0.0% 0.0%0.0%0.0% cpu07 27.4%0.0% 10.7% 0.0% 0.0%0.0% 61.7% Mem: 4123700k av, 3933896k used, 189804k free, 0k shrd, 221948k buff 2492124k actv, 760612k in_d, 41416k in_c Swap: 2040244k av, 5632k used, 2034612k free 3113272k cached Note that cpu06 is not a postgres process. The output of vmstat looks like this: # vmstat 1 procs memory swap io system cpu r b swpd free buff cache si so bi bo in cs us sy id wa 4 0 5632 184264 221948 3113308 0 000 00 0 0 0 0 3 0 5632 184264 221948 3113308 0 000 112 211894 36 9 55 0 5 0 5632 184264 221948 3113308 0 000 125 222071 39 8 53 0 4 0 5632 184264 221948 3113308 0 000 110 215097 39 10 52 0 1 0 5632 184588 221948 3113308 0 00 96 139 187561 35 10 55 0 3 0 5632 184588 221948 3113308 0 000 114 241731 38 10 52 0 3 0 5632 184920 221948 3113308 0 000 132 257168 40 9 51 0 1 0 5632 184912 221948 3113308 0 000 114 251802 38 9 54 0 Note the test case assumes you've got shared_buffers set to at least 1000; with smaller values, you may get some I/O syscalls, which will probably skew the results. shared_buffers 16384 (1 row) I found that killing three of the four concurrent queries dropped context switches to about 70,000 to 100,000. Two or more sessions brings it up to 200K+. Joe ---(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] Wierd context-switching issue on Xeon
When grilled further on (Mon, 19 Apr 2004 20:53:09 -0400), Tom Lane [EMAIL PROTECTED] confessed: I wrote: Here is a test case. Hmmm ... I've been able to reproduce the CS storm on a dual Athlon, which seems to pretty much let the Xeon per se off the hook. Anybody got a multiple Opteron to try? Totally non-Intel CPUs? It would be interesting to see results with non-Linux kernels, too. Same problem on my dual AMD MP with 2.6.5 kernel using two sessions of your test, but maybe not quite as severe. The highest CS values I saw was 102k, with some non-db number crunching going on in parallel with the test. 'Average' about 80k with two instances. Using the anticipatory scheduler. A single instance pulls in around 200-300 CS, and no tests running around 200-300 CS (i.e. no CS difference). A snipet: procs ---memory-- ---swap-- -io --system-- cpu 3 0284 90624 93452 145374000 0 0 1075 76548 83 17 0 0 6 0284 125312 93452 147019600 0 0 1073 87702 78 22 0 0 3 0284 178392 93460 14202080076 298 1083 67721 77 24 0 0 4 0284 177120 93460 142150000 1104 0 1054 89593 80 21 0 0 5 0284 173504 93460 142517200 3584 0 1110 65536 81 19 0 0 4 0284 169984 93460 142870800 3456 0 1098 66937 81 20 0 0 6 0284 170944 93460 142870800 8 0 1045 66065 81 19 0 0 6 0284 167288 93460 142877600 0 8 1097 75560 81 19 0 0 6 0284 136296 93460 145835600 0 0 1036 80808 75 26 0 0 5 0284 132864 93460 146168800 0 0 1007 76071 84 17 0 0 4 0284 132880 93460 146168800 0 0 1079 86903 82 18 0 0 5 0284 132880 93460 146168800 0 0 1078 79885 83 17 0 0 6 0284 132648 93460 146168800 0 760 1228 66564 86 14 0 0 6 0284 132648 93460 146168800 0 0 1047 69741 86 15 0 0 6 0284 132672 93460 146168800 0 0 1057 79052 84 16 0 0 5 0284 132672 93460 146168800 0 0 1054 81109 82 18 0 0 5 0284 132736 93460 146168800 0 0 1043 91725 80 20 0 0 Cheers, Rob -- 21:33:03 up 3 days, 1:10, 3 users, load average: 5.05, 4.67, 4.22 Linux 2.6.5-01 #5 SMP Tue Apr 6 21:32:39 MDT 2004 pgp0.pgp Description: PGP signature
Re: [PERFORM] Why will vacuum not end?
This vacuum is running a marathon. Why will it not end and show me free space map INFO? We have deleted a lot of data and I would like to be confident that these deletions will be used as free space, rather than creating more table files. Does another postgres query running have a lock on that table? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PERFORM] order by index, and inheritance
I have a query which performs not so well: SELECT * FROM mm_mediasources ORDER BY number DESC LIMIT 20; costs nearly a minute. The table contains over 300 000 records. The table has two extensions, which are (a the moment) nearly empty, but have something to do with this, because: SELECT * FROM only mm_mediasources ORDER BY number DESC LIMIT 20; performs ok (8ms). The query plan is then as I would expect: media=# explain SELECT * FROM only mm_mediasources ORDER BY number DESC LIMIT 20; QUERY PLAN - Limit (cost=0.00..8.36 rows=20 width=105) - Index Scan Backward using mediasource_object on mm_mediasources (cost=0.00..114641.05 rows=274318 width=105) The query plan of the original query, without 'only' does table scans: media=# explain SELECT * FROM mm_mediasources ORDER BY number DESC LIMIT 20; QUERY PLAN Limit (cost=47248.70..47248.75 rows=20 width=105) - Sort (cost=47248.70..47934.52 rows=274328 width=105) Sort Key: public.mm_mediasources.number - Result (cost=0.00..8364.28 rows=274328 width=105) - Append (cost=0.00..8364.28 rows=274328 width=105) - Seq Scan on mm_mediasources (cost=0.00..8362.18 rows=274318 width=105) - Seq Scan on mm_audiosources mm_mediasources (cost=0.00..1.01 rows=1 width=84) - Seq Scan on mm_videosources mm_mediasources (cost=0.00..1.09 rows=9 width=89) and presumably because if that performs so lousy. Simply selecting on a number does work fast: media=# explain SELECT * FROM mm_mediasources where number = 606973 ; QUERY PLAN Result (cost=0.00..6.13 rows=4 width=105) - Append (cost=0.00..6.13 rows=4 width=105) - Index Scan using mediasource_object on mm_mediasources (cost=0.00..4.00 rows=2 width=105) Index Cond: (number = 606973) - Seq Scan on mm_audiosources mm_mediasources (cost=0.00..1.01 rows=1 width=84) Filter: (number = 606973) - Seq Scan on mm_videosources mm_mediasources (cost=0.00..1.11 rows=1 width=89) Filter: (number = 606973) (3ms) I suppose seq scans are used on the extensions because they contain so few records. All tables have index on number. How do I force it to use them also when I use order by? I use psql 7.3.2 Michiel -- Michiel Meeuwissen | Mediapark C101 Hilversum | +31 (0)35 6772979| I hate computers nl_NL eo_XX en_US| mihxil' | [] () | ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Wierd context-switching issue on Xeon
Same problem with dual 1Ghz P3's running Postgres 7.4.2, linux 2.4.x, and 2GB ram, under load, with long transactions (i.e. 1 cannot serialize rollback per minute). 200K was the worst observed with vmstat. Finally moved DB to a single xeon box. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
