Re: [SQL] same question little different test MSSQL vrs Postgres
Quoting Dennis Sacks [EMAIL PROTECTED]: Bruno Wolff III wrote: On Tue, Jan 25, 2005 at 21:21:08 -0700, Dennis Sacks [EMAIL PROTECTED] wrote: One of the things you'll want to do regularly is run a vacuum analyze. You can read up on this in the postgresql docs. This is essential to the indexes being used properly. At a bare minimum, after you import a large amount of data, you'll want to run vacuum analyze. Good point! Analyze after bulk inserts, vacuum analyze after updates/deletes and inserts. :) Hmmm ... in performance tests of bulk inserts into a table with six indexes, I found that without vacuum analyze (rather than analyze), insertion slowed down albeit something less than linearly. Table of 6M rows, about 3GB (including index files). This is 7.4.1 on SuSE Linux, RAID5 Xeon(sigh) 2.8GHz 4GB nothing else running. The inserts were always done with an existing record check (LEFT JOIN ... WHERE joinkey IS NULL). -- Dreams come true, not free. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] same question little different test MSSQL vrs Postgres
Now you tell me. We had a fellow working here kept screaming AMD, but I am a very paranoid person and was not aware Linux and Postgres have been running on the new chips. I don't like to be a first. We have bought the Dell and I cant tell you if the controller uses 64bits, I just got what they had on their page for their 4 proc rack mount. Part of my reason for going Dell was we already have Dell equipment and the Linux support is offered from Dell as well, so I have one vendor to worry about. Being a developer and Director of IT I want the fastest best, but sometimes I flavor my opinions with safest and easiest. The RPM delivery is something I understand (it's easy). What is SU like? Is there any difference in the performance between the two Vendors? I am sure we will be buying more Postgres servers in the near future (One of the big reasons we are taking the time to convert from MSSQL was so we could afford to invest in more servers MSSQL was cost prohibitive even for one server). As easy as Fedura was I still had several issues getting to where I am now, so I am paranoid of something that requires even more knowledge to pull it off; that being said I never minded getting into the details to get a better end result. As you said we have made the investment in the Dell (25K). I feel pretty stupid if it is as you say a waste of money to get 8 gigs on this platform as I just made that same mistake a year ago when I bought the 2 processor boxes with standard addition MSSQL and 4 gigs (It only uses 2 gig). I was under the impression this machine would utilize all 8 gigs. Are you saying only 4 will be available for caching etc, or just the chipset cant deal with numbers 8 gig and will be slower to access them? If it is the later then I would imagine it would still outperform a similar box with 4 gig assuming my demand on cache is larger then 4 gig. Just to confirm you have these quad Opteron (I am assuming a 4 processor config?) in a production environment running su and postgres with hardware support from HP and software from su? You indicate three separate physical drives will give best performance (one for data 10K speeds, one for admin, one for wall 15 speed)? I am not too sophisticated at knowing how to irder this arrangement and set it up in Linux, any chance you could detail (1 card with 2 channels 4 10k drives on one channel, 2 15k drives on the second, do I need another channel and drive(s) for admin files?), drive layout when installing config in postgres to utilize? If need be maybe we can get you to do this as a consultant as I do understand how important the hardware and the proper config is. I found out too late with MSSQL that I should have used two seprate drive arrays, one for data, one for log (this would have required the split back plane). So not to plug a specific vendor but if you have production environment example with real equipment suggestions I would be very appreciative. I know that's a lot to ask so if you don't have time that's cool, thanks so much for bringing this up so that my next purchase I will seriously look at quad Opteron technology if it is a tried and true solution for this OS and Postgres. Joel Fradkin -Original Message- From: Andrew Hammond [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 26, 2005 5:16 PM To: Joel Fradkin Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] same question little different test MSSQL vrs Postgres -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 We've done some pretty extensive benchmarking and load testing on a couple of platforms including the Xeon and Opteron. You may have already bought that Dell box, but I'll say it anyway. Xeon quad processors are a terrible platform for postgres. Trying to use more than 4GB of memory on a 32 bit machine is a waste of money. If you want performance, get a quad Opteron with the same amount of memory. I guarantee you'll see at least an order of magnitude performance improvement and substantially more under highly concurrent loads. If you decide to go this way, HP sells a very nice box. I also strongly recommend you investigate SuSE instead of RedHat. Fedora core is good technology, but SuSE offers equally good technology with better support. Also make sure that your SCSI HBA is actually using the 64 bit PCI bus. There are cards out there which plug into 64 bit PCI but only actually address 32 bits (Qlogic's QLA2340 / 2342 for example). You make no mention of the disk subsystem you plan to use. This is most critical part of your system. Database performance is almost always bound by IO. Usually disk IO. Briefly, put PGDATA on the widest RAID 10 array of disks you can manage. It's not worth spending the extra money to get 15kRPM disks for this. The size of the disks involved is pretty much irrelevant, only the number of them matters. Put the WAL files on a dedicated RAID 1 pair of 15kRPM disks. Put the postgres log files (or syslog) on a seperate filesystem. - -- Andrew Hammond
Re: [SQL] same question little different test MSSQL vrs Postgres
QUERY PLAN Seq Scan on tblcase (cost=0.00..30066.21 rows=37401 width=996) (actual time=0.344..962.260 rows=22636 loops=1) Filter: ((clientnum)::text = 'SAKS'::text) Total runtime: 1034.434 ms Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 26, 2005 1:27 AM To: Joel Fradkin Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] same question little different test MSSQL vrs Postgres Joel Fradkin [EMAIL PROTECTED] writes: I also tried a simple select * from tblcase where clientum = 'SAKS' Try: explain analyze select * from tblcase where clientum = 'SAKS' Send the output. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] same question little different test MSSQL vrs Postgres
Joel Fradkin wrote: QUERY PLAN Seq Scan on tblcase (cost=0.00..30066.21 rows=37401 width=996) (actual time=0.344..962.260 rows=22636 loops=1) Filter: ((clientnum)::text = 'SAKS'::text) Total runtime: 1034.434 ms That's only 1 second - to return 22,636 rows. Not 27 seconds, as in the original post. You'll never persuade PG to use the index when some 75% of your rows match the filter - it just doesn't make sense. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] same question little different test MSSQL vrs Postgres
Joel Fradkin wrote: Well last evening (did not try it this morning) it was taking the extra time. I have made some adjustments to the config file per a few web sites that you all recommended my looking at. The crucial one I'd say is the performance guide at: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php The first half-dozen settings are the crucial ones. It is now using 137 of 756 meg avail. it is now taking 8 secs to return 22,000 rows (using pgadminIII in a sql edit window). That might be too much RAM. Don't forget PG likes to work with your operating-system (unlike many other DBs). Make sure Windows is using enough RAM to cache diskspace. I'm curious as to how this takes 8secs whereas you had 1 second earlier. Are you sure some of this isn't pgadmin's overhead to display the rows? The EXPLAIN ANALYSE still shows the same as below, but the table has 344,000 recs of which only 22636 are clientnum = 'SAKS' That sounds like it's about the borderline between using an index and not (depending on cache-size, disk speeds etc). I am still doing a seq search (this applies to the view question where if it is a small result set it used a index search but on a larger return set it did a seq search) in my view, but with the adjustments to the kernel I get a result in 140 secs (MSSQL was 135 secs). If you want to check whether the index would help, try issuing the following before running your query: SET ENABLE_SEQSCAN=FALSE; This will force PG to use any index it can regardless of whether it thinks it will help. This is not production, I am still very worried that I have to do all this tweeking to use this, MSSQL worked out of the box as it does (not saying its great, but I never had to adjust a kernel setting etc). Since we cannot afford the 70,000 dollars they want to license it I am not implying I can use MSSQL, but I could look at other DB's like MYSQL, or Firebird, etc. I'm a little curious what kernel settings you are changing on Windows. I wasn't aware there was much to be done there. I'm afraid you do have to change half a dozen settings in postgresql.conf to match your workload, but PG runs on a much wider range of machines than MSSQL so it's difficult to come up with a reasonable default. Takes me about 5 minutes when I setup an installation to make sure the figures are reasonable (rather than the best they can be). I have a lot of time now (two weeks) in this conversion and do not wish to give up, I will see if I can learn what is needed to get the maximum performance. I have seen much information available and this list has been a huge resource. I really appreciate all the help. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] same question little different test MSSQL vrs Postgres
Well last evening (did not try it this morning) it was taking the extra time. I have made some adjustments to the config file per a few web sites that you all recommended my looking at. It is now using 137 of 756 meg avail. it is now taking 8 secs to return 22,000 rows (using pgadminIII in a sql edit window). The EXPLAIN ANALYSE still shows the same as below, but the table has 344,000 recs of which only 22636 are clientnum = 'SAKS' I am still doing a seq search (this applies to the view question where if it is a small result set it used a index search but on a larger return set it did a seq search) in my view, but with the adjustments to the kernel I get a result in 140 secs (MSSQL was 135 secs). This is not production, I am still very worried that I have to do all this tweeking to use this, MSSQL worked out of the box as it does (not saying its great, but I never had to adjust a kernel setting etc). Since we cannot afford the 70,000 dollars they want to license it I am not implying I can use MSSQL, but I could look at other DB's like MYSQL, or Firebird, etc. I have a lot of time now (two weeks) in this conversion and do not wish to give up, I will see if I can learn what is needed to get the maximum performance. I have seen much information available and this list has been a huge resource. I really appreciate all the help. Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 QUERY PLAN Seq Scan on tblcase (cost=0.00..30066.21 rows=37401 width=996) (actual time=0.344..962.260 rows=22636 loops=1) Filter: ((clientnum)::text = 'SAKS'::text) Total runtime: 1034.434 ms That's only 1 second - to return 22,636 rows. Not 27 seconds, as in the original post. You'll never persuade PG to use the index when some 75% of your rows match the filter - it just doesn't make sense. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] same question little different test MSSQL vrs Postgres
The postgres is running on Linux Fedora core 3 (production will be redhat on Dell 4 proc 8 gig box). My client pgadminIII is running on XP. Sorry I was not clearer on this. I am playing with the settings now, I got it to return in 100 secs (the view that is that took 135 on MSSQL). My testing is using identical Dell desktops for the MSSQL and the Linux, with a third machine for the clients. I do not mind getting up to speed on the proper setting to optimize the hardware, I am worried that as production environment can be somewhat dynamic that I will have issues getting a optimized environment and that it will work for our needs. My whole reason for being here is that our duel proc production MSSQL server is just no longer keeping up with the demand, so it is important that whatever I implement is going to up to the challenge. I am still convinced Postgres was the correct choice, especially with all the guidance I have been able to get here. 100 seconds will be fine compared to the 135 of MSSQL, I just was getting worse responses before adjusting. At the moment I think I went too far as I see it using swap and going slower, but it never used much of the 756 meg (137 max was all I ever saw it use). I guess the swap buffers and cache are the important settings (least that seems to be what is affecting the memory). Not sure exactly what would cause it to use seq vrs index, but I will try the force and see if it helps the speed. Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 26, 2005 10:21 AM To: Joel Fradkin Cc: [EMAIL PROTECTED]; pgsql-sql@postgresql.org Subject: Re: [SQL] same question little different test MSSQL vrs Postgres Joel Fradkin wrote: Well last evening (did not try it this morning) it was taking the extra time. I have made some adjustments to the config file per a few web sites that you all recommended my looking at. The crucial one I'd say is the performance guide at: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php The first half-dozen settings are the crucial ones. It is now using 137 of 756 meg avail. it is now taking 8 secs to return 22,000 rows (using pgadminIII in a sql edit window). That might be too much RAM. Don't forget PG likes to work with your operating-system (unlike many other DBs). Make sure Windows is using enough RAM to cache diskspace. I'm curious as to how this takes 8secs whereas you had 1 second earlier. Are you sure some of this isn't pgadmin's overhead to display the rows? The EXPLAIN ANALYSE still shows the same as below, but the table has 344,000 recs of which only 22636 are clientnum = 'SAKS' That sounds like it's about the borderline between using an index and not (depending on cache-size, disk speeds etc). I am still doing a seq search (this applies to the view question where if it is a small result set it used a index search but on a larger return set it did a seq search) in my view, but with the adjustments to the kernel I get a result in 140 secs (MSSQL was 135 secs). If you want to check whether the index would help, try issuing the following before running your query: SET ENABLE_SEQSCAN=FALSE; This will force PG to use any index it can regardless of whether it thinks it will help. This is not production, I am still very worried that I have to do all this tweeking to use this, MSSQL worked out of the box as it does (not saying its great, but I never had to adjust a kernel setting etc). Since we cannot afford the 70,000 dollars they want to license it I am not implying I can use MSSQL, but I could look at other DB's like MYSQL, or Firebird, etc. I'm a little curious what kernel settings you are changing on Windows. I wasn't aware there was much to be done there. I'm afraid you do have to change half a dozen settings in postgresql.conf to match your workload, but PG runs on a much wider range of machines than MSSQL so it's difficult to come up with a reasonable default. Takes me about 5 minutes when I setup an installation to make sure the figures are reasonable (rather than the best they can be). I have a lot of time now (two weeks) in this conversion and do not wish to give up, I will see if I can learn what is needed to get the maximum performance. I have seen much information available and this list has been
Re: [SQL] same question little different test MSSQL vrs Postgres
Joel Fradkin [EMAIL PROTECTED] writes: QUERY PLAN Seq Scan on tblcase (cost=0.00..30066.21 rows=37401 width=996) (actual time=0.344..962.260 rows=22636 loops=1) Filter: ((clientnum)::text = 'SAKS'::text) Total runtime: 1034.434 ms Well that says it only took 1s. So it seems this is highly dependent on whether the data is in cache. Perhaps it was in cache on MSSQL when you profiled it there and not on postgres? You could put an index on clientnum, but if the data is usually in cache like this it might not even be necessary. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] same question little different test MSSQL vrs Postgres
I tried the SET ENABLE_SEQSCAN=FALSE; And the result took 29 secs instead of 117. After playing around with the cache and buffers etc I see I am no longer doing any swapping (not sure how I got the 100 sec response might have been shared buffers set higher, been goofing around with it all morning). My worry here is it should obviously use an index scan so something is not setup correctly yet. I don't want to second guess the analyzer (or is this a normal thing?) Least it is blowing the doors off MSSQL (which is what I touted to my boss and was pretty upset when I got no result last night). The 117 was before I forced the seq off so even doing a seq I am getting results now that are better then MSSQL. Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 26, 2005 10:21 AM To: Joel Fradkin Cc: [EMAIL PROTECTED]; pgsql-sql@postgresql.org Subject: Re: [SQL] same question little different test MSSQL vrs Postgres Joel Fradkin wrote: Well last evening (did not try it this morning) it was taking the extra time. I have made some adjustments to the config file per a few web sites that you all recommended my looking at. The crucial one I'd say is the performance guide at: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php The first half-dozen settings are the crucial ones. It is now using 137 of 756 meg avail. it is now taking 8 secs to return 22,000 rows (using pgadminIII in a sql edit window). That might be too much RAM. Don't forget PG likes to work with your operating-system (unlike many other DBs). Make sure Windows is using enough RAM to cache diskspace. I'm curious as to how this takes 8secs whereas you had 1 second earlier. Are you sure some of this isn't pgadmin's overhead to display the rows? The EXPLAIN ANALYSE still shows the same as below, but the table has 344,000 recs of which only 22636 are clientnum = 'SAKS' That sounds like it's about the borderline between using an index and not (depending on cache-size, disk speeds etc). I am still doing a seq search (this applies to the view question where if it is a small result set it used a index search but on a larger return set it did a seq search) in my view, but with the adjustments to the kernel I get a result in 140 secs (MSSQL was 135 secs). If you want to check whether the index would help, try issuing the following before running your query: SET ENABLE_SEQSCAN=FALSE; This will force PG to use any index it can regardless of whether it thinks it will help. This is not production, I am still very worried that I have to do all this tweeking to use this, MSSQL worked out of the box as it does (not saying its great, but I never had to adjust a kernel setting etc). Since we cannot afford the 70,000 dollars they want to license it I am not implying I can use MSSQL, but I could look at other DB's like MYSQL, or Firebird, etc. I'm a little curious what kernel settings you are changing on Windows. I wasn't aware there was much to be done there. I'm afraid you do have to change half a dozen settings in postgresql.conf to match your workload, but PG runs on a much wider range of machines than MSSQL so it's difficult to come up with a reasonable default. Takes me about 5 minutes when I setup an installation to make sure the figures are reasonable (rather than the best they can be). I have a lot of time now (two weeks) in this conversion and do not wish to give up, I will see if I can learn what is needed to get the maximum performance. I have seen much information available and this list has been a huge resource. I really appreciate all the help. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] same question little different test MSSQL vrs Postgres
Joel Fradkin [EMAIL PROTECTED] writes: I tried the SET ENABLE_SEQSCAN=FALSE; And the result took 29 secs instead of 117. After playing around with the cache and buffers etc I see I am no longer doing any swapping (not sure how I got the 100 sec response might have been shared buffers set higher, been goofing around with it all morning). If it's swapping you're definitely going to get bad results. You really want the *majority* of RAM left free for the OS to cache disk data. My worry here is it should obviously use an index scan so something is not setup correctly yet. I don't want to second guess the analyzer (or is this a normal thing?) No that's not obvious. 22k out of 344k is a selectivity of 6.6% which is probably about borderline. The optimizer is estimating even worse at 10.9% which isn't far off but puts it well out of the range for an index scan. If you really want to get postgres using an index scan you'll have to a) improve the estimate using alter table tblcase alter column clientnum set statistics to raise the statistics target for that column and reanalyze. And b) lower random_page_cost. random_page_cost tells postgres how much slower indexes are than table scans and at the default setting it accurately represents most disk hardware. If your database fits within RAM and is often cached then you might have to lower it to model that fact. But you shouldn't do it based on a single query like this. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] same question little different test MSSQL vrs Postgres
Joel Fradkin wrote: The postgres is running on Linux Fedora core 3 (production will be redhat on Dell 4 proc 8 gig box). My client pgadminIII is running on XP. Sorry I was not clearer on this. Ah! you're the gent who had the problems with SE-Linux on Fedora 3. Sorry - should have made the connection, but there's so much traffic on the lists it's easy to miss. I am playing with the settings now, I got it to return in 100 secs (the view that is that took 135 on MSSQL). My testing is using identical Dell desktops for the MSSQL and the Linux, with a third machine for the clients. I do not mind getting up to speed on the proper setting to optimize the hardware, I am worried that as production environment can be somewhat dynamic that I will have issues getting a optimized environment and that it will work for our needs. My whole reason for being here is that our duel proc production MSSQL server is just no longer keeping up with the demand, so it is important that whatever I implement is going to up to the challenge. You might want to look at the overall design of the database at some point too. Also, don't forget the compromises you made when designing for MSSQL might not be useful (or even harmful) with PG. I am still convinced Postgres was the correct choice, especially with all the guidance I have been able to get here. 100 seconds will be fine compared to the 135 of MSSQL, I just was getting worse responses before adjusting. At the moment I think I went too far as I see it using swap and going slower, but it never used much of the 756 meg (137 max was all I ever saw it use). If you're on Linux then 135MB sounds like too much (for one client, far too much). I guess the swap buffers and cache are the important settings (least that seems to be what is affecting the memory). Not sure exactly what would cause it to use seq vrs index, but I will try the force and see if it helps the speed. Try starting with your shared-buffers at say 4000-8000 (32MB to 64MB), sort-mem/work-mem at 8000-32000 (8MB-32MB), random-page-cost somewhere between 2 and 4. Then, judge how much RAM your box is using to cache disk-space (free -m) and set effective-cache-size accordingly. That's it - you may want to play around with the figures slightly, but pick the lowest numbers above and restart PG and it'll run OK. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] same question little different test MSSQL vrs Postgres
Thank you I will look at that info. I did do an EXPLAIN ANALYSE on the view and could see it was doing the seq scan on 3 fields, so I did an index for the three fields and it then chose an index scan and ran in 27 seconds. I also did adjust my defaults to much smaller numbers on shared buffers (per the tidbits page recommendation like 8 meg for my memory size). I looked at http://www.desknow.com/kb/idx/0/061/article/ which recommended doing a vacuum verbose to determine the exact max_fsm_pages and I set the cache to use 25% of my available memory per the recommendation on tid bits. Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 26, 2005 11:50 AM To: Joel Fradkin Cc: 'Richard Huxton'; [EMAIL PROTECTED]; pgsql-sql@postgresql.org; [EMAIL PROTECTED]; Steve Goldsmith Subject: Re: [SQL] same question little different test MSSQL vrs Postgres Joel Fradkin [EMAIL PROTECTED] writes: I tried the SET ENABLE_SEQSCAN=FALSE; And the result took 29 secs instead of 117. After playing around with the cache and buffers etc I see I am no longer doing any swapping (not sure how I got the 100 sec response might have been shared buffers set higher, been goofing around with it all morning). If it's swapping you're definitely going to get bad results. You really want the *majority* of RAM left free for the OS to cache disk data. My worry here is it should obviously use an index scan so something is not setup correctly yet. I don't want to second guess the analyzer (or is this a normal thing?) No that's not obvious. 22k out of 344k is a selectivity of 6.6% which is probably about borderline. The optimizer is estimating even worse at 10.9% which isn't far off but puts it well out of the range for an index scan. If you really want to get postgres using an index scan you'll have to a) improve the estimate using alter table tblcase alter column clientnum set statistics to raise the statistics target for that column and reanalyze. And b) lower random_page_cost. random_page_cost tells postgres how much slower indexes are than table scans and at the default setting it accurately represents most disk hardware. If your database fits within RAM and is often cached then you might have to lower it to model that fact. But you shouldn't do it based on a single query like this. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] same question little different test MSSQL vrs Postgres
Joel Fradkin wrote: Thank you I will look at that info. I did do an EXPLAIN ANALYSE on the view and could see it was doing the seq scan on 3 fields, so I did an index for the three fields and it then chose an index scan and ran in 27 seconds. I also did adjust my defaults to much smaller numbers on shared buffers (per the tidbits page recommendation like 8 meg for my memory size). I looked at http://www.desknow.com/kb/idx/0/061/article/ which recommended doing a vacuum verbose to determine the exact max_fsm_pages and I set the cache to use 25% of my available memory per the recommendation on tid bits. Note that the effective_cache_size (if I've spelt it right) just tells PG what your cache size is. You should set it based on what free tells you about your system's use of memory. -- Richard Huxton Archonet Ltd ---(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: [SQL] same question little different test MSSQL vrs Postgres
On Tue, Jan 25, 2005 at 21:21:08 -0700, Dennis Sacks [EMAIL PROTECTED] wrote: One of the things you'll want to do regularly is run a vacuum analyze. You can read up on this in the postgresql docs. This is essential to the indexes being used properly. At a bare minimum, after you import a large amount of data, you'll want to run vacuum analyze. Note that there is no need to vacuum after inserts (only updates and deletes), so you can just do an analyze in that case. ---(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: [SQL] same question little different test MSSQL vrs Postgres
Bruno Wolff III wrote: On Tue, Jan 25, 2005 at 21:21:08 -0700, Dennis Sacks [EMAIL PROTECTED] wrote: One of the things you'll want to do regularly is run a vacuum analyze. You can read up on this in the postgresql docs. This is essential to the indexes being used properly. At a bare minimum, after you import a large amount of data, you'll want to run vacuum analyze. Note that there is no need to vacuum after inserts (only updates and deletes), so you can just do an analyze in that case. Good point! Analyze after bulk inserts, vacuum analyze after updates/deletes and inserts. :) Dennis Sacks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] same question little different test MSSQL vrs Postgres
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 We've done some pretty extensive benchmarking and load testing on a couple of platforms including the Xeon and Opteron. You may have already bought that Dell box, but I'll say it anyway. Xeon quad processors are a terrible platform for postgres. Trying to use more than 4GB of memory on a 32 bit machine is a waste of money. If you want performance, get a quad Opteron with the same amount of memory. I guarantee you'll see at least an order of magnitude performance improvement and substantially more under highly concurrent loads. If you decide to go this way, HP sells a very nice box. I also strongly recommend you investigate SuSE instead of RedHat. Fedora core is good technology, but SuSE offers equally good technology with better support. Also make sure that your SCSI HBA is actually using the 64 bit PCI bus. There are cards out there which plug into 64 bit PCI but only actually address 32 bits (Qlogic's QLA2340 / 2342 for example). You make no mention of the disk subsystem you plan to use. This is most critical part of your system. Database performance is almost always bound by IO. Usually disk IO. Briefly, put PGDATA on the widest RAID 10 array of disks you can manage. It's not worth spending the extra money to get 15kRPM disks for this. The size of the disks involved is pretty much irrelevant, only the number of them matters. Put the WAL files on a dedicated RAID 1 pair of 15kRPM disks. Put the postgres log files (or syslog) on a seperate filesystem. - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A Joel Fradkin wrote: | The postgres is running on Linux Fedora core 3 (production will be redhat on | Dell 4 proc 8 gig box). -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFB+BaPgfzn5SevSpoRAgirAKDBbedScL3leQVidZjmsGmxoph8wQCgvhoW 2ZznEkxOMA3btZEBdzHd8TU= =eg7h -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] same question little different test MSSQL vrs Postgres
I also tried a simple select * from tblcase where clientum = SAKS On both MSSQL and Postgres. MSSQL was 3 secs, Postgres was 27 secs. There is a key for clientnum, but it appeared on both systems (identical Dell Desktops Postgres is running Linux MSSQL is XP) it did not do a indexed search. Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology advanced tools. 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA,Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments.
Re: [SQL] same question little different test MSSQL vrs Postgres
Joel Fradkin wrote: I also tried a simple select * from tblcase where clientum = SAKS On both MSSQL and Postgres. MSSQL was 3 secs, Postgres was 27 secs. There is a key for clientnum, but it appeared on both systems (identical Dell Desktops Postgres is running Linux MSSQL is XP) it did not do a indexed search. One of the things you'll want to do regularly is run a "vacuum analyze". You can read up on this in the postgresql docs. This is essential to the indexes being used properly. At a bare minimum, after you import a large amount of data, you'll want to run vacuum analyze. Dennis Sacks [EMAIL PROTECTED]
Re: [SQL] same question little different test MSSQL vrs Postgres
Joel Fradkin [EMAIL PROTECTED] writes: I also tried a simple select * from tblcase where clientum = 'SAKS' Try: explain analyze select * from tblcase where clientum = 'SAKS' Send the output. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
