Thread: New to PostgreSQL
I'm a relative newbie to databases, and am looking for some input on selecting the right database for my work. My language of choice is PHP5. I started out assuming that MySQL was the natural choice for me, since PHP and MySQL seem to go together like peanut butter and jelly. However, PostgreSQL has also attracted my attention. All my programming life I have moved from language to language, never feeling the master of any. I am looking for a platform to learn and learn well. The choice of PHP5 as the language has been somewhat easier than deciding on the database. So I'll summarize my perceptions based in the research I've done, and would greatly appreciate any commentary. Please don't be afraid to tell me I'm naive, misguided, short sighted or nuts. ;-) PostgreSQL pros: 1. Mature product compared to MySQL. 2. Supports triggers, stored procedures, cursors. 3. Also supports foreign key constrains which I believe are only supported in MySQL beta at this time. 4. A true object relational database, which should mesh well with PHP5's new beefed up object model. 5. Avoids the complexities and ambiguities in licensing from which MySQL seems to suffer. PostGreSQL cons: 1. My impression based on what I've seen written is that PHP/MySQL integration is better. After all, while people talk of LAMP, one does not ever hear of LAPP. However, looking over the functions available for each in PHP5, I don't see obvious evidence of this. 2. Administration is more difficult. I read this in the "PHP5 MySQL Bible" but from what little I've seen for myself, I don't really see the difference. I did note a couple of things in the recent LWN article on PostgreSQL. 7.5 will introduce direct import of CSV files, and the direct renaming of columns, which I believe are things that have been long supported in MySQL. Not show stoppers, of course, but they do hint at PostgreSQL perhaps not being as convenient to use as MySQL. Then again, I recognize that this is a perception based on little evidence. 3. I prefer MySQL's phpmyadmin to the command line for working with the database. I have heard that phppgadmin is not as feature complete. However, looking over my recent installation, I don't see glaring differences. 4. MySQL is more multiplatform. While this does not bother me, I do have coworkers that use Windows that will be affected by my choice of database. Fortunately, I believe 7.5 is to have true (i.e. non-cygwin win32 support). 5. Performance. Here it seems almost impossible to get solid information, and what little there is out there seems quite dated. My general impression is that under light load and simple queries, MySQL is more nimble, but that under heavier, multi-user load more complex queries PostgreSQL pulls ahead. This is based on hear-say and I get the distinct impression that no one really knows, or at least no one ever seems to present numbers to back them up. I also understand that it can be difficult to make an apples to apples comparison due to the feature disparity between the two, which was quite vast until recent times (MySQL's 4.x series). I am currently working on a project, which started out MySQL, but which I am porting to PostGreSQL and will finish development of the two branches in tandem. This seems like a good way to get a feel for similarities and differences. I hope that this post is appropriate for this forum. And please understand that it is in no way intended as flame bait. Thanks for any insights, Steve Bergman
Steve Bergman wrote: > PostGreSQL cons: It seems to me that a lot of these arguments are based on hearsay. You'd be better off downloading PostgreSQL and judging for yourself > 1. My impression based on what I've seen written is that PHP/MySQL > integration is better. After all, while people talk of LAMP, one > does not ever hear of LAPP. However, looking over the functions > available for each in PHP5, I don't see obvious evidence of this. What many people don't know is that LAMP actually stands for Linux, Apache, Middleware, PostgreSQL. Nonetheless, "integration is better" is a nearly content-free statement without any details. > 2. Administration is more difficult. Again, this is hearsay unless you've tried it yourself. Who would take the "PHP5 MySQL Bible" as a judge on that? > 3. I prefer MySQL's phpmyadmin to the command line for working with > the database. I have heard that phppgadmin is not as feature > complete. However, looking over my recent installation, I don't see > glaring differences. Again, hearsay. Try it yourself. > 4. MySQL is more multiplatform. While this does not bother me, I do > have coworkers that use Windows that will be affected by my choice of > database. Fortunately, I believe 7.5 is to have true (i.e. > non-cygwin win32 support). Ignoring the Windows port, PostgreSQL is usually more multiplatform. What really matters, however, is the platform you're interested in. > 5. Performance. Here it seems almost impossible to get solid > information, and what little there is out there seems quite dated. > My general impression is that under light load and simple queries, > MySQL is more nimble, but that under heavier, multi-user load more > complex queries PostgreSQL pulls ahead. This is approximately right, but again, try it yourself. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Sun, Aug 01, 2004 at 07:42:37PM +0200, Peter Eisentraut wrote: > Steve Bergman wrote: > > 5. Performance. Here it seems almost impossible to get solid > > information, and what little there is out there seems quite dated. > > My general impression is that under light load and simple queries, > > MySQL is more nimble, but that under heavier, multi-user load more > > complex queries PostgreSQL pulls ahead. > > This is approximately right, but again, try it yourself. Jan Wieck has prepared a sort-of-TPC-W testing platform, which allows one to compare the performance of a real application using whatever the database is able to provide. A feature that the database doesn't provide is coded in the PHP application code instead --- this is what PHP/MySQL developer do, and what Postgres users should take advantage of. I haven't seen numbers from Jan's test, but apparently anyone can take the test and run it on her own servers ... -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "¿Que diferencia tiene para los muertos, los huérfanos, y aquellos que han perdido su hogar, si la loca destrucción ha sido realizada bajo el nombre del totalitarismo o del santo nombre de la libertad y la democracia?" (Gandhi)
Steve, Welcome to the community. > So I'll summarize my perceptions based in the research I've > done, and would greatly appreciate any commentary. Please don't be > afraid to tell me I'm naive, misguided, short sighted or nuts. ;-) Thank you for posting this. It is always useful for us to have a clear idea of what the "informed" OSS community thinks of PostgreSQL; it tells us what work we still need to do. > 4. A true object relational database, which should mesh well with PHP5's > new beefed up object model. Yeah, execept that PHP really needs some of the OO-to-Relational mapping tools which Perl and Java have. > 5. Avoids the complexities and ambiguities in licensing from which > MySQL seems to suffer. That's an understatement ;-) > 1. My impression based on what I've seen written is that PHP/MySQL > integration is better. After all, while people talk of LAMP, one does > not ever hear of LAPP. However, looking over the functions available > for each in PHP5, I don't see obvious evidence of this. Well, when people say this, "integration" means two things: 1) thanks to O'Reilly, documentation and books on PHP+MySQL are much more available than PHP+PostgreSQL 2) Thanks to both history and the distinct lack of robust database-abstraction tools in PHP to date, "hard-wiring" of MySQL syntax is very common in OSS PHP apps. Thus, frequently, if you want to use a common PHP app off sourceforge you need to either use MySQL or port it yourself. > 2. Administration is more difficult. I read this in the "PHP5 MySQL > Bible" but from what little I've seen for myself, I don't really see the > difference. I did note a couple of things in the recent LWN article on > PostgreSQL. 7.5 will introduce direct import of CSV files, and the > direct renaming of columns, which I believe are things that have been > long supported in MySQL. Not show stoppers, of course, but they do hint > at PostgreSQL perhaps not being as convenient to use as MySQL. Then > again, I recognize that this is a perception based on little evidence. Some things are "more difficult"; this is frequently a tradeoff between flexibility and ease-of-use. Since we support more platforms than MySQL (see below), our setup can be more complex. Since we give the DBA more control over database performance, tuning is more complex. Since we have a high regard for the SQL Standards, we don't provide as many non-standard options on the command line. That's a way in which we are likely to always be different from MySQL; we would rather have the flexibility than the simplicity when we have to make a choice. > 3. I prefer MySQL's phpmyadmin to the command line for working with the > database. I have heard that phppgadmin is not as feature complete. > However, looking over my recent installation, I don't see glaring > differences. Yes, I think the phpPgAdmin team would be surprised to hear this. Also please note that there are *many* options for PG Admin tools; pgAdminIII, pgAccess, XPG, Mammoth Administrator, etc. > 4. MySQL is more multiplatform. While this does not bother me, I do > have coworkers that use Windows that will be affected by my choice of > database. Fortunately, I believe 7.5 is to have true (i.e. non-cygwin > win32 support). Amusing how "more multiplatform" == Windows. We have supported many more unix-like platforms for a much longer time than MySQL -- pretty much if it has a unix shell, we're ported to it, 27 different OSes at last count. Agreed, though, that Windows is a necessity for some people which is why we're doing the port in the first place. > This is based on hear-say and I get the > distinct impression that no one really knows, or at least no one ever > seems to present numbers to back them up. I also understand that it can > be difficult to make an apples to apples comparison due to the feature > disparity between the two, which was quite vast until recent times > (MySQL's 4.x series). Yeah. Try Jan's TPC-W test. Also, MySQL has been talking about improving CrashMe recently. > I am currently working on a project, which started out MySQL, but which > I am porting to PostGreSQL and will finish development of the two > branches in tandem. This seems like a good way to get a feel for > similarities and differences. That is an *excellent* way to test. -- Josh Berkus Aglio Database Solutions San Francisco
On Sun, 2004-08-01 at 13:32 -0700, Josh Berkus wrote: > 1) thanks to O'Reilly, documentation and books on PHP+MySQL are much more > available than PHP+PostgreSQL Thank you so much for the insights. As it happens I just got back from Barnes and Noble... empty handed. The only PostgreSQL book they had was "Practical PostgreSQL" from O'Reilly, Dec 2001. I was really looking for "PostgreSQL: A Comprehensive Guide to Building, Programming, and Administering PostgreSQL Databases" by Korry Douglas and Susan Douglas, which is more recent (Fed, 2003) and seems to get good reviews. Does anyone have any recommendations on a good book on PostgreSQL? I'm a bit embarrassed about the Windows "multiplatform" comment. I should have phrased it better. Also, I was just kind of *assumming* that MySQL compiled on pretty much any POSIX platform. The only OS of great interest to me currently is Linux. But I am trying, and with some success, to get one of my Win32 using coworkers interested in open source tools, and it is important to him that his new development platform run on both Linux and Windows, hence my concern. Thanks again! Steve Bergman
On Sun, 01 Aug 2004 15:58:09 -0500 Steve Bergman <steve@rueb.com> wrote:
> As it happens I just got back from Barnes and Noble... empty handed.
> The only PostgreSQL book they had was "Practical PostgreSQL" from
> O'Reilly, Dec 2001. I was really looking for "PostgreSQL: A
> Comprehensive Guide to Building, Programming, and Administering
> PostgreSQL Databases" by Korry Douglas and Susan Douglas, which is more
> recent (Fed, 2003) and seems to get good reviews. Does anyone have any
> recommendations on a good book on PostgreSQL?
the douglas book is not bad at all. it seems to me that i bought my copy at
a Barnes & Noble.
i also use _Postgresql: Essential Reference_ quite a lot, although it is
missing a few things that i occasionally need to look up.
i have yet to see a decent treatment of PL/PGSQL anywhere.
richard
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security
On Sun, 2004-08-01 at 14:58, Steve Bergman wrote: > On Sun, 2004-08-01 at 13:32 -0700, Josh Berkus wrote: > > > 1) thanks to O'Reilly, documentation and books on PHP+MySQL are much more > > available than PHP+PostgreSQL > > Thank you so much for the insights. > > As it happens I just got back from Barnes and Noble... empty handed. > The only PostgreSQL book they had was "Practical PostgreSQL" from > O'Reilly, Dec 2001. I was really looking for "PostgreSQL: A > Comprehensive Guide to Building, Programming, and Administering > PostgreSQL Databases" by Korry Douglas and Susan Douglas, which is more > recent (Fed, 2003) and seems to get good reviews. Does anyone have any > recommendations on a good book on PostgreSQL? I also like the php/postgresql book by Hans-Jurgen Schonig and Ewal Geschwinde. while there were quite a few typos due to it being translated from German, the writing style and organization is pretty good, and it's a good book for beginners as well as experienced developers. > I'm a bit embarrassed about the Windows "multiplatform" comment. I > should have phrased it better. Also, I was just kind of *assumming* > that MySQL compiled on pretty much any POSIX platform. The only OS of > great interest to me currently is Linux. But I am trying, and with some > success, to get one of my Win32 using coworkers interested in open > source tools, and it is important to him that his new development > platform run on both Linux and Windows, hence my concern. At my last job (a company now sliding quickly into a Windows only trap, sad, was a great company with a great unix shop before Lyle Lanley showed up) I ran a central PostgreSQL server and anyone who wanted their own database could have one. It ran on my own workstation, which had about 99.9% uptime without me really trying. And I never really noticed the load. Of course, my workstation was a 1.4 GHz Celeron with 512 Meg of RAM, so it wasn't puny by any standards. Til windows native support shows up in 7.5, you can always do that for a while.
> 1. Mature product compared to MySQL. > > 2. Supports triggers, stored procedures, cursors. > > 3. Also supports foreign key constrains which I believe are only > supported in MySQL beta at this time. You cannot exist as a real human being unless you have foreign keys :) > 4. A true object relational database, which should mesh well with PHP5's > new beefed up object model. I don't think it will make much difference really. > 5. Avoids the complexities and ambiguities in licensing from which > MySQL seems to suffer. > > > PostGreSQL cons: > > 1. My impression based on what I've seen written is that PHP/MySQL > integration is better. After all, while people talk of LAMP, one does > not ever hear of LAPP. However, looking over the functions available > for each in PHP5, I don't see obvious evidence of this. Yeah, it's a just silly myth. I do heaps of PHP/PostgreSQL coding. > 2. Administration is more difficult. I read this in the "PHP5 MySQL > Bible" but from what little I've seen for myself, I don't really see the > difference. I did note a couple of things in the recent LWN article on > PostgreSQL. 7.5 will introduce direct import of CSV files, and the > direct renaming of columns, which I believe are things that have been > long supported in MySQL. Not show stoppers, of course, but they do hint > at PostgreSQL perhaps not being as convenient to use as MySQL. Then > again, I recognize that this is a perception based on little evidence. 7.5 will be able to alter column TYPE, not column name. However, since MySQL has to copy your entire table to do things like adding an index, dropping a column, etc. which PostgreSQL can do in O(1) time for dropping a col for instance, then maybe it's postgres that's more convenient. > 3. I prefer MySQL's phpmyadmin to the command line for working with the > database. I have heard that phppgadmin is not as feature complete. > However, looking over my recent installation, I don't see glaring > differences. I'm the lead developer of phpPgAdmin and I can guarantee you that phpPgAdmin has ten times the features of phpMyAdmin, basically because PostgreSQL has 10 times the features of MySQL. > 4. MySQL is more multiplatform. While this does not bother me, I do > have coworkers that use Windows that will be affected by my choice of > database. Fortunately, I believe 7.5 is to have true (i.e. non-cygwin > win32 support). Well, PostgreSQL runs on far more platforms than MySQL, it's just that until 7.5 (beta this week), it didn't run natively on windows. > 5. Performance. Here it seems almost impossible to get solid > information, and what little there is out there seems quite dated. My > general impression is that under light load and simple queries, MySQL is > more nimble, but that under heavier, multi-user load more complex > queries PostgreSQL pulls ahead. This is based on hear-say and I get the > distinct impression that no one really knows, or at least no one ever > seems to present numbers to back them up. I also understand that it can > be difficult to make an apples to apples comparison due to the feature > disparity between the two, which was quite vast until recent times > (MySQL's 4.x series). Performance is fine on both servers really. PostgreSQL is fast enough for any purpose, including simple queries. Of course I believe PostgreSQL allows you to optimize in ways that MySQL users can only dream of. eg. stored procs, prepared queries, triggers, rules, functiones, custom aggregates, operators, index methods, partial indexes, expressional indexes, etc. Just use PostgreSQL - trust me on this! Chris
On Mon, 2 Aug 2004, Christopher Kings-Lynne wrote: > > PostgreSQL. 7.5 will introduce direct import of CSV files, and the > > direct renaming of columns, which I believe are things that have been > > long supported in MySQL. > > 7.5 will be able to alter column TYPE, not column name. And renaming have been in pg since at least 7.0. -- /Dennis Björklund
A few slightly different takes on things... Steve Bergman wrote: >I'm a relative newbie to databases, and am looking for some input on >selecting the right database for my work. My language of choice is >PHP5. I started out assuming that MySQL was the natural choice for me, >since PHP and MySQL seem to go together like peanut butter and jelly. >However, PostgreSQL has also attracted my attention. All my programming >life I have moved from language to language, never feeling the master of >any. I am looking for a platform to learn and learn well. The choice >of PHP5 as the language has been somewhat easier than deciding on the >database. So I'll summarize my perceptions based in the research I've >done, and would greatly appreciate any commentary. Please don't be >afraid to tell me I'm naive, misguided, short sighted or nuts. ;-) > > > I see language choice as relatively irrelevant. Thanks for your post though. >PostgreSQL pros: > >1. Mature product compared to MySQL. > >2. Supports triggers, stored procedures, cursors. > > Furthermore stored procedures can be written in your choice of languages. I usually use SQL and PLPGSQL but PLPerl, and PLTCL come with it out of the box. Additionally you can download handlers for many other language handlers. >4. A true object relational database, which should mesh well with PHP5's >new beefed up object model. > > > Maybe, but at the moment, the OR functionality of Pgsql is overrated, IMO. Though if you are a C programmer, it can be really useful. >PostGreSQL cons: > >1. My impression based on what I've seen written is that PHP/MySQL >integration is better. After all, while people talk of LAMP, one does >not ever hear of LAPP. However, looking over the functions available >for each in PHP5, I don't see obvious evidence of this. > > > I code in PHP against PostgreSQL all the time. The coding is different but not any less integrated. If you like I can send you my lightweight abstraction layers for MySQL and PostgreSQL development. They wrap most of the worst differences. >2. Administration is more difficult. I read this in the "PHP5 MySQL >Bible" but from what little I've seen for myself, I don't really see the >difference. I did note a couple of things in the recent LWN article on >PostgreSQL. 7.5 will introduce direct import of CSV files, and the >direct renaming of columns, which I believe are things that have been >long supported in MySQL. Not show stoppers, of course, but they do hint >at PostgreSQL perhaps not being as convenient to use as MySQL. Then >again, I recognize that this is a perception based on little evidence. > > > Renaming of columns used to be my pet peve. It has been supported for a while now. Then there was dropping a column. THis is now resolved. Changing datatypes is more complex, and I prefer to do it manually. MySQL will automate this process for you but it can be dangerous. >3. I prefer MySQL's phpmyadmin to the command line for working with the >database. I have heard that phppgadmin is not as feature complete. >However, looking over my recent installation, I don't see glaring >differences. > > You always get more flexibility from the command line. Of course most of the GUIs allow you to use a command line for any database manager. >I am currently working on a project, which started out MySQL, but which >I am porting to PostGreSQL and will finish development of the two >branches in tandem. This seems like a good way to get a feel for >similarities and differences. > > Good way to get started. I will share my experience that PostgreSQL has a slightly steeper, but in the end, I would never go back to MySQL. You may find my lightweight abstraction laters interesting in part because reading the code will help show you how the coding is different between the databases. If you are interested, email me off list (files are released under the LGPL) Best Wishes, Chris Travers Metatron Technology Consulting
On Mon, 2004-08-02 at 16:47 -0700, Jonathan Gardner wrote: > > They don't need to run postgreSQL on their developer machine. Just provide > them with a test cluster or a developer's cluster and allow them to create > and administer their own databases. At work, I do run a MySQL and now also a PostgreSQL server. However, one of my coworkers is taken in enough with the idea of using Open Source tools that he wants to set up a development platform at home. Historically, a Windows developer, he is installing Linux there. However, he wants the ability to run the same stuff under Windows for our clients who don't use Unix or Linux. > > > > I am currently working on a project, which started out MySQL, but which > > I am porting to PostGreSQL and will finish development of the two > > branches in tandem. This seems like a good way to get a feel for > > similarities and differences. > > > > There is so much out there that developing in parallel with MySQL will be > like trying to race someone in a wheelchair in a wheelchair. Just because > they are limited to a wheelchair shouldn't mean you have to do the same! > You should be free to run, walk, or even catch a plane ride if you have > that available. > I really appreciate the great responses that I have gotten here. I have ported my (rather small and simple, 1500 line, 4 tables) current project to pgsql, and have already used a few pgsql features. Namely, the (more flexible) unique key constraints and also views. When I decided to develop the pgsql and mysql branches in tandem, I decided to guard against trying to use pgsql as a mysql drop in replacement, since I knew that would be unfair to pgsql. I've decided, at this point do drop development on the mysql branch and just finish up the pgsql branch. I'm pretty much convinced that learning pgsql is where I should put my efforts. Once I've had some more time to study pgsql's capabilities, I'll probably tear this project down and completely redesign it to take full advantage. -Steve
On Mon, 2004-08-02 at 19:57, Steve Bergman wrote: > On Mon, 2004-08-02 at 16:47 -0700, Jonathan Gardner wrote: > > > > > They don't need to run postgreSQL on their developer machine. Just provide > > them with a test cluster or a developer's cluster and allow them to create > > and administer their own databases. > > At work, I do run a MySQL and now also a PostgreSQL server. However, > one of my coworkers is taken in enough with the idea of using Open > Source tools that he wants to set up a development platform at home. > Historically, a Windows developer, he is installing Linux there. > However, he wants the ability to run the same stuff under Windows for > our clients who don't use Unix or Linux. Good news, PostgreSQL is about to enter beta for the next version, and will include a native windows port as part of that version. Some other cool features will be Point in Time Recovery (allows restoring a database to EXACTLY the point it was at when the storage subsystem it was on dies,) Sub transactions / save points, and a few other nice ones, take a look at the release notes when beta comes out this week or so. > I really appreciate the great responses that I have gotten here. I have > ported my (rather small and simple, 1500 line, 4 tables) current project > to pgsql, and have already used a few pgsql features. Namely, the (more > flexible) unique key constraints and also views. When I decided to > develop the pgsql and mysql branches in tandem, I decided to guard > against trying to use pgsql as a mysql drop in replacement, since I knew > that would be unfair to pgsql. I've decided, at this point do drop > development on the mysql branch and just finish up the pgsql branch. > I'm pretty much convinced that learning pgsql is where I should put my > efforts. Once I've had some more time to study pgsql's capabilities, > I'll probably tear this project down and completely redesign it to take > full advantage. That was pretty much my philosophy back when I started with version 6.5.3 of PostgreSQL back in the day. Back then it was kinda slow and you had to be a little more careful what you were doing, as postgres was still a little fragile. Things like unconstrained joins could kill the whole server instance. Nowadays it's very robust, and quite fast. And every time I turn around someone's added some useful new feature I didn't really know I needed.
Steve Bergman wrote: >On Mon, 2004-08-02 at 16:47 -0700, Jonathan Gardner wrote: > > > >>They don't need to run postgreSQL on their developer machine. Just provide >>them with a test cluster or a developer's cluster and allow them to create >>and administer their own databases. >> >> > <> > At work, I do run a MySQL and now also a PostgreSQL server. However, > one of my coworkers is taken in enough with the idea of using Open > Source tools that he wants to set up a development platform at home. > Historically, a Windows developer, he is installing Linux there. > However, he wants the ability to run the same stuff under Windows for > our clients who don't use Unix or Linux. > Hoping we will see PostgreSQL for Win32 do really well :-) I have been running it on Cygwin for a while. Complex to set up but pretty stable aside from pid file issues > >> <>I really appreciate the great responses that I have gotten here. I have >> ported my (rather small and simple, 1500 line, 4 tables) current project >> to pgsql, and have already used a few pgsql features. Namely, the (more >> flexible) unique key constraints and also views. When I decided to >> develop the pgsql and mysql branches in tandem, I decided to guard >> against trying to use pgsql as a mysql drop in replacement, since I knew >> that would be unfair to pgsql. I've decided, at this point do drop >> development on the mysql branch and just finish up the pgsql branch. >> I'm pretty much convinced that learning pgsql is where I should put my >> efforts. Once I've had some more time to study pgsql's capabilities, >> I'll probably tear this project down and completely redesign it to take >> full advantage. >> My lightweight wrappers are more symantic wrappers. Also, I have concluded that the ideal database *should* allow you to have PostgreSQL be a drop-in replacement for anything. The only difference is that you get a number of additional features by using the PostgreSQL version. I do this using many VIEWs which often wrap stored procedures, etc. Here is an example of PostgreSQL in one environment being a drop in replacement for another. I recently ported the application to a new environment-- one of limited user accounts. I was able to set up a database which required modifying only *one* function in my code to make it work. The solution made extensive use of views, stored procs, and triggers to enforce permissions on a per-session app-authenticated level rather than the per-session db authenticated way that is usual. You can ALWAYS wrap stored procs into views to create extremely sophisticated databases. Best Wishes, Chris Travers Metatron Technology Consulting
Attachment
On 8/1/2004 2:52 PM, Alvaro Herrera Munoz wrote: > On Sun, Aug 01, 2004 at 07:42:37PM +0200, Peter Eisentraut wrote: >> Steve Bergman wrote: > >> > 5. Performance. Here it seems almost impossible to get solid >> > information, and what little there is out there seems quite dated. >> > My general impression is that under light load and simple queries, >> > MySQL is more nimble, but that under heavier, multi-user load more >> > complex queries PostgreSQL pulls ahead. >> >> This is approximately right, but again, try it yourself. > > Jan Wieck has prepared a sort-of-TPC-W testing platform, which allows > one to compare the performance of a real application using whatever > the database is able to provide. A feature that the database doesn't > provide is coded in the PHP application code instead --- this is what > PHP/MySQL developer do, and what Postgres users should take advantage > of. > > I haven't seen numbers from Jan's test, but apparently anyone can take > the test and run it on her own servers ... > The code is available here: http://pgfoundry.org/projects/tpc-w-php/ I have run it in a very small configuration (P3 667MHz, 640MB, single IDE) for 200 emulated browsers and 1000 items using Apache 1.3, PHP4 and MySQL 4.1.1 or PostgreSQL 7.4.2. The result is that they are head to head. Without pgpool MySQL is slightly better, with pgpool PostgreSQL pulls ahead. Two groups could possibly waste some time tweaking here, tune there, the tyical arm-wrestling of people who don't have anything better to do. But I wouldn't expect any of those two all of the sudden skyrocketing. The difference is that some of the queries had to be rewritten for MySQL in a way that I would consider not maintainable any more. The new subselect support in 4.1 isn't mature enough to just write a query and expect it works performant. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: >> > > The code is available here: > > http://pgfoundry.org/projects/tpc-w-php/ > I can't see any code, only "This Project Has Not Released Any Files" ?!? Regards, Andreas
On Fri, 2004-08-06 at 16:30, Andreas Pflug wrote: > Jan Wieck wrote: > >> > > > > The code is available here: > > > > http://pgfoundry.org/projects/tpc-w-php/ > > > > I can't see any code, only "This Project Has Not Released Any Files" ?!? > > > Regards, > Andreas Yeah, looks like you need to do a CVS checkout of their repository to give it a whirl. http://pgfoundry.org/scm/?group_id=1000031 which lead me here: http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/tpc-w-php/tpcw-benchmark/ however the stats on the summary page don't show any commits/adds. CVS Repository ( 0 commits, 0 adds ) I'd guess that they imported it recently and haven't done any work against the CVS repository. hope that helps, -Robby /*************************************** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | robby@planetargon.com * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting & Development ****************************************/
On 8/6/2004 7:30 PM, Andreas Pflug wrote: > Jan Wieck wrote: >>> >> >> The code is available here: >> >> http://pgfoundry.org/projects/tpc-w-php/ >> > > I can't see any code, only "This Project Has Not Released Any Files" ?!? Anon CVS instructions are available just a click away (under CVS): http://pgfoundry.org/scm/?group_id=1000031 Jan > > > Regards, > Andreas -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Fri, 2004-08-06 at 14:56 -0400, Jan Wieck wrote: > > I have run it in a very small configuration (P3 667MHz, 640MB, single > IDE) for 200 emulated browsers and 1000 items using Apache 1.3, PHP4 and > MySQL 4.1.1 or PostgreSQL 7.4.2. The result is that they are head to > head. Without pgpool MySQL is slightly better, with pgpool PostgreSQL > pulls ahead. > > Two groups could possibly waste some time tweaking here, tune there, the > tyical arm-wrestling of people who don't have anything better to do. That would be me this weekend. ;-) I'm working on getting this set up for testing postgresql/mysql on various filesystems. (ext3 data=writeback, ordered, journal, reiserfs 3, and possibly XFS.) I have never tuned pgsql before and could use some guidance. I've read the tuning guide from the web site, and the only values I have changed from default are the shared_buffers and effective_cache_size. The target system is: AMD Athlon(tm) XP 2100+ uniprocessor 1GB PC133 memory Fedora Core Rawhide Kernel 2.6.7 (vendor supplied) Hard drives: IDE /dev/hda=120GB 7200rpm, /dev/hdc=60GB, 7200 rpm I've moved pg_xlog to /dev/hdc and the database resides on /dev/hda. postgresql.conf is as follows: max_connections = 100 shared_buffers = 7864 # 6% of main memory effective_cache_size = 64000 # half of main memory syslog = 2 syslog_facility = 'LOCAL0' syslog_ident = 'postgres' lc_messages = 'en_US.UTF-8' # locale for system error message strings lc_monetary = 'en_US.UTF-8' # locale for monetary formatting lc_numeric = 'en_US.UTF-8' # locale for number formatting lc_time = 'en_US.UTF-8' # locale for time formatting The RBE will be running on an athlon thunderbird 1200 w/256MB DDR/266 over a 100mbit linksys switch. Server network card is a linksys (tulip clone) and the network chipset for the client is (I believe) and rhine of some sort. I don't have it set up yet and can't remember for sure. (I'm concerned that the client may not be up to this task, particularly the Rhine chipset. Should I be?) Any comments or suggestions would be greatly appreciated. Thanks, Steve Bergman
Oh, and a couple of other things. I am using pgpool and unix sockets. apache is 2.0.50. pgsql is 7.4.3 (mysql will be 4.1.3 beta). -Steve
Ok. I hope this is the last addendum. Can someone give me specific advice on the use or non-use of pgavd (auto vaccuum daemon) for this test? Also, could someone point me to a download for pgavd. The performance tuning document says its in "contrib" but I don't seem to be finding it. Thanks, Steve
On Sat, Aug 07, 2004 at 12:22:51PM -0500, Steve Bergman wrote: > Oh, and a couple of other things. I am using pgpool and unix sockets. > apache is 2.0.50. pgsql is 7.4.3 (mysql will be 4.1.3 beta). Why don't you try different versions of Postgres too? Say the current devel version versus the latest 7.4 and the latest 7.3. So we can see how much we have improved. Regarding pgavd, it's now called pg_autovacuum AFAIK. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Endurecerse, pero jamás perder la ternura" (E. Guevara)
OK. I'll put 8.0.0 beta on my list. the only references I am seeing to autovacuum on gborg point me to the pgavd project, which fails miserably to build (m4 problems) on my Fedora rawhide system. The project seems quite unmaintained, the latest cvs checkins being 19 months old, and bug reports over a year old have been totally ignored. Am I looking at the wrong project? -Steve On Sat, 2004-08-07 at 14:43 -0400, Alvaro Herrera wrote: > On Sat, Aug 07, 2004 at 12:22:51PM -0500, Steve Bergman wrote: > > > Oh, and a couple of other things. I am using pgpool and unix sockets. > > apache is 2.0.50. pgsql is 7.4.3 (mysql will be 4.1.3 beta). > > Why don't you try different versions of Postgres too? Say the current > devel version versus the latest 7.4 and the latest 7.3. So we can see > how much we have improved. > > Regarding pgavd, it's now called pg_autovacuum AFAIK. >
Steve Bergman wrote: > > the only references I am seeing to autovacuum on gborg point me to the > pgavd project, which fails miserably to build (m4 problems) on my Fedora > rawhide system. The project seems quite unmaintained, the latest cvs > checkins being 19 months old, and bug reports over a year old have been > totally ignored. Am I looking at the wrong project? Yes, you did; pg_autovacuum is a contrib module. It was headed to enter the backend as postmaster controlled process, but unfortunately failed for lack of reviewer's time. Regards, Andreas
On Sunday 08 Aug 2004 12:39 am, Steve Bergman wrote: > OK. I'll put 8.0.0 beta on my list. > > the only references I am seeing to autovacuum on gborg point me to the > pgavd project, which fails miserably to build (m4 problems) on my Fedora > rawhide system. The project seems quite unmaintained, the latest cvs > checkins being 19 months old, and bug reports over a year old have been > totally ignored. Am I looking at the wrong project? Yes. The project is dead. It is since been migrated to C and integrated in core backend system. It is bundled in contrib module as far as 7.4.x is concerned. I don't know where it lives in 8.0 source tree though.. HTH Shridhar
>however the stats on the summary page don't show any commits/adds. >CVS Repository ( 0 commits, 0 adds ) > >I'd guess that they imported it recently and haven't done any work >against the CVS repository. The cvs repository statistics on pgfoundry are broken. They show up zero no matter how much you commit... //Magnus