[PERFORM] PostgreSQL and Xeon MP
Hello, We are experiencing performances problem with a quad Xeon MP and PostgreSQL 7.4 for a year now. Our context switch rate is not so high but the load of the server is blocked to 4 even on very high load and we have 60% cpu idle even in this case. Our database fits in RAM and we don't have any IO problem. I saw this post from Tom Lane http://archives.postgresql.org/pgsql-performance/2004-04/msg00249.php and several other references to problem with Xeon MP and I suspect our problems are related to this. We tried to put our production load on a dual standard Xeon on monday and it performs far better with the same configuration parameters. I know that work has been done by Tom for PostgreSQL 8.1 on multiprocessor support but I didn't find any information on if it solves the problem with Xeon MP or not. My question is should we expect a resolution of our problem by switching to 8.1 or will we still have problems and should we consider a hardware change? We will try to upgrade next tuesday so we will have the real answer soon but if anyone has any experience or information on this, he will be very welcome. Thanks for your help. -- Guillaume ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] PostgreSQL and Xeon MP
Guillaume Smet wrote: Hello, We are experiencing performances problem with a quad Xeon MP and PostgreSQL 7.4 for a year now. I had a similar issue with a client the other week. Our context switch rate is not so high but the load of the server is blocked to 4 even on very high load and we have 60% cpu idle even in this case. Our database fits in RAM and we don't have any IO problem. Actually, I think that's part of the problem - it's the memory bandwidth. I saw this post from Tom Lane http://archives.postgresql.org/pgsql-performance/2004-04/msg00249.php and several other references to problem with Xeon MP and I suspect our problems are related to this. You should be seeing context-switching jump dramatically if it's the classic multi-Xeon problem. There's a point at which it seems to just escalate without a corresponding jump in activity. We tried to put our production load on a dual standard Xeon on monday and it performs far better with the same configuration parameters. I know that work has been done by Tom for PostgreSQL 8.1 on multiprocessor support but I didn't find any information on if it solves the problem with Xeon MP or not. I checked with Tom last week. Thread starts below: http://archives.postgresql.org/pgsql-hackers/2006-02/msg01118.php He's of the opinion that 8.1.3 will be an improvement. My question is should we expect a resolution of our problem by switching to 8.1 or will we still have problems and should we consider a hardware change? We will try to upgrade next tuesday so we will have the real answer soon but if anyone has any experience or information on this, he will be very welcome. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] BETWEEN optimizer problems with single-value
On Wed, 2006-03-15 at 21:05 -0500, Tom Lane wrote: So we need a more accurate estimate for the boundary case. Agreed. Using 1.0e-10 isn't very useful... the selectivity for a range should never be less than the selectivity for an equality, so we should simply put in a test against one of the pseudo constants and use that as the minimal value. That's easier said than done, because you'd first have to find the appropriate equality operator to use (ie, one having semantics that agree with the inequality operators). ... Kevin: this is also the reason we can't simply transform the WHERE clause into a more appropriate form... Possibly we could drop this code's reliance on seeing SCALARLTSEL/SCALARGTSEL as the estimators, and instead try to locate a common btree opclass for the operators --- which would then let us identify the right equality operator to use, and also let us distinguish from = etc. If we're trying to get the boundary cases right I suspect we have to account for that. I could see such an approach being tremendously slow though :-(, because we'd go looking for btree opclasses even for operators that have nothing to do with or . Trying to get the information in the wrong place would be very expensive, I agree. But preparing that information when we have access to it and passing it through the plan would be much cheaper. Relating op-opclass will be very useful in other places in planning, even if any one case seems not to justify the work to record it. (This case feels like deja vu, all over again.) The operator and the opclass are only connected via an index access method, but for a particular index each column has only one opclass. So the opclass will have a 1-1 correspondence with the operator for *that* plan only, realising that other plans might have different correspondences. find_usable_indexes() or thereabouts could annotate a restriction OpExpr with the opclass it will use. Once we have the link, clauselist_selectivity() can trivially compare opclasses for both OpExprs, then retrieve other information for that opclass for various purposes. Seems lots of work for such a corner case, but would be worth it if this solves other problems as well. Best Regards, Simon Riggs ---(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] PostgreSQL and Xeon MP
Richard, You should be seeing context-switching jump dramatically if it's the classic multi-Xeon problem. There's a point at which it seems to just escalate without a corresponding jump in activity. No we don't have this problem of very high context switching in our case even when the database is very slow. When I mean very slow, we have pages which loads in a few seconds in the normal case (load between 3 and 4) which takes several minutes (up to 5-10 minutes) to be generated in the worst case (load at 4 but really bad performances). If I take a look on our cpu load graph, in one year, the cpu load was never higher than 5 even in the worst cases... I checked with Tom last week. Thread starts below: http://archives.postgresql.org/pgsql-hackers/2006-02/msg01118.php He's of the opinion that 8.1.3 will be an improvement. Thanks for pointing me this thread, I searched in -performance not in -hackers as the original thread was in -performance. We planned a migration to 8.1.3 so we'll see what happen with this version. Do you plan to test it before next tuesday? If so, I'm interested in your results. I'll post our results here as soon as we complete the upgrade. -- Guillaume ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL and Xeon MP
Hi Guillaume, I had a similar issue last summer. Could you please provide details about your XEON MP server and some statistics (context-switches/load/CPU usage)? I tried different servers (x86) with different results. I saw a difference between XEON MP w/ and w/o EMT64. The memory bandwidth makes also a difference. What version of XEON MP does your server have? Which type of RAM does you server have? Do you use Hyperthreading? You should provide details from the XEON DP? Regards Sven. Guillaume Smet schrieb: Richard, You should be seeing context-switching jump dramatically if it's the classic multi-Xeon problem. There's a point at which it seems to just escalate without a corresponding jump in activity. No we don't have this problem of very high context switching in our case even when the database is very slow. When I mean very slow, we have pages which loads in a few seconds in the normal case (load between 3 and 4) which takes several minutes (up to 5-10 minutes) to be generated in the worst case (load at 4 but really bad performances). If I take a look on our cpu load graph, in one year, the cpu load was never higher than 5 even in the worst cases... I checked with Tom last week. Thread starts below: http://archives.postgresql.org/pgsql-hackers/2006-02/msg01118.php He's of the opinion that 8.1.3 will be an improvement. Thanks for pointing me this thread, I searched in -performance not in -hackers as the original thread was in -performance. We planned a migration to 8.1.3 so we'll see what happen with this version. Do you plan to test it before next tuesday? If so, I'm interested in your results. I'll post our results here as soon as we complete the upgrade. -- Guillaume ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- /This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient, you should not copy it, re-transmit it, use it or disclose its contents, but should return it to the sender immediately and delete your copy from your system. Thank you for your cooperation./ Sven Geisler [EMAIL PROTECTED] Tel +49.30.5362.1627 Fax .1638 Senior Developer,AEC/communications GmbHBerlin, Germany ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] PostgreSQL and Xeon MP
Sven, On 3/16/06, Sven Geisler [EMAIL PROTECTED] wrote: What version of XEON MP does your server have? The server is a dell 6650 from end of 2004 with 4 xeon mp 2.2 and 2MB cache per proc. Here are the information from Dell: 4x PROCESSOR, 80532, 2.2GHZ, 2MB cache, 400Mhz, SOCKET F 8x DUAL IN-LINE MEMORY MODULE, 512MB, 266MHz Do you use Hyperthreading? No, we don't use it. You should provide details from the XEON DP? The only problem is that the Xeon DP is installed with a 2.6 kernel and a postgresql 8.1.3 (it is used to test the migration from 7.4 to 8.1.3). So it's very difficult to really compare the two behaviours. It's a Dell 2850 with: 2 x PROCESSOR, 80546K, 2.8G, 1MB cache, XEON NOCONA, 800MHz 4 x DUAL IN-LINE MEMORY MODULE, 1GB, 400MHz This server is obviously newer than the other one. -- Guillaume ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] PostgreSQL and Xeon MP
On 3/16/06, Sven Geisler [EMAIL PROTECTED] wrote: Hi Guillaume, I had a similar issue last summer. Could you please provide details about your XEON MP server and some statistics (context-switches/load/CPU usage)? I forgot the statistics: CPU load usually from 1 to 4. CPU usage 40% for each processor usually and sometimes when the server completely hangs, it grows to 60%.., Here is a top output of the server at this time: 15:21:17 up 138 days, 13:25, 1 user, load average: 1.29, 1.25, 1.38 82 processes: 81 sleeping, 1 running, 0 zombie, 0 stopped CPU states: cpuusernice systemirq softirq iowaitidle total 25.7%0.0%3.9% 0.0% 0.3%0.1% 69.7% cpu00 29.3%0.0%4.7% 0.1% 0.5%0.0% 65.0% cpu01 20.7%0.0%1.9% 0.0% 0.3%0.0% 76.8% cpu02 25.5%0.0%5.5% 0.0% 0.1%0.3% 68.2% cpu03 27.3%0.0%3.3% 0.0% 0.1%0.1% 68.8% Mem: 3857224k av, 3298580k used, 558644k free, 0k shrd, 105172k buff 2160124k actv, 701304k in_d, 56400k in_c Swap: 4281272k av,6488k used, 4274784k free 2839348k cached We have currently between 3000 and 13000 context switches/s, average of 5000 I'd say visually. Here is a top output I had on november 17 when the server completely hangs (several minutes for each page of the website) and it is typical of this server behaviour: 17:08:41 up 19 days, 15:16, 1 user, load average: 4.03, 4.26, 4.36 288 processes: 285 sleeping, 3 running, 0 zombie, 0 stopped CPU states: cpuusernice systemirq softirq iowaitidle total 59.0%0.0%8.8% 0.2% 0.0%0.0% 31.9% cpu00 52.3%0.0% 13.3% 0.9% 0.0%0.0% 33.3% cpu01 65.7%0.0%7.6% 0.0% 0.0%0.0% 26.6% cpu02 58.0%0.0%7.6% 0.0% 0.0%0.0% 34.2% cpu03 60.0%0.0%6.6% 0.0% 0.0%0.0% 33.3% Mem: 3857224k av, 3495880k used, 361344k free, 0k shrd, 92160k buff 2374048k actv, 463576k in_d, 37708k in_c Swap: 4281272k av, 25412k used, 4255860k free 2173392k cached As you can see, load is blocked to 4, no iowait and cpu idle of 30%. Vmstat showed 5000 context switches/s on average so we had no context switch storm. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL and Xeon MP
Guillaume Smet [EMAIL PROTECTED] writes: Here is a top output I had on november 17 when the server completely hangs (several minutes for each page of the website) and it is typical of this server behaviour: 17:08:41 up 19 days, 15:16, 1 user, load average: 4.03, 4.26, 4.36 288 processes: 285 sleeping, 3 running, 0 zombie, 0 stopped CPU states: cpuusernice systemirq softirq iowaitidle total 59.0%0.0%8.8% 0.2% 0.0%0.0% 31.9% cpu00 52.3%0.0% 13.3% 0.9% 0.0%0.0% 33.3% cpu01 65.7%0.0%7.6% 0.0% 0.0%0.0% 26.6% cpu02 58.0%0.0%7.6% 0.0% 0.0%0.0% 34.2% cpu03 60.0%0.0%6.6% 0.0% 0.0%0.0% 33.3% Mem: 3857224k av, 3495880k used, 361344k free, 0k shrd, 92160k buff 2374048k actv, 463576k in_d, 37708k in_c Swap: 4281272k av, 25412k used, 4255860k free 2173392k cached As you can see, load is blocked to 4, no iowait and cpu idle of 30%. Can you try strace'ing some of the backend processes while the system is behaving like this? I suspect what you'll find is a whole lot of delaying select() calls due to high contention for spinlocks ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL and Xeon MP
Hi Guillaume, Guillaume Smet schrieb: The server is a dell 6650 from end of 2004 with 4 xeon mp 2.2 and 2MB cache per proc. Here are the information from Dell: 4x PROCESSOR, 80532, 2.2GHZ, 2MB cache, 400Mhz, SOCKET F 8x DUAL IN-LINE MEMORY MODULE, 512MB, 266MHz You should provide details from the XEON DP? The only problem is that the Xeon DP is installed with a 2.6 kernel and a postgresql 8.1.3 (it is used to test the migration from 7.4 to 8.1.3). So it's very difficult to really compare the two behaviours. It's a Dell 2850 with: 2 x PROCESSOR, 80546K, 2.8G, 1MB cache, XEON NOCONA, 800MHz 4 x DUAL IN-LINE MEMORY MODULE, 1GB, 400MHz Did you compare 7.4 on a 4-way with 8.1 on a 2-way? How many queries and clients did you use to test the performance? How much faster is the XEON DP? I think, you can expect that your XEON DP is faster on a single query because CPU and RAM are faster. The overall performance can be better on your XEON DP if you only have a few clients. I guess, the newer hardware and the newer PostgreSQL version cause the better performance. Regards Sven. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] BETWEEN optimizer problems with single-value
Simon Riggs [EMAIL PROTECTED] writes: Trying to get the information in the wrong place would be very expensive, I agree. But preparing that information when we have access to it and passing it through the plan would be much cheaper. Where would that be? The operator and the opclass are only connected via an index access method, but for a particular index each column has only one opclass. If you're proposing making clauselist_selectivity depend on what indexes exist, I think that's very much the wrong approach. In the first place, it still has to give usable answers for unindexed columns, and in the second place there might be multiple indexes with different opclasses for the same column, so the ambiguity problem still exists. I have been wondering if we shouldn't add some more indexes on pg_amop or something to make it easier to do this sort of lookup --- we definitely seem to be finding multiple reasons to want to look up which opclasses contain a given operator. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] PostgreSQL and Xeon MP
On 3/16/06, Sven Geisler [EMAIL PROTECTED] wrote: Did you compare 7.4 on a 4-way with 8.1 on a 2-way? I know there are too many parameters changing between the two servers but I can't really change anything before tuesday. On tuesday, we will be able to compare both servers with the same software. How many queries and clients did you use to test the performance? Googlebot is indexing this site generating 2-3 mbits/s of traffic so we use the googlebot to stress this server. There was a lot of clients and a lot of queries. How much faster is the XEON DP? Well, on high load, PostgreSQL scales well on the DP (load at 40, queries slower but still performing well) and is awfully slow on the MP box. ---(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] PostgreSQL and Xeon MP
On 3/16/06, Tom Lane [EMAIL PROTECTED] wrote: Can you try strace'ing some of the backend processes while the system is behaving like this? I suspect what you'll find is a whole lot of delaying select() calls due to high contention for spinlocks ... Tom, I think we can try to do it. You mean strace -p pid with pid on some of the postgres process not on the postmaster itself, does you? Do we need other options? Which pattern should we expect? I'm not really familiar with strace and its output. Thanks for your help. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL and Xeon MP
Guillaume Smet [EMAIL PROTECTED] writes: You mean strace -p pid with pid on some of the postgres process not on the postmaster itself, does you? Right, pick a couple that are accumulating CPU time. Do we need other options? strace will generate a *whole lot* of output to stderr. I usually do something like strace -p pid 2outfile and then control-C it after a few seconds. Which pattern should we expect? What we want to find out is if there's a lot of select()s and/or semop()s shown in the result. Ideally there wouldn't be any, but I fear that's not what you'll find. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] PostgreSQL and Xeon MP
Hi Guillaume, Guillaume Smet schrieb: How much faster is the XEON DP? Well, on high load, PostgreSQL scales well on the DP (load at 40, queries slower but still performing well) and is awfully slow on the MP box. I know what you mean with awfully slow. I think, your application is facing contention. The contention becomes larger as more CPU you have. PostgreSQL 8.1 is addressing contention on multiprocessor servers as you mentioned before. I guess, you will see that your 4-way XEON MP isn't that bad if you compare both servers with the same PostgreSQL version. Regards Sven. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL and Xeon MP
On 3/16/06, Tom Lane [EMAIL PROTECTED] wrote: What we want to find out is if there's a lot of select()s and/or semop()s shown in the result. Ideally there wouldn't be any, but I fear that's not what you'll find. OK, I'll try to do it on monday before our upgrade then see what happens with PostgreSQL 8.1.3. Thanks for your help. ---(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] Background writer configuration
Kevin, please, could you post other settings from your postgresql.conf? interested in: bgwriter_delay shared_buffers checkpoint_segments checkpoint_timeout wal_buffers On Wed, 15 Mar 2006 13:43:45 -0600 Kevin Grittner [EMAIL PROTECTED] wrote: We were seeing clusters of query timeouts with our web site, which were corrected by adjusting the configuration of the background writer. I'm posting just to provide information which others might find useful -- I don't have any problem I'm trying to solve in this regard. -- Evgeny Gridasov Software Engineer I-Free, Russia ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] 1 TB of memory
PostgreSQL tuned to the max and still too slow? Database too big to fit into memory? Here's the solution! http://www.superssd.com/ products/tera-ramsan/ Anyone purchasing one will be expected to post benchmarks! :) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] 1 TB of memory
Jim Nasby wrote: PostgreSQL tuned to the max and still too slow? Database too big to fit into memory? Here's the solution! http://www.superssd.com/products/tera-ramsan/ Anyone purchasing one will be expected to post benchmarks! :) And give us one :) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] BETWEEN optimizer problems with single-value
On Thu, 2006-03-16 at 10:57 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Trying to get the information in the wrong place would be very expensive, I agree. But preparing that information when we have access to it and passing it through the plan would be much cheaper. Where would that be? The operator and the opclass are only connected via an index access method, but for a particular index each column has only one opclass. If you're proposing making clauselist_selectivity depend on what indexes exist, I think that's very much the wrong approach. Using available information sounds OK to me. Guess you're thinking of the lack of plan invalidation? In the first place, it still has to give usable answers for unindexed columns, and in the second place there might be multiple indexes with different opclasses for the same column, so the ambiguity problem still exists. I was thinking that we would fill out the OpExpr with different opclasses for each plan, so each one sees a different story. (I was thinking there was a clauselist for each plan; if not, there could be.) So the multiple index problem shouldn't exist. Non-indexed cases still cause the problem, true. I have been wondering if we shouldn't add some more indexes on pg_amop or something to make it easier to do this sort of lookup --- we definitely seem to be finding multiple reasons to want to look up which opclasses contain a given operator. Agreed, but still looking for better way than that. [BTW how do you add new indexes to system tables? I want to add one to pg_inherits but not sure where to look.] Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] BETWEEN optimizer problems with single-value
Simon Riggs wrote: [BTW how do you add new indexes to system tables? I want to add one to pg_inherits but not sure where to look.] See src/include/catalog/indexing.h -- I don't remember if there's anything else that needs modification. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] BETWEEN optimizer problems with single-value
On Thu, 2006-03-16 at 15:41 -0400, Alvaro Herrera wrote: Simon Riggs wrote: [BTW how do you add new indexes to system tables? I want to add one to pg_inherits but not sure where to look.] See src/include/catalog/indexing.h -- I don't remember if there's anything else that needs modification. That was easy: many thanks! Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] BETWEEN optimizer problems with single-value
Simon Riggs [EMAIL PROTECTED] writes: I was thinking that we would fill out the OpExpr with different opclasses for each plan, so each one sees a different story. (I was thinking there was a clauselist for each plan; if not, there could be.) This is backwards: there isn't a plan yet. If there were, having clauselist_selectivity return different answers depending on what index the plan was thinking of using would still be wrong. [BTW how do you add new indexes to system tables? I want to add one to pg_inherits but not sure where to look.] src/include/catalog/indexing.h Offhand I think adding a new entry is all you have to do. You may also want a syscache to go with it, which'll take a bit more work. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] BETWEEN optimizer problems with single-value
On Thu, 2006-03-16 at 14:45 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: [BTW how do you add new indexes to system tables? I want to add one to pg_inherits but not sure where to look.] src/include/catalog/indexing.h Offhand I think adding a new entry is all you have to do. You may also want a syscache to go with it, which'll take a bit more work. I see its actually postgres.bki... I never scrolled to the bottom before now. I'll have a go. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Indexes with descending date columns
Hi I have a performance problem when traversing a table in index order with multiple columns including a date column in date reverse order. Below follows a simplified description of the table, the index and the associated query \d prcdedit prcdedit_prcd | character(20) | prcdedit_date | timestamp without time zone | Indexes: prcdedit_idx btree (prcdedit_prcd, prcdedit_date) When invoking a query such as select oid, prcdedit_prcd, prcdedit_date, 'dd/mm/ hh24:mi:ss') as mydate where prcdedit_prcd 'somevalue' order by prcdedit_prcd, prcdedit_date desc; the peformance is dismal. However removing the 'desc' qualifier as follows the query flys select oid, prcdedit_prcd, prcdedit_date, 'dd/mm/ hh24:mi:ss') as mydate where prcdedit_prcd 'somevalue' order by prcdedit_prcd, prcdedit_date; PostgreSQL Version = 8.1.2 Row count on the table is 30 Explain is as follows for desc Limit (cost=81486.35..81486.41 rows=25 width=230) (actual time=116619.652..116619.861 rows=25 loops=1) - Sort (cost=81486.35..82411.34 rows=369997 width=230) (actual time=116619.646..116619.729 rows=25 loops=1) Sort Key: prcdedit_prcd, prcdedit_date, oid - Bitmap Heap Scan on prcdedit (cost=4645.99..23454.94 rows=369997 width=230) (actual time=376.952..11798.834 rows=369630 loops=1) Recheck Cond: (prcdedit_prcd '063266 '::bpchar) - Bitmap Index Scan on prcdedit_idx (cost=0.00..4645.99 rows=369997 width=0) (actual time=366.048..366.048 rows=369630 loops=1) Index Cond: (prcdedit_prcd '063266 '::bpchar) Total runtime: 116950.175 ms and as follows when I remove the 'desc' Limit (cost=0.00..2.34 rows=25 width=230) (actual time=0.082..0.535 rows=25 loops=1) - Index Scan using prcdedit_idx on prcdedit (cost=0.00..34664.63 rows=369997 width=230) (actual time=0.075..0.405 rows=25 loops=1) Index Cond: (prcdedit_prcd '063266 '::bpchar) Total runtime: 0.664 ms Any assistance/advice much appreciated. -- Regards Theo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] 1 TB of memory
The US Dept of Homeland Security has at least two =10=TB SSDs. begin speculation Rumor is they are being used for Carnivore or an offshoot/descendent of Carnivore. end speculation Good luck getting them to give you benchmark data. You need deep pockets to afford = 1TB of SSD. (...and as the example shows, perhaps more money than sense.) Ron -Original Message- From: Jim Nasby [EMAIL PROTECTED] Sent: Mar 16, 2006 1:33 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] 1 TB of memory PostgreSQL tuned to the max and still too slow? Database too big to fit into memory? Here's the solution! http://www.superssd.com/ products/tera-ramsan/ Anyone purchasing one will be expected to post benchmarks! :) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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] 1 TB of memory
On 3/16/06, Jim Nasby [EMAIL PROTECTED] wrote: PostgreSQL tuned to the max and still too slow? Database too big to fit into memory? Here's the solution! http://www.superssd.com/ products/tera-ramsan/ Anyone purchasing one will be expected to post benchmarks! :) Pricing is tight-lipped, but searching shows $1.85 /GB. That's close to $500,000 for 250GB. One report says a person paid $219,000 for 32GB and 1TB costs well over $1,000,000. But they guarantee the performance. Too rich for me. -- Matthew Nuzum www.bearfruit.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Help optimizing a slow index scan
explain analyze select distinct eventmain.incidentid, eventmain.entrydate, eventgeo.long, eventgeo.lat, eventgeo.geox, eventgeo.geoy from eventmain, eventgeo where eventmain.incidentid = eventgeo.incidentid and ( long -104.998027962962 and long -104.985957781349 ) and ( lat 39.7075542720006 and lat 39.7186195832938 ) and eventmain.entrydate '2006-1-1 00:00' and eventmain.entrydate = '2006-3-17 00:00' order by eventmain.entrydate; QUERY PLAN - Unique (cost=121313.81..121330.72 rows=451 width=178) (actual time=723719.761..723726.875 rows=1408 loops=1) - Sort (cost=121313.81..121314.94 rows=451 width=178) (actual time=723719.755..723721.807 rows=1408 loops=1) Sort Key: eventmain.entrydate, eventmain.disposition, eventmain.incidentid, eventgeo.reportingarea, eventgeo.beatid, eventmain.finaltype, eventmain.casenumber, eventgeo.eventlocation, eventmain.insertdate, eventmain.priority, eventgeo.long, eventgeo.lat, eventgeo.geox, eventgeo.geoy - Nested Loop (cost=0.00..121293.93 rows=451 width=178) (actual time=1916.230..723712.900 rows=1408 loops=1) - Index Scan using eventgeo_lat_idx on eventgeo (cost=0.00..85488.05 rows=10149 width=76) (actual time=0.402..393376.129 rows=22937 loops=1) Index Cond: ((lat 39.7075542720006::double precision) AND (lat 39.7186195832938::double precision)) Filter: ((long -104.998027962962::double precision) AND (long -104.985957781349::double precision)) - Index Scan using eventmain_incidentid_idx on eventmain (cost=0.00..3.52 rows=1 width=119) (actual time=14.384..14.392 rows=0 loops=22937) Index Cond: ((eventmain.incidentid)::text = (outer.incidentid)::text) Filter: ((entrydate '2006-01-01 00:00:00'::timestamp without time zone) AND (entrydate = '2006-03-17 00:00:00'::timestamp without time zone)) Total runtime: 723729.238 ms(!) I'm trying to figure out why it's consuming so much time on the index scan for eventgeo_lat_idx. Also, I have an index on long that the planner does not appear to find helpful. There are 3.3 million records in eventmain and eventgeo. The server has a reasonably fast RAID10 setup with 16x 15k RPM drives and 12GB of RAM ( 11GB listed as cache by vmstat ). Running version 8.0.2 on linux kernel 2.6.12. I have just vacuum analyze'd both tables, rebuilt the eventgeo_lat_idx index and reran the query multiple times to see if caching helped ( it didn't help much ). The server seems to be fine utilizing other fields from this table but using long and lat seem to drag it down significantly. Is it because there's such slight differences between the records, since they are all within a few hundredths of a degree from each other? Thanks for your time and ideas. -Dan ---(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] Background writer configuration
On Thu, Mar 16, 2006 at 12:15 pm, in message [EMAIL PROTECTED], Evgeny Gridasov [EMAIL PROTECTED] wrote: please, could you post other settings from your postgresql.conf? Everything in postgresql.conf which is not commented out: listen_addresses = '*' # what IP interface(s) to listen on; max_connections = 600 # note: increasing max_connections costs shared_buffers = 2 # min 16 or max_connections*2, 8KB each work_mem = 10240# min 64, size in KB max_fsm_pages = 140 # min max_fsm_relations*16, 6 bytes each bgwriter_lru_percent = 20.0 # 0-100% of LRU buffers scanned/round bgwriter_lru_maxpages = 200 # 0-1000 buffers max written/round bgwriter_all_percent = 10.0 # 0-100% of all buffers scanned/round bgwriter_all_maxpages = 600 # 0-1000 buffers max written/round full_page_writes = off # recover from partial page writes wal_buffers = 20# min 4, 8KB each checkpoint_segments = 10# in logfile segments, min 1, 16MB each effective_cache_size = 524288 # typically 8KB each random_page_cost = 2# units are one sequential page fetch redirect_stderr = on# Enable capturing of stderr into log log_line_prefix = '[%m] %p %q%u %d %r ' # Special values: stats_start_collector = on stats_block_level = on stats_row_level = on autovacuum = true # enable autovacuum subprocess? autovacuum_naptime = 10 # time between autovacuum runs, in secs autovacuum_vacuum_threshold = 1 # min # of tuple updates before autovacuum_analyze_threshold = 1# min # of tuple updates before autovacuum_vacuum_scale_factor = 0.2# fraction of rel size before autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before lc_messages = 'C' # locale for system error message lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C'# locale for number formatting lc_time = 'C' # locale for time formatting sql_inheritance = off standard_conforming_strings = on ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] 1 TB of memory
Jim, PostgreSQL tuned to the max and still too slow? Database too big to fit into memory? Here's the solution! http://www.superssd.com/ products/tera-ramsan/ With a single 3 Gbyte/second infiniband connection to the device? You'd be better off with 4 x $10K servers that do 800MB/s from disk each and a Bizgres MPP - then you'd do 3.2GB/s (faster than the SSD) at a price 1/10 of the SSD, and you'd have 24TB of RAID5 disk under you. Plus - need more speed? Add 12 more servers, and you'd run at 12.8GB/s and have 96TB of disk to work with, and you'd *still* spend less on HW and SW than the SSD. - Luke ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] 1 TB of memory
Jim, On 3/16/06 10:44 PM, Luke Lonergan [EMAIL PROTECTED] wrote: Plus - need more speed? Add 12 more servers, and you'd run at 12.8GB/s and have 96TB of disk to work with, and you'd *still* spend less on HW and SW than the SSD. And I forgot to mention that with these 16 servers you'd have 64 CPUs and 256GB of RAM working for you in addition to the 96TB of disk. Every query would use all of that RAM and all of those CPUs, all at the same time. By comparison, with the SSD, you'd have 1 CPU trying to saturate 1 connection to the SSD. If you do anything other than just access the data there (order by, group by, join, aggregation, functions), you'll be faced with trying to have 1 CPU do all the work on 1 TB of data. I suggest that it won't be any faster than having the 1 TB on disk for most queries, as you would be CPU bound. By comparison, with the MPP system, all 64 CPUs would be used at one time to process the N TB of data and if you grew from N TB to 2N TB, you could double the machine size and it would take the same amount of time to do 2N as it did to do N. That's what data parallelism and scaling is all about. Without it, you don't have a prayer of using all 1TB of data in queries. - Luke ---(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] 1 TB of memory
Luke, With a single 3 Gbyte/second infiniband connection to the device? Hey, take it easy! Jim's post was tongue-in-cheek. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Indexes with descending date columns
I have a performance problem when traversing a table in index order with multiple columns including a date column in date reverse order. Below follows a simplified description of the table, the index and the associated query \d prcdedit prcdedit_prcd | character(20) | prcdedit_date | timestamp without time zone | Indexes: prcdedit_idx btree (prcdedit_prcd, prcdedit_date) Depending on how you use the table, there are three possible solutions. First, if it makes sense in the domain, using an ORDER BY where _both_ columns are used descending will make PG search the index in reverse and will be just as fast as when both as searched by the default ascending. Second possibility: Create a dummy column whose value depends on the negative of prcdedit_date, e.g., -extract(epoch from prcdedit_date), keep the dummy column in sync with the original column using triggers, and rewrite your queries to use ORDER BY prcdedit_prod, dummy_column. Third: Create an index on a function which sorts in the order you want, and then always sort using the function index (you could use the -extract(epoch...) gimmick for that, among other possibilities.) HTH. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
