Re: same question little different test MSSQL vrs Postgres - Mailing list pgsql-sql
From | Joel Fradkin |
---|---|
Subject | Re: same question little different test MSSQL vrs Postgres |
Date | |
Msg-id | 000001c503c0$792d8e40$797ba8c0@jfradkin Whole thread Raw |
In response to | Re: same question little different test MSSQL vrs Postgres (Richard Huxton <dev@archonet.com>) |
Responses |
Re: same question little different test MSSQL vrs Postgres
Re: same question little different test MSSQL vrs Postgres |
List | pgsql-sql |
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 jfradkin@wazagua.com www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, IncThis email message is for the use of the intended recipient(s) andmay 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:dev@archonet.com] Sent: Wednesday, January 26, 2005 10:21 AM To: Joel Fradkin Cc: gsstark@mit.edu; 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