August 8, 2008 at 10:02 am
Nice intro. My company is evaluating MySQL with a variety of expectations depending on with whom you speak. We are primarly MS SQL and Oracle off-host ( non-DB2 ). The core DB group has little experience in the Open Source arena but are looking to remedy that shortly. While I agree MySQL definitely has a place (cost, especially when looking at Oracle...yeeesh) and fits in, I'm a stodgy "old-timer" trying to redeem myself from my propreitary db ways. My initial research raises concerns, particularly the supportability of MySQL in large enterprise environments regarding:
- Environments with high concurrency demands, not just reads but high transactional volume. I'm under the impression this could be ugly.
- Lack of good backup technology. Having to lock all my tables to get a file level backup reduces availability somewhat unless you get fancy with your volume managers.
- Enterprise level security. Sometimes regulatory requirements impose requirements in this regard. For example, audit capabilities (DCL, DML, DDL, Logins etc ), account configuration ( password complexity, ability to lock accounts after x bad logins or at all! etc ). These features seem lacking in MySQL that were at least somewhat addressed in MS SQL 2005.
- Operational support/performance tuning aids. Oracle has the wait interface, tracing, ASH etc. MS SQL has profile among other features. What does MYSQL have....crickets. If I have a poor performing process what would I use? Instance wide stats are not going to cut it at that level. However, perhaps I've not gotten to that level in my researching yet....
- Vendor support...okay, this is the bigot in me. I know this can be purchased from Novell, Sun...etc. It would be interesting to know how this check box has been addressed by others. When a system is down...its nice to call somebody and get a quick answer rather than some forum with sometime dubious answers.
I agree I'd love to hear more about your experiences with these topics. Explaining additional comments like Innodb being a scaled down version of MySQL. Do you mean performance or in terms of robustness of functionality? etc.
Thanks
August 8, 2008 at 10:26 am
As a reply to the large datasets, myisam grew out the data warehouse world and understand it can handle large datasets. However the comment is based on the experience of building a 12 GB primary key on a 20 GB dataset. The process took over 36 hours to complete on a HP 585 with 16 GB of memory.
August 8, 2008 at 11:12 am
I'm sorta amazed that this thread hasn't become a religious war...
You can use both innodb and MyIsam for very large datasets. In fact, a lot of the most popular web sites use MySQL. The best thing is to contact MySQL and talk with someone very familiar with what you are trying to do and then see how it compares.
There are third party backup solutions for MySQL. There's also mysqldump which is nice to dump a small database (schema and data). Sorta like Oracle's datapump but text based.
I'm sure MySQL could point you in the right directory for query tuning, but one nice feature was the slow running query log. You could activate it and MySQL would write any query that ran long to a text log file. You could then begin optimizing with the queries causing the most trouble. Quite helpful if you ask me.
Others have mentioned PostgreSQL. Postgres is another nice database. To me, Postgres tends to track Oracle and MySQL is more like a MSSQL (yes I know there are missing features), but it's always dangerous to make those comparisons :^).
August 8, 2008 at 11:37 am
The long running query log falls a bit short in an oltp environment, because it can only be set at the second level. For example if you want to track queries running over 100 ms, you can't use this feature out of the box. I say out of the box, because I know there is a tweak to the source code you can compile that does provide this functionality.
ken kaufman
Lead DBA, Zillow.com
August 8, 2008 at 12:56 pm
Thanks for the interesting article.
NDB
"This is a true cluster". Yes you're right. And I think the cluster probably starts scaling even more when you have 4, 6, 8 , or 16 nodes. Something you can't do on MS SQL.
SQL Server Cluster is just a fail over solution, (or hot stand by), where SQL resources are switched to another physical box. But SQL resources are not shared among the 2 physical boxes.
Mysql also offers a fail over solution (DRDB i think). We're using Veritas cluster on Unix.
We also have a 64 bit, 16 CPU, 32 GB RAM and a SAN. CPU is sleeping all day long, yawning.
MyISAM.
Yes, it's fast, for FullText search. Not meant for PK look up. (innoDB is faster for this).
Replication
I would also have written something about replication.
MS SQL replication is just pure pain. Mysql is so easier to set up and offer lots of features like
1 master, 1,2, 10 slaves, 1 master 1 slave which replicates to other slaves. Another nice things is you can have innoDB on master (for the writes) then MyIsam on the slave (for the reads).
The Web is 80-90% read in general. We're also converting our TextML server to myIsam until we find better solution on Unix.
I would have loved to see your hardware configuration.
Did you use any 64 bit?
Anyway, great article. I think Mysql is really getting some traction in big corpo.
Franck L.
Sr SQL DB (10+ years) and MySQL DBA (3 years)
August 8, 2008 at 12:59 pm
bnordberg (8/8/2008)
Is anyone else having issues with MySQL's documentation and addition/removal of features? Things like database renaming (added then removed), local file imports having to use the option -L (was optional, now required) ... It seems like each release, no matter how minor, means I have to re-learn everything. Scripts I save in 1 version fail when run against the next minor release. I realize things like the database renaming were better off removed as they did not work at all. But how about fixing instead of just removing!Also I would have to agree with performance issues, I have tables with 144 million rows, in MyISAM and the performance is worse than that of an MS Access DB!
Overall MySQL is not my DB of choice - SQL Server, Oracle and Sybase are better in every aspect. But MySQL is free, so I guess its worth all my time re-writing scripts!?
Are you using a SAN?
How about indexes?
MyIsam is not best for PK lookup.
August 8, 2008 at 1:25 pm
All our installs have been all 64 bit mostly using 16 GB of memory, with some using 8. The ease and sturdiness of replication has been a significant benefit for redundancy as well as splitting applications up between read and write. I hear in 6.0 they'll be leveraging replication to provide a synchronous mirror type env. One of the nice features of SQL Servers replication model is the fact that the transactions are stored in the distribution database, and I've been looking at a way to leverage the MSrepl_commands table so that we can replicate out to mysql.
ken kaufman
Lead DBA, Zillow.com
August 8, 2008 at 1:33 pm
All our installs have been all 64 bit mostly using 16 GB of memory, with some using 8. The ease and sturdiness of replication has been a significant benefit for redundancy as well as splitting applications up between read and write. I hear in 6.0 they'll be leveraging replication to provide a synchronous mirror type env. One of the nice features of SQL Servers replication model is the fact that the transactions are stored in the distribution database, and I've been looking at a way to leverage the MSrepl_commands table so that we can replicate out to mysql.
it should work fine then. Are you using a SAN?
Have you tried switching few tables to innoDB and run some query tests?
I think one important missing feature in MySQL is a Query is not able to use multiple indexes for 1 table. Always uses 1 index per query per table.
And the explain command is limited. MS SQL Query plan and Statistic io is a really good way to improve perf.
August 8, 2008 at 1:36 pm
We haven't used any SAN's we've stayed to single box solutions (HP 165/185/385/585). On the PK I referenced earlier we needed to query individual records on a dataset of 600 million rows in under 20 ms. Due to the particulars of the query it had to come off a b-tree in memory. We tried innodb, but the load times took to long. The index itself was 14 bytes, so we didn’t want to use ndb as we were getting close to our memory limitations on the box. So we created the b-tree on a myisam structure and pinned it using the query cache. In retrospect the memory engine would most likely have been a better choice.
ken kaufman
Lead DBA, Zillow.com
August 8, 2008 at 2:05 pm
Samuel Clough (8/8/2008)
I'm sorta amazed that this thread hasn't become a religious war...
We're a pretty laid back group here, I think it's the patchouli that Steve pipes through our connections.
😀
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
August 8, 2008 at 2:13 pm
Thanks for the article, Ken, very informative. It wasn't what I was expecting, I was expecting more of a command-level or operational level rather than storage engine level. Still, good information. I've always wondered what difference the storage engine made, and now I know.
I've tried to get MySQL working on my various platforms but always had problems, it was a couple of generations ago and hopefully they've made substantial improvements to their installers. In the books that I've read I was impressed by how close their syntax is to T-SQL and ANSI, it made me feel that the learning curve from a syntactic standpoint wouldn't be too great. One thing that I thought was really cool was the backup scripting out the table structure in addition to making the data a series of insert statements.
Currently I have it pre-installed on my web sites (on RHE) as a standard package supporting and supported by Fantastico, running a few Small Machines Forum message boards, a chat engine, and now my WordPress blog. I quite like the power and stability, and the PHP admin interface works well, though it takes a little getting used to.
To add my $0.002 worth to suggestions for future articles, how about an installation walk-through? I know I could find plenty online, but it would be nice to see one from someone that we know, so to speak. I'd also like to see an article illustrating how to do stored procedures in MySQL.
Not that there's any pressure for you to write more articles for us here.... :hehe:
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
August 8, 2008 at 5:51 pm
I’m not sure if Steve wants to turn this into a mysql tutorial site. If you’re coming from a windows background the install can be more difficult then working with the database. I’ll post something on my blog and give the url once complete. Until then here’s a quick step through list provided by mysql:
shell> groupadd mysql
shell> useradd -g mysql mysql
shell> cd /usr/local
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
shell> bin/mysqld_safe --user=mysql &
ken kaufman
Lead DBA, Zillow.com
August 9, 2008 at 1:39 am
Great Article....:)
August 9, 2008 at 1:48 am
Nice article...
Atif Sheikh
August 11, 2008 at 1:37 am
If you are using Debian linux, you can install MySQL with a simple: sudo apt-cache install mysql-server. This will take care of all the group and user creations, the mysql_install_db script, etc. It will also all a script to init.d which will start the server automatically when the machine restarts, something that was missed out of the instructions above.
The windows installer is almost as easy. Download the installer and run and all you need to do is answer basic questions about the installation.
To fine tune MySQL you need to play with a file called my.cnf which under linux lives in /etc/my.cnf and using the windows installer end up in the MySQL Server [version] directory.
my.cnf allows you to set parameters concerning how much memory is allocated for the query cache, for joins, which port to accept connections on (default is 3306), where the data lives, how many letters to include in a full-text index, etc, etc. You can even use two versions of my.cnf to run two instances of MySQL on the same box using different ports and set the path to my.cnf when you install the db, ie, when you run mysql_install_db.
The MySQL manual is an excellent source of information for all of this http://dev.mysql.com/doc/ as it includes the official documentation from the MySQL developers and also lots of helpful comments from users to avoid gotchas.
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply