Thread: PostgreSQL vs. MySQL
Hi, has anybody tested PostgreSQL 7.3.x tables agains MySQL 4.0.12/13 with InnoDB? Regards, Rafal
PostgreSQL (as being a really advanced RDBMS), generally requires some tuning in order to get the best performance. Your best bet is to try both. Also check to see IF mysql has -Referential integrity -subselects -transactions -(other usefull features like arrays,user defined types,etc..) (its probable that you will need some of the above) On Fri, 4 Jul 2003, Rafal Kedziorski wrote: > Hi, > > has anybody tested PostgreSQL 7.3.x tables agains MySQL 4.0.12/13 with InnoDB? > > > Regards, > Rafal > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-210-8981112 fax: +30-210-8981877 email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr
On Friday 04 Jul 2003 11:03 am, Rafal Kedziorski wrote: > Hi, > > has anybody tested PostgreSQL 7.3.x tables agains MySQL 4.0.12/13 with > InnoDB? Lots of people probably. The big problem is that unless the tester's setup matches your intended usage the results are of little worth. For the tests to be meaningful, you need the same: - hardware - OS - query complexity - usage patterns - tuning options I'd suggest running your own tests with real data where possible. Just to make the situation more interesting, the best way to solve a problem in PG isn't necessarily the same in MySQL. From my experience and general discussion on the lists, I'd say MySQL can win for: - simple selects - some aggregates (e.g. count(*)) PG wins for: - complex queries - large numbers of clients - stored procedures/functions - SQL compliance -- Richard Huxton
I recently took a system from MySQL to Postgres. Same HW, SW, same data.
The major operations where moderately complex queries (joins on 8 tables).
The results we got was that Postgres was fully 3 times slower than MySql.
We were on this list a fair bit looking for answers and tried all the
standard answers. It was still much much much slower.
Brian Tarbox
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Rafal
Kedziorski
Sent: Friday, July 04, 2003 6:03 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] PostgreSQL vs. MySQL
Hi,
has anybody tested PostgreSQL 7.3.x tables agains MySQL 4.0.12/13 with
InnoDB?
Regards,
Rafal
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
> I recently took a system from MySQL to Postgres. Same HW, SW, same data. > The major operations where moderately complex queries (joins on 8 tables). > > The results we got was that Postgres was fully 3 times slower than MySql. > We were on this list a fair bit looking for answers and tried all the > standard answers. It was still much much much slower. I'm curious what the usage was. How many concurrent processes were performing the complex queries? I've heard that Postgres does better when the number of concurrent users is high and MySQL does better when the number is low. I have no idea if that is true or not. Michael
I'm actually leaving this list but I can answer this question. Our results were with a single user and we were running Inodb. We were running on RedHat 8.0 / 9.0 with vanilla linux settings. Brian -----Original Message----- From: Michael Mattox [mailto:michael.mattox@verideon.com] Sent: Friday, July 04, 2003 8:36 AM To: Brian Tarbox; Rafal Kedziorski; pgsql-performance@postgresql.org Subject: RE: [PERFORM] PostgreSQL vs. MySQL > I recently took a system from MySQL to Postgres. Same HW, SW, same data. > The major operations where moderately complex queries (joins on 8 tables). > > The results we got was that Postgres was fully 3 times slower than MySql. > We were on this list a fair bit looking for answers and tried all the > standard answers. It was still much much much slower. I'm curious what the usage was. How many concurrent processes were performing the complex queries? I've heard that Postgres does better when the number of concurrent users is high and MySQL does better when the number is low. I have no idea if that is true or not. Michael
> I'm actually leaving this list but I can answer this question. > Our results > were with a single user and we were running Inodb. We were running on > RedHat 8.0 / 9.0 with vanilla linux settings. That's funny, you make a statement that Postgres was 3 times slower than MySQL and then you promptly leave the list! Just kidding. It'd be interesting to see what happens if you test your system with a hundred users. If it's a webapp you can use JMeter to do this really easily. Michael
On Friday 04 July 2003 18:16, Michael Mattox wrote: > > I'm actually leaving this list but I can answer this question. > > Our results > > were with a single user and we were running Inodb. We were running on > > RedHat 8.0 / 9.0 with vanilla linux settings. > > That's funny, you make a statement that Postgres was 3 times slower than > MySQL and then you promptly leave the list! Just kidding. > > It'd be interesting to see what happens if you test your system with a > hundred users. If it's a webapp you can use JMeter to do this really > easily. Hundred users is a later scenario. I am curious about "vanilla linux settings" What does that mean. Postgresql communmity would always like to help who need it but this thread so far gives me impression that OP isn't willing to provide sufficient information.. Shridhar
On Friday 04 July 2003 17:57, Brian Tarbox wrote: > I recently took a system from MySQL to Postgres. Same HW, SW, same data. > The major operations where moderately complex queries (joins on 8 tables). > > The results we got was that Postgres was fully 3 times slower than MySql. > We were on this list a fair bit looking for answers and tried all the > standard answers. It was still much much much slower. This invites the slew of questions thereof. Can you provide more information on 1. Hardware 2. Postgresql version 3. Postgresql tuning you did 4. data size 5. nature of queries 6. mysql benchmarks to rate against. Unless you provide these, it's difficult to help.. Shridhar
> Unless you provide these, it's difficult to help.. http://archives.postgresql.org/pgsql-performance/2003-05/msg00299.php Note the thread with Tom and Brian.
Attachment
On 4 Jul 2003 at 9:11, Rod Taylor wrote: > > Unless you provide these, it's difficult to help.. > > http://archives.postgresql.org/pgsql-performance/2003-05/msg00299.php Well, even in that thread there wasn't enough information I asked for in other mail. It was bit too vague to be a comfortable DB tuning problem. Am I reading the thread wrong? Please correct me. Bye Shridhar -- Ahead warp factor one, Mr. Sulu.
On Fri, 2003-07-04 at 09:20, Shridhar Daithankar wrote: > On 4 Jul 2003 at 9:11, Rod Taylor wrote: > > > > Unless you provide these, it's difficult to help.. > > > > http://archives.postgresql.org/pgsql-performance/2003-05/msg00299.php > > Well, even in that thread there wasn't enough information I asked for in other > mail. It was bit too vague to be a comfortable DB tuning problem. Completely too little information, and it stopped with Tom asking for additional information. I don't think Brian has any interest in being helped. Many here would be more than happy to do so if the information were to flow.
Attachment
> I recently took a system from MySQL to Postgres. Same HW, SW, same data. > The major operations where moderately complex queries (joins on 8 tables). > > The results we got was that Postgres was fully 3 times slower than MySql. > We were on this list a fair bit looking for answers and tried all the > standard answers. It was still much much much slower. I have never found a query in MySQL that was faster than one in PostgreSQL. Chris
"Brian Tarbox" <btarbox@theworld.com> writes:
> I recently took a system from MySQL to Postgres. Same HW, SW, same data.
> The major operations where moderately complex queries (joins on 8 tables).
> The results we got was that Postgres was fully 3 times slower than MySql.
> We were on this list a fair bit looking for answers and tried all the
> standard answers. It was still much much much slower.
Could we see the details? It's not very fair to not give us a chance to
learn about problems.
regards, tom lane
Ok, I'll give more data :-) Under both MySql and Postgres the tests were run on a variety of systems, all with similar results. My own personal testing was done on a P4 2.4Mhz, 512 mb memory, latest production versions of each database. By vanilla RedHat I mean that I installed RH on a clean system, said install everything and did no customization of RH settings. We had about 40 tables in the db, with joined queries on about 8-12 tables. Some tables had 10,000 records, some 1000 records, other tables had dozens of records. There were indexes on all join fields, and all join fields were listed as foriegn keys. All join fields were unique primary keys in their home table (so the index distribution would be very spread out). I'm not permitted to post the actual tables as per company policy. I did no tuning of MySql. The only tuning for PG was to vacuum and vacuum analyze. I'll also mention that comments like this one are not productive: >I don't think Brian has any interest in being helped. Please understand the limits of how much information a consultant can submit to an open list like this about a client's confidential information. I've answered every question I _can_ answer and when I get hostility in response all I can do is sigh and move on. I'm sorry if Shridhar is upset that I can't validate his favorite db but ad hominin comments aren't helpful. Brian -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Shridhar Daithankar Sent: Friday, July 04, 2003 8:54 AM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] PostgreSQL vs. MySQL On Friday 04 July 2003 17:57, Brian Tarbox wrote: > I recently took a system from MySQL to Postgres. Same HW, SW, same data. > The major operations where moderately complex queries (joins on 8 tables). > > The results we got was that Postgres was fully 3 times slower than MySql. > We were on this list a fair bit looking for answers and tried all the > standard answers. It was still much much much slower. This invites the slew of questions thereof. Can you provide more information on 1. Hardware 2. Postgresql version 3. Postgresql tuning you did 4. data size 5. nature of queries 6. mysql benchmarks to rate against. Unless you provide these, it's difficult to help.. Shridhar ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On 4 Jul 2003 at 10:07, Brian Tarbox wrote: > Ok, I'll give more data :-) > > Under both MySql and Postgres the tests were run on a variety of systems, > all with similar results. My own personal testing was done on a P4 2.4Mhz, > 512 mb memory, latest production versions of each database. By vanilla > RedHat I mean that I installed RH on a clean system, said install everything > and did no customization of RH settings. > We had about 40 tables in the db, with joined queries on about 8-12 tables. > Some tables had 10,000 records, some 1000 records, other tables had dozens > of records. There were indexes on all join fields, and all join fields were > listed as foriegn keys. All join fields were unique primary keys in their > home table (so the index distribution would be very spread out). I'm not > permitted to post the actual tables as per company policy. > > I did no tuning of MySql. The only tuning for PG was to vacuum and vacuum > analyze. No wonder pg bombed out so badly. In fact I am surprised it was slower only by factor of 3. Rule of thumb is if you have more than 1K records in any table, you got to tune postgresql.conf. I don't think I need to elaborate what difference tuning in postgresql.conf can make. > > I'll also mention that comments like this one are not productive: > > >I don't think Brian has any interest in being helped. > > Please understand the limits of how much information a consultant can submit > to an open list like this about a client's confidential information. I've > answered every question I _can_ answer and when I get hostility in response > all I can do is sigh and move on. Well, definition of threshold of hostile response differ from person to person. That is understood but by internet standards, I don't think you have received any hostile response. But that's not the topic I would like to continue to discuss. What I would suggest you is to look at some other performance problem description submitted earlier. I don't think these guys have permission to disclose sensitive data either but they did everything they could in their limits. Look at, http://archives.postgresql.org/pgsql-performance/2003-06/msg00134.php and the thread thereof. You can reach there from http://archives.postgresql.org/pgsql-performance/2003-06/threads.php There is a reason why Michael got so many and so detailed responses. Within your limits, I am sure you could have posted more and earlier rather than posting details when original thread is long gone. > I'm sorry if Shridhar is upset that I can't validate his favorite db but ad > hominin comments aren't helpful. I have no problems personally if postgresql does not work with you. The very first reason I stick with postgresql is that it works best for me. The moment it does not work for somebody else, there is a potential problem which I would like to rectify ASAP. That is the idea of getting on lists and forums. It's not about product as much it is about helping each other. And certainly. I have posted weirder qeuries here and I disagree that you couldn't post more. However this is a judgement from what you have posted and by all chances it is wrong. Never mind that. At the end, it's the problem and solution that matters. Peace.. Bye Shridhar -- Murphy's Laws: (1) If anything can go wrong, it will. (2) Nothing is as easy as it looks. (3) Everything takes longer than you think it will.
Rod Taylor <rbt@rbt.ca> writes:
>> It was bit too vague to be a comfortable DB tuning problem.
> Completely too little information, and it stopped with Tom asking for
> additional information.
There was something awfully fishy about that. Brian was saying that he
got a seqscan plan out of "WHERE foo = 100", where foo is an integer
primary key. That's just not real credible, at least not once you get
past the couple of standard issues that were mentioned in the thread.
And we never did get word one of information about his join problems.
> I don't think Brian has any interest in being helped.
I suspect he'd made up his mind already. Which is his privilege, but
it'd be nice to have some clue what the problem was ...
regards, tom lane
On Fri, Jul 04, 2003 at 10:07:46AM -0400, Brian Tarbox wrote:
> 512 mb memory, latest production versions of each database. By vanilla
> RedHat I mean that I installed RH on a clean system, said install everything
> and did no customization of RH settings.
Does that include no customization of the Postgres settings?
> We had about 40 tables in the db, with joined queries on about 8-12 tables.
SELECTs only? because. . .
> of records. There were indexes on all join fields, and all join fields were
> listed as foriegn keys. All join fields were unique primary keys in their
. . .you know that FK constraints in Postgres are not cheap, right?
> I did no tuning of MySql. The only tuning for PG was to vacuum and vacuum
> analyze.
This appears to be a "yes" answer to my question above. Out of the
box, PostgreSQL is set up to be able to run on a 1992-vintage SGI
Indy with 8 M of RAM (ok, I may be exaggerating, but only by a bit);
it is not tuned for performance. Running without even tweaking the
shared buffers is guaranteed to get you lousy performance.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
> Please understand the limits of how much information a consultant can submit > to an open list like this about a client's confidential information. I've > answered every question I _can_ answer and when I get hostility in response > all I can do is sigh and move on. Is there any chance you could show us an EXPLAIN ANALYZE output of the poor performing query in question? > I'm sorry if Shridhar is upset that I can't validate his favorite db but ad > hominin comments aren't helpful. It was me who gave the comment based upon previous threads which requested information that had gone unanswered (not even a response stating such information could not be provided). The database you describe is quite small, so I'm not surprised MySQL does well with it. That said, it isn't normal to experience poor performance with PostgreSQL unless you've stumbled upon a poor spot (IN based sub-queries used to be poor performing, aggregates can be slow, mismatched datatypes, etc.). Output of EXPLAIN ANALYZE of a contrived query representative of the type of work done (that demonstrates the problem) with renamed tables and columns would go a long way to helping us help you.
Attachment
> This appears to be a "yes" answer to my question above. Out of the > box, PostgreSQL is set up to be able to run on a 1992-vintage SGI > Indy with 8 M of RAM (ok, I may be exaggerating, but only by a bit); > it is not tuned for performance. Running without even tweaking the > shared buffers is guaranteed to get you lousy performance. I see this as a major problem. How many people run postgres, decide it's too slow and give up without digging into the documentation or coming to this group? This seems to be pretty common. Even worst, they tell 10 others how slow Postgres is and then it gets a bad reputation. In my opinion the defaults should be set up for a typical database server machine. Michael
"Brian Tarbox" <btarbox@theworld.com> writes:
> I'm not permitted to post the actual tables as per company policy.
Nobody wants to see your data, only the table schemas and queries. If
you feel that even that contains some sensitive information, just rename
the table and field names to something meaningless. But the kinds of
problems I am interested in finding out about require seeing the column
datatypes and the form of the queries. The hardware and platform
details you gave mean nothing to me (and probably not to anyone else
either, given that you were comparing to MySQL on the same platform).
> I did no tuning of MySql. The only tuning for PG was to vacuum and vacuum
> analyze.
If you didn't at least bump up shared_buffers, you were deliberately
skewing the results against Postgres. Surely you can't have been
subscribed to pgsql-performance very long without knowing that the
default postgresql.conf settings are set up for a toy installation.
> all I can do is sigh and move on.
You're still looking for reasons not to answer our questions, aren't
you? Do you actually want to find out what the problem was here?
If not, you're wasting our list bandwidth. I'd like to find out,
if only so I can try to fix it in future releases, but without useful
information I'll just have to write this off as an unsubstantiated report.
regards, tom lane
On 4 Jul 2003 at 16:35, Michael Mattox wrote: > I see this as a major problem. How many people run postgres, decide it's > too slow and give up without digging into the documentation or coming to > this group? This seems to be pretty common. Even worst, they tell 10 > others how slow Postgres is and then it gets a bad reputation. > > In my opinion the defaults should be set up for a typical database server > machine. Well, there are few major reasons defaults are the way they are and the reason it hurts the way they are 1. Postgresql expects to start on every machine on which it can run. Now some of the arcane platforms need kernel recompilation to raise SHMMAX and defaults to 1MB. 2. Postgresql uses shared memory being process based architecture. Mysql uses process memory being threaded application. It does not need kernel settings to work and usually works best it can. 3. We expect users/admins to be reading docs. If one does not read docs, it does not matter what defaults are. Sooner or later, it is going to fall on it's face. 4. Unlike likes of Oracle, postgresql does not pre-claim resources and starts hogging the system, replacing OS whereever possible. No it does not work that way.. One thing always strikes me. Lot of people(Not you Michael!..:-)) would complain that postgresql is slow and needs tweaking are not bothered by the fact that oracle needs almost same kind of and same amount of tweaking to get somewhere. Perception matterrs a lot. I would have whined for java as well but this is not the forum for that..:-) On a positive note, me and Josh are finishing a bare bone performance article that would answer lot of your questions. I am counting on you to provide valuable feedback. I expect it out tomorrow or on sunday..Josh will confirm that.. Bye Shridhar -- Theorem: a cat has nine tails.Proof: No cat has eight tails. A cat has one tail more than no cat. Therefore, a cat has nine tails.
hi, At 20:19 04.07.2003 +0530, Shridhar Daithankar wrote: [...] >On a positive note, me and Josh are finishing a bare bone performance article where will be this article published? >that would answer lot of your questions. I am counting on you to provide >valuable feedback. I expect it out tomorrow or on sunday..Josh will confirm >that.. Rafal
> 2. Postgresql uses shared memory being process based architecture. Mysql uses > process memory being threaded application. It does not need kernel settings to > work and usually works best it can. MySQL has other issues with the kernel due to their threading choice such as memory limits per process, or poor threaded SMP support on some platforms (inability for a single process to use more than one CPU at a time regardless of thread count). Threads aren't an easy way around kernel limitations, which is probably why Apache has gone for a combination of the two -- but of course that adds complexity.
Attachment
On Friday 04 July 2003 20:36, Rod Taylor wrote: > > 2. Postgresql uses shared memory being process based architecture. Mysql > > uses process memory being threaded application. It does not need kernel > > settings to work and usually works best it can. > > MySQL has other issues with the kernel due to their threading choice > such as memory limits per process, or poor threaded SMP support on some > platforms (inability for a single process to use more than one CPU at a > time regardless of thread count). > > Threads aren't an easy way around kernel limitations, which is probably > why Apache has gone for a combination of the two -- but of course that > adds complexity. Correct. It's not debate about whether threading is better or not. But it certainly affects the default way with which these two applications work. Shridhar
On Fri, Jul 04, 2003 at 04:35:03PM +0200, Michael Mattox wrote:
> I see this as a major problem. How many people run postgres, decide it's
> too slow and give up without digging into the documentation or coming to
> this group? This seems to be pretty common. Even worst, they tell 10
> others how slow Postgres is and then it gets a bad reputation.
There have been various proposals to do things of this sort. But
there are always problems with it. For instance, on many OSes,
Postgres would not run _at all_ when you first compiled it if its
defaults were set more agressively. Then how many people would
complain, "It just doesn't work," and move on without asking about
it?
I cannot, for the life of me, understand how anyone can install some
software which is supposed to provide meaningful results under
production conditions, and not bother to read even the basic
"quickstart"-type stuff that is kicking around. There is _no secret_
that Postgres is configured as a toy out of the box. One presumes
that DBAs are hired to do _some_ little bit of work.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
On Friday 04 July 2003 20:56, Andrew Sullivan wrote: > On Fri, Jul 04, 2003 at 04:35:03PM +0200, Michael Mattox wrote: > > I see this as a major problem. How many people run postgres, decide it's > > too slow and give up without digging into the documentation or coming to > > this group? This seems to be pretty common. Even worst, they tell 10 > > others how slow Postgres is and then it gets a bad reputation. > > There have been various proposals to do things of this sort. But > there are always problems with it. For instance, on many OSes, > Postgres would not run _at all_ when you first compiled it if its > defaults were set more agressively. Then how many people would > complain, "It just doesn't work," and move on without asking about > it? There was a proposal to ship various postgresql.conf.sample like one for large servers, one for medium, one for update intensive purpose etc. I was thinking over it. Actaully we could tweak initdb script to be interactiev and get inputs from users and tune it accordingly. Of course it would be nowhere near the admin reading the docs. but at least it won't fall flat on performance groundas the way falls now. Shridhar
That would be something that I'd like to see. Being new to PostgreSQL some of the basics of tuning the database were a little hard to find. The reason people go with MySQL is because it's fast and easy to use. That's why I had been using it for years. Then when a problem came along and I couldn't use MySQL I checked out PostgreSQL and found that it would fill the gap, but I had been able to get by on doing very little in terms of administration for MySQL (which performed well for me) and I was expecting PostgreSQL to be similar. As with many people I have the hat of DB admin, server admin, programmer and designer and the less I have to do in any of those areas makes my life a lot easier. When I first started using PostgreSQL I installed it and entered my data without any thought of having to tune it because I never had to before. If there were some program that could be inserted to the end of the make process or something it might help dimwits like me :-) realize that there was more that needs to be done once the installation has been completed. Kevin ----- Original Message ----- From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> To: <pgsql-performance@postgresql.org> Sent: Friday, July 04, 2003 10:28 AM Subject: Re: [PERFORM] PostgreSQL vs. MySQL > On Friday 04 July 2003 20:56, Andrew Sullivan wrote: > > On Fri, Jul 04, 2003 at 04:35:03PM +0200, Michael Mattox wrote: > > > I see this as a major problem. How many people run postgres, decide it's > > > too slow and give up without digging into the documentation or coming to > > > this group? This seems to be pretty common. Even worst, they tell 10 > > > others how slow Postgres is and then it gets a bad reputation. > > > > There have been various proposals to do things of this sort. But > > there are always problems with it. For instance, on many OSes, > > Postgres would not run _at all_ when you first compiled it if its > > defaults were set more agressively. Then how many people would > > complain, "It just doesn't work," and move on without asking about > > it? > > There was a proposal to ship various postgresql.conf.sample like one for large > servers, one for medium, one for update intensive purpose etc. > > I was thinking over it. Actaully we could tweak initdb script to be > interactiev and get inputs from users and tune it accordingly. Of course it > would be nowhere near the admin reading the docs. but at least it won't fall > flat on performance groundas the way falls now. > > Shridhar > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
> I don't think Brian has any interest in being helped. >I suspect he'd made up his mind already. With all due respect Tom, I don't think I'm the one demonstrating a closed mind. Rather than trying to figure out whats going on in my head, how about figuring out whats going on in my database? :-) I'm answering every question I can. I supplied HW info because someone asked, and then Tom said: "The hardware and platform details you gave mean nothing to me...". Which would you like guys?? I am not allowed to share schemas...sorry but thats what the contract says. The queries represent code, thus intellectual property, thus I can't post them. I posted an Explain output at some point and was told my database was too small to be fast. So, I added 10,000 records, vacummed, and my selects were still the same speed. How many people on this list have asked for a tuning/performance doc? I hear that there is one coming soon. Thats great. Saying RTM is fine too, if the manual is clear. Look at Michael Mattox's thread on this very topic on 6/24. Michael said: "I think the biggest area of confusion for me was that the various parameters are very briefly described and no context is given for their parameters.? Shridhar then suggested he change OSes, upgrade his kernel (with specific patches), get different HW, etc. That goes a bit beyond casual tuning. I'm not saying (and never did say) that postgres could not be fast. All I ever said was that with the same minimal effort applied to both DBs, postgres was slower. I really wasn't looking for battle this fine day....I'm going outside to BBQ! (and if you conclude from that that I'm not interested in this or that, there's nothing I can do about that. It is a beautiful day out and bbq does sound more fun than this list. sorry) Brian
> I'm not saying (and never did say) that postgres could not be fast. > All I ever said was that with the same minimal effort applied to both > DBs, postgres was slower. Afaik, your original posting said postgresql was 3 times slower than mysql and that you are going to leave this list now. This implied that you have made your decision between postgresql and mysql, taking mysql because it is faster. Now you say your testing setup has minimal effort applied. Well, it is not very surprising that mysql is faster in standard configurations. As Shridhar pointed out, postgresql has very conservative default values, so that it starts on nearly every machine. If I was your client and gave you the task to choose a suitable database for my application and you evaluated suitable databases this way, then something is seriously wrong with your work. Regards, Bjoern
My goodness people!! If you are just going to bash people who are trying to learn PostgreSQL then you have no chance of ever getting new people using it! Cut out this crap and do what this list is meant to do, which is, I'm assuming, helping people figure out why their installations aren't running as fast as they would like. This is pathetic!! Kevin ----- Original Message ----- From: "Bjoern Metzdorf" <bm@turtle-entertainment.de> To: "Postgresql Performance" <pgsql-performance@postgresql.org> Sent: Friday, July 04, 2003 11:22 AM Subject: Re: [PERFORM] PostgreSQL vs. MySQL > > I'm not saying (and never did say) that postgres could not be fast. > > All I ever said was that with the same minimal effort applied to both > > DBs, postgres was slower. > > Afaik, your original posting said postgresql was 3 times slower than mysql > and that you are going to leave this list now. This implied that you have > made your decision between postgresql and mysql, taking mysql because it is > faster. > > Now you say your testing setup has minimal effort applied. Well, it is not > very surprising that mysql is faster in standard configurations. As Shridhar > pointed out, postgresql has very conservative default values, so that it > starts on nearly every machine. > > If I was your client and gave you the task to choose a suitable database for > my application and you evaluated suitable databases this way, then something > is seriously wrong with your work. > > Regards, > Bjoern > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
On Fri, 4 Jul 2003, Brian Tarbox wrote: > > I don't think Brian has any interest in being helped. > >I suspect he'd made up his mind already. > > > With all due respect Tom, I don't think I'm the one demonstrating a closed > mind. > Rather than trying to figure out whats going on in my head, how about > figuring out whats going on in my database? :-) Well, in the case of getting a sequential scan on something like select * from foo where col=10; where col is a primary key, the things I can think of to check are does select * from foo where col='10'; give a different plan? In general for cases where you can't post queries or schema we're kinda stuck and not really able to give intelligent advice since it's often schema/query specific, so the general questions/comments are things like (which you've probably heard, but I think they should get put into this thread if only to move the thread towards usefulness) What is the relative costs/plan if you set enable_seqscan to false before explain analyzing the query? If there are places you think that it should be able to do an index scan and it still doesn't, make sure that there aren't cross datatype issues (especially with int constants). Also, using explain analyze, where is the time being taken, it's often not where the cost factor would expect it. Do the row estimates match reality in the explain analyze output, if not does analyzing help, if not does raising the statistics target (to say 50, 100, 1000) with alter table and then analyzing help? Does vacuuming help, what about vacuum full? If the latter does and the former doesn't, you may need to look at raising the fsm settings. If shared_buffers is less than 1000, does setting it to something between 1000-8000 raise performance? How much memory does the machine have that's being used for caching, if it's alot, try raising effective_cache_size to see if that helps the choice of plan by making a more reasonable guess as to cache hit rates. Are there any sorts in the query, if so, how large would expect the result set that's being sorted to be, can you afford to make sort_mem cover that (either permanently by changing conf files or before the query with a set command)? Is it possible to avoid some sorts in the plan with a multi-column index? For 7.3 and earlier, does the query use IN or =ANY, if so it might help to try to convert to an exists form. Does the query use any mix/max aggregates, it might help to look for a workaround, this is one case that is truly slow. PostgreSQL really does require more than minimal optimization at start, effective_cache_size, shared_buffers, sort_mem and the fsm settings really need to be set at a level for the machine/queries you have. Without the queries we can't be too specific. Big speed losses I can think of are the datatype mismatch confusion, followed quickly by row estimates that don't match reality (generally requiring a greater statistics target on the column) and issues with correlation (I'm not really sure there's a good solution for this currently, maybe someone will know -- I've not run into it really on anything I've looked at).
On Fri, Jul 04, 2003 at 12:10:46PM -0400, Brian Tarbox wrote:
> I am not allowed to share schemas...sorry but thats what the contract says.
> The queries represent code, thus intellectual property, thus I can't post
> them.
If you ask for help, but say, "I can't tell you anything," no-one
will be able to help you.
I think what people are reacting to angrily is that you complain that
PostgreSQL is slow, it appears you haven't tuned it correctly, and
you're not willing to share with anyone what you did. In that case,
you shouldn't be reporting, "MySQL was faster that PostgreSQL for
me." You should at most be reporting, "MySQL was faster than
PostgreSQL for me, but I haven't any idea how to tune PostgreSQL, and
didn't know how to learn to do so." That, at least, gives people a
fighting chance to evaluate the utility of your report.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
Brian, Howdy! I'm Josh Berkus, I'm also on the Core Team for PostgreSQL, and I wanted to give some closure on your issue before you quit with a bad taste in your mouth. Your posting hit a sore point in the collective PostgreSQL community, so you got a strong reaction from several people on the list -- probably out of proportion to your posting. Or, to put it another way, you posted something intended to offend people out of your frustration, and got a very offended reaction back. > Rather than trying to figure out whats going on in my head, how about > figuring out whats going on in my database? :-) > I am not allowed to share schemas...sorry but thats what the contract says. > The queries represent code, thus intellectual property, thus I can't post > them. I think you recognize, now, that this list cannot help you under those circumstances? A significant portion of my income derives from clients who need tuning help under NDA. If, however, you don't need any capabilites that PostgreSQL has which MySQL doesn't, hiring a consultant would not be money well spent. > I really wasn't looking for battle this fine day....I'm going outside to > BBQ! (and if you conclude from that that I'm not interested in this or > that, there's nothing I can do about that. It is a beautiful day out and > bbq does sound more fun than this list. sorry) No arguments there ... wish I didn't have to work :-( -- Josh Berkus Aglio Database Solutions San Francisco
> Andrew Sullivan wrote: > I cannot, for the life of me, understand how anyone can > install some software which is supposed to provide meaningful > results under production conditions, and not bother to read > even the basic "quickstart"-type stuff that is kicking > around. Then please point out where it sais, in the documentation, that the value for the shared_memory of 64 is too low and that 4000 is a nice value to start with? Please, also point out the part of the documentation that explains how high the fsm-settings should be, what the impact of a lower or higher sort_mem-setting is, what kind of value the effective_cache_size should have and the best way to determine that. If you can find the above in the default-documentation, like the "getting started"-documents or the administration documentation, than be so kind to give direct links or quotes to that. I was unable to find that, now in a 15 minute search in the docs themselves and I have read most part of them (in the past)... Especially in chapter 10 "Performance hints" I was surprised not to see such information, although it could be considered an administration task, but there it wasn't in chapter 10 (monitoring database usage) either. I'm sorry to put this in a such a confronting manner, but you simply can't expect people to search for information that they don't know the existence of... Actually, that doesn't appear to exist, at least not on the places you'd expect that information to be placed. I, myself, have read Bruce's document on performance tuning, but even that document doesn't provide the detail of information that can be read in this mailing-list. Having said that, this list only has 461 subscribers and I can hardly believe that that are _all_ users of postgresql, as long as it's not the default way of trying to gather data, it shouldn't be expected that anyone actually tries to find his information in this list. Anyway, I saw that there has been done some effort to create a document that does describe such parameters, I'd be happy to see and read that :) > There is _no secret_ that Postgres is configured as > a toy out of the box. One presumes that DBAs are hired to do > _some_ little bit of work. I don't see it on the frontpage, nor in the documentation. Anyway, see above :) Regards, Arjen Btw, I've tried to tune my postgresql database using the administration and tech documents, and saw quite a few queries run quite a lot faster on mysql, I'll try to set up a more useful test environment and supply this list with information to allow me to tune it to run more or less equal to mysql. I do see 3x runs, even with the shared memory and sort mem settings cranked up and having done little to none tuning on mysql :)
Why is such a simple list of questions not somewhere in the documentation? :( Of course a few of your questions are relatively case-dependent, but the others are very general. Such information should be in the documentation and easy to access :) Regards, Arjen > Stephan Szabo wrote a nice list of helpful questions
On Fri, Jul 04, 2003 at 08:07:18PM +0200, Arjen van der Meijden wrote:
> > Andrew Sullivan wrote:
> > results under production conditions, and not bother to read
> > even the basic "quickstart"-type stuff that is kicking
> > around.
> Then please point out where it sais, in the documentation, that the
> value for the shared_memory of 64 is too low and that 4000 is a nice
> value to start with?
I think I did indeed speak too soon, as the criticism is a fair one:
nowhere in the installation instructions or the "getting started"
docs does it say that you really ought to do some tuning once you
have the system installed. Can I suggest for the time being that
something along these lines should go in 14.6.3, "Tuning the
installation":
---snip---
By default, PostgreSQL is configured to run on minimal hardware. As
a result, some tuning of your installation will be necessary before
using it for anything other than extremely small databases. At the
very least, it will probably be necessary to increase your shared
buffers setting. See Chapter 16 for details on what tuning options
are available to you.
---snip---
> I'm sorry to put this in a such a confronting manner, but you simply
> can't expect people to search for information that they don't know the
> existence of.
No need to apologise; I think you're right.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
People: > I think I did indeed speak too soon, as the criticism is a fair one: > nowhere in the installation instructions or the "getting started" > docs does it say that you really ought to do some tuning once you > have the system installed. Can I suggest for the time being that > something along these lines should go in 14.6.3, "Tuning the > installation": > > ---snip--- > By default, PostgreSQL is configured to run on minimal hardware. As > a result, some tuning of your installation will be necessary before > using it for anything other than extremely small databases. At the > very least, it will probably be necessary to increase your shared > buffers setting. See Chapter 16 for details on what tuning options > are available to you. > ---snip--- I think we actually need much more than this. Kaarel on the Advocacy list has volunteered to try to extend our "getting started" section to encompass some basic tuning stuff. Of course, more people would be better. -- -Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
>> ---snip---
>> By default, PostgreSQL is configured to run on minimal hardware. As
>> a result, some tuning of your installation will be necessary before
>> using it for anything other than extremely small databases. At the
>> very least, it will probably be necessary to increase your shared
>> buffers setting. See Chapter 16 for details on what tuning options
>> are available to you.
>> ---snip---
> I think we actually need much more than this.
I am about to propose a patch that will cause the default shared_buffers
to be more realistic, say 1000, on machines where the kernel will allow
it. Not sure if people will let me get away with applying it
post-feature-freeze, but if so that would change the terms of this
debate noticeably.
regards, tom lane
Tom,
> I am about to propose a patch that will cause the default shared_buffers
> to be more realistic, say 1000, on machines where the kernel will allow
> it. Not sure if people will let me get away with applying it
> post-feature-freeze, but if so that would change the terms of this
> debate noticeably.
+1
--
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
> I am about to propose a patch that will cause the default shared_buffers > to be more realistic, say 1000, on machines where the kernel will allow > it. Not sure if people will let me get away with applying it > post-feature-freeze, but if so that would change the terms of this > debate noticeably. It's not a feature change, it's a bug fix -- bug being an oversight.
Attachment
>Afaik, your original posting said postgresql was 3 times slower than mysql >and that you are going to leave this list now. This implied that you have >made your decision between postgresql and mysql, taking mysql because it is >faster. Well, that shows what you get for making implications. The client is sticking with postgres and we are coding around the issue in other ways. >If I was your client and gave you the task to choose a suitable database for >my application and you evaluated suitable databases this way, then something >is seriously wrong with your work. > >Regards, >Bjoern Glad to see you're not getting personal with this. Ad hominin attacks are for folks with no better answers. Please go read the posts by Kevin Schroeder and Arjen va der Meijden before slinging any more 'help'. over and out.
>> Afaik, your original posting said postgresql was 3 times slower than >> mysql and that you are going to leave this list now. This implied >> that you have made your decision between postgresql and mysql, >> taking mysql because it is faster. > > Well, that shows what you get for making implications. The client is > sticking with postgres and we are coding around the issue in other > ways. As many other guys here pointed out, there are numerous ways to tune postgresql for maximum performance. If you are willing to share more information about your particular project, we might be able to help you out and optimize your application, without the need to code around the issue as much as you may be doing right now. Even if it is not possible for you to share enough information, there are a lot of places where you can read about performance tuning (if not in the docs then in the archives). >> If I was your client and gave you the task to choose a suitable >> database for my application and you evaluated suitable databases >> this way, then something is seriously wrong with your work. >> > Glad to see you're not getting personal with this. Ad hominin attacks > are for folks with no better answers. Yep, you're right. Sorry for that, I didn't mean to get personal. I was somehow irritated that you come here, post your database comparison and want to leave right afterwards, without going into detail (what should be the case normally). Again our offer: Post (possibly obfuscated) schema information, and we will certainly be able to help you with performance tuning. Regards, Bjoern
...and on Sat, Jul 05, 2003 at 12:24:18AM +0200, Bjoern Metzdorf used the keyboard:
> >> Afaik, your original posting said postgresql was 3 times slower than
> >> mysql and that you are going to leave this list now. This implied
> >> that you have made your decision between postgresql and mysql,
> >> taking mysql because it is faster.
> >
> > Well, that shows what you get for making implications. The client is
> > sticking with postgres and we are coding around the issue in other
> > ways.
>
> As many other guys here pointed out, there are numerous ways to tune
> postgresql for maximum performance. If you are willing to share more
> information about your particular project, we might be able to help you out
> and optimize your application, without the need to code around the issue as
> much as you may be doing right now.
> Even if it is not possible for you to share enough information, there are a
> lot of places where you can read about performance tuning (if not in the
> docs then in the archives).
>
Also, I should think the clients would not be too offended if Brian posted
some hint about the actual quantity of data involved here, both the total
expected database size and some info about the estimated "working set" size,
such as a sum of sizes of tables most commonly used in JOIN queries and the
percentage of data being shuffled around in those. Are indexes big? Are
there any multicolumn indexes in use? Lots of sorting expected? Lots of
UPDATEs/INSERTs/DELETEs?
Also, it would be helpful to know just how normalized the database is, to
provide some advice about possible query optimization, which could again
prove helpful in speeding the machinery up.
Another useful piece of information would be the amount of memory consumed
by other applications vs. the amount of memory reserved by the OS for cache,
and the nature of those other applications running - are they big cache
consumers, such as Apache with static content and a large load would be,
or do they keep a low profile?
I think this would, in combination with the information already posted, such
as the amount of memory and I/O subsystem info, at least enable us to advise
about the recommended shared_buffers, effective_cache_size, sort_mem,
vacuum_mem, and others, without compromising the intellectual property of
Brian's clients.
> > over and out.
I CC'd this post over to you, Brian, 'cause this signoff made me rather
unsure as to whether or not you're still on the list. Hope you don't mind.
Sincerely,
--
Grega Bremec
System Administration & Development Support
grega.bremec-at-noviforum.si
http://najdi.si/
http://www.noviforum.si/
On Friday, July 4, 2003, at 07:07 AM, Brian Tarbox wrote:
> We had about 40 tables in the db, with joined queries on about 8-12
> tables.
>
A while ago a tested a moderately complex schema on MySQL, Pg, and
Oracle. I usually heavily normalize schemas and then define views as a
denormalized API, which sends MySQL to the book of toys already. The
views more often than not would join anywhere from 6-12 tables, using
plain (as opposed to compound) foreign keys to primary key straight
joins.
I noticed that Pg was more than an order of magnitude slower for joins
> 8 tables than Oracle. I won't claim that none of this can have been
due to lack of tuning. My point is the following though. After I dug in
it turned out that of the 4 secs Pg needed to execute the query it
spent 3.9 secs in the planner. The execution plan Pg came up with was
pretty good - it just needed an extraordinary amount of time to arrive
at it, spoiling its own results.
Asking this list I then learned how to tweak GEQO such that it would
pick up the planning and do it faster than it would otherwise. I was
able to get the planner time down to a quarter - still a multitude of
the actual execution time.
I was told on this list that query planning suffers from combinatorial
explosion very quickly - and I completely buy that. It's just - Oracle
planned the same query in a fraction of a second, using the cost-based
optimizer, on a slower machine. I've seen it plan 15-table joins in
much less than a second, and I have no idea how it would do that. In
addition, once you've prepared a query in Oracle, the execution plan is
pre-compiled.
If I were a CS student I'd offer myself to the hall of humiliation and
set out to write a fast query planner for Pg ...
-hilmar
--
-------------------------------------------------------------
Hilmar Lapp email: lapp at gnf.org
GNF, San Diego, Ca. 92121 phone: +1-858-812-1757
-------------------------------------------------------------
Brian Tarbox kirjutas R, 04.07.2003 kell 15:27: > I recently took a system from MySQL to Postgres. Same HW, SW, same data. > The major operations where moderately complex queries (joins on 8 tables). > The results we got was that Postgres was fully 3 times slower than MySql. For each and every query ?? > We were on this list a fair bit looking for answers and tried all the > standard answers. Could you post the list of "standard answers" you tried ? > It was still much much much slower. Was this with InnoDB ? what kind of joins were they (i.e "FROM a JOIN b on a.i=b.i" or "FROM a,b WHERE a.i = b.i" ? What was the ratio of planning time to actual execution time in pgsql? Where the queries originally optimized for MySQL ? ---------------- Hannu
On Mon, 7 Jul 2003, Brian Tarbox wrote: > Oddly enough, the particular application in question will have an extremely > small user base...perhaps a few simultainous users at most. > > As to the testing, I neglected to say early in this thread that my manager > instructed me _not_ to do further performance testing...so as a good > consultant I complied. I'm not going to touch if that was a smart > instruction to give :-) But remember, you can always rename your performance testing as compliance testing and then it's ok, as long as you don't keep any detailed records about the time it took to run the "compliance testing" queries. Definitely look at the output from explain analyze select ... to see what the planner THINKS the query is gonna cost versus what it really costs. If you see a huge difference between, say estimated rows and actual rows, or some other value, it points to the analyzer not getting the right data for the planner. You can adjust the percentage of a table sampled with alter table to force more data into analyze.
On Fri, 4 Jul 2003, Brian Tarbox wrote: > I'm actually leaving this list but I can answer this question. Our results > were with a single user and we were running Inodb. We were running on > RedHat 8.0 / 9.0 with vanilla linux settings. Hi Brian, I just wanted to add that if you aren't testing your setup for multiple users, you are doing yourself a disservice. The performance of your app with one user is somewhat interesting, the performance of the system with a dozen or a hundred users is of paramount importance. A server that dies under heavy parallel load is useless, no matter how fast it ran when tested for one user. Conversely, one would prefer a server that was a little slow for single users but can hold up under load. When I first built my test box a few years ago, I tested postgresql / apache / php at 100 or more parallel users. That's where things start getting ugly, and you've got to test for it now, before you commit to a platform. Postgresql is designed to work on anything out of the box, which means it's not optimized for high performance, but for running on old Sparc 2s with 128 meg of ram. If you're going to test it against MySQL, be fair to yourself and performance tune them both before testing, they're performance on vanilla linux with vanilla configuration tuning teachs you little about how they'll behave in production on heavy iron. Good luck on your testing, and please, don't quit testing at the first sign one or the other is faster, be throrough and complete, including heavy parallel load testing with reads AND writes. Know the point at which each system begins to fail / become unresponsive, and how they behave in overload.
Oddly enough, the particular application in question will have an extremely small user base...perhaps a few simultainous users at most. As to the testing, I neglected to say early in this thread that my manager instructed me _not_ to do further performance testing...so as a good consultant I complied. I'm not going to touch if that was a smart instruction to give :-) Brian -----Original Message----- From: scott.marlowe [mailto:scott.marlowe@ihs.com] Sent: Monday, July 07, 2003 1:35 PM To: Brian Tarbox Cc: michael.mattox@verideon.com; Rafal Kedziorski; pgsql-performance@postgresql.org Subject: Re: [PERFORM] PostgreSQL vs. MySQL On Fri, 4 Jul 2003, Brian Tarbox wrote: > I'm actually leaving this list but I can answer this question. Our results > were with a single user and we were running Inodb. We were running on > RedHat 8.0 / 9.0 with vanilla linux settings. Hi Brian, I just wanted to add that if you aren't testing your setup for multiple users, you are doing yourself a disservice. The performance of your app with one user is somewhat interesting, the performance of the system with a dozen or a hundred users is of paramount importance. A server that dies under heavy parallel load is useless, no matter how fast it ran when tested for one user. Conversely, one would prefer a server that was a little slow for single users but can hold up under load. When I first built my test box a few years ago, I tested postgresql / apache / php at 100 or more parallel users. That's where things start getting ugly, and you've got to test for it now, before you commit to a platform. Postgresql is designed to work on anything out of the box, which means it's not optimized for high performance, but for running on old Sparc 2s with 128 meg of ram. If you're going to test it against MySQL, be fair to yourself and performance tune them both before testing, they're performance on vanilla linux with vanilla configuration tuning teachs you little about how they'll behave in production on heavy iron. Good luck on your testing, and please, don't quit testing at the first sign one or the other is faster, be throrough and complete, including heavy parallel load testing with reads AND writes. Know the point at which each system begins to fail / become unresponsive, and how they behave in overload.
On Sunday 13 July 2003 10:23, Ron Johnson wrote: > On Fri, 2003-07-04 at 09:49, Shridhar Daithankar wrote: > > On 4 Jul 2003 at 16:35, Michael Mattox wrote: > > [snip] > > > On a positive note, me and Josh are finishing a bare bone performance > > article that would answer lot of your questions. I am counting on you to > > provide valuable feedback. I expect it out tomorrow or on sunday..Josh > > will confirm that.. > > Hello, > > Is this doc publicly available yet? Yes. See http://www.varlena.com/GeneralBits/ I thought I announved it on performance.. anyways.. Shridhar
I think the issue with multiple users is that a car is good for moving a few people, but it can't move lots of large boxes. A truck can move large boxes, but it can't move a few people efficiently. PostgreSQL is more like a truck, while MySQL is more like a car. As an aside, I think Solaris is slower than other OS's because it is built to scale efficiently to many CPU's, and that takes a performance hit in a machine with just a few CPU's, though they are working on tuning those cases. Of course, this is all just a generalization. --------------------------------------------------------------------------- scott.marlowe wrote: > On Fri, 4 Jul 2003, Brian Tarbox wrote: > > > I'm actually leaving this list but I can answer this question. Our results > > were with a single user and we were running Inodb. We were running on > > RedHat 8.0 / 9.0 with vanilla linux settings. > > Hi Brian, I just wanted to add that if you aren't testing your setup for > multiple users, you are doing yourself a disservice. The performance of > your app with one user is somewhat interesting, the performance of the > system with a dozen or a hundred users is of paramount importance. > > A server that dies under heavy parallel load is useless, no matter how > fast it ran when tested for one user. Conversely, one would prefer a > server that was a little slow for single users but can hold up under load. > > When I first built my test box a few years ago, I tested postgresql / > apache / php at 100 or more parallel users. That's where things start > getting ugly, and you've got to test for it now, before you commit to a > platform. > > Postgresql is designed to work on anything out of the box, which means > it's not optimized for high performance, but for running on old Sparc 2s > with 128 meg of ram. If you're going to test it against MySQL, be fair to > yourself and performance tune them both before testing, they're > performance on vanilla linux with vanilla configuration tuning teachs you > little about how they'll behave in production on heavy iron. > > Good luck on your testing, and please, don't quit testing at the first > sign one or the other is faster, be throrough and complete, including > heavy parallel load testing with reads AND writes. Know the point at > which each system begins to fail / become unresponsive, and how they > behave in overload. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Brian Tarbox wrote: > Oddly enough, the particular application in question will have an extremely > small user base...perhaps a few simultainous users at most. > > As to the testing, I neglected to say early in this thread that my manager > instructed me _not_ to do further performance testing...so as a good > consultant I complied. I'm not going to touch if that was a smart > instruction to give :-) Performance is probably 'good enough', and you can revisit it later when you have more time. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Andrew Sullivan wrote:
> On Fri, Jul 04, 2003 at 08:07:18PM +0200, Arjen van der Meijden wrote:
> > > Andrew Sullivan wrote:
> > > results under production conditions, and not bother to read
> > > even the basic "quickstart"-type stuff that is kicking
> > > around.
> > Then please point out where it sais, in the documentation, that the
> > value for the shared_memory of 64 is too low and that 4000 is a nice
> > value to start with?
>
> I think I did indeed speak too soon, as the criticism is a fair one:
> nowhere in the installation instructions or the "getting started"
> docs does it say that you really ought to do some tuning once you
> have the system installed. Can I suggest for the time being that
> something along these lines should go in 14.6.3, "Tuning the
> installation":
>
> ---snip---
> By default, PostgreSQL is configured to run on minimal hardware. As
> a result, some tuning of your installation will be necessary before
> using it for anything other than extremely small databases. At the
> very least, it will probably be necessary to increase your shared
> buffers setting. See Chapter 16 for details on what tuning options
> are available to you.
> ---snip---
>
> > I'm sorry to put this in a such a confronting manner, but you simply
> > can't expect people to search for information that they don't know the
> > existence of.
>
> No need to apologise; I think you're right.
Agreed. Text added to install docs:
<para>
By default, <productname>PostgreSQL</> is configured to run on minimal
hardware. This allows it to start up with almost any hardware
configuration. However, the default configuration is not designed for
optimum performance. To achieve optimum performance, several server
variables must be adjusted, the two most common being
<varname>shared_buffers</varname> and <varname> sort_mem</varname>
mentioned in <![%standalone-include[the documentation]]>
<![%standalone-ignore[<xref linkend="runtime-config-resource-memory">]]>.
Other parameters in <![%standalone-include[the documentation]]>
<![%standalone-ignore[<xref linkend="runtime-config-resource">]]>
also affect performance.
</para>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce, > Agreed. Text added to install docs: > > <para> > By default, <productname>PostgreSQL</> is configured to run on minimal > hardware. This allows it to start up with almost any hardware > configuration. However, the default configuration is not designed for > optimum performance. To achieve optimum performance, several server > variables must be adjusted, the two most common being > <varname>shared_buffers</varname> and <varname> sort_mem</varname> > mentioned in <![%standalone-include[the documentation]]> > <![%standalone-ignore[<xref > linkend="runtime-config-resource-memory">]]>. Other parameters in > <![%standalone-include[the documentation]]> <![%standalone-ignore[<xref > linkend="runtime-config-resource">]]> also affect performance. > </para> What would you think of adding a condensed version of my and Shridhar's guide to the install docs? I think I can offer a 3-paragraph version which would cover the major points of setting PostgreSQL.conf. -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus wrote: > Bruce, > > > Agreed. Text added to install docs: > > > > <para> > > By default, <productname>PostgreSQL</> is configured to run on minimal > > hardware. This allows it to start up with almost any hardware > > configuration. However, the default configuration is not designed for > > optimum performance. To achieve optimum performance, several server > > variables must be adjusted, the two most common being > > <varname>shared_buffers</varname> and <varname> sort_mem</varname> > > mentioned in <![%standalone-include[the documentation]]> > > <![%standalone-ignore[<xref > > linkend="runtime-config-resource-memory">]]>. Other parameters in > > <![%standalone-include[the documentation]]> <![%standalone-ignore[<xref > > linkend="runtime-config-resource">]]> also affect performance. > > </para> > > What would you think of adding a condensed version of my and Shridhar's guide > to the install docs? I think I can offer a 3-paragraph version which would > cover the major points of setting PostgreSQL.conf. Yes, I think that is a good idea --- now, does it go in the install docs, or in the docs next to each GUC item? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce, > Yes, I think that is a good idea --- now, does it go in the install > docs, or in the docs next to each GUC item? Hmmm ... both, I think. The Install Docs should have: "Here are the top # things you will want to adjust in your PostgreSQL.conf: 1) Shared_buffers <link> 2) Sort_mem <link> 3) effective_cache_size <link> 4) random_page_cost <link> 5) Fsync <link> etc." Then next to each of these items in the Docs, I add 1-2 sentences about how to set that item. Hmmm ... do we have similar instructions for setting connection options and pg_hba.conf? We should have a P telling people they need to do this. Barring an objection, I'll get to work on this. -- Josh Berkus Aglio Database Solutions San Francisco
Totally agree. --------------------------------------------------------------------------- Josh Berkus wrote: > Bruce, > > > Yes, I think that is a good idea --- now, does it go in the install > > docs, or in the docs next to each GUC item? > > Hmmm ... both, I think. The Install Docs should have: > > "Here are the top # things you will want to adjust in your PostgreSQL.conf: > 1) Shared_buffers <link> > 2) Sort_mem <link> > 3) effective_cache_size <link> > 4) random_page_cost <link> > 5) Fsync <link> > etc." > > Then next to each of these items in the Docs, I add 1-2 sentences about how to > set that item. > > Hmmm ... do we have similar instructions for setting connection options and > pg_hba.conf? We should have a P telling people they need to do this. > > Barring an objection, I'll get to work on this. > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Wed, 2003-10-08 at 14:05, Josh Berkus wrote: > Hmmm ... both, I think. The Install Docs should have: > > "Here are the top # things you will want to adjust in your PostgreSQL.conf: > 1) Shared_buffers <link> > 2) Sort_mem <link> > 3) effective_cache_size <link> > 4) random_page_cost <link> > 5) Fsync <link> > etc." > Barring an objection, I'll get to work on this. I think this kind of information belongs in the documentation proper, not in the installation instructions. I think you should put this kind of tuning information in the "Performance Tips" chapter, and include a pointer to it in the installation instructions. -Neil
>>>>> "JB" == Josh Berkus <josh@agliodbs.com> writes:
JB> Hmmm ... both, I think. The Install Docs should have:
JB> "Here are the top # things you will want to adjust in your PostgreSQL.conf:
JB> 1) Shared_buffers <link>
JB> 2) Sort_mem <link>
JB> 3) effective_cache_size <link>
JB> 4) random_page_cost <link>
JB> 5) Fsync <link>
JB> etc."
Add:
max_fsm_relations (perhaps it is ok with current default)
max_fsm_pages
I don't think you really want to diddle with fsync in the name of
speed at the cost of safety.
and possibly:
checkpoint_segments (if you do a lot of writes to the DB for extended
durations of time) With 7.4 it warns you in the
logs if you should increase this.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
On Wed, Oct 08, 2003 at 01:28:53PM -0400, Bruce Momjian wrote:
>
> Agreed. Text added to install docs:
[&c.]
I think this is just right. It tells a user where to find the info
needed, doesn't reproduce it all over the place, and still points out
that this is something you'd better do. Combined with the new
probe-to-set-shared-buffers bit at install time, I think the reports
of 400 billion times worse performance than MySQL will probably
diminish.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110