-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Ken,
Ken Bowen wrote: > Unfortunately, MySQL isn't really that tunable. On the other hand, MySQL > requires very little in the way of tuning! This was my comment, and that's the gist I got from reading Zawodny's and Balling's "High Performance MySQL" from O'Reilly. There are /some/ things you can tune, but not much. It's like an auto-adjusting clutch. Sure, you can adjust it, but it's going to do a pretty good job on its own. > I created a couple of proof-of-concept projects with PostgreSQL for > someone about six months ago, and the only really puzzling thing what > how to set up "sequences" corresponding to MySQL's AUTOINCREMENT (I'm > not sure I've really wrapped head around it completely anyway). For > those small projects, it worked well, but there was no stress involved. I have no experience with PostgreSQL, but I was always irritated by lack of support (in previous JDBC specifications) for the AUTOINCREMENT fields (fixed, of course, in JDBC 1.4 when Statement.getGeneratedKeys was added). Oracle-style SEQUENCES always made more sense to me -- although they require an additional query to be issued every time you want a new id, you can use them for anything; not just record PKs. I think PostgreSQL has Oracle-style sequences. There are no auto-incrementing PKs or anything like that: you first SELECT the next value from the sequence, then use that as the PK for your INSERT that you issue immediately afterward. If you don't need to capture the new id from the code, you could probably even do this: INSERT INTO my_table (id, ...) VALUES ((SELECT next FROM my_sequence), ...) I have no idea if that would even work. Give it a try! > I'd like to hear about people's experiences and what they think are the > strengths and weaknesses of each DBMS for use behind Tomcat. MySQL has always had super good performance for me, especially using MyISAM tables. The addition of InnoDB tables was great because you got the relatively fast speed of MySQL (compared to, say, Oracle) with the integrity you really expect from a production-quality RDBMS. Since I'm not a DBA, I'm appreciated the fact that MySQL can often be a fire-and-forget kind of service. It just doesn't need much care and feeding. I remember my first job out of college required new engineering hires to go through their one-box-wonder runbooks to make sure we "understood how installs went and how the pieces fit together". Installing Apache Jserv (yeah!), httpd, and most everything else took about one or two printed pages of documentation. The rest of the runbook was Oracle install and configuration. Install. Run the command-line tool. Quit the command-line tool. Edit a config file. Re-run the command-line tool. Create the database. Quit the command-line tool. Change the rollback segment size. Etc. etc. etc. I never even understood anything I was doing, and neither did the folks that were supervising our execution of these tasks. These were just instructions that probably worked for someone in the past, so they wrote them down and nobody ever bothered to figure out if there were any better options. I was one of the first engineers who used MySQL in a project that went to production (and I had to implement my own pre-innodb manual rollback logic -- yikes!). MySQL supports a lot of functions that are, of course, outside the SQL standard, but are nonetheless useful. Here is what my MySQL driver (Connector/J 5.0.8) reports for the functions available to the client (server version is 5.0.70): Supported functions: Numeric: ABS,ACOS,ASIN,ATAN,ATAN2,BIT_COUNT,CEILING,COS,COT,DEGREES,EXP,FLOOR,LOG,LOG10,MAX,MIN,MOD,PI,POW,POWER,RADIANS,RAND,ROUND,SIN,SQRT,TAN,TRUNCATE String: ASCII,BIN,BIT_LENGTH,CHAR,CHARACTER_LENGTH,CHAR_LENGTH,CONCAT,CONCAT_WS,CONV,ELT,EXPORT_SET,FIELD,FIND_IN_SET,HEX,INSERT,INSTR,LCASE,LEFT,LENGTH,LOAD_FILE,LOCATE,LOCATE,LOWER,LPAD,LTRIM,MAKE_SET,MATCH,MID,OCT,OCTET_LENGTH,ORD,POSITION,QUOTE,REPEAT,REPLACE,REVERSE,RIGHT,RPAD,RTRIM,SOUNDEX,SPACE,STRCMP,SUBSTRING,SUBSTRING,SUBSTRING,SUBSTRING,SUBSTRING_INDEX,TRIM,UCASE,UPPER System: DATABASE,USER,SYSTEM_USER,SESSION_USER,PASSWORD,ENCRYPT,LAST_INSERT_ID,VERSION Time/Date: DAYOFWEEK,WEEKDAY,DAYOFMONTH,DAYOFYEAR,MONTH,DAYNAME,MONTHNAME,QUARTER,WEEK,YEAR,HOUR,MINUTE,SECOND,PERIOD_ADD,PERIOD_DIFF,TO_DAYS,FROM_DAYS,DATE_FORMAT,TIME_FORMAT,CURDATE,CURRENT_DATE,CURTIME,CURRENT_TIME,NOW,SYSDATE,CURRENT_TIMESTAMP,UNIX_TIMESTAMP,FROM_UNIXTIME,SEC_TO_TIME,TIME_TO_SEC Some stuff is a bit far-fetched (SOUNDEX? come on...) but others are very useful (like the statistical functions, regular expressions, etc.). I definitely make use of these functions in my apps, even though they are MySQL-specific (I just make sure to say something in the code comments!). I don't make heavy use of stored procedures, so I cannot comment on those but you may make your decision based upon only that (supported languages, spec compliance, etc.). I do know that triggers have only recently made an appearance and I tried playing with them once: my initial experience was that they did not meet my needs. All I wanted to do was check some stuff and then throw an error if some conditions weren't met: essentially a complex CONSTRAINT that MySQL wasn't able to do with a regular DDL constraint. Apparently, this version of MySQL (5.0?) doesn't allow you to simply say "fail right now". Instead, I had to do a SELECT from a table that didn't exist, but had an informative name (like "SELECT bogus FROM your-record-is-bad"). That's about it. MySQL has been very good to me, in spite of the strangeness with which I've had to deal for almost a decade, now. I'd love to hear others' experiences. Hope that helps, - -chris -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAklvxlIACgkQ9CaO5/Lv0PCtFwCgm10RxhfqVfZLaQuJ7atsXIZq 3Y4AnjjrSUA6E16dYpg3th5ka+1ueprY =hHWo -----END PGP SIGNATURE----- --------------------------------------------------------------------- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
