tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christopher Schultz <ch...@christopherschultz.net>
Subject Re: PostgreSQL vs MySQL with Tomcat
Date Thu, 15 Jan 2009 23:27:14 GMT
-----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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Mime
View raw message