Re: [PERFORM] Wierd context-switching issue on Xeon patch for 7.4.1
On Thu, 2004-04-22 at 10:37 -0700, Josh Berkus wrote: Tom, The tricky part is that a slow adaptation rate means we can't have every backend figuring this out for itself --- the right value would have to be maintained globally, and I'm not sure how to do that without adding a lot of overhead. This may be a moot point, since you've stated that changing the loop timing won't solve the problem, but what about making the test part of make? I don't think too many systems are going to change processor architectures once in production, and those that do can be told to re-compile. Sure they do - PostgreSQL is regularly provided as a pre-compiled distribution. I haven't compiled PostgreSQL for years, and we have it running on dozens of machines, some SMP, some not, but most running Debian Linux. Even having a compiler _installed_ on one of our client's database servers would usually be considered against security procedures, and would get a black mark when the auditors came through. Regards, Andrew McMillan - Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 Planning an election? Call us! - ---(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] Why will vacuum not end?
It is set at max_fsm_pages = 150 . We are running a DELL PowerEdge 6650 with 4 CPU's Mem: 3611320k av from top. The database is on a shared device (SAN) raid5, 172 GB. Qlogic Fibre optic cards(desc: QLogic Corp.|QLA2312 Fibre Channel Adapter) connected to the Dell version of an EMC SAN (FC4700 I believe). I have set vacuum_mem = 917504; and started another vacuum verbose on the table in question. Tried to set vacuum_mem to 1114112 and vacuum failed, then tried 917504 and vacuum started. PWFPM_DEV=# set vacuum_mem = '1114112'; SET PWFPM_DEV=# show vacuum_mem; vacuum_mem 1114112 (1 row) PWFPM_DEV=# vacuum verbose forecastelement; INFO: vacuuming public.forecastelement ERROR: invalid memory alloc request size 1140850686 PWFPM_DEV=# set vacuum_mem = 917504; SET PWFPM_DEV=# show vacuum_mem; vacuum_mem 917504 (1 row) PWFPM_DEV=# select now();vacuum verbose forecastelement;select now(); now --- 2004-04-25 01:40:23.367123+00 (1 row) INFO: vacuuming public.forecastelement I performed a query that used a seqscan PWFPM_DEV=# explain analyze select count(*) from forecastelement; QUERY PLAN --- Aggregate (cost=16635987.60..16635987.60 rows=1 width=0) (actual time=1352.844..1352.847 rows=1 loops=1) - Seq Scan on forecastelement (cost=0.00..15403082.88 rows=493161888 width=0) (actual time=243.562..12692714.422 rows=264422681 loops=1) Total runtime: 13111221.978 ms (3 rows) Dan. -Original Message- From: Manfred Koizar [mailto:[EMAIL PROTECTED] Sent: Saturday, April 24, 2004 8:29 PM To: Shea,Dan [CIS] Cc: 'Josh Berkus'; [EMAIL PROTECTED] Subject: Re: [PERFORM] Why will vacuum not end? On Sat, 24 Apr 2004 15:58:08 -0400, Shea,Dan [CIS] [EMAIL PROTECTED] wrote: There were defintely 219,177,133 deletions. The deletions are most likely from the beginning, it was based on the reception_time of the data. I would rather not use re-index, unless it is faster then using vacuum. I don't know whether it would be faster. But if you decide to reindex, make sure sort_mem is *huge*! What do you think would be the best way to get around this? Increase vacuum_mem to a higher amount 1.5 to 2 GB or try a re-index (rather not re-index so that data can be queried without soing a seqscan). Just out of curiosity: What kind of machine is this running on? And how long does a seq scan take? Once the index is cleaned up, how does vacuum handle the table? If you are lucky VACUUM frees half the index pages. And if we assume that the most time spent scanning an index goes into random page accesses, future VACUUMs will take only 3 seconds per index scan. Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Number of pages in a random sample (was: query slows down with more accurate stats)
On Mon, 19 Apr 2004 12:00:10 -0400, Tom Lane [EMAIL PROTECTED] wrote: 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? I would have replied earlier, if I had a good answer. What I have so far contains at least one, probably two flaws. Knowing not much more than the four basic arithmetic operations I was not able to improve my model. So I post what I have: As usual we assume a constant number c of tuples per page. If we have a table of size B pages and want to collect a sample of n tuples, the number of possible samples is (again in OOo syntax) left( binom{cB}{n} right) If we select an arbitrary page, the number of possible samples that do NOT contain any tuple from this page is left( binom {c (B-1)} {n} right) Let's forget about our actual implementations of sampling methods and pretend we have a perfect random sampling method. So the probability Pnot(c, B, n) that a certain page is not represented in a random sample is left( binom {c (B-1)} {n} right) over left( binom{cB}{n} right) which can be transformed into the more computing-friendly form prod from{i=0} to{n-1} {{cB-c - i} over {cB - i}} Clearly the probability that a certain page *is* represented in a sample is Pyes(c, B, n) = 1 - Pnot(c, B, n) The next step assumes that these probabilities are independent for different pages, which in reality they are not. We simply estimate the number of pages represented in a random sample as numPag(c, B, n) = B * Pyes(c, B, n) Here are some results for n = 3000: B \ c-10 | 100 | 200 ---+---+---+--- 100 | --- | 100 | 100 1000 | 972 | 953 | 951 2000 | 1606 | 1559 | 1556 3000 | 1954 | 1902 | 1899 6000 | 2408 | 2366 | 2363 9000 | 2588 | 2555 | 2553 2 | 2805 | 2788 | 2787 3 | 2869 | 2856 | 2856 10 | 2960 | 2956 | 2956 This doesn't look to depend heavily on the number of tuples per page, which sort of justifies the assumption that c is constant. In the next step I tried to estimate the number of pages that contain exactly 1, 2, ... tuples of the sample. My naive procedure works as follows (I'm not sure whether it is even valid as a rough approximation, constructive criticism is very welcome): For c=100, B=3000, n=3000 we expect 1902 pages to contain at least 1 tuple of the sample. There are 1098 more tuples than pages, these tuples lie somewhere in those 1902 pages from the first step. numPag(99, 1902, 1098) = 836 pages contain at least a second tuple. So the number of pages containing exactly 1 tuple is 1902 - 836 = 1066. Repeating these steps we get 611 pages with 2 tuples, 192 with 3, 30 with 4, and 3 pages with 5 tuples. Here are some more numbers for c = 100 and n = 3000: B | pages with 1, 2, ... tuples ---+ 100 | 1 to 24 tuples: 0, then 1, 2, 4, 10, 18, 26, 24, 11, 4 1000 | 108, 201, 268, 229, 113, 29, 5 2000 | 616, 555, 292, 83, 12, 1 3000 | 1066, 611, 192, 30, 3 6000 | 1809, 484, 68, 5 9000 | 2146, 374, 32, 2 2 | 2584, 196, 8 3 | 2716, 138, 3 10 | 2912, 44 A small C program to experimentally confirm or refute these calculations is attached. Its results are fairly compatible with above numbers, IMHO. Servus Manfred /* ** samsim.c - sampling simulator */ #include stdio.h #include stdlib.h #include sys/time.h #include unistd.h typedef int bool; #define MAX_RANDOM_VALUE (0x7FFF) static void initrandom() { struct timeval tv; gettimeofday(tv, NULL); srandom(tv.tv_sec ^ tv.tv_usec); }/*initrandom*/ /* Select a random value R uniformly distributed in 0 R 1 */ static double random_fract(void) { longz; /* random() can produce endpoint values, try again if so */ do { z = random(); } while (z = 0 || z = MAX_RANDOM_VALUE); return (double) z / (double) MAX_RANDOM_VALUE; } /* ** data structure for (modified) Algorithm S from Knuth 3.4.2 */ typedef struct { longN; /* number of tuples, known in advance */ int n; /* sample size */ longt; /* current tuple number */ int m; /* tuples selected so far */ } SamplerData; typedef SamplerData *Sampler; static void Sampler_Init(Sampler bs, long N, int samplesize); static bool Sampler_HasMore(Sampler bs); static long
Re: [PERFORM] Why will vacuum not end?
On Sun, 25 Apr 2004 09:05:11 -0400, Shea,Dan [CIS] [EMAIL PROTECTED] wrote: It is set at max_fsm_pages = 150 . This might be too low. Your index has ca. 5 M pages, you are going to delete half of its entries, and what you delete is a contiguous range of values. So up to 2.5 M index pages might be freed (minus inner nodes and pages not completely empty). And there will be lots of free heap pages too ... I wrote: If you are lucky VACUUM frees half the index pages. And if we assume that the most time spent scanning an index goes into random page accesses, future VACUUMs will take only 3 seconds per index scan. After a closer look at the code and after having slept over it I'm not so sure any more that the number of tuple ids to be removed has only minor influence on the time spent for a bulk delete run. After the current VACUUM has finished would you be so kind to run another VACUUM VERBOSE with only a few dead tuples and post the results here? Servus Manfred ---(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