Re: modperl and SQL db select
Quoting dreamwvr [EMAIL PROTECTED] [Mar 21, 2002 13:10]: Is there any issue with using modperl with postgres vs mysql for a database driven website? Don't want to bark up the wrong tree in a mod_perl project only to discover I picked the wrong .db :-/ Take a look at URL:http://www.phpbuilder.com/columns/tim2705.php3, in which the author discusses why Sourceforge uses postgresql instead of MySQL. It's a little dated (the postgres version is 7.1, for example) but a fun read. (darren) -- We must respect the other fellow's religion, but only in the sense and to the extent that we respect his theory that his wife is beautiful and his children smart. -- H.L.Mencken
Re: modperl and SQL db select
darren chamberlain [EMAIL PROTECTED] writes: Quoting dreamwvr [EMAIL PROTECTED] [Mar 21, 2002 13:10]: Is there any issue with using modperl with postgres vs mysql for a database driven website? Don't want to bark up the wrong tree in a mod_perl project only to discover I picked the wrong .db :-/ Take a look at URL:http://www.phpbuilder.com/columns/tim2705.php3, in which the author discusses why Sourceforge uses postgresql instead of MySQL. It's a little dated (the postgres version is 7.1, for example) but a fun read. It's also been thoroughly rebutted ISTR :-) -- David Hodgkinson, Wizard for Hirehttp://www.davehodgkinson.com Editor-in-chief, The Highway Star http://www.deep-purple.com All the Purple Family Tree news http://www.slashrock.com Interim Technical Director, Web Architecture Consultant for hire
Re: modperl and SQL db select
Quoting Dave Hodgkinson [EMAIL PROTECTED] [Mar 21, 2002 13:25]: darren chamberlain [EMAIL PROTECTED] writes: Quoting dreamwvr [EMAIL PROTECTED] [Mar 21, 2002 13:10]: Is there any issue with using modperl with postgres vs mysql for a database driven website? Don't want to bark up the wrong tree in a mod_perl project only to discover I picked the wrong .db :-/ Take a look at URL:http://www.phpbuilder.com/columns/tim2705.php3, in which the author discusses why Sourceforge uses postgresql instead of MySQL. It's a little dated (the postgres version is 7.1, for example) but a fun read. It's also been thoroughly rebutted ISTR :-) I didn't say it was _accurate_, I said it was _fun_. :) (darren) -- There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies. -- C.A.R. Hoare
Re: modperl and SQL db select
On March 21, 2002 10:37 am, darren chamberlain wrote: Quoting Dave Hodgkinson [EMAIL PROTECTED] [Mar 21, 2002 13:25]: darren chamberlain [EMAIL PROTECTED] writes: Quoting dreamwvr [EMAIL PROTECTED] [Mar 21, 2002 13:10]: Is there any issue with using modperl with postgres vs mysql for a database driven website? Don't want to bark up the wrong tree in a mod_perl project only to discover I picked the wrong .db :-/ Take a look at URL:http://www.phpbuilder.com/columns/tim2705.php3, in which the author discusses why Sourceforge uses postgresql instead of MySQL. It's a little dated (the postgres version is 7.1, for example) but a fun read. Note that a couple of his assertions in this document would be rather naive. The first one I noted was that he assumes that a high performance app has several joins. I think everyone here who's developed a few db apps will tell you that joins are hugely costly and should be avoided for an application's most common cases. I recoded a mysql/mod_perl web app with a couple of joins to a slightly but not horridly denormalized db structure and realized some huge performance gains. Further on, he was discussing threaded discussion forums and assumed a recursive query. While recursing the DATA is probably (though provabably not always) necessary, there is no need to storm the db with that many queries, since query setup time, queing, and results extraction have noticable time penalties. Extracting the data with a single query and recursing it in RAM is a much more db and cpu friendly idea. So is time dependent caching. How many inserts into a table per second do you expect? For your average discussion thing, maybe one new comment per minute. With a one minute cache on the db data, suddenly your speed limits are removed. Why would you need to requery the db if you don't expect it to change? This is the joy of mod_perl, being able to keep some context of your _application_ between hits of your _users_. As for performance, mysql and postgresql are getting pretty similar results, _these_ days. With aggressive app level caching of db data, and the innodb table handler, I was able to push mysql and mod_perl to 80+ 50kbyte page views per second on bog standard 650 mhz intel hardware. -- Jay yohimbe Thorne [EMAIL PROTECTED] Mgr Sys Tech, Userfriendly.org
Re: modperl and SQL db select
Postgres: +More relational? So they say. More features perhaps. +stored procedures - ie. you can make query wrapping methods -pain the rear postgres user permissions to control. +been doing complex sql queries (subselects etc,) for longer. +Reminds me a lot of what I used to be able to do with oracle. +probably faster to set up ( than oracle ;) ). +every element of the database has a unique oid field which internally makes everything uniquely accessible by this field. +has all or nothing transaction support to avoid partially corrupting your data - it either completes the query(or group of queries) or it does'nt - useful when it's under heavy load or joe blogs comes along writes cr@p code and dependent writes updates to the live db, since he enjoys making changes to live, he doesn't know much sql and and he mispells update in the second query. Ooops. He's got a partially altered database and who knows what insanity this might cause for us. Or even manager blogs using odbc and doing the same there with his 'i'm a techie deep down' script. That was long! +Better under heavy load. +Clean shutdowns under heavy load. +psql has a cr@p command line. +better optimizer Mysql:+we all start with it. +Bench marks much faster. +/-Can allow nested queries (subselects again) though this is a relatively new feature. +People say it's less relational. Isn't really - but has fewer features (see above) +Doesn't have stored procedure support. +Think that I've read that it's indexing mechanism is not as good as postgres'. +probably easier to grasp if you haven't used it before +negate all of the above postgres points. -less reliable under heavy load. Images: Image::Magick - probably don't want the pictures themselves in the database, so much as pointers to their physical location and a means of pushing them in and out - use this for getting stats on them and manipulating thumbnails etc. My .02 EU Probably get flamed for being all wrong. But I'm not. So there!! :) Happy First Day of Spring, fiq On Thu, 21 Mar 2002, dreamwvr wrote: hi, Is there any issue with using modperl with postgres vs mysql for a database driven website? Don't want to bark up the wrong tree in a mod_perl project only to discover I picked the wrong .db :-/ From a licensing perspective which one is less risky if doing some work for a client? Noticed slashcode however the postgres port has stopped. AFAIK anyhow. Thought if practical I would do some coding to integrate Postgres if it is the right choice. If not MySQL? Just wanted to confirm that either is safe for a customer to use. They would not sell the program but they might sell the programs I write and the actual singular database. Basically I want to simply insert data into a .db and then produce HTML from the results and queries. The thought has crossed my mind to as well output the images as well from the .db What mod:perl modules should I seriously consider. Appreciate any pointers here as am just about to to begin the DBI:: stuff. TIA [EMAIL PROTECTED]
Re: modperl and SQL db select
On Thu, 21 Mar 2002, Jay Thorne wrote: The first one I noted was that he assumes that a high performance app has several joins. I think everyone here who's developed a few db apps will tell you that joins are hugely costly and should be avoided for an application's most common cases. Actually, I've developed _more_ than a few DB apps and I'd tell you that joins are not only _not_ hugely costly, but can sometimes be a performance improvement. It really depends on a lot of factors including what RDBMS you are using, how many connections you have, ratio of reads to writes, how complicated the joins are. But a blanket statement like that is flat out wrong. I recoded a mysql/mod_perl web app with a couple of joins to a slightly but not horridly denormalized db structure and realized some huge performance gains. You removed denormalization, thereby increasing the amount of work necessary to maintain data integrity so the reads may be faster but the writes are definitely slower and more prone to screwing things up. Even worse, you denormalized in favor of _one_ app, obviously, which means any new app you write on top of this denormalized db in the future may suffer because the denormalization doesn't serve it well. Further on, he was discussing threaded discussion forums and assumed a recursive query. While recursing the DATA is probably (though provabably not always) necessary, there is no need to storm the db with that many queries, since query setup time, queing, and results extraction have noticable time penalties. Extracting the data with a single query and recursing it in RAM is a much more db and cpu friendly idea. This I can agree on, though it is more a limitation of SQL, which provides no meaningful way to do a recursive query. As for performance, mysql and postgresql are getting pretty similar results, _these_ days. With aggressive app level caching of db data, and the innodb table handler, I was able to push mysql and mod_perl to 80+ 50kbyte page views per second on bog standard 650 mhz intel hardware. Again, that will depend on a number of things including complexity of your queries and ratio of reads vs. writes, etc. It's worth noting that MySQL's query optimizer pretty much stinks and will often not use indexes because it simply gives up when queries have an 'OR' in them, for example. What it comes down to is that except for _extremely_ demanding applications, either one is likely to be perfectly suitable so pick the one that you like best, or that has the features you need, or that your sysadmins can support most easily, or whatever. -dave /*== www.urth.org we await the New Sun ==*/
Re: modperl and SQL db select
On Thu, 21 Mar 2002, Rafiq Ismail (ADMIN) wrote: Mysql:+we all start with it. +Bench marks much faster. +/-Can allow nested queries (subselects again) though this is a relatively new feature. Eh? Subselects aren't scheduled for implementation until 4.1. The current version is 4.0.2. +People say it's less relational. Isn't really - but has fewer features (see above) I'm not sure what less relational means. -less reliable under heavy load. I'm not sure where you get that from either. MySQL is used under some very heavy loads (read the MySQL users list). Wow, this thread is really bringing out the fiction! /*== www.urth.org we await the New Sun ==*/
Re: modperl and SQL db select
On March 21, 2002 01:05 pm, Dave Rolsky wrote: On Thu, 21 Mar 2002, Jay Thorne wrote: The first one I noted was that he assumes that a high performance app has several joins. I think everyone here who's developed a few db apps will tell you that joins are hugely costly and should be avoided for an application's most common cases. Actually, I've developed _more_ than a few DB apps and I'd tell you that joins are not only _not_ hugely costly, but can sometimes be a performance improvement. It really depends on a lot of factors including what RDBMS you are using, how many connections you have, ratio of reads to writes, how complicated the joins are. But a blanket statement like that is flat out wrong. Okay, now I need an example. I've never seen a query on any db where a single table query was slower than a two table join. Of course, I'm biased here, since my knowledge of the more bizarre db's is limited. I've only seen things like sybase, oracle, mysql, postgres and mssql. -- Jay yohimbe Thorne [EMAIL PROTECTED] Mgr Sys Tech, Userfriendly.org
Re: modperl and SQL db select
Please, please, please KILL THIS THREAD!
Re: modperl and SQL db select
Hi all, On Thu, 21 Mar 2002, Perrin Harkins wrote: Please, please, please KILL THIS THREAD! I second that. It's all been done before guys. 73, Ged.
Re: modperl and SQL db select
On Thursday, March 21, 2002, at 01:05 PM, Dave Rolsky wrote: Further on, he was discussing threaded discussion forums and assumed a recursive query. While recursing the DATA is probably (though provabably not always) necessary, there is no need to storm the db with that many queries, since query setup time, queing, and results extraction have noticable time penalties. Extracting the data with a single query and recursing it in RAM is a much more db and cpu friendly idea. This I can agree on, though it is more a limitation of SQL, which provides no meaningful way to do a recursive query. Vanilla SQL, perhaps, but not Oracle's flavor! CONNECT BY PRIOR :-) -- -- Tom Mornini -- eWingz Systems, Inc. -- -- ICQ: 113526784, AOL: tmornini, Yahoo: tmornini, MSN: tmornini
Re: modperl and SQL db select
Is there any issue with using modperl with postgres vs mysql for a database driven website? Don't want to bark up the wrong tree in a mod_perl project only to discover I picked the wrong .db :-/ http://www.mysql.com/news/article-91.html This was an interesting article, even though it didn't compare postgres to the other databases being tested. All of the mod_perl sites we have worked on use MySQL, and we have never had any problems with it. The newest versions of MySQL (4.x) also add some functionality that might have made MySQL look a bit less tempting for a developer to use in the past. Marc Slagle Secure Level 559 Liberty Hill, 2W Cincinnati, Ohio, 45210 (513) 665 4931 http://www.securelevel.com
